viewvc.cgi/sql/pgsql2interbase

#!/usr/local/bin/perl -w

# PostgreSQL to InterBase dump file converter
#
# usage:
# cat pg_db_name | ./mysql2pgsql > interbase.sql
#
# Convert PostgresSQL database dump file to something readable by isql !

# 2000-08-30 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
#       based on mysql2pgsql

$create=0;      # inside create table?
$table="";
$triggers="";   # create triggers

while(<>) {
        chomp;

        next if (/^\\connect/);

        if ($create) {                  # are we inside create table?

                if (/DEFAULT nextval \( '"([^"]+)_seq"' \)/i) {
                        $trig=$col=$1;
                        $col=~s/(\w+)_([^_]+)/$1.$2/;
                        $triggers.="
set term !! ;
create trigger ${trig}_trig for $table
before insert position 0
as begin
$col = gen_id(${trig}_gen,1) ;
end !!
set term ; !!
";
                        s/ DEFAULT nextval \( '"[^"]+"' \)//i;
                }
                s/DEFAULT bool/DEFAULT/i;

                # int(48...) -> int
                s/\w*int\d+/ int/gi;

                # bool -> char(1)
                s/\w*bool/ char(1)/gi;

                # datetime -> timestamp
                s/datetime/timestamp/gi;

        } else {        # not inside create table

                if (/CREATE SEQUENCE "(\w+)_seq" start (\d+)/i) {
                        my ($gen,$start) = ($1,$2);
                        $sql.="create generator ${gen}_gen ;\n";
                        $sql.="set generator ${gen}_gen to $start ;\n";
                        next;
                }

                # left-over from create sequnce
                next if (/^SELECT nextval/i);
                
                # you will have to re-write functions manually!
                if (/^CREATE FUNCTION/i) {
                        print STDERR "functions not supported: $_\n\n";
                        next;
                }

                # rule is usually a defined view
                if (/^CREATE RULE/i) {
                        print STDERR "rules (views...) not supported: $_\n\n";
                        next;
                }
                if (/COPY "([^"]+)" FROM stdin/i) {
                        my $table=$1;
                        my $line=<>; chomp $line;
                        while($line ne "\\.") {
                                $sql.="insert into \"$table\" values (";
                                undef @newarr;
                                foreach $var (split(/\t/,$line)) {
                                        if ($var eq "\\N") {
                                                push @newarr,"null";
                                        } elsif ($var=~/^\d+$/ || $var=~/^\d+\.\d+$/) {
                                                push @newarr,"$var";
                                        } elsif ($var=~/\w{3} (\w{3}) (\d+) (\d\d:\d\d:\d\d) (\d{4})/) {
                                                # timestamp
                                                push @newarr,"'$2-$1-$4 $3'";
                                        } else {
                                                push @newarr,"'$var'";
                                        }
                                }
                                $sql.=join(",",@newarr).");\n";
                                $line=<>; chomp $line;
                        }
                        next;
                }

                if (/(CREATE \w*\s*INDEX "[^"]+" on "[^"]+")[^(]*\(([^\)]+)\)/i) {
                        my ($ind,$col) = ($1,$2);
                        $col=~s/" "[^"]+"/"/g;  # nuke ops_name
                        $sql.="$ind ( $col );\n";
                        next;
                }

        }


        $sql.="$_\n";

        if (/CREATE TABLE "([^"]+)"/i) {
                $table=$1 if (defined($1));
                $create++;
        }

        while ($sql=~/;/) {
                ($dosql,$sql)=split(/;/,$sql,2);
                $dosql.=";";    # nuked by split, put it back!
                if ("$dosql" ne "") {
                        print "$dosql\n";
                        $create=0;
                } else {
                        print STDERR "empty sql!\n";
                }
        }

}

print "$sql\n$triggers\n";

print "\n";

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