1: # 2008 February 12
2: #
3: # The author disclaims copyright to this source code. In place of
4: # a legal notice, here is a blessing:
5: #
6: # May you do good and not evil.
7: # May you find forgiveness for yourself and forgive others.
8: # May you share freely, never taking more than you give.
9: #
10: #***********************************************************************
11: # This file implements regression tests for SQLite library. Specifically,
12: # it tests issues relating to firing an INSTEAD OF trigger on a VIEW
13: # when one tries to UPDATE or DELETE from the view. Does the WHERE
14: # clause of the UPDATE or DELETE statement get passed down correctly
15: # into the query that manifests the view?
16: #
17: # Ticket #2938
18: #
19:
20: set testdir [file dirname $argv0]
21: source $testdir/tester.tcl
22: ifcapable !trigger||!compound {
23: finish_test
24: return
25: }
26:
27: # Create two table containing some sample data
28: #
29: do_test triggerA-1.1 {
30: db eval {
31: CREATE TABLE t1(x INTEGER PRIMARY KEY, y TEXT UNIQUE);
32: CREATE TABLE t2(a INTEGER PRIMARY KEY, b INTEGER UNIQUE, c TEXT);
33: }
34: set i 1
35: foreach word {one two three four five six seven eight nine ten} {
36: set j [expr {$i*100 + [string length $word]}]
37: db eval {
38: INSERT INTO t1 VALUES($i,$word);
39: INSERT INTO t2 VALUES(20-$i,$j,$word);
40: }
41: incr i
42: }
43: db eval {
44: SELECT count(*) FROM t1 UNION ALL SELECT count(*) FROM t2;
45: }
46: } {10 10}
47:
48: # Create views of various forms against one or both of the two tables.
49: #
50: do_test triggerA-1.2 {
51: db eval {
52: CREATE VIEW v1 AS SELECT y, x FROM t1;
53: SELECT * FROM v1 ORDER BY 1;
54: }
55: } {eight 8 five 5 four 4 nine 9 one 1 seven 7 six 6 ten 10 three 3 two 2}
56: do_test triggerA-1.3 {
57: db eval {
58: CREATE VIEW v2 AS SELECT x, y FROM t1 WHERE y GLOB '*e*';
59: SELECT * FROM v2 ORDER BY 1;
60: }
61: } {1 one 3 three 5 five 7 seven 8 eight 9 nine 10 ten}
62: do_test triggerA-1.4 {
63: db eval {
64: CREATE VIEW v3 AS
65: SELECT CAST(x AS TEXT) AS c1 FROM t1 UNION SELECT y FROM t1;
66: SELECT * FROM v3 ORDER BY c1;
67: }
68: } {1 10 2 3 4 5 6 7 8 9 eight five four nine one seven six ten three two}
69: do_test triggerA-1.5 {
70: db eval {
71: CREATE VIEW v4 AS
72: SELECT CAST(x AS TEXT) AS c1 FROM t1
73: UNION SELECT y FROM t1 WHERE x BETWEEN 3 and 5;
74: SELECT * FROM v4 ORDER BY 1;
75: }
76: } {1 10 2 3 4 5 6 7 8 9 five four three}
77: do_test triggerA-1.6 {
78: db eval {
79: CREATE VIEW v5 AS SELECT x, b FROM t1, t2 WHERE y=c;
80: SELECT * FROM v5 ORDER BY x DESC;
81: }
82: } {10 1003 9 904 8 805 7 705 6 603 5 504 4 404 3 305 2 203 1 103}
83:
84: # Create INSTEAD OF triggers on the views. Run UPDATE and DELETE statements
85: # using those triggers. Verify correct operation.
86: #
87: do_test triggerA-2.1 {
88: db eval {
89: CREATE TABLE result2(a,b);
90: CREATE TRIGGER r1d INSTEAD OF DELETE ON v1 BEGIN
91: INSERT INTO result2(a,b) VALUES(old.y, old.x);
92: END;
93: DELETE FROM v1 WHERE x=5;
94: SELECT * FROM result2;
95: }
96: } {five 5}
97: do_test triggerA-2.2 {
98: db eval {
99: CREATE TABLE result4(a,b,c,d);
100: CREATE TRIGGER r1u INSTEAD OF UPDATE ON v1 BEGIN
101: INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
102: END;
103: UPDATE v1 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
104: SELECT * FROM result4 ORDER BY a;
105: }
106: } {five 5 five-extra 5 four 4 four-extra 4 three 3 three-extra 3}
107:
108:
109: do_test triggerA-2.3 {
110: db eval {
111: DELETE FROM result2;
112: CREATE TRIGGER r2d INSTEAD OF DELETE ON v2 BEGIN
113: INSERT INTO result2(a,b) VALUES(old.y, old.x);
114: END;
115: DELETE FROM v2 WHERE x=5;
116: SELECT * FROM result2;
117: }
118: } {five 5}
119: do_test triggerA-2.4 {
120: db eval {
121: DELETE FROM result4;
122: CREATE TRIGGER r2u INSTEAD OF UPDATE ON v2 BEGIN
123: INSERT INTO result4(a,b,c,d) VALUES(old.y, old.x, new.y, new.x);
124: END;
125: UPDATE v2 SET y=y||'-extra' WHERE x BETWEEN 3 AND 5;
126: SELECT * FROM result4 ORDER BY a;
127: }
128: } {five 5 five-extra 5 three 3 three-extra 3}
129:
130:
131: do_test triggerA-2.5 {
132: db eval {
133: CREATE TABLE result1(a);
134: CREATE TRIGGER r3d INSTEAD OF DELETE ON v3 BEGIN
135: INSERT INTO result1(a) VALUES(old.c1);
136: END;
137: DELETE FROM v3 WHERE c1 BETWEEN '8' AND 'eight';
138: SELECT * FROM result1 ORDER BY a;
139: }
140: } {8 9 eight}
141: do_test triggerA-2.6 {
142: db eval {
143: DELETE FROM result2;
144: CREATE TRIGGER r3u INSTEAD OF UPDATE ON v3 BEGIN
145: INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
146: END;
147: UPDATE v3 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
148: SELECT * FROM result2 ORDER BY a;
149: }
150: } {8 8-extra 9 9-extra eight eight-extra}
151:
152:
153: do_test triggerA-2.7 {
154: db eval {
155: DELETE FROM result1;
156: CREATE TRIGGER r4d INSTEAD OF DELETE ON v4 BEGIN
157: INSERT INTO result1(a) VALUES(old.c1);
158: END;
159: DELETE FROM v4 WHERE c1 BETWEEN '8' AND 'eight';
160: SELECT * FROM result1 ORDER BY a;
161: }
162: } {8 9}
163: do_test triggerA-2.8 {
164: db eval {
165: DELETE FROM result2;
166: CREATE TRIGGER r4u INSTEAD OF UPDATE ON v4 BEGIN
167: INSERT INTO result2(a,b) VALUES(old.c1, new.c1);
168: END;
169: UPDATE v4 SET c1 = c1 || '-extra' WHERE c1 BETWEEN '8' and 'eight';
170: SELECT * FROM result2 ORDER BY a;
171: }
172: } {8 8-extra 9 9-extra}
173:
174:
175: do_test triggerA-2.9 {
176: db eval {
177: DELETE FROM result2;
178: CREATE TRIGGER r5d INSTEAD OF DELETE ON v5 BEGIN
179: INSERT INTO result2(a,b) VALUES(old.x, old.b);
180: END;
181: DELETE FROM v5 WHERE x=5;
182: SELECT * FROM result2;
183: }
184: } {5 504}
185: do_test triggerA-2.10 {
186: db eval {
187: DELETE FROM result4;
188: CREATE TRIGGER r5u INSTEAD OF UPDATE ON v5 BEGIN
189: INSERT INTO result4(a,b,c,d) VALUES(old.x, old.b, new.x, new.b);
190: END;
191: UPDATE v5 SET b = b+9900000 WHERE x BETWEEN 3 AND 5;
192: SELECT * FROM result4 ORDER BY a;
193: }
194: } {3 305 3 9900305 4 404 4 9900404 5 504 5 9900504}
195:
196: # Only run the reamining tests if memory debugging is turned on.
197: #
198: ifcapable !memdebug {
199: puts "Skipping triggerA malloc tests: not compiled with -DSQLITE_MEMDEBUG..."
200: finish_test
201: return
202: }
203: source $testdir/malloc_common.tcl
204:
205: # Save a copy of the current database configuration.
206: #
207: db close
208: forcedelete test.db-triggerA
209: copy_file test.db test.db-triggerA
210: sqlite3 db test.db
211:
212: # Run malloc tests on the INSTEAD OF trigger firing.
213: #
214: do_malloc_test triggerA-3 -tclprep {
215: db close
216: forcedelete test.db test.db-journal
217: forcecopy test.db-triggerA test.db
218: sqlite3 db test.db
219: sqlite3_extended_result_codes db 1
220: db eval {SELECT * FROM v5; -- warm up the cache}
221: } -sqlbody {
222: DELETE FROM v5 WHERE x=5;
223: UPDATE v5 SET b=b+9900000 WHERE x BETWEEN 3 AND 5;
224: }
225:
226: # Clean up the saved database copy.
227: #
228: forcedelete test.db-triggerA
229:
230: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to triggerA.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test