SoC 2007 Project

Hello everyone, This year again my Google Summer of Code project is related to MDB2_Schema. Before I officially release it, I would like to open it for discussion if anyone is interested. It is a bit long, though. I am sorry if you get bored in the middle... 1) INTRODUCTION PEAR::MDB2_Schema enables users to maintain RDBMS independant schema files in XML that can be used to create, alter and drop database entities (also called as DDL: Data Definition Language). Reverse engineering database schemas from existing databases is also supported. MDB2_Schema version 0.7.0, which was supported by a Google SoC 2006 project, introduced a new XML syntax to handle data manipulation. The ability to insert data into a database was improved, plus it is now possible to update and delete records (also called as DML: Data Manipulation Language). 2) PROPOSED CHANGES It is now time to take MDB2_Schema one step higher: Foreign Keys support, which has been a feature frequently requested by PEAR users. The required XML additions has already been defined in conjunction with David Morse, a PEAR::DB_Table developer. The main goal is to keep compatibility between both classes. XML definition:

       <foreign>

          <name>constraint_name</name>?
          <field>field_name</field>+

          <references>
              <table>referenced_table_name</table>
              <field>referenced_field_id</field>*
          </references>

          <match>full|partial|simple</match>?
          <ondelete>cascade|setnull|setdefault|restrict|noaction</ondelete>?
          <onupdate>cascade|setnull|setdefault|restrict|noaction</onupdate>?

       </foreign>

Where the symbols denote: ? : optional + : at least one instance * : zero or more instances The referenced key is optional. If it is absent, it is assumed that the referenced key is the primary key of referenced table. The number and types of fields in the referenced key must match those of the foreign key. 3) GENERAL DIRECTIONS The first step to take is to update MDB2 XML documentation and validators, for instance DTDs and XSDs. Next, MDB2_Schema Parser should be able to parse such new elements and reserve a new spot for them in the database definition array. After we get capable of parse a database schema with foreign keys in handmade XML files, we would need to be able to physicaly create the new database. Also, in the oposite direction, we should be able to reverse engineer an existing database with such feature. Finally we should be able to detect foreign key changes between database versions and do the required uptades when requested. 4) REQUIRED CHANGES: MDB2 For the moment there is no notion of a foreign key in MDB2. The Reverse class can query a database for indices (primary, unique, and normal), but doesn't provide a method to obtain information about foreign key constraints. If we are going to add foreign keys to the XML schema, it might make sense to add some corresponding support to MDB2 at the same time. Changes in MDB2 are already being discussed and will be done in conjunction to its developer Lorenzo Alberton. For reverse engineering alone, we would need to define an interface in the common Reverse class for a getTableConstraintDefinition() method, and write a concrete implementation for some drivers. At the other side, the Manager module class will need modification on at least 3 existing methods:

       o createConstraint()
       o dropConstraint()
       o listTableConstraints()

We would also want to think through whether corresponding additions would be appropriate in the Manager drivers, and about whether to add some sort of flag that indicates whether foreign keys are supported or not. This feature would have to be less portable than others, since some databases (or MySQL with some engines) don't support foreign key constraints. 5) REQUIRED CHANGES: MDB2_SCHEMA Having MDB2 set, we are ready to start the aditions of the necessary MDB2 API calls in MDB2_Schema. Besides the Parser and the Writer module class, the following methods would also need some modification:

       o alterDatabaseIndexes()
       o compareTableIndexesDefinitions()
       o createTableIndexes()
       o getDefinitionFromDatabase()
       o updateDatabase()
       o verifyAlterDatabase()

6) THE CHALENGE (YES, THERE IS ALWAYS A BAD TRIP) Creating constraints when defining a new table can be relatively simple. The problem, however, appears when updating a database. Since foreign keys reference other tables/columns we need to manage the foreign keys on a certain order to keep database consistency. Those dependency issues can't be easily resolved automatically, though. 7) EXTRAS Before the FK implementation however, I would like to get MDB2_Schema ready to be released as a stable package. Some test units are lacking (comparedefinitions(), initializetable() and parsed definitions) and there is no end user documentation yet, that is, documentation on how to use the API.