RE: [PEAR-DEV] Sequences for DB:MySQL

> From: Blair Robertson [mailto:brobertson@squiz.net] > Sent: Friday, May 02, 2003 2:05 AM > I have just noticed an inconsistency between MySQL and PostgreSQL DB > objects when you are creating sequences. > > When you call createSequence() with MySQL it set's the value to 1, > so when you make your first call with nextId() a 2 is returned. > > When you call createSequence() with Postgre and then call nextId() a 1 > is returned, which is what I would expect. > > I can't test what the other databases do, but by looking at nextId() fn > in the other DB classes that are using tables to emulate sequences (ie > mssql, fbsql and odbc) createSequence() appears to initialise the value > to zero because these fns repeat in the do..while loop after creating > the sequence. > > I have attached a patch to the DB/mysql.php that fixes this issue in > createSequence() and alters nextId() to perform the same repeat that the > other classes do - please tell me what you think. Seems to fix the situation indeed. The patch seems ok to me as since it only creates more "work" in the rare case when the sequence does not yet exist. BTW: MDB never had this problem because it did not use UPDATE in nextId. However it uses two queries (INSERT, DELETE). This makes it a bit more fool proof if things go bad. But obviously creates more work. > BTW - if you are wondering why I need to call createSequence() rather > than just using the on demand feature of nextId() it has to do with > Postgre, transactions and a failed NEXTVAL() call - see the points > raised in this bug report, http://bugs.php.net/bug.php?id=22761 yeah as I commented in the bug report, we should leave things as is and simply document it.