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 .
9 input
=${PWD}/cache
/Bidirectional
/mysqlcreatelogtable.sql
14 REL_PATH
=${PWD}/cache
/Bidirectional
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);"
24 done >>$REL_PATH/create100table.sql
25 echo "table creation file created"
28 while [[ $a -lt 101 ]];do
29 echo "echo select * from t$a;"
30 echo "select * from t$a;"
32 done >>$REL_PATH/selectfrom100.sql
33 echo "select file created"
36 while [[ $a -lt 101 ]];do
37 echo "drop table t$a;"
39 done >>$REL_PATH/drop100table.sql
42 while [[ $a -lt 101 ]];do
43 echo "update t$a set f2=50 where f2 >19;"
45 done >> $REL_PATH/update100table.sql
46 echo "Update file created"
48 cp $REL_PATH/csql1.conf
/tmp
49 export CSQL_CONFIG_FILE
=/tmp
/csql1.conf
50 rm -f /tmp
/csql
1/csqlds.conf
51 touch /tmp
/csql
1/csqlds.conf
52 $CSQL_INSTALL_ROOT/bin
/csqlds
-U $DBUSER -P $PASSWORD -D $DSN -N $TDB -a
57 DS
="$DSN $DBUSER $PASSWORD"
58 echo DSN
=$DSN >>$CSQL_CONFIG_FILE
59 if [ $DSN = "myodbc3" ]
61 LOG_FILE
=$REL_PATH/mysqlcreatelogtable.sql
62 echo "use test;">>$REL_PATH/updatetrigger100.sql
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;"
68 done >> $REL_PATH/updatetrigger100.sql
69 echo "Trigger file generated"
71 while [[ $a -lt 101 ]];do
72 echo "drop trigger if exists triggerupdatet$a;"
74 done >>$REL_PATH/dropupdatetrigger.sql
75 echo "Drop trigger file generated"
76 elif [ $DSN = "psql" ]
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();"
84 done >> $REL_PATH/updatetrigger100.sql
85 echo "Trigger file generated"
88 while [[ $a -lt 101 ]];do
89 echo "DROP FUNCTION IF EXISTS log_update_t$a() CASCADE ;"
91 done >>$REL_PATH/dropupdatetrigger.sql
92 echo "Drop trigger file generated"
94 LOG_FILE
=$REL_PATH/psqlcreatelogtable.sql
95 elif [ $DSN = "oracle" ]
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;"
101 done >> $REL_PATH/updatetrigger100.sql
102 echo "Trigger file generated"
104 while [[ $a -lt 101 ]];do
105 echo "DROP TRIGGER triggert$a;"
107 done >>$REL_PATH/dropupdatetrigger.sql
108 echo "Drop trigger file generated"
110 LOG_FILE
=$REL_PATH/oraclecreatelogtable.sql
111 echo TARGET_DATABASE
=oracle
>>$CSQL_CONFIG_FILE
112 elif [ $DSN = "sybase" ]
114 echo "Sybase trigger Missinf"
119 isql
$DS < $LOG_FILE >/dev
/null
2>&1
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
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
138 while [[ $a -lt 101 ]];do
139 echo "1 t$a NULL NULL NULL $DSN"
141 done >> /tmp
/csql
/csqltable.conf
143 $CSQL_INSTALL_ROOT/bin
/csqlserver
-c >/dev
/null
2>&1 &
146 echo "server started"
148 $CSQL_INSTALL_ROOT/bin
/csql
-s $REL_PATH/selectfrom100.sql
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
162 echo "Update some record in target database"
163 isql
$DS < ${REL_PATH}/update100table.sql
>/dev
/null
2>&1
165 echo "Records in csql after update in target DB"
166 $CSQL_INSTALL_ROOT/bin
/csql
-s $REL_PATH/selectfrom100.sql
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 }'`
176 ipcrm
-M 4000 -M 4500
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