#!/usr/bin/perl
# ISDK - Information System Development Kit
# Copyright (C) 1998-1999 Szilard Hajba, Peter Vazsonyi
#
# This program is free software; you can redistribute it and/or
# modify it under the terms of the GNU General Public License as
# published by the Free Software Foundation; either version 2 of
# the License, or (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
# Library General Public License for more details.
#
# You should have received a copy of the GNU Library General Public
# License along with this library; if not, write to the Free
# Software Foundation, Inc., 675 Mass Ave, Cambridge, MA 02139, USA.

require "getopts.pl";

# Variables:
#	$sqlo{table}{field}=type
#	$sqln{table}{field}=type
#	$type{type1}{type2}=command
#	$init{table}{field}=init_expr

sub parse_sql {
    my ($file, $var)=@_;
    my $ln;
    open FILE, $file;
    while ($ln=<FILE>) {
	if ($ln =~ /^\s*CREATE\s*TABLE\s*(\w+)\s*\(/) {
	    $tbl=$1;
	} elsif ($ln =~ /^\s*CREATE\s*/) {
	    $tbl="";
	} elsif ($ln =~ /^\s*(\w+)\s+([\@a-zA-Z_0-9()]+)(\s*,?\s*(--.*)?)?$/) {
#	    print "$tbl:$1-$2\n";
	    $$var{$tbl}{$1}=$2 if ($tbl);
	}
    }
    close FILE;
}

sub parse_typeconv {
    my ($file)=@_;
    open FILE, $file;
    while ($ln=<FILE>) {
	if ($ln =~ /^\s*([\@a-zA-Z_0-9()]+)\s*->\s*([\@a-zA-Z_0-9()]+)\s+(.*)$/) {
	    $type{$1}{$2}="$3";
	} elsif ($ln =~ /^\s*([\@a-zA-Z_0-9()]+)\s*:\s*([\@a-zA-Z_0-9()]+)\s*=\s*(.+)$/) {
	    $init{$1}{$2}="$3";
	} elsif ($ln =~ /^\s*([\@a-zA-Z_0-9()]+)\s*:\s*([\@a-zA-Z_0-9()]+)\s*$/) {
	    $init{$1}{$2}='NULL';
	} elsif ($ln =~ /^\s*ignore \s*table \s*(\w+)\s*$/) {
	    delete $sqlo{$1};
	    delete $sqln{$1};
	}
    }
    close FILE;
}

sub table_add {
    my ($table)=@_;
    my ($data);
    $data="CREATE TABLE $table (";
    foreach $field (keys %{$sqln{$table}}) {
	$data.="\n    $field ".$sqln{$table}{$field}.",";
    }
    $data =~ s/,$/\n);\n\n/;
    $zilu.=$data;
}

sub table_remove {
    my ($table)=@_;
    $zilu.="DROP TABLE $table;\n\n";
}

sub fields_add {
    my ($table, @fields)=@_;
    foreach $field (@fields) {
	$zilu.="ALTER TABLE $table ADD COLUMN $field ".$sqln{$table}{$field}.";\n";
    }
}

sub fields_remove {
    my ($table, @vfields)=@_;
    my ($data);
    foreach $field (@vfields) {
	$fields{$field}=1;
    }
    $data="--ALTER TABLE $table REMOVE COLUMNS ".join(', ', @vfields).";\n";
    $data.="BEGIN;\nLOCK $table;\nSELECT ";
    foreach $field (keys %{$sqlo{$table}}) {
	$data.="$field, " if !$fields{$field};
    }
    foreach $field (keys %{$sqln{$table}}) {
	if (!$sqlo{$table}{$field}) {
	    $data.="$field, " if !$fields{$field};
	}
    }
    $data =~ s/, $//;
    $data.=" INTO _temp_table FROM $table;\n";
    $zilu.=$data;
    $zilu.="DROP TABLE $table;\n";
    $zilu.="ALTER TABLE _temp_table RENAME TO $table;\n";
    $zilu.="COMMIT;\n\n";
}

sub field_conv_pre {
#    $zilu="BEGIN;\nLOCK $table;\n";
}

sub field_conv {
    my ($table, $field)=@_;
    $zilu.="ALTER TABLE $table RENAME COLUMN $field TO __dbd_int_$field;\n";
    push @of, "__dbd_int_$field";
}

sub fields_conv_post {
    my ($table, @fields)=@_;
    my ($convs)="";
    foreach $field (@fields) {
	my ($conv)=$type{$sqlo{$table}{$field}}{$sqln{$table}{$field}};
	if ($conv) {
#	    my ($d, $s)=($1, $2);
	    $conv =~ s/\@d/$field/;
	    $conv =~ s/\@s/__dbd_int_$field/;
	} else {
	    print STDERR "WARNING: Type conversion from '"
		    .$sqlo{$table}{$field}."' to '"
		    .$sqln{$table}{$field}."' not defined (field: $table:$field)\n";
	    $conv='@d=@s';
#	    my ($d, $s)=($1, $2);
	    $conv =~ s/\@d/$field/;
	    $conv =~ s/\@s/__dbd_int_$field/;
	}
	$convs.="$conv, ";
    }
    $convs =~ s/, $//;
    $zilu.="UPDATE $table SET $convs;\n" if $convs;
    $zilu=~s/^/BEGIN;\nLOCK $table;\n/ if $convs;
    $zilu.="COMMIT;\n" if $convs;
}

sub fields_init {
    my ($table, @fields)=@_;
    my ($inits);
    foreach $field (@fields) {
	my ($int)=$init{$table}{$field};
	if ($int) {
	    my ($d, $s)=($1, $2);
	    $int =~ s/^/$field=/;
	    $inits.="$int, ";
	} else {
	    print STDERR "WARNING: Initialization of field '"
		    ."$table:$field' not defined\n";
	}
    }
    $inits =~ s/, $//;
    $zilu.="UPDATE $table SET $inits;\n" if $inits;
}

exit 1 if (!Getopts("ho:n:t:"));
if ($opt_h) {
    print STDERR "usage: $0 -h\n";
    print STDERR "       $0 -t <def file> -o <old file> -n <new file>\n";
    print STDERR "           <def file>: Type and init definition file\n";
    print STDERR "           <old file>: The name of the older SQL file\n";
    print STDERR "           <new file>: The name of the newer SQL file\n";
    print STDERR "Description:\n";
    print STDERR "    The program loads the two SQL files and creates an SQL\n";
    print STDERR "    format diff file what can be used to convert an existing\n";
    print STDERR "    database of the old format into the new format.\n";
    print STDERR "    The SQL files can contain ONLY table definitions, any\n";
    print STDERR "    other rows are ignored.\n\n";
    print STDERR "format of the definition file:\n";
    print STDERR "    <type1> -> <type2> <expression>\n";
    print STDERR "    <table>:<field> = <expression>\n\n";
    print STDERR "Example:\n";
    print STDERR "    int4 -> text \@d=\@s::text\n";
    print STDERR "    recipe:rcode = '00000'\n\n";
    exit 1;
}
exit 1 if !$opt_o || !$opt_n || !$opt_t;
parse_sql($opt_o, sqlo);
parse_sql($opt_n, sqln);
parse_typeconv($opt_t);

foreach $table (keys %sqlo) {
    @of=();
    @cf=();
    @if=();
    @nf=();
    if ($sqln{$table}) {
	# Renaming conv fields
	&field_conv_pre;
	foreach $field (keys %{$sqln{$table}}) {
	    if ($sqlo{$table}{$field} && $sqlo{$table}{$field} ne $sqln{$table}{$field}) {
		&field_conv($table, $field);
	    }
	}
	# Adding new fields
	foreach $field (keys %{$sqln{$table}}) {
	    if ($sqlo{$table}{$field} ne $sqln{$table}{$field}) {
		push(@nf, $field);
	    }
	}
	&fields_add($table, @nf);
	# Converting fields
	foreach $field (keys %{$sqln{$table}}) {
	    if ($sqlo{$table}{$field} && $sqlo{$table}{$field} ne $sqln{$table}{$field}) {
		push(@cf, $field);
	    }
	}
	&fields_conv_post($table, @cf);
	# Initializing new fields
	foreach $field (keys %{$sqln{$table}}) {
	    if (!$sqlo{$table}{$field}) {
		push(@if, $field);
	    }
	}
	&fields_init($table, @if);
	# Removing old fields
	foreach $field (keys %{$sqlo{$table}}) {
	    if (!$sqln{$table}{$field}) {
		push(@of, $field);
	    }
	}
#	print "--+".join(':',%{$sqlo{$table}})."\n---".join(':',%{$sqln{$table}})."\n--=".join(':',@of)."\n";
	&fields_remove($table, @of) if @of;
    } else {
	&table_remove($table);
    }
    print "-- TABLE $table:\n$zilu\n" if $zilu;
    $zilu="";
}
foreach $table (keys %sqln) {
    if (!$sqlo{$table}) {
#	$zilu.="-- TABLE $table:\n";
	&table_add($table, (keys %{$sqln{$table}}));
	print "-- TABLE $table:\n$zilu\n" if $zilu;
	$zilu="";
    }
}
