test/cache/Bidirectional/test014.ksh

   1 #!/bin/sh
   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 for((a=1; a<=100; a++))
  18 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 done >>$REL_PATH/create100table.sql
  24 echo "table creation file created"
  25
  26 for((a=1; a<=100; a++))
  27 do
  28 echo "echo select * from t$a;"
  29 echo "select * from t$a;"
  30 done >>$REL_PATH/selectfrom100.sql
  31 echo "select file created"
  32
  33 for((a=1; a<=100; a++))
  34 do
  35 echo "drop table t$a;"
  36 done >>$REL_PATH/drop100table.sql
  37
  38 echo "use test;">>$REL_PATH/updatetrigger100.sql
  39 for((a=1; a<=100; a++))
  40 do
  41 echo "drop trigger if exists triggerupdatet$a;"
  42 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;"
  43 done >> $REL_PATH/updatetrigger100.sql
  44 echo "Trigger file generated"
  45
  46 for((a=1; a<=100; a++))
  47 do
  48 echo "drop trigger if exists triggerupdatet$a;"
  49 done >>$REL_PATH/dropupdatetrigger.sql
  50 echo "Drop trigger file generated"
  51
  52 for((a=1; a<=100; a++))
  53 do
  54 echo "update t$a set f2=50 where f2 >19;"
  55 done >> $REL_PATH/update100table.sql
  56 echo "Update file created"
  57 rm  -f /tmp/csql1.conf
  58 cp $REL_PATH/csql1.conf /tmp
  59 export CSQL_CONFIG_FILE=/tmp/csql1.conf
  60 echo DSN=$DSN >>$CSQL_CONFIG_FILE
  61
  62 isql $DSN < $REL_PATH/mysqlcreatelogtable.sql >/dev/null 2>&1
  63 if [ $? -ne 0 ]
  64 then
  65     echo "DSN is not set for target db"
  66     rm -f $REL_PATH/create100table.sql
  67     rm -f $REL_PATH/update100table.sql
  68     rm -f $REL_PATH/selectfrom100.sql
  69     rm -f $REL_PATH/drop100table.sql
  70     rm -f $REL_PATH/dropupdatetrigger.sql
  71     rm -f $REL_PATH/updatetrigger100.sql
  72     exit 1
  73 fi
  74 echo Log table created in target DB
  75 isql $DSN < $REL_PATH/create100table.sql >/dev/null
  76 rm -f /tmp/csql/csqltable.conf /tmp/csql/csql.db
  77 touch /tmp/csql/csqltable.conf /tmp/csql/csql.db
  78 isql $DSN <$REL_PATH/updatetrigger100.sql >/dev/null
  79
  80
  81 for (( a=1; a<=100; a++ ))
  82 do
  83     echo "1:t$a NULL NULL NULL"
  84 done >> /tmp/csql/csqltable.conf
  85
  86 $CSQL_INSTALL_ROOT/bin/csqlserver -c >/dev/null 2>&1 &
  87 pid=$!
  88 sleep 30
  89 echo "server  started"
  90
  91 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/selectfrom100.sql
  92 if [ $? -ne 0 ]
  93 then
  94     echo "unable to locate cache 1"
  95     isql $DSN < ${REL_PATH}/drop100table.sql >/dev/null 2>&1
  96     rm -f $REL_PATH/create100table.sql
  97     rm -f $REL_PATH/update100table.sql
  98     rm -f $REL_PATH/selectfrom100.sql
  99     rm -f $REL_PATH/drop100table.sql
 100     rm -f $REL_PATH/dropupdatetrigger.sql
 101     rm -f $REL_PATH/updatetrigger100.sql
 102     exit 2
 103 fi
 104
 105 echo "Update some record in target database"
 106 isql $DSN < ${REL_PATH}/update100table.sql >/dev/null 2>&1
 107 sleep 30
 108 echo "Records in csql after update in target DB"
 109 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/selectfrom100.sql
 110
 111 $CSQL_INSTALL_ROOT/bin/csql -s $REL_PATH/drop100table.sql > /dev/null 2>&1
 112 isql $DSN < ${REL_PATH}/drop100table.sql >/dev/null 2>&1
 113 rm -f /tmp/csql/csqltable.conf /tmp/csql/csql.db
 114 touch /tmp/csql/csqltable.conf /tmp/csql/csql.db
 115 isql $DSN < $REL_PATH/dropupdatetrigger.sql >/dev/null 2>&1
 116 isql $DSN < $REL_PATH/mysqldeletelogtable.sql >/dev/null 2>&1
 117 kill -2 $pid
 118 ipcrm -M 4000 -M 4500
 119
 120 rm -f $REL_PATH/create100table.sql
 121 rm -f $REL_PATH/update100table.sql
 122 rm -f $REL_PATH/selectfrom100.sql
 123 rm -f $REL_PATH/drop100table.sql
 124 rm -f $REL_PATH/updatetrigger100.sql
 125 rm -f $REL_PATH/dropupdatetrigger.sql
 126 exit 0;