Saturday, May 2, 2009

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

No comments: