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