1 package SQL::Translator::Producer::Dumper;
3 # -------------------------------------------------------------------
4 # Copyright (C) 2002-2006 SQLFairy Authors
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.
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.
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
19 # -------------------------------------------------------------------
23 SQL::Translator::Producer::Dumper - SQL Dumper producer for SQL::Translator
27 use SQL::Translator::Producer::Dumper;
31 db_user Database username
32 db_password Database password
34 mysql_loadfile Create MySQL's LOAD FILE syntax instead of INSERTs
35 skip=t1[,t2] Skip tables in comma-separated list
36 skiplike=regex Skip tables in comma-separated list
40 This producer creates a Perl script that can connect to a database and
41 dump the data as INSERT statements (a la mysqldump) or as a file
42 suitable for MySQL's LOAD DATA command. If you enable "add-truncate"
43 or specify tables to "skip" (also using the "skiplike" regular
44 expression) then the generated dumper script will leave out those
45 tables. However, these will also be options in the generated dumper,
46 so you can wait to specify these options when you dump your database.
47 The database username, password, and DSN can be hardcoded into the
48 generated script, or part of the DSN can be intuited from the
56 use File::Temp 'tempfile';
58 use vars qw($VERSION);
66 my $args = $t->producer_args;
67 my $schema = $t->schema;
68 my $add_truncate = $args->{'add_truncate'} || 0;
69 my $skip = $args->{'skip'} || '';
70 my $skiplike = $args->{'skiplike'} || '';
71 my $db_user = $args->{'db_user'} || 'db_user';
72 my $db_pass = $args->{'db_password'} || 'db_pass';
73 my $parser_name = $t->parser_type;
74 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ }
76 my $sqlt_version = $t->version;
78 if ( $parser_name =~ /Parser::(\w+)$/ ) {
89 my $dbd = $type_to_dbd{ $parser_name } || 'DBD';
90 my $dsn = $args->{'dsn'} || "dbi:$dbd:";
91 if ( $dbd eq 'Pg' && ! $args->{'dsn'} ) {
92 $dsn .= 'dbname=dbname;host=hostname';
94 elsif ( $dbd eq 'Oracle' && ! $args->{'dsn'} ) {
95 $db_user = "$db_user/$db_pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" .
96 "(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=sid)))";
99 elsif ( $dbd eq 'mysql' && ! $args->{'dsn'} ) {
103 my $template = Template->new;
104 my $template_text = template();
114 perl => $Config{'startperl'},
116 skiplike => $skiplike,
119 ) or die $template->error;
124 # -------------------------------------------------------------------
127 # Returns the template to be processed by Template Toolkit
130 [% perl || '#!/usr/bin/perl' %]
133 # Generated by SQL::Translator [% translator.version %]
134 # [% date.format( date.now, "%Y-%m-%d" ) %]
135 # For more info, see http://sqlfairy.sourceforge.net/
142 use File::Spec::Functions 'catfile';
144 my ( $help, $add_truncate, $skip, $skiplike, $no_comments,
145 $takelike, $mysql_loadfile );
147 'add-truncate' => \$add_truncate,
149 'no-comments' => \$no_comments,
150 'mysql-loadfile' => \$mysql_loadfile,
152 'skiplike:s' => \$skiplike,
153 'takelike:s' => \$takelike,
159 $0 [options] > dump.sql
162 -h|--help Show help and exit
163 --add-truncate Add "TRUNCATE TABLE" statements
164 --mysql-loadfile Create MySQL's LOAD FILE syntax, not INSERTs
165 --no-comments Suppress comments
166 --skip=t1[,t2] Comma-separated list of tables to skip
167 --skiplike=regex Regular expression of table names to skip
168 --takelike=regex Regular expression of table names to take
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|enum|date)' )
192 ? 'string' : 'number';
193 field_names.push( field_name );
199 fields => field_names,
204 my $db = DBI->connect(
210 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ } split (/,/, $skip);
212 [%- FOREACH t IN table_defs %]
214 table_name => '[% t.name %]',
215 fields => [ qw/ [% t.fields.join(' ') %] / ],
217 [%- FOREACH fname IN t.types.keys %]
218 '[% fname %]' => '[% t.types.$fname %]',
225 for my $table ( @tables ) {
226 my $table_name = $table->{'table_name'};
227 next if $skip{ $table_name };
228 next if $skiplike && $table_name =~ qr/$skiplike/;
229 next if $takelike && $table_name !~ qr/$takelike/;
231 my ( $out_fh, $outfile );
232 if ( $mysql_loadfile ) {
233 $outfile = catfile( cwd(), "$table_name.txt" );
234 open $out_fh, ">$outfile" or
235 die "Can't write LOAD FILE to '$table_name': $!\n";
238 print "--\n-- Data for table '$table_name'\n--\n" unless $no_comments;
240 if ( $add_truncate ) {
241 print "TRUNCATE TABLE $table_name;\n";
245 'select ' . join(', ', @{ $table->{'fields'} } ) . " from $table_name"
247 my $sth = $db->prepare( $sql );
250 while ( my $rec = $sth->fetchrow_hashref ) {
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 Youens-Clark E<lt>kclark@cpan.orgE<gt>.