1 package SQL::Translator::Producer::Dumper;
5 SQL::Translator::Producer::Dumper - SQL Dumper producer for SQL::Translator
9 use SQL::Translator::Producer::Dumper;
13 db_user Database username
14 db_password Database password
16 mysql_loadfile Create MySQL's LOAD FILE syntax instead of INSERTs
17 skip=t1[,t2] Skip tables in comma-separated list
18 skiplike=regex Skip tables in comma-separated list
22 This producer creates a Perl script that can connect to a database and
23 dump the data as INSERT statements (a la mysqldump) or as a file
24 suitable for MySQL's LOAD DATA command. If you enable "add-truncate"
25 or specify tables to "skip" (also using the "skiplike" regular
26 expression) then the generated dumper script will leave out those
27 tables. However, these will also be options in the generated dumper,
28 so you can wait to specify these options when you dump your database.
29 The database username, password, and DSN can be hardcoded into the
30 generated script, or part of the DSN can be intuited from the
39 use File::Temp 'tempfile';
44 our $VERSION = '1.60';
48 my $args = $t->producer_args;
49 my $schema = $t->schema;
50 my $add_truncate = $args->{'add_truncate'} || 0;
51 my $skip = $args->{'skip'} || '';
52 my $skiplike = $args->{'skiplike'} || '';
53 my $db_user = $args->{'db_user'} || 'db_user';
54 my $db_pass = $args->{'db_password'} || 'db_pass';
55 my $parser_name = $t->parser_type;
56 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ }
58 my $sqlt_version = $t->version;
60 if ( $parser_name =~ /Parser::(\w+)$/ ) {
71 my $dbd = $type_to_dbd{ $parser_name } || 'DBD';
72 my $dsn = $args->{'dsn'} || "dbi:$dbd:";
73 if ( $dbd eq 'Pg' && ! $args->{'dsn'} ) {
74 $dsn .= 'dbname=dbname;host=hostname';
76 elsif ( $dbd eq 'Oracle' && ! $args->{'dsn'} ) {
77 $db_user = "$db_user/$db_pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" .
78 "(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=sid)))";
81 elsif ( $dbd eq 'mysql' && ! $args->{'dsn'} ) {
85 my $template = Template->new;
86 my $template_text = template();
96 perl => $Config{'startperl'},
98 skiplike => $skiplike,
101 ) or die $template->error;
108 # Returns the template to be processed by Template Toolkit
111 [% perl || '#!/usr/bin/perl' %]
114 # Generated by SQL::Translator [% translator.version %]
115 # [% date.format( date.now, "%Y-%m-%d" ) %]
116 # For more info, see http://sqlfairy.sourceforge.net/
123 use File::Spec::Functions 'catfile';
125 my ( $help, $add_truncate, $skip, $skiplike, $no_comments,
126 $takelike, $mysql_loadfile );
128 'add-truncate' => \$add_truncate,
130 'no-comments' => \$no_comments,
131 'mysql-loadfile' => \$mysql_loadfile,
133 'skiplike:s' => \$skiplike,
134 'takelike:s' => \$takelike,
140 $0 [options] > dump.sql
143 -h|--help Show help and exit
144 --add-truncate Add "TRUNCATE TABLE" statements
145 --mysql-loadfile Create MySQL's LOAD FILE syntax, not INSERTs
146 --no-comments Suppress comments
147 --skip=t1[,t2] Comma-separated list of tables to skip
148 --skiplike=regex Regular expression of table names to skip
149 --takelike=regex Regular expression of table names to take
155 $no_comments = 1 if $mysql_loadfile;
161 FOREACH table IN schema.get_tables;
162 SET table_name = table.name;
163 NEXT IF skip.$table_name;
164 NEXT IF skiplike AND table_name.match("(?:$skiplike)");
166 SET field_names = [];
168 FOR field IN table.get_fields;
169 field_name = field.name;
170 fname_len = field.name.length;
171 max_field = fname_len > max_field ? fname_len : max_field;
172 types.$field_name = field.data_type.match( '(char|str|long|text|enum|date)' )
173 ? 'string' : 'number';
174 field_names.push( field_name );
180 fields => field_names,
185 my $db = DBI->connect(
191 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ } split (/,/, $skip);
193 [%- FOREACH t IN table_defs %]
195 table_name => '[% t.name %]',
196 fields => [ qw/ [% t.fields.join(' ') %] / ],
198 [%- FOREACH fname IN t.types.keys %]
199 '[% fname %]' => '[% t.types.$fname %]',
206 for my $table ( @tables ) {
207 my $table_name = $table->{'table_name'};
208 next if $skip{ $table_name };
209 next if $skiplike && $table_name =~ qr/$skiplike/;
210 next if $takelike && $table_name !~ qr/$takelike/;
212 my ( $out_fh, $outfile );
213 if ( $mysql_loadfile ) {
214 $outfile = catfile( cwd(), "$table_name.txt" );
215 open $out_fh, ">$outfile" or
216 die "Can't write LOAD FILE to '$table_name': $!\n";
219 print "--\n-- Data for table '$table_name'\n--\n" unless $no_comments;
221 if ( $add_truncate ) {
222 print "TRUNCATE TABLE $table_name;\n";
226 'select ' . join(', ', @{ $table->{'fields'} } ) . " from $table_name"
228 my $sth = $db->prepare( $sql );
231 while ( my $rec = $sth->fetchrow_hashref ) {
233 for my $fld ( @{ $table->{'fields'} } ) {
234 my $val = $rec->{ $fld };
235 if ( $table->{'types'}{ $fld } eq 'string' ) {
236 if ( defined $val ) {
245 $val = defined $val ? $val : $mysql_loadfile ? '\N' : 'NULL';
250 if ( $mysql_loadfile ) {
251 print $out_fh join("\t", @vals), "\n";
254 print "INSERT INTO $table_name (".
255 join(', ', @{ $table->{'fields'} }) .
256 ') VALUES (', join(', ', @vals), ");\n";
261 print "LOAD DATA INFILE '$outfile' INTO TABLE $table_name ",
262 "FIELDS OPTIONALLY ENCLOSED BY '\\'';\n";
263 close $out_fh or die "Can't close filehandle: $!\n";
274 # -------------------------------------------------------------------
275 # To create a little flower is the labour of ages.
277 # -------------------------------------------------------------------
283 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.