[Zope] Sybase: How generate id keys? (surrogate keys)

[Alexander Staubo]

I use identity columns with Sybase -- the only problem I've experienced
is a tendency to occasionally "burn" ID values, resulting in gaps.

If you're not worried about non-sequentiality, identity columns are
generally not a problem. Do you anticipate crashes? I have run Sybase
for about a year, and never experienced a single crash -- meanwhile,
Zope has crashed and/or been restarted many, many times.

Aside for identity columns, here's an alternative scheme I used some
time ago with an  Access database (where, aside from DAO/ADO, you simply
cannot obtain the previously inserted "autoincrement" value, causing
problems for web-based forms): Create a table such as this

	create table Ids
	(
	  Id int
	    not null
	    primary key nonclustered
	)

Then create either a stored procedure (don't like 'em) or a Zope DTML
Method that will (a) get the lowest available ID, and (b) if the table
is getting empty (count < delta), then replenish the table with new,
unique values, starting at the ID you just obtained. (If the table is
empty when you start out, assume this is the first time the method is
run, and simply start with a seed of, say, 1.)

Because of transaction logic, IDs will never be "burned", and the only
gaps you will experience will come from IDs that disappear when their
parent records are deleted.

Hope this helps.

-- 
Alexander Staubo         http://alex.mop.no/
The difference between theory and practice is that, in theory,
there is no difference between theory and practice.

> -----Original Message-----
> From: Hung Jung Lu [mailto:hungjunglu@hotmail.com]
> Sent: Tuesday, March 21, 2000 12:24 AM
> To: zope@zope.org
> Subject: [Zope] Sybase: How generate id keys? (surrogate keys)
> 
> 
> Hi,
> 
> How do people generate id keys (surrogate primary keys)
> in Sybase+Zope? There are some recommended ways of doing it
> in Sybase with stored procedures but I can't run stored
> procedures in Sybase.
> 
> I am looking at heavy-traffic strategies. So I need something
> slightly better than a simple increment table for the keys.
> 
> Any recommendations? Thanks in advance.
> 
> Hung Jung
> 
> ______________________________________________________
> Get Your Private, Free Email at http://www.hotmail.com
> 
> 
> _______________________________________________
> Zope maillist  -  Zope@zope.org
> http://lists.zope.org/mailman/listinfo/zope
> **   No cross posts or HTML encoding!  **
> (Related lists - 
>  http://lists.zope.org/mailman/listinfo/zope-announce
>  http://lists.zope.org/mailman/listinfo/zope-dev )
>