test/cache/Bidirectional/test014.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 # Update 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 a=1
  42 while [[ $a -lt 101 ]];do
  43    echo "update t$a set f2=50 where f2 >19;"
  44    (( a +=1 ))
  45 done >> $REL_PATH/update100table.sql
  46 echo "Update file created"
  47 rm  -f /tmp/csql1.conf
  48 cp $REL_PATH/csql1.conf /tmp
  49 export CSQL_CONFIG_FILE=/tmp/csql1.conf
  50 rm -f /tmp/csql1/csqlds.conf
  51 touch /tmp/csql1/csqlds.conf
  52 $CSQL_INSTALL_ROOT/bin/csqlds -U $DBUSER -P $PASSWORD -D $DSN -N $TDB -a
  53 if [ $? -ne 0 ]
  54 then
  55    exit 1;
  56 fi
  57 DS="$DSN $DBUSER $PASSWORD"
  58 echo DSN=$DSN >>$CSQL_CONFIG_FILE
  59 if [ $DSN = "myodbc3" ]
  60 then
  61     LOG_FILE=$REL_PATH/mysqlcreatelogtable.sql
  62     echo "use test;">>$REL_PATH/updatetrigger100.sql
  63     a=1
  64     while [[ $a -lt 101 ]];do
  65         echo "drop trigger if exists triggerupdatet$a;"
  66         echo "create trigger triggerupdatet$a AFTER UPDATE on t$a FOR EACH ROW BEGIN Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('t$a', OLD.f1, 2,1);Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('t$a', NEW.f1, 1,1); End;"
  67         (( a +=1 ))
  68     done >> $REL_PATH/updatetrigger100.sql
  69     echo "Trigger file generated"
  70        a=1
  71     while [[ $a -lt 101 ]];do
  72        echo "drop trigger if exists triggerupdatet$a;"
  73        (( a +=1 ))
  74     done >>$REL_PATH/dropupdatetrigger.sql
  75     echo "Drop trigger file generated"
  76 elif [ $DSN = "psql" ]
  77 then
  78    a=1
  79    while [[ $a -lt 101 ]];do
  80        echo "DROP FUNCTION IF EXISTS log_update_t$a() CASCADE ;"
  81        echo "CREATE OR REPLACE FUNCTION log_update_t$a() RETURNS trigger AS \$triggerupdatet$a$ BEGIN insert into csql_log_int (tablename, pkid, operation, cacheid) values ('t$a', OLD.f1, 2, 1); insert into csql_log_int (tablename, pkid, operation, cacheid) values ('t$a', NEW.f1, 1, 1); RETURN NEW; END; \$triggerupdatet$a$ LANGUAGE plpgsql;"
  82        echo "create trigger triggerupdatet$a AFTER UPDATE on t$a FOR EACH ROW EXECUTE PROCEDURE log_update_t$a();"
  83        (( a +=1 ))
  84    done >> $REL_PATH/updatetrigger100.sql
  85    echo "Trigger file generated"
  86
  87    a=1
  88    while [[ $a -lt 101 ]];do
  89        echo "DROP FUNCTION IF EXISTS log_update_t$a() CASCADE ;"
  90        (( a +=1 ))
  91    done >>$REL_PATH/dropupdatetrigger.sql
  92    echo "Drop trigger file generated"
  93
  94    LOG_FILE=$REL_PATH/psqlcreatelogtable.sql
  95 elif [ $DSN = "oracle" ]
  96 then
  97    a=1
  98    while [[ $a -lt 101 ]];do
  99        echo "CREATE OR REPLACE TRIGGER triggert$a AFTER  update on t$a FOR EACH ROW declare  rid PLS_INTEGER; begin  SELECT CSQL_ID.NEXTVAL INTO rid  FROM dual;  if updating then insert into csql_log_int values ('t$a',:old.f1,2,1,rid); SELECT CSQL_ID.NEXTVAL INTO rid  FROM dual; insert into csql_log_int values ('t$a',:new.f1,1,1,rid); end if; end;"
 100        (( a +=1 ))
 101    done >> $REL_PATH/updatetrigger100.sql
 102    echo "Trigger file generated"
 103    a=1
 104    while [[ $a -lt 101 ]];do
 105        echo "DROP TRIGGER triggert$a;"
 106        (( a +=1 ))
 107    done >>$REL_PATH/dropupdatetrigger.sql
 108    echo "Drop trigger file generated"
 109
 110    LOG_FILE=$REL_PATH/oraclecreatelogtable.sql
 111    echo TARGET_DATABASE=oracle >>$CSQL_CONFIG_FILE
 112 elif [ $DSN = "sybase" ]
 113 then
 114     echo "Sybase trigger Missinf"
 115 else
 116     echo "other triger"
 117 fi
 118 #echo $DS
 119 isql $DS < $LOG_FILE >/dev/null 2>&1
 120 if [ $? -ne 0 ]
 121 then
 122     echo "DSN is not set for target db"
 123     rm -f $REL_PATH/create100table.sql
 124     rm -f $REL_PATH/update100table.sql
 125     rm -f $REL_PATH/selectfrom100.sql
 126     rm -f $REL_PATH/drop100table.sql
 127     rm -f $REL_PATH/dropupdatetrigger.sql
 128     rm -f $REL_PATH/updatetrigger100.sql
 129     exit 1
 130 fi
 131 echo Log table created in target DB
 132 isql $DS < $REL_PATH/create100table.sql >/dev/null
 133 rm -f /tmp/csql/csqltable.conf
 134 touch /tmp/csql/csqltable.conf
 135 isql $DS <$REL_PATH/updatetrigger100.sql >/dev/null
 136
 137 a=1
 138 while [[ $a -lt 101 ]];do
 139     echo "1 t$a NULL NULL NULL $DSN"
 140     (( a +=1 ))
 141 done >> /tmp/csql/csqltable.conf
 142
 143 $CSQL_INSTALL_ROOT/bin/csqlserver -c >/dev/null 2>&1 &
 144 pid=$!
 145 sleep 30
 146 echo "server  started"
 147
 148 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/selectfrom100.sql
 149 if [ $? -ne 0 ]
 150 then
 151     echo "unable to locate cache 1"
 152     isql $DS < ${REL_PATH}/drop100table.sql >/dev/null 2>&1
 153     rm -f $REL_PATH/create100table.sql
 154     rm -f $REL_PATH/update100table.sql
 155     rm -f $REL_PATH/selectfrom100.sql
 156     rm -f $REL_PATH/drop100table.sql
 157     rm -f $REL_PATH/dropupdatetrigger.sql
 158     rm -f $REL_PATH/updatetrigger100.sql
 159     exit 2
 160 fi
 161
 162 echo "Update some record in target database"
 163 isql $DS < ${REL_PATH}/update100table.sql >/dev/null 2>&1
 164 sleep 30
 165 echo "Records in csql after update in target DB"
 166 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/selectfrom100.sql
 167
 168 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/drop100table.sql > /dev/null 2>&1
 169 isql $DS < ${REL_PATH}/drop100table.sql >/dev/null 2>&1
 170 rm -f /tmp/csql/csqltable.conf
 171 touch /tmp/csql/csqltable.conf
 172 isql $DS < $REL_PATH/dropupdatetrigger.sql >/dev/null 2>&1
 173 isql $DS < $REL_PATH/mysqldeletelogtable.sql >/dev/null 2>&1
 174 kill `ps -ef | grep csqlcacheserver | awk -F" " '{ print $2 }'`
 175 kill -9 $pid
 176 ipcrm -M 4000 -M 4500
 177
 178 rm -f $REL_PATH/create100table.sql
 179 rm -f $REL_PATH/update100table.sql
 180 rm -f $REL_PATH/selectfrom100.sql
 181 rm -f $REL_PATH/drop100table.sql
 182 rm -f $REL_PATH/updatetrigger100.sql
 183 rm -f $REL_PATH/dropupdatetrigger.sql
 184 exit 0;