1 package SQL::Translator::Producer::Dumper;
3 # -------------------------------------------------------------------
4 # $Id: Dumper.pm,v 1.1 2004-03-09 19:14:42 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 add-truncate Add "TRUNCATE TABLE" statements for each table
34 db_user Database username
35 db_password Database password
37 mysql_loadfile Create MySQL's LOAD FILE syntax instead of INSERTs
38 skip=t1[,t2] Skip tables in comma-separated list
39 skiplike=regex Skip tables in comma-separated list
43 This producer creates a Perl script that can connect to a database and
44 dump the data as INSERT statements (a la mysqldump) or as a file
45 suitable for MySQL's LOAD DATA command. If you enable "add-truncate"
46 or specify tables to "skip" (also using the "skiplike" regular
47 expression) then the generated dumper script will leave out those
48 tables. However, these will also be options in the generated dumper,
49 so you can wait to specify these options when you dump your database.
50 The database username, password, and DSN can be hardcoded into the
51 generated script, or part of the DSN can be intuited from the
59 use File::Temp 'tempfile';
61 use vars qw($VERSION);
65 $VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/;
69 my $args = $t->producer_args;
70 my $schema = $t->schema;
71 my $add_truncate = $args->{'add_truncate'} || 0;
72 my $skip = $args->{'skip'} || '';
73 my $skiplike = $args->{'skiplike'} || '';
74 my $db_user = $args->{'db_user'} || 'db_user';
75 my $db_pass = $args->{'db_password'} || 'db_pass';
76 my $parser_name = $t->parser_type;
77 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ }
79 my $sqlt_version = $t->version;
81 if ( $parser_name =~ /Parser::(\w+)$/ ) {
92 my $dbd = $type_to_dbd{ $parser_name } || 'DBD';
93 my $dsn = $args->{'dsn'} || "dbi:$dbd:";
94 if ( $dbd eq 'Pg' && ! $args->{'dsn'} ) {
95 $dsn .= 'dbname=dbname;host=hostname';
97 elsif ( $dbd eq 'Oracle' && ! $args->{'dsn'} ) {
98 $db_user = "$db_user/$db_pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" .
99 "(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=sid)))";
102 elsif ( $dbd eq 'mysql' && ! $args->{'dsn'} ) {
106 my $template = Template->new;
107 my $template_text = template();
117 perl => $Config{'startperl'},
119 skiplike => $skiplike,
122 ) or die $template->error;
127 # -------------------------------------------------------------------
130 # Returns the template to be processed by Template Toolkit
133 [% perl || '#!/usr/bin/perl' %]
136 # Generated by SQL::Translator [% translator.version %]
137 # [% date.format( date.now, "%Y-%m-%d" ) %]
138 # For more info, see http://sqlfairy.sourceforge.net/
145 use File::Spec::Functions 'catfile';
147 my ( $help, $add_truncate, $skip, $skiplike, $no_comments, $mysql_loadfile );
149 'add-truncate' => \$add_truncate,
151 'no-comments' => \$no_comments,
152 'mysql-loadfile' => \$mysql_loadfile,
154 'skiplike:s' => \$skiplike,
163 -h|--help Show help and exit
164 --add-truncate Add "TRUNCATE TABLE" statements
165 --mysql-loadfile Create MySQL's LOAD FILE syntax, not INSERTs
166 --no-comments Suppress comments
167 --skip=t1[,t2] Comma-separated list of tables to skip
168 --skiplike=regex Comma-separated list of tables to skip
174 $no_comments = 1 if $mysql_loadfile;
180 FOREACH table IN schema.get_tables;
181 SET table_name = table.name;
182 NEXT IF skip.$table_name;
183 NEXT IF skiplike AND table_name.match("(?:$skiplike)");
185 SET field_names = [];
187 FOR field IN table.get_fields;
188 field_name = field.name;
189 fname_len = field.name.length;
190 max_field = fname_len > max_field ? fname_len : max_field;
191 types.$field_name = field.data_type.match( '(char|str|long|text)' )
192 ? 'string' : 'number';
193 field_names.push( field_name );
199 fields => field_names,
204 my $db = DBI->connect('[% dsn %]', '[% db_user %]', '[% db_pass %]');
205 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ } split (/,/, $skip);
207 [%- FOREACH t IN table_defs %]
209 table_name => '[% t.name %]',
210 fields => [ qw/ [% t.fields.join(' ') %] / ],
212 [%- FOREACH fname IN t.types.keys %]
213 '[% fname %]' => '[% t.types.$fname %]',
220 for my $table ( @tables ) {
221 my $table_name = $table->{'table_name'};
222 next if $skip{ $table_name };
223 next if $skiplike && $table_name =~ qr/$skiplike/;
225 my ( $out_fh, $outfile );
226 if ( $mysql_loadfile ) {
227 $outfile = catfile( cwd(), "$table_name.txt" );
228 open $out_fh, ">$outfile" or
229 die "Can't write LOAD FILE to '$table_name': $!\n";
232 print "--\n-- Data for table '$table_name'\n--\n" unless $no_comments;
234 if ( $add_truncate ) {
235 print "TRUNCATE TABLE $table_name;\n";
238 my $data = $db->selectall_arrayref(
239 'select ' . join(', ', @{ $table->{'fields'} } ) . " from $table_name",
243 for my $rec ( @{ $data } ) {
245 for my $fld ( @{ $table->{'fields'} } ) {
246 my $val = $rec->{ $fld };
247 if ( $table->{'types'}{ $fld } eq 'string' ) {
248 if ( defined $val ) {
257 $val = defined $val ? $val : $mysql_loadfile ? '\N' : 'NULL';
262 if ( $mysql_loadfile ) {
263 print $out_fh join("\t", @vals), "\n";
266 print "INSERT INTO $table_name (".
267 join(', ', @{ $table->{'fields'} }) .
268 ' VALUES (', join(', ', @vals), ");\n";
273 print "LOAD DATA INFILE '$outfile' INTO TABLE $table_name ",
274 "FIELDS OPTIONALLY ENCLOSED BY '\\'';\n";
275 close $out_fh or die "Can't close filehandle: $!\n";
286 # -------------------------------------------------------------------
287 # To create a little flower is the labour of ages.
289 # -------------------------------------------------------------------
295 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.