It's been all systems go at Openads central this year. One of the big jobs we've been taking on is dealing with our database schema. Managing database schemata is actually a tough job, if you want to do it in a agile way. (No one actaully designs their database schema up front any more, right?) There are several great books on the subject, though, and based on those ideas, we've produced a white paper outlining a system we're writing for managing database schemata for PHP, in the same vein as Rail's Migrations.
As part of this project, we've decided to use PEAR::MDB2 and PEAR::MDB2_Schema to manage the jobs of reading in our existing database schemata from existing databases, to then be stored in XML files, The same two PEAR packages will then be used to prepare the SQL DDL/DML commands required to create new databases when installing Openads, or to modify an existing Openads database when upgrading. Of course, the DDL/DML created will all be based on XML schemata files.
If you've ever used PEAR::MDB2, you may be aware that it works by storing database schemata information in an "internal" MDB2 datatype. This datatype needs to be converted into database-specific commands when talking to a database, obviously. But if you're reverse engineering a schema from an existing database, you also need to be able to convert an existing database "nativetype" into the appropriate MDB2 datatype.
However, this is where we've run into a few issues.
