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_identifiers => 1,
24 # Validate schema object
25 validate => 1,
26 # Make all table names CAPS in producers which support this option
27 format_table_name => sub {my $tablename = shift; return uc($tablename)},
28 # Null-op formatting, only here for documentation's sake
29 format_package_name => sub {return shift},
30 format_fk_name => sub {return shift},
31 format_pk_name => sub {return shift},
32 );
33 my $output = $translator->translate(
35 from => 'MySQL',
36 to => 'Oracle',
37 # Or an arrayref of filenames, i.e. [ $file1, $file2, $file3 ]
38 filename => $file,
39 ) or die $translator->error;
40 print $output;
42
44 This documentation covers the API for SQL::Translator. For a more
45 general discussion of how to use the modules and scripts, please see
46 SQL::Translator::Manual.
47 SQL::Translator is a group of Perl modules that converts vendor-
49 specific SQL table definitions into other formats, such as other
50 vendor-specific SQL, ER diagrams, documentation (POD and HTML), XML,
51 and Class::DBI classes. The main focus of SQL::Translator is SQL, but
52 parsers exist for other structured data formats, including Excel
53 spreadsheets and arbitrarily delimited text files. Through the
54 separation of the code into parsers and producers with an object model
55 in between, it's possible to combine any parser with any producer, to
56 plug in custom parsers or producers, or to manipulate the parsed data
57 via the built-in object model. Presently only the definition parts of
58 SQL are handled (CREATE, ALTER), not the manipulation of data (INSERT,
59 UPDATE, DELETE).
60
62 new
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_identifiers
85 • quote_table_names (DEPRECATED)
87 • quote_field_names (DEPRECATED)
89 • no_comments
91 • trace
93 • validate
95 All options are, well, optional; these attributes can be set via
97 instance methods. Internally, they are; no (non-syntactical) advantage
98 is gained by passing options to the constructor.
99
101 add_drop_table
102 Toggles whether or not to add "DROP TABLE" statements just before the
103 create definitions.
104 quote_identifiers
106 Toggles whether or not to quote identifiers (table, column, constraint,
107 etc.) with a quoting mechanism suitable for the chosen Producer. The
108 default (true) is to quote them.
109 quote_table_names
111 DEPRECATED - A legacy proxy to "quote_identifiers"
112 quote_field_names
114 DEPRECATED - A legacy proxy to "quote_identifiers"
115 no_comments
117 Toggles whether to print comments in the output. Accepts a true or
118 false value, returns the current value.
119 producer
121 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
164 containing 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
179 "producer_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
189 called to perform the parsing. The basic idea is the same as that of
190 "producer" (see above), except the default subroutine name is "parse",
191 and will be invoked as "$module_name::parse($tr, $data)". Also, the
192 parser subroutine will be passed a string containing the entirety of
193 the data to be parsed.
194 # Invokes SQL::Translator::Parser::MySQL::parse()
196 $tr->parser("MySQL");
197 # Invokes My::Groovy::Parser::parse()
199 $tr->parser("My::Groovy::Parser");
200 # Invoke an anonymous subroutine directly
202 $tr->parser(sub {
203 my $dumper = Data::Dumper->new([ $_[1] ], [ "SQL" ]);
204 $dumper->Purity(1)->Terse(1)->Deepcopy(1);
205 return $dumper->Dump;
206 });
207 There is also "parser_type" and "parser_args", which perform
209 analogously to "producer_type" and "producer_args"
210 filters
212 Set or retrieve the filters to run over the schema during the
213 translation, before the producer creates its output. Filters are sub
214 routines called, in order, with the schema object to filter as the 1st
215 arg and a hash of options (passed as a list) for the rest of the args.
216 They are free to do whatever they want to the schema object, which will
217 be handed to any following filters, then used by the producer.
218 Filters are set as an array, which gives the order they run in. Like
220 parsers and producers, they can be defined by a module name, a module
221 name relative to the SQL::Translator::Filter namespace, a module name
222 and function name together or a reference to an anonymous subroutine.
223 When using a module name a function called "filter" will be invoked in
224 that package to do the work.
225 To pass args to the filter set it as an array ref with the 1st value
227 giving the filter (name or sub) and the rest its args. e.g.
228 $tr->filters(
230 sub {
231 my $schema = shift;
232 # Do stuff to schema here!
233 },
234 DropFKeys,
235 [ "Names", table => 'lc' ],
236 [ "Foo", foo => "bar", hello => "world" ],
237 [ "Filter5" ],
238 );
239 Although you normally set them in the constructor, which calls through
241 to filters. i.e.
242 my $translator = SQL::Translator->new(
244 ...
245 filters => [
246 sub { ... },
247 [ "Names", table => 'lc' ],
248 ],
249 ...
250 );
251 See t/36-filters.t for more examples.
253 Multiple set calls to filters are cumulative with new filters added to
255 the end of the current list.
256 Returns the filters as a list of array refs, the 1st value being a
258 reference to the filter sub and the rest its args.
259 show_warnings
261 Toggles whether to print warnings of name conflicts, identifier
262 mutations, etc. Probably only generated by producers to let the user
263 know when something won't translate very smoothly (e.g., MySQL "enum"
264 fields into Oracle). Accepts a true or false value, returns the
265 current value.
266 translate
268 The "translate" method calls the subroutine referenced by the "parser"
269 data member, then calls any "filters" and finally calls the "producer"
270 sub routine (these members are described above). It accepts as
271 arguments a number of things, in key => value format, including
272 (potentially) a parser and a producer (they are passed directly to the
273 "parser" and "producer" methods).
274 Here is how the parameter list to "translate" is parsed:
276 • 1 argument means it's the data to be parsed; which could be a
278 string (filename) or a reference to a scalar (a string stored in
279 memory), or a reference to a hash, which is parsed as being more
280 than one argument (see next section).
281 # Parse the file /path/to/datafile
283 my $output = $tr->translate("/path/to/datafile");
284 # Parse the data contained in the string $data
286 my $output = $tr->translate(\$data);
287 • More than 1 argument means its a hash of things, and it might be
289 setting a parser, producer, or datasource (this key is named
290 "filename" or "file" if it's a file, or "data" for a SCALAR
291 reference.
292 # As above, parse /path/to/datafile, but with different producers
294 for my $prod ("MySQL", "XML", "Sybase") {
295 print $tr->translate(
296 producer => $prod,
297 filename => "/path/to/datafile",
298 );
299 }
300 # The filename hash key could also be:
302 datasource => \$data,
303 You get the idea.
305 filename, data
307 Using the "filename" method, the filename of the data to be parsed can
308 be set. This method can be used in conjunction with the "data" method,
309 below. If both the "filename" and "data" methods are invoked as
310 mutators, the data set in the "data" method is used.
311 $tr->filename("/my/data/files/create.sql");
313 or:
315 my $create_script = do {
317 local $/;
318 open CREATE, "/my/data/files/create.sql" or die $!;
319 <CREATE>;
320 };
321 $tr->data(\$create_script);
322 "filename" takes a string, which is interpreted as a filename. "data"
324 takes a reference to a string, which is used as the data to be parsed.
325 If a filename is set, then that file is opened and read when the
326 "translate" method is called, as long as the data instance variable is
327 not set.
328 schema
330 Returns the SQL::Translator::Schema object.
331 trace
333 Turns on/off the tracing option of Parse::RecDescent.
334 validate
336 Whether or not to validate the schema object after parsing and before
337 producing.
338 version
340 Returns the version of the SQL::Translator release.
341
343 See the included AUTHORS file:
344 <http://search.cpan.org/dist/SQL-Translator/AUTHORS>
345
347 If you are stuck with a problem or have doubts about a particular
348 approach do not hesitate to contact us via any of the following options
349 (the list is sorted by "fastest response time"):
350 • IRC: irc.perl.org#sql-translator
352 • Mailing list:
354 <http://lists.scsys.co.uk/mailman/listinfo/dbix-class>
355 • RT Bug Tracker:
357 <https://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Translator>
358
360 Contributions are always welcome, in all usable forms (we especially
361 welcome documentation improvements). The delivery methods include git-
362 or unified-diff formatted patches, GitHub pull requests, or plain bug
363 reports either via RT or the Mailing list. Contributors are generally
364 granted access to the official repository after their first several
365 patches pass successful review. Don't hesitate to contact us with any
366 further questions you may have.
367 This project is maintained in a git repository. The code and related
369 tools are accessible at the following locations:
370 • Official repo:
372 <git://git.shadowcat.co.uk/dbsrgits/SQL-Translator.git>
373 • Official gitweb:
375 <http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Translator.git>
376 • GitHub mirror: <https://github.com/dbsrgits/SQL-Translator>
378 • Authorized committers:
380 <ssh://dbsrgits@git.shadowcat.co.uk/sql-translator.git>
381 • Travis-CI log:
383 <https://travis-ci.org/dbsrgits/sql-translator/builds>
384
386 Copyright 2012 the SQL::Translator authors, as listed in "AUTHORS".
387
389 This library is free software and may be distributed under the same
390 terms as Perl 5 itself.
391
393 If you find this module useful, please use
394 <http://cpanratings.perl.org/rate/?distribution=SQL-Translator> to rate
395 it.
396
398 perl, SQL::Translator::Parser, SQL::Translator::Producer,
399 Parse::RecDescent, GD, GraphViz, Text::RecordParser, Class::DBI,
400 XML::Writer.
401perl v5.32.1 2021-01-27 SQL::Translator(3)