2 SQL::Translator - convert schema from one database to another
7 my $translator = SQL::Translator->new(
8 xlate => $xlate || {}, # Overrides for field translation
9 debug => $debug, # Print debug info
10 trace => $trace, # Print Parse::RecDescent trace
11 no_comments => $no_comments, # Don't include comments in output
12 show_warnings => $show_warnings, # Print name mutations, conflicts
13 add_drop_table => $add_drop_table, # Add "drop table" statements
16 my $output = $translator->translate(
20 ) or die $translator->error;
25 This module attempts to simplify the task of converting one database
26 create syntax to another through the use of Parsers (which understand
27 the source format) and Producers (which understand the destination
28 format). The idea is that any Parser can be used with any Producer in
29 the conversion process. So, if you wanted Postgres-to-Oracle, you would
30 use the Postgres parser and the Oracle producer.
33 The constructor is called new, and accepts a optional hash of options.
43 All options are, well, optional; these attributes can be set via
44 instance methods. Internally, they are; no (non-syntactical) advantage
45 is gained by passing options to the constructor.
50 Toggles whether or not to add "DROP TABLE" statements just before the
55 Allows the user to override default translation of fields. For example,
56 if a MySQL "text" field would normally be converted to a "long" for
57 Oracle, the user could specify to change it to a "CLOB." Accepts a
58 hashref where keys are the "from" value and values are the "to," returns
59 the current value of the field.
63 Toggles whether to print comments in the output. Accepts a true or false
64 value, returns the current value.
68 The producer method is an accessor/mutator, used to retrieve or define
69 what subroutine is called to produce the output. A subroutine defined as
70 a producer will be invoked as a function (*not a method*) and passed 2
71 parameters: its container SQL::Translator instance and a data structure.
72 It is expected that the function transform the data structure to a
73 string. The SQL::Transformer instance is provided for informational
74 purposes; for example, the type of the parser can be retrieved using the
75 parser_type method, and the error and debug methods can be called when
78 When defining a producer, one of several things can be passed in: A
79 module name (e.g., My::Groovy::Producer), a module name relative to the
80 SQL::Translator::Producer namespace (e.g., MySQL), a module name and
81 function combination (My::Groovy::Producer::transmogrify), or a
82 reference to an anonymous subroutine. If a full module name is passed in
83 (for the purposes of this method, a string containing "::" is considered
84 to be a module name), it is treated as a package, and a function called
85 "produce" will be invoked: $modulename::produce. If $modulename cannot
86 be loaded, the final portion is stripped off and treated as a function.
87 In other words, if there is no file named
88 My/Groovy/Producer/transmogrify.pm, SQL::Translator will attempt to load
89 My/Groovy/Producer.pm and use transmogrify as the name of the function,
90 instead of the default "produce".
92 my $tr = SQL::Translator->new;
94 # This will invoke My::Groovy::Producer::produce($tr, $data)
95 $tr->producer("My::Groovy::Producer");
97 # This will invoke SQL::Translator::Producer::Sybase::produce($tr, $data)
98 $tr->producer("Sybase");
100 # This will invoke My::Groovy::Producer::transmogrify($tr, $data),
101 # assuming that My::Groovy::Producer::transmogrify is not a module
103 $tr->producer("My::Groovy::Producer::transmogrify");
105 # This will invoke the referenced subroutine directly, as
106 # $subref->($tr, $data);
107 $tr->producer(\&my_producer);
109 There is also a method named producer_type, which is a string containing
110 the classname to which the above produce function belongs. In the case
111 of anonymous subroutines, this method returns the string "CODE".
113 Finally, there is a method named producer_args, which is both an
114 accessor and a mutator. Arbitrary data may be stored in name => value
115 pairs for the producer subroutine to access:
117 sub My::Random::producer {
118 my ($tr, $data) = @_;
119 my $pr_args = $tr->producer_args();
121 # $pr_args is a hashref.
123 Extra data passed to the producer method is passed to producer_args:
125 $tr->producer("xSV", delimiter => ',\s*');
127 # In SQL::Translator::Producer::xSV:
128 my $args = $tr->producer_args;
129 my $delimiter = $args->{'delimiter'}; # value is ,\s*
133 The parser method defines or retrieves a subroutine that will be called
134 to perform the parsing. The basic idea is the same as that of producer
135 (see above), except the default subroutine name is "parse", and will be
136 invoked as $module_name::parse($tr, $data). Also, the parser subroutine
137 will be passed a string containing the entirety of the data to be
140 # Invokes SQL::Translator::Parser::MySQL::parse()
141 $tr->parser("MySQL");
143 # Invokes My::Groovy::Parser::parse()
144 $tr->parser("My::Groovy::Parser");
146 # Invoke an anonymous subroutine directly
148 my $dumper = Data::Dumper->new([ $_[1] ], [ "SQL" ]);
149 $dumper->Purity(1)->Terse(1)->Deepcopy(1);
150 return $dumper->Dump;
153 There is also parser_type and parser_args, which perform analogously to
154 producer_type and producer_args
158 Toggles whether to print warnings of name conflicts, identifier
159 mutations, etc. Probably only generated by producers to let the user
160 know when something won't translate very smoothly (e.g., MySQL "enum"
161 fields into Oracle). Accepts a true or false value, returns the current
166 The translate method calls the subroutines referenced by the parser and
167 producer data members (described above). It accepts as arguments a
168 number of things, in key => value format, including (potentially) a
169 parser and a producer (they are passed directly to the parser and
172 Here is how the parameter list to translate is parsed:
174 * 1 argument means it's the data to be parsed; which could be a string
175 (filename) or a refernce to a scalar (a string stored in memory), or
176 a reference to a hash, which is parsed as being more than one
177 argument (see next section).
179 # Parse the file /path/to/datafile
180 my $output = $tr->translate("/path/to/datafile");
182 # Parse the data contained in the string $data
183 my $output = $tr->translate(\$data);
185 * More than 1 argument means its a hash of things, and it might be
186 setting a parser, producer, or datasource (this key is named
187 "filename" or "file" if it's a file, or "data" for a SCALAR
190 # As above, parse /path/to/datafile, but with different producers
191 for my $prod ("MySQL", "XML", "Sybase") {
192 print $tr->translate(
194 filename => "/path/to/datafile",
198 # The filename hash key could also be:
199 datasource => \$data,
205 Using the filename method, the filename of the data to be parsed can be
206 set. This method can be used in conjunction with the data method, below.
207 If both the filename and data methods are invoked as mutators, the data
208 set in the data method is used.
210 $tr->filename("/my/data/files/create.sql");
214 my $create_script = do {
216 open CREATE, "/my/data/files/create.sql" or die $!;
219 $tr->data(\$create_script);
221 filename takes a string, which is interpreted as a filename. data takes
222 a reference to a string, which is used as the data to be parsed. If a
223 filename is set, then that file is opened and read when the translate
224 method is called, as long as the data instance variable is not set.
228 Turns on/off the tracing option of Parse::RecDescent.
231 Ken Y. Clark, <kclark@cpan.org>, darren chamberlain <darren@cpan.org>,
232 Chris Mungall <cjm@fruitfly.org>, Allen Day
233 <allenday@users.sourceforge.net>
236 This program is free software; you can redistribute it and/or modify it
237 under the terms of the GNU General Public License as published by the
238 Free Software Foundation; version 2.
240 This program is distributed in the hope that it will be useful, but
241 WITHOUT ANY WARRANTY; without even the implied warranty of
242 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
243 Public License for more details.
245 You should have received a copy of the GNU General Public License along
246 with this program; if not, write to the Free Software Foundation, Inc.,
247 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
250 the perl manpage, the SQL::Translator::Parser manpage, the
251 SQL::Translator::Producer manpage, the Parse::RecDescent manpage