1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/trigger4.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,200 @@
1.4 +# The author disclaims copyright to this source code. In place of
1.5 +# a legal notice, here is a blessing:
1.6 +#
1.7 +# May you do good and not evil.
1.8 +# May you find forgiveness for yourself and forgive others.
1.9 +# May you share freely, never taking more than you give.
1.10 +#
1.11 +#***********************************************************************
1.12 +#
1.13 +# This file tests the triggers of views.
1.14 +#
1.15 +
1.16 +set testdir [file dirname $argv0]
1.17 +source $testdir/tester.tcl
1.18 +
1.19 +# If either views or triggers are disabled in this build, omit this file.
1.20 +ifcapable {!trigger || !view} {
1.21 + finish_test
1.22 + return
1.23 +}
1.24 +
1.25 +do_test trigger4-1.1 {
1.26 + execsql {
1.27 + create table test1(id integer primary key,a);
1.28 + create table test2(id integer,b);
1.29 + create view test as
1.30 + select test1.id as id,a as a,b as b
1.31 + from test1 join test2 on test2.id = test1.id;
1.32 + create trigger I_test instead of insert on test
1.33 + begin
1.34 + insert into test1 (id,a) values (NEW.id,NEW.a);
1.35 + insert into test2 (id,b) values (NEW.id,NEW.b);
1.36 + end;
1.37 + insert into test values(1,2,3);
1.38 + select * from test1;
1.39 + }
1.40 +} {1 2}
1.41 +do_test trigger4-1.2 {
1.42 + execsql {
1.43 + select * from test2;
1.44 + }
1.45 +} {1 3}
1.46 +do_test trigger4-1.3 {
1.47 + db close
1.48 + sqlite3 db test.db
1.49 + execsql {
1.50 + insert into test values(4,5,6);
1.51 + select * from test1;
1.52 + }
1.53 +} {1 2 4 5}
1.54 +do_test trigger4-1.4 {
1.55 + execsql {
1.56 + select * from test2;
1.57 + }
1.58 +} {1 3 4 6}
1.59 +
1.60 +do_test trigger4-2.1 {
1.61 + execsql {
1.62 + create trigger U_test instead of update on test
1.63 + begin
1.64 + update test1 set a=NEW.a where id=NEW.id;
1.65 + update test2 set b=NEW.b where id=NEW.id;
1.66 + end;
1.67 + update test set a=22 where id=1;
1.68 + select * from test1;
1.69 + }
1.70 +} {1 22 4 5}
1.71 +do_test trigger4-2.2 {
1.72 + execsql {
1.73 + select * from test2;
1.74 + }
1.75 +} {1 3 4 6}
1.76 +do_test trigger4-2.3 {
1.77 + db close
1.78 + sqlite3 db test.db
1.79 + execsql {
1.80 + update test set b=66 where id=4;
1.81 + select * from test1;
1.82 + }
1.83 +} {1 22 4 5}
1.84 +do_test trigger4-2.4 {
1.85 + execsql {
1.86 + select * from test2;
1.87 + }
1.88 +} {1 3 4 66}
1.89 +
1.90 +do_test trigger4-3.1 {
1.91 + catchsql {
1.92 + drop table test2;
1.93 + insert into test values(7,8,9);
1.94 + }
1.95 +} {1 {no such table: main.test2}}
1.96 +do_test trigger4-3.2 {
1.97 + db close
1.98 + sqlite3 db test.db
1.99 + catchsql {
1.100 + insert into test values(7,8,9);
1.101 + }
1.102 +} {1 {no such table: main.test2}}
1.103 +do_test trigger4-3.3 {
1.104 + catchsql {
1.105 + update test set a=222 where id=1;
1.106 + }
1.107 +} {1 {no such table: main.test2}}
1.108 +do_test trigger4-3.4 {
1.109 + execsql {
1.110 + select * from test1;
1.111 + }
1.112 +} {1 22 4 5}
1.113 +do_test trigger4-3.5 {
1.114 + execsql {
1.115 + create table test2(id,b);
1.116 + insert into test values(7,8,9);
1.117 + select * from test1;
1.118 + }
1.119 +} {1 22 4 5 7 8}
1.120 +do_test trigger4-3.6 {
1.121 + execsql {
1.122 + select * from test2;
1.123 + }
1.124 +} {7 9}
1.125 +do_test trigger4-3.7 {
1.126 + db close
1.127 + sqlite3 db test.db
1.128 + execsql {
1.129 + update test set b=99 where id=7;
1.130 + select * from test2;
1.131 + }
1.132 +} {7 99}
1.133 +
1.134 +do_test trigger4-4.1 {
1.135 + db close
1.136 + file delete -force trigtest.db
1.137 + file delete -force trigtest.db-journal
1.138 + sqlite3 db trigtest.db
1.139 + catchsql {drop table tbl; drop view vw}
1.140 + execsql {
1.141 + create table tbl(a integer primary key, b integer);
1.142 + create view vw as select * from tbl;
1.143 + create trigger t_del_tbl instead of delete on vw for each row begin
1.144 + delete from tbl where a = old.a;
1.145 + end;
1.146 + create trigger t_upd_tbl instead of update on vw for each row begin
1.147 + update tbl set a=new.a, b=new.b where a = old.a;
1.148 + end;
1.149 + create trigger t_ins_tbl instead of insert on vw for each row begin
1.150 + insert into tbl values (new.a,new.b);
1.151 + end;
1.152 + insert into tbl values(101,1001);
1.153 + insert into tbl values(102,1002);
1.154 + insert into tbl select a+2, b+2 from tbl;
1.155 + insert into tbl select a+4, b+4 from tbl;
1.156 + insert into tbl select a+8, b+8 from tbl;
1.157 + insert into tbl select a+16, b+16 from tbl;
1.158 + insert into tbl select a+32, b+32 from tbl;
1.159 + insert into tbl select a+64, b+64 from tbl;
1.160 + select count(*) from vw;
1.161 + }
1.162 +} {128}
1.163 +do_test trigger4-4.2 {
1.164 + execsql {select a, b from vw where a<103 or a>226 order by a}
1.165 +} {101 1001 102 1002 227 1127 228 1128}
1.166 +
1.167 +#test delete from view
1.168 +do_test trigger4-5.1 {
1.169 + catchsql {delete from vw where a>101 and a<2000}
1.170 +} {0 {}}
1.171 +do_test trigger4-5.2 {
1.172 + execsql {select * from vw}
1.173 +} {101 1001}
1.174 +
1.175 +#test insert into view
1.176 +do_test trigger4-6.1 {
1.177 + catchsql {
1.178 + insert into vw values(102,1002);
1.179 + insert into vw select a+2, b+2 from vw;
1.180 + insert into vw select a+4, b+4 from vw;
1.181 + insert into vw select a+8, b+8 from vw;
1.182 + insert into vw select a+16, b+16 from vw;
1.183 + insert into vw select a+32, b+32 from vw;
1.184 + insert into vw select a+64, b+64 from vw;
1.185 + }
1.186 +} {0 {}}
1.187 +do_test trigger4-6.2 {
1.188 + execsql {select count(*) from vw}
1.189 +} {128}
1.190 +
1.191 +#test update of view
1.192 +do_test trigger4-7.1 {
1.193 + catchsql {update vw set b=b+1000 where a>101 and a<2000}
1.194 +} {0 {}}
1.195 +do_test trigger4-7.2 {
1.196 + execsql {select a, b from vw where a<=102 or a>=227 order by a}
1.197 +} {101 1001 102 2002 227 2127 228 2128}
1.198 +
1.199 +integrity_check trigger4-99.9
1.200 +db close
1.201 +file delete -force trigtest.db trigtest.db-journal
1.202 +
1.203 +finish_test