Added a better quote; quit putting FKs at field level (only at table); fixed
[dbsrgits/SQL-Translator.git] / README
CommitLineData
46d3d648 1NAME
2 SQL::Translator - convert schema from one database to another
3
4SYNOPSIS
5 use SQL::Translator;
6
7 my $translator = SQL::Translator->new(
2e1b1775 8 debug => 1, # Print debug info
9 trace => 0, # Print Parse::RecDescent trace
10 no_comments => 0, # Don't include comments in output
11 show_warnings => 0, # Print name mutations, conflicts
12 add_drop_table => 1, # Add "drop table" statements
d9934baa 13
14 # Make all table names CAPS in producers which support this option
15 format_table_name => sub {my $tablename = shift; return uc($tablename)},
16
17 # Null-op formatting, only here for documentation's sake
18 format_package_name => sub {return shift},
19 format_fk_name => sub {return shift},
20 format_pk_name => sub {return shift},
46d3d648 21 );
22
23 my $output = $translator->translate(
24 from => "MySQL",
25 to => "Oracle",
d9934baa 26 # Or an arrayref of filenames, i.e. [ $file1, $file2, $file3 ]
27 filename => $file,
46d3d648 28 ) or die $translator->error;
29
30 print $output;
31
32DESCRIPTION
33 This module attempts to simplify the task of converting one database
34 create syntax to another through the use of Parsers (which understand
35 the source format) and Producers (which understand the destination
36 format). The idea is that any Parser can be used with any Producer in
37 the conversion process. So, if you wanted Postgres-to-Oracle, you would
38 use the Postgres parser and the Oracle producer.
39
40CONSTRUCTOR
d9934baa 41 The constructor is called "new", and accepts a optional hash of options.
46d3d648 42 Valid options are:
43
2e1b1775 44 * parser / from
45
46 * parser_args
47
48 * producer / to
49
50 * producer_args
51
52 * filename / file
53
54 * data
55
56 * debug
57
46d3d648 58 All options are, well, optional; these attributes can be set via
59 instance methods. Internally, they are; no (non-syntactical) advantage
60 is gained by passing options to the constructor.
61
62METHODS
63 add_drop_table
64
65 Toggles whether or not to add "DROP TABLE" statements just before the
66 create definitions.
67
68 custom_translate
69
70 Allows the user to override default translation of fields. For example,
71 if a MySQL "text" field would normally be converted to a "long" for
72 Oracle, the user could specify to change it to a "CLOB." Accepts a
73 hashref where keys are the "from" value and values are the "to," returns
74 the current value of the field.
75
76 no_comments
77
78 Toggles whether to print comments in the output. Accepts a true or false
79 value, returns the current value.
80
81 producer
82
d9934baa 83 The "producer" method is an accessor/mutator, used to retrieve or define
46d3d648 84 what subroutine is called to produce the output. A subroutine defined as
85 a producer will be invoked as a function (*not a method*) and passed 2
2e1b1775 86 parameters: its container "SQL::Translator" instance and a data
87 structure. It is expected that the function transform the data structure
88 to a string. The "SQL::Transformer" instance is provided for
89 informational purposes; for example, the type of the parser can be
d9934baa 90 retrieved using the "parser_type" method, and the "error" and "debug"
91 methods can be called when needed.
46d3d648 92
93 When defining a producer, one of several things can be passed in: A
d9934baa 94 module name (e.g., "My::Groovy::Producer"), a module name relative to
95 the "SQL::Translator::Producer" namespace (e.g., "MySQL"), a module name
96 and function combination ("My::Groovy::Producer::transmogrify"), or a
46d3d648 97 reference to an anonymous subroutine. If a full module name is passed in
98 (for the purposes of this method, a string containing "::" is considered
99 to be a module name), it is treated as a package, and a function called
2e1b1775 100 "produce" will be invoked: "$modulename::produce". If $modulename cannot
46d3d648 101 be loaded, the final portion is stripped off and treated as a function.
102 In other words, if there is no file named
2e1b1775 103 My/Groovy/Producer/transmogrify.pm, "SQL::Translator" will attempt to
d9934baa 104 load My/Groovy/Producer.pm and use "transmogrify" as the name of the
2e1b1775 105 function, instead of the default "produce".
46d3d648 106
107 my $tr = SQL::Translator->new;
108
109 # This will invoke My::Groovy::Producer::produce($tr, $data)
110 $tr->producer("My::Groovy::Producer");
111
112 # This will invoke SQL::Translator::Producer::Sybase::produce($tr, $data)
113 $tr->producer("Sybase");
114
115 # This will invoke My::Groovy::Producer::transmogrify($tr, $data),
116 # assuming that My::Groovy::Producer::transmogrify is not a module
117 # on disk.
118 $tr->producer("My::Groovy::Producer::transmogrify");
119
120 # This will invoke the referenced subroutine directly, as
121 # $subref->($tr, $data);
122 $tr->producer(\&my_producer);
123
d9934baa 124 There is also a method named "producer_type", which is a string
125 containing the classname to which the above "produce" function belongs.
126 In the case of anonymous subroutines, this method returns the string
127 "CODE".
46d3d648 128
d9934baa 129 Finally, there is a method named "producer_args", which is both an
46d3d648 130 accessor and a mutator. Arbitrary data may be stored in name => value
131 pairs for the producer subroutine to access:
132
133 sub My::Random::producer {
134 my ($tr, $data) = @_;
135 my $pr_args = $tr->producer_args();
136
137 # $pr_args is a hashref.
138
d9934baa 139 Extra data passed to the "producer" method is passed to "producer_args":
46d3d648 140
141 $tr->producer("xSV", delimiter => ',\s*');
142
143 # In SQL::Translator::Producer::xSV:
144 my $args = $tr->producer_args;
145 my $delimiter = $args->{'delimiter'}; # value is ,\s*
146
147 parser
148
d9934baa 149 The "parser" method defines or retrieves a subroutine that will be
150 called to perform the parsing. The basic idea is the same as that of
151 "producer" (see above), except the default subroutine name is "parse",
152 and will be invoked as "$module_name::parse($tr, $data)". Also, the
153 parser subroutine will be passed a string containing the entirety of the
154 data to be parsed.
46d3d648 155
156 # Invokes SQL::Translator::Parser::MySQL::parse()
157 $tr->parser("MySQL");
158
159 # Invokes My::Groovy::Parser::parse()
160 $tr->parser("My::Groovy::Parser");
161
162 # Invoke an anonymous subroutine directly
163 $tr->parser(sub {
164 my $dumper = Data::Dumper->new([ $_[1] ], [ "SQL" ]);
165 $dumper->Purity(1)->Terse(1)->Deepcopy(1);
166 return $dumper->Dump;
167 });
168
d9934baa 169 There is also "parser_type" and "parser_args", which perform analogously
170 to "producer_type" and "producer_args"
46d3d648 171
172 show_warnings
173
174 Toggles whether to print warnings of name conflicts, identifier
175 mutations, etc. Probably only generated by producers to let the user
176 know when something won't translate very smoothly (e.g., MySQL "enum"
177 fields into Oracle). Accepts a true or false value, returns the current
178 value.
179
180 translate
181
d9934baa 182 The "translate" method calls the subroutines referenced by the "parser"
183 and "producer" data members (described above). It accepts as arguments a
46d3d648 184 number of things, in key => value format, including (potentially) a
d9934baa 185 parser and a producer (they are passed directly to the "parser" and
186 "producer" methods).
46d3d648 187
d9934baa 188 Here is how the parameter list to "translate" is parsed:
46d3d648 189
190 * 1 argument means it's the data to be parsed; which could be a string
2e1b1775 191 (filename) or a reference to a scalar (a string stored in memory),
192 or a reference to a hash, which is parsed as being more than one
46d3d648 193 argument (see next section).
194
195 # Parse the file /path/to/datafile
196 my $output = $tr->translate("/path/to/datafile");
197
198 # Parse the data contained in the string $data
199 my $output = $tr->translate(\$data);
200
201 * More than 1 argument means its a hash of things, and it might be
202 setting a parser, producer, or datasource (this key is named
203 "filename" or "file" if it's a file, or "data" for a SCALAR
204 reference.
205
206 # As above, parse /path/to/datafile, but with different producers
207 for my $prod ("MySQL", "XML", "Sybase") {
208 print $tr->translate(
209 producer => $prod,
210 filename => "/path/to/datafile",
211 );
212 }
213
214 # The filename hash key could also be:
215 datasource => \$data,
216
217 You get the idea.
218
219 filename, data
220
d9934baa 221 Using the "filename" method, the filename of the data to be parsed can
222 be set. This method can be used in conjunction with the "data" method,
223 below. If both the "filename" and "data" methods are invoked as
224 mutators, the data set in the "data" method is used.
46d3d648 225
226 $tr->filename("/my/data/files/create.sql");
227
228 or:
229
230 my $create_script = do {
231 local $/;
232 open CREATE, "/my/data/files/create.sql" or die $!;
233 <CREATE>;
234 };
235 $tr->data(\$create_script);
236
d9934baa 237 "filename" takes a string, which is interpreted as a filename. "data"
238 takes a reference to a string, which is used as the data to be parsed.
239 If a filename is set, then that file is opened and read when the
240 "translate" method is called, as long as the data instance variable is
241 not set.
46d3d648 242
243 trace
244
245 Turns on/off the tracing option of Parse::RecDescent.
246
247AUTHORS
248 Ken Y. Clark, <kclark@cpan.org>, darren chamberlain <darren@cpan.org>,
249 Chris Mungall <cjm@fruitfly.org>, Allen Day
250 <allenday@users.sourceforge.net>
251
252COPYRIGHT
253 This program is free software; you can redistribute it and/or modify it
254 under the terms of the GNU General Public License as published by the
255 Free Software Foundation; version 2.
256
257 This program is distributed in the hope that it will be useful, but
258 WITHOUT ANY WARRANTY; without even the implied warranty of
259 MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General
260 Public License for more details.
261
262 You should have received a copy of the GNU General Public License along
263 with this program; if not, write to the Free Software Foundation, Inc.,
264 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA
265
2e1b1775 266BUGS
267 Please use http://rt.cpan.org/ for reporting bugs.
268
46d3d648 269SEE ALSO
270 the perl manpage, the SQL::Translator::Parser manpage, the
271 SQL::Translator::Producer manpage, the Parse::RecDescent manpage
272