/[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.2 - (hide annotations)
Mon Jul 31 08:55:32 2000 UTC (23 years, 8 months ago) by dpavlin
Branch: MAIN
Changes since 1.1: +12 -1 lines
better documentation for bool emulation by char(1)

1 dpavlin 1.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
12 dpavlin 1.2 #
13     # Warning: bool datatype is converted to char(1) which will break
14     # your application if you tend to check true values with
15     # if ($foo)
16     # and not with
17     # if ($foo = 't')
18     # In data from InterBase it will always return true which is wrong!
19     #
20 dpavlin 1.1
21     $create=0; # inside create table?
22     $table="";
23     $triggers=""; # create triggers
24    
25     while(<>) {
26     chomp;
27    
28     next if (/^\\connect/);
29    
30     if ($create) { # are we inside create table?
31    
32     if (/DEFAULT nextval \( '"([^"]+)_seq"' \)/i) {
33     $trig=$col=$1;
34     $col=~s/(\w+)_([^_]+)/$1.$2/;
35     $triggers.="
36     set term !! ;
37     create trigger ${trig}_trig for $table
38     before insert position 0
39     as begin
40     $col = gen_id(${trig}_gen,1) ;
41     end !!
42     set term ; !!
43     ";
44     s/ DEFAULT nextval \( '"[^"]+"' \)//i;
45     }
46     s/DEFAULT bool/DEFAULT/i;
47    
48     # int(48...) -> int
49     s/\w*int\d+/ int/gi;
50    
51     # bool -> char(1)
52 dpavlin 1.2 if (/bool/i) {
53     s/\w*bool/ char(1)/gi;
54     print STDERR "Warning: bool emulated by char(1)\n\n";
55     }
56 dpavlin 1.1
57     # datetime -> timestamp
58     s/datetime/timestamp/gi;
59    
60     } else { # not inside create table
61    
62     if (/CREATE SEQUENCE "(\w+)_seq" start (\d+)/i) {
63     my ($gen,$start) = ($1,$2);
64     $sql.="create generator ${gen}_gen ;\n";
65     $sql.="set generator ${gen}_gen to $start ;\n";
66     next;
67     }
68    
69     # left-over from create sequnce
70     next if (/^SELECT nextval/i);
71    
72     # you will have to re-write functions manually!
73     if (/^CREATE FUNCTION/i) {
74     print STDERR "functions not supported: $_\n\n";
75     next;
76     }
77    
78     # rule is usually a defined view
79     if (/^CREATE RULE/i) {
80     print STDERR "rules (views...) not supported: $_\n\n";
81     next;
82     }
83     if (/COPY "([^"]+)" FROM stdin/i) {
84     my $table=$1;
85     my $line=<>; chomp $line;
86     while($line ne "\\.") {
87     $sql.="insert into \"$table\" values (";
88     undef @newarr;
89     foreach $var (split(/\t/,$line)) {
90     if ($var eq "\\N") {
91     push @newarr,"null";
92     } elsif ($var=~/^\d+$/ || $var=~/^\d+\.\d+$/) {
93     push @newarr,"$var";
94     } elsif ($var=~/\w{3} (\w{3}) (\d+) (\d\d:\d\d:\d\d) (\d{4})/) {
95     # timestamp
96     push @newarr,"'$2-$1-$4 $3'";
97     } else {
98     push @newarr,"'$var'";
99     }
100     }
101     $sql.=join(",",@newarr).");\n";
102     $line=<>; chomp $line;
103     }
104     next;
105     }
106    
107     if (/(CREATE \w*\s*INDEX "[^"]+" on "[^"]+")[^(]*\(([^\)]+)\)/i) {
108     my ($ind,$col) = ($1,$2);
109     $col=~s/" "[^"]+"/"/g; # nuke ops_name
110     $sql.="$ind ( $col );\n";
111     next;
112     }
113    
114     }
115    
116    
117     $sql.="$_\n";
118    
119     if (/CREATE TABLE "([^"]+)"/i) {
120     $table=$1 if (defined($1));
121     $create++;
122     }
123    
124     while ($sql=~/;/) {
125     ($dosql,$sql)=split(/;/,$sql,2);
126     $dosql.=";"; # nuked by split, put it back!
127     if ("$dosql" ne "") {
128     print "$dosql\n";
129     $create=0;
130     } else {
131     print STDERR "empty sql!\n";
132     }
133     }
134    
135     }
136    
137     print "$sql\n$triggers\n";
138    
139     print "\n";
140    

  ViewVC Help
Powered by ViewVC 1.1.26