SQLFairy schema translation

The SQLFairy project, which has one of the most ... unusual logos around, produces a nifty command-line tool called sqlt. This tool can map the structure of a database from one format to another, including SQL databases, Excel spreadsheets, comma-separated tables, etc. Here's the full list of formats it can read from ("Parsers") and write to ("Producers"):

Parsers: Access, DB2, DB2-Grammar, DBI, DBI-DB2, DBI-MySQL, DBI-Oracle, DBI-PostgreSQL, DBI-SQLServer, DBI-SQLite, DBI-Sybase, DBIx-Class, Excel, MySQL, Oracle, PostgreSQL, SQLServer, SQLite, Storable, Sybase, XML, XML-SQLFairy, YAML, xSV

Producers: ClassDBI, DB2, DBIx-Class-File, DiaUml, Diagram, Dumper, GraphViz, HTML, Latex, MySQL, Oracle, POD, PostgreSQL, SQLServer, SQLite, Storable, Sybase, TT-Base, TT-Table, TTSchema, XML, XML-SQLFairy, YAML

It translates the structure of data (in SQL: CREATE, ALTER) and not the data itself (in SQL: INSERT, UPDATE, DELETE). On Ubuntu/Debian systems, this tool is available in the "sqlfairy" package. Let's try a quick test on the following Excel spreadsheet (well, actually an OpenOffice spreadsheet, but saved in Excel format):


If we save this as sqlfairy.xls and run sqlt like this:

sqlt --from Excel sqlfairy.xls --to MySQL

we get:

--
-- Created by SQL::Translator::Producer::MySQL
-- Created on Sat May 2 14:30:57 2009
--
SET foreign_key_checks=0;

--
-- Table: `Accounts`
--
CREATE TABLE `Accounts` (
`Account` integer(3) NOT NULL DEFAULT '',
`First_Name` char(4) DEFAULT '',
`Last_Name` char(5) DEFAULT '',
`Balance` integer(4) DEFAULT '',
PRIMARY KEY (`Account`)
);

SET foreign_key_checks=1;

Not a bad start. If you're translating from a mysql database, you can either connect to it live using the DBI parser, or dump your database first like this:

mysqldump --user=USER --password=PASS DATABASE_NAME --lock-tables=false --no-data > dump.sql

And then convert it like this (here we convert to sqlfairy's own xml format):

sqlt --from MySQL dump.sql -t XML-SQLFairy > dump.xml