1 ----------------------------------README---------------------------------------
2 This contains an example program that demonstrates usage of multiple bidirectional cache .
7 This will create a table employee with three field eid integer(primary key),zone integer,balance float and inserts 10 rows into table employee.
10 This will insert 2 rows into employee table.
13 This will create csql_log_table for Bi-directional cache.
16 This will create trigger for cached table employee to generate log on any non-select DML statement.
21 A) Take two csql.conf file (say csql.conf,csql1.conf). Change SYS_DB_KEY ,USER_DB_KEY, ID_SHM_KEY, MSG_KEY, TABLE_CONFIG_FILE with different values for both configuration file.
22 Set CACHE_TABLE=true ENABLE_BIDIRECTIONAL_CACHE=true and CACHE_ID=1 in csql.conf and CACHE_ID=2 in csql1.conf.Set TABLE_CONFIG_FILE path different for different cache node.
24 B) Add an entry into csqlds.conf using csqlds tool mentioned as below.
26 $ csqlds -U <tdbusername> -P <password> -D <dsnname> -N <tdbname> -a
28 Note# tdb->target database
31 C) Change CSQL_CONFIG_FILE enviroment variable in `setupenv.ksh` to sample/csql.conf and and in another setup file (say setupenv1.ksh)set CSQL_CONFIG_FILE to sample/csql1.conf.
36 A) In two different terminal set enviroment variables using the setup script and start csqlserver.
43 ii) In another terminal
48 B) In mysql create log table, employee table and run trigger.
51 $isql myodbc3 < create.sql
52 $isql myodbc3 < mysqllog.sql
53 $isql myodbc3 < trigger.sql
55 C) In two different terminal set the necessary enviroment variables using the setup script and cache the table.
60 $cachetable -t employee -c "zone=1"
62 ii) In another terminal
65 $cachetable -t employee -c "zone=2"
67 D) Insert some record in mysql
69 $isql myodbc3 < insert.sql
76 +---------------------------------------+
83 +---------------------------------------+
84 SQL> create table employee(eid int,zone int,balance float,primary key(eid));
86 SQL> insert into employee values(1020,1,1000.50);
88 SQL> insert into employee values(1021,1,2000.50);
90 SQL> insert into employee values(1022,1,3000.50);
92 SQL> insert into employee values(1023,1,4000.50);
94 SQL> insert into employee values(1024,1,5000.50);
96 SQL> insert into employee values(1025,2,6000.50);
98 SQL> insert into employee values(1026,2,7000.50);
100 SQL> insert into employee values(1027,2,8000.50);
101 SQLRowCount returns 1
102 SQL> insert into employee values(1028,2,9000.50);
103 SQLRowCount returns 1
104 SQL> insert into employee values(1029,2,1100.50);
105 SQLRowCount returns 1
109 +---------------------------------------+
116 +---------------------------------------+
117 SQL> create table csql_log_int(tablename char(64), pkid int, operation int,cacheid int, id int not null unique auto_increment)engine='innodb';
118 SQLRowCount returns 0
123 +---------------------------------------+
130 +---------------------------------------+
131 SQL> drop trigger if exists triggerinsertemployee;
132 SQLRowCount returns 0
133 SQL> drop trigger if exists triggerupdateemployee;
134 SQLRowCount returns 0
135 SQL> drop trigger if exists triggerdeleteemployee;
136 SQLRowCount returns 0
137 SQL> create trigger triggerinsertemployee AFTER INSERT on employee FOR EACH ROW BEGIN Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('employee', NEW.eid, 1,1); Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('employee', NEW.eid, 1,2);End;
138 SQLRowCount returns 0
139 SQL> create trigger triggerupdateemployee AFTER UPDATE on employee FOR EACH ROW BEGIN Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('employee', OLD.eid, 2,1);Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('employee', NEW.eid, 1,1);Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('employee', OLD.eid, 2,2);Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('employee', NEW.eid, 1,2); End;
140 SQLRowCount returns 0
141 SQL> create trigger triggerdeleteemployee AFTER DELETE on employee FOR EACH ROW BEGIN Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('employee', OLD.eid, 2,1); Insert into csql_log_int (tablename, pkid, operation,cacheid) values ('employee', OLD.eid, 2,2);End;
142 SQLRowCount returns 0
144 D) After cache the table data in diiferent node.
148 $ cachetable -t employee -c "zone = 1"
150 CSQL>select * from employee;
151 ---------------------------------------------------------
152 employee.eid employee.zone employee.balance
153 ---------------------------------------------------------
160 ii) In another cache node
162 $ cachetable -t employee -c "zone = 2"
164 CSQL>select * from employee;
165 ---------------------------------------------------------
166 employee.eid employee.zone employee.balance
167 ---------------------------------------------------------
176 +---------------------------------------+
183 +---------------------------------------+
184 SQL> insert into employee values(1030,1,6000.50);
185 SQLRowCount returns 1
186 SQL> insert into employee values(1031,2,7000.50);
187 SQLRowCount returns 1
190 F) After insert in mysql data in different node.
194 CSQL>select * from employee;
195 ---------------------------------------------------------
196 employee.eid employee.zone employee.balance
197 ---------------------------------------------------------
205 ii) In another cache node
206 CSQL>select * from employee;
207 ---------------------------------------------------------
208 employee.eid employee.zone employee.balance
209 ---------------------------------------------------------
217 For Postgres cache solution follow Cache Guide.