/[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.3 - (show annotations)
Tue Aug 1 06:48:05 2000 UTC (23 years, 8 months ago) by dpavlin
Branch: MAIN
Changes since 1.2: +32 -9 lines
create table is finally on right place, added checking of created generators
and nuking of quotes (so that you can use un-quoted clumns names in queries)

1 #!/usr/local/bin/perl -w
2
3 # PostgreSQL to InterBase dump file converter
4 #
5 # usage:
6 # cat pg_db_name | ./mysql2pgsql > 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 # 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/);
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 print STDERR "Warning: bool emulated by char(1)\n\n";
72 }
73
74 # datetime -> timestamp
75 s/datetime/timestamp/gi;
76
77 # nuke quotes
78 s/"([^"]+)"/$1/g;
79
80 } else { # not inside create table
81
82 if (/CREATE SEQUENCE "(\w+)_seq" start (\d+)/i) {
83 my ($gen,$start) = ($1,$2);
84 $sql.="create generator ${gen}_gen ;\n";
85 $sql.="set generator ${gen}_gen to $start ;\n";
86 $generator{$gen}++; # to find unused generators
87 next;
88 }
89
90 # left-over from create sequnce
91 next if (/^SELECT nextval/i);
92
93 # you will have to re-write functions manually!
94 if (/^CREATE FUNCTION/i) {
95 print STDERR "functions not supported: $_\n\n";
96 next;
97 }
98
99 # rule is usually a defined view
100 if (/^CREATE RULE/i) {
101 print STDERR "rules (views...) not supported: $_\n\n";
102 next;
103 }
104 if (/COPY "([^"]+)" FROM stdin/i) {
105 my $table=$1;
106 my $line=<>; chomp $line;
107 while($line ne "\\.") {
108 $sql.="insert into $table values (";
109 undef @newarr;
110 foreach $var (split(/\t/,$line)) {
111 if ($var eq "\\N") {
112 push @newarr,"null";
113 } elsif ($var=~/^\d+$/ || $var=~/^\d+\.\d+$/) {
114 push @newarr,"$var";
115 } elsif ($var=~/\w{3} (\w{3}) (\d+) (\d\d:\d\d:\d\d) (\d{4})/) {
116 # timestamp
117 push @newarr,"'$2-$1-$4 $3'";
118 } else {
119 push @newarr,"'$var'";
120 }
121 }
122 $sql.=join(",",@newarr).");\n";
123 $line=<>; chomp $line;
124 }
125 next;
126 }
127
128 if (/(CREATE \w*\s*INDEX "[^"]+" on "[^"]+")[^(]*\(([^\)]+)\)/i) {
129 my ($ind,$col) = ($1,$2);
130 $col=~s/" "[^"]+"/"/g; # nuke ops_name
131 $sql.="$ind ( $col );\n";
132 # $sql.=s/"([^"]+)"/$1/g;
133 next;
134 }
135
136 }
137
138 if ($create && /\);/) { $create-- }
139
140 $sql=~s/"([^"]+)"/$1/g;
141 $sql.="$_\n";
142
143
144 while ($sql=~/;/) {
145 ($dosql,$sql)=split(/;/,$sql,2);
146 $dosql.=";"; # nuked by split, put it back!
147 if ("$dosql" ne "") {
148 print "$dosql\n";
149 } else {
150 print STDERR "empty sql!\n";
151 }
152 }
153
154 }
155
156 print "$sql\n$triggers\n";
157
158 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

  ViewVC Help
Powered by ViewVC 1.1.26