test/cache/Bidirectional/test018.ksh

   1 #!/bin/ksh
   2 # Test Case
   3
   4 # Bi-directional cache  test
   5 #Create table t1 with field f1 int ,f2 char,f3 smallint , f4 bigint, primarykey(f1) . Make bidirectional flag and cache flag true. run trigger ,create log table in target db.Cache the table by -c f1>10 .
   6 # Insert record in target db with f1 values as f1>10 and f1<10 and f1 =10 and select from t1 it should shows records having f1 gretter than 10.
   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=$REL_PATH/db2createlogtable.sql
  51     TRI_FILE1=$REL_PATH/trigger.db2
  52     TRI_FILE2=$REL_PATH/trigger1.db2
  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 if [ $DSN = "oracle" ]
  72 then
  73     echo "create table t1(f1 int ,f2 char(12),f3 smallint, f4 number(38),primary key(f1));">$REL_PATH/t1.sql
  74 else
  75     echo "create table t1(f1 int NOT NULL ,f2 char(12),f3 smallint, f4 bigint,primary key(f1));">$REL_PATH/t1.sql
  76 fi
  77 for a in 11 12 13 14
  78 do
  79 echo "insert into t1 values($a,'papu',12,$a+123124);"
  80 done>>$REL_PATH/t1.sql
  81
  82 isql $DS < $REL_PATH/t1.sql  >/dev/null 2>&1
  83 if [ $? -ne 0 ]
  84 then
  85     echo "unable to creste in target db"
  86     rm -f $REL_PATH/t1.sql
  87     exit 2
  88 fi
  89 isql $DS <$TRI_FILE1 >/dev/null
  90
  91 $CSQL_INSTALL_ROOT/bin/csqlserver  >/dev/null 2>&1 &
  92 pid=$!
  93 sleep 5
  94 echo "server  started"
  95 echo "drop table t1;">${REL_PATH}/dt1.sql
  96
  97 $CSQL_INSTALL_ROOT/bin/cachetable -t t1 -c "f1>10"
  98 if [ $? -ne 0 ]
  99 then
 100     echo "Unable to cache"
 101     isql $DS <${REL_PATH}/dt1.sql  >/dev/null 2>&1
 102     rm -f $REL_PATH/t1.sql ${REL_PATH}/dt1.sql
 103     isql $DS <${REL_PATH}/mysqldeletelogtable.sql >/dev/null 2>&1
 104     kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 105     kill -9 $pid
 106     ipcrm -M 4000 -M 4500
 107     exit 3
 108 fi
 109 echo "select * from t1;">$REL_PATH/sel.sql
 110 $CSQL_INSTALL_ROOT/bin/csql -g -s $REL_PATH/sel.sql
 111 if [ $? -ne 0 ]
 112 then
 113     echo "In select"
 114     isql $DS <${REL_PATH}/dt1.sql  >/dev/null 2>&1
 115     rm -f $REL_PATH/t1.sql ${REL_PATH}/dt1.sql $REL_PATH/sel.sql
 116     isql $DS <${REL_PATH}/mysqldeletelogtable.sql >/dev/null 2>&1
 117     kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 118     kill -9 $pid
 119     ipcrm -M 4000 -M 4500
 120     exit 4
 121 fi
 122
 123 echo "insert into t1 values(17,'papu',12,123124);">$REL_PATH/insert1.sql
 124 echo "insert into t1 values(7,'biju',12,123125);">>$REL_PATH/insert1.sql
 125 echo "insert into t1 values(10,'tapu',12,1231266);">>$REL_PATH/insert1.sql
 126 isql $DS < ${REL_PATH}/insert1.sql >/dev/null 2>&1
 127 sleep 15
 128 $CSQL_INSTALL_ROOT/bin/csql -g -s $REL_PATH/sel.sql
 129 if [ $? -ne 0 ]
 130 then
 131     echo "In 2nd select"
 132     isql $DS <${REL_PATH}/dt1.sql  >/dev/null 2>&1
 133     rm -f $REL_PATH/t1.sql ${REL_PATH}/dt1.sql $REL_PATH/sel.sql $REL_PATH/insert1.sql
 134     isql $DS <${REL_PATH}/mysqldeletelogtable.sql >/dev/null 2>&1
 135     kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 136     kill -9 $pid
 137     ipcrm -M 4000 -M 4500
 138     exit 5
 139 fi
 140
 141 $CSQL_INSTALL_ROOT/bin/cachetable -t t1 -u
 142 isql $DS <${REL_PATH}/dt1.sql  >/dev/null 2>&1
 143 isql $DS <${REL_PATH}/mysqldeletelogtable.sql >/dev/null 2>&1
 144
 145 rm -f $REL_PATH/t1.sql ${REL_PATH}/dt1.sql $REL_PATH/sel.sql $REL_PATH/insert1.sql
 146 kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 147 kill -9 $pid
 148 ipcrm -M 4000 -M 4500
 149 exit 0
 150