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

[Bill Anderson]

Hung Jung Lu wrote:
> 
> bill anderson <bil-@libc.org> wrote:
> >IOW, would a random, unique id work for you?
> 
> Yes, it'll be fine.
> 
> >http://www.zope.org/Members/Bill/Documentation/AutoGenID
> 
> Thanks. That is the current work-around that I am using...
> (Key derived from a time-stamp with millisecond precision.)
> There is one observation and two problems:
> 
> (1) ZopeTime is not as good as the getdate() function
>     from Sybase, if you are running multiple Zope
>     webservers (for load-balancing.) That is, it's
>     better to rely on one unique source of timer.
> 
> (2) There is a slight chance of two processes getting
>     the same key, if they happen to occur within one
>     millisecond of each other. The probability is small,
>     but unfortunately I have to address even this minuscule
>     probability. :(
> 
> (3) If the server's time is re-adjusted for any reason,
>     it's possible that some keys are regenerated.
> 
> I agree that the millisecond-precision key is really good
> for non-critical applications. But because of the nature of
> our project, we need to address even the slightest chance
> of error. :(

Just a few thoughts.

Simple workaround: make the id field unique. it would seem to me that
you could then use a try/except clause that tried to insert the data
into sybase, and redreate an id if it failed due to the id already
existing.

Additional options:
 o add a randomly selected letter (or two) to the numerical id. Now the
odds that any two servers, or a change in time, will obtain the same key
are extremely slim.
 o Randomize the order of numbers in the 'timestamp'

Combine these with the unique restriction above, and you should have a
_very_ scaleable, an relatively easy to implement, solution. 

Bill

-- 
In flying I have learned that carelessness and overconfidence are 
usually far more dangerous than deliberately accepted risks. 
          -- Wilbur Wright in a letter to his father, September 1900