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
46 general 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-
50 specific SQL table definitions into other formats, such as other
51 vendor-specific SQL, ER diagrams, documentation (POD and HTML), XML,
52 and 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
55 separation of the code into parsers and producers with an object model
56 in between, it's possible to combine any parser with any producer, to
57 plug in custom parsers or producers, or to manipulate the parsed data
58 via the built-in object model. Presently only the definition parts of
59 SQL 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
101 create definitions.
102 quote_table_names
104 Toggles whether or not to quote table names with " in DROP and CREATE
105 statements. The default (true) is to quote them.
106 quote_field_names
108 Toggles whether or not to quote field names with " in most statements.
109 The default (true), is to quote them.
110 no_comments
112 Toggles whether to print comments in the output. Accepts a true or
113 false value, returns the current value.
114 producer
116 The "producer" method is an accessor/mutator, used to retrieve or
117 define what subroutine is called to produce the output. A subroutine
118 defined as a producer will be invoked as a function (not a method) and
119 passed its container "SQL::Translator" instance, which it should call
120 the "schema" method on, to get the "SQL::Translator::Schema" generated
121 by the parser. It is expected that the function transform the schema
122 structure to a string. The "SQL::Translator" instance is also useful
123 for informational purposes; for example, the type of the parser can be
124 retrieved using the "parser_type" method, and the "error" and "debug"
125 methods can be called when needed.
126 When defining a producer, one of several things can be passed in: A
128 module name (e.g., "My::Groovy::Producer"), a module name relative to
129 the "SQL::Translator::Producer" namespace (e.g., "MySQL"), a module
130 name and function combination ("My::Groovy::Producer::transmogrify"),
131 or a reference to an anonymous subroutine. If a full module name is
132 passed in (for the purposes of this method, a string containing "::" is
133 considered to be a module name), it is treated as a package, and a
134 function called "produce" will be invoked: $modulename::produce. If
135 $modulename cannot be loaded, the final portion is stripped off and
136 treated as a function. In other words, if there is no file named
137 My/Groovy/Producer/transmogrify.pm, "SQL::Translator" will attempt to
138 load My/Groovy/Producer.pm and use "transmogrify" as the name of the
139 function, instead of the default "produce".
140 my $tr = SQL::Translator->new;
142 # This will invoke My::Groovy::Producer::produce($tr, $data)
144 $tr->producer("My::Groovy::Producer");
145 # This will invoke SQL::Translator::Producer::Sybase::produce($tr, $data)
147 $tr->producer("Sybase");
148 # This will invoke My::Groovy::Producer::transmogrify($tr, $data),
150 # assuming that My::Groovy::Producer::transmogrify is not a module
151 # on disk.
152 $tr->producer("My::Groovy::Producer::transmogrify");
153 # This will invoke the referenced subroutine directly, as
155 # $subref->($tr, $data);
156 $tr->producer(\&my_producer);
157 There is also a method named "producer_type", which is a string
159 containing the classname to which the above "produce" function belongs.
160 In the case of anonymous subroutines, this method returns the string
161 "CODE".
162 Finally, there is a method named "producer_args", which is both an
164 accessor and a mutator. Arbitrary data may be stored in name => value
165 pairs for the producer subroutine to access:
166 sub My::Random::producer {
168 my ($tr, $data) = @_;
169 my $pr_args = $tr->producer_args();
170 # $pr_args is a hashref.
172 Extra data passed to the "producer" method is passed to
174 "producer_args":
175 $tr->producer("xSV", delimiter => ',\s*');
177 # In SQL::Translator::Producer::xSV:
179 my $args = $tr->producer_args;
180 my $delimiter = $args->{'delimiter'}; # value is ,\s*
181 parser
183 The "parser" method defines or retrieves a subroutine that will be
184 called to perform the parsing. The basic idea is the same as that of
185 "producer" (see above), except the default subroutine name is "parse",
186 and will be invoked as "$module_name::parse($tr, $data)". Also, the
187 parser subroutine will be passed a string containing the entirety of
188 the data to be parsed.
189 # Invokes SQL::Translator::Parser::MySQL::parse()
191 $tr->parser("MySQL");
192 # Invokes My::Groovy::Parser::parse()
194 $tr->parser("My::Groovy::Parser");
195 # Invoke an anonymous subroutine directly
197 $tr->parser(sub {
198 my $dumper = Data::Dumper->new([ $_[1] ], [ "SQL" ]);
199 $dumper->Purity(1)->Terse(1)->Deepcopy(1);
200 return $dumper->Dump;
201 });
202 There is also "parser_type" and "parser_args", which perform
204 analogously to "producer_type" and "producer_args"
205 filters
207 Set or retreive the filters to run over the schema during the
208 translation, before the producer creates its output. Filters are sub
209 routines called, in order, with the schema object to filter as the 1st
210 arg and a hash of options (passed as a list) for the rest of the args.
211 They are free to do whatever they want to the schema object, which will
212 be handed to any following filters, then used by the producer.
213 Filters are set as an array, which gives the order they run in. Like
215 parsers and producers, they can be defined by a module name, a module
216 name relative to the SQL::Translator::Filter namespace, a module name
217 and function name together or a reference to an anonymous subroutine.
218 When using a module name a function called "filter" will be invoked in
219 that package to do the work.
220 To pass args to the filter set it as an array ref with the 1st value
222 giving the filter (name or sub) and the rest its args. e.g.
223 $tr->filters(
225 sub {
226 my $schema = shift;
227 # Do stuff to schema here!
228 },
229 DropFKeys,
230 [ "Names", table => 'lc' ],
231 [ "Foo", foo => "bar", hello => "world" ],
232 [ "Filter5" ],
233 );
234 Although you normally set them in the constructor, which calls through
236 to filters. i.e.
237 my $translator = SQL::Translator->new(
239 ...
240 filters => [
241 sub { ... },
242 [ "Names", table => 'lc' ],
243 ],
244 ...
245 );
246 See t/36-filters.t for more examples.
248 Multiple set calls to filters are cumulative with new filters added to
250 the end of the current list.
251 Returns the filters as a list of array refs, the 1st value being a
253 reference to the filter sub and the rest its args.
254 show_warnings
256 Toggles whether to print warnings of name conflicts, identifier
257 mutations, etc. Probably only generated by producers to let the user
258 know when something won't translate very smoothly (e.g., MySQL "enum"
259 fields into Oracle). Accepts a true or false value, returns the
260 current value.
261 translate
263 The "translate" method calls the subroutine referenced by the "parser"
264 data member, then calls any "filters" and finally calls the "producer"
265 sub routine (these members are described above). It accepts as
266 arguments a number of things, in key => value format, including
267 (potentially) a parser and a producer (they are passed directly to the
268 "parser" and "producer" methods).
269 Here is how the parameter list to "translate" is parsed:
271 · 1 argument means it's the data to be parsed; which could be a
273 string (filename) or a reference to a scalar (a string stored in
274 memory), or a reference to a hash, which is parsed as being more
275 than one argument (see next section).
276 # Parse the file /path/to/datafile
278 my $output = $tr->translate("/path/to/datafile");
279 # Parse the data contained in the string $data
281 my $output = $tr->translate(\$data);
282 · More than 1 argument means its a hash of things, and it might be
284 setting a parser, producer, or datasource (this key is named
285 "filename" or "file" if it's a file, or "data" for a SCALAR
286 reference.
287 # As above, parse /path/to/datafile, but with different producers
289 for my $prod ("MySQL", "XML", "Sybase") {
290 print $tr->translate(
291 producer => $prod,
292 filename => "/path/to/datafile",
293 );
294 }
295 # The filename hash key could also be:
297 datasource => \$data,
298 You get the idea.
300 filename, data
302 Using the "filename" method, the filename of the data to be parsed can
303 be set. This method can be used in conjunction with the "data" method,
304 below. If both the "filename" and "data" methods are invoked as
305 mutators, the data set in the "data" method is used.
306 $tr->filename("/my/data/files/create.sql");
308 or:
310 my $create_script = do {
312 local $/;
313 open CREATE, "/my/data/files/create.sql" or die $!;
314 <CREATE>;
315 };
316 $tr->data(\$create_script);
317 "filename" takes a string, which is interpreted as a filename. "data"
319 takes a reference to a string, which is used as the data to be parsed.
320 If a filename is set, then that file is opened and read when the
321 "translate" method is called, as long as the data instance variable is
322 not set.
323 schema
325 Returns the SQL::Translator::Schema object.
326 trace
328 Turns on/off the tracing option of Parse::RecDescent.
329 validate
331 Whether or not to validate the schema object after parsing and before
332 producing.
333 version
335 Returns the version of the SQL::Translator release.
336
338 See the included AUTHORS file:
339 http://search.cpan.org/dist/SQL-Translator/AUTHORS
340 <http://search.cpan.org/dist/SQL-Translator/AUTHORS>
341 If you would like to contribute to the project, you can send patches to
343 the developers mailing list:
344 sqlfairy-developers@lists.sourceforge.net
346 Or send us a message (with your Sourceforge username) asking to be
348 added to the project and what you'd like to contribute.
349
351 This program is free software; you can redistribute it and/or modify it
352 under the terms of the GNU General Public License as published by the
353 Free Software Foundation; version 2.
354 This program is distributed in the hope that it will be useful, but
356 WITHOUT ANY WARRANTY; without even the implied warranty of
357 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
358 General Public License for more details.
359 You should have received a copy of the GNU General Public License along
361 with this program; if not, write to the Free Software Foundation, Inc.,
362 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
363
365 Please use <http://rt.cpan.org/> for reporting bugs.
366
368 If you find this module useful, please use
369 http://cpanratings.perl.org/rate/?distribution=SQL-Translator
370 <http://cpanratings.perl.org/rate/?distribution=SQL-Translator> to rate
371 it.
372
374 perl, SQL::Translator::Parser, SQL::Translator::Producer,
375 Parse::RecDescent, GD, GraphViz, Text::RecordParser, Class::DBI,
376 XML::Writer.
377perl v5.12.0 2010-02-24 SQL::Translator(3)