1 package SQL::Translator::Producer::Dumper;
3 # -------------------------------------------------------------------
4 # $Id: Dumper.pm 1440 2009-01-17 16:31:57Z jawnsy $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2002-2006 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';
65 my $args = $t->producer_args;
66 my $schema = $t->schema;
67 my $add_truncate = $args->{'add_truncate'} || 0;
68 my $skip = $args->{'skip'} || '';
69 my $skiplike = $args->{'skiplike'} || '';
70 my $db_user = $args->{'db_user'} || 'db_user';
71 my $db_pass = $args->{'db_password'} || 'db_pass';
72 my $parser_name = $t->parser_type;
73 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ }
75 my $sqlt_version = $t->version;
77 if ( $parser_name =~ /Parser::(\w+)$/ ) {
88 my $dbd = $type_to_dbd{ $parser_name } || 'DBD';
89 my $dsn = $args->{'dsn'} || "dbi:$dbd:";
90 if ( $dbd eq 'Pg' && ! $args->{'dsn'} ) {
91 $dsn .= 'dbname=dbname;host=hostname';
93 elsif ( $dbd eq 'Oracle' && ! $args->{'dsn'} ) {
94 $db_user = "$db_user/$db_pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" .
95 "(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=sid)))";
98 elsif ( $dbd eq 'mysql' && ! $args->{'dsn'} ) {
102 my $template = Template->new;
103 my $template_text = template();
113 perl => $Config{'startperl'},
115 skiplike => $skiplike,
118 ) or die $template->error;
123 # -------------------------------------------------------------------
126 # Returns the template to be processed by Template Toolkit
129 [% perl || '#!/usr/bin/perl' %]
132 # Generated by SQL::Translator [% translator.version %]
133 # [% date.format( date.now, "%Y-%m-%d" ) %]
134 # For more info, see http://sqlfairy.sourceforge.net/
141 use File::Spec::Functions 'catfile';
143 my ( $help, $add_truncate, $skip, $skiplike, $no_comments,
144 $takelike, $mysql_loadfile );
146 'add-truncate' => \$add_truncate,
148 'no-comments' => \$no_comments,
149 'mysql-loadfile' => \$mysql_loadfile,
151 'skiplike:s' => \$skiplike,
152 'takelike:s' => \$takelike,
158 $0 [options] > dump.sql
161 -h|--help Show help and exit
162 --add-truncate Add "TRUNCATE TABLE" statements
163 --mysql-loadfile Create MySQL's LOAD FILE syntax, not INSERTs
164 --no-comments Suppress comments
165 --skip=t1[,t2] Comma-separated list of tables to skip
166 --skiplike=regex Regular expression of table names to skip
167 --takelike=regex Regular expression of table names to take
173 $no_comments = 1 if $mysql_loadfile;
179 FOREACH table IN schema.get_tables;
180 SET table_name = table.name;
181 NEXT IF skip.$table_name;
182 NEXT IF skiplike AND table_name.match("(?:$skiplike)");
184 SET field_names = [];
186 FOR field IN table.get_fields;
187 field_name = field.name;
188 fname_len = field.name.length;
189 max_field = fname_len > max_field ? fname_len : max_field;
190 types.$field_name = field.data_type.match( '(char|str|long|text|enum|date)' )
191 ? 'string' : 'number';
192 field_names.push( field_name );
198 fields => field_names,
203 my $db = DBI->connect(
209 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ } split (/,/, $skip);
211 [%- FOREACH t IN table_defs %]
213 table_name => '[% t.name %]',
214 fields => [ qw/ [% t.fields.join(' ') %] / ],
216 [%- FOREACH fname IN t.types.keys %]
217 '[% fname %]' => '[% t.types.$fname %]',
224 for my $table ( @tables ) {
225 my $table_name = $table->{'table_name'};
226 next if $skip{ $table_name };
227 next if $skiplike && $table_name =~ qr/$skiplike/;
228 next if $takelike && $table_name !~ qr/$takelike/;
230 my ( $out_fh, $outfile );
231 if ( $mysql_loadfile ) {
232 $outfile = catfile( cwd(), "$table_name.txt" );
233 open $out_fh, ">$outfile" or
234 die "Can't write LOAD FILE to '$table_name': $!\n";
237 print "--\n-- Data for table '$table_name'\n--\n" unless $no_comments;
239 if ( $add_truncate ) {
240 print "TRUNCATE TABLE $table_name;\n";
244 'select ' . join(', ', @{ $table->{'fields'} } ) . " from $table_name"
246 my $sth = $db->prepare( $sql );
249 while ( my $rec = $sth->fetchrow_hashref ) {
251 for my $fld ( @{ $table->{'fields'} } ) {
252 my $val = $rec->{ $fld };
253 if ( $table->{'types'}{ $fld } eq 'string' ) {
254 if ( defined $val ) {
263 $val = defined $val ? $val : $mysql_loadfile ? '\N' : 'NULL';
268 if ( $mysql_loadfile ) {
269 print $out_fh join("\t", @vals), "\n";
272 print "INSERT INTO $table_name (".
273 join(', ', @{ $table->{'fields'} }) .
274 ') VALUES (', join(', ', @vals), ");\n";
279 print "LOAD DATA INFILE '$outfile' INTO TABLE $table_name ",
280 "FIELDS OPTIONALLY ENCLOSED BY '\\'';\n";
281 close $out_fh or die "Can't close filehandle: $!\n";
292 # -------------------------------------------------------------------
293 # To create a little flower is the labour of ages.
295 # -------------------------------------------------------------------
301 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.