4 # -------------------------------------------------------------------
5 # $Id: sqlt,v 1.8 2003-10-14 17:03:45 kycl4rk 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.8 $ =~ /(\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
172 @files = ('-') unless (@files);
174 pod2usage(1) if $help;
176 my $translator = SQL::Translator->new(
177 debug => $debug || 0,
178 trace => $trace || 0,
179 no_comments => $no_comments || 0,
180 show_warnings => $show_warnings || 0,
181 add_drop_table => $add_drop_table || 0,
182 validate => $validate || 0,
184 trim_fields => $no_trim ? 0 : 1,
185 scan_fields => $no_scan ? 0 : 1,
186 field_separator => $field_separator,
187 record_separator => $record_separator,
190 db_password => $db_password,
193 imap_file => $imap_file,
194 imap_url => $imap_url,
198 emit_empty_tags => $emit_empty_tags,
199 attrib_values => $attrib_values,
204 my @parsers = $translator->list_parsers;
205 my @producers = $translator->list_producers;
207 for ( @parsers, @producers ) {
208 if ( $_ =~ m/.+::(\w+)\.pm/ ) {
213 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
214 print "\nProducers:\n", map { "\t$_\n" } sort @producers;
219 pod2usage( msg => 'Please supply "from" and "to" arguments' )
222 $translator->parser($from);
223 $translator->producer($to);
225 for my $file (@files) {
226 my @args = ($file eq '-') ? (data => \*STDIN) : (file => $file);
227 my $output = $translator->translate(@args) or die
228 "Error: " . $translator->error;
232 # ----------------------------------------------------
233 # It is not all books that are as dull as their readers.
234 # Henry David Thoreau
235 # ----------------------------------------------------
241 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>,
242 darren chamberlain E<lt>darren@cpan.orgE<gt>.
246 SQL::Translator, L<http://sqlfairy.sourceforge.net>.