File: sqlt

package info (click to toggle)
libsql-translator-perl 0.11021-1
  • links: PTS, VCS
  • area: main
  • in suites: stretch
  • size: 4,544 kB
  • ctags: 2,143
  • sloc: perl: 67,158; sql: 3,809; xml: 249; makefile: 7
file content (375 lines) | stat: -rwxr-xr-x 12,276 bytes parent folder | download | duplicates (2)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
#!/usr/bin/env perl
# vim: set ft=perl:

# -------------------------------------------------------------------
# Copyright (C) 2002-2009 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
# published by the Free Software Foundation; version 2.
#
# This program is distributed in the hope that it will be useful, but
# WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA
# 02110-1301 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

  General Parser Options:

    --skip             Comma-separated list of tables to skip (only implemented in some parsers)
    --ignore_opts      Comma-separated list of table options to ignore

  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

  MySQL Producer Options:

    --mysql-version  MySQL server version

  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

  PostgreSQL Producer Options:

    --postgres-version   PostgreSQL server version

  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=<p>       Use the namespace prefix given as argument.
    --no-newlines      Write the XML as a single line.
    --indent=<n>       Use <n> 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 warnings;
use Getopt::Long;
use Pod::Usage;
use SQL::Translator;

use vars qw( $VERSION );
$VERSION = '1.59';

my $from;             # the original 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 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
my $no_trim;          # don't trim whitespace on xSV fields
my $no_scan;          # don't scan xSV fields for data types and sizes
my $field_separator;  # for xSV files
my $record_separator; # for xSV files
my $validate;         # whether to validate the parsed document
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 $ignore_opts;
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
my $postgres_version; # PostgreSQL version
my $mysql_version; # MySQL version

GetOptions(
    'add-drop-table'   => \$add_drop_table,
    'quote-table-names|quote_table_names'   => \$quote_table_names,
    'quote-field-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,
    'ignore_opts:s'    => \$ignore_opts,
    '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,
    'postgres-version=f' => \$postgres_version,
    'mysql-version=f' => \$mysql_version,
) or pod2usage(2);

if ($use_same_auth) {
    $producer_dsn = $dsn;
    $producer_db_user = $db_user;
    $producer_db_password = $db_password;
}

if (
    ( !defined $from && defined $dsn )
    ||
    $from =~ /^DBI.*/
) {
    $from = 'DBI';
}

my @files = @ARGV; # source files
unless ( @files ) {
    if ( defined($from) && $from eq 'DBI' ) {
        @files = ('!');
    }
    else {
        @files = ('-');
    }
}

pod2usage(1) if $help;

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    => defined $quote_table_names ? $quote_table_names : 1,
    quote_field_names    => defined $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,
        skip => $skip,
        ignore_opts => $ignore_opts,
    },
    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,
        postgres_version => $postgres_version,
        mysql_version    => $mysql_version,
        package_name     => $package_name,
    },
);

if ( $list ) {
    my @parsers   = $translator->list_parsers;
    my @producers = $translator->list_producers;

    for ( @parsers, @producers ) {
        if ( $_ =~ m/.+::(\w+)\.pm/ ) {
            $_ = $1;
        }
    }

    print "\nParsers:\n",   map { "\t$_\n" } sort @parsers;
    print "\nProducers:\n", map { "\t$_\n" } sort @producers;
    print "\n";
    exit(0);
}

pod2usage( msg => 'Please supply "from" and "to" arguments' )
    unless $from && $to;

$translator->parser($from);
$translator->producer($to);

for my $file (@files) {
    my @args =
        ($file eq '-') ? (data => \*STDIN) :
        ($file eq '!') ? (data => '') :
        (file => $file);

    my $output = $translator->translate(@args) or die
        "Error: " . $translator->error;

    print $output;
}

# ----------------------------------------------------
# It is not all books that are as dull as their readers.
# Henry David Thoreau
# ----------------------------------------------------

=pod

=head1 AUTHOR

Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>,
darren chamberlain E<lt>darren@cpan.orgE<gt>.

=head1 SEE ALSO

SQL::Translator, L<http://sqlfairy.sourceforge.net>.

=cut