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:
- 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. - 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. - 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. - 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.