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

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

# 2000-08-30 DbP -- Dobrica Pavlinusic <dpavlin@rot13.org>
#	based on mysql2pgsql changes are on:
#	http://cvs.linux.hr/cvsweb.cgi/sql/pgsql2interbase
#
# Warning: bool datatype is converted to char(1) which will break
# your application if you tend to check true values with
#	if ($foo)
# and not with
#	if ($foo = 't')
# In data from InterBase it will always return true which is wrong!
#

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

$|=1;

while(<>) {
	chomp;

#	warn "- $create:$_-\n";

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

	next if (/^\\connect/);

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

		if (/DEFAULT\s+nextval\s*\(\s*['"]+([^"']+)_seq["']+\s*\)/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 ; !!
";
			$generator{$trig}--;

			s/DEFAULT\s+nextval\s*\([^\)]+\)//i;
		}

		die "nextval not removed!" if (/nextval/);

		# nuke bool type definition on default
		s/DEFAULT bool/DEFAULT/i;

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

		# bool -> char(1)
		if (/bool/i) {
			s/\w*bool/ char(1)/gi;
			warn "Warning: bool emulated by char(1)\n\n";
		}

		# 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";
			$generator{$gen}++;	# to find unused generators
			next;
		}

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

		# rule is usually a defined view
		if (/^CREATE RULE/i) {
			warn "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.=lc($ind)." ( $col );\n";
			next;
		}

		if (/GRANT (.+) on "([^"]+)" to ([^;]+);/i) {
			($what,$table,$user) = ($1,$2,$3);
			$user=~s/"//g;
			if ($user =~ /group\s+(\S+)/i) {
				$sql.="grant $what on $table to group $1 ;\n";
				$groups{$1}++;
			} else {
				$sql.="grant $what on $table to $user ;\n";
				$users{$user}++;
			}
			next ;
		}

	}

	if ($create && /\);/) { $create-- }

	$sql.="$_\n";


	while ($sql=~/;/) {
		($dosql,$sql)=split(/;/,$sql,2);
		$dosql.=";";	# nuked by split, put it back!
		if ("$dosql" ne "") {
			$dosql=~s/"([^"]+)"/$1/g;	# nuke quotes
			print "$dosql\n";
		} else {
			warn "empty sql!\n";
		}
	}

}

$sql=~s/"([^"]+)"/$1/g;	# nuke quotes
print "$sql\n$triggers\n";

foreach $gen (keys %generator) {
	warn "created, but overused/unused generator: $gen (ref.count: $generator{$gen})\n" if ($generator{$gen} != 0);
}

warn "Users used: ",join(", ",keys(%users)),"\n";
warn "Groups used: ",join(", ",keys(%groups)),"\n";