#!/bin/bash

rdschemas="SELECT schemata.schema_name FROM information_schema.schemata"
rdtables="SELECT tables.table_name FROM information_schema.tables WHERE tables.table_schema = 'par1' AND table_type = 'BASE TABLE'"
rdcols="SELECT  DISTINCT\
           t0.column_name AS column,\
           CASE WHEN t0.data_type = 'character varying' THEN 'varchar' WHEN t0.data_type = 'double precision' THEN 'double' ELSE t0.data_type END AS origtyp,\
           t0.character_maximum_length AS maxlength,\
           NOT t0.column_default ISNULL AS default,\
           t0.is_nullable AS nullable,\
           CASE WHEN position(E'::' in t0.column_default ) = 0 THEN t0.column_default ELSE substring(t0.column_default from 0 for position(E'::' in t0.column_default)) END AS defvalue\
           FROM information_schema.columns t0 WHERE t0.table_schema = 'par1' AND t0.table_name = 'par2' AND t0.column_name like 'par3' ORDER BY t0.column_name";

rdconcheck="SELECT DISTINCT\
            t0."conname" AS name,\
            t0."consrc" AS check,\
            t3."nspname" AS cschema\
            FROM\
            ((((pg_catalog.pg_constraint t0 LEFT JOIN pg_catalog.pg_class t1 ON ( t0.conrelid = t1.oid ) ) LEFT JOIN pg_catalog.pg_namespace t2 ON ( t1.relnamespace = t2.oid ) )) LEFT JOIN pg_catalog.pg_namespace t3 ON ( t0.connamespace = t3.oid ))\
            WHERE\
            ( t0.contype = E'c' AND t0.conrelid != 0 and t2.nspname = 'par1' AND t1.relname = 'par2' )"; 

need_default=0

get_con()
{
    par=$1
    tcon=${par/|*};par=${par#*|}
    tsource=${par/|*};par=${par#*|}
}

get_checkcon()
{
    par=$1
    ccon=${par/|*};par=${par#*|}
    csource=${par/|*};par=${par#*|}
}

get_column()
{
    par=$1
    tname=${par/|*};par=${par#*|}
    ttyp=${par/|*};par=${par#*|}
    tlength=${par/|*};par=${par#*|}
    tdef=${par/|*};par=${par#*|}
    tnull=${par/|*};par=${par#*|}
    tdefval=$par    

   if [ $ttyp == "double" ]; then
      ttyp="double precision"
   fi

}

get_checkcolumn()
{
    par=$1
    cname=${par/|*};par=${par#*|}
    ctyp=${par/|*};par=${par#*|}
    clength=${par/|*};par=${par#*|}
    cdef=${par/|*};par=${par#*|}
    cnull=${par/|*};par=${par#*|}
    cdefval=$par    

   if [ $ctyp == "double" ]; then
      ctyp="double precision"
   fi

}

column_write()
{
   get_column $1   
   echo -n "$tname $ttyp"
   
   if [ "$tlength" != "" ]; then
       echo -n "("$tlength") "
   else
       echo -n " "
   fi
   
   if [ "$tdef" = "t" ] || [ "$need_default" = "1" ]; then
       if [ "$need_default" = "1" ] && [ "$tdefval" = '' ] && [ "$ttyp" != "varchar" ]; then
           tdefval="CAST ( 0 AS $ttyp)"
       fi
       
       if [ "$ttyp" = "varchar" ] && [ "$tdefval" = "" ]; then
           tdefval="''";
       fi    
       echo -n DEFAULT "$tdefval "
   fi
   
   if [ "$tnull" = "NO" ]; then
       echo -n NOT NULL
   fi
}

column_add()
{
    need_default=1
    echo -n "ALTER TABLE $1.$2 ADD COLUMN "
    column_write $3
    echo ";"
    need_default=0
    if [ "$tdef" != "t" ]; then
        echo "ALTER TABLE $1.$2 ALTER COLUMN $tname DROP DEFAULT;"
        if [ "$tnull" != "NO" ]; then
          if [ "$ttyp" = "varchar" ] && [ "$tdefval" = "''" ]; then
            echo "UPDATE $1.$2 SET $tname = null WHERE $tname = '';"
          else
            echo "UPDATE $1.$2 SET $tname = null WHERE $tname = 0;"
          fi
        fi
    fi
}

column_mod()
{
    if [ "$tdef" != "$cdef" ] && [ "$tdef" != "t" ]; then
        echo "ALTER TABLE $1.$2 ALTER COLUMN $cname DROP DEFAULT;"
    
    elif [ "$tdef" = "t" ] || [ "$tdefval" != "$cdefval" ]; then
        echo -n "ALTER TABLE $1.$2 ALTER COLUMN $cname SET DEFAULT "
        if [ "$ttyp" = "varchar" ]; then
            echo "$tdefval;"
        else
            echo "$tdefval;"
        fi
    fi

    if [ "$ttyp" != "$ctyp" ] || [ "$tlength" != "$clength" ]; then
        echo -n "ALTER TABLE $1.$2 ALTER COLUMN $cname TYPE $ttyp"
        
        if [ "$tlength" != "" ]; then
            echo -n "("$tlength")";
        fi
        echo ";"
    fi

    if [ "$tnull" != "$cnull" ]; then
        echo -n "ALTER TABLE $1.$2 ALTER COLUMN $cname "
        if [ "$tnull" = "NO" ]; then
           echo -n "SET "
        else
           echo -n "DROP "
        fi   
        echo "NOT NULL;"
    fi
}

column_del()
{
    echo "column $1.$2.$3 muss gel�scht werden" >> drop.sql
    echo "ALTER TABLE $1.$2 DROP COLUMN $3" >> drop.sql

}

table_create()
{
   echo create table $1.$2 "("
   cols=`get_data "$rdcols" $1 $2 "%"`
   komma=
   for c in $3
   do
       echo -n "    $komma"
       column_write $c
       echo ""
       komma=','
   done
   echo ");"
}

schema_create()
{
    echo "CREATE SCHEMA $1;"
}

echo "--Tabellen �berpr�fen"
checkschemas=`get_checkdata "$rdschemas"`

for s in $schemas
do
    echo "-- Schema $s"
    found=0
    for cs in $checkschemas 
    do
        if [ "$s" = "$cs" ]; then
          found=1
          continue
        fi
    done
    
    if [ "$found" = "0" ]; then
        schema_create $s
    fi
    
    tables=`get_data "$rdtables" $s`
    for t in $tables
    do
        colok=0
        echo -ne "                                                     \r" >&2
        echo -ne "check $s.$t\r" >&2
        checkcols="$(get_checkdata "$rdcols" $s $t "%")"
        cols="$(get_data "$rdcols" $s $t "%")"

        if [ "$checkcols" = "" ]; then
           table_create $s $t "$cols"
           colok=1
        fi
        
        if [ "$cols" = "$checkcols" ]; then
            echo "-- $s.$t is ok";
            colok=1
        fi
        
        if [ "$colok" == 0 ]; then
            for c in $cols
            do
                get_column $c
                found=0
                for cc in $checkcols
                do
                    get_checkcolumn $cc
                    if [ "$cname" = "$tname" ]; then
                        found=1
                        if [ "$cc" != "$c" ]; then
                           column_mod $s $t $c $cc
                       fi
                    fi
                done
                if [ "$found" = "0" ]; then
                     column_add $s $t $c
                fi
            done
            
            for c in $checkcols
            do
                get_checkcolumn $c
                found=0
                for cc in $cols
                do
                    get_column $cc        
                    if [ "$cname" = "$tname" ]; then
                        found=1
                    continue
                    fi
                done
                if [ "$found" = "0" ]; then
                     column_del $s $t $cname
                fi
            done
        fi

        constr=`get_data "$rdconcheck" $s $t`
        checkconstr=`get_checkdata "$rdconcheck" $s $t`
        
        if [ ! "$constr" = "$checkconstr" ]; then
            for con in $checkconstr
            do
                get_checkcon $con
                echo ALTER TABLE $s.$t DROP CONSTRAINT "$ccon;";
            done

            for con in $constr
            do
                get_con $con
                echo ALTER TABLE $s.$t ADD CONSTRAINT $tcon CHECK "(" $tsource ");";
            done
        fi
        
    done
done
