/[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

Annotation of /pgsql2interbase

Parent Directory Parent Directory | Revision Log Revision Log


Revision 1.5 - (hide 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 dpavlin 1.1 #!/usr/local/bin/perl -w
2    
3     # PostgreSQL to InterBase dump file converter
4     #
5     # usage:
6 dpavlin 1.5 # cat pg_db_name | ./pgsql2interbase > interbase.sql
7 dpavlin 1.1 #
8     # Convert PostgresSQL database dump file to something readable by isql !
9    
10     # 2000-08-30 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
11 dpavlin 1.3 # based on mysql2pgsql changes are on:
12     # http://cvs.linux.hr/cvsweb.cgi/sql/pgsql2interbase
13 dpavlin 1.2 #
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 dpavlin 1.1
22     $create=0; # inside create table?
23     $table="";
24     $triggers=""; # create triggers
25    
26 dpavlin 1.3 $|=1;
27    
28 dpavlin 1.1 while(<>) {
29     chomp;
30    
31 dpavlin 1.4 # warn "- $create:$_-\n";
32 dpavlin 1.3
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 dpavlin 1.1 next if (/^\\connect/);
40    
41     if ($create) { # are we inside create table?
42    
43 dpavlin 1.3 if (/DEFAULT\s+nextval\s*\(\s*['"]+([^"']+)_seq["']+\s*\)/i) {
44 dpavlin 1.1 $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 dpavlin 1.3 $generator{$trig}--;
56    
57     s/DEFAULT\s+nextval\s*\([^\)]+\)//i;
58 dpavlin 1.1 }
59 dpavlin 1.3
60     die "nextval not removed!" if (/nextval/);
61    
62     # nuke bool type definition on default
63 dpavlin 1.1 s/DEFAULT bool/DEFAULT/i;
64    
65     # int(48...) -> int
66     s/\w*int\d+/ int/gi;
67    
68     # bool -> char(1)
69 dpavlin 1.2 if (/bool/i) {
70     s/\w*bool/ char(1)/gi;
71 dpavlin 1.4 warn "Warning: bool emulated by char(1)\n\n";
72 dpavlin 1.2 }
73 dpavlin 1.1
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 dpavlin 1.3 $generator{$gen}++; # to find unused generators
84 dpavlin 1.1 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 dpavlin 1.4 warn "functions not supported: $_\n\n";
93 dpavlin 1.1 next;
94     }
95    
96     # rule is usually a defined view
97     if (/^CREATE RULE/i) {
98 dpavlin 1.4 warn "rules (views...) not supported: $_\n\n";
99 dpavlin 1.1 next;
100     }
101     if (/COPY "([^"]+)" FROM stdin/i) {
102     my $table=$1;
103     my $line=<>; chomp $line;
104     while($line ne "\\.") {
105 dpavlin 1.3 $sql.="insert into $table values (";
106 dpavlin 1.1 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 dpavlin 1.4 $sql.=lc($ind)." ( $col );\n";
129 dpavlin 1.1 next;
130     }
131    
132 dpavlin 1.4 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 dpavlin 1.1 }
146    
147 dpavlin 1.3 if ($create && /\);/) { $create-- }
148 dpavlin 1.1
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 dpavlin 1.4 $dosql=~s/"([^"]+)"/$1/g; # nuke quotes
157 dpavlin 1.1 print "$dosql\n";
158     } else {
159 dpavlin 1.4 warn "empty sql!\n";
160 dpavlin 1.1 }
161     }
162    
163     }
164    
165 dpavlin 1.4 $sql=~s/"([^"]+)"/$1/g; # nuke quotes
166 dpavlin 1.1 print "$sql\n$triggers\n";
167    
168 dpavlin 1.3 foreach $gen (keys %generator) {
169     warn "created, but overused/unused generator: $gen (ref.count: $generator{$gen})\n" if ($generator{$gen} != 0);
170     }
171 dpavlin 1.4
172     warn "Users used: ",join(", ",keys(%users)),"\n";
173     warn "Groups used: ",join(", ",keys(%groups)),"\n";
174 dpavlin 1.1

  ViewVC Help
Powered by ViewVC 1.1.26