[Zope] Database transaction (follow up)

[Matt Goodall]

This is a multi-part message in MIME format.
--------------E98B47BD12D252DCCF2C1C76
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

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.
> 
> Any other ideas would be more than welcome.

Firstly, thanks for the replies but I forgot to mention something
important!

Also, I'd like to apologise now for the length of this post - it's not
exactly succinct!

This post is more of a, "has anyone got any better ideas", sort of
thing. Feel free to criticise, correct or suggest anything.

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

I haven't had the chance to try something like Chris McDonough suggested
yet but it could be useful.

So a couple of work-around ideas:

1. Use ZODB to keep track of the latest docnum for each doctype. My
understanding is that ZODB does support transactions - is that correct?
2. Lock and unlock the table around the "real" queries.
3. Change to a database manager that supports transactions i.e.
PostgreSQL.
4. Some sort of file system lock from an external python method.

And a couple of questions:

1. Does anyone know the state of the ZPyGreSQLDA product?
2. Would it be better to use ODBC? Is this even possible on Linux?
3. What happens to Zope if the user cancels the request after the lock
but before the unlock? I know it's unlikely but someone's bound to
manage it! You know what users are like :-). I suspect all of the
solutions I can think of are susceptible to this. 

Well, thanks for reading this and thanks for any ideas.

Cheers, Matt.

-- 
Matt Goodall             |  Isotek Electronics Ltd
email: mgg@isotek.co.uk  |  Claro House, Servia Road
Tel: +44 113 2343202     |  Leeds, LS7 1NL
Fax: +44 113 2342918     |  England
--------------E98B47BD12D252DCCF2C1C76
Content-Type: text/x-vcard; charset=us-ascii;
 name="mgg.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Matt Goodall
Content-Disposition: attachment;
 filename="mgg.vcf"

begin:vcard 
n:Goodall;Matt
tel;fax:+44 113 234 2918
tel;work:+44 113 234 3202/3
x-mozilla-html:TRUE
org:Isotek Electronics Ltd
adr;quoted-printable:;;Claro House=0D=0AServia Road;Leeds;W.Yorks;LS7 1NL;England
adr:;;;;;;
version:2.1
email;internet:mgg@isotek.co.uk
x-mozilla-cpt:;-21184
fn:Matt Goodall
end:vcard

--------------E98B47BD12D252DCCF2C1C76--