PageRenderTime 57ms CodeModel.GetById 25ms RepoModel.GetById 1ms app.codeStats 0ms

/script/sqlt

https://github.com/gitpan/SQL-Translator
Perl | 375 lines | 297 code | 43 blank | 35 comment | 26 complexity | 4985e4f36347c30c303ee01089e7205e MD5 | raw file
Possible License(s): AGPL-1.0
  1. #!/usr/bin/env perl
  2. # vim: set ft=perl:
  3. # -------------------------------------------------------------------
  4. # Copyright (C) 2002-2009 SQLFairy Authors
  5. #
  6. # This program is free software; you can redistribute it and/or
  7. # modify it under the terms of the GNU General Public License as
  8. # published by the Free Software Foundation; version 2.
  9. #
  10. # This program is distributed in the hope that it will be useful, but
  11. # WITHOUT ANY WARRANTY; without even the implied warranty of
  12. # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
  13. # General Public License for more details.
  14. #
  15. # You should have received a copy of the GNU General Public License
  16. # along with this program; if not, write to the Free Software
  17. # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
  18. # 02111-1307 USA
  19. # -------------------------------------------------------------------
  20. =head1 NAME
  21. sqlt - convert SQL schema using SQL::Translator
  22. =head1 SYNOPSIS
  23. For help:
  24. sqlt -h|--help
  25. For a list of all parsers and producers:
  26. sqlt -l|--list
  27. To translate a schema:
  28. sqlt -f|--from|--parser MySQL
  29. -t|--to|--producer Oracle
  30. [options]
  31. file [file2 ...]
  32. General Options:
  33. -d|--debug Print debug info
  34. -v|--validate Validate the schema
  35. --version Show the version of SQL::Translator
  36. --trace Print parser trace info
  37. --show-warnings Print warnings to STDERR
  38. General Parser Options:
  39. --skip Comma-separated list of tables to skip (only implemented in some parsers)
  40. --ignore_opts Comma-separated list of table options to ignore
  41. DBI Parser Options:
  42. --dsn DSN for connecting to database
  43. (see also --use-same-auth below)
  44. --db-user Database user
  45. --db-password Database password
  46. xSV Parser Options:
  47. --fs The field separator
  48. --rs The record separator
  49. --no-trim Don't trim whitespace on fields
  50. --no-scan Don't scan fields for data types and sizes
  51. MySQL Parser Options:
  52. --mysql-parser-version Target MySQL parser version for dealing with
  53. /*! comments; default = 30000
  54. MySQL Producer Options:
  55. --mysql-version MySQL server version
  56. General Producer Options
  57. --producer-db-user Database user for producer
  58. --producer-db-pass Database password for producer
  59. --producer-dsn DSN for producer
  60. --use-same-auth Use these DSN, user, password for producer output
  61. DB Producer Options:
  62. --add-drop-table Add 'DROP TABLE' statements before creates
  63. --quote-table-names Quote all table names in statements
  64. --quote-field-names Qjuote all field names in statements
  65. --no-comments Don't include comments in SQL output
  66. PostgreSQL Producer Options:
  67. --postgres-version PostgreSQL server version
  68. Diagram Producer Options:
  69. --imap-file Filename to put image map data
  70. --imap-url URL to use for image map
  71. Dumper Producer Options:
  72. --skip Comma-separated list of tables to skip
  73. --skiplike Regex for tables to skip
  74. --add-truncate Add "TRUNCATE TABLE" statements for each table
  75. HTML/POD Producer Options:
  76. --pretty Use CGI::Pretty for the output
  77. --title Title of schema
  78. TTSchema Producer Options:
  79. --template The path to the template
  80. --tt-var var=value Pass extra variables to the template
  81. --tt-conf option=value Pass extra config options to Template
  82. XML-SQLFairy Producer Options:
  83. --add-prefix Use an explicit namespace prefix of 'sqlf:'
  84. --prefix=<p> Use the namespace prefix given as argument.
  85. --no-newlines Write the XML as a single line.
  86. --indent=<n> Use <n> characters of whitespace to indent the XML.
  87. ClassDBI Producer Options:
  88. --package Base package name for Class::DBI modules.
  89. =head1 DESCRIPTION
  90. This script is part of the SQL Fairy project. It will try to convert
  91. any source file for which it has a grammar into any format for which
  92. it has a producer.
  93. If using "show-warnings," be sure to redirect STDERR to a separate file.
  94. In bash, you could do this:
  95. $ sql_translator.pl -f MySQL -t PostgreSQL --show-warnings \
  96. file.sql 1>out 2>err
  97. You can specify a parser or producer located in any module that Perl
  98. knows about, allowing you to easily substitute your own.
  99. =cut
  100. # -------------------------------------------------------------------
  101. use strict;
  102. use warnings;
  103. use Getopt::Long;
  104. use Pod::Usage;
  105. use SQL::Translator;
  106. use vars qw( $VERSION );
  107. $VERSION = '1.59';
  108. my $from; # the original database
  109. my $to; # the destination database
  110. my $help; # show POD and bail
  111. my $stdin; # whether to read STDIN for create script
  112. my $no_comments; # whether to put comments in out file
  113. my $show_warnings; # whether to show warnings from SQL::Translator
  114. my $add_drop_table; # whether to add "DROP table" statements
  115. my $quote_table_names; # whether to quote table names
  116. my $quote_field_names; # whether to quote field names
  117. my $debug; # whether to print debug info
  118. my $trace; # whether to print parser trace
  119. my $list; # list all parsers and producers
  120. my $no_trim; # don't trim whitespace on xSV fields
  121. my $no_scan; # don't scan xSV fields for data types and sizes
  122. my $field_separator; # for xSV files
  123. my $record_separator; # for xSV files
  124. my $validate; # whether to validate the parsed document
  125. my $imap_file; # filename where to place image map coords
  126. my $imap_url; # URL to use in making image map
  127. my $pretty; # use CGI::Pretty instead of CGI (HTML producer)
  128. my $template; # template to pass to TTSchema producer
  129. my %tt_vars; # additional template vars to pass the TTSchema producer
  130. my %tt_conf; # additional template conf to pass the TTSchema producer
  131. my $title; # title for HTML/POD producer
  132. my $add_prefix; # Use explicit namespace prefix (XML producer)
  133. my $prefix; # Set explicit namespace prefix (XML producer)
  134. my $newlines; # Add newlines around tags (XML producer)
  135. my $indent; # Number of indent chars for XML
  136. my $package_name; # Base class name for ClassDBI
  137. my $use_same_auth =0; # producer uses same DSN, user, password as parser
  138. my $dsn; # DBI parser
  139. my $db_user; # DBI parser
  140. my $db_password; # DBI parser
  141. my $show_version; # Show version and exit script
  142. my $skip;
  143. my $skiplike;
  144. my $ignore_opts;
  145. my $producer_db_user; # DSN for producer (e.g. Dumper, ClassDBI)
  146. my $producer_db_password; # db_pass "
  147. my $producer_dsn; # db_user "
  148. my $add_truncate;
  149. my $mysql_parser_version; # MySQL parser arg for /*! comments
  150. my $postgres_version; # PostgreSQL version
  151. my $mysql_version; # MySQL version
  152. GetOptions(
  153. 'add-drop-table' => \$add_drop_table,
  154. 'quote_table_names' => \$quote_table_names,
  155. 'quote_field_names' => \$quote_field_names,
  156. 'd|debug' => \$debug,
  157. 'f|from|parser:s' => \$from,
  158. 'fs:s' => \$field_separator,
  159. 'h|help' => \$help,
  160. 'imap-file:s' => \$imap_file,
  161. 'imap-url:s' => \$imap_url,
  162. 't|to|producer:s' => \$to,
  163. 'l|list' => \$list,
  164. 'pretty!' => \$pretty,
  165. 'no-comments' => \$no_comments,
  166. 'no-scan' => \$no_scan,
  167. 'no-trim' => \$no_trim,
  168. 'rs:s' => \$record_separator,
  169. 'show-warnings' => \$show_warnings,
  170. 'template:s' => \$template,
  171. 'tt-var=s' => \%tt_vars,
  172. 'tt-conf=s' => \%tt_conf,
  173. 'title:s' => \$title,
  174. 'trace' => \$trace,
  175. 'v|validate' => \$validate,
  176. 'dsn:s' => \$dsn,
  177. 'db-user:s' => \$db_user,
  178. 'db-password:s' => \$db_password,
  179. 'producer-dsn:s' => \$producer_dsn,
  180. 'producer-db-user:s'=> \$producer_db_user,
  181. 'producer-db-pass:s'=> \$producer_db_password,
  182. 'skip:s' => \$skip,
  183. 'skiplike:s' => \$skiplike,
  184. 'ignore_opts:s' => \$ignore_opts,
  185. 'add_truncate' => \$add_truncate,
  186. 'add-prefix' => \$add_prefix,
  187. 'prefix:s' => \$prefix,
  188. 'indent:s' => \$indent,
  189. 'newlines!' => \$newlines,
  190. 'package=s' => \$package_name,
  191. 'use-same-auth' => \$use_same_auth,
  192. 'version' => \$show_version,
  193. 'mysql-parser-version=i' => \$mysql_parser_version,
  194. 'postgres-version=f' => \$postgres_version,
  195. 'mysql-version=f' => \$mysql_version,
  196. ) or pod2usage(2);
  197. if ($use_same_auth) {
  198. $producer_dsn = $dsn;
  199. $producer_db_user = $db_user;
  200. $producer_db_password = $db_password;
  201. }
  202. if (
  203. ( !defined $from && defined $dsn )
  204. ||
  205. $from =~ /^DBI.*/
  206. ) {
  207. $from = 'DBI';
  208. }
  209. my @files = @ARGV; # source files
  210. unless ( @files ) {
  211. if ( defined($from) && $from eq 'DBI' ) {
  212. @files = ('!');
  213. }
  214. else {
  215. @files = ('-');
  216. }
  217. }
  218. pod2usage(1) if $help;
  219. if ( $show_version ) {
  220. print "SQL::Translator v", $SQL::Translator::VERSION, "\n";
  221. exit(0);
  222. }
  223. my $translator = SQL::Translator->new(
  224. debug => $debug || 0,
  225. trace => $trace || 0,
  226. no_comments => $no_comments || 0,
  227. show_warnings => $show_warnings || 0,
  228. add_drop_table => $add_drop_table || 0,
  229. quote_table_names => defined $quote_table_names ? $quote_table_names : 1,
  230. quote_field_names => defined $quote_field_names ? $quote_field_names : 1,
  231. validate => $validate || 0,
  232. parser_args => {
  233. trim_fields => $no_trim ? 0 : 1,
  234. scan_fields => $no_scan ? 0 : 1,
  235. field_separator => $field_separator,
  236. record_separator => $record_separator,
  237. dsn => $dsn,
  238. db_user => $db_user,
  239. db_password => $db_password,
  240. mysql_parser_version => $mysql_parser_version,
  241. skip => $skip,
  242. ignore_opts => $ignore_opts,
  243. },
  244. producer_args => {
  245. imap_file => $imap_file,
  246. imap_url => $imap_url,
  247. pretty => $pretty,
  248. ttfile => $template,
  249. tt_vars => \%tt_vars,
  250. tt_conf => \%tt_conf,
  251. title => $title,
  252. dsn => $producer_dsn,
  253. db_user => $producer_db_user,
  254. db_password => $producer_db_password,
  255. skip => $skip,
  256. skiplike => $skiplike,
  257. add_truncate => $add_truncate,
  258. add_prefix => $add_prefix,
  259. prefix => $prefix,
  260. indent => $indent,
  261. newlines => $newlines,
  262. postgres_version => $postgres_version,
  263. mysql_version => $mysql_version,
  264. package_name => $package_name,
  265. },
  266. );
  267. if ( $list ) {
  268. my @parsers = $translator->list_parsers;
  269. my @producers = $translator->list_producers;
  270. for ( @parsers, @producers ) {
  271. if ( $_ =~ m/.+::(\w+)\.pm/ ) {
  272. $_ = $1;
  273. }
  274. }
  275. print "\nParsers:\n", map { "\t$_\n" } sort @parsers;
  276. print "\nProducers:\n", map { "\t$_\n" } sort @producers;
  277. print "\n";
  278. exit(0);
  279. }
  280. pod2usage( msg => 'Please supply "from" and "to" arguments' )
  281. unless $from && $to;
  282. $translator->parser($from);
  283. $translator->producer($to);
  284. for my $file (@files) {
  285. my @args =
  286. ($file eq '-') ? (data => \*STDIN) :
  287. ($file eq '!') ? (data => '') :
  288. (file => $file);
  289. my $output = $translator->translate(@args) or die
  290. "Error: " . $translator->error;
  291. print $output;
  292. }
  293. # ----------------------------------------------------
  294. # It is not all books that are as dull as their readers.
  295. # Henry David Thoreau
  296. # ----------------------------------------------------
  297. =pod
  298. =head1 AUTHOR
  299. Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
  300. darren chamberlain E<lt>darren@cpan.orgE<gt>.
  301. =head1 SEE ALSO
  302. SQL::Translator, L<http://sqlfairy.sourceforge.net>.
  303. =cut