CHAPTER 6 – PEAR DB – Sequences

Database sequences are tricky portabilitywise because they are part of the SQL grammar in some databases, such as Oracle, or implemented as INSERT side effects, such as MySQL's AUTO_INCREMENT feature. The different ways of handling sequences cannot be mixed easily. To provide a single API, DB offers a third way to deal with sequences, which is different from both of these, but at least works for any database supported by DB: <?php require_once 'DB.php'; PEAR::setErrorHandling(PEAR_ERROR_DIE, "%s<br />n"); $dbh = DB::connect("mysql://test@localhost/world"); $dbh->query("CREATE TABLE foo (myid INTEGER)"); $next = $dbh->nextId("foo"); $dbh->query("INSERT INTO foo VALUES(?)", $next); $next = $dbh->nextId("foo"); $dbh->query("INSERT INTO foo VALUES(?)", $next); $next = $dbh->nextId("foo"); $dbh->query("INSERT INTO foo VALUES(?)", $next); $result = $dbh->query("SELECT * FROM foo"); while ($result->fetchInto($row)) { print "$row[0]<br />n"; } $dbh->query("DROP TABLE foo"); #$dbh->dropSequence("foo"); The paradigm is not to use auto-increments, last-insert-id calls, or even "sequencename.nextid" as part of the query. Instead, you must call a driver function to generate a new sequence number for the specific sequence that you then use in your query. The sequence number generation is still atomic. The only disadvantage with this approach is that you depend on PHP code (DB) to make the right sequences for you. This means that if you need to obtain sequence numbers from non-PHP code, this code must mimic PHP's behavior. This example displays three lines with "1", "2", and "3". Running this script repeatedly will not restart the output at 1, but continue with "4" and so on. (If you uncomment the last line with the dropSequence() line call, the sequence will be reset and the output will start with "1".) The methods for dealing with sequences are the following: nextId($seqname, $create = true). nextId() returns the next sequence num- ber for $seqname. If the sequence does not exist, it will be created if $create is true (the default value). createSequence($seqname). Creates a sequence or a sequence table for data- bases that do not support real sequences. The table name is the result of sprintf($dbh->getOption("seqname_format"), $seqname). dropSequence($seqname). Removes the sequence or sequence table. Subsequent calls to nextId() for the same $seqname will re-create and reset the sequence.