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