X-Git-Url: http://git.shadowcat.co.uk/gitweb/gitweb.cgi?a=blobdiff_plain;f=bin%2Fsqlt;h=3df1db42b3f2b1158dc1dccf2eaa06597518e8fc;hb=1c680eb9aac43c469260f89be06027951722402e;hp=8038ac0aa5a17082cdd863d70d95a1ee35e989c3;hpb=7d8b348991ab628caa939808ed6282577312f7e0;p=dbsrgits%2FSQL-Translator.git diff --git a/bin/sqlt b/bin/sqlt index 8038ac0..3df1db4 100755 --- a/bin/sqlt +++ b/bin/sqlt @@ -1,10 +1,10 @@ #!/usr/bin/perl -w +# vim: set ft=perl: # ------------------------------------------------------------------- -# $Id: sqlt,v 1.1 2003-08-26 02:29:12 kycl4rk Exp $ +# $Id: sqlt,v 1.22 2007-03-21 15:21:31 duality72 Exp $ # ------------------------------------------------------------------- -# Copyright (C) 2002 Ken Y. Clark , -# darren chamberlain +# Copyright (C) 2002-4 SQLFairy Authors # # This program is free software; you can redistribute it and/or # modify it under the terms of the GNU General Public License as @@ -21,23 +21,137 @@ # 02111-1307 USA # ------------------------------------------------------------------- +=head1 NAME + +sqlt - convert SQL schema using SQL::Translator + +=head1 SYNOPSIS + +For help: + + sqlt -h|--help + +For a list of all parsers and producers: + + sqlt -l|--list + +To translate a schema: + + sqlt -f|--from|--parser MySQL + -t|--to|--producer Oracle + [options] + file [file2 ...] + + General Options: + + -d|--debug Print debug info + -v|--validate Validate the schema + --version Show the version of SQL::Translator + --trace Print parser trace info + --show-warnings Print warnings to STDERR + + DBI Parser Options: + + --dsn DSN for connecting to database + (see also --use-same-auth below) + --db-user Database user + --db-password Database password + + xSV Parser Options: + + --fs The field separator + --rs The record separator + --no-trim Don't trim whitespace on fields + --no-scan Don't scan fields for data types and sizes + + MySQL Parser Options: + + --mysql-parser-version Target MySQL parser version for dealing with + /*! comments; default = 30000 + + General Producer Options + + --producer-db-user Database user for producer + --producer-db-pass Database password for producer + --producer-dsn DSN for producer + --use-same-auth Use these DSN, user, password for producer output + + DB Producer Options: + + --add-drop-table Add 'DROP TABLE' statements before creates + --quote-table-names Quote all table names in statements + --quote-field-names Qjuote all field names in statements + --no-comments Don't include comments in SQL output + + Diagram Producer Options: + + --imap-file Filename to put image map data + --imap-url URL to use for image map + + Dumper Producer Options: + + --skip Comma-separated list of tables to skip + --skiplike Regex for tables to skip + --add-truncate Add "TRUNCATE TABLE" statements for each table + + HTML/POD Producer Options: + + --pretty Use CGI::Pretty for the output + --title Title of schema + + TTSchema Producer Options: + + --template The path to the template + --tt-var var=value Pass extra variables to the template + --tt-conf option=value Pass extra config options to Template + + XML-SQLFairy Producer Options: + + --add-prefix Use an explicit namespace prefix of 'sqlf:' + --prefix=

