Earlier I talked about how I'm writing my own abstraction layer for databases - one that has a pdo-like api but also abstracts out things like sql differences, limits, sequences, et al. Well, I'd been watching the development of mdb2 in pear and decided not to go that way...wow it's big. But one of the things I DID like about metabase and company was the xml definition of a database. So I expanded on that idea.
First of all, I had to write my own xml definition because I neede more control over that xml file, but instead of using a DTD I did an XSD file. What is XSD? Xml Schema Definition file. These are the "sucessors" to DTD and are really a lot of fun to do. First of all, an xsd file has a LOT more control than a DTD. You can do namespacing and all sorts of fancy things. For example, you can do nodes that are only allowed to have an enumerated list of values - I used that when defining a fields type to limit choices to the "basic" types I decided on. But I ended up with a pretty basic and nice design for the xml that can hold both structural information and/or insert/update/delete information.
What's so nifty about using an xml file to control how to define a database's structure or dump db information? Well, I've written a manager class that works like this - you can read in an xml file. The file will be compared with the current database and alter statements will automagically be created. Or you can "reverse engineer" a database, and dump out an xml file with the structure and/or data. Then load that database into a totally different db backend, without ever writing a line of sql ;) the manager class also has generic methods for dumping straight sql, creating/dropping/altering dbs, sequences, tables, columns, keys, what have you. But the fun methods are the ones dealing with dumping a current database to xml, changing an sql file into xml, and comparing two xml files to create alter statements - there are also reverse methods - dumping a database to sql, changing an xml file to sql, and comparing two sql files to create alter statements, but the meat of the class is the "magicness" of it. For the module installs/upgrades, just dump an xml file of your database. If you've renamed any tables, you'll have to add the previous names in by hand, and drop it in. New installs will get the full xml file, upgrades will have upgrade statements magically created, doesn't matter what earlier version they had.
I use the new php5 dom functions to validate a loaded/created xml file against the xsd file and then shove it into a simplexml object for easy reading and iterating. I could leave it in dom, but dom to me has always been more useful for directly manipulating objects, I just want to read over the thing and translate it to the proper sql, or compare two objects to find the differences so I can write alter statements. I still wish simplexml had a better way to deal with attributes though. But the xsd file means I'll always know exactly what nodes can/should have attributes attached.
Well, I'm still working on this monstrosity, but when I'm finished I'll probably create a module for pff called dbmanager that can work with several database types and do phpmyadmin style management for any db with an driver and manager class.
Back to the grind!