4 # -------------------------------------------------------------------
5 # $Id: sqlt,v 1.14 2004-07-08 20:37:53 grommit 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 --add-prefix Use an explicit namespace prefix of 'sqlf:'
96 --prefix=<p> Use the namespace prefix given as argument.
97 --no-newlines Write the XML as a single line.
98 --indent=<n> Use <n> characters of whitespace to indent the XML.
102 This script is part of the SQL Fairy project. It will try to convert
103 any source file for which it has a grammar into any format for which
106 If using "show-warnings," be sure to redirect STDERR to a separate file.
107 In bash, you could do this:
109 $ sql_translator.pl -f MySQL -t PostgreSQL --show-warnings \
112 You can specify a parser or producer located in any module that Perl
113 knows about, allowing you to easily substitute your own.
117 # -------------------------------------------------------------------
124 use vars qw( $VERSION );
125 $VERSION = sprintf "%d.%02d", q$Revision: 1.14 $ =~ /(\d+)\.(\d+)/;
127 my $from; # the original database
128 my $to; # the destination database
129 my $help; # show POD and bail
130 my $stdin; # whether to read STDIN for create script
131 my $no_comments; # whether to put comments in out file
132 my $show_warnings; # whether to show warnings from SQL::Translator
133 my $add_drop_table; # whether to show warnings from SQL::Translator
134 my $debug; # whether to print debug info
135 my $trace; # whether to print parser trace
136 my $list; # list all parsers and producers
137 my $no_trim; # don't trim whitespace on xSV fields
138 my $no_scan; # don't scan xSV fields for data types and sizes
139 my $field_separator; # for xSV files
140 my $record_separator; # for xSV files
141 my $validate; # whether to validate the parsed document
142 my $imap_file; # filename where to place image map coords
143 my $imap_url; # URL to use in making image map
144 my $pretty; # use CGI::Pretty instead of CGI (HTML producer)
145 my $template; # template to pass to TTSchema producer
146 my $title; # title for HTML/POD producer
147 my $add_prefix; # Use explicit namespace prefix (XML producer)
148 my $prefix; # Set explicit namespace prefix (XML producer)
149 my $newlines; # Add newlines around tags (XML producer)
150 my $indent; # Number of indent chars for XML
151 my $dsn; # DBI parser
152 my $db_user; # DBI parser
153 my $db_password; # DBI parser
162 'add-drop-table' => \$add_drop_table,
163 'd|debug' => \$debug,
164 'f|from|parser:s' => \$from,
165 'fs:s' => \$field_separator,
167 'imap-file:s' => \$imap_file,
168 'imap-url:s' => \$imap_url,
169 't|to|producer:s' => \$to,
171 'pretty!' => \$pretty,
172 'no-comments' => \$no_comments,
173 'no-scan' => \$no_scan,
174 'no-trim' => \$no_trim,
175 'rs:s' => \$record_separator,
176 'show-warnings' => \$show_warnings,
177 'template:s' => \$template,
178 'title:s' => \$title,
180 'v|validate' => \$validate,
182 'db-user:s' => \$db_user,
183 'db-password:s' => \$db_password,
184 'dumper-dsn:s' => \$dumper_dsn,
185 'dumper-db-user:s' => \$dumper_db_user,
186 'dumper-db-pass:s' => \$dumper_db_pass,
188 'skiplike:s' => \$skiplike,
189 'add_truncate' => \$add_truncate,
190 'add-prefix' => \$add_prefix,
191 'prefix:s' => \$prefix,
192 'indent:s' => \$indent,
193 'newlines!' => \$newlines,
196 my @files = @ARGV; # source files
198 if ( defined($from) && $from eq 'DBI' ) {
206 pod2usage(1) if $help;
208 my $translator = SQL::Translator->new(
209 debug => $debug || 0,
210 trace => $trace || 0,
211 no_comments => $no_comments || 0,
212 show_warnings => $show_warnings || 0,
213 add_drop_table => $add_drop_table || 0,
214 validate => $validate || 0,
216 trim_fields => $no_trim ? 0 : 1,
217 scan_fields => $no_scan ? 0 : 1,
218 field_separator => $field_separator,
219 record_separator => $record_separator,
222 db_password => $db_password,
225 imap_file => $imap_file,
226 imap_url => $imap_url,
231 db_user => $dumper_db_user,
232 db_password => $dumper_db_pass,
234 skiplike => $skiplike,
235 add_truncate => $add_truncate,
236 add_prefix => $add_prefix,
239 newlines => $newlines,
244 my @parsers = $translator->list_parsers;
245 my @producers = $translator->list_producers;
247 for ( @parsers, @producers ) {
248 if ( $_ =~ m/.+::(\w+)\.pm/ ) {
253 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
254 print "\nProducers:\n", map { "\t$_\n" } sort @producers;
259 pod2usage( msg => 'Please supply "from" and "to" arguments' )
262 $translator->parser($from);
263 $translator->producer($to);
265 for my $file (@files) {
267 ($file eq '-') ? (data => \*STDIN) :
268 ($file eq '!') ? (data => '') :
271 my $output = $translator->translate(@args) or die
272 "Error: " . $translator->error;
277 # ----------------------------------------------------
278 # It is not all books that are as dull as their readers.
279 # Henry David Thoreau
280 # ----------------------------------------------------
286 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>,
287 darren chamberlain E<lt>darren@cpan.orgE<gt>.
291 SQL::Translator, L<http://sqlfairy.sourceforge.net>.