/[sql]/pgsql2interbase
This is repository of my old source code which isn't updated any more. Go to git.rot13.org for current projects!
ViewVC logotype

Contents of /pgsql2interbase

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.5 - (show annotations)
Fri Feb 22 17:21:47 2002 UTC (22 years, 1 month ago) by dpavlin
Branch: MAIN
CVS Tags: HEAD
Changes since 1.4: +1 -1 lines
typo fix

1 #!/usr/local/bin/perl -w
2
3 # PostgreSQL to InterBase dump file converter
4 #
5 # usage:
6 # cat pg_db_name | ./pgsql2interbase > interbase.sql
7 #
8 # Convert PostgresSQL database dump file to something readable by isql !
9
10 # 2000-08-30 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
11 # 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
15 # your application if you tend to check true values with
16 # if ($foo)
17 # and not with
18 # if ($foo = 't')
19 # In data from InterBase it will always return true which is wrong!
20 #
21
22 $create=0; # inside create table?
23 $table="";
24 $triggers=""; # create triggers
25
26 $|=1;
27
28 while(<>) {
29 chomp;
30
31 # warn "- $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/);
40
41 if ($create) { # are we inside create table?
42
43 if (/DEFAULT\s+nextval\s*\(\s*['"]+([^"']+)_seq["']+\s*\)/i) {
44 $trig=$col=$1;
45 $col=~s/(\w+)_([^_]+)/$1.$2/;
46 $triggers.="
47 set term !! ;
48 create trigger ${trig}_trig for $table
49 before insert position 0
50 as begin
51 $col = gen_id(${trig}_gen,1) ;
52 end !!
53 set term ; !!
54 ";
55 $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;
64
65 # int(48...) -> int
66 s/\w*int\d+/ int/gi;
67
68 # bool -> char(1)
69 if (/bool/i) {
70 s/\w*bool/ char(1)/gi;
71 warn "Warning: bool emulated by char(1)\n\n";
72 }
73
74 # datetime -> timestamp
75 s/datetime/timestamp/gi;
76
77 } else { # not inside create table
78
79 if (/CREATE SEQUENCE "(\w+)_seq" start (\d+)/i) {
80 my ($gen,$start) = ($1,$2);
81 $sql.="create generator ${gen}_gen ;\n";
82 $sql.="set generator ${gen}_gen to $start ;\n";
83 $generator{$gen}++; # to find unused generators
84 next;
85 }
86
87 # left-over from create sequnce
88 next if (/^SELECT nextval/i);
89
90 # you will have to re-write functions manually!
91 if (/^CREATE FUNCTION/i) {
92 warn "functions not supported: $_\n\n";
93 next;
94 }
95
96 # rule is usually a defined view
97 if (/^CREATE RULE/i) {
98 warn "rules (views...) not supported: $_\n\n";
99 next;
100 }
101 if (/COPY "([^"]+)" FROM stdin/i) {
102 my $table=$1;
103 my $line=<>; chomp $line;
104 while($line ne "\\.") {
105 $sql.="insert into $table values (";
106 undef @newarr;
107 foreach $var (split(/\t/,$line)) {
108 if ($var eq "\\N") {
109 push @newarr,"null";
110 } elsif ($var=~/^\d+$/ || $var=~/^\d+\.\d+$/) {
111 push @newarr,"$var";
112 } elsif ($var=~/\w{3} (\w{3}) (\d+) (\d\d:\d\d:\d\d) (\d{4})/) {
113 # timestamp
114 push @newarr,"'$2-$1-$4 $3'";
115 } else {
116 push @newarr,"'$var'";
117 }
118 }
119 $sql.=join(",",@newarr).");\n";
120 $line=<>; chomp $line;
121 }
122 next;
123 }
124
125 if (/(CREATE \w*\s*INDEX "[^"]+" on "[^"]+")[^(]*\(([^\)]+)\)/i) {
126 my ($ind,$col) = ($1,$2);
127 $col=~s/" "[^"]+"/"/g; # nuke ops_name
128 $sql.=lc($ind)." ( $col );\n";
129 next;
130 }
131
132 if (/GRANT (.+) on "([^"]+)" to ([^;]+);/i) {
133 ($what,$table,$user) = ($1,$2,$3);
134 $user=~s/"//g;
135 if ($user =~ /group\s+(\S+)/i) {
136 $sql.="grant $what on $table to group $1 ;\n";
137 $groups{$1}++;
138 } else {
139 $sql.="grant $what on $table to $user ;\n";
140 $users{$user}++;
141 }
142 next ;
143 }
144
145 }
146
147 if ($create && /\);/) { $create-- }
148
149 $sql.="$_\n";
150
151
152 while ($sql=~/;/) {
153 ($dosql,$sql)=split(/;/,$sql,2);
154 $dosql.=";"; # nuked by split, put it back!
155 if ("$dosql" ne "") {
156 $dosql=~s/"([^"]+)"/$1/g; # nuke quotes
157 print "$dosql\n";
158 } else {
159 warn "empty sql!\n";
160 }
161 }
162
163 }
164
165 $sql=~s/"([^"]+)"/$1/g; # nuke quotes
166 print "$sql\n$triggers\n";
167
168 foreach $gen (keys %generator) {
169 warn "created, but overused/unused generator: $gen (ref.count: $generator{$gen})\n" if ($generator{$gen} != 0);
170 }
171
172 warn "Users used: ",join(", ",keys(%users)),"\n";
173 warn "Groups used: ",join(", ",keys(%groups)),"\n";
174

  ViewVC Help
Powered by ViewVC 1.1.26