Use the namespace prefix given as argument. + --no-newlines Write the XML as a single line. + --indent= Use characters of whitespace to indent the XML. + + ClassDBI Producer Options: + + --package Base package name for Class::DBI modules. + +=head1 DESCRIPTION + +This script is part of the SQL Fairy project. It will try to convert +any source file for which it has a grammar into any format for which +it has a producer. + +If using "show-warnings," be sure to redirect STDERR to a separate file. +In bash, you could do this: + + $ sql_translator.pl -f MySQL -t PostgreSQL --show-warnings \ + file.sql 1>out 2>err + +You can specify a parser or producer located in any module that Perl +knows about, allowing you to easily substitute your own. + +=cut + +# ------------------------------------------------------------------- + use strict; use Getopt::Long; use Pod::Usage; use SQL::Translator; -use Data::Dumper; - use vars qw( $VERSION ); -$VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/; +$VERSION = sprintf "%d.%02d", q$Revision: 1.22 $ =~ /(\d+)\.(\d+)/; my $from; # the original database -my $to; # the destination database +my $to; # the destination database my $help; # show POD and bail my $stdin; # whether to read STDIN for create script my $no_comments; # whether to put comments in out file my $show_warnings; # whether to show warnings from SQL::Translator -my $add_drop_table; # whether to show warnings from SQL::Translator +my $add_drop_table; # whether to add "DROP table" statements +my $quote_table_names; # whether to quote table names +my $quote_field_names; # whether to quote field names my $debug; # whether to print debug info my $trace; # whether to print parser trace my $list; # list all parsers and producers @@ -50,56 +164,132 @@ my $imap_file; # filename where to place image map coords my $imap_url; # URL to use in making image map my $pretty; # use CGI::Pretty instead of CGI (HTML producer) my $template; # template to pass to TTSchema producer +my %tt_vars; # additional template vars to pass the TTSchema producer +my %tt_conf; # additional template conf to pass the TTSchema producer +my $title; # title for HTML/POD producer +my $add_prefix; # Use explicit namespace prefix (XML producer) +my $prefix; # Set explicit namespace prefix (XML producer) +my $newlines; # Add newlines around tags (XML producer) +my $indent; # Number of indent chars for XML +my $package_name; # Base class name for ClassDBI +my $use_same_auth =0; # producer uses same DSN, user, password as parser +my $dsn; # DBI parser +my $db_user; # DBI parser +my $db_password; # DBI parser +my $show_version; # Show version and exit script +my $skip; +my $skiplike; +my $producer_db_user; # DSN for producer (e.g. Dumper, ClassDBI) +my $producer_db_password; # db_pass " +my $producer_dsn; # db_user " +my $add_truncate; +my $mysql_parser_version; # MySQL parser arg for /*! comments -# -# Get options, explain how to use the script if necessary. -# GetOptions( - 'f|from|parser:s' => \$from, - 't|to|producer:s' => \$to, - 'h|help' => \$help, - 'l|list' => \$list, - 'd|debug' => \$debug, - 'trace' => \$trace, - 'no-comments' => \$no_comments, - 'show-warnings' => \$show_warnings, - 'add-drop-table' => \$add_drop_table, - 'v|validate' => \$validate, - 'no-trim' => \$no_trim, - 'no-scan' => \$no_scan, - 'fs:s' => \$field_separator, - 'rs:s' => \$record_separator, - 'imap-file:s' => \$imap_file, - 'imap-url:s' => \$imap_url, - 'pretty!' => \$pretty, - 'template:s' => \$template, + 'add-drop-table' => \$add_drop_table, + 'quote_table_names' => \$quote_table_names, + 'quote_field_names' => \$quote_field_names, + 'd|debug' => \$debug, + 'f|from|parser:s' => \$from, + 'fs:s' => \$field_separator, + 'h|help' => \$help, + 'imap-file:s' => \$imap_file, + 'imap-url:s' => \$imap_url, + 't|to|producer:s' => \$to, + 'l|list' => \$list, + 'pretty!' => \$pretty, + 'no-comments' => \$no_comments, + 'no-scan' => \$no_scan, + 'no-trim' => \$no_trim, + 'rs:s' => \$record_separator, + 'show-warnings' => \$show_warnings, + 'template:s' => \$template, + 'tt-var=s' => \%tt_vars, + 'tt-conf=s' => \%tt_conf, + 'title:s' => \$title, + 'trace' => \$trace, + 'v|validate' => \$validate, + 'dsn:s' => \$dsn, + 'db-user:s' => \$db_user, + 'db-password:s' => \$db_password, + 'producer-dsn:s' => \$producer_dsn, + 'producer-db-user:s'=> \$producer_db_user, + 'producer-db-pass:s'=> \$producer_db_password, + 'skip:s' => \$skip, + 'skiplike:s' => \$skiplike, + 'add_truncate' => \$add_truncate, + 'add-prefix' => \$add_prefix, + 'prefix:s' => \$prefix, + 'indent:s' => \$indent, + 'newlines!' => \$newlines, + 'package=s' => \$package_name, + 'use-same-auth' => \$use_same_auth, + 'version' => \$show_version, + 'mysql-parser-version=i' => \$mysql_parser_version, ) or pod2usage(2); -my @files = @ARGV; # the create script(s) for the original db +if ($use_same_auth) { + $producer_dsn = $dsn; + $producer_db_user = $db_user; + $producer_db_password = $db_password; +} + +$from = 'DBI' if !defined $from && defined $dsn; +my @files = @ARGV; # source files +unless ( @files ) { + if ( defined($from) && $from eq 'DBI' ) { + @files = ('!'); + } + else { + @files = ('-'); + } +} pod2usage(1) if $help; -# -# If everything is OK, translate file(s). -# -my $translator = SQL::Translator->new( - debug => $debug || 0, - trace => $trace || 0, - no_comments => $no_comments || 0, - show_warnings => $show_warnings || 0, - add_drop_table => $add_drop_table || 0, - validate => $validate || 0, - parser_args => { +if ( $show_version ) { + print "SQL::Translator v", $SQL::Translator::VERSION, "\n"; + exit(0); +} + +my $translator = SQL::Translator->new( + debug => $debug || 0, + trace => $trace || 0, + no_comments => $no_comments || 0, + show_warnings => $show_warnings || 0, + add_drop_table => $add_drop_table || 0, + quote_table_names => $quote_table_names || 1, + quote_field_names => $quote_field_names || 1, + validate => $validate || 0, + parser_args => { trim_fields => $no_trim ? 0 : 1, scan_fields => $no_scan ? 0 : 1, field_separator => $field_separator, record_separator => $record_separator, + dsn => $dsn, + db_user => $db_user, + db_password => $db_password, + mysql_parser_version => $mysql_parser_version, }, producer_args => { imap_file => $imap_file, imap_url => $imap_url, pretty => $pretty, ttfile => $template, + tt_vars => \%tt_vars, + tt_conf => \%tt_conf, + title => $title, + dsn => $producer_dsn, + db_user => $producer_db_user, + db_password => $producer_db_password, + skip => $skip, + skiplike => $skiplike, + add_truncate => $add_truncate, + add_prefix => $add_prefix, + prefix => $prefix, + indent => $indent, + newlines => $newlines, + package_name => $package_name, }, ); @@ -112,21 +302,28 @@ if ( $list ) { $_ = $1; } } - + print "\nParsers:\n", map { "\t$_\n" } sort @parsers; print "\nProducers:\n", map { "\t$_\n" } sort @producers; print "\n"; exit(0); } -pod2usage(2) unless $from && $to && @files; +pod2usage( msg => 'Please supply "from" and "to" arguments' ) + unless $from && $to; $translator->parser($from); $translator->producer($to); for my $file (@files) { - my $output = $translator->translate(file => $file) or die + my @args = + ($file eq '-') ? (data => \*STDIN) : + ($file eq '!') ? (data => '') : + (file => $file); + + my $output = $translator->translate(@args) or die "Error: " . $translator->error; + print $output; } @@ -135,79 +332,15 @@ for my $file (@files) { # Henry David Thoreau # ---------------------------------------------------- -=head1 NAME - -sql_translator.pl - convert an SQL database schema - -=head1 SYNOPSIS - -For help: - - ./sql_translator.pl -h|--help - -For a list of all parsers and producers: - - ./sql_translator.pl -l|--list - -To translate a schema: - - ./sql_translator.pl - -f|--from|--parser MySQL - -t|--to|--producer Oracle - [options] - file - - Options: - - -d|--debug Print debug info - -v|--validate Validate the schema - --trace Print parser trace info - --no-comments Don't include comments in SQL output - --show-warnings Print to STDERR warnings of conflicts, etc. - --add-drop-table Add 'drop table' statements before creates - - xSV Options: - - --fs The field separator - --rs The record separator - --no-trim Don't trim whitespace on fields - --no-scan Don't scan fields for data types and sizes - - Diagram Options: - - --imap-file Filename to put image map data - --imap-url URL to use for image map - - HTML Options: - - --pretty Use CGI::Pretty for the outpu - - TTSchema Options: - - --template The path to the template - -=head1 DESCRIPTION - -This script is part of the SQL Fairy project -(http://sqlfairy.sourceforge.net/). It will try to convert any -database syntax for which it has a grammar into some other format it -knows about. - -If using "show-warnings," be sure to redirect STDERR to a separate file. -In bash, you could do this: - - $ sql_translator.pl -f MySQL -t PostgreSQL --show-warnings \ - file.sql 1>out 2>err - -You can specify a parser or producer located in any module that Perl -knows about, allowing you to easily substitute your own. +=pod =head1 AUTHOR -Ken Y. Clark Ekclark@cpan.orgE +Ken Youens-Clark Ekclark@cpan.orgE, +darren chamberlain Edarren@cpan.orgE. =head1 SEE ALSO -SQL::Translator. +SQL::Translator, L. =cut