test/cache/Bidirectional/test022.ksh

   1 #!/bin/ksh
   2 # Test Case
   3
   4 # Bi-directional cache  test
   5 # Create table t1 with field f1 int unique, f2 char,f3 smallint , f4 bigint ,primary key(f2,f3) . Make bidirectional flag and cache flag true. run trigger ,create log table in target db.Cache the table by -f f1,f2,f3 -p f1 . insert record with 4 field .and select from t1 it should three field values.
   6 # Author:= Bijaya
   7
   8 input=${PWD}/cache/Bidirectional/mysqlcreatelogtable.sql
   9
  10 REL_PATH=.
  11 if [ -s "$input" ]
  12 then
  13     REL_PATH=${PWD}/cache/Bidirectional
  14 fi
  15 rm /tmp/csql.conf
  16 cp $REL_PATH/csql.conf /tmp
  17 export CSQL_CONFIG_FILE=/tmp/csql.conf
  18 echo DSN=$DSN >>$CSQL_CONFIG_FILE
  19 DS="$DSN $DBUSER $PASSWORD"
  20 rm -f /tmp/csql/csqlds.conf
  21 touch /tmp/csql/csqlds.conf
  22 $CSQL_INSTALL_ROOT/bin/csqlds -U $DBUSER -P $PASSWORD -D $DSN -N $TDB -a
  23 if [ $? -ne 0 ]
  24 then
  25    exit 1;
  26 fi
  27 if [ "$DSN" = "myodbc3" ]
  28 then
  29    LOG_FILE=$REL_PATH/mysqlcreatelogtable.sql
  30    TRI_FILE1=$REL_PATH/trigger.sql
  31    TRI_FILE2=$REL_PATH/trigger1.sql
  32 elif [ "$DSN" = "psql" ]
  33 then
  34     LOG_FILE=$REL_PATH/psqlcreatelogtable.sql
  35     TRI_FILE1=$REL_PATH/trigger.psql
  36     TRI_FILE2=$REL_PATH/trigger1.psql
  37 elif [ "$DSN" = "oracle" ]
  38 then
  39     LOG_FILE=$REL_PATH/oraclecreatelogtable.sql
  40     TRI_FILE1=$REL_PATH/trigger.oracle
  41     TRI_FILE2=$REL_PATH/trigger1.oracle
  42 elif [ "$DSN" = "sybase" ]
  43 then
  44     LOG_FILE=$REL_PATH/sybasecreatelogtable.sql
  45     TRI_FILE1=$REL_PATH/trigger.sybase
  46     TRI_FILE2=$REL_PATH/trigger1.sybase
  47 elif [ "$DSN" = "db2" ]
  48 then
  49     LOG_FILE=$REL_PATH/db2createlogtable.sql
  50     TRI_FILE1=$REL_PATH/trigger.db2
  51     TRI_FILE2=$REL_PATH/trigger1.db2
  52 else
  53     LOG_FILE=
  54     TRI_FILE1=
  55     TRI_FILE2=
  56 fi
  57
  58 #echo $DS
  59 isql $DS < $LOG_FILE >/dev/null 2>&1
  60 if [ $? -ne 0 ]
  61 then
  62     echo "DSN is not set for target db"
  63     exit 1
  64 fi
  65
  66 echo Log table created in target DB
  67
  68 rm -f /tmp/csql/csqltable.conf
  69 touch /tmp/csql/csqltable.conf
  70 echo "create table t1(f1 int ,f2 char(12),f3 int, f4 int,primary key(f2));">$REL_PATH/t1.sql
  71
  72 if [ $DSN = "db2" ]
  73 then
  74 echo "create table t1(f1 int not null,f2 char(12) not null,f3 int, f4 int,primary key(f2));">$REL_PATH/t1.sql
  75 fi
  76
  77 echo "create unique index ind on t1(f1) ;">>$REL_PATH/t1.sql
  78 for a in 11 12 13 14
  79 do
  80 echo "insert into t1 values($a,'papu$a',12,$a+123124);"
  81 done>>$REL_PATH/t1.sql
  82
  83 isql $DS < $REL_PATH/t1.sql  >/dev/null 2>&1
  84 if [ $? -ne 0 ]
  85 then
  86     echo "unable to creste in target db"
  87     rm -f $REL_PATH/t1.sql
  88     exit 2
  89 fi
  90 isql $DS <$TRI_FILE1 >/dev/null
  91
  92 $CSQL_INSTALL_ROOT/bin/csqlserver  >/dev/null 2>&1 &
  93 pid=$!
  94 sleep 5
  95 echo "server  started"
  96 echo "drop table t1;">${REL_PATH}/dt1.sql
  97 if [ $DSN = "oracle" -o $DSN = "sybase" -o $DSN = "db2" ]
  98 then
  99 {
 100 $CSQL_INSTALL_ROOT/bin/cachetable -t t1 -c "f1>10" -f f1,f3,f2 -p f1 -F
 101 if [ $? -ne 0 ]
 102 then
 103     echo "Unable to cache"
 104     isql $DS <${REL_PATH}/dt1.sql  >/dev/null 2>&1
 105     rm -f $REL_PATH/t1.sql ${REL_PATH}/dt1.sql
 106     isql $DS <${REL_PATH}/mysqldeletelogtable.sql >/dev/null 2>&1
 107     kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 108     kill -9 $pid
 109     ipcrm -M 4000 -M 4500
 110     exit 3
 111 fi
 112 }
 113 else
 114 {
 115 $CSQL_INSTALL_ROOT/bin/cachetable -t t1 -c "f1>10" -f f1,f3,f2 -p f1
 116 if [ $? -ne 0 ]
 117 then
 118     echo "Unable to cache"
 119     isql $DS <${REL_PATH}/dt1.sql  >/dev/null 2>&1
 120     rm -f $REL_PATH/t1.sql ${REL_PATH}/dt1.sql
 121     isql $DS <${REL_PATH}/mysqldeletelogtable.sql >/dev/null 2>&1
 122     kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 123     kill -9 $pid
 124     ipcrm -M 4000 -M 4500
 125     exit 3
 126 fi
 127 }
 128 fi
 129
 130 echo "select * from t1;">$REL_PATH/sel.sql
 131 $CSQL_INSTALL_ROOT/bin/csql -g -s $REL_PATH/sel.sql
 132 if [ $? -ne 0 ]
 133 then
 134     echo "In select"
 135     isql $DS <${REL_PATH}/dt1.sql  >/dev/null 2>&1
 136     rm -f $REL_PATH/t1.sql ${REL_PATH}/dt1.sql $REL_PATH/sel.sql
 137     isql $DS <${REL_PATH}/mysqldeletelogtable.sql >/dev/null 2>&1
 138     kill `ps -e | grep csqlcacheserver | awk -F" " '{ print $1 }'`
 139     kill -9 $pid
 140     ipcrm -M 4000 -M 4500
 141     exit 4
 142 fi
 143
 144 echo "insert into t1 values(17,'papu',12,123124);">$REL_PATH/insert1.sql
 145 echo "insert into t1 values(7,'biju',12,123125);">>$REL_PATH/insert1.sql
 146 echo "insert into t1 values(10,'tapu',12,1231266);">>$REL_PATH/insert1.sql
 147 isql $DS < ${REL_PATH}/insert1.sql >/dev/null 2>&1
 148 sleep 15
 149 $CSQL_INSTALL_ROOT/bin/csql -g -s $REL_PATH/sel.sql
 150 if [ $? -ne 0 ]
 151 then
 152     echo "In 2nd select"
 153     isql $DS <${REL_PATH}/dt1.sql  >/dev/null 2>&1
 154     rm -f $REL_PATH/t1.sql ${REL_PATH}/dt1.sql $REL_PATH/sel.sql $REL_PATH/insert1.sql
 155     isql $DS <${REL_PATH}/mysqldeletelogtable.sql >/dev/null 2>&1
 156     kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 157     kill -9 $pid
 158     ipcrm -M 4000 -M 4500
 159     exit 5
 160 fi
 161
 162 $CSQL_INSTALL_ROOT/bin/cachetable -t t1 -u
 163 isql $DS <${REL_PATH}/dt1.sql  >/dev/null 2>&1
 164 isql $DS <${REL_PATH}/mysqldeletelogtable.sql >/dev/null 2>&1
 165
 166 rm -f $REL_PATH/t1.sql ${REL_PATH}/dt1.sql $REL_PATH/sel.sql $REL_PATH/insert1.sql
 167 kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 168 kill -9 $pid
 169 ipcrm -M 4000 -M 4500
 170 exit 0
 171