6 sqlt - convert SQL schema using SQL::Translator
14 For a list of all parsers and producers:
18 To translate a schema:
20 sqlt -f|--from|--parser MySQL
21 -t|--to|--producer Oracle
27 -d|--debug Print debug info
28 -v|--validate Validate the schema
29 --version Show the version of SQL::Translator
30 --trace Print parser trace info
31 --show-warnings Print warnings to STDERR
33 General Parser Options:
35 --skip Comma-separated list of tables to skip (only implemented in some parsers)
36 --ignore_opts Comma-separated list of table options to ignore
40 --dsn DSN for connecting to database
41 (see also --use-same-auth below)
42 --db-user Database user
43 --db-password Database password
47 --fs The field separator
48 --rs The record separator
49 --no-trim Don't trim whitespace on fields
50 --no-scan Don't scan fields for data types and sizes
54 --mysql-parser-version Target MySQL parser version for dealing with
55 /*! comments; default = 30000
57 MySQL Producer Options:
59 --mysql-version MySQL server version
61 General Producer Options
63 --producer-db-user Database user for producer
64 --producer-db-pass Database password for producer
65 --producer-dsn DSN for producer
66 --use-same-auth Use these DSN, user, password for producer output
70 --add-drop-table Add 'DROP TABLE' statements before creates
71 --quote-table-names Quote all table names in statements
72 --quote-field-names Qjuote all field names in statements
73 --no-comments Don't include comments in SQL output
75 PostgreSQL Producer Options:
77 --postgres-version PostgreSQL server version
79 Diagram Producer Options:
81 --imap-file Filename to put image map data
82 --imap-url URL to use for image map
84 Dumper Producer Options:
86 --skip Comma-separated list of tables to skip
87 --skiplike Regex for tables to skip
88 --add-truncate Add "TRUNCATE TABLE" statements for each table
90 HTML/POD Producer Options:
92 --pretty Use CGI::Pretty for the output
93 --title Title of schema
95 TTSchema Producer Options:
97 --template The path to the template
98 --tt-var var=value Pass extra variables to the template
99 --tt-conf option=value Pass extra config options to Template
101 XML-SQLFairy Producer Options:
103 --add-prefix Use an explicit namespace prefix of 'sqlf:'
104 --prefix=<p> Use the namespace prefix given as argument.
105 --no-newlines Write the XML as a single line.
106 --indent=<n> Use <n> characters of whitespace to indent the XML.
108 ClassDBI Producer Options:
110 --package Base package name for Class::DBI modules.
114 This script is part of the SQL Fairy project. It will try to convert
115 any source file for which it has a grammar into any format for which
118 If using "show-warnings," be sure to redirect STDERR to a separate file.
119 In bash, you could do this:
121 $ sql_translator.pl -f MySQL -t PostgreSQL --show-warnings \
124 You can specify a parser or producer located in any module that Perl
125 knows about, allowing you to easily substitute your own.
129 # -------------------------------------------------------------------
137 use vars qw( $VERSION );
140 my $from; # the original database
141 my $to; # the destination database
142 my $help; # show POD and bail
143 my $stdin; # whether to read STDIN for create script
144 my $no_comments; # whether to put comments in out file
145 my $show_warnings; # whether to show warnings from SQL::Translator
146 my $add_drop_table; # whether to add "DROP table" statements
147 my $quote_table_names; # whether to quote table names
148 my $quote_field_names; # whether to quote field names
149 my $debug; # whether to print debug info
150 my $trace; # whether to print parser trace
151 my $list; # list all parsers and producers
152 my $no_trim; # don't trim whitespace on xSV fields
153 my $no_scan; # don't scan xSV fields for data types and sizes
154 my $field_separator; # for xSV files
155 my $record_separator; # for xSV files
156 my $validate; # whether to validate the parsed document
157 my $imap_file; # filename where to place image map coords
158 my $imap_url; # URL to use in making image map
159 my $pretty; # use CGI::Pretty instead of CGI (HTML producer)
160 my $template; # template to pass to TTSchema producer
161 my %tt_vars; # additional template vars to pass the TTSchema producer
162 my %tt_conf; # additional template conf to pass the TTSchema producer
163 my $title; # title for HTML/POD producer
164 my $add_prefix; # Use explicit namespace prefix (XML producer)
165 my $prefix; # Set explicit namespace prefix (XML producer)
166 my $newlines; # Add newlines around tags (XML producer)
167 my $indent; # Number of indent chars for XML
168 my $package_name; # Base class name for ClassDBI
169 my $use_same_auth =0; # producer uses same DSN, user, password as parser
170 my $dsn; # DBI parser
171 my $db_user; # DBI parser
172 my $db_password; # DBI parser
173 my $show_version; # Show version and exit script
177 my $producer_db_user; # DSN for producer (e.g. Dumper, ClassDBI)
178 my $producer_db_password; # db_pass "
179 my $producer_dsn; # db_user "
181 my $mysql_parser_version; # MySQL parser arg for /*! comments
182 my $postgres_version; # PostgreSQL version
183 my $mysql_version; # MySQL version
186 'add-drop-table' => \$add_drop_table,
187 'quote-table-names|quote_table_names' => \$quote_table_names,
188 'quote-field-names|quote_field_names' => \$quote_field_names,
189 'd|debug' => \$debug,
190 'f|from|parser:s' => \$from,
191 'fs:s' => \$field_separator,
193 'imap-file:s' => \$imap_file,
194 'imap-url:s' => \$imap_url,
195 't|to|producer:s' => \$to,
197 'pretty!' => \$pretty,
198 'no-comments' => \$no_comments,
199 'no-scan' => \$no_scan,
200 'no-trim' => \$no_trim,
201 'rs:s' => \$record_separator,
202 'show-warnings' => \$show_warnings,
203 'template:s' => \$template,
204 'tt-var=s' => \%tt_vars,
205 'tt-conf=s' => \%tt_conf,
206 'title:s' => \$title,
208 'v|validate' => \$validate,
210 'db-user:s' => \$db_user,
211 'db-password:s' => \$db_password,
212 'producer-dsn:s' => \$producer_dsn,
213 'producer-db-user:s'=> \$producer_db_user,
214 'producer-db-pass:s'=> \$producer_db_password,
216 'skiplike:s' => \$skiplike,
217 'ignore_opts:s' => \$ignore_opts,
218 'add_truncate' => \$add_truncate,
219 'add-prefix' => \$add_prefix,
220 'prefix:s' => \$prefix,
221 'indent:s' => \$indent,
222 'newlines!' => \$newlines,
223 'package=s' => \$package_name,
224 'use-same-auth' => \$use_same_auth,
225 'version' => \$show_version,
226 'mysql-parser-version=i' => \$mysql_parser_version,
227 'postgres-version=f' => \$postgres_version,
228 'mysql-version=f' => \$mysql_version,
231 if ($use_same_auth) {
232 $producer_dsn = $dsn;
233 $producer_db_user = $db_user;
234 $producer_db_password = $db_password;
238 ( !defined $from && defined $dsn )
245 my @files = @ARGV; # source files
247 if ( defined($from) && $from eq 'DBI' ) {
255 pod2usage(1) if $help;
257 if ( $show_version ) {
258 print "SQL::Translator v", $SQL::Translator::VERSION, "\n";
262 my $translator = SQL::Translator->new(
263 debug => $debug || 0,
264 trace => $trace || 0,
265 no_comments => $no_comments || 0,
266 show_warnings => $show_warnings || 0,
267 add_drop_table => $add_drop_table || 0,
268 quote_table_names => defined $quote_table_names ? $quote_table_names : 1,
269 quote_field_names => defined $quote_field_names ? $quote_field_names : 1,
270 validate => $validate || 0,
272 trim_fields => $no_trim ? 0 : 1,
273 scan_fields => $no_scan ? 0 : 1,
274 field_separator => $field_separator,
275 record_separator => $record_separator,
278 db_password => $db_password,
279 mysql_parser_version => $mysql_parser_version,
281 ignore_opts => $ignore_opts,
284 imap_file => $imap_file,
285 imap_url => $imap_url,
288 tt_vars => \%tt_vars,
289 tt_conf => \%tt_conf,
291 dsn => $producer_dsn,
292 db_user => $producer_db_user,
293 db_password => $producer_db_password,
295 skiplike => $skiplike,
296 add_truncate => $add_truncate,
297 add_prefix => $add_prefix,
300 newlines => $newlines,
301 postgres_version => $postgres_version,
302 mysql_version => $mysql_version,
303 package_name => $package_name,
308 my @parsers = $translator->list_parsers;
309 my @producers = $translator->list_producers;
311 for ( @parsers, @producers ) {
312 if ( $_ =~ m/.+::(\w+)\.pm/ ) {
317 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
318 print "\nProducers:\n", map { "\t$_\n" } sort @producers;
323 pod2usage( msg => 'Please supply "from" and "to" arguments' )
326 $translator->parser($from);
327 $translator->producer($to);
329 for my $file (@files) {
331 ($file eq '-') ? (data => \*STDIN) :
332 ($file eq '!') ? (data => '') :
335 my $output = $translator->translate(@args) or die
336 "Error: " . $translator->error;
341 # ----------------------------------------------------
342 # It is not all books that are as dull as their readers.
343 # Henry David Thoreau
344 # ----------------------------------------------------
350 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
351 darren chamberlain E<lt>darren@cpan.orgE<gt>.
355 SQL::Translator, L<http://sqlfairy.sourceforge.net>.