=head1 NAME
-SQL::Translator::Manual
+SQL::Translator::Manual - sqlfairy user manual
=head1 SYNOPSIS
=item * Parsers
-The parsers are responsible for reading the input files and describing
+The parsers are responsible for reading the input files and describing
them to the Schema object middleware.
=item * Producers
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.
+as they will be referenced later in this document.
=head1 SQLFAIRY SCRIPTS
=item * sqlt
This is the main interface for text-to-text translations, e.g.,
-converting a MySQL schema to Oracle.
+converting a MySQL schema to Oracle.
=item * sqlt-diagram
=item * sqlt-diff
This script will examine two schemas and report the SQL commands
-(ALTER, CREATE) needed to turn the first schema into the second.
+(ALTER, CREATE) needed to turn the first schema into the second.
=item * sqlt-dumper
$ 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 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
+ $ sqlt -f XML-SQLFairy -t Oracle foo.xml > foo-oracle.sql
=head1 SERIALIZING SCHEMAS
parsed schema if you need to perform repeated conversions. For
example, as part of a build process the author converts a MySQL schema
first to YAML, then to PostgreSQL, Oracle, SQLite and Sybase.
-Additionally, a variety of documention in HTML and images is produced.
+Additionally, a variety of documentation in HTML and images is produced.
This can be accomplished like so:
$ sqlt -f MySQL -t YAML schema-mysql.sql > schema.yaml
$ ...
SQLFairy has three serialization producers, none of which is superior
-to the other in their description of a schema.
+to the other in their description of a schema.
=over 4
=back
-=head1 AUTOMATED CODE-GENERATION
+=head1 AUTOMATED CODE-GENERATION
Given that so many applications interact with SQL databases, it's no
wonder that people have automated code to deal with this interaction.
[% END -%]
[% END %]
-And the process it like so:
+And then process it like so:
$ sqlt -f YAML -t TTSchema --template schema.tt foo.yaml
$ 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
+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.
+will likely need to be corrected for the target database.
=head1 A UNIFIED GRAPHICAL INTERFACE
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
+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;
Executing this script produces the following:
- $ ./my-producer.pl
+ $ ./my-producer.pl
Table = foo
Table = bar
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 $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 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 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