Re: The future of database packages in PEAR

I found the answer to another one of my questions about Doctrine in the user documentation. Doctrine does check that all values that are assigned to columns are of the right type, as well as (when appropriate) of the right length.( See http://www.phpdoctrine.com/documentation.php?index=7.2#7.2.2) Lukas, thanks for the thoughtful reply. I agree with all but the following: I wrote:

... how does Doctrine deal with enforcing foreign key integrity? ... Lukas replied:

I dont know this either. But the aim must be to do a maximum of integrity checking in the database. The main reason for this is not really performance, but the simple fact that it makes it easier for DBA's to work directly on the database and it also makes it easier to work in hereogenous environments with other languages.

You make a good argument for why it will often be better to let the database take care of foreign key and other integrity checks, particularly so in environments in which the php developer does not administer the database. I don't think this is an argument against inclusion of php emulation of the same checks and referentially triggered actions (like Doctrine's php implementation of cascacding deletes), as long as the php emulation can easily be turned off. If one of the goals of such a package is portability, the ideal should be to make it possible to write software that works on SQLite or MySQL with the default table engine (which do not support foreign key constraints) and on MS SQL server (which does), and have it behave the same way, with the same degree of referential integrity. The choice in Doctrine to explicitly include cascading deletes of dependent rows was, I think, a particularly sensible example: This is an automatic action that you might often want to include in a database that is implemented in MySQL or SQLite, which a MySQL/PHP programmer shouldn't have to re-implement. My thought in writing DB_Table_Database was simply that, once I wrote an emulation for one foreign key action or check that is part of ANSI SQL, why not just go ahead and implement them all, using more or less standard SQL names and behaviors, and let the package user turn the emulation on or off as desired. In the worst case, when emulation is left on for a database that supports foreign key constraints, there is no harm done except redundancy and a loss in performance. The other thing that would be necessary to allow full portability between databases that do support foreign key constraints and those that don't would be the use of standard error codes and exceptions in response to errors returned from the RDBMS. Truly portable error handling code would require that the same error code and exception be thrown whether a foreign key constraint violation is detected by the php layer or by the RDBMS. I don't know to what extent errors are standardized in MDB2. The PDO driver used by Doctrine does seem to use standard error messages ("PDO standardizes on using SQL-92 SQLSTATE error code strings; individual PDO drivers are responsible for mapping their native codes to the appropriate SQLSTATE codes." http://us2.php.net/pdo). It looks like this ought to make this level of portability possible. With sufficiently portable errors in place, one could set php emulation of foreign key checks and triggered actions on by default for MySQL and SQLite and off by default for more full-featured databases, and have the defaults yield fully portable behavior. If a MySQL user wanted to turn off php emulation of foreign key constraint checks for performance reasons, he still could, even while maintaining cascading deletes. I'm proposing this kind of portability as one more design goal of a "dream" package for portable interaction with a relational database. Doctrine is getting pretty close to this dream, so it seems like a good time to toss out the idea. regards, David -- !-------------------------------------------------------------------! ! David Morse email: morse@cems.umn.edu ! ! Dept of Chem Eng & Mat Sci phone: (612)625-0167 ! ! University of Minnesota ! ! Minneapolis, MN 55455 ! !-------------------------------------------------------------------!