test/cache/Bidirectional/test015.ksh

   1 #!/bin/ksh
   2 # Test Case
   3 # Create 100 table in target database with a integer primary key.
   4 # Run trigger for all table. Make sure Log table is in target database
   5 # Run cache server with bi-directional and cache flag true. Cache all tables.
   6 # Delete some record in each table in target database. Check in csql node records are inserted or not.
   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
  17 a=1
  18 while [[ $a -lt 101 ]];do
  19 echo "create table t$a(f1 int,f2 int,primary key(f1));"
  20 echo "insert into t$a values(1,2);"
  21 echo "insert into t$a values(10,20);"
  22 echo "insert into t$a values(11,21);"
  23 (( a +=1 ))
  24 done >>$REL_PATH/create100table.sql
  25 echo "table creation file created"
  26
  27 a=1
  28 while [[ $a -lt 101 ]];do
  29 echo "echo select * from t$a;"
  30 echo "select * from t$a;"
  31 (( a +=1 ))
  32 done >>$REL_PATH/selectfrom100.sql
  33 echo "select file created"
  34
  35 a=1
  36 while [[ $a -lt 101 ]];do
  37 echo "drop table t$a;"
  38 (( a +=1 ))
  39 done >>$REL_PATH/drop100table.sql
  40
  41
  42 a=1
  43 while [[ $a -lt 101 ]];do
  44 echo "delete from t$a where f2 >19;"
  45 (( a +=1 ))
  46 done >> $REL_PATH/deletefrom100table.sql
  47 echo "Update file created"
  48
  49 rm -f /tmp/csql1.conf
  50 cp $REL_PATH/csql1.conf /tmp
  51 export CSQL_CONFIG_FILE=/tmp/csql1.conf
  52 rm -f /tmp/csql1/csqlds.conf
  53 touch /tmp/csql1/csqlds.conf
  54 $CSQL_INSTALL_ROOT/bin/csqlds -U $DBUSER -P $PASSWORD -D $DSN -N $TDB -a
  55 if [ $? -ne 0 ]
  56 then
  57    exit 1;
  58 fi
  59 DS="$DSN $DBUSER $PASSWORD"
  60 echo DSN=$DSN >>$CSQL_CONFIG_FILE
  61
  62 if [ $DSN = "myodbc3" ]
  63 then
  64    echo "use test;">>$REL_PATH/deletetrigger100.sql
  65    a=1
  66    while [[ $a -lt 101 ]];do
  67        echo "drop trigger if exists triggerdeletet$a;"
  68        echo "create trigger triggerdeletet$a AFTER DELETE on t$a FOR EACH ROW BEGIN Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('t$a', OLD.f1, 2,1); End;"
  69        (( a +=1 ))
  70   done >> $REL_PATH/deletetrigger100.sql
  71   echo "Trigger file generated"
  72
  73    a=1
  74    while [[ $a -lt 101 ]];do
  75        echo "drop trigger if exists triggerdeletet$a;"
  76        (( a +=1 ))
  77    done >>$REL_PATH/dropdeletetrigger.sql
  78    echo "Drop trigger file generated"
  79        LOG_FILE=$REL_PATH/mysqlcreatelogtable.sql
  80 elif [ $DSN = "psql" ]
  81 then
  82    echo "CREATE LANGUAGE plpgsql;">$REL_PATH/deletetrigger100.sql
  83    a=1
  84    while [[ $a -lt 101 ]];do
  85        echo "DROP FUNCTION IF EXISTS log_delete_t$a() CASCADE ;"
  86        echo "CREATE OR REPLACE FUNCTION log_delete_t$a() RETURNS trigger AS \$triggerdeletet$a$ BEGIN insert into csql_log_int (tablename, pkid, operation, cacheid) values ('t$a', OLD.f1, 2, 1); RETURN NEW; END; \$triggerdeletet$a$ LANGUAGE plpgsql;"
  87        echo "create trigger triggerdeletet$a AFTER DELETE on t$a  FOR EACH ROW EXECUTE PROCEDURE log_delete_t$a();"
  88        (( a +=1 ))
  89    done >> $REL_PATH/deletetrigger100.sql
  90    echo "Trigger file generated"
  91
  92    a=1
  93    while [[ $a -lt 101 ]];do
  94        echo "DROP FUNCTION IF EXISTS log_delete_t$a() CASCADE ;"
  95        (( a +=1 ))
  96    done >>$REL_PATH/dropdeletetrigger.sql
  97    echo "Drop trigger file generated"
  98    LOG_FILE=$REL_PATH/psqlcreatelogtable.sql
  99 elif [ $DSN = "oracle" ]
 100 then
 101    a=1
 102    while [[ $a -lt 101 ]];do
 103        echo "CREATE OR REPLACE TRIGGER triggertdel$a AFTER  delete on t$a FOR EACH ROW declare  rid PLS_INTEGER; begin  SELECT CSQL_ID.NEXTVAL INTO rid  FROM dual;  if deleting then insert into csql_log_int values ('t$a',:old.f1,2,1,rid); end if; end;"
 104        (( a +=1 ))
 105    done >> $REL_PATH/deletetrigger100.sql
 106    echo "Trigger file generated"
 107
 108    a=1
 109    while [[ $a -lt 101 ]];do
 110        echo "DROP TRIGGER triggertdel$a;"
 111        (( a +=1 ))
 112    done >>$REL_PATH/dropdeletetrigger.sql
 113    echo "Drop trigger file generated"
 114    LOG_FILE=$REL_PATH/oraclecreatelogtable.sql
 115 elif [ $DSN = "sybase" ]
 116 then
 117    echo "sybase trigger missing"
 118 else
 119    echo "other trigger"
 120 fi
 121 #echo $DS
 122 isql $DS < $LOG_FILE >/dev/null 2>&1
 123 if [ $? -ne 0 ]
 124 then
 125     echo "DSN is not set for target db"
 126     rm -f $REL_PATH/create100table.sql
 127     rm -f $REL_PATH/deletefrom100table.sql
 128     rm -f $REL_PATH/selectfrom100.sql
 129     rm -f $REL_PATH/drop100table.sql
 130     rm -f $REL_PATH/dropdeletetrigger.sql
 131     rm -f $REL_PATH/deletetrigger100.sql
 132     exit 1
 133 fi
 134 echo Log table created in target DB
 135 isql $DS < $REL_PATH/create100table.sql >/dev/null
 136 rm -f /tmp/csql/csqltable.conf
 137 touch /tmp/csql/csqltable.conf
 138 isql $DS <$REL_PATH/deletetrigger100.sql >/dev/null
 139
 140 a=1
 141 while [[ $a -lt 101 ]];do
 142     echo "1 t$a NULL NULL NULL $DSN"
 143     (( a +=1 ))
 144 done >> /tmp/csql/csqltable.conf
 145
 146 $CSQL_INSTALL_ROOT/bin/csqlserver -c >/dev/null 2>&1 &
 147 pid=$!
 148 sleep 30
 149 echo "server  started"
 150
 151 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/selectfrom100.sql
 152 if [ $? -ne 0 ]
 153 then
 154     echo "unable to locate cache 1"
 155     isql $DS < ${REL_PATH}/drop100table.sql >/dev/null 2>&1
 156     rm -f $REL_PATH/create100table.sql
 157     rm -f $REL_PATH/deletefrom100table.sql
 158     rm -f $REL_PATH/selectfrom100.sql
 159     rm -f $REL_PATH/drop100table.sql
 160     rm -f $REL_PATH/dropdeletetrigger.sql
 161     rm -f $REL_PATH/deletetrigger100.sql
 162     exit 2
 163 fi
 164
 165 echo "Delete some record in target database"
 166 isql $DS < ${REL_PATH}/deletefrom100table.sql >/dev/null 2>&1
 167 sleep 30
 168 echo "Records in csql after Delete some recode from target DB"
 169 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/selectfrom100.sql
 170
 171 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/drop100table.sql > /dev/null 2>&1
 172 isql $DS < ${REL_PATH}/drop100table.sql >/dev/null 2>&1
 173 rm -f /tmp/csql/csqltable.conf
 174 touch /tmp/csql/csqltable.conf
 175 isql $DS < $REL_PATH/dropdeletetrigger.sql >/dev/null 2>&1
 176 isql $DS < $REL_PATH/mysqldeletelogtable.sql >/dev/null 2>&1
 177 kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 178 kill -9 $pid
 179 ipcrm -M 4000 -M 4500
 180
 181 rm -f $REL_PATH/create100table.sql
 182 rm -f $REL_PATH/deletefrom100table.sql
 183 rm -f $REL_PATH/selectfrom100.sql
 184 rm -f $REL_PATH/drop100table.sql
 185 rm -f $REL_PATH/deletetrigger100.sql
 186 rm -f $REL_PATH/dropdeletetrigger.sql
 187 exit 0;