2 # convert a solid dump to a postgres dump
13 # Maps into postgres types
16 "varchar(254)" => "character varying",
17 "integer" => "integer",
19 "decimal(16,2)" => "real",
20 "timestamp" => "timestamp"
23 #####################################################################
24 # read a file into a string
27 my($filename) = shift;
29 open(INPUTFILE, $filename) || die "can't open $filename";
30 my($saved_delim) = $/;
32 my($data) = <INPUTFILE>;
38 ########################################################################
39 # remove double quotes from a string
43 if ($n =~ /^\"(\S+)\"$/) {
49 ########################################################################
50 # change a table name of form "DAVID"."FOO" into foo
54 if ($n =~ /^\"(\w+)\"\.\"(\w+)\"$/) {
57 return lc NoQuotes($n);
61 ########################################################################
62 # fix up a data field on load
69 # fix some bad date fields
70 if ($v =~ /1(19\d\d-\d\d-0)/) {
71 print "Fixed bad date $v as 1900-01-01\n";
85 ########################################################################
86 # process a 'LOAD DATA' via COPY FROM
99 if ($line =~ /^\)$/) {
102 if ($line =~ /^INFILE '(.*)'/) {
106 if ($line =~ /^INTO TABLE (\S+)/) {
107 $table = CvtTable($1);
110 if ($line =~ /^FIELDS TERMINATED BY '(.*)'$/) {
114 if ($line =~ /^\t(\S+) /) {
116 $fields .= ", " . lc NoQuotes($1);
118 $fields .= lc NoQuotes($1);
122 print "Load data into $table from $fname\n";
124 my $data = FileLoad($fname);
126 my @lines = split /[^\r]$/ms, $data;
128 my $count = $#lines + 1;
132 print OUTPUT "COPY $table FROM stdin;\n";
134 for (my $i=0; $i <= $#lines; $i++) {
137 if (! ($l =~ /\'/)) { next; }
139 if ($l =~ /^'(.*?)',(.*)$/ms) {
143 } elsif ($l =~ /^(.*?),(.*)$/ms) {
147 } elsif ($l =~ /\'(.*)/) {
151 } elsif ($l =~ /^NUL$/) {
152 $v = FixData("NULL");
161 print OUTPUT "\\.\n\n";
163 print "$count/$count\n";
167 ########################################################################
168 # process a 'CREATE INDEX'
173 if ($line =~ /(\S+) ON (\S+) \((\S+)\)/) {
174 my $index = CvtTable($1);
175 my $table = CvtTable($2);
176 my @fields = split(',', $3);
178 print "Create index $index\n";
179 print OUTPUT "CREATE INDEX $index ON $table (";
180 for (my $i=0; $i <= $#fields; $i++) {
181 my $f = lc NoQuotes($fields[$i]);
187 print OUTPUT ");\n\n";
192 ########################################################################
193 # process a 'CREATE VIEW'
198 if ($line =~ /(\w+) AS SELECT (.*);$/) {
202 print "Create view $view\n";
203 print OUTPUT "CREATE VIEW $view AS SELECT $query;\n\n";
208 ########################################################################
209 # process a 'CREATE TABLE'
216 $table_name = CvtTable($table_name);
218 print "Creating table $table_name\n";
229 if ($_ =~ /^\);/) { last; }
230 if ($_ =~ /^$/) { last; }
232 if ($_ =~ /UNIQUE (.*)$/) {
240 if ($line =~ /(.*?),$/) {
244 if ($line =~ / NOT NULL/) {
248 if ($line =~ /(\S+) (\S+)/) {
249 $name = lc NoQuotes($1);
251 if ($type =~ /(.*)\);$/) {
256 if (! $TypeMap{$type}) {
257 die "Unknown type $type for field $name in table $table_name\n";
260 $type = $TypeMap{$type};
262 $fields[$nfields]->{'type'} = $type;
263 $fields[$nfields]->{'name'} = $name;
267 print OUTPUT "CREATE TABLE $table_name (\n";
268 for (my $i=0; $i < $nfields; $i++) {
272 print OUTPUT "\t$fields[$i]->{'name'} $fields[$i]->{'type'}";
276 print OUTPUT ",\n\tUNIQUE $unique";
279 print OUTPUT "\n);\n\n";
283 ########################################################################
287 my($filename) = shift;
289 print "Processing $filename\n";
291 open(INPUT,"<$filename") || die "Can't open $filename";
294 if ($_ =~ /^CREATE TABLE (\S+) \(/) {
298 if ($_ =~ /^CREATE INDEX (.*)/) {
302 if ($_ =~ /^CREATE VIEW (.*)/) {
306 if ($_ =~ /^LOAD DATA/) {
315 #########################################
320 solid to postgres converter
321 Copyright tridge\@samba.org 2002
323 Usage: solid_convert.pl [options] <files>
326 --help this help page
327 --output FILE file to output to
337 'help|h|?' => \$opt_help,
338 'output=s' => \$opt_output,
346 die "ERROR: You must specify an output file with --output" unless ($opt_output);
348 open(OUTPUT,">$opt_output") || die "ERROR: Failed to open $opt_output";
350 for (my $i=0; $i <= $#ARGV; $i++) {
351 process_file($ARGV[$i]);