Using PostgreSQL SERIAL keys and PEAR DB sequences properly

Our internal CRUD app framework uses DB_DataObject as its data access layer, and DB_DO is in turn built on top of the Pear_DB layer. Pear_DB has a special sequence system that works great out of the box with MySQL AUTO_INCREMENT keys, but it needs a little fiddling to play nice with PostgreSQL’s SERIAL metatype. Basically, if you don’t tweak the sequence stuff a bit, Pear_DB won’t be able to retrieve the next ID # in the sequence. This is an ignorable problem for single-table situations, but can cause problems (including data loss) with many-to-many relationships and other more complex cases.

The easiest way to get the Pear_DB sequences to play nice with your PostgreSQL sequences is to redefine the sequenceKey() method in your DB_DataObject-extending class:

class Otacon_Contact extends DB_DataObject 
{
     [...]

    /* the code above is auto generated do not remove the tag below */
    ###END_AUTOCODE

    /* this is required to get PEAR DB sequences working properly */
    function sequenceKey() { return array('contact_id', false, 'contact_contact_id_seq'); }

     [...]
}

In the example above, contact_id is the primary_key of the table, and contact_contact_id_seq is the sequence that contact_id gets its next value from. This makes the Pear_DB sequence stuff to query this sequence to get the next value for your primary key, and you should be golden.

  • ucho
    http://litestep.boo.pl
    07/11/2006 08:02:55 AM

    They should put this in both PSQL and PEAR::DB manual :P