SQL::Translator(3) User Contributed Perl Documentation SQL::Translator(3)NAMESQL::Translator - manipulate structured data definitions (SQL and more)
SYNOPSIS
use SQL::Translator;
my $translator = SQL::Translator->new(
# Print debug info
debug => 1,
# Print Parse::RecDescent trace
trace => 0,
# Don't include comments in output
no_comments => 0,
# Print name mutations, conflicts
show_warnings => 0,
# Add "drop table" statements
add_drop_table => 1,
# to quote or not to quote, thats the question
quote_table_names => 1,
quote_field_names => 1,
# Validate schema object
validate => 1,
# Make all table names CAPS in producers which support this option
format_table_name => sub {my $tablename = shift; return uc($tablename)},
# Null-op formatting, only here for documentation's sake
format_package_name => sub {return shift},
format_fk_name => sub {return shift},
format_pk_name => sub {return shift},
);
my $output = $translator->translate(
from => 'MySQL',
to => 'Oracle',
# Or an arrayref of filenames, i.e. [ $file1, $file2, $file3 ]
filename => $file,
) or die $translator->error;
print $output;
DESCRIPTION
This documentation covers the API for SQL::Translator. For a more
general discussion of how to use the modules and scripts, please see
SQL::Translator::Manual.
SQL::Translator is a group of Perl modules that converts vendor-
specific SQL table definitions into other formats, such as other
vendor-specific SQL, ER diagrams, documentation (POD and HTML), XML,
and Class::DBI classes. The main focus of SQL::Translator is SQL, but
parsers exist for other structured data formats, including Excel
spreadsheets and arbitrarily delimited text files. Through the
separation of the code into parsers and producers with an object model
in between, it's possible to combine any parser with any producer, to
plug in custom parsers or producers, or to manipulate the parsed data
via the built-in object model. Presently only the definition parts of
SQL are handled (CREATE, ALTER), not the manipulation of data (INSERT,
UPDATE, DELETE).
CONSTRUCTOR
The constructor is called "new", and accepts a optional hash of
options. Valid options are:
· parser / from
· parser_args
· producer / to
· producer_args
· filters
· filename / file
· data
· debug
· add_drop_table
· quote_table_names
· quote_field_names
· no_comments
· trace
· validate
All options are, well, optional; these attributes can be set via
instance methods. Internally, they are; no (non-syntactical) advantage
is gained by passing options to the constructor.
METHODS
add_drop_table
Toggles whether or not to add "DROP TABLE" statements just before the
create definitions.
quote_table_names
Toggles whether or not to quote table names with " in DROP and CREATE
statements. The default (true) is to quote them.
quote_field_names
Toggles whether or not to quote field names with " in most statements.
The default (true), is to quote them.
no_comments
Toggles whether to print comments in the output. Accepts a true or
false value, returns the current value.
producer
The "producer" method is an accessor/mutator, used to retrieve or
define what subroutine is called to produce the output. A subroutine
defined as a producer will be invoked as a function (not a method) and
passed its container "SQL::Translator" instance, which it should call
the "schema" method on, to get the "SQL::Translator::Schema" generated
by the parser. It is expected that the function transform the schema
structure to a string. The "SQL::Translator" instance is also useful
for informational purposes; for example, the type of the parser can be
retrieved using the "parser_type" method, and the "error" and "debug"
methods can be called when needed.
When defining a producer, one of several things can be passed in: A
module name (e.g., "My::Groovy::Producer"), a module name relative to
the "SQL::Translator::Producer" namespace (e.g., "MySQL"), a module
name and function combination ("My::Groovy::Producer::transmogrify"),
or a reference to an anonymous subroutine. If a full module name is
passed in (for the purposes of this method, a string containing "::" is
considered to be a module name), it is treated as a package, and a
function called "produce" will be invoked: $modulename::produce. If
$modulename cannot be loaded, the final portion is stripped off and
treated as a function. In other words, if there is no file named
My/Groovy/Producer/transmogrify.pm, "SQL::Translator" will attempt to
load My/Groovy/Producer.pm and use "transmogrify" as the name of the
function, instead of the default "produce".
my $tr = SQL::Translator->new;
# This will invoke My::Groovy::Producer::produce($tr, $data)
$tr->producer("My::Groovy::Producer");
# This will invoke SQL::Translator::Producer::Sybase::produce($tr, $data)
$tr->producer("Sybase");
# This will invoke My::Groovy::Producer::transmogrify($tr, $data),
# assuming that My::Groovy::Producer::transmogrify is not a module
# on disk.
$tr->producer("My::Groovy::Producer::transmogrify");
# This will invoke the referenced subroutine directly, as
# $subref->($tr, $data);
$tr->producer(\&my_producer);
There is also a method named "producer_type", which is a string
containing the classname to which the above "produce" function belongs.
In the case of anonymous subroutines, this method returns the string
"CODE".
Finally, there is a method named "producer_args", which is both an
accessor and a mutator. Arbitrary data may be stored in name => value
pairs for the producer subroutine to access:
sub My::Random::producer {
my ($tr, $data) = @_;
my $pr_args = $tr->producer_args();
# $pr_args is a hashref.
Extra data passed to the "producer" method is passed to
"producer_args":
$tr->producer("xSV", delimiter => ',\s*');
# In SQL::Translator::Producer::xSV:
my $args = $tr->producer_args;
my $delimiter = $args->{'delimiter'}; # value is ,\s*
parser
The "parser" method defines or retrieves a subroutine that will be
called to perform the parsing. The basic idea is the same as that of
"producer" (see above), except the default subroutine name is "parse",
and will be invoked as "$module_name::parse($tr, $data)". Also, the
parser subroutine will be passed a string containing the entirety of
the data to be parsed.
# Invokes SQL::Translator::Parser::MySQL::parse()
$tr->parser("MySQL");
# Invokes My::Groovy::Parser::parse()
$tr->parser("My::Groovy::Parser");
# Invoke an anonymous subroutine directly
$tr->parser(sub {
my $dumper = Data::Dumper->new([ $_[1] ], [ "SQL" ]);
$dumper->Purity(1)->Terse(1)->Deepcopy(1);
return $dumper->Dump;
});
There is also "parser_type" and "parser_args", which perform
analogously to "producer_type" and "producer_args"
filters
Set or retreive the filters to run over the schema during the
translation, before the producer creates its output. Filters are sub
routines called, in order, with the schema object to filter as the 1st
arg and a hash of options (passed as a list) for the rest of the args.
They are free to do whatever they want to the schema object, which will
be handed to any following filters, then used by the producer.
Filters are set as an array, which gives the order they run in. Like
parsers and producers, they can be defined by a module name, a module
name relative to the SQL::Translator::Filter namespace, a module name
and function name together or a reference to an anonymous subroutine.
When using a module name a function called "filter" will be invoked in
that package to do the work.
To pass args to the filter set it as an array ref with the 1st value
giving the filter (name or sub) and the rest its args. e.g.
$tr->filters(
sub {
my $schema = shift;
# Do stuff to schema here!
},
DropFKeys,
[ "Names", table => 'lc' ],
[ "Foo", foo => "bar", hello => "world" ],
[ "Filter5" ],
);
Although you normally set them in the constructor, which calls through
to filters. i.e.
my $translator = SQL::Translator->new(
...
filters => [
sub { ... },
[ "Names", table => 'lc' ],
],
...
);
See t/36-filters.t for more examples.
Multiple set calls to filters are cumulative with new filters added to
the end of the current list.
Returns the filters as a list of array refs, the 1st value being a
reference to the filter sub and the rest its args.
show_warnings
Toggles whether to print warnings of name conflicts, identifier
mutations, etc. Probably only generated by producers to let the user
know when something won't translate very smoothly (e.g., MySQL "enum"
fields into Oracle). Accepts a true or false value, returns the
current value.
translate
The "translate" method calls the subroutine referenced by the "parser"
data member, then calls any "filters" and finally calls the "producer"
sub routine (these members are described above). It accepts as
arguments a number of things, in key => value format, including
(potentially) a parser and a producer (they are passed directly to the
"parser" and "producer" methods).
Here is how the parameter list to "translate" is parsed:
· 1 argument means it's the data to be parsed; which could be a
string (filename) or a reference to a scalar (a string stored in
memory), or a reference to a hash, which is parsed as being more
than one argument (see next section).
# Parse the file /path/to/datafile
my $output = $tr->translate("/path/to/datafile");
# Parse the data contained in the string $data
my $output = $tr->translate(\$data);
· More than 1 argument means its a hash of things, and it might be
setting a parser, producer, or datasource (this key is named
"filename" or "file" if it's a file, or "data" for a SCALAR
reference.
# As above, parse /path/to/datafile, but with different producers
for my $prod ("MySQL", "XML", "Sybase") {
print $tr->translate(
producer => $prod,
filename => "/path/to/datafile",
);
}
# The filename hash key could also be:
datasource => \$data,
You get the idea.
filename, data
Using the "filename" method, the filename of the data to be parsed can
be set. This method can be used in conjunction with the "data" method,
below. If both the "filename" and "data" methods are invoked as
mutators, the data set in the "data" method is used.
$tr->filename("/my/data/files/create.sql");
or:
my $create_script = do {
local $/;
open CREATE, "/my/data/files/create.sql" or die $!;
<CREATE>;
};
$tr->data(\$create_script);
"filename" takes a string, which is interpreted as a filename. "data"
takes a reference to a string, which is used as the data to be parsed.
If a filename is set, then that file is opened and read when the
"translate" method is called, as long as the data instance variable is
not set.
schema
Returns the SQL::Translator::Schema object.
trace
Turns on/off the tracing option of Parse::RecDescent.
validate
Whether or not to validate the schema object after parsing and before
producing.
version
Returns the version of the SQL::Translator release.
AUTHORS
See the included AUTHORS file:
http://search.cpan.org/dist/SQL-Translator/AUTHORS
<http://search.cpan.org/dist/SQL-Translator/AUTHORS>
If you would like to contribute to the project, you can send patches to
the developers mailing list:
sqlfairy-developers@lists.sourceforge.net
Or send us a message (with your Sourceforge username) asking to be
added to the project and what you'd like to contribute.
COPYRIGHT
This program is free software; you can redistribute it and/or modify it
under the terms of the GNU General Public License as published by the
Free Software Foundation; version 2.
This program is distributed in the hope that it will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
General Public License for more details.
You should have received a copy of the GNU General Public License along
with this program; if not, write to the Free Software Foundation, Inc.,
59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
BUGS
Please use <http://rt.cpan.org/> for reporting bugs.
PRAISE
If you find this module useful, please use
http://cpanratings.perl.org/rate/?distribution=SQL-Translator
<http://cpanratings.perl.org/rate/?distribution=SQL-Translator> to rate
it.
SEE ALSO
perl, SQL::Translator::Parser, SQL::Translator::Producer,
Parse::RecDescent, GD, GraphViz, Text::RecordParser, Class::DBI,
XML::Writer.
perl v5.14.2 2011-10-05 SQL::Translator(3)
