=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
(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
$ ...
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
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 )