#!/bin/bash -ve
db="${1:-sam.cg.uat.gdb}"
dir="${2:-`basename "$db.sql"`}"

mkdir "$dir"
cd "$dir"

sql() {
	isql -u sysdba -p sysdba "$@" "$db"
}

split_words() {
	perl -ne 'print "$1\n" while /(\S+)/g'
}

mkdir tables views triggers procs data dump

echo 'show tables;' | sql | split_words > tables.ls
echo 'show views;' | sql | split_words > views.ls
echo 'show procedures;' | sql > procs-deps.tbl
#echo 'show triggers;' | sql > table-triggers.tbl

< procs-deps.tbl tail -n +3 | grep -v '^ ' | sed 's/ .*//' > procs.ls
#< table-triggers.tbl tail -n +3 | sed 's/.* //' > triggers.ls

while read A; do echo 'show table '$A';' | sql > tables/$A; echo "$A"; done < tables.ls
while read A; do echo 'show view '$A';' | sql > views/$A; echo "$A"; done < views.ls
while read A; do echo 'show procedure '$A';' | sql > procs/"$A"; echo "$A"; done < procs.ls
#while read A; do echo 'show trigger '$A';' | sql > triggers/"$A"; echo "$A"; done < triggers.ls

echo 'show comments;' | sql > comments

sql -a > sql

mkdir .lists
mv *.ls *.tbl .lists

# data
if [ -n "$DUMP_DATA" ]; then
    cat tables.ls views.ls | while read A; do echo 'set list; select * from '$A';' | sql > data/$A; echo "$A"; done
fi

# dependencies
(
cd tables
export TABLES=`ls`
for A in $TABLES; do echo "$A" "$A"; done
grep -i 'foreign key' * | sed 's/:.*References//; s/ (.*//'
perl -ne '
    BEGIN {
        %table = map {uc($_)=>1} split /\s+/, $ENV{TABLES};
    }
    if (/from (.*)/) { my $x = $1; my @matches = $x =~ /(?:^|,)\s*(\w+)/g; print uc "$ARGV $_\n" for grep {$table{uc $_}} @matches; } if (/join\s*(\w+)/) { print uc "$ARGV $1\n" if $table{uc $1}; }
'  $(grep -lwi select `grep -lwi check $TABLES`)
) >table.dep
<table.dep tsort | tac > table.ord

# extract triggers from SQL
# TODO do this for other objects too, to get useful correct SQL
<sql perl -ne '
    if (/^CREATE TRIGGER (\w+)/) {
        open $out, ">", "triggers/$1";
        print $out "SET AUTODDL ON;\nSET TERM ^ ;\n";
    }
    print $out $_ if $out;
    if ($out && /\^$/) {
        print $out "SET TERM ; ^\n";
        close $out;
        undef $out;
    }
'

# dump
if [ -n "$DBIDUMP" ]; then
    cd dump
    dbidump -u sysdba -p sysdba -d 'dbi:InterBase:database=_;host=localhost;ib_dialect=3' dump "$db"
    cd ..
fi