1SQL::Translator(3) User Contributed Perl Documentation SQL::Translator(3)
2
6 SQL::Translator - manipulate structured data definitions (SQL and more)
7
9 use SQL::Translator;
10 my $translator = SQL::Translator->new(
12 # Print debug info
13 debug => 1,
14 # Print Parse::RecDescent trace
15 trace => 0,
16 # Don't include comments in output
17 no_comments => 0,
18 # Print name mutations, conflicts
19 show_warnings => 0,
20 # Add "drop table" statements
21 add_drop_table => 1,
22 # to quote or not to quote, thats the question
23 quote_table_names => 1,
24 quote_field_names => 1,
25 # Validate schema object
26 validate => 1,
27 # Make all table names CAPS in producers which support this option
28 format_table_name => sub {my $tablename = shift; return uc($tablename)},
29 # Null-op formatting, only here for documentation's sake
30 format_package_name => sub {return shift},
31 format_fk_name => sub {return shift},
32 format_pk_name => sub {return shift},
33 );
34 my $output = $translator->translate(
36 from => 'MySQL',
37 to => 'Oracle',
38 # Or an arrayref of filenames, i.e. [ $file1, $file2, $file3 ]
39 filename => $file,
40 ) or die $translator->error;
41 print $output;
43
45 This documentation covers the API for SQL::Translator. For a more gen‐
46 eral discussion of how to use the modules and scripts, please see
47 SQL::Translator::Manual.
48 SQL::Translator is a group of Perl modules that converts vendor-spe‐
50 cific SQL table definitions into other formats, such as other vendor-
51 specific SQL, ER diagrams, documentation (POD and HTML), XML, and
52 Class::DBI classes. The main focus of SQL::Translator is SQL, but
53 parsers exist for other structured data formats, including Excel
54 spreadsheets and arbitrarily delimited text files. Through the separa‐
55 tion of the code into parsers and producers with an object model in
56 between, it's possible to combine any parser with any producer, to plug
57 in custom parsers or producers, or to manipulate the parsed data via
58 the built-in object model. Presently only the definition parts of SQL
59 are handled (CREATE, ALTER), not the manipulation of data (INSERT,
60 UPDATE, DELETE).
61
63 The constructor is called "new", and accepts a optional hash of
64 options. Valid options are:
65 · parser / from
67 · parser_args
69 · producer / to
71 · producer_args
73 · filters
75 · filename / file
77 · data
79 · debug
81 · add_drop_table
83 · quote_table_names
85 · quote_field_names
87 · no_comments
89 · trace
91 · validate
93 All options are, well, optional; these attributes can be set via
95 instance methods. Internally, they are; no (non-syntactical) advantage
96 is gained by passing options to the constructor.
97
99 add_drop_table
100 Toggles whether or not to add "DROP TABLE" statements just before the
102 create definitions.
103 quote_table_names
105 Toggles whether or not to quote table names with " in DROP and CREATE
107 statements. The default (true) is to quote them.
108 quote_field_names
110 Toggles whether or not to quote field names with " in most statements.
112 The default (true), is to quote them.
113 no_comments
115 Toggles whether to print comments in the output. Accepts a true or
117 false value, returns the current value.
118 producer
120 The "producer" method is an accessor/mutator, used to retrieve or
122 define what subroutine is called to produce the output. A subroutine
123 defined as a producer will be invoked as a function (not a method) and
124 passed its container "SQL::Translator" instance, which it should call
125 the "schema" method on, to get the "SQL::Translator::Schema" generated
126 by the parser. It is expected that the function transform the schema
127 structure to a string. The "SQL::Translator" instance is also useful
128 for informational purposes; for example, the type of the parser can be
129 retrieved using the "parser_type" method, and the "error" and "debug"
130 methods can be called when needed.
131 When defining a producer, one of several things can be passed in: A
133 module name (e.g., "My::Groovy::Producer"), a module name relative to
134 the "SQL::Translator::Producer" namespace (e.g., "MySQL"), a module
135 name and function combination ("My::Groovy::Producer::transmogrify"),
136 or a reference to an anonymous subroutine. If a full module name is
137 passed in (for the purposes of this method, a string containing "::" is
138 considered to be a module name), it is treated as a package, and a
139 function called "produce" will be invoked: $modulename::produce. If
140 $modulename cannot be loaded, the final portion is stripped off and
141 treated as a function. In other words, if there is no file named
142 My/Groovy/Producer/transmogrify.pm, "SQL::Translator" will attempt to
143 load My/Groovy/Producer.pm and use "transmogrify" as the name of the
144 function, instead of the default "produce".
145 my $tr = SQL::Translator->new;
147 # This will invoke My::Groovy::Producer::produce($tr, $data)
149 $tr->producer("My::Groovy::Producer");
150 # This will invoke SQL::Translator::Producer::Sybase::produce($tr, $data)
152 $tr->producer("Sybase");
153 # This will invoke My::Groovy::Producer::transmogrify($tr, $data),
155 # assuming that My::Groovy::Producer::transmogrify is not a module
156 # on disk.
157 $tr->producer("My::Groovy::Producer::transmogrify");
158 # This will invoke the referenced subroutine directly, as
160 # $subref->($tr, $data);
161 $tr->producer(\&my_producer);
162 There is also a method named "producer_type", which is a string con‐
164 taining the classname to which the above "produce" function belongs.
165 In the case of anonymous subroutines, this method returns the string
166 "CODE".
167 Finally, there is a method named "producer_args", which is both an
169 accessor and a mutator. Arbitrary data may be stored in name => value
170 pairs for the producer subroutine to access:
171 sub My::Random::producer {
173 my ($tr, $data) = @_;
174 my $pr_args = $tr->producer_args();
175 # $pr_args is a hashref.
177 Extra data passed to the "producer" method is passed to "pro‐
179 ducer_args":
180 $tr->producer("xSV", delimiter => ',\s*');
182 # In SQL::Translator::Producer::xSV:
184 my $args = $tr->producer_args;
185 my $delimiter = $args->{'delimiter'}; # value is ,\s*
186 parser
188 The "parser" method defines or retrieves a subroutine that will be
190 called to perform the parsing. The basic idea is the same as that of
191 "producer" (see above), except the default subroutine name is "parse",
192 and will be invoked as "$module_name::parse($tr, $data)". Also, the
193 parser subroutine will be passed a string containing the entirety of
194 the data to be parsed.
195 # Invokes SQL::Translator::Parser::MySQL::parse()
197 $tr->parser("MySQL");
198 # Invokes My::Groovy::Parser::parse()
200 $tr->parser("My::Groovy::Parser");
201 # Invoke an anonymous subroutine directly
203 $tr->parser(sub {
204 my $dumper = Data::Dumper->new([ $_[1] ], [ "SQL" ]);
205 $dumper->Purity(1)->Terse(1)->Deepcopy(1);
206 return $dumper->Dump;
207 });
208 There is also "parser_type" and "parser_args", which perform analo‐
210 gously to "producer_type" and "producer_args"
211 filters
213 Set or retreive the filters to run over the schema during the transla‐
215 tion, before the producer creates its output. Filters are sub routines
216 called, in order, with the schema object to filter as the 1st arg and a
217 hash of options (passed as a list) for the rest of the args. They are
218 free to do whatever they want to the schema object, which will be
219 handed to any following filters, then used by the producer.
220 Filters are set as an array, which gives the order they run in. Like
222 parsers and producers, they can be defined by a module name, a module
223 name relative to the SQL::Translator::Filter namespace, a module name
224 and function name together or a reference to an anonymous subroutine.
225 When using a module name a function called "filter" will be invoked in
226 that package to do the work.
227 To pass args to the filter set it as an array ref with the 1st value
229 giving the filter (name or sub) and the rest its args. e.g.
230 $tr->filters(
232 sub {
233 my $schema = shift;
234 # Do stuff to schema here!
235 },
236 DropFKeys,
237 [ "Names", table => 'lc' ],
238 [ "Foo", foo => "bar", hello => "world" ],
239 [ "Filter5" ],
240 );
241 Although you normally set them in the constructor, which calls through
243 to filters. i.e.
244 my $translator = SQL::Translator->new(
246 ...
247 filters => [
248 sub { ... },
249 [ "Names", table => 'lc' ],
250 ],
251 ...
252 );
253 See t/36-filters.t for more examples.
255 Multiple set calls to filters are cumulative with new filters added to
257 the end of the current list.
258 Returns the filters as a list of array refs, the 1st value being a ref‐
260 erence to the filter sub and the rest its args.
261 show_warnings
263 Toggles whether to print warnings of name conflicts, identifier muta‐
265 tions, etc. Probably only generated by producers to let the user know
266 when something won't translate very smoothly (e.g., MySQL "enum" fields
267 into Oracle). Accepts a true or false value, returns the current
268 value.
269 translate
271 The "translate" method calls the subroutine referenced by the "parser"
273 data member, then calls any "filters" and finally calls the "producer"
274 sub routine (these members are described above). It accepts as argu‐
275 ments a number of things, in key => value format, including (poten‐
276 tially) a parser and a producer (they are passed directly to the
277 "parser" and "producer" methods).
278 Here is how the parameter list to "translate" is parsed:
280 · 1 argument means it's the data to be parsed; which could be a
282 string (filename) or a reference to a scalar (a string stored in
283 memory), or a reference to a hash, which is parsed as being more
284 than one argument (see next section).
285 # Parse the file /path/to/datafile
287 my $output = $tr->translate("/path/to/datafile");
288 # Parse the data contained in the string $data
290 my $output = $tr->translate(\$data);
291 · More than 1 argument means its a hash of things, and it might be
293 setting a parser, producer, or datasource (this key is named "file‐
294 name" or "file" if it's a file, or "data" for a SCALAR reference.
295 # As above, parse /path/to/datafile, but with different producers
297 for my $prod ("MySQL", "XML", "Sybase") {
298 print $tr->translate(
299 producer => $prod,
300 filename => "/path/to/datafile",
301 );
302 }
303 # The filename hash key could also be:
305 datasource => \$data,
306 You get the idea.
308 filename, data
310 Using the "filename" method, the filename of the data to be parsed can
312 be set. This method can be used in conjunction with the "data" method,
313 below. If both the "filename" and "data" methods are invoked as muta‐
314 tors, the data set in the "data" method is used.
315 $tr->filename("/my/data/files/create.sql");
317 or:
319 my $create_script = do {
321 local $/;
322 open CREATE, "/my/data/files/create.sql" or die $!;
323 <CREATE>;
324 };
325 $tr->data(\$create_script);
326 "filename" takes a string, which is interpreted as a filename. "data"
328 takes a reference to a string, which is used as the data to be parsed.
329 If a filename is set, then that file is opened and read when the
330 "translate" method is called, as long as the data instance variable is
331 not set.
332 schema
334 Returns the SQL::Translator::Schema object.
336 trace
338 Turns on/off the tracing option of Parse::RecDescent.
340 validate
342 Whether or not to validate the schema object after parsing and before
344 producing.
345 version
347 Returns the version of the SQL::Translator release.
349
351 The following people have contributed to the SQLFairy project:
352 * Mark Addison <grommit@users.sourceforge.net>
354 * Sam Angiuoli <angiuoli@users.sourceforge.net>
355 * Dave Cash <dave@gnofn.org>
356 * Darren Chamberlain <dlc@users.sourceforge.net>
357 * Ken Y. Clark <kclark@cpan.org>
358 * Allen Day <allenday@users.sourceforge.net>
359 * Paul Harrington <phrrngtn@users.sourceforge.net>
360 * Mikey Melillo <mmelillo@users.sourceforge.net>
361 * Chris Mungall <cjm@fruitfly.org>
362 * Ross Smith II <rossta@users.sf.net>
363 * Gudmundur A. Thorisson <mummi@cshl.org>
364 * Chris To <christot@users.sourceforge.net>
365 * Jason Williams <smdwilliams@users.sourceforge.net>
366 * Ying Zhang <zyolive@yahoo.com>
367 If you would like to contribute to the project, you can send patches to
369 the developers mailing list:
370 sqlfairy-developers@lists.sourceforge.net
372 Or send us a message (with your Sourceforge username) asking to be
374 added to the project and what you'd like to contribute.
375
377 This program is free software; you can redistribute it and/or modify it
378 under the terms of the GNU General Public License as published by the
379 Free Software Foundation; version 2.
380 This program is distributed in the hope that it will be useful, but
382 WITHOUT ANY WARRANTY; without even the implied warranty of MER‐
383 CHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
384 Public License for more details.
385 You should have received a copy of the GNU General Public License along
387 with this program; if not, write to the Free Software Foundation, Inc.,
388 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
389
391 Please use <http://rt.cpan.org/> for reporting bugs.
392
394 If you find this module useful, please use <http://cpanrat‐
395 ings.perl.org/rate/?distribution=SQL-Translator> to rate it.
396
398 perl, SQL::Translator::Parser, SQL::Translator::Producer, Parse::RecDe‐
399 scent, GD, GraphViz, Text::RecordParser, Class::DBI, XML::Writer.
400perl v5.8.8 2007-10-24 SQL::Translator(3)