Re: Re: Foreign Keys in XML

Igor Feghali:

The status is that I am hoping someone will implement it :) However I have given lead of the MDB2_Schema package to Igor.

I am planning to begin FK support as soon as I get DML well established

    and major bugs fixed. Maybe I should play with the new XML Parser first, that will depend on how much time ill take to get it done and how urgent FK addition is. Definitely I will wait until we get a solid syntax for FKs before I start coding it.

The proposed DB_Table_Database class includes methods for reading and writing the database schema as XML. I'd like to adopt the MDB2 XML schema, but need foreign key support. It appears that the syntax for foreign keys has not yet been implemented in MDB2_Schema, but there is a proposed syntax on the todo list of the MDB2 home page, at http://oss.backendmedia.com/MDB2/ForeignKeys. For the moment, I've implemented that proposal, but I don't like it much. This is primarily because I think that foreign keys constraints are conceptually distinct from indices, and should be represented by a distinct element type. Lukas Smith posted some correspondence between us on this issue, in which I discussed how the proposed syntax could lead naturally to name clashes (http://news.php.net/php.pear.dev/44750). Here is an alternative proposal for the syntax for foreign keys, in which a foreign key would be represented by a <foreign> subelement of the referencing table: <foreign> <name>auth_user_id</name> <field>auth_user_id</field> <field>..</field> <references>

      <table>foo</table>
      <field>bar</field>
      <field>...</field>

</references> <on_update>cascade|restrict|set_null|set_default|no_action</on_update> <on_delete>cascade|restrict|set_null|set_default|no_action</on_delete> </foreign> The names of elements are chosen to mimic the SQL create table syntax, with all lower case letters and underscores replacing spaces. Multiple <field> elements would be used to represent multi-column keys. The <name> field would be optional, as would the <field> sub-element(s) of the <references> element, which represent the referenced key of the referenced table, and the <on_update> and <on_delete> elements. If the <references> subelement contains no fields, the reference is to the primary key of the referenced table. If present, the field(s) in the <references> subelement must match the number and types of the foreign key fields. Primary keys can be declared in the current MDB2 XML schema by declaring indices of type primary. ANSI SQL seems to require that a referenced key be unique, i.e., that it either be a primary key or that there be a unique constraint declared on that key in the referenced table. There is no logical requirement for a foreign/referencing key to be unique, but one will often declare an index on a foreign key for efficient joins. MySQL with InnoDB tables seems to require an index be declared on referencing as well as referenced keys, for efficiency, and will create an index on a foreign key automatically if it isn't explicitly declared. I've not been able to find any indication that this is true for PostgreSQL, which requires only that the referenced key be unique. I have no idea regarding other RDBMS's. The proposal is that the XML schema include separate <index> elements for any desired indices (which impose unique constraints if they are primary or unique indices) and <foreign> elements for foreign key references, even when an index and a reference are declared on the same key, as will often be the case. Manager classes for RDBMS's that do not internally implement foreign key constraints (MySQL ISAM and SQLite) would create the indices and ignore the foreign key constraints, but a php layer like DB_Table_Database would still be aware of the intended reference. Would the above syntax break any existing software? Any comments or suggested improvements? -David -- !-------------------------------------------------------------------! ! David Morse email: morse@cems.umn.edu ! ! Dept of Chem Eng & Mat Sci phone: (612)625-0167 ! ! University of Minnesota ! ! Minneapolis, MN 55455 ! !-------------------------------------------------------------------!