1SQL::Translator::ManualU(s3e)r Contributed Perl DocumentaStQiLo:n:Translator::Manual(3)
2
6 SQL::Translator::Manual
7
9 SQL::Translator (AKA "SQLFairy") is a collection of modules for trans‐
10 forming (mainly) SQL DDL files into a variety of other formats, includ‐
11 ing other SQL dialects, documentation, images, and code. In this man‐
12 ual, we will attempt to address how to use SQLFairy for common tasks.
13 For a lower-level discussion of how the code works, please read the
14 documentation for SQL::Translator.
15 It may prove helpful to have a general understanding of the SQLFairy
17 code before continuing. The code can be broken into three conceptual
18 groupings:
19 * Parsers
21 The parsers are responsible for reading the input files and
22 describing them to the Schema object middleware.
23 * Producers
25 The producers create the output as described by the Schema middle‐
26 ware.
27 * Schema objects
29 The Schema objects bridge the communication between the Parsers and
30 Producers by representing any parsed file through a standard set of
31 generic objects to represent concepts like Tables, Fields (col‐
32 umns), Indices, Constraints, etc.
33 It's not necessary to understand how to write or manipulate any of
35 these for most common tasks, but you should aware of the concepts as
36 they will be referenced later in this document.
37
39 Most common tasks can be accomplished through the use of the script
40 interfaces to the SQL::Translator code. All SQLFairy scripts begin
41 with "sqlt." Here are the scripts and a description of what they each
42 do:
43 * sqlt
45 This is the main interface for text-to-text translations, e.g.,
46 converting a MySQL schema to Oracle.
47 * sqlt-diagram
49 This is a tailored interface for the Diagram producer and its many
50 myriad options.
51 * sqlt-diff
53 This script will examine two schemas and report the SQL commands
54 (ALTER, CREATE) needed to turn the first schema into the second.
55 * sqlt-dumper
57 This script generates a Perl script that can be used to connect to
58 a database and dump the data in each table in different formats,
59 similar to the "mysqldump" program.
60 * sqlt-graph
62 This is an interface to the GraphViz visualization tool and its
63 myriad options.
64 * sqlt.cgi
66 This is a CGI script that presents an HTML form for uploading or
67 pasting a schema and choosing an output and the output options.
68 To read the full documentation for each script, use "perldoc" (or exe‐
70 cute any of the command-line scripts with the "--help" flag).
71
73 Probably the most common task SQLFairy is used for is to convert one
74 dialect of SQL to another. If you have a text description of an SQL
75 database (AKA a "DDL" -- "Data Definition Language"), then you should
76 use the "sqlt" script with switches to indicate the parser and producer
77 and the name of the text file as the final argument. For example, to
78 convert the "foo.sql" MySQL schema to a version suitable for Post‐
79 greSQL, you would do the following:
80 $ sqlt -f MySQL -t PostgreSQL foo.sql > foo-pg.sql
82 The "from" and "to" options are case-sensitive and must match exactly
84 the names of the Parser and Producer classes in SQL::Translator. For a
85 complete listing of your options, execute "sqlt" with the "--list"
86 flag.
87
89 It is possible to extract some schemas directly from the database with‐
90 out parsing a text file (the "foo.sql" in the above example). This can
91 prove significantly faster than parsing a text file. To do this, use
92 the "DBI" parser and provide the necessary arguments to connect to the
93 database and indicate the producer class, like so:
94 $ sqlt -f DBI --dsn dbi:mysql:FOO --db-user guest \
96 --db-password p4ssw0rd -t PostgreSQL > foo
97 The "--list" option to "sqlt" will show the databases supported by DBI
99 parsers.
100
102 Certain structured document formats can be easily thought of as tables.
103 SQLFairy can parse Microsoft Excel spreadsheets and arbitrarily delim‐
104 ited text files just as if they were schemas which contained only one
105 table definition. The column names are normalized to something sane
106 for most databases (whitespace is converted to underscores and non-word
107 characters are removed), and the data in each field is scanned to
108 determine the appropriate data type (character, integer, or float) and
109 size. For instance, to convert a comma-separated file to an SQLite
110 database, do the following:
111 $ sqlt -f xSV --fs ',' -t SQLite foo.csv > foo-sqlite.sql
113 Additionally, there are non-SQL represenations of relational schemas
115 such as XML and XMI. Currently the XMI support in SQLFairy is experi‐
116 mental and not released. Additionally, the only XML supported is our
117 own version; however, it would be fairly easy to add an XML parser for
118 something like the TorqueDB (http://db.apache.org/torque/) project.
119 The actual parsing of XML should be trivial given the number of XML
120 parsers available, so all that would be left would be to map the spe‐
121 cific concepts in the source file to the Schema objects in SQLFairy.
122 To convert a schema in SQLFairy's XML dialect to Oracle, do the follow‐
124 ing:
125 $ sqlt -f XML-SQLFairy -t Oracle foo.xml > foo-oracle.sql
127
129 Parsing a schema is generally the most computationally expensive opera‐
130 tion performed by SQLFairy, so it may behoove you to serialize a parsed
131 schema if you need to perform repeated conversions. For example, as
132 part of a build process the author converts a MySQL schema first to
133 YAML, then to PostgreSQL, Oracle, SQLite and Sybase. Additionally, a
134 variety of documention in HTML and images is produced. This can be
135 accomplished like so:
136 $ sqlt -f MySQL -t YAML schema-mysql.sql > schema.yaml
138 $ sqlt -f YAML -t Oracle schema.yaml > schema-oracle.sql
139 $ sqlt -f YAML -t PostgreSQL schema.yaml > schema-postgresql.sql
140 $ ...
141 SQLFairy has three serialization producers, none of which is superior
143 to the other in their description of a schema.
144 * XML-SQLFairy
146 This is the aforementioned XML format. It is essentially a direct
147 mapping of the Schema objects into XML. This can also provide a
148 very convenient bridge to describing a schema to a non-Perl appli‐
149 cation. Providing a producer argument to "sqlt" of just "XML" will
150 default to using "XML-SQLFairy."
151 * Storable
153 This producer stores the Schema object using Perl's Storable.pm
154 module available on CPAN.
155 * YAML
157 This producer serialized the Schema object with the very readable
158 structured data format of YAML (http://www.yaml.org/). Earlier
159 examples show serializing to YAML.
160
162 The visualization tools in SQLFairy can graphically represent the
163 tables, fields, datatypes and sizes, constraints, and foreign key rela‐
164 tionships in a very compact and intuitive format. This can be very
165 beneficial in understanding and document large or small schemas. Two
166 producers in SQLFairy will create pseudo-E/R (entity-relationship) dia‐
167 grams:
168 * Diagram
170 The first visualization tool in SQLFairy, this producer uses libgd
171 to draw a picture of the schema. The tables are evenly distributed
172 in definition order running in columns (i.e., no graphing algo‐
173 rithms are used), so the many of the lines showing the foreign key
174 relationships may cross over each other and the table boxes.
175 Please read the documentation of the "sqlt-diagram" script for all
176 the options available to this producer.
177 * GraphViz
179 The layout of the GraphViz producer is far superior to the Diagram
180 producer as it uses the Graphviz binary from Bell Labs to create
181 very professional-looking graphs. There are several different lay‐
182 out algorithms and node shapes available. Please see the documen‐
183 tation of the "sqlt-graph" script for more information.
184
186 Given that so many applications interact with SQL databases, it's no
187 wonder that people have automated code to deal with this interaction.
188 Class::DBI from CPAN is one such module that allows a developer to
189 describe the relationships between tables and fields in class declara‐
190 tions and then generates all the SQL to interact (SELECT, UPDATE,
191 DELETE, INSERT statements) at runtime. Obviously, the schema already
192 describes itself, so it only makes sense that you should be able to
193 generate this kind of code directly from the schema. The "ClassDBI"
194 producer in SQLFairy does just this, creating a Perl module that inher‐
195 its from Class::DBI and sets up most of the code needed to interact
196 with the database. Here is an example of how to do this:
197 $ sqlt -f MySQL -t ClassDBI foo.sql > Foo.pm
199 Then simply edit Foo.pm as needed and include it in your code.
201
203 The Dumper producer creates a Perl script that can select the fields in
204 each table and then create "INSERT" statements for each record in the
205 database similar to the output generated by MySQL's "mysqldump" pro‐
206 gram:
207 $ sqlt -f YAML -t Dumper --dumper-db-user guest \
209 > --dumper-db-pass p4ssw0rd --dumper-dsn dbi:mysql:FOO \
210 > foo.yaml > foo-dumper.pl
211 And then execute the resulting script to dump the data:
213 $ chmod +x foo-dumper.pl
215 $ ./foo-dumper.pl > foo-data.sql
216 The dumper script also has a number of options available. Execute the
218 script with the "--help" flag to read about them.
219
221 SQLFairy offers two producers to help document schemas:
222 * HTML
224 This producer creates a single HTML document which uses HTML for‐
225 matting to describe the Schema objects and to create hyperlinks on
226 foreign key relationships. This can be a surprisingly useful docu‐
227 mentation aid as it creates a very readable format that allows one
228 to jump easily to specific tables and fields. It's also possible
229 to plugin your own CSS to further control the presentation of the
230 HTML.
231 * POD
233 This is arguably not that useful of a producer by itself, but the
234 number of POD-conversion tools could be used to further transform
235 the POD into something more interesting. The schema is basically
236 represented in POD sections where tables are broken down into
237 fields, indices, constraints, foreign keys, etc.
238
240 All of the producers which create text output could have been coded
241 using a templating system to mix in the dynamic output with static
242 text. CPAN offers several diverse templating systems, but few are as
243 powerful as Template Toolkit (http://www.template-toolkit.org/). You
244 can easily create your own producer without writing any Perl code at
245 all simply by writing a template using Template Toolkit's syntax. The
246 template will be passed a reference to the Schema object briefly
247 described at the beginning of this document and mentioned many times
248 throughout. For example, you could create a template that simply
249 prints the name of each table and field that looks like this:
250 # file: schema.tt
252 [% FOREACH table IN schema.get_tables %]
253 Table: [% table.name %]
254 Fields:
255 [% FOREACH field IN table.get_fields -%]
256 [% field.name %]
257 [% END -%]
258 [% END %]
259 And then process it like so:
261 $ sqlt -f YAML -t TTSchema --template schema.tt foo.yaml
263 To create output like this:
265 Table: foo
267 Fields:
268 foo_id
269 foo_name
270 For more information on Template Toolkit, please install the "Template"
272 module and read the POD.
273
275 As mentioned above, the "sqlt-diff" schema examines two schemas and
276 creates SQL schema modification statements that can be used to trans‐
277 form the first schema into the second. The flag syntax is somewhat
278 quirky:
279 $ sqlt-diff foo-v1.sql=MySQL foo-v2.sql=Oracle > diff.sql
281 As demonstrated, the schemas need not even be from the same vendor,
283 though this is likely to produce some spurious results as datatypes are
284 not currently viewed equivalent unless they match exactly, even if they
285 would be converted to the same. For example, MySQL's "integer" data
286 type would be converted to Oracle's "number," but the differ isn't
287 quite smart enough yet to figure this out. Also, as the SQL to ALTER a
288 field definition varies from database vendor to vendor, these state‐
289 ments are made using just the keyword "CHANGE" and will likely need to
290 be corrected for the target database.
291
293 Seeing all the above options and scripts, you may be pining for a sin‐
294 gle, graphical interface to handle all these transformations and
295 choices. This is exactly what the "sqlt.cgi" script provides. Simply
296 drop this script into your web server's CGI directory and enable the
297 execute bit and you can point your web browser to an HTML form which
298 provides a simple interface to all the SQLFairy parsers and producers.
299
301 Now that you have seen how the parsers and producers interact via the
302 Schema objects, you may wish to create your own versions to plugin.
303 Producers are probably the easier concept to grok, so let's cover that
305 first. By far the easiest way to create custom output is to use the
306 TTSchema producer in conjunction with a Template Toolkit template as
307 described earlier. However, you can also easily pass a reference to a
308 subroutine that SQL::Translator can call for the production of the
309 ouput. This subroutine will be passed a single argument of the
310 SQL::Translator object which you can use to access the Schema objects.
311 Please read the POD for SQL::Translator and SQL::Translator::Schema to
312 learn the methods you can call. Here is a very simple example:
313 #!/usr/bin/perl
315 use strict;
317 use SQL::Translator;
318 my $input = q[
320 create table foo (
321 foo_id int not null default '0' primary key,
322 foo_name varchar(30) not null default ''
323 );
324 create table bar (
326 bar_id int not null default '0' primary key,
327 bar_value varchar(100) not null default ''
328 );
329 ];
330 my $t = SQL::Translator->new;
332 $t->parser('MySQL') or die $t->error;
333 $t->producer( \&produce ) or die $t->error;
334 my $output = $t->translate( \$input ) or die $t->error;
335 print $output;
336 sub produce {
338 my $tr = shift;
339 my $schema = $tr->schema;
340 my $output = '';
341 for my $t ( $schema->get_tables ) {
342 $output .= join('', "Table = ", $t->name, "\n");
343 }
344 return $output;
345 }
346 Executing this script produces the following:
348 $ ./my-producer.pl
350 Table = foo
351 Table = bar
352 A custom parser will be passed two arguments: the SQL::Translator
354 object and the data to be parsed. In this example, the schema will be
355 represented in a simple text format. Each line is a table definition
356 where the fields are separated by colons. The first field is the table
357 name and the following fields are column definitions where the column
358 name, data type and size are separated by spaces. The specifics of the
359 example are unimportant -- what is being demonstrated is that you have
360 to decide how to parse the incoming data and then map the concepts in
361 the data to the Schema object.
362 #!/usr/bin/perl
364 use strict;
366 use SQL::Translator;
367 my $input =
369 "foo:foo_id int 11:foo_name varchar 30\n" .
370 "bar:bar_id int 11:bar_value varchar 30"
371 ;
372 my $t = SQL::Translator->new;
374 $t->parser( \&parser ) or die $t->error;
375 $t->producer('Oracle') or die $t->error;
376 my $output = $t->translate( \$input ) or die $t->error;
377 print $output;
378 sub parser {
380 my ( $tr, $data ) = @_;
381 my $schema = $tr->schema;
382 for my $line ( split( /\n/, $data ) ) {
384 my ( $table_name, @fields ) = split( /:/, $line );
385 my $table = $schema->add_table( name => $table_name )
386 or die $schema->error;
387 for ( @fields ) {
388 my ( $f_name, $type, $size ) = split;
389 $table->add_field(
390 name => $f_name,
391 data_type => $type,
392 size => $size,
393 ) or die $table->error;
394 }
395 }
396 return 1;
398 }
399 And here is the output produced by this script:
401 --
403 -- Created by SQL::Translator::Producer::Oracle
404 -- Created on Wed Mar 31 15:43:30 2004
405 --
406 --
407 -- Table: foo
408 --
409 CREATE TABLE foo (
411 foo_id number(11),
412 foo_name varchar2(30)
413 );
414 --
416 -- Table: bar
417 --
418 CREATE TABLE bar (
420 bar_id number(11),
421 bar_value varchar2(30)
422 );
423 If you create a useful parser or producer, you are encouraged to submit
425 your work to the SQLFairy project!
426
428 You may find that the TTSchema producer doesn't give you enough control
429 over templating and you want to play with the Template config or add
430 you own variables. Or maybe you just have a really good template you
431 want to submit to SQLFairy :) If so, the SQL::Translator::Pro‐
432 ducer::TT::Base producer may be just for you! Instead of working like a
433 normal producer it provides a base class so you can cheaply build new
434 producer modules based on templates.
435 It's simplest use is when we just want to put a single template in its
437 own module. So to create a Foo producer we create a Custom/Foo.pm file
438 as follows, putting our template in the __DATA__ section.
439 package Custom::Foo.pm;
441 use base qw/SQL::Translator::Producer::TT::Base/;
442 # Use our new class as the producer
443 sub produce { return __PACKAGE__->new( translator => shift )->run; };
444 __DATA__
446 [% FOREACH table IN schema.get_tables %]
447 Table: [% table.name %]
448 Fields:
449 [% FOREACH field IN table.get_fields -%]
450 [% field.name %]
451 [% END -%]
452 [% END %]
453 For that we get a producer called Custom::Foo that we can now call like
455 a normal producer (as long as the directory with Custom/Foo.pm is in
456 our @INC path):
457 $ sqlt -f YAML -t Custom-Foo foo.yaml
459 The template gets variables of "schema" and "translator" to use in
461 building its output. You also get a number of methods you can override
462 to hook into the template generation.
463 tt_config Allows you to set the config options used by the Template
465 object. The Template Toolkit provides a huge number of options which
466 allow you to do all sorts of magic (See Template::Manual::Config for
467 details). This method provides a hook into them by returning a hash of
468 options for the Template. e.g. Say you want to use the INTERPOLATE
469 option to save some typing in your template;
470 sub tt_config { ( INTERPOLATE => 1 ); }
472 Another common use for this is to add you own filters to the template:
474 sub tt_config {(
476 INTERPOLATE => 1,
477 FILTERS => { foo_filter => \&foo_filter, }
478 );}
479 Another common extension is adding your own template variables. This is
481 done with tt_vars:
482 sub tt_vars { ( foo => "bar" ); }
484 What about using template files instead of DATA sections? You can
486 already - if you give a template on the command line your new producer
487 will use that instead of reading the DATA section:
488 $ sqlt -f YAML -t Custom-Foo --template foo.tt foo.yaml
490 This is usefull as you can set up a producer that adds a set of filters
492 and variables that you can then use in templates given on the command
493 line. (There is also a tt_schema method to over ride if you need even
494 finer control over the source of your template). Note that if you leave
495 out the DATA section all together then your producer will require a
496 template file name to be given.
497 See SQL::Translator::Producer::TT::Base for more details.
499
501 Ken Y. Clark <kclark@cpan.org>.
502perl v5.8.8 2007-10-24 SQL::Translator::Manual(3)