#!/bin/bash

USER=root
DBNAME=$1

MYSQL="mysql -u $USER $DBNAME"

if [ $# != 1 ]; then
    echo "USAGE: $0 db"
    echo "warning this script will drop/recreate tables test1,test_log,results,defs"
fi

setup()
{
    $MYSQL <<.EOF 
        drop table if exists results;
        create table results (test_name varchar(40), results varchar(20));

        drop table if exists test_log;
        create table test_log (id integer auto_increment primary key, op char(1), test_name varchar(40), v_new varchar(30));     
.EOF
    if [ $? -ne 0 ]; then
        echo "%%%-ERRROR: problem setting up tests..."
        exit 1
    fi
}

cleanup()
{
    $MYSQL <<.EOF 
        drop table if exists results;
        drop table if exists test_log;
        drop table if exists test1;
        drop table if exists defs;
        drop trigger if exists test1_bi;
        drop trigger if exists test1_ai;
.EOF
    if [ $? -ne 0 ]; then
        echo "%%%-ERRROR: problem setting up tests..."
        exit 1
    fi
}

test_reset()
{
    local col_type="$1"
    local nullable="$2"
    local def_value="$3"

    $MYSQL <<.EOF
        delete from test_log;
        
        drop table if exists test1;
        create table test1 (test_name varchar(40), v $col_type $nullable) engine = myisam;

        drop table if exists defs;
        create table defs (v $col_type) engine = myisam;  
        insert into defs (v) value ($def_value);
        
        drop trigger if exists test1_bi;
        drop trigger if exists test1_ai;
        delimiter //
        
        create trigger test1_bi before insert
        on test1 for each row
        begin
            INSERT into test.test_log (op, test_name, v_new) values ('B', NEW.test_name, cast(NEW.v as char));        
            SET NEW.v = (select v from defs);
        end;
        //
        
        create trigger test1_ai after insert
        on test1 for each row
        begin
            INSERT into test.test_log (op, test_name, v_new) values ('A', NEW.test_name, cast(NEW.v as char));        
        end;
        //
.EOF
    if [ $? -ne 0 ]; then
        echo "%%%-ERROR(test_reset): error creating new tables for: col_type: ($col_type) nullable: ($nullable) def_value: ($def_value)"
        exit 1
    fi
}


do_test()
{
    local test_name="$1"
    local col_type="$2"
    local nullable="$3"
    local def_value="$4"
    local extra_cols="$5"
    local extra_vals="$6"
    
    echo '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
    echo
    #echo "%%%-INFO: test: $test_name, col_type: $col_type, nullable: $nullable, def_value: $dev_value, extra_cols: '$extra_cols', extra_vals: '$extra_vals'"
    #echo
    
    test_reset "$col_type" "$nullable" "$def_value"

    $MYSQL -vv -e "insert into test1 (test_name $extra_cols) values ('$test_name' $extra_vals)"
    if [ $? -ne 0 ]; then
        $MYSQL -e "insert into results values ('$test_name','FAIL-INS')"
        return
    fi

    #$MYSQL -e 'select v, cast(v as char) from defs'
    #if [ $? -ne 0 ]; then
    #    echo "%%%-ERROR: checking defs"
    #    exit 1
    #fi
    
    $MYSQL -e 'select * from test_log'
    if [ $? -ne 0 ]; then
        echo "%%%-ERROR: checking test_log"
        exit 1
    fi
    
    #$MYSQL -e 'select * from test1'
    #if [ $? -ne 0 ]; then
    #    echo "%%%-ERROR: checking test1"
    #    exit 1
    #fi
    
    $MYSQL <<.EOF
        insert into results (test_name, results)
        select test_name, CASE WHEN v_new = $def_value THEN 'SUCCESS' ELSE 'FAIL-VAL-NOT-FOUND' END
        from test_log order by id desc limit 1
        ;
.EOF
    if [ $? -ne 0 ]; then
        echo "%%%-ERROR: updating test status"
        exit 1
    fi

}

setup

do_test 'nullable int v not provided'       integer null        1   ''      ''
do_test 'nullable int v=2'                  integer null        1   ',v'    ',2.2'
do_test 'nullable int v=null'               integer null        1   ',v'    ',NULL'
do_test 'not nullable int v not provided'   integer 'not null'  1   ''      ''
do_test 'not nullable int v=2'              integer 'not null'  1   ',v'    ',2.2'
do_test 'not nullable int v=null'           integer 'not null'  1   ',v'    ',NULL'

do_test 'nullable float v not provided'     float null          1.1 ''      ''
do_test 'nullable float v=2.2'              float null          1.1 ',v'    ',2.2'
do_test 'nullable float v=null'             float null          1.1 ',v'    ',NULL'
do_test 'not nullable float v not provided' float 'not null'    1.1 ''      ''
do_test 'not nullable float v=2.2'          float 'not null'    1.1 ',v'    ',2.2'
do_test 'not nullable float v=null'         float 'not null'    1.1 ',v'    ',NULL'

do_test 'nullable datetime v not provided'      datetime null       "'2010-01-01 01:01:01'" ''      ''
do_test 'nullable datetime v=current_timestamp' datetime null       "'2010-01-01 01:01:01'" ',v'    ',current_timestamp'
do_test 'nullable datetime v=null'              datetime null       "'2010-01-01 01:01:01'" ',v'    ',null'
do_test 'not null datetime v not provided'      datetime 'not null' "'2010-01-01 01:01:01'" ''      ''
do_test 'not null datetime v=current_timestamp' datetime 'not null' "'2010-01-01 01:01:01'" ',v'    ',current_timestamp'
do_test 'not null datetime v=null'              datetime 'not null' "'2010-01-01 01:01:01'" ',v'    ',null'

do_test 'nullable timestamp v not provided'      timestamp null       "'2010-01-01 01:01:01'" ''      ''
do_test 'nullable timestamp v=current_timestamp' timestamp null       "'2010-01-01 01:01:01'" ',v'    ',current_timestamp'
do_test 'nullable timestamp v=null'              timestamp null       "'2010-01-01 01:01:01'" ',v'    ',null'
do_test 'not null timestamp v not provided'      timestamp 'not null' "'2010-01-01 01:01:01'" ''      ''
do_test 'not null timestamp v=current_timestamp' timestamp 'not null' "'2010-01-01 01:01:01'" ',v'    ',current_timestamp'
do_test 'not null timestamp v=null'              timestamp 'not null' "'2010-01-01 01:01:01'" ',v'    ',null'

do_test 'nullable varchar v not provided'   'varchar(10)' null       "'abc'" ''      ""
do_test 'nullable varchar v=efg'            'varchar(10)' null       "'abc'" ',v'    ",'efg'"
do_test 'nullable varchar v=null'           'varchar(10)' null       "'abc'" ',v'    ",null"
do_test 'not null varchar v not provided'   'varchar(10)' 'not null' "'abc'" ''      ""
do_test 'not null varchar v=efg'            'varchar(10)' 'not null' "'abc'" ',v'    ",'efg'"
do_test 'not null varchar v=null'           'varchar(10)' 'not null' "'abc'" ',v'    "null"

$MYSQL -e "select * from results"