Re: [Zope] Sybase: How generate id keys? (surrogate keys)
bill anderson <bil-@libc.org> wrote:
IOW, would a random, unique id work for you?
Yes, it'll be fine.
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. :( I think I will adapt the Sybase's recommended stored procedure to Zope, by using DTML and ZSQL methods. It's kind of nasty for something as simple as a surrogate key generation, but it seems more secure. thanks again, Hung Jung ______________________________________________________ Get Your Private, Free Email at http://www.hotmail.com
Hung Jung Lu wrote:
bill anderson <bil-@libc.org> wrote:
IOW, would a random, unique id work for you?
Yes, it'll be fine.
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
participants (2)
-
Bill Anderson -
Hung Jung Lu