=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 is a non-SQL represenation of relational schemas namely
+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.
+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.
$ 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 )
$ 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
+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
projects / dbsrgits/SQL-Translator.git / blobdiff