On Fri, Oct 10, 2008 at 2:06 AM, <[EMAIL PROTECTED]> wrote: > > hi there. > i had similar thread some months ago > http://groups.google.com/group/sqlalchemy/browse_thread/thread/1a3790d58e819f01/4d8e175fc04055aa > the option 2 has 2 subcases. > - one is to keep all numberings in one table and have a somewhat > twisted query+update mechanism (Sequence if u have it, or manual > +locks otherwise) - we had that before; > - another one that i "invented" is to have a separate table for each > kind of numbering, and each number is a reference to a record in that > table. This needs no locking, querying and does not rely on Sequence > or similar DB-query-and-update mechanims. it also can work on any > scheme for numbering - the "number" itself can be anything, e.g. > text, or list dept2-room3-pos7. but i guess can be somewhat too > verbose. > > On Thursday 09 October 2008 23:14:27 Lukasz Szybalski wrote: >> Hello, >> I was wondering if anybody has a good strategy for auto >> incrementing fields. >> >> I want to auto increment field called "case#" . >> >> I have a choice of database auto increment on field "case#" or do >> it myself? (correct? No other choices exists? or something in >> between?) >> >> 1. I would like to be able to do pick a number where we will start >> doing a case#? >> 2. Reserver a case# for a special group which can auto increment >> case# between 2,000,000-2,999,999, and add them as they come. >> 3. I don't want to use (system_id) >> >> So it seems as the only way is to make my primary key: >> case# - unique key, primary, not auto incrementing and let some >> program manage auto incrementing. >> >> What options do I have with sqlalchemy to manage any range of these >> primary keys? >> 1. let db auto increment >> 2. Hold the next case# in a separate database table, and let my >> program use it to find next case# value. How would I lock/unlock >> the next case# to make there is no race condition and each case# is >> taken/successfully saved. >> 3. Any other options? >> >> Have people exeperienced with other strategy that is >> semi-automatic, and would for for these cases.? >>
It seems as a good solution will be to take the "next case#" to a different table and use that with isolation/serialization so there is no deadlocks in the application. For this thread (http://groups.google.com/group/sqlalchemy-devel/browse_thread/thread/653b6b2f49a940b8) it seems as postgreSQL is a way to go, compared to mssql for example. Am I missing anything/other options ? Lucas --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~----------~----~----~----~------~----~------~--~---