1.1 --- /dev/null Thu Jan 01 00:00:00 1970 +0000
1.2 +++ b/os/persistentdata/persistentstorage/sqlite3api/TEST/TclScript/triggerA.test Fri Jun 15 03:10:57 2012 +0200
1.3 @@ -0,0 +1,230 @@
1.4 +# 2008 February 12
1.5 +#
1.6 +# The author disclaims copyright to this source code. In place of
1.7 +# a legal notice, here is a blessing:
1.8 +#
1.9 +# May you do good and not evil.
1.10 +# May you find forgiveness for yourself and forgive others.
1.11 +# May you share freely, never taking more than you give.
1.12 +#
1.13 +#***********************************************************************
1.14 +# This file implements regression tests for SQLite library. Specifically,
1.15 +# it tests issues relating to firing an INSTEAD OF trigger on a VIEW
1.16 +# when one tries to UPDATE or DELETE from the view. Does the WHERE
1.17 +# clause of the UPDATE or DELETE statement get passed down correctly
1.18 +# into the query that manifests the view?
1.19 +#
1.20 +# Ticket #2938
1.21 +#
1.22 +
1.23 +set testdir [file dirname $argv0]
1.24 +source $testdir/tester.tcl
1.25 +ifcapable !trigger||!compound {
1.26 + finish_test
1.27 + return
1.28 +}
1.29 +
1.30 +# Create two table containing some sample data
1.31 +#
1.32 +do_test triggerA-1.1 {
1.33 + db eval {
1.34 + CREATE TABLE t1(x INTEGER PRIMARY KEY, y TEXT UNIQUE);
1.35 + CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c TEXT);
1.36 + }
1.37 + set i 1
1.38 + foreach word {one two three four five six seven eight nine ten} {
1.39 + set j [expr {$i*100 + [string length $word]}]
1.40 + db eval {
1.41 + INSERT INTO t1 VALUES($i,$word);
1.42 + INSERT INTO t2 VALUES(20-$i,$j,$word);
1.43 + }
1.44 + incr i
1.45 + }
1.46 + db eval {
1.47 + SELECT count(*) FROM t1 UNION ALL SELECT count(*) FROM t2;
1.48 + }
1.49 +} {10 10}
1.50 +
1.51 +# Create views of various forms against one or both of the two tables.
1.52 +#
1.53 +do_test triggerA-1.2 {
1.54 + db eval {
1.55 + CREATE VIEW v1 AS SELECT y, x FROM t1;
1.56 + SELECT * FROM v1 ORDER BY 1;
1.57 + }
1.58 +} {eight 8 five 5 four 4 nine 9 one 1 seven 7 six 6 ten 10 three 3 two 2}
1.59 +do_test triggerA-1.3 {
1.60 + db eval {
1.61 + CREATE VIEW v2 AS SELECT x, y FROM t1 WHERE y GLOB '*e*';
1.62 + SELECT * FROM v2 ORDER BY 1;
1.63 + }
1.64 +} {1 one 3 three 5 five 7 seven 8 eight 9 nine 10 ten}
1.65 +do_test triggerA-1.4 {
1.66 + db eval {
1.67 + CREATE VIEW v3 AS
1.68 + SELECT CAST(x AS TEXT) AS c1 FROM t1 UNION SELECT y FROM t1;
1.69 + SELECT * FROM v3 ORDER BY c1;
1.70 + }
1.71 +} {1 10 2 3 4 5 6 7 8 9 eight five four nine one seven six ten three two}
1.72 +do_test triggerA-1.5 {
1.73 + db eval {
1.74 + CREATE VIEW v4 AS
1.75 + SELECT CAST(x AS TEXT) AS c1 FROM t1
1.76 + UNION SELECT y FROM t1 WHERE x BETWEEN 3 and 5;
1.77 + SELECT * FROM v4 ORDER BY 1;
1.78 + }
1.79 +} {1 10 2 3 4 5 6 7 8 9 five four three}
1.80 +do_test triggerA-1.6 {
1.81 + db eval {
1.82 + CREATE VIEW v5 AS SELECT x, b FROM t1, t2 WHERE y=c;
1.83 + SELECT * FROM v5;
1.84 + }
1.85 +} {1 103 2 203 3 305 4 404 5 504 6 603 7 705 8 805 9 904 10 1003}
1.86 +
1.87 +# Create INSTEAD OF triggers on the views. Run UPDATE and DELETE statements
1.88 +# using those triggers. Verify correct operation.
1.89 +#
1.90 +do_test triggerA-2.1 {
1.91 + db eval {
1.92 + CREATE TABLE result2(a,b);
1.93 + CREATE TRIGGER r1d INSTEAD OF DELETE ON v1 BEGIN
1.94 + INSERT INTO result2(a,b) VALUES(old.y, old.x);
1.95 + END;
1.96 + DELETE FROM v1 WHERE x=5;
1.97 + SELECT * FROM result2;
1.98 + }
1.99 +} {five 5}
1.100 +do_test triggerA-2.2 {
1.101 + db eval {
1.102 + CREATE TABLE result4(a,b,c,d);
1.103 + CREATE TRIGGER r1u INSTEAD OF UPDATE ON v1 BEGIN
1.104 + INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
1.105 + END;
1.106 + UPDATE v1 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
1.107 + SELECT * FROM result4 ORDER BY a;
1.108 + }
1.109 +} {five 5 five-extra 5 four 4 four-extra 4 three 3 three-extra 3}
1.110 +
1.111 +
1.112 +do_test triggerA-2.3 {
1.113 + db eval {
1.114 + DELETE FROM result2;
1.115 + CREATE TRIGGER r2d INSTEAD OF DELETE ON v2 BEGIN
1.116 + INSERT INTO result2(a,b) VALUES(old.y, old.x);
1.117 + END;
1.118 + DELETE FROM v2 WHERE x=5;
1.119 + SELECT * FROM result2;
1.120 + }
1.121 +} {five 5}
1.122 +do_test triggerA-2.4 {
1.123 + db eval {
1.124 + DELETE FROM result4;
1.125 + CREATE TRIGGER r2u INSTEAD OF UPDATE ON v2 BEGIN
1.126 + INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
1.127 + END;
1.128 + UPDATE v2 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
1.129 + SELECT * FROM result4 ORDER BY a;
1.130 + }
1.131 +} {five 5 five-extra 5 three 3 three-extra 3}
1.132 +
1.133 +
1.134 +do_test triggerA-2.5 {
1.135 + db eval {
1.136 + CREATE TABLE result1(a);
1.137 + CREATE TRIGGER r3d INSTEAD OF DELETE ON v3 BEGIN
1.138 + INSERT INTO result1(a) VALUES(old.c1);
1.139 + END;
1.140 + DELETE FROM v3 WHERE c1 BETWEEN '8' AND 'eight';
1.141 + SELECT * FROM result1 ORDER BY a;
1.142 + }
1.143 +} {8 9 eight}
1.144 +do_test triggerA-2.6 {
1.145 + db eval {
1.146 + DELETE FROM result2;
1.147 + CREATE TRIGGER r3u INSTEAD OF UPDATE ON v3 BEGIN
1.148 + INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
1.149 + END;
1.150 + UPDATE v3 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
1.151 + SELECT * FROM result2 ORDER BY a;
1.152 + }
1.153 +} {8 8-extra 9 9-extra eight eight-extra}
1.154 +
1.155 +
1.156 +do_test triggerA-2.7 {
1.157 + db eval {
1.158 + DELETE FROM result1;
1.159 + CREATE TRIGGER r4d INSTEAD OF DELETE ON v4 BEGIN
1.160 + INSERT INTO result1(a) VALUES(old.c1);
1.161 + END;
1.162 + DELETE FROM v4 WHERE c1 BETWEEN '8' AND 'eight';
1.163 + SELECT * FROM result1 ORDER BY a;
1.164 + }
1.165 +} {8 9}
1.166 +do_test triggerA-2.8 {
1.167 + db eval {
1.168 + DELETE FROM result2;
1.169 + CREATE TRIGGER r4u INSTEAD OF UPDATE ON v4 BEGIN
1.170 + INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
1.171 + END;
1.172 + UPDATE v4 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
1.173 + SELECT * FROM result2 ORDER BY a;
1.174 + }
1.175 +} {8 8-extra 9 9-extra}
1.176 +
1.177 +
1.178 +do_test triggerA-2.9 {
1.179 + db eval {
1.180 + DELETE FROM result2;
1.181 + CREATE TRIGGER r5d INSTEAD OF DELETE ON v5 BEGIN
1.182 + INSERT INTO result2(a,b) VALUES(old.x, old.b);
1.183 + END;
1.184 + DELETE FROM v5 WHERE x=5;
1.185 + SELECT * FROM result2;
1.186 + }
1.187 +} {5 504}
1.188 +do_test triggerA-2.10 {
1.189 + db eval {
1.190 + DELETE FROM result4;
1.191 + CREATE TRIGGER r5u INSTEAD OF UPDATE ON v5 BEGIN
1.192 + INSERT INTO result4(a,b,c,d) VALUES(old.x, old.b, new.x, new.b);
1.193 + END;
1.194 + UPDATE v5 SET b = b+9900000 WHERE x BETWEEN 3 AND 5;
1.195 + SELECT * FROM result4 ORDER BY a;
1.196 + }
1.197 +} {3 305 3 9900305 4 404 4 9900404 5 504 5 9900504}
1.198 +
1.199 +# Only run the reamining tests if memory debugging is turned on.
1.200 +#
1.201 +ifcapable !memdebug {
1.202 + puts "Skipping triggerA malloc tests: not compiled with -DSQLITE_MEMDEBUG..."
1.203 + finish_test
1.204 + return
1.205 +}
1.206 +source $testdir/malloc_common.tcl
1.207 +
1.208 +# Save a copy of the current database configuration.
1.209 +#
1.210 +db close
1.211 +file delete -force test.db-triggerA
1.212 +file copy test.db test.db-triggerA
1.213 +sqlite3 db test.db
1.214 +
1.215 +# Run malloc tests on the INSTEAD OF trigger firing.
1.216 +#
1.217 +do_malloc_test triggerA-3 -tclprep {
1.218 + db close
1.219 + file delete -force test.db test.db-journal
1.220 + file copy -force test.db-triggerA test.db
1.221 + sqlite3 db test.db
1.222 + sqlite3_extended_result_codes db 1
1.223 + db eval {SELECT * FROM v5; -- warm up the cache}
1.224 +} -sqlbody {
1.225 + DELETE FROM v5 WHERE x=5;
1.226 + UPDATE v5 SET b=b+9900000 WHERE x BETWEEN 3 AND 5;
1.227 +}
1.228 +
1.229 +# Clean up the saved database copy.
1.230 +#
1.231 +file delete -force test.db-triggerA
1.232 +
1.233 +finish_test