Alternative MySQL PEAR DB sequence behavior

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 :) --- D:\Program Files\php4\pear\DB\old-mysql.php Wed Jul 11 19:50:26 2001 +++ D:\Program Files\php4\pear\DB\mysql.php Wed Jul 11 20:53:34 2001 @@ -373,7 +373,9 @@ $sqn = preg_replace('/[^a-z0-9_]/i', '_', $seq_name); $repeat = 0; do { - $result = $this->query("INSERT INTO ${sqn}_seq VALUES(NULL)"); + $result = $this->query("UPDATE ${sqn}_seq ". + 'SET id=LAST_INSERT_ID(id+1)'); + error_log('affected rows:' . $this->affectedRows()); if ($ondemand && DB::isError($result) && $result->getCode() == DB_ERROR_NOSUCHTABLE) { $repeat = 1; @@ -381,6 +383,25 @@ if (DB::isError($result)) { return $result; } + } else if (DB::isError($result) && + $result->getCode() == DB_ERROR_ALREADY_EXISTS) { + // Must be using old sequence emulation implementation, + // clean up the dupes + $highest_id = $this->getOne("SELECT id FROM ${sqn}_seq ". + 'ORDER BY id DESC LIMIT 1'); + if (DB::isError($highest_id)) { + return $highest_id; + } + // We should probably do something if $highest_id isn't + // numeric, but I'm at a loss as how to handle that... + $result = $this->query("DELETE FROM ${sqn}_seq ". + "WHERE id <> $highest_id"); + if (DB::isError($result)) { + return $result; + } + // This should kill all rows except the highest, now we + // can try again + $repeat = 1; } else { $repeat = 0; } @@ -397,9 +418,14 @@ function createSequence($seq_name) { $sqn = preg_replace('/[^a-z0-9_]/i', '_', $seq_name); - return $this->query("CREATE TABLE ${sqn}_seq ". + $res = $this->query("CREATE TABLE ${sqn}_seq ". '(id INTEGER UNSIGNED AUTO_INCREMENT NOT NULL,'. ' PRIMARY KEY(id))'); + if (DB::isError($res)) { + return $res; + } + // nextId call will generate ID 1 + return $this->query("INSERT INTO ${sqn}_seq VALUES(0)"); } // }}}