Hi, 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. 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
Matt, MySQL doesn't support transactions currently (but you probably know this). There is a function in mySQL that allows you to retrieve the "next" UID from a key column. I can't remember for the life of me what it is, but it's in the MySQL manual. You'd need to incorporate this into a subselect in the insert or update statement in the SQL query eg: INSERT INTO my_table (my_col1, my_col2, my_col3) values (SELECT next_available_id, <dtml-var myargument>, <dtml-var myargument2>) I dont even know if MySQL allows this, but its the most atomic way to do this. Matt Goodall wrote:
Hi,
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.
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
-- Chris McDonough - Digital Creations, Inc. Publishers of Zope - http://www.zope.org
At 04:42 PM 2/10/00 +0000, Matt Goodall wrote:
Hi,
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.
In MySQL, just declare your id field to be AUTO_INCREMENT. Nothing could be easier. From the manual: An integer column may have the additional attribute AUTO_INCREMENT. When you insert a value of NULL (recommended) or 0 into an AUTO_INCREMENT column, the column is set to value+1, where value is the largest value for the column currently in the table. AUTO_INCREMENT sequences begin with 1. The SQL function LAST_INSERT_ID() returns the last generated AUTO_INCREMENT value. This is maintained on a per-connection basis, so it is safe to use. Andrew
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
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
participants (4)
-
Andrew Wilcox -
Chris McDonough -
Matt Goodall -
Stephen Harrison