1SQL::Translator(3)    User Contributed Perl Documentation   SQL::Translator(3)
2
3
4

NAME

6       SQL::Translator - manipulate structured data definitions (SQL and more)
7

SYNOPSIS

9         use SQL::Translator;
10
11         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
34         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
41         print $output;
42

DESCRIPTION

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
48       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

CONSTRUCTOR

62   new
63       The constructor is called "new", and accepts a optional hash of
64       options.  Valid options are:
65
66       •   parser / from
67
68       •   parser_args
69
70       •   producer / to
71
72       •   producer_args
73
74       •   filters
75
76       •   filename / file
77
78       •   data
79
80       •   debug
81
82       •   add_drop_table
83
84       •   quote_identifiers
85
86       •   quote_table_names (DEPRECATED)
87
88       •   quote_field_names (DEPRECATED)
89
90       •   no_comments
91
92       •   trace
93
94       •   validate
95
96       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

METHODS

101   add_drop_table
102       Toggles whether or not to add "DROP TABLE" statements just before the
103       create definitions.
104
105   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
110   quote_table_names
111       DEPRECATED - A legacy proxy to "quote_identifiers"
112
113   quote_field_names
114       DEPRECATED - A legacy proxy to "quote_identifiers"
115
116   no_comments
117       Toggles whether to print comments in the output.  Accepts a true or
118       false value, returns the current value.
119
120   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
132       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
146         my $tr = SQL::Translator->new;
147
148         # This will invoke My::Groovy::Producer::produce($tr, $data)
149         $tr->producer("My::Groovy::Producer");
150
151         # This will invoke SQL::Translator::Producer::Sybase::produce($tr, $data)
152         $tr->producer("Sybase");
153
154         # 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
159         # This will invoke the referenced subroutine directly, as
160         # $subref->($tr, $data);
161         $tr->producer(\&my_producer);
162
163       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
168       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
172         sub My::Random::producer {
173             my ($tr, $data) = @_;
174             my $pr_args = $tr->producer_args();
175
176             # $pr_args is a hashref.
177
178       Extra data passed to the "producer" method is passed to
179       "producer_args":
180
181         $tr->producer("xSV", delimiter => ',\s*');
182
183         # In SQL::Translator::Producer::xSV:
184         my $args = $tr->producer_args;
185         my $delimiter = $args->{'delimiter'}; # value is ,\s*
186
187   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
195         # Invokes SQL::Translator::Parser::MySQL::parse()
196         $tr->parser("MySQL");
197
198         # Invokes My::Groovy::Parser::parse()
199         $tr->parser("My::Groovy::Parser");
200
201         # 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
208       There is also "parser_type" and "parser_args", which perform
209       analogously to "producer_type" and "producer_args"
210
211   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
219       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
226       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
229        $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
240       Although you normally set them in the constructor, which calls through
241       to filters. i.e.
242
243         my $translator  = SQL::Translator->new(
244             ...
245             filters => [
246                 sub { ... },
247                 [ "Names", table => 'lc' ],
248             ],
249             ...
250         );
251
252       See t/36-filters.t for more examples.
253
254       Multiple set calls to filters are cumulative with new filters added to
255       the end of the current list.
256
257       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
260   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
267   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
275       Here is how the parameter list to "translate" is parsed:
276
277       •   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
282             # Parse the file /path/to/datafile
283             my $output = $tr->translate("/path/to/datafile");
284
285             # Parse the data contained in the string $data
286             my $output = $tr->translate(\$data);
287
288       •   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
293             # 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
301             # The filename hash key could also be:
302                 datasource => \$data,
303
304           You get the idea.
305
306   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
312           $tr->filename("/my/data/files/create.sql");
313
314       or:
315
316           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
323       "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
329   schema
330       Returns the SQL::Translator::Schema object.
331
332   trace
333       Turns on/off the tracing option of Parse::RecDescent.
334
335   validate
336       Whether or not to validate the schema object after parsing and before
337       producing.
338
339   version
340       Returns the version of the SQL::Translator release.
341

AUTHORS

343       See the included AUTHORS file:
344       <http://search.cpan.org/dist/SQL-Translator/AUTHORS>
345

GETTING HELP/SUPPORT

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
351       •   IRC: irc.perl.org#sql-translator
352
353       •   Mailing list:
354           <http://lists.scsys.co.uk/mailman/listinfo/dbix-class>
355
356       •   RT Bug Tracker:
357           <https://rt.cpan.org/NoAuth/Bugs.html?Dist=SQL-Translator>
358

HOW TO CONTRIBUTE

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
368       This project is maintained in a git repository. The code and related
369       tools are accessible at the following locations:
370
371       •   Official repo:
372           <git://git.shadowcat.co.uk/dbsrgits/SQL-Translator.git>
373
374       •   Official gitweb:
375           <http://git.shadowcat.co.uk/gitweb/gitweb.cgi?p=dbsrgits/SQL-Translator.git>
376
377       •   GitHub mirror: <https://github.com/dbsrgits/SQL-Translator>
378
379       •   Authorized committers:
380           <ssh://dbsrgits@git.shadowcat.co.uk/sql-translator.git>
381
382       •   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

LICENSE

389       This library is free software and may be distributed under the same
390       terms as Perl 5 itself.
391

PRAISE

393       If you find this module useful, please use
394       <http://cpanratings.perl.org/rate/?distribution=SQL-Translator> to rate
395       it.
396

SEE ALSO

398       perl, SQL::Translator::Parser, SQL::Translator::Producer,
399       Parse::RecDescent, GD, GraphViz, Text::RecordParser, Class::DBI,
400       XML::Writer.
401
402
403
404perl v5.36.0                      2023-02-27                SQL::Translator(3)
Impressum