how to do manualy incremented counter

[a...@svilendobrev.com]

hi
lets have, say, invoice-numbers.
a completely new invoice has new invoice-number.
same invoice may have many versions - the invoice-number does not
change.

how to safely generate a new number for each new document (lets say it
is simple number-incrementing)?

one safe and simple way i've just invented is to have a separate table
of one column - the invoice-numbers - and have a foreign key to it.
First version of new invoice will create a row in the table and link
to it, other versions of the invoice keep the link. Besides the
simplicity this allows for any numbering scheme - whatever one puts
there; even the table may have just primary id if just rowids are
enough.

More space-savvy way is to have just one table with a row per
numbering-type (one for invoice-numbers, one for account-numbers,
employee-numbers etc), and for a new document increment the
respective row and get its value. If there are sequences, this seems
alright, the whole operation is atomic.

But if there are no sequences, i cannot make this multi-access safe.
atomic updates are alright for avoiding races, but reading the value
after that - no guarantee it is right one, another atomic update may
have sneaked in meanwhile. Any help here?
or should i use locking? im trying to avoid it...

ciao
svilen

[Bobby Impollonia]

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.

[a...@svilendobrev.com]

On Tuesday 24 June 2008 17:58:54 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.

no no, this is not primary_key, as i said, there are many versions
sharing same invoice_number.
The primary_key is a version-id and thats alright 100% automatical.

[Jorge Godoy]

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.

[a...@svilendobrev.com]

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...