1 package SQL::Translator::Producer::Dumper;
3 # -------------------------------------------------------------------
4 # $Id: Dumper.pm,v 1.5 2004-04-23 12:46:53 kycl4rk Exp $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-4 SQLFairy Authors
8 # This program is free software; you can redistribute it and/or
9 # modify it under the terms of the GNU General Public License as
10 # published by the Free Software Foundation; version 2.
12 # This program is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21 # -------------------------------------------------------------------
25 SQL::Translator::Producer::Dumper - SQL Dumper producer for SQL::Translator
29 use SQL::Translator::Producer::Dumper;
33 db_user Database username
34 db_password Database password
36 mysql_loadfile Create MySQL's LOAD FILE syntax instead of INSERTs
37 skip=t1[,t2] Skip tables in comma-separated list
38 skiplike=regex Skip tables in comma-separated list
42 This producer creates a Perl script that can connect to a database and
43 dump the data as INSERT statements (a la mysqldump) or as a file
44 suitable for MySQL's LOAD DATA command. If you enable "add-truncate"
45 or specify tables to "skip" (also using the "skiplike" regular
46 expression) then the generated dumper script will leave out those
47 tables. However, these will also be options in the generated dumper,
48 so you can wait to specify these options when you dump your database.
49 The database username, password, and DSN can be hardcoded into the
50 generated script, or part of the DSN can be intuited from the
58 use File::Temp 'tempfile';
60 use vars qw($VERSION);
64 $VERSION = sprintf "%d.%02d", q$Revision: 1.5 $ =~ /(\d+)\.(\d+)/;
68 my $args = $t->producer_args;
69 my $schema = $t->schema;
70 my $add_truncate = $args->{'add_truncate'} || 0;
71 my $skip = $args->{'skip'} || '';
72 my $skiplike = $args->{'skiplike'} || '';
73 my $db_user = $args->{'db_user'} || 'db_user';
74 my $db_pass = $args->{'db_password'} || 'db_pass';
75 my $parser_name = $t->parser_type;
76 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ }
78 my $sqlt_version = $t->version;
80 if ( $parser_name =~ /Parser::(\w+)$/ ) {
91 my $dbd = $type_to_dbd{ $parser_name } || 'DBD';
92 my $dsn = $args->{'dsn'} || "dbi:$dbd:";
93 if ( $dbd eq 'Pg' && ! $args->{'dsn'} ) {
94 $dsn .= 'dbname=dbname;host=hostname';
96 elsif ( $dbd eq 'Oracle' && ! $args->{'dsn'} ) {
97 $db_user = "$db_user/$db_pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" .
98 "(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=sid)))";
101 elsif ( $dbd eq 'mysql' && ! $args->{'dsn'} ) {
105 my $template = Template->new;
106 my $template_text = template();
116 perl => $Config{'startperl'},
118 skiplike => $skiplike,
121 ) or die $template->error;
126 # -------------------------------------------------------------------
129 # Returns the template to be processed by Template Toolkit
132 [% perl || '#!/usr/bin/perl' %]
135 # Generated by SQL::Translator [% translator.version %]
136 # [% date.format( date.now, "%Y-%m-%d" ) %]
137 # For more info, see http://sqlfairy.sourceforge.net/
144 use File::Spec::Functions 'catfile';
146 my ( $help, $add_truncate, $skip, $skiplike, $no_comments,
147 $takelike, $mysql_loadfile );
149 'add-truncate' => \$add_truncate,
151 'no-comments' => \$no_comments,
152 'mysql-loadfile' => \$mysql_loadfile,
154 'skiplike:s' => \$skiplike,
155 'takelike:s' => \$takelike,
161 $0 [options] > dump.sql
164 -h|--help Show help and exit
165 --add-truncate Add "TRUNCATE TABLE" statements
166 --mysql-loadfile Create MySQL's LOAD FILE syntax, not INSERTs
167 --no-comments Suppress comments
168 --skip=t1[,t2] Comma-separated list of tables to skip
169 --skiplike=regex Comma-separated list of tables to skip
175 $no_comments = 1 if $mysql_loadfile;
181 FOREACH table IN schema.get_tables;
182 SET table_name = table.name;
183 NEXT IF skip.$table_name;
184 NEXT IF skiplike AND table_name.match("(?:$skiplike)");
186 SET field_names = [];
188 FOR field IN table.get_fields;
189 field_name = field.name;
190 fname_len = field.name.length;
191 max_field = fname_len > max_field ? fname_len : max_field;
192 types.$field_name = field.data_type.match( '(char|str|long|text)' )
193 ? 'string' : 'number';
194 field_names.push( field_name );
200 fields => field_names,
205 my $db = DBI->connect(
211 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ } split (/,/, $skip);
213 [%- FOREACH t IN table_defs %]
215 table_name => '[% t.name %]',
216 fields => [ qw/ [% t.fields.join(' ') %] / ],
218 [%- FOREACH fname IN t.types.keys %]
219 '[% fname %]' => '[% t.types.$fname %]',
226 for my $table ( @tables ) {
227 my $table_name = $table->{'table_name'};
228 next if $skip{ $table_name };
229 next if $skiplike && $table_name =~ qr/$skiplike/;
230 next if $takelike && $table_name !~ qr/$takelike/;
232 my ( $out_fh, $outfile );
233 if ( $mysql_loadfile ) {
234 $outfile = catfile( cwd(), "$table_name.txt" );
235 open $out_fh, ">$outfile" or
236 die "Can't write LOAD FILE to '$table_name': $!\n";
239 print "--\n-- Data for table '$table_name'\n--\n" unless $no_comments;
241 if ( $add_truncate ) {
242 print "TRUNCATE TABLE $table_name;\n";
245 my $data = $db->selectall_arrayref(
246 'select ' . join(', ', @{ $table->{'fields'} } ) . " from $table_name",
250 for my $rec ( @{ $data } ) {
252 for my $fld ( @{ $table->{'fields'} } ) {
253 my $val = $rec->{ $fld };
254 if ( $table->{'types'}{ $fld } eq 'string' ) {
255 if ( defined $val ) {
264 $val = defined $val ? $val : $mysql_loadfile ? '\N' : 'NULL';
269 if ( $mysql_loadfile ) {
270 print $out_fh join("\t", @vals), "\n";
273 print "INSERT INTO $table_name (".
274 join(', ', @{ $table->{'fields'} }) .
275 ') VALUES (', join(', ', @vals), ");\n";
280 print "LOAD DATA INFILE '$outfile' INTO TABLE $table_name ",
281 "FIELDS OPTIONALLY ENCLOSED BY '\\'';\n";
282 close $out_fh or die "Can't close filehandle: $!\n";
293 # -------------------------------------------------------------------
294 # To create a little flower is the labour of ages.
296 # -------------------------------------------------------------------
302 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.