Re: Re: The future of database packages in PEAR

David Morse wrote:

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

Yes, the key is to use as much native functionality as possible. Integrity constraint checking should be done as close to the data storage as possible.

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.

The problem is that PDO does very little work to make database throw SQLSTATE error codes if they are not provided natively. As such there is still a need to do some of this in userland, or better yet help to move these fixes upstream to PDO itself.

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.

Sounds good to me. regards, Lukas