$ sqlt -f xSV --fs ',' -t SQLite foo.csv > foo-sqlite.sql
-Additionally, there are non-SQL represenations of relational schemas
-such as XML and XMI. Currently the XMI support in SQLFairy is
-experimental and not released. 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.
+Additionally, there is a non-SQL represenation 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:
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 end result may result in many crossed lines showing the
-foreign key relationships. Please read the documentation of the
-"sqlt-diagram" script for all the options available to this producer.
+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.
=item * GraphViz
[% END -%]
[% END %]
-And the process it like so:
+And then process it like so:
$ sqlt -f YAML -t TTSchema --template schema.tt foo.yaml
For more information on Template Toolkit, please install the
"Template" module and read the POD.
+=head1 FINDING THE DIFFERENCES BETWEEN TWO SCHEMAS
+
+As mentioned above, the "sqlt-diff" schema examines two schemas and
+creates SQL schema modification statements that can be used to
+transform the first schema into the second. The flag syntax is
+somewhat quirky:
+
+ $ 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.
+
+=head1 A UNIFIED GRAPHICAL INTERFACE
+
+Seeing all the above options and scripts, you may be pining for a
+single, graphical interface to handle all these transformations and
+choices. This is exactly what the "sqlt.cgi" script provides. Simply
+drop this script into your web server's CGI directory and enable the
+execute bit and you can point your web browser to an HTML form which
+provides a simple interface to all the SQLFairy parsers and producers.
+
=head1 PLUGIN YOUR OWN PARSERS AND PRODUCERS
Now that you have seen how the parsers and producers interact via the
Schema objects, you may wish to create your own versions to plugin.
+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
+ouput. 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!
+
+=head1 PLUGIN TEMPLATE TOOLKIT PRODUCERS
+
+You may find that the TTSchema producer doesn't give you enough control over
+templating and you want to play with the Template config or add you own
+variables. Or maybe you just have a really good template you want to submit to
+SQLFairy :) If so, the SQL::Translator::Producer::TT::Base producer may be
+just for you! Instead of working like a normal producer it provides a base
+class so you can cheaply build new producer modules based on templates.
+
+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 F<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 F<Custom/Foo.pm> is in our @INC
+path):
+
+ $ sqlt -f YAML -t Custom-Foo foo.yaml
+
+The template gets variables of C<schema> and C<translator> to use in building
+its output. You also get a number of methods you can override to hook into the
+template generation.
+
+B<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 L<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 B<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 usefull 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 L<SQL::Translator::Producer::TT::Base> for more details.
+
=head1 AUTHOR
Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
projects / dbsrgits/SQL-Translator.git / blobdiff