#! /bin/bash

case $# in
0)
    dbname=bowtest
    ;;
1)
    case $1 in
    -h|--help)
        echo -e "Usage: $0 [dbname] [-h|--help]\n" \
                "Default database: bowtest\n" \
                "Here's the help you asked for."
        exit 1
        ;;
    *)
        dbname=$1
        ;;
    esac
    ;;
*)
    echo -e "Usage: $0 [dbname] [-h|--help]\n" \
            "You have specified too many arguments.\n" \
            "Please retry."
    exit 1
    ;;
esac

echo "Execute $0 on database $dbname";

sqlLog=weird.log
sql $dbname << SQL_END > $sqlLog
\r
\nocontinue
set trace point qe61;
\p\g
drop table if exists base;
create table base(
    refresh_id integer4 not null not default,
    basket_id  integer4 not null not default,
    action     integer1 not null with default 1
) with nojournaling;

modify base to btree unique on refresh_id with unique_scope=statement;

insert into base(refresh_id, basket_id)
values (1, 2), (2, 4), (3, 6), (4, 8), (5, 10), (6, 12);

drop table if exists base_snapshot;
create table base_snapshot(
    snapshot_id integer4 not null not default,
    basket_id   integer4 not null not default,
    version     integer4 not null not default,
    accept      integer4 not null with default 9,
    stuff       /*long varchar*/ varchar(50)  not null not default
) with nojournaling;

modify base_snapshot to btree unique on basket_id, snapshot_id /*version*/ with unique_scope=statement;

insert into base_snapshot values
    (100, 2, 1, 9, 'One fish.'),
    (200, 4, 2, 9, 'Two fish.'),
    (300, 6, 3, 9, 'Red fish, blue fish.'),
	(400, 8, 4, 9, 'This one has a little star.'),
	(500, 10, 5, 9, 'This one has a little car.'),
	(600, 12, 6, 9, 'My, what a lot of fish there are!');

drop procedure if exists trial_loop;
create procedure trial_loop
as declare
    refresh_id integer4 not null not default;
    snapshot_id integer4 not null not default;

    iloop integer4 not null not default;
    ndone integer4 not null not default;
    nfail integer4 not null not default;
    enum  integer4 not null not default;
    msg   varchar(256) not null not default;
begin
    iloop = 0;
    ndone = 0;
    nfail = 0;
    FOR select r.refresh_id, s.snapshot_id into :refresh_id, :snapshot_id
        from base r, base_snapshot s
        WHERE r.basket_id = s.basket_id AND r.action = 1 AND s.accept = 9
        order by refresh_id
    DO
        iloop = iloop + 1;
        msg = '[' + varchar(:iloop) + '] for refresh_id = ' + varchar(:refresh_id) + ', snapshot_id = ' + varchar(:snapshot_id);
        message :msg;

        if (mod(:refresh_id, 2) = 0) then
            nfail = nfail + 1;
            msg = 'Failed with refresh_id = ' + varchar(:refresh_id) + ' so I''ll rollback';
            rollback;
        else
            ndone = ndone + 1;
			msg = 'Cool to commit';
            commit;
        endif;
        message :msg;
    endfor;

    msg = 'Summary: Encountered ' + varchar(:iloop) + '. Updated ' + varchar(:ndone) +', Failed ' + varchar(:nfail);
    message :msg;
end;

commit;
\p\g
rollback
\p\g
SQL_END

if [ `egrep -c -e'\<E_[A-Z]{2}[0-9A-F]{4}' $sqlLog` -gt 0 ]; then
    echo -e "$0 Unexpected SQL errors in setup.\n" \
            "Dbname: $dbname\n" \
            "Log: $sqlLog\n" \
            "Program aborts."
    exit 1
fi
sql $dbname << TRY_THIS_PROC | tee -a $sqlLog
execute procedure trial_loop;
\p\g
modify base to heap;
\p\g
execute procedure trial_loop;
\p\g
rollback
\p\g
TRY_THIS_PROC