Translate SQL Syntax Between Databases

SQL::Translator is an interesting Perl module that, among other things, can convert database table definitions to and from several DB platforms. Essentially, this could allow you to write and maintain your table definition code for a single platform, say MySQL, and then use Translator to output table structure into Oracle, Sybase, or PostgreSQL dialects.

Manipulation of data, such as SELECT, INSERT, UPDATE and DELETE are not yet supported, so you’re still on your own there if you’re writing platform agnostic code. That said, this is an incredibly useful tool. Just consider this example that Chris Dolan posted on use Perl:

MySQL understands this syntax:

create table book (
id int,
author_id int,
FOREIGN KEY fk_author_id (author_id) REFERENCES author (id)
) TYPE=InnoDB;

but not this nicer syntax (it silently ignores the “references” clause):

create table book (
id int,
author_id int references author (id),
) TYPE=InnoDB;

Perl to the rescue! I can write my schema in the latter syntax and use SQL::Translator to rewrite into the supported syntax.

References:

• MySQL foreign key syntax translator (4 line perl script!)
• SQL::Translator on CPAN