4 # -------------------------------------------------------------------
6 # -------------------------------------------------------------------
7 # Copyright (C) 2002-2009 SQLFairy Authors
9 # This program is free software; you can redistribute it and/or
10 # modify it under the terms of the GNU General Public License as
11 # published by the Free Software Foundation; version 2.
13 # This program is distributed in the hope that it will be useful, but
14 # WITHOUT ANY WARRANTY; without even the implied warranty of
15 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
16 # General Public License for more details.
18 # You should have received a copy of the GNU General Public License
19 # along with this program; if not, write to the Free Software
20 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
22 # -------------------------------------------------------------------
26 sqlt - convert SQL schema using SQL::Translator
34 For a list of all parsers and producers:
38 To translate a schema:
40 sqlt -f|--from|--parser MySQL
41 -t|--to|--producer Oracle
47 -d|--debug Print debug info
48 -v|--validate Validate the schema
49 --version Show the version of SQL::Translator
50 --trace Print parser trace info
51 --show-warnings Print warnings to STDERR
53 General Parser Options:
55 --skip Comma-separated list of tables to skip (only implemented in some parsers)
56 --ignore_opts Comma-separated list of table options to ignore
60 --dsn DSN for connecting to database
61 (see also --use-same-auth below)
62 --db-user Database user
63 --db-password Database password
67 --fs The field separator
68 --rs The record separator
69 --no-trim Don't trim whitespace on fields
70 --no-scan Don't scan fields for data types and sizes
74 --mysql-parser-version Target MySQL parser version for dealing with
75 /*! comments; default = 30000
77 MySQL Producer Options:
79 --mysql-version MySQL server version
81 General Producer Options
83 --producer-db-user Database user for producer
84 --producer-db-pass Database password for producer
85 --producer-dsn DSN for producer
86 --use-same-auth Use these DSN, user, password for producer output
90 --add-drop-table Add 'DROP TABLE' statements before creates
91 --quote-table-names Quote all table names in statements
92 --quote-field-names Qjuote all field names in statements
93 --no-comments Don't include comments in SQL output
95 PostgreSQL Producer Options:
97 --postgres-version PostgreSQL server version
99 Diagram Producer Options:
101 --imap-file Filename to put image map data
102 --imap-url URL to use for image map
104 Dumper Producer Options:
106 --skip Comma-separated list of tables to skip
107 --skiplike Regex for tables to skip
108 --add-truncate Add "TRUNCATE TABLE" statements for each table
110 HTML/POD Producer Options:
112 --pretty Use CGI::Pretty for the output
113 --title Title of schema
115 TTSchema Producer Options:
117 --template The path to the template
118 --tt-var var=value Pass extra variables to the template
119 --tt-conf option=value Pass extra config options to Template
121 XML-SQLFairy Producer Options:
123 --add-prefix Use an explicit namespace prefix of 'sqlf:'
124 --prefix=<p> Use the namespace prefix given as argument.
125 --no-newlines Write the XML as a single line.
126 --indent=<n> Use <n> characters of whitespace to indent the XML.
128 ClassDBI Producer Options:
130 --package Base package name for Class::DBI modules.
134 This script is part of the SQL Fairy project. It will try to convert
135 any source file for which it has a grammar into any format for which
138 If using "show-warnings," be sure to redirect STDERR to a separate file.
139 In bash, you could do this:
141 $ sql_translator.pl -f MySQL -t PostgreSQL --show-warnings \
144 You can specify a parser or producer located in any module that Perl
145 knows about, allowing you to easily substitute your own.
149 # -------------------------------------------------------------------
156 use vars qw( $VERSION );
159 my $from; # the original database
160 my $to; # the destination database
161 my $help; # show POD and bail
162 my $stdin; # whether to read STDIN for create script
163 my $no_comments; # whether to put comments in out file
164 my $show_warnings; # whether to show warnings from SQL::Translator
165 my $add_drop_table; # whether to add "DROP table" statements
166 my $quote_table_names; # whether to quote table names
167 my $quote_field_names; # whether to quote field names
168 my $debug; # whether to print debug info
169 my $trace; # whether to print parser trace
170 my $list; # list all parsers and producers
171 my $no_trim; # don't trim whitespace on xSV fields
172 my $no_scan; # don't scan xSV fields for data types and sizes
173 my $field_separator; # for xSV files
174 my $record_separator; # for xSV files
175 my $validate; # whether to validate the parsed document
176 my $imap_file; # filename where to place image map coords
177 my $imap_url; # URL to use in making image map
178 my $pretty; # use CGI::Pretty instead of CGI (HTML producer)
179 my $template; # template to pass to TTSchema producer
180 my %tt_vars; # additional template vars to pass the TTSchema producer
181 my %tt_conf; # additional template conf to pass the TTSchema producer
182 my $title; # title for HTML/POD producer
183 my $add_prefix; # Use explicit namespace prefix (XML producer)
184 my $prefix; # Set explicit namespace prefix (XML producer)
185 my $newlines; # Add newlines around tags (XML producer)
186 my $indent; # Number of indent chars for XML
187 my $package_name; # Base class name for ClassDBI
188 my $use_same_auth =0; # producer uses same DSN, user, password as parser
189 my $dsn; # DBI parser
190 my $db_user; # DBI parser
191 my $db_password; # DBI parser
192 my $show_version; # Show version and exit script
196 my $producer_db_user; # DSN for producer (e.g. Dumper, ClassDBI)
197 my $producer_db_password; # db_pass "
198 my $producer_dsn; # db_user "
200 my $mysql_parser_version; # MySQL parser arg for /*! comments
201 my $postgres_version; # PostgreSQL version
202 my $mysql_version; # MySQL version
205 'add-drop-table' => \$add_drop_table,
206 'quote_table_names' => \$quote_table_names,
207 'quote_field_names' => \$quote_field_names,
208 'd|debug' => \$debug,
209 'f|from|parser:s' => \$from,
210 'fs:s' => \$field_separator,
212 'imap-file:s' => \$imap_file,
213 'imap-url:s' => \$imap_url,
214 't|to|producer:s' => \$to,
216 'pretty!' => \$pretty,
217 'no-comments' => \$no_comments,
218 'no-scan' => \$no_scan,
219 'no-trim' => \$no_trim,
220 'rs:s' => \$record_separator,
221 'show-warnings' => \$show_warnings,
222 'template:s' => \$template,
223 'tt-var=s' => \%tt_vars,
224 'tt-conf=s' => \%tt_conf,
225 'title:s' => \$title,
227 'v|validate' => \$validate,
229 'db-user:s' => \$db_user,
230 'db-password:s' => \$db_password,
231 'producer-dsn:s' => \$producer_dsn,
232 'producer-db-user:s'=> \$producer_db_user,
233 'producer-db-pass:s'=> \$producer_db_password,
235 'skiplike:s' => \$skiplike,
236 'ignore_opts:s' => \$ignore_opts,
237 'add_truncate' => \$add_truncate,
238 'add-prefix' => \$add_prefix,
239 'prefix:s' => \$prefix,
240 'indent:s' => \$indent,
241 'newlines!' => \$newlines,
242 'package=s' => \$package_name,
243 'use-same-auth' => \$use_same_auth,
244 'version' => \$show_version,
245 'mysql-parser-version=i' => \$mysql_parser_version,
246 'postgres-version=f' => \$postgres_version,
247 'mysql-version=f' => \$mysql_version,
250 if ($use_same_auth) {
251 $producer_dsn = $dsn;
252 $producer_db_user = $db_user;
253 $producer_db_password = $db_password;
256 $from = 'DBI' if !defined $from && defined $dsn;
257 my @files = @ARGV; # source files
259 if ( defined($from) && $from eq 'DBI' ) {
267 pod2usage(1) if $help;
269 if ( $show_version ) {
270 print "SQL::Translator v", $SQL::Translator::VERSION, "\n";
274 my $translator = SQL::Translator->new(
275 debug => $debug || 0,
276 trace => $trace || 0,
277 no_comments => $no_comments || 0,
278 show_warnings => $show_warnings || 0,
279 add_drop_table => $add_drop_table || 0,
280 quote_table_names => $quote_table_names || 1,
281 quote_field_names => $quote_field_names || 1,
282 validate => $validate || 0,
284 trim_fields => $no_trim ? 0 : 1,
285 scan_fields => $no_scan ? 0 : 1,
286 field_separator => $field_separator,
287 record_separator => $record_separator,
290 db_password => $db_password,
291 mysql_parser_version => $mysql_parser_version,
293 ignore_opts => $ignore_opts,
296 imap_file => $imap_file,
297 imap_url => $imap_url,
300 tt_vars => \%tt_vars,
301 tt_conf => \%tt_conf,
303 dsn => $producer_dsn,
304 db_user => $producer_db_user,
305 db_password => $producer_db_password,
307 skiplike => $skiplike,
308 add_truncate => $add_truncate,
309 add_prefix => $add_prefix,
312 newlines => $newlines,
313 postgres_version => $postgres_version,
314 mysql_version => $mysql_version,
315 package_name => $package_name,
320 my @parsers = $translator->list_parsers;
321 my @producers = $translator->list_producers;
323 for ( @parsers, @producers ) {
324 if ( $_ =~ m/.+::(\w+)\.pm/ ) {
329 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
330 print "\nProducers:\n", map { "\t$_\n" } sort @producers;
335 pod2usage( msg => 'Please supply "from" and "to" arguments' )
338 $translator->parser($from);
339 $translator->producer($to);
341 for my $file (@files) {
343 ($file eq '-') ? (data => \*STDIN) :
344 ($file eq '!') ? (data => '') :
347 my $output = $translator->translate(@args) or die
348 "Error: " . $translator->error;
353 # ----------------------------------------------------
354 # It is not all books that are as dull as their readers.
355 # Henry David Thoreau
356 # ----------------------------------------------------
362 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
363 darren chamberlain E<lt>darren@cpan.orgE<gt>.
367 SQL::Translator, L<http://sqlfairy.sourceforge.net>.