Lorenzo Alberton

London, UK   ·   Contact me   ·  

« Articles

PEAR MDB2 updates (FOREIGN KEY support!)

PHP, PEAR, Database   ·   17 June 2007 23:09

Abstract: 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.

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:



Enjoy!

Back

Related articles

Latest articles

Filter articles by topic

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

Back