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
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>.