Re: Alternative MySQL PEAR DB sequence behavior

"Stig S. Bakken" wrote: > > Oleg Rekutin wrote: > > > > Current MySQL sequence emulation simply keeps adding and adding IDs to the > > sequence's table. This bothered me, because a lot of sequences with a lot of > > IDs are just going to take up space for no reason. > > > > So I modified it to keep track of just one value... Haven't tested it > > rigorously, but AFAIK it works. Patch is quoted below. (So here I am, having > > wasted 1.5 hours on this patch when I have an urgent project otherwise :).) > > > > It also backwards-compatible, as it cleans up the garbage from the old-style > > sequences (otherwise UPDATE fails), so that nothing has to be done on the > > part of the user when the new mechanism is used. In addition, since the new- > > style sequences use the same tables w/ an AUTO_INCREMENT id, it is also > > backwards-compatible in the sense that if the user downgrades to the old- > > style mechanism or moves the DB & application to a server with an older > > version of PEAR DB, everything should still work (it will just keep on > > inserting values). > > > > Deep inside I want all sequences to be contained in one table, but that's > > too much of a pain in the ass to do, plus various concurrency issues arise > > (there are concurrency issues nonetheless, mostly arising from the fact if > > two clients attempt to create two sequences simultaneously... CREATE > > DATABASE will fail for one of them... might want to use CREATE DATABASE IF > > NOT EXISTS then?). > > > > Again, you might want to run tests of your own... Works for me :) > > I've committed this patch now, and it works fine except that the first > id returned seems to be 2? At least in the case of > DB/tests/mysql/005.phpt. Any ideas? It seems that INSERT INTO ${sqn}_seq VALUES(0) in createSequence inserts the value "1", so update always get at least value 2. Should I change the test? Tomas V.V.Cox PS. I've commited the new version of the patch Oleg send me time ago