3 # -------------------------------------------------------------------
4 # $Id: sqlt-dumper,v 1.1 2003-08-26 03:56:43 kycl4rk Exp $
5 # -------------------------------------------------------------------
6 # Copyright (C) 2003 Ken Y. Clark <kclark@cpan.org>
8 # This program is free software; you can redistribute it and/or
9 # modify it under the terms of the GNU General Public License as
10 # published by the Free Software Foundation; version 2.
12 # This program is distributed in the hope that it will be useful, but
13 # WITHOUT ANY WARRANTY; without even the implied warranty of
14 # MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
15 # General Public License for more details.
17 # You should have received a copy of the GNU General Public License
18 # along with this program; if not, write to the Free Software
19 # Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA
21 # -------------------------------------------------------------------
25 sqlt-dumper - create a dumper script from a schema
29 sqlt-dumper -d Oracle [options] schema.sql > dumper.pl
31 ./dumper.pl > data.sql
35 -h|--help Show help and exit
36 --add-truncate Add "TRUNCATE TABLE" statements for each table
37 --skip=t1[,t2] Skip tables in comma-separated list
38 -u|--user Database username
39 -p|--password Database password
44 This script uses SQL::Translator to parse the SQL schema and create a
45 Perl script that can connect to the database and dump the data as
46 INSERT statements a la mysqldump. If you enable "add-truncate" or
47 specify tables to "skip," then the generated dumper script will have
48 those hardcoded. However, these will also be options in the generated
49 dumper, so you can wait to specify these options when you dump your
50 database. The database username, password, and DSN can be hardcoded
51 into the generated script, or part of the DSN can be intuited from the
56 # -------------------------------------------------------------------
64 $VERSION = sprintf "%d.%02d", q$Revision: 1.1 $ =~ /(\d+)\.(\d+)/;
66 my ( $help, $db, $add_truncate, $skip, $db_user, $db_pass, $dsn );
69 'd|f|from|db=s' => \$db,
70 'add-truncate' => \$add_truncate,
72 'u|user:s' => \$db_user,
73 'p|password:s' => \$db_pass,
77 pod2usage(0) if $help;
78 pod2usage( 'No database driver specified' ) unless $db;
79 $db_user ||= 'username';
80 $db_pass ||= 'password';
83 my $file = shift @ARGV or pod2usage( -msg => 'No input file' );
85 my $t = SQL::Translator->new;
86 $t->parser( $db ) or die $t->error, "\n";
87 $t->filename( $file ) or die $t->error, "\n";
89 my %skip = map { $_, 1 } map { s/^\s+|\s+$//; $_ } split (/,/, $skip);
90 my $parser = $t->parser or die $t->error;
91 $parser->($t, $t->data);
92 my $schema = $t->schema;
100 # By sqlt-dumper.pl, part of the SQLFairy project
101 # For more info, see http://sqlfairy.sourceforge.net/
108 my ( \$help, \$add_truncate, \$skip );
110 'h|help' => \\\$help,
111 'add-truncate' => \\\$add_truncate,
112 'skip:s' => \\\$skip,
121 -h|--help Show help and exit
122 --add-truncate Add "TRUNCATE TABLE" statements
123 --skip=t1[,t2] Comma-separated list of tables to skip
129 my \%skip = map { \$_, 1 } map { s/^\\s+|\\s+\$//; \$_ } split (/,/, \$skip);
130 my \$db = DBI->connect('$dsn', '$db_user', '$db_pass');
134 for my $table ( $schema->get_tables ) {
135 my $table_name = $table->name;
136 next if $skip{ $table_name };
137 my ( @field_names, %types );
138 for my $field ( $table->get_fields ) {
139 $types{ $field->name } = $field->data_type =~ m/(char|str|long|text)/
140 ? 'string' : 'number';
141 push @field_names, $field->name;
145 "#\n# Table: $table_name\n#\n{\n",
146 " next if \$skip{'$table_name'};\n",
147 " print \"--\\n-- Data for table '$table_name'\\n--\\n\";\n\n",
148 " if ( \$add_truncate ) {\n",
149 " print \"TRUNCATE TABLE $table_name;\\n\";\n",
153 my $insert = "INSERT INTO $table_name (". join(', ', @field_names).
156 if ( $add_truncate ) {
157 $out .= " print \"TRUNCATE TABLE $table_name;\\n\";\n";
162 join("\n", map { " $_ => '$types{ $_ }'," } @field_names),
164 " my \$data = \$db->selectall_arrayref(\n",
165 " 'select ", join(', ', @field_names), " from $table_name',\n",
166 " { Columns => {} },\n",
168 " for my \$rec ( \@{ \$data } ) {\n",
170 " for my \$fld ( qw[", join(' ', @field_names), "] ) {\n",
171 " my \$val = \$rec->{ \$fld };\n",
172 " if ( \$types{ \$fld } eq 'string' ) {\n",
173 " \$val =~ s/'/\\'/g;\n",
174 " \$val = defined \$val ? qq['\$val'] : qq[''];\n",
177 " \$val = defined \$val ? \$val : 'NULL';\n",
179 " push \@vals, \$val;\n",
181 " print \"$insert\", join(', ', \@vals), \");\\n\";\n",
191 # -------------------------------------------------------------------
197 Ken Y. Clark E<lt>kclark@cpan.orgE<gt>.
201 perl, SQL::Translator.