Commit | Line | Data |
fa44993c |
1 | package SQL::Translator::Producer::Dumper; |
2 | |
3 | # ------------------------------------------------------------------- |
478f608d |
4 | # Copyright (C) 2002-2006 SQLFairy Authors |
fa44993c |
5 | # |
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. |
9 | # |
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. |
14 | # |
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 |
18 | # 02111-1307 USA |
19 | # ------------------------------------------------------------------- |
20 | |
21 | =head1 NAME |
22 | |
23 | SQL::Translator::Producer::Dumper - SQL Dumper producer for SQL::Translator |
24 | |
25 | =head1 SYNOPSIS |
26 | |
27 | use SQL::Translator::Producer::Dumper; |
28 | |
29 | Options: |
30 | |
fa44993c |
31 | db_user Database username |
32 | db_password Database password |
33 | dsn DSN for DBI |
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 |
37 | |
38 | =head1 DESCRIPTION |
39 | |
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 |
49 | "database" argument. |
50 | |
51 | =cut |
52 | |
53 | use strict; |
54 | use Config; |
55 | use SQL::Translator; |
56 | use File::Temp 'tempfile'; |
57 | use Template; |
da06ac74 |
58 | use vars qw($VERSION); |
fa44993c |
59 | |
60 | use Data::Dumper; |
61 | |
da06ac74 |
62 | $VERSION = '1.99'; |
63 | |
fa44993c |
64 | sub produce { |
65 | my $t = shift; |
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+$//; $_ } |
75 | split (/,/, $skip); |
76 | my $sqlt_version = $t->version; |
77 | |
78 | if ( $parser_name =~ /Parser::(\w+)$/ ) { |
79 | $parser_name = $1 |
80 | } |
81 | |
82 | my %type_to_dbd = ( |
83 | MySQL => 'mysql', |
84 | Oracle => 'Oracle', |
85 | PostgreSQL => 'Pg', |
86 | SQLite => 'SQLite', |
87 | Sybase => 'Sybase', |
88 | ); |
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'; |
93 | } |
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)))"; |
97 | $db_pass = ''; |
98 | } |
99 | elsif ( $dbd eq 'mysql' && ! $args->{'dsn'} ) { |
100 | $dsn .= 'dbname'; |
101 | } |
102 | |
103 | my $template = Template->new; |
104 | my $template_text = template(); |
105 | my $out; |
106 | $template->process( |
107 | \$template_text, |
108 | { |
109 | translator => $t, |
110 | schema => $schema, |
111 | db_user => $db_user, |
112 | db_pass => $db_pass, |
113 | dsn => $dsn, |
114 | perl => $Config{'startperl'}, |
115 | skip => \%skip, |
116 | skiplike => $skiplike, |
117 | }, |
118 | \$out |
119 | ) or die $template->error; |
120 | |
121 | return $out; |
122 | } |
123 | |
124 | # ------------------------------------------------------------------- |
125 | sub template { |
126 | # |
127 | # Returns the template to be processed by Template Toolkit |
128 | # |
129 | return <<'EOF'; |
130 | [% perl || '#!/usr/bin/perl' %] |
131 | [% USE date %] |
132 | # |
133 | # Generated by SQL::Translator [% translator.version %] |
134 | # [% date.format( date.now, "%Y-%m-%d" ) %] |
135 | # For more info, see http://sqlfairy.sourceforge.net/ |
136 | # |
137 | |
138 | use strict; |
139 | use Cwd; |
140 | use DBI; |
141 | use Getopt::Long; |
142 | use File::Spec::Functions 'catfile'; |
143 | |
b7df9b7e |
144 | my ( $help, $add_truncate, $skip, $skiplike, $no_comments, |
145 | $takelike, $mysql_loadfile ); |
fa44993c |
146 | GetOptions( |
147 | 'add-truncate' => \$add_truncate, |
148 | 'h|help' => \$help, |
149 | 'no-comments' => \$no_comments, |
150 | 'mysql-loadfile' => \$mysql_loadfile, |
151 | 'skip:s' => \$skip, |
152 | 'skiplike:s' => \$skiplike, |
b7df9b7e |
153 | 'takelike:s' => \$takelike, |
fa44993c |
154 | ); |
155 | |
156 | if ( $help ) { |
157 | print <<"USAGE"; |
158 | Usage: |
25afc779 |
159 | $0 [options] > dump.sql |
fa44993c |
160 | |
161 | Options: |
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 |
f4086310 |
167 | --skiplike=regex Regular expression of table names to skip |
168 | --takelike=regex Regular expression of table names to take |
fa44993c |
169 | |
170 | USAGE |
171 | exit(0); |
172 | } |
173 | |
174 | $no_comments = 1 if $mysql_loadfile; |
175 | |
176 | [%- |
177 | SET table_defs = []; |
178 | SET max_field = 0; |
179 | |
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)"); |
184 | |
185 | SET field_names = []; |
186 | SET types = {}; |
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; |
43cf00a1 |
191 | types.$field_name = field.data_type.match( '(char|str|long|text|enum|date)' ) |
fa44993c |
192 | ? 'string' : 'number'; |
193 | field_names.push( field_name ); |
194 | END; |
195 | |
196 | table_defs.push({ |
197 | name => table_name, |
198 | types => types, |
199 | fields => field_names, |
200 | }); |
201 | END |
202 | -%] |
203 | |
e96fe4d3 |
204 | my $db = DBI->connect( |
205 | '[% dsn %]', |
206 | '[% db_user %]', |
207 | '[% db_pass %]', |
208 | { RaiseError => 1 } |
209 | ); |
fa44993c |
210 | my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ } split (/,/, $skip); |
211 | my @tables = ( |
212 | [%- FOREACH t IN table_defs %] |
213 | { |
214 | table_name => '[% t.name %]', |
215 | fields => [ qw/ [% t.fields.join(' ') %] / ], |
216 | types => { |
217 | [%- FOREACH fname IN t.types.keys %] |
218 | '[% fname %]' => '[% t.types.$fname %]', |
219 | [%- END %] |
220 | }, |
221 | }, |
222 | [%- END %] |
223 | ); |
224 | |
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/; |
b7df9b7e |
229 | next if $takelike && $table_name !~ qr/$takelike/; |
fa44993c |
230 | |
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"; |
236 | } |
237 | |
238 | print "--\n-- Data for table '$table_name'\n--\n" unless $no_comments; |
239 | |
240 | if ( $add_truncate ) { |
241 | print "TRUNCATE TABLE $table_name;\n"; |
242 | } |
243 | |
ea378e76 |
244 | my $sql = |
245 | 'select ' . join(', ', @{ $table->{'fields'} } ) . " from $table_name" |
246 | ; |
247 | my $sth = $db->prepare( $sql ); |
248 | $sth->execute; |
fa44993c |
249 | |
ea378e76 |
250 | while ( my $rec = $sth->fetchrow_hashref ) { |
fa44993c |
251 | my @vals; |
252 | for my $fld ( @{ $table->{'fields'} } ) { |
253 | my $val = $rec->{ $fld }; |
254 | if ( $table->{'types'}{ $fld } eq 'string' ) { |
255 | if ( defined $val ) { |
256 | $val =~ s/'/\\'/g; |
257 | $val = qq['$val'] |
258 | } |
259 | else { |
260 | $val = qq['']; |
261 | } |
262 | } |
263 | else { |
264 | $val = defined $val ? $val : $mysql_loadfile ? '\N' : 'NULL'; |
265 | } |
266 | push @vals, $val; |
267 | } |
268 | |
269 | if ( $mysql_loadfile ) { |
270 | print $out_fh join("\t", @vals), "\n"; |
271 | } |
272 | else { |
273 | print "INSERT INTO $table_name (". |
274 | join(', ', @{ $table->{'fields'} }) . |
afff8ac6 |
275 | ') VALUES (', join(', ', @vals), ");\n"; |
fa44993c |
276 | } |
277 | } |
278 | |
279 | if ( $out_fh ) { |
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"; |
283 | } |
284 | else { |
285 | print "\n"; |
286 | } |
287 | } |
288 | EOF |
289 | } |
290 | |
291 | 1; |
292 | |
293 | # ------------------------------------------------------------------- |
294 | # To create a little flower is the labour of ages. |
295 | # William Blake |
296 | # ------------------------------------------------------------------- |
297 | |
298 | =pod |
299 | |
300 | =head1 AUTHOR |
301 | |
ea378e76 |
302 | Ken Youens-Clark E<lt>kclark@cpan.orgE<gt>. |
fa44993c |
303 | |
304 | =cut |