PEAR MDB2: FOREIGN KEY support is complete!

I just committed to CVS the last piece of code to add full FOREIGN KEY constraints support to PEAR::MDB2 in all the drivers. The last missing piece was the sqlite driver: even if SQLite parses FKs, they are not enforced, so MDB2 now automatically creates some triggers to enforce them. All the ON UPDATE|DELETE actions are covered (RESTRICT|CASCADE|SET NULL|SET DEFAULT|NO ACTION). You're welcome to fetch the package from CVS and give it a go.
To create a constraint, you have to use the createConstraint() method in the Manager module. The constraint definition has this array structure (you can use it in the Manager module to create a new constraint, or you get it back from the Reverse module when you call getTableConstraintDefinition()):

array (
    [primary] => true|false
    [unique]  => true|false
    [foreign] => true|false
    [check]   => true|false
    [fields] => array (
        [field1name] => array() // one entry per each field covered by the constraint
        [field2name] => array()
        [field3name] => array(
            [sorting] => ascending|descending
            [position] => 3
        )
    )
    [references] => array(
        [table] => name
        [fields] => array(
            [field1name] => array( //one entry for each referenced field
                [position] => 1
            ) 
            [field2name] => array(
                [position] => 2
            )
            [field3name] => array(
                [position] => 3
            )
        )
    [deferrable] => true|false
    [initiallydeferred] => true|false
    [onupdate] => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION
    [ondelete] => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION
    [match] => SIMPLE|PARTIAL|FULL

Updated PEAR packages

I've also recently published the Release Candidate 1 of the PEAR::Translation2 package, so we're really close to a stable release, and added two "Reverse" drivers in the /examples/ dir of the PEAR::Pager (CVS only), useful for blogs and archives where more recent items/posts come first. In these drivers, the page numbers are generated in reverse order like this:

< 5 | 4 | 3 | 2 >

HTH

Enjoy!




2 responses to "PEAR MDB2: FOREIGN KEY support is complete!"

Hello. I'm using the Manager module in MDB2 and I can't find any documentation beyond the example posted here. Examples are clear for Primary Keys and Unique constraints, but not so for Foreign Keys and non-existent for Checks.
I want to add constraints but can't figure out how to build a proper array. Would you help me? I can't figure out what the Sorting and Position for a field are for (within the context of a constraint), what's the meaning of referring multiple fields from multiple fields (are they paired FK references by order?).
Also, there's no information on how to write a Check, and I can't figure out what deferrable, initiallydeferred and match are for. Perhaps I'm missing an updated documentation page?

Hi, please have a look at the MDB2 test suite, there are some examples there. The official manual also has some examples, the syntax to create constraints is the same (just use the above array instead of the one to create a PRIMARY KEY): http://pear.php.net/manual/en/package.database.mdb2.intro-manager-module.php
Regarding the sorting and position properties, they are for multi-field constraints. CHECK constraints are not supported yet.
DEFERRABLE, INITIALLY DEFERRED and MATCH options are defined by the SQL standard, but they're not available in all DBMS. Here's an example of how they're used:
http://infolab.stanford.edu/~ullman/fcdb/oracle
http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html/or-triggers.html

Lorenzo Alberton

Lorenzo Alberton Lorenzo PHP5 ZCE - Zend Certified Engineer has been working with large enterprise UK companies for the past years and is now CTO at DataSift. He's an international conference speaker and a long-time contributor to many open source projects. Lorenzo Alberton's profile on LinkedIN View Lorenzo Alberton's Twitter stream

Lorenzo Alberton - Sun Certified MySQL 5 Developer

Tags

AJAX, Apache, Book Review, Charset, Cheat Sheet, Data structures, Database, Firebird SQL, Hadoop, Imagick, INFORMATION_SCHEMA, JavaScript, Kafka, Linux, Message Queues, mod_rewrite, Monitoring, MySQL, NoSQL, Oracle, PDO, PEAR, Performance, PHP, PostgreSQL, Profiling, Scalability, Security, SPL, SQL Server, SQLite, Testing, Tutorial, TYPO3, Windows, Zend Framework

Buy me a book - The Best Software Writing 1