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