1 package SQL::Translator;
3 # ----------------------------------------------------------------------
4 # $Id: Translator.pm,v 1.11 2002-11-21 17:45:17 dlc Exp $
5 # ----------------------------------------------------------------------
6 # Copyright (C) 2002 Ken Y. Clark <kycl4rk@users.sourceforge.net>,
7 # darren chamberlain <darren@cpan.org>
9 # This program is free software; you can redistribute it and/or
10 # modify it under the terms of the GNU General Public License as
11 # published by the Free Software Foundation; version 2.
13 # This program is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 # General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with this program; if not, write to the Free Software
20 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
22 # -------------------------------------------------------------------
26 SQL::Translator - convert schema from one database to another
31 my $translator = SQL::Translator->new;
32 my $output = $translator->translate(
36 ) or die $translator->error;
41 This module attempts to simplify the task of converting one database
42 create syntax to another through the use of Parsers (which understand
43 the sourced format) and Producers (which understand the destination
44 format). The idea is that any Parser can be used with any Producer in
45 the conversion process. So, if you wanted PostgreSQL-to-Oracle, you
46 would use the PostgreSQL parser and the Oracle producer.
51 use vars qw( $VERSION $DEFAULT_SUB $DEBUG $ERROR );
52 use base 'Class::Base';
54 $VERSION = sprintf "%d.%02d", q$Revision: 1.11 $ =~ /(\d+)\.(\d+)/;
55 $DEBUG = 0 unless defined $DEBUG;
60 use File::Spec::Functions qw(catfile);
61 use File::Basename qw(dirname);
64 # ----------------------------------------------------------------------
65 # The default behavior is to "pass through" values (note that the
66 # SQL::Translator instance is the first value ($_[0]), and the stuff
67 # to be parsed is the second value ($_[1])
68 # ----------------------------------------------------------------------
69 $DEFAULT_SUB = sub { $_[1] } unless defined $DEFAULT_SUB;
73 The constructor is called B<new>, and accepts a optional hash of options.
78 =item parser (aka from)
82 =item producer (aka to)
86 =item filename (aka file)
94 All options are, well, optional; these attributes can be set via
95 instance methods. Internally, they are; no (non-syntactical)
96 advantage is gained by passing options to the constructor.
100 # ----------------------------------------------------------------------
104 # new takes an optional hash of arguments. These arguments may
105 # include a parser, specified with the keys "parser" or "from",
106 # and a producer, specified with the keys "producer" or "to".
108 # The values that can be passed as the parser or producer are
109 # given directly to the parser or producer methods, respectively.
110 # See the appropriate method description below for details about
111 # what each expects/accepts.
112 # ----------------------------------------------------------------------
114 my ( $self, $config ) = @_;
117 # Set the parser and producer.
119 # If a 'parser' or 'from' parameter is passed in, use that as the
120 # parser; if a 'producer' or 'to' parameter is passed in, use that
121 # as the producer; both default to $DEFAULT_SUB.
123 $self->parser ($config->{'parser'} || $config->{'from'} || $DEFAULT_SUB);
124 $self->producer($config->{'producer'} || $config->{'to'} || $DEFAULT_SUB);
127 # Set the parser_args and producer_args
129 for my $pargs ( qw[ parser_args producer_args ] ) {
130 $self->$pargs( $config->{$pargs} ) if defined $config->{ $pargs };
134 # Set the data source, if 'filename' or 'file' is provided.
136 $config->{'filename'} ||= $config->{'file'} || "";
137 $self->filename( $config->{'filename'} ) if $config->{'filename'};
140 # Finally, if there is a 'data' parameter, use that in
141 # preference to filename and file
143 if ( my $data = $config->{'data'} ) {
144 $self->data( $data );
147 $self->{'debug'} = defined $config->{'debug'} ? $config->{'debug'} : $DEBUG;
156 The B<producer> method is an accessor/mutator, used to retrieve or
157 define what subroutine is called to produce the output. A subroutine
158 defined as a producer will be invoked as a function (not a method) and
159 passed 2 parameters: its container SQL::Translator instance and a
160 data structure. It is expected that the function transform the data
161 structure to a string. The SQL::Transformer instance is provided for
162 informational purposes; for example, the type of the parser can be
163 retrieved using the B<parser_type> method, and the B<error> and
164 B<debug> methods can be called when needed.
166 When defining a producer, one of several things can be passed
167 in: A module name (e.g., My::Groovy::Producer), a module name
168 relative to the SQL::Translator::Producer namespace (e.g., MySQL), a
169 module name and function combination (My::Groovy::Producer::transmogrify),
170 or a reference to an anonymous subroutine. If a full module name is
171 passed in (for the purposes of this method, a string containing "::"
172 is considered to be a module name), it is treated as a package, and a
173 function called "produce" will be invoked: $modulename::produce. If
174 $modulename cannot be loaded, the final portion is stripped off and
175 treated as a function. In other words, if there is no file named
176 My/Groovy/Producer/transmogrify.pm, SQL::Translator will attempt to load
177 My/Groovy/Producer.pm and use transmogrify as the name of the function,
178 instead of the default "produce".
180 my $tr = SQL::Translator->new;
182 # This will invoke My::Groovy::Producer::produce($tr, $data)
183 $tr->producer("My::Groovy::Producer");
185 # This will invoke SQL::Translator::Producer::Sybase::produce($tr, $data)
186 $tr->producer("Sybase");
188 # This will invoke My::Groovy::Producer::transmogrify($tr, $data),
189 # assuming that My::Groovy::Producer::transmogrify is not a module
191 $tr->producer("My::Groovy::Producer::transmogrify");
193 # This will invoke the referenced subroutine directly, as
194 # $subref->($tr, $data);
195 $tr->producer(\&my_producer);
197 There is also a method named B<producer_type>, which is a string
198 containing the classname to which the above B<produce> function
199 belongs. In the case of anonymous subroutines, this method returns
202 Finally, there is a method named B<producer_args>, which is both an
203 accessor and a mutator. Arbitrary data may be stored in name => value
204 pairs for the producer subroutine to access:
206 sub My::Random::producer {
207 my ($tr, $data) = @_;
208 my $pr_args = $tr->producer_args();
210 # $pr_args is a hashref.
212 Extra data passed to the B<producer> method is passed to
215 $tr->producer("xSV", delimiter => ',\s*');
217 # In SQL::Translator::Producer::xSV:
218 my $args = $tr->producer_args;
219 my $delimiter = $args->{'delimiter'}; # value is ,\s*
223 # producer and producer_type
227 # producer as a mutator
229 my $producer = shift;
231 # Passed a module name (string containing "::")
232 if ($producer =~ /::/) {
235 # Module name was passed directly
236 # We try to load the name; if it doesn't load, there's
237 # a possibility that it has a function name attached to
239 if (load($producer)) {
240 $func_name = "produce";
243 # Module::function was passed
245 # Passed Module::Name::function; try to recover
246 my @func_parts = split /::/, $producer;
247 $func_name = pop @func_parts;
248 $producer = join "::", @func_parts;
250 # If this doesn't work, then we have a legitimate
252 load($producer) or die "Can't load $producer: $@";
255 # get code reference and assign
256 $self->{'producer'} = \&{ "$producer\::$func_name" };
257 $self->{'producer_type'} = $producer;
258 $self->debug("Got producer: $producer\::$func_name\n");
261 # passed an anonymous subroutine reference
262 elsif (isa($producer, 'CODE')) {
263 $self->{'producer'} = $producer;
264 $self->{'producer_type'} = "CODE";
265 $self->debug("Got producer: code ref\n");
268 # passed a string containing no "::"; relative package name
270 my $Pp = sprintf "SQL::Translator::Producer::$producer";
271 load($Pp) or die "Can't load $Pp: $@";
272 $self->{'producer'} = \&{ "$Pp\::produce" };
273 $self->{'producer_type'} = $Pp;
274 $self->debug("Got producer: $Pp\n");
277 # At this point, $self->{'producer'} contains a subroutine
278 # reference that is ready to run
280 # Anything left? If so, it's producer_args
281 $self->producer_args(@_) if (@_);
284 return $self->{'producer'};
287 # ----------------------------------------------------------------------
290 # producer_type is an accessor that allows producer subs to get
291 # information about their origin. This is poptentially important;
292 # since all producer subs are called as subroutine refernces, there is
293 # no way for a producer to find out which package the sub lives in
294 # originally, for example.
295 # ----------------------------------------------------------------------
296 sub producer_type { $_[0]->{'producer_type'} }
298 # ----------------------------------------------------------------------
301 # Arbitrary name => value pairs of paramters can be passed to a
302 # producer using this method.
303 # ----------------------------------------------------------------------
307 my $args = isa($_[0], 'HASH') ? shift : { @_ };
308 $self->{'producer_args'} = $args;
310 $self->{'producer_args'};
315 The B<parser> method defines or retrieves a subroutine that will be
316 called to perform the parsing. The basic idea is the same as that of
317 B<producer> (see above), except the default subroutine name is
318 "parse", and will be invoked as $module_name::parse($tr, $data).
319 Also, the parser subroutine will be passed a string containing the
320 entirety of the data to be parsed (or possibly a reference to a string?).
322 # Invokes SQL::Translator::Parser::MySQL::parse()
323 $tr->parser("MySQL");
325 # Invokes My::Groovy::Parser::parse()
326 $tr->parser("My::Groovy::Parser");
328 # Invoke an anonymous subroutine directly
330 my $dumper = Data::Dumper->new([ $_[1] ], [ "SQL" ]);
331 $dumper->Purity(1)->Terse(1)->Deepcopy(1);
332 return $dumper->Dump;
335 There is also B<parser_type> and B<parser_args>, which perform
336 analogously to B<producer_type> and B<producer_args>
343 # parser as a mutator
347 # Passed a module name (string containing "::")
348 if ($parser =~ /::/) {
351 # Module name was passed directly
352 # We try to load the name; if it doesn't load, there's
353 # a possibility that it has a function name attached to
356 $func_name = "parse";
359 # Module::function was passed
361 # Passed Module::Name::function; try to recover
362 my @func_parts = split /::/, $parser;
363 $func_name = pop @func_parts;
364 $parser = join "::", @func_parts;
366 # If this doesn't work, then we have a legitimate
368 load($parser) or die "Can't load $parser: $@";
371 # get code reference and assign
372 $self->{'parser'} = \&{ "$parser\::$func_name" };
373 $self->{'parser_type'} = $parser;
374 $self->debug("Got parser: $parser\::$func_name\n");
377 # passed an anonymous subroutine reference
378 elsif ( isa( $parser, 'CODE' ) ) {
379 $self->{'parser'} = $parser;
380 $self->{'parser_type'} = "CODE";
381 $self->debug("Got parser: code ref\n");
384 # passed a string containing no "::"; relative package name
386 my $Pp = "SQL::Translator::Parser::$parser";
387 load( $Pp ) or die "Can't load $Pp: $@";
388 $self->{'parser'} = \&{ "$Pp\::parse" };
389 $self->{'parser_type'} = $Pp;
390 $self->debug("Got parser: $Pp\n");
394 # At this point, $self->{'parser'} contains a subroutine
395 # reference that is ready to run
397 $self->parser_args( @_ ) if (@_);
400 return $self->{'parser'};
403 sub parser_type { $_[0]->{'parser_type'} }
409 my $args = isa($_[0], 'HASH') ? shift : { @_ };
410 $self->{'parser_args'} = $args;
412 $self->{'parser_args'};
417 The B<translate> method calls the subroutines referenced by the
418 B<parser> and B<producer> data members (described above). It accepts
419 as arguments a number of things, in key => value format, including
420 (potentially) a parser and a producer (they are passed directly to the
421 B<parser> and B<producer> methods).
423 Here is how the parameter list to B<translate> is parsed:
429 1 argument means it's the data to be parsed; which could be a string
430 (filename) or a refernce to a scalar (a string stored in memory), or a
431 reference to a hash, which is parsed as being more than one argument
434 # Parse the file /path/to/datafile
435 my $output = $tr->translate("/path/to/datafile");
437 # Parse the data contained in the string $data
438 my $output = $tr->translate(\$data);
442 More than 1 argument means its a hash of things, and it might be
443 setting a parser, producer, or datasource (this key is named
444 "filename" or "file" if it's a file, or "data" for a SCALAR reference.
446 # As above, parse /path/to/datafile, but with different producers
447 for my $prod ("MySQL", "XML", "Sybase") {
448 print $tr->translate(
450 filename => "/path/to/datafile",
454 # The filename hash key could also be:
455 datasource => \$data,
461 =head2 B<filename>, B<data>
463 Using the B<filename> method, the filename of the data to be parsed
464 can be set. This method can be used in conjunction with the B<data>
465 method, below. If both the B<filename> and B<data> methods are
466 invoked as mutators, the data set in the B<data> method is used.
468 $tr->filename("/my/data/files/create.sql");
472 my $create_script = do {
474 open CREATE, "/my/data/files/create.sql" or die $!;
477 $tr->data(\$create_script);
479 B<filename> takes a string, which is interpreted as a filename.
480 B<data> takes a reference to a string, which is used as the data to be
481 parsed. If a filename is set, then that file is opened and read when
482 the B<translate> method is called, as long as the data instance
487 # filename - get or set the filename
491 my $filename = shift;
493 my $msg = "Cannot use directory '$filename' as input source";
494 return $self->error($msg);
495 } elsif (-f _ && -r _) {
496 $self->{'filename'} = $filename;
497 $self->debug("Got filename: '$self->{'filename'}'\n");
499 my $msg = "Cannot use '$filename' as input source: ".
500 "file does not exist or is not readable.";
501 return $self->error($msg);
508 # data - get or set the data
509 # if $self->{'data'} is not set, but $self->{'filename'} is, then
510 # $self->{'filename'} is opened and read, whith the results put into
515 # Set $self->{'data'} to $_[0], if it is provided.
518 if (isa($data, "SCALAR")) {
519 $self->{'data'} = $data;
521 elsif (! ref $data) {
522 $self->{'data'} = \$data;
526 # If we have a filename but no data yet, populate.
527 if (not $self->{'data'} and my $filename = $self->filename) {
528 $self->debug("Opening '$filename' to get contents.\n");
533 unless (open FH, $filename) {
534 return $self->error("Can't read file '$filename': $!");
538 $self->{'data'} = \$data;
541 return $self->error("Can't close file '$filename': $!");
545 return $self->{'data'};
551 my ($args, $parser, $parser_type, $producer, $producer_type);
552 my ($parser_output, $producer_output);
556 # Passed a reference to a hash?
557 if (isa($_[0], 'HASH')) {
559 $self->debug("translate: Got a hashref\n");
563 # Passed a reference to a string containing the data
564 elsif (isa($_[0], 'SCALAR')) {
565 # passed a ref to a string
566 $self->debug("translate: Got a SCALAR reference (string)\n");
570 # Not a reference; treat it as a filename
571 elsif (! ref $_[0]) {
572 # Not a ref, it's a filename
573 $self->debug("translate: Got a filename\n");
574 $self->filename($_[0]);
577 # Passed something else entirely.
579 # We're not impressed. Take your empty string and leave.
582 # Actually, if data, parser, and producer are set, then we
583 # can continue. Too bad, because I like my comment
585 return "" unless ($self->data &&
591 # You must pass in a hash, or you get nothing.
596 # ----------------------------------------------------------------------
597 # Can specify the data to be transformed using "filename", "file",
598 # "data", or "datasource".
599 # ----------------------------------------------------------------------
600 if (my $filename = ($args->{'filename'} || $args->{'file'})) {
601 $self->filename($filename);
604 if (my $data = ($self->{'data'} || $self->{'datasource'})) {
608 # ----------------------------------------------------------------
610 # ----------------------------------------------------------------
611 my $data = $self->data;
612 unless (length $$data) {
613 return $self->error("Empty data file!");
616 # ----------------------------------------------------------------
617 # Local reference to the parser subroutine
618 # ----------------------------------------------------------------
619 if ($parser = ($args->{'parser'} || $args->{'from'})) {
620 $self->parser($parser);
622 $parser = $self->parser;
623 $parser_type = $self->parser_type;
625 # ----------------------------------------------------------------
626 # Local reference to the producer subroutine
627 # ----------------------------------------------------------------
628 if ($producer = ($args->{'producer'} || $args->{'to'})) {
629 $self->producer($producer);
631 $producer = $self->producer;
632 $producer_type = $self->producer_type;
634 # ----------------------------------------------------------------
635 # Execute the parser, then execute the producer with that output.
636 # Allowances are made for each piece to die, or fail to compile,
637 # since the referenced subroutines could be almost anything. In
638 # the future, each of these might happen in a Safe environment,
639 # depending on how paranoid we want to be.
640 # ----------------------------------------------------------------
641 eval { $parser_output = $parser->($self, $$data) };
642 if ($@ || ! $parser_output) {
643 my $msg = sprintf "translate: Error with parser '%s': %s",
644 $parser_type, ($@) ? $@ : " no results";
645 return $self->error($msg);
648 eval { $producer_output = $producer->($self, $parser_output) };
649 if ($@ || ! $producer_output) {
650 my $msg = sprintf "translate: Error with producer '%s': %s",
651 $producer_type, ($@) ? $@ : " no results";
652 return $self->error($msg);
655 return $producer_output;
659 require SQL::Translator::Producer;
660 my $path = catfile(dirname($INC{'SQL/Translator/Producer.pm'}), "Producer");
661 my $dh = IO::Dir->new($path);
663 my @available = map { join "::", "SQL::Translator::Producer", $_ }
664 grep /\.pm$/, $dh->read;
671 require SQL::Translator::Parser;
672 my $path = catfile(dirname($INC{'SQL/Translator/Parser.pm'}), "Parser");
673 my $dh = IO::Dir->new($path);
675 my @available = map { join "::", "SQL::Translator::Parser", $_ }
676 grep /\.pm$/, $dh->read;
683 my $module = do { my $m = shift; $m =~ s[::][/]g; "$m.pm" };
684 return 1 if $INC{$module};
686 eval { require $module };
692 sub isa { UNIVERSAL::isa($_[0], $_[1]) }
696 #-----------------------------------------------------
697 # Rescue the drowning and tie your shoestrings.
698 # Henry David Thoreau
699 #-----------------------------------------------------
705 Ken Y. Clark, E<lt>kclark@logsoft.comE<gt>,
706 darren chamberlain E<lt>darren@cpan.orgE<gt>
710 This program is free software; you can redistribute it and/or modify
711 it under the terms of the GNU General Public License as published by
712 the Free Software Foundation; version 2.
714 This program is distributed in the hope that it will be useful, but
715 WITHOUT ANY WARRANTY; without even the implied warranty of
716 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
717 General Public License for more details.
719 You should have received a copy of the GNU General Public License
720 along with this program; if not, write to the Free Software
721 Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307
726 L<perl>, L<Parse::RecDescent>