4 # -------------------------------------------------------------------
5 # $Id: sqlt,v 1.13 2004-03-09 19:11:30 kycl4rk Exp $
6 # -------------------------------------------------------------------
7 # Copyright (C) 2002-4 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 --trace Print parser trace info
50 --show-warnings Print warnings to STDERR
54 --dsn DSN for connecting to database
55 --db-user Database user
56 --db-password Database password
60 --fs The field separator
61 --rs The record separator
62 --no-trim Don't trim whitespace on fields
63 --no-scan Don't scan fields for data types and sizes
67 --add-drop-table Add 'DROP TABLE' statements before creates
68 --no-comments Don't include comments in SQL output
70 Diagram Producer Options:
72 --imap-file Filename to put image map data
73 --imap-url URL to use for image map
75 Dumper Producer Options:
77 --skip Comma-separated list of tables to skip
78 --skiplike Regex for tables to skip
79 --dumper-db-user Database user for dumper script
80 --dumper-db-pass Database password for dumper script
81 --dumper-dsn DSN for dumper script
82 --add-truncate Add "TRUNCATE TABLE" statements for each table
84 HTML/POD Producer Options:
86 --pretty Use CGI::Pretty for the output
87 --title Title of schema
89 TTSchema Producer Options:
91 --template The path to the template
93 XML-SQLFairy Producer Options:
95 --emit-empty-tags Print empty tags for attributes
96 --attrib-values Use attributes instead of tags for
97 values of the schema objects
101 This script is part of the SQL Fairy project. It will try to convert
102 any source file for which it has a grammar into any format for which
105 If using "show-warnings," be sure to redirect STDERR to a separate file.
106 In bash, you could do this:
108 $ sql_translator.pl -f MySQL -t PostgreSQL --show-warnings \
111 You can specify a parser or producer located in any module that Perl
112 knows about, allowing you to easily substitute your own.
116 # -------------------------------------------------------------------
123 use vars qw( $VERSION );
124 $VERSION = sprintf "%d.%02d", q$Revision: 1.13 $ =~ /(\d+)\.(\d+)/;
126 my $from; # the original database
127 my $to; # the destination database
128 my $help; # show POD and bail
129 my $stdin; # whether to read STDIN for create script
130 my $no_comments; # whether to put comments in out file
131 my $show_warnings; # whether to show warnings from SQL::Translator
132 my $add_drop_table; # whether to show warnings from SQL::Translator
133 my $debug; # whether to print debug info
134 my $trace; # whether to print parser trace
135 my $list; # list all parsers and producers
136 my $no_trim; # don't trim whitespace on xSV fields
137 my $no_scan; # don't scan xSV fields for data types and sizes
138 my $field_separator; # for xSV files
139 my $record_separator; # for xSV files
140 my $validate; # whether to validate the parsed document
141 my $imap_file; # filename where to place image map coords
142 my $imap_url; # URL to use in making image map
143 my $pretty; # use CGI::Pretty instead of CGI (HTML producer)
144 my $template; # template to pass to TTSchema producer
145 my $title; # title for HTML/POD producer
146 my $emit_empty_tags; # show empty XML tags
147 my $attrib_values; # use XML attributes instead of tags
148 my $dsn; # DBI parser
149 my $db_user; # DBI parser
150 my $db_password; # DBI parser
159 'add-drop-table' => \$add_drop_table,
160 'attrib-values' => \$attrib_values,
161 'd|debug' => \$debug,
162 'emit-empty-tags' => \$emit_empty_tags,
163 'f|from|parser:s' => \$from,
164 'fs:s' => \$field_separator,
166 'imap-file:s' => \$imap_file,
167 'imap-url:s' => \$imap_url,
168 't|to|producer:s' => \$to,
170 'pretty!' => \$pretty,
171 'no-comments' => \$no_comments,
172 'no-scan' => \$no_scan,
173 'no-trim' => \$no_trim,
174 'rs:s' => \$record_separator,
175 'show-warnings' => \$show_warnings,
176 'template:s' => \$template,
177 'title:s' => \$title,
179 'v|validate' => \$validate,
181 'db-user:s' => \$db_user,
182 'db-password:s' => \$db_password,
183 'dumper-dsn:s' => \$dumper_dsn,
184 'dumper-db-user:s' => \$dumper_db_user,
185 'dumper-db-pass:s' => \$dumper_db_pass,
187 'skiplike:s' => \$skiplike,
188 'add_truncate' => \$add_truncate,
191 my @files = @ARGV; # source files
193 if ( defined($from) && $from eq 'DBI' ) {
201 pod2usage(1) if $help;
203 my $translator = SQL::Translator->new(
204 debug => $debug || 0,
205 trace => $trace || 0,
206 no_comments => $no_comments || 0,
207 show_warnings => $show_warnings || 0,
208 add_drop_table => $add_drop_table || 0,
209 validate => $validate || 0,
211 trim_fields => $no_trim ? 0 : 1,
212 scan_fields => $no_scan ? 0 : 1,
213 field_separator => $field_separator,
214 record_separator => $record_separator,
217 db_password => $db_password,
220 imap_file => $imap_file,
221 imap_url => $imap_url,
225 emit_empty_tags => $emit_empty_tags,
226 attrib_values => $attrib_values,
228 db_user => $dumper_db_user,
229 db_password => $dumper_db_pass,
231 skiplike => $skiplike,
232 add_truncate => $add_truncate,
237 my @parsers = $translator->list_parsers;
238 my @producers = $translator->list_producers;
240 for ( @parsers, @producers ) {
241 if ( $_ =~ m/.+::(\w+)\.pm/ ) {
246 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
247 print "\nProducers:\n", map { "\t$_\n" } sort @producers;
252 pod2usage( msg => 'Please supply "from" and "to" arguments' )
255 $translator->parser($from);
256 $translator->producer($to);
258 for my $file (@files) {
260 ($file eq '-') ? (data => \*STDIN) :
261 ($file eq '!') ? (data => '') :
264 my $output = $translator->translate(@args) or die
265 "Error: " . $translator->error;
270 # ----------------------------------------------------
271 # It is not all books that are as dull as their readers.
272 # Henry David Thoreau
273 # ----------------------------------------------------
279 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>,
280 darren chamberlain E<lt>darren@cpan.orgE<gt>.
284 SQL::Translator, L<http://sqlfairy.sourceforge.net>.