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';
41 use vars qw($VERSION);
49 my $args = $t->producer_args;
50 my $schema = $t->schema;
51 my $add_truncate = $args->{'add_truncate'} || 0;
52 my $skip = $args->{'skip'} || '';
53 my $skiplike = $args->{'skiplike'} || '';
54 my $db_user = $args->{'db_user'} || 'db_user';
55 my $db_pass = $args->{'db_password'} || 'db_pass';
56 my $parser_name = $t->parser_type;
57 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ }
59 my $sqlt_version = $t->version;
61 if ( $parser_name =~ /Parser::(\w+)$/ ) {
72 my $dbd = $type_to_dbd{ $parser_name } || 'DBD';
73 my $dsn = $args->{'dsn'} || "dbi:$dbd:";
74 if ( $dbd eq 'Pg' && ! $args->{'dsn'} ) {
75 $dsn .= 'dbname=dbname;host=hostname';
77 elsif ( $dbd eq 'Oracle' && ! $args->{'dsn'} ) {
78 $db_user = "$db_user/$db_pass@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)" .
79 "(HOST=hostname)(PORT=1521))(CONNECT_DATA=(SID=sid)))";
82 elsif ( $dbd eq 'mysql' && ! $args->{'dsn'} ) {
86 my $template = Template->new;
87 my $template_text = template();
97 perl => $Config{'startperl'},
99 skiplike => $skiplike,
102 ) or die $template->error;
109 # Returns the template to be processed by Template Toolkit
112 [% perl || '#!/usr/bin/perl' %]
115 # Generated by SQL::Translator [% translator.version %]
116 # [% date.format( date.now, "%Y-%m-%d" ) %]
117 # For more info, see http://sqlfairy.sourceforge.net/
124 use File::Spec::Functions 'catfile';
126 my ( $help, $add_truncate, $skip, $skiplike, $no_comments,
127 $takelike, $mysql_loadfile );
129 'add-truncate' => \$add_truncate,
131 'no-comments' => \$no_comments,
132 'mysql-loadfile' => \$mysql_loadfile,
134 'skiplike:s' => \$skiplike,
135 'takelike:s' => \$takelike,
141 $0 [options] > dump.sql
144 -h|--help Show help and exit
145 --add-truncate Add "TRUNCATE TABLE" statements
146 --mysql-loadfile Create MySQL's LOAD FILE syntax, not INSERTs
147 --no-comments Suppress comments
148 --skip=t1[,t2] Comma-separated list of tables to skip
149 --skiplike=regex Regular expression of table names to skip
150 --takelike=regex Regular expression of table names to take
156 $no_comments = 1 if $mysql_loadfile;
162 FOREACH table IN schema.get_tables;
163 SET table_name = table.name;
164 NEXT IF skip.$table_name;
165 NEXT IF skiplike AND table_name.match("(?:$skiplike)");
167 SET field_names = [];
169 FOR field IN table.get_fields;
170 field_name = field.name;
171 fname_len = field.name.length;
172 max_field = fname_len > max_field ? fname_len : max_field;
173 types.$field_name = field.data_type.match( '(char|str|long|text|enum|date)' )
174 ? 'string' : 'number';
175 field_names.push( field_name );
181 fields => field_names,
186 my $db = DBI->connect(
192 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ } split (/,/, $skip);
194 [%- FOREACH t IN table_defs %]
196 table_name => '[% t.name %]',
197 fields => [ qw/ [% t.fields.join(' ') %] / ],
199 [%- FOREACH fname IN t.types.keys %]
200 '[% fname %]' => '[% t.types.$fname %]',
207 for my $table ( @tables ) {
208 my $table_name = $table->{'table_name'};
209 next if $skip{ $table_name };
210 next if $skiplike && $table_name =~ qr/$skiplike/;
211 next if $takelike && $table_name !~ qr/$takelike/;
213 my ( $out_fh, $outfile );
214 if ( $mysql_loadfile ) {
215 $outfile = catfile( cwd(), "$table_name.txt" );
216 open $out_fh, ">$outfile" or
217 die "Can't write LOAD FILE to '$table_name': $!\n";
220 print "--\n-- Data for table '$table_name'\n--\n" unless $no_comments;
222 if ( $add_truncate ) {
223 print "TRUNCATE TABLE $table_name;\n";
227 'select ' . join(', ', @{ $table->{'fields'} } ) . " from $table_name"
229 my $sth = $db->prepare( $sql );
232 while ( my $rec = $sth->fetchrow_hashref ) {
234 for my $fld ( @{ $table->{'fields'} } ) {
235 my $val = $rec->{ $fld };
236 if ( $table->{'types'}{ $fld } eq 'string' ) {
237 if ( defined $val ) {
246 $val = defined $val ? $val : $mysql_loadfile ? '\N' : 'NULL';
251 if ( $mysql_loadfile ) {
252 print $out_fh join("\t", @vals), "\n";
255 print "INSERT INTO $table_name (".
256 join(', ', @{ $table->{'fields'} }) .
257 ') VALUES (', join(', ', @vals), ");\n";
262 print "LOAD DATA INFILE '$outfile' INTO TABLE $table_name ",
263 "FIELDS OPTIONALLY ENCLOSED BY '\\'';\n";
264 close $out_fh or die "Can't close filehandle: $!\n";
275 # -------------------------------------------------------------------
276 # To create a little flower is the labour of ages.
278 # -------------------------------------------------------------------
284 Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>.