[Zope] Database transaction (follow up)

[Stephen Harrison]

> Matt Goodall wrote:
> 
> > When adding to a database table I need to calculate the next number from
> > the existing records, increment that number and add the new record using
> > the calculated number.
> > 
> > The critical thing is that the transaction must be uninterrupted or two
> > or more users could be assigned the same number.
> > 
> > Can this be done with the Zope database connections? I'm using MySQL at
> > the moment but I could (presumably) change to PostgreSQL quite easily.

[snip]

> The stuff I forgot to mention is that I'm running Zope on Linux and that
> my database table looks something like this:
> 
> 	doctype char(1) not null,
> 	docnum int not null,
> 	... other stuff ...
> 	primary key (doctype,docnum)
> 
> When I'm adding a record the docnum needs to be the next number for the
> doctype which means I can't use an auto incrementing field. I need to do
> something like:
> 
>  begin transaction
>  select max(docnum) from mytable where doctype='x'
>  nextdocnum = docnum+1
>  insert into mytable values( 'x', nextdocnum, ... )
>  commit

What you could do is create another table to keep track of your 
index keys:

   CREATE TABLE sequences(
     sequence varchar(16) PRIMARY KEY,
     n int)

where sequence is an identifier (doctype) and n is an incremented 
number for that key which you use to get your new key.

So, to use this in your sql method:

  SELECT count(*)
  FROM sequences
  WHERE sequence=<doctype>

  if(count = 0)
    # Create a new sequence record
    INSERT INTO sequences
    SET sequence = <doctype>,
           n = 0
  
  # Increment the index
  UPDATE sequences
  SET n = LAST_INSERT_ID(n+1)
  WHERE sequence = <doctype>

  # Get the index
  x = SELECT LAST_INSERT_ID()

  # Now insert the record in your table
  INSERT INTO mytable VALUES( 'x', nextdocnum, ... )

This method does not need transactions because even if multiple 
instances are running at the same time, they will each get their 
own index from the sequences table, so when they insert into 
mytable, the records will be unique.

Cheers,
Stephen

-- 
Stephen Harrison - stephen@nipltd.com
New Information Paradigms - www.nipltd.com