4 # -------------------------------------------------------------------
5 # $Id: sqlt,v 1.11 2004-02-03 13:27:21 dlc Exp $
6 # -------------------------------------------------------------------
7 # Copyright (C) 2002 Ken Y. Clark <kclar@cpan.org>,
8 # darren chamberlain <darren@cpan.org>
10 # This program is free software; you can redistribute it and/or
11 # modify it under the terms of the GNU General Public License as
12 # published by the Free Software Foundation; version 2.
14 # This program is distributed in the hope that it will be useful, but
15 # WITHOUT ANY WARRANTY; without even the implied warranty of
16 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
17 # General Public License for more details.
19 # You should have received a copy of the GNU General Public License
20 # along with this program; if not, write to the Free Software
21 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
23 # -------------------------------------------------------------------
27 sqlt - convert SQL schema using SQL::Translator
35 For a list of all parsers and producers:
39 To translate a schema:
41 sqlt -f|--from|--parser MySQL
42 -t|--to|--producer Oracle
48 -d|--debug Print debug info
49 -v|--validate Validate the schema
50 --trace Print parser trace info
51 --show-warnings Print warnings to STDERR
55 --dsn DSN for connecting to database
56 --db-user Database user
57 --db-password Database password
61 --fs The field separator
62 --rs The record separator
63 --no-trim Don't trim whitespace on fields
64 --no-scan Don't scan fields for data types and sizes
68 --add-drop-table Add 'DROP TABLE' statements before creates
69 --no-comments Don't include comments in SQL output
71 Diagram Producer Options:
73 --imap-file Filename to put image map data
74 --imap-url URL to use for image map
76 HTML/POD Producer Options:
78 --pretty Use CGI::Pretty for the output
79 --title Title of schema
81 TTSchema Producer Options:
83 --template The path to the template
85 XML-SQLFairy Producer Options:
87 --emit-empty-tags Print empty tags for attributes
88 --attrib-values Use attributes instead of tags for
89 values of the schema objects
93 This script is part of the SQL Fairy project. It will try to convert
94 any source file for which it has a grammar into any format for which
97 If using "show-warnings," be sure to redirect STDERR to a separate file.
98 In bash, you could do this:
100 $ sql_translator.pl -f MySQL -t PostgreSQL --show-warnings \
103 You can specify a parser or producer located in any module that Perl
104 knows about, allowing you to easily substitute your own.
108 # -------------------------------------------------------------------
115 use vars qw( $VERSION );
116 $VERSION = sprintf "%d.%02d", q$Revision: 1.11 $ =~ /(\d+)\.(\d+)/;
118 my $from; # the original database
119 my $to; # the destination database
120 my $help; # show POD and bail
121 my $stdin; # whether to read STDIN for create script
122 my $no_comments; # whether to put comments in out file
123 my $show_warnings; # whether to show warnings from SQL::Translator
124 my $add_drop_table; # whether to show warnings from SQL::Translator
125 my $debug; # whether to print debug info
126 my $trace; # whether to print parser trace
127 my $list; # list all parsers and producers
128 my $no_trim; # don't trim whitespace on xSV fields
129 my $no_scan; # don't scan xSV fields for data types and sizes
130 my $field_separator; # for xSV files
131 my $record_separator; # for xSV files
132 my $validate; # whether to validate the parsed document
133 my $imap_file; # filename where to place image map coords
134 my $imap_url; # URL to use in making image map
135 my $pretty; # use CGI::Pretty instead of CGI (HTML producer)
136 my $template; # template to pass to TTSchema producer
137 my $title; # title for HTML/POD producer
138 my $emit_empty_tags; # show empty XML tags
139 my $attrib_values; # use XML attributes instead of tags
140 my $dsn; # DBI parser
141 my $db_user; # DBI parser
142 my $db_password; # DBI parser
145 'add-drop-table' => \$add_drop_table,
146 'attrib-values' => \$attrib_values,
147 'd|debug' => \$debug,
148 'emit-empty-tags' => \$emit_empty_tags,
149 'f|from|parser:s' => \$from,
150 'fs:s' => \$field_separator,
152 'imap-file:s' => \$imap_file,
153 'imap-url:s' => \$imap_url,
154 't|to|producer:s' => \$to,
156 'pretty!' => \$pretty,
157 'no-comments' => \$no_comments,
158 'no-scan' => \$no_scan,
159 'no-trim' => \$no_trim,
160 'rs:s' => \$record_separator,
161 'show-warnings' => \$show_warnings,
162 'template:s' => \$template,
163 'title:s' => \$title,
165 'v|validate' => \$validate,
167 'db-user:s' => \$db_user,
168 'db-password:s' => \$db_password,
171 my @files = @ARGV; # source files
173 if ( defined($from) && $from eq 'DBI' ) {
181 pod2usage(1) if $help;
183 my $translator = SQL::Translator->new(
184 debug => $debug || 0,
185 trace => $trace || 0,
186 no_comments => $no_comments || 0,
187 show_warnings => $show_warnings || 0,
188 add_drop_table => $add_drop_table || 0,
189 validate => $validate || 0,
191 trim_fields => $no_trim ? 0 : 1,
192 scan_fields => $no_scan ? 0 : 1,
193 field_separator => $field_separator,
194 record_separator => $record_separator,
197 db_password => $db_password,
200 imap_file => $imap_file,
201 imap_url => $imap_url,
205 emit_empty_tags => $emit_empty_tags,
206 attrib_values => $attrib_values,
211 my @parsers = $translator->list_parsers;
212 my @producers = $translator->list_producers;
214 for ( @parsers, @producers ) {
215 if ( $_ =~ m/.+::(\w+)\.pm/ ) {
220 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
221 print "\nProducers:\n", map { "\t$_\n" } sort @producers;
226 pod2usage( msg => 'Please supply "from" and "to" arguments' )
229 $translator->parser($from);
230 $translator->producer($to);
232 for my $file (@files) {
234 ($file eq '-') ? (data => \*STDIN) :
235 ($file eq '!') ? (data => '') :
238 my $output = $translator->translate(@args) or die
239 "Error: " . $translator->error;
244 # ----------------------------------------------------
245 # It is not all books that are as dull as their readers.
246 # Henry David Thoreau
247 # ----------------------------------------------------
253 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>,
254 darren chamberlain E<lt>darren@cpan.orgE<gt>.
258 SQL::Translator, L<http://sqlfairy.sourceforge.net>.