package SQL::Translator::Producer::Dumper;
-# -------------------------------------------------------------------
-# $Id: Dumper.pm,v 1.1 2004-03-09 19:14:42 kycl4rk Exp $
-# -------------------------------------------------------------------
-# Copyright (C) 2002-4 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., 59 Temple Place, Suite 330, Boston, MA
-# 02111-1307 USA
-# -------------------------------------------------------------------
-
=head1 NAME
SQL::Translator::Producer::Dumper - SQL Dumper producer for SQL::Translator
Options:
- add-truncate Add "TRUNCATE TABLE" statements for each table
db_user Database username
db_password Database password
dsn DSN for DBI
=cut
use strict;
+use warnings;
use Config;
use SQL::Translator;
use File::Temp 'tempfile';
use Template;
-use vars qw($VERSION);
use Data::Dumper;
-$VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/;
+our $VERSION = '1.62';
sub produce {
my $t = shift;
my $args = $t->producer_args;
my $schema = $t->schema;
- my $add_truncate = $args->{'add_truncate'} || 0;
+ my $add_truncate = $args->{'add_truncate'} || 0;
my $skip = $args->{'skip'} || '';
my $skiplike = $args->{'skiplike'} || '';
my $db_user = $args->{'db_user'} || 'db_user';
my $db_pass = $args->{'db_password'} || 'db_pass';
my $parser_name = $t->parser_type;
- my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ }
+ my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ }
split (/,/, $skip);
my $sqlt_version = $t->version;
- if ( $parser_name =~ /Parser::(\w+)$/ ) {
- $parser_name = $1
+ if ( $parser_name =~ /Parser::(\w+)$/ ) {
+ $parser_name = $1
}
my %type_to_dbd = (
my $template = Template->new;
my $template_text = template();
my $out;
- $template->process(
- \$template_text,
- {
+ $template->process(
+ \$template_text,
+ {
translator => $t,
schema => $schema,
db_user => $db_user,
perl => $Config{'startperl'},
skip => \%skip,
skiplike => $skiplike,
- },
- \$out
+ },
+ \$out
) or die $template->error;
return $out;
}
-# -------------------------------------------------------------------
sub template {
#
# Returns the template to be processed by Template Toolkit
use Getopt::Long;
use File::Spec::Functions 'catfile';
-my ( $help, $add_truncate, $skip, $skiplike, $no_comments, $mysql_loadfile );
+my ( $help, $add_truncate, $skip, $skiplike, $no_comments,
+ $takelike, $mysql_loadfile );
GetOptions(
'add-truncate' => \$add_truncate,
'h|help' => \$help,
'mysql-loadfile' => \$mysql_loadfile,
'skip:s' => \$skip,
'skiplike:s' => \$skiplike,
+ 'takelike:s' => \$takelike,
);
if ( $help ) {
print <<"USAGE";
Usage:
- $0 [options]
+ $0 [options] > dump.sql
Options:
-h|--help Show help and exit
--mysql-loadfile Create MySQL's LOAD FILE syntax, not INSERTs
--no-comments Suppress comments
--skip=t1[,t2] Comma-separated list of tables to skip
- --skiplike=regex Comma-separated list of tables to skip
+ --skiplike=regex Regular expression of table names to skip
+ --takelike=regex Regular expression of table names to take
USAGE
exit(0);
field_name = field.name;
fname_len = field.name.length;
max_field = fname_len > max_field ? fname_len : max_field;
- types.$field_name = field.data_type.match( '(char|str|long|text)' )
+ types.$field_name = field.data_type.match( '(char|str|long|text|enum|date)' )
? 'string' : 'number';
field_names.push( field_name );
END;
types => types,
fields => field_names,
});
-END
+END
-%]
-my $db = DBI->connect('[% dsn %]', '[% db_user %]', '[% db_pass %]');
+my $db = DBI->connect(
+ '[% dsn %]',
+ '[% db_user %]',
+ '[% db_pass %]',
+ { RaiseError => 1 }
+);
my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ } split (/,/, $skip);
my @tables = (
[%- FOREACH t IN table_defs %]
my $table_name = $table->{'table_name'};
next if $skip{ $table_name };
next if $skiplike && $table_name =~ qr/$skiplike/;
+ next if $takelike && $table_name !~ qr/$takelike/;
my ( $out_fh, $outfile );
if ( $mysql_loadfile ) {
$outfile = catfile( cwd(), "$table_name.txt" );
- open $out_fh, ">$outfile" or
+ open $out_fh, ">$outfile" or
die "Can't write LOAD FILE to '$table_name': $!\n";
}
print "TRUNCATE TABLE $table_name;\n";
}
- my $data = $db->selectall_arrayref(
- 'select ' . join(', ', @{ $table->{'fields'} } ) . " from $table_name",
- { Columns => {} }
- );
+ my $sql =
+ 'select ' . join(', ', @{ $table->{'fields'} } ) . " from $table_name"
+ ;
+ my $sth = $db->prepare( $sql );
+ $sth->execute;
- for my $rec ( @{ $data } ) {
+ while ( my $rec = $sth->fetchrow_hashref ) {
my @vals;
for my $fld ( @{ $table->{'fields'} } ) {
my $val = $rec->{ $fld };
$val = defined $val ? $val : $mysql_loadfile ? '\N' : 'NULL';
}
push @vals, $val;
- }
+ }
if ( $mysql_loadfile ) {
print $out_fh join("\t", @vals), "\n";
}
else {
print "INSERT INTO $table_name (".
- join(', ', @{ $table->{'fields'} }) .
- ' VALUES (', join(', ', @vals), ");\n";
+ join(', ', @{ $table->{'fields'} }) .
+ ') VALUES (', join(', ', @vals), ");\n";
}
}
=head1 AUTHOR
-Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
+Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.
=cut