8 |
# Convert PostgresSQL database dump file to something readable by isql ! |
# Convert PostgresSQL database dump file to something readable by isql ! |
9 |
|
|
10 |
# 2000-08-30 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org> |
# 2000-08-30 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org> |
11 |
# based on mysql2pgsql |
# based on mysql2pgsql changes are on: |
12 |
|
# http://cvs.linux.hr/cvsweb.cgi/sql/pgsql2interbase |
13 |
# |
# |
14 |
# Warning: bool datatype is converted to char(1) which will break |
# Warning: bool datatype is converted to char(1) which will break |
15 |
# your application if you tend to check true values with |
# your application if you tend to check true values with |
23 |
$table=""; |
$table=""; |
24 |
$triggers=""; # create triggers |
$triggers=""; # create triggers |
25 |
|
|
26 |
|
$|=1; |
27 |
|
|
28 |
while(<>) { |
while(<>) { |
29 |
chomp; |
chomp; |
30 |
|
|
31 |
|
# print "- $create:$_-\n"; |
32 |
|
|
33 |
|
if (/CREATE\s+TABLE\s+"([^"]+)"/i) { |
34 |
|
$table=$1 if (defined($1)); |
35 |
|
$create++; |
36 |
|
s/CREATE\s+TABLE\s+"[^"]+"/create table $table/i; |
37 |
|
} |
38 |
|
|
39 |
next if (/^\\connect/); |
next if (/^\\connect/); |
40 |
|
|
41 |
if ($create) { # are we inside create table? |
if ($create) { # are we inside create table? |
42 |
|
|
43 |
if (/DEFAULT nextval \( '"([^"]+)_seq"' \)/i) { |
if (/DEFAULT\s+nextval\s*\(\s*['"]+([^"']+)_seq["']+\s*\)/i) { |
44 |
$trig=$col=$1; |
$trig=$col=$1; |
45 |
$col=~s/(\w+)_([^_]+)/$1.$2/; |
$col=~s/(\w+)_([^_]+)/$1.$2/; |
46 |
$triggers.=" |
$triggers.=" |
52 |
end !! |
end !! |
53 |
set term ; !! |
set term ; !! |
54 |
"; |
"; |
55 |
s/ DEFAULT nextval \( '"[^"]+"' \)//i; |
$generator{$trig}--; |
56 |
|
|
57 |
|
s/DEFAULT\s+nextval\s*\([^\)]+\)//i; |
58 |
} |
} |
59 |
|
|
60 |
|
die "nextval not removed!" if (/nextval/); |
61 |
|
|
62 |
|
# nuke bool type definition on default |
63 |
s/DEFAULT bool/DEFAULT/i; |
s/DEFAULT bool/DEFAULT/i; |
64 |
|
|
65 |
# int(48...) -> int |
# int(48...) -> int |
74 |
# datetime -> timestamp |
# datetime -> timestamp |
75 |
s/datetime/timestamp/gi; |
s/datetime/timestamp/gi; |
76 |
|
|
77 |
|
# nuke quotes |
78 |
|
s/"([^"]+)"/$1/g; |
79 |
|
|
80 |
} else { # not inside create table |
} else { # not inside create table |
81 |
|
|
82 |
if (/CREATE SEQUENCE "(\w+)_seq" start (\d+)/i) { |
if (/CREATE SEQUENCE "(\w+)_seq" start (\d+)/i) { |
83 |
my ($gen,$start) = ($1,$2); |
my ($gen,$start) = ($1,$2); |
84 |
$sql.="create generator ${gen}_gen ;\n"; |
$sql.="create generator ${gen}_gen ;\n"; |
85 |
$sql.="set generator ${gen}_gen to $start ;\n"; |
$sql.="set generator ${gen}_gen to $start ;\n"; |
86 |
|
$generator{$gen}++; # to find unused generators |
87 |
next; |
next; |
88 |
} |
} |
89 |
|
|
105 |
my $table=$1; |
my $table=$1; |
106 |
my $line=<>; chomp $line; |
my $line=<>; chomp $line; |
107 |
while($line ne "\\.") { |
while($line ne "\\.") { |
108 |
$sql.="insert into \"$table\" values ("; |
$sql.="insert into $table values ("; |
109 |
undef @newarr; |
undef @newarr; |
110 |
foreach $var (split(/\t/,$line)) { |
foreach $var (split(/\t/,$line)) { |
111 |
if ($var eq "\\N") { |
if ($var eq "\\N") { |
129 |
my ($ind,$col) = ($1,$2); |
my ($ind,$col) = ($1,$2); |
130 |
$col=~s/" "[^"]+"/"/g; # nuke ops_name |
$col=~s/" "[^"]+"/"/g; # nuke ops_name |
131 |
$sql.="$ind ( $col );\n"; |
$sql.="$ind ( $col );\n"; |
132 |
|
# $sql.=s/"([^"]+)"/$1/g; |
133 |
next; |
next; |
134 |
} |
} |
135 |
|
|
136 |
} |
} |
137 |
|
|
138 |
|
if ($create && /\);/) { $create-- } |
139 |
|
|
140 |
|
$sql=~s/"([^"]+)"/$1/g; |
141 |
$sql.="$_\n"; |
$sql.="$_\n"; |
142 |
|
|
|
if (/CREATE TABLE "([^"]+)"/i) { |
|
|
$table=$1 if (defined($1)); |
|
|
$create++; |
|
|
} |
|
143 |
|
|
144 |
while ($sql=~/;/) { |
while ($sql=~/;/) { |
145 |
($dosql,$sql)=split(/;/,$sql,2); |
($dosql,$sql)=split(/;/,$sql,2); |
146 |
$dosql.=";"; # nuked by split, put it back! |
$dosql.=";"; # nuked by split, put it back! |
147 |
if ("$dosql" ne "") { |
if ("$dosql" ne "") { |
148 |
print "$dosql\n"; |
print "$dosql\n"; |
|
$create=0; |
|
149 |
} else { |
} else { |
150 |
print STDERR "empty sql!\n"; |
print STDERR "empty sql!\n"; |
151 |
} |
} |
157 |
|
|
158 |
print "\n"; |
print "\n"; |
159 |
|
|
160 |
|
foreach $gen (keys %generator) { |
161 |
|
warn "created, but overused/unused generator: $gen (ref.count: $generator{$gen})\n" if ($generator{$gen} != 0); |
162 |
|
} |
163 |
|
|