NAME

SYNOPSIS

It may prove helpful to have a general understanding of the SQLFairy code before continuing. The code can be broken into three conceptual groupings:

• Parsers

  The parsers are responsible for reading the input files and describing them to the Schema object middleware.

• Producers

  The producers create the output as described by the Schema middleware.

• Schema objects

  The Schema objects bridge the communication between the Parsers and Producers by representing any parsed file through a standard set of generic objects to represent concepts like Tables, Fields (columns), Indices, Constraints, etc.

It's not necessary to understand how to write or manipulate any of these for most common tasks, but you should aware of the concepts as they will be referenced later in this document.

SQLFAIRY SCRIPTS

• sqlt

  This is the main interface for text-to-text translations, e.g., converting a MySQL schema to Oracle.

• sqlt-diagram

  This is a tailored interface for the Diagram producer and its many myriad options.

• sqlt-diff

  This script will examine two schemas and report the SQL commands (ALTER, CREATE) needed to turn the first schema into the second.

• sqlt-dumper

  This script generates a Perl script that can be used to connect to a database and dump the data in each table in different formats, similar to the "mysqldump" program.

• sqlt-graph

  This is an interface to the GraphViz visualization tool and its myriad options.

• sqlt.cgi

  This is a CGI script that presents an HTML form for uploading or pasting a schema and choosing an output and the output options.

To read the full documentation for each script, use "perldoc" (or execute any of the command-line scripts with the "--help" flag).

CONVERTING SQL DIALECTS

  $ sqlt -f MySQL -t PostgreSQL foo.sql > foo-pg.sql

The "from" and "to" options are case-sensitive and must match exactly the names of the Parser and Producer classes in SQL::Translator. For a complete listing of your options, execute "sqlt" with the "--list" flag.

EXTRACT SQL SCHEMAS DIRECTLY FROM DATABASE

  $ sqlt -f DBI --dsn dbi:mysql:FOO --db-user guest \
    --db-password p4ssw0rd -t PostgreSQL > foo

The "--list" option to "sqlt" will show the databases supported by DBI parsers.

HANDLING NON-SQL DATA

  $ sqlt -f xSV --fs ',' -t SQLite foo.csv > foo-sqlite.sql

Additionally, there is a non-SQL representation of relational schemas namely XML. Additionally, the only XML supported is our own version; however, it would be fairly easy to add an XML parser for something like the TorqueDB (http://db.apache.org/torque/) project. The actual parsing of XML should be trivial given the number of XML parsers available, so all that would be left would be to map the specific concepts in the source file to the Schema objects in SQLFairy.

To convert a schema in SQLFairy's XML dialect to Oracle, do the following:

  $ sqlt -f XML-SQLFairy -t Oracle foo.xml > foo-oracle.sql

SERIALIZING SCHEMAS

  $ sqlt -f MySQL -t YAML schema-mysql.sql > schema.yaml
  $ sqlt -f YAML -t Oracle schema.yaml > schema-oracle.sql
  $ sqlt -f YAML -t PostgreSQL schema.yaml > schema-postgresql.sql
  $ ...

SQLFairy has three serialization producers, none of which is superior to the other in their description of a schema.

• XML-SQLFairy

  This is the aforementioned XML format. It is essentially a direct mapping of the Schema objects into XML. This can also provide a very convenient bridge to describing a schema to a non-Perl application. Providing a producer argument to "sqlt" of just "XML" will default to using "XML-SQLFairy."

• Storable

  This producer stores the Schema object using Perl's Storable.pm module available on CPAN.

• YAML

  This producer serialized the Schema object with the very readable structured data format of YAML (http://www.yaml.org/). Earlier examples show serializing to YAML.

VISUALIZING SQL SCHEMAS

• Diagram

  The first visualization tool in SQLFairy, this producer uses libgd to draw a picture of the schema. The tables are evenly distributed in definition order running in columns (i.e., no graphing algorithms are used), so the many of the lines showing the foreign key relationships may cross over each other and the table boxes. Please read the documentation of the "sqlt-diagram" script for all the options available to this producer.

• GraphViz

  The layout of the GraphViz producer is far superior to the Diagram producer as it uses the Graphviz binary from Bell Labs to create very professional-looking graphs. There are several different layout algorithms and node shapes available. Please see the documentation of the "sqlt-graph" script for more information.

AUTOMATED CODE-GENERATION

  $ sqlt -f MySQL -t ClassDBI foo.sql > Foo.pm

Then simply edit Foo.pm as needed and include it in your code.

CREATING A DATA DUMPER SCRIPT

  $ sqlt -f YAML -t Dumper --dumper-db-user guest \
  > --dumper-db-pass p4ssw0rd --dumper-dsn dbi:mysql:FOO \
  > foo.yaml > foo-dumper.pl

And then execute the resulting script to dump the data:

  $ chmod +x foo-dumper.pl
  $ ./foo-dumper.pl > foo-data.sql

The dumper script also has a number of options available. Execute the script with the "--help" flag to read about them.

DOCUMENTING WITH SQL::TRANSLATOR

• HTML

  This producer creates a single HTML document which uses HTML formatting to describe the Schema objects and to create hyperlinks on foreign key relationships. This can be a surprisingly useful documentation aid as it creates a very readable format that allows one to jump easily to specific tables and fields. It's also possible to plugin your own CSS to further control the presentation of the HTML.

• POD

  This is arguably not that useful of a producer by itself, but the number of POD-conversion tools could be used to further transform the POD into something more interesting. The schema is basically represented in POD sections where tables are broken down into fields, indices, constraints, foreign keys, etc.

TEMPLATE-BASED MANIPULATION OF SCHEMA OBJECTS

  # file: schema.tt
  [% FOREACH table IN schema.get_tables %]
  Table: [% table.name %]
  Fields:
  [% FOREACH field IN table.get_fields -%]
    [% field.name %]
  [% END -%]
  [% END %]

And then process it like so:

  $ sqlt -f YAML -t TTSchema --template schema.tt foo.yaml

To create output like this:

  Table: foo
  Fields:
    foo_id
    foo_name

For more information on Template Toolkit, please install the "Template" module and read the POD.

FINDING THE DIFFERENCES BETWEEN TWO SCHEMAS

  $ sqlt-diff foo-v1.sql=MySQL foo-v2.sql=Oracle > diff.sql

As demonstrated, the schemas need not even be from the same vendor, though this is likely to produce some spurious results as datatypes are not currently viewed equivalent unless they match exactly, even if they would be converted to the same. For example, MySQL's "integer" data type would be converted to Oracle's "number," but the differ isn't quite smart enough yet to figure this out. Also, as the SQL to ALTER a field definition varies from database vendor to vendor, these statements are made using just the keyword "CHANGE" and will likely need to be corrected for the target database.

A UNIFIED GRAPHICAL INTERFACE

PLUGIN YOUR OWN PARSERS AND PRODUCERS

Producers are probably the easier concept to grok, so let's cover that first. By far the easiest way to create custom output is to use the TTSchema producer in conjunction with a Template Toolkit template as described earlier. However, you can also easily pass a reference to a subroutine that SQL::Translator can call for the production of the output. This subroutine will be passed a single argument of the SQL::Translator object which you can use to access the Schema objects. Please read the POD for SQL::Translator and SQL::Translator::Schema to learn the methods you can call. Here is a very simple example:

  #!/usr/bin/perl

  use strict;
  use SQL::Translator;

  my $input = q[
      create table foo (
          foo_id int not null default '0' primary key,
          foo_name varchar(30) not null default ''
      );

      create table bar (
          bar_id int not null default '0' primary key,
          bar_value varchar(100) not null default ''
      );
  ];

  my $t = SQL::Translator->new;
  $t->parser('MySQL') or die $t->error;
  $t->producer( \&produce ) or die $t->error;
  my $output = $t->translate( \$input ) or die $t->error;
  print $output;

  sub produce {
      my $tr     = shift;
      my $schema = $tr->schema;
      my $output = '';
      for my $t ( $schema->get_tables ) {
          $output .= join('', "Table = ", $t->name, "\n");
      }
      return $output;
  }

Executing this script produces the following:

  $ ./my-producer.pl
  Table = foo
  Table = bar

A custom parser will be passed two arguments: the SQL::Translator object and the data to be parsed. In this example, the schema will be represented in a simple text format. Each line is a table definition where the fields are separated by colons. The first field is the table name and the following fields are column definitions where the column name, data type and size are separated by spaces. The specifics of the example are unimportant -- what is being demonstrated is that you have to decide how to parse the incoming data and then map the concepts in the data to the Schema object.

  #!/usr/bin/perl

  use strict;
  use SQL::Translator;

  my $input =
      "foo:foo_id int 11:foo_name varchar 30\n" .
      "bar:bar_id int 11:bar_value varchar 30"
  ;

  my $t = SQL::Translator->new;
  $t->parser( \&parser ) or die $t->error;
  $t->producer('Oracle') or die $t->error;
  my $output = $t->translate( \$input ) or die $t->error;
  print $output;

  sub parser {
      my ( $tr, $data ) = @_;
      my $schema = $tr->schema;

      for my $line ( split( /\n/, $data ) ) {
          my ( $table_name, @fields ) = split( /:/, $line );
          my $table = $schema->add_table( name => $table_name )
              or die $schema->error;
          for ( @fields ) {
              my ( $f_name, $type, $size ) = split;
              $table->add_field(
                  name      => $f_name,
                  data_type => $type,
                  size      => $size,
              ) or die $table->error;
          }
      }

      return 1;
  }

And here is the output produced by this script:

  --
  -- Created by SQL::Translator::Producer::Oracle
  -- Created on Wed Mar 31 15:43:30 2004
  --
  --
  -- Table: foo
  --

  CREATE TABLE foo (
    foo_id number(11),
    foo_name varchar2(30)
  );

  --
  -- Table: bar
  --

  CREATE TABLE bar (
    bar_id number(11),
    bar_value varchar2(30)
  );

If you create a useful parser or producer, you are encouraged to submit your work to the SQLFairy project!

PLUGIN TEMPLATE TOOLKIT PRODUCERS

It's simplest use is when we just want to put a single template in its own module. So to create a Foo producer we create a Custom/Foo.pm file as follows, putting our template in the __DATA__ section.

 package Custom::Foo.pm;
 use base qw/SQL::Translator::Producer::TT::Base/;
 # Use our new class as the producer
 sub produce { return __PACKAGE__->new( translator => shift )->run; };

 __DATA__
 [% FOREACH table IN schema.get_tables %]
 Table: [% table.name %]
 Fields:
 [% FOREACH field IN table.get_fields -%]
   [% field.name %]
 [% END -%]
 [% END %]

For that we get a producer called Custom::Foo that we can now call like a normal producer (as long as the directory with Custom/Foo.pm is in our @INC path):

 $ sqlt -f YAML -t Custom-Foo foo.yaml

The template gets variables of "schema" and "translator" to use in building its output. You also get a number of methods you can override to hook into the template generation.

tt_config Allows you to set the config options used by the Template object. The Template Toolkit provides a huge number of options which allow you to do all sorts of magic (See Template::Manual::Config for details). This method provides a hook into them by returning a hash of options for the Template. e.g. Say you want to use the INTERPOLATE option to save some typing in your template;

 sub tt_config { ( INTERPOLATE => 1 ); }

Another common use for this is to add you own filters to the template:

 sub tt_config {(
    INTERPOLATE => 1,
    FILTERS => { foo_filter => \&foo_filter, }
 );}

Another common extension is adding your own template variables. This is done with tt_vars:

 sub tt_vars { ( foo => "bar" ); }

What about using template files instead of DATA sections? You can already - if you give a template on the command line your new producer will use that instead of reading the DATA section:

 $ sqlt -f YAML -t Custom-Foo --template foo.tt foo.yaml

This is useful as you can set up a producer that adds a set of filters and variables that you can then use in templates given on the command line. (There is also a tt_schema method to over ride if you need even finer control over the source of your template). Note that if you leave out the DATA section all together then your producer will require a template file name to be given.

See SQL::Translator::Producer::TT::Base for more details.

AUTHOR