[sqlalchemy] Re: how to do manualy incremented counter - az...@svilendobrev.com - com.googlegroups.sqlalchemy

5 messages in com.googlegroups.sqlalchemy[sqlalchemy] Re: how to do manualy in...

From	Sent On	Attachments
az...@svilendobrev.com	24 Jun 2008 07:13
Bobby Impollonia	24 Jun 2008 07:58
az...@svilendobrev.com	24 Jun 2008 08:23
Jorge Godoy	24 Jun 2008 09:31
az...@svilendobrev.com	24 Jun 2008 09:55

Subject:	[sqlalchemy] Re: how to do manualy incremented counter
From:	az...@svilendobrev.com (az...@svilendobrev.com)
Date:	06/24/2008 09:55:11 AM
List:	com.googlegroups.sqlalchemy

On Tuesday 24 June 2008 19:32:19 Jorge Godoy wrote:

Bobby Impollonia wrote:

In mysql you can declare a integer column to be auto_increment and it will handle for you giving each row a different number. Other databases have similar mechanisms. It sounds like the invoice number is the primary key for the invoice table? If you have a integer primary key in sqlalchemy, it assumes that you want to make it auto increment so this should all happen automatically.

I don't think this would work for invoices. What happens if a transaction is rolled back in MySQL an an auto-increment column is used? In PostgreSQL, using sequences, the number would be lost forever.

Invoices usually require a continuous numbering and should have no holes in the sequence.

What is the best solution is to have a table where you'll store the numbers, access it in a serializable way and then create and remove locks. This will grant that you don't have holes and that you don't have repeated numbers allocated to different invoices.

mmm yes, there is this things also. Some numbering schemes allow/force holes, others not. There can be also hierarchical numbering schemes, e.g. employee is 1-31-52 maybe a table is really the most flexible way of doing it...