Really nice tool, but found some problem while compare existing Database schema in MySQL and schema defined by XML SQLFairy using SQL::Translator::Diff.
The problem is that SQL::Translator::Parser::MySQL parses default field's value NULL as is, but SQL::Translator::Parser::XML for default NULL value sets undef (see cpan.uwinnipeg.ca/htdocs/SQL-Translat....
This makes SQL::Translator ::Diff to generate unnecessary ALTERs for nullable fields with default value NULL.
Also known as "sql-fairy", SQL::Translator reads and writes many, many dialects of SQL and translates between. As such, this is profoundly useful. I use it to write database schemas in near-ANSI syntax and then translate that to 1) MySQL (which has a peculiar foreign key syntax), 2) SQLite and 3) DBIx-Class code. This saves me a ton of duplication and coordination.
Even better, there is SQL::Translator::Diff which lets you compare a schema.sql to the schema behind a live DBI connection and outputs CREATE, DROP and ALTER commands that you can run. I use this to generate the SQL to update servers to newer schemas when I upgrade applications. It's a little slow to run the diff, but it's much more convenient than keeping track of what version of the database schema is running on what dev machine.
I also love the included 'sqlt-graph' tool which can generate a PNG, SVG, etc. representation of your schema. This is profoundly useful when discussing the data relations with developers/users who are not SQL-fluent.
Although the version number is only '0.09000', this package is definitely production-ready. I've been using it continuously for almost two years now.
This module is a wonderful work!
However, I hope the author will provide more args to the GraphViz producer, so I can get more control over the tyle of the pictures generated. The default settings lead to graphs with poor appearance.
While working on Handel, I've come to the point where I need a sane way to create SQL scripts for multiple database. SQL::Translater is making my life more sane in this area. Once I define the schema, I can just have it toss out create scripts for SQLite/MySQL/Postgres and go on with life. This pays off even more when I want to define foreign key relationships, but only some platforms support them; or support them in different ways.
On the flip side, I wish the docs were a little more verbose about the formats, especially the YAML producer.
An outstanding product--I needed to create audit tables with related functions and triggers for a large (~100 tables) PostgreSQL schema, and after spending 1/2 day writing a template, I can get the sql file for the audit tables from SQL::Translator.
Although I'm biased (I work on this project), I think this is a great
piece of software: Very useful, very featureful, and fairly complete.
This distribution is really superb in terms of the abstraction it lets you do. I use it to create my schema in OO, and generate sane SQL from that. This I pipe to whatever database I'm using. The application level is completely oblivious of all this detail.
It just works, and it does it well.
There are three quirks though.
Speed. Parsing schemas and stuff is slow. That's what you get for using Parse::RecDescent. It's well worth it though.
The documentation is occasionally sparse, and you have to dig hard and long to find silly little bits, when you're trying to pick it up. The documentation is scarecely necessary after that, though.
Lastly, the installation process is tedious, as there are lots of extensions that, IMHO, should be packaged seperately, to allow an easier installation. Not everyone wants GD diagrams, or can even install GD...
I think this is a very good thing which has to be done perfectly and is surely worth much effort.
This is an update to an old review:
Works great now, top notch!
brilliant concept, I need this in very many ways., I cannot however get it to work. How do I use the DBI-MySQL parser? The filename seems mandatory, how do I specify the database and host?
Can someone PLEASE enter some examples in the synopses, manpages etc. It is ironic with *all* these man-pages, people seem to have cleverly avoided giving a single working example, :( sigh
I would like to do: sqlt-diagram -f DBI-MySQL 'localhost:RGW'
or something like it
In order to use this module to convert from one database schema to another, you need to install the C library for GD graphics, Spreadsheet::WriteExcel, and download, compile and install the Graphviz C programs, which I think is somewhat overkill. Go look at DBIx::DBSchema instead.
I wish there were a SQL::Translator::Manual as promised in the SQL::Translator POD, but this module has saved me much time and effort.
4 hidden unhelpful reviews