Lorenzo Alberton

At a glance

Some updates on recent MDB2 developement: FOREIGN KEY support in the Manager and Reverse modules, etc. Also: updated SQL technical articles on INFORMATION_SCHEMA for Firebird, PostgreSQL, SQL Server and Oracle.

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 updates (FOREIGN KEY support!)

I recently committed to CVS some changes to add full FOREIGN KEY constraints support to PEAR::MDB2, both in the Manager (createConstraint()) and in the Reverse module (getTableConstraintDefinition()). The constraint definition is translated to this array structure (some key names could change slightly before an official release, so they are in sync with MDB2_Schema):

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
        )
    )
    [references] => array(
        [table] => name
        [fields] => array(
            [field1name] => position //one entry for each referenced field
            [field2name] => position
            [field3name] => position
        )
    [deferrable] => true|false
    [initially_deferred] => true|false
    [on_update] => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION
    [on_delete] => CASCADE|RESTRICT|SET NULL|SET DEFAULT|NO ACTION
    [match] => SIMPLE|PARTIAL|FULL
);

The next step will be adding CHECK constraint support. If you feel brave enough, please fetch the CVS version of MDB2 and give it a go. And of course send me some feedback ;-)

Updated INFORMATION_SCHEMA tutorials

On a side note, I've updated my tutorials on INFORMATION_SCHEMA and system tables for Firebird/Interbase, PostgreSQL and SQL Server, and added one for Oracle:

• Interbase/Firebird INFORMATION_SCHEMA
• Oracle INFORMATION_SCHEMA
• PostgreSQL INFORMATION_SCHEMA
• SQL Server INFORMATION_SCHEMA

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