#! /bin/bash

# GLOBALS

UPDATE_THIS=(
    "if (:action = 'U') then" 
    "    update test_before" 
    "    set id = id + 1" 
    "    where a = :old_a;"
    "endif;"
)

UPDATE_JUNK=(
    "if (:action = 'U') then" 
    "    update test_junk" 
    "    set id = id + 1" 
    "    where a = :old_a;"
    "endif;"
)

KEEP=0

# SUBROUTINES
Usage ()
{
echo "Usage is:
    $0 [-ddbname] [--useLong] [--noUpdate] [--updateJunk] [-h|--help]
Synopsis:
    Run the before trigger code which casues the problem.

Where:
[-ddbname] Optional. Specify a database to connect to.
    If not specified then 'iidbdb' is assumed.

[--useLong] Optional.
    The datatype of a specific field in the table is set to long varchar.
    If not specified the datatype of that field is set to varchar(2000).

[--noUpdate] Optional.
    If specified the update clause which triggers the problem is removed
    from the procedure.

    Default is to include the update clause.

[--updateJunk] Optional.
    If specified we update a junk table and not the original table.

    By default the original table is updated.

[-h|--help] Optional. Display this help information.
"
    echo -e "Additional:\n" \
            "   $*"
    exit 1;
}; #Usage()

# MAIN PROGRAM
for arg in $*; do
    case $arg in
    -d*)
        DBNAME=`echo $arg | cut -c3-`
        ;;
    --useLong)
        DATATYPE="long varchar"
        ;;
    --noUpdate)
        UPDATE_THIS=();
        ;;
    --updateJunk)
        UPDATE_THIS=()
		UPDATE_THIS=${UPDATE_JUNK[*]}
        ;;
    --keep)
        KEEP=1
        ;;
    -h|--help)
        Usage "Here's the help you asked for."
        ;;
    *)
        Usage "Unexpected argument $arg encountered.\n" \
              "   Please retry\n";
        exit 1
        ;;
    esac
done

# Set some defaults
[ "$DBNAME" == '' ] && DBNAME='iidbdb';
[ "$DATATYPE" == '' ] && DATATYPE='varchar(2000)'

SQL_LOG=log.test

echo "Performing data setup now..."
sql $DBNAME << SQL_END > $SQL_LOG
\r
\nocontinue
drop table if exists test_before;
drop table if exists test_junk;
drop table if exists archive_test_before;

create table test_before(
    a integer4 not null not default,
    b $DATATYPE not null,
    c integer2 not null not default,
    id integer4 not null with default
) with nojournaling, page_size = 8192;

create table archive_test_before(
    a integer4 not null not default,
    b $DATATYPE not null,
    c integer2 not null not default,
    id integer4 not null with default,
    action char(1) not null not default,
    action_date ingresdate not null with default 'now',
    action_username varchar(32) not null not default
) with nojournaling, page_size = 8192;

create table test_junk(
    a integer4 not null not default,
    id integer4 not null with default
) with nojournaling, page_size=8192;

modify test_before to btree unique on a with unique_scope=statement;
modify archive_test_before to btree on a, action_date;

insert into test_before(a, b, c) values (1, 'not a very long string', 10);
insert into test_junk(a, id) values (1, 0);

drop procedure if exists rfp_test_before_archive;

create procedure rfp_test_before_archive(
    action          char(1) not null not default,
    old_a           integer4 not null not default,
    old_b           $DATATYPE not null not default,
    old_c           integer2 not null not default,
    old_id          integer4 not null not default
)
as
begin
    insert into archive_test_before (a, b, c, id, action, action_date, action_username)
    values (
        :old_a, :old_b, :old_c, :old_id,
        :action, date('now'), dbmsinfo('username')
    );
    ${UPDATE_THIS[*]}
end;
\p\g
drop procedure if exists wfp_test_before_archive;

create procedure wfp_test_before_archive(
    action          char(1) not null not default,
    old_a           integer4 not null not default,
    old_b           $DATATYPE not null not default,
    old_c           integer2 not null not default,
    old_id          integer4 not null not default
)
as
begin
    execute procedure rfp_test_before_archive(
        action = :action,
        old_a  = :old_a,
        old_b  = :old_b,
        old_c  = :old_c,
        old_id = :old_id
    );
end;
\p\g
create rule test_before_archive_del
before delete from test_before
for each row execute procedure wfp_test_before_archive(
    action    = 'D',
    old_a     = old.a,
    old_b     = old.b,
    old_c     = old.c,
    old_id    = old.id
);
\p\g
create rule test_before_archive_upd
before update(a, b, c) of test_before
for each row
execute procedure wfp_test_before_archive(
    action    = 'U',
    old_a     = old.a,
    old_b     = old.b,
    old_c     = old.c,
    old_id    = old.id
);
\p\g
commit;
\p\g
SQL_END
if [ `egrep -e'\<E_[A-Z]{2}[0-9A-F]{4}' $SQL_LOG | wc -l` -gt 0 ] ; then
    echo "Unexpected errors setting up test case."
    echo "LOG: $SQL_LOG"
    exit 1
fi;

echo "Setup OK. Now test an update/delete";
sql -s $DBNAME << SQL_END
update test_before set c = c+1 where a=1;
commit;
\p\g
SQL_END

if [ $KEEP -eq 0 ]; then
    echo "Cleaning tables and procedures from database."
    sql $DBNAME << SQL_END > $SQL_LOG
    drop table if exists test_before;
    drop table if exists archive_test_before;
    drop procedure if exists rfp_test_before_archive;
    drop procedure if exists wfp_test_before_archive;

    commit;
    \p\g
SQL_END
    if [ `egrep -e'\<E_[A-Z]{2}[0-9A-F]{4}' $SQL_LOG | wc -l` -gt 0 ] ; then
        echo "Unexpected errors cleaning up test case."
        echo "LOG: $SQL_LOG"
        exit 1
    fi;
else
    echo -e "Keeping tables, rules and procedures in database $DBNAME\n" \
            "Tables: test_before, archive_test_before\n" \
            "Procedures: rfp_test_before_archive, wfp_test_before_archive\n" \
            "Rules: test_before_archive_del, test_before_archive_upd"
fi;