Today, I discovered SQL::Translator, which seems to have some very interesting use cases. Basically, it is a perl module for translating a database schema from one of a number of formats and turning it into another format. Parsers include:

• Live querying of DB2, MySQL, DBI-PostgreSQL, SQLite and Sybase databases
• Access
• Excel
• SQL for DB2, MySQL, Oracle, PostgreSQL, SQLite and Sybase
• Storable
• XML
• YAML

Output formats include:

• Class::DBI
• SQL for MySQL, Oracle, PostgreSQL, SQLServer, SQLite and Sybase
• Storable, XML and YAML
• POD, Diagram, GraphViz and HTML

Several things spring to mind with this:

1. Defining your Schema in XML and using SQL::Translator to convert it into SQL for several databases and a set of classes for Class::DBI, which would make your application immediately target any of the supported databases.
2. Documenting an existing database for which you've lost existing documentation by pointing it at a running database instance and outputting HTML page and, thanks to the Diagram output module, visual representation of the structure.
3. Convert one database from product to another. Point it at a MySQL database and generate SQL for postgresql. If you generated some Class::DBI stuff you could possibly quickly write a script to copy data too.
4. Using the sqlt-diff script, compare you current SQL to what is running on the database and generate a SQL script to upgrade the database structure using ALTER TABLE etc. Presumably you'd need to convert any data yourself, but is still a time saver for large databases.

I'm sure other people could think of some interesting uses for this. Having looked at the Class::DBI stuff, I think it could do with some improvements. I can't see a way to set the class names, although I haven't spent that much time looking and it insists on having all the classes in one file. Also the XML and YAML formats generated are rather verbose and I haven't looked to see how much I could cut them down to use as the source definition. I suspect that I can make it a lot shorter and rely on sensible defaults.

My initial reason for wanting to use SQL::Translator is that Class::DBI::Pg has a large start up time and isn't really suitable for CGI use if you have a complex database. This might be mitigated by using mod_perl, but in the mean time I was hoping I could speed up startup by telling Class::DBI my column names, rather than it querying the database. SQL::Translator should allow me to save duplicating the database structure, whilst allowing me to support multiple backend databases. If I get this working, I'll write up a short HOWTO.