Lorenzo Alberton

At a glance

I've finally committed the last pieces of code to implement full FOREIGN KEY support in the MDB2 Manager and Reverse modules. Please fetch the CVS version of the package and test it.

Print     Permalink

Latest articles

• PHP UK 2010: RDBMS in the social networks age
• Profiling PHP applications with XHProf: the path to scalability
• Speaking at the PHP Barcelona conference 2009
• New article: Graphs in the database, SQL meets social networks
• Create a video preview as animated GIF with FFmpeg and PHP SPL

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!

Lorenzo Alberton

Lorenzo works as a Consultant / Software Engineer at Ibuildings, always busy with many cool clients. He's a long-time contributor to many open source projects.

Tags

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