4 # -------------------------------------------------------------------
5 # $Id: sqlt,v 1.5 2003-10-04 01:22:46 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.5 $ =~ /(\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 pod2usage(1) if $help;
175 my $translator = SQL::Translator->new(
176 debug => $debug || 0,
177 trace => $trace || 0,
178 no_comments => $no_comments || 0,
179 show_warnings => $show_warnings || 0,
180 add_drop_table => $add_drop_table || 0,
181 validate => $validate || 0,
183 trim_fields => $no_trim ? 0 : 1,
184 scan_fields => $no_scan ? 0 : 1,
185 field_separator => $field_separator,
186 record_separator => $record_separator,
189 db_password => $db_password,
192 imap_file => $imap_file,
193 imap_url => $imap_url,
197 emit_empty_tags => $emit_empty_tags,
198 attrib_values => $attrib_values,
203 my @parsers = $translator->list_parsers;
204 my @producers = $translator->list_producers;
206 for ( @parsers, @producers ) {
207 if ( $_ =~ m/.+::(\w+)\.pm/ ) {
212 print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
213 print "\nProducers:\n", map { "\t$_\n" } sort @producers;
218 pod2usage( msg => 'Please supply "from" and "to" arguments' ) unless $from && $to;
220 $translator->parser($from);
221 $translator->producer($to);
223 for my $file (@files) {
224 my $output = $translator->translate(file => $file) or die
225 "Error: " . $translator->error;
229 # ----------------------------------------------------
230 # It is not all books that are as dull as their readers.
231 # Henry David Thoreau
232 # ----------------------------------------------------
238 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
242 perl, SQL::Translator, Parse::RecDescent,
243 L<http://sqlfairy.sourceforge.net>.