Annotation of embedaddon/sqlite3/test/trigger9.test, revision 1.1.1.1
1.1 misho 1: # 2008 January 1
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 some compiler optimizations for SQL statements featuring
13: # triggers:
14: #
15: #
16: #
17:
18: # trigger9-1.* - Test that if there are no references to OLD.* cols, or a
19: # reference to only OLD.rowid, the data is not loaded.
20: #
21: # trigger9-2.* - Test that for NEW.* records populated by UPDATE
22: # statements, unused fields are populated with NULL values.
23: #
24: # trigger9-3.* - Test that the temporary tables used for OLD.* references
25: # in "INSTEAD OF" triggers have NULL values in unused
26: # fields.
27: #
28:
29: set testdir [file dirname $argv0]
30: source $testdir/tester.tcl
31: ifcapable {!trigger} {
32: finish_test
33: return
34: }
35:
36: proc has_rowdata {sql} {
37: expr {[lsearch [execsql "explain $sql"] RowData]>=0}
38: }
39:
40: do_test trigger9-1.1 {
41: execsql {
42: PRAGMA page_size = 1024;
43: CREATE TABLE t1(x, y, z);
44: INSERT INTO t1 VALUES('1', randstr(10000,10000), '2');
45: INSERT INTO t1 VALUES('2', randstr(10000,10000), '4');
46: INSERT INTO t1 VALUES('3', randstr(10000,10000), '6');
47: CREATE TABLE t2(x);
48: }
49: } {}
50:
51: do_test trigger9-1.2.1 {
52: execsql {
53: BEGIN;
54: CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN
55: INSERT INTO t2 VALUES(old.rowid);
56: END;
57: DELETE FROM t1;
58: SELECT * FROM t2;
59: }
60: } {1 2 3}
61: do_test trigger9-1.2.3 {
62: has_rowdata {DELETE FROM t1}
63: } 0
64: do_test trigger9-1.2.4 { execsql { ROLLBACK } } {}
65:
66: do_test trigger9-1.3.1 {
67: execsql {
68: BEGIN;
69: CREATE TRIGGER trig1 BEFORE DELETE ON t1 BEGIN
70: INSERT INTO t2 VALUES(old.x);
71: END;
72: DELETE FROM t1;
73: SELECT * FROM t2;
74: }
75: } {1 2 3}
76: do_test trigger9-1.3.2 {
77: has_rowdata {DELETE FROM t1}
78: } 0
79: do_test trigger9-1.3.3 { execsql { ROLLBACK } } {}
80:
81: do_test trigger9-1.4.1 {
82: execsql {
83: BEGIN;
84: CREATE TRIGGER trig1 BEFORE DELETE ON t1 WHEN old.x='1' BEGIN
85: INSERT INTO t2 VALUES(old.rowid);
86: END;
87: DELETE FROM t1;
88: SELECT * FROM t2;
89: }
90: } {1}
91: do_test trigger9-1.4.2 {
92: has_rowdata {DELETE FROM t1}
93: } 0
94: do_test trigger9-1.4.3 { execsql { ROLLBACK } } {}
95:
96: do_test trigger9-1.5.1 {
97: execsql {
98: BEGIN;
99: CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
100: INSERT INTO t2 VALUES(old.rowid);
101: END;
102: UPDATE t1 SET y = '';
103: SELECT * FROM t2;
104: }
105: } {1 2 3}
106: do_test trigger9-1.5.2 {
107: has_rowdata {UPDATE t1 SET y = ''}
108: } 0
109: do_test trigger9-1.5.3 { execsql { ROLLBACK } } {}
110:
111: do_test trigger9-1.6.1 {
112: execsql {
113: BEGIN;
114: CREATE TRIGGER trig1 BEFORE UPDATE ON t1 BEGIN
115: INSERT INTO t2 VALUES(old.x);
116: END;
117: UPDATE t1 SET y = '';
118: SELECT * FROM t2;
119: }
120: } {1 2 3}
121: do_test trigger9-1.6.2 {
122: has_rowdata {UPDATE t1 SET y = ''}
123: } 0
124: do_test trigger9-1.6.3 { execsql { ROLLBACK } } {}
125:
126: do_test trigger9-1.7.1 {
127: execsql {
128: BEGIN;
129: CREATE TRIGGER trig1 BEFORE UPDATE ON t1 WHEN old.x>='2' BEGIN
130: INSERT INTO t2 VALUES(old.x);
131: END;
132: UPDATE t1 SET y = '';
133: SELECT * FROM t2;
134: }
135: } {2 3}
136: do_test trigger9-1.7.2 {
137: has_rowdata {UPDATE t1 SET y = ''}
138: } 0
139: do_test trigger9-1.7.3 { execsql { ROLLBACK } } {}
140:
141: do_test trigger9-3.1 {
142: execsql {
143: CREATE TABLE t3(a, b);
144: INSERT INTO t3 VALUES(1, 'one');
145: INSERT INTO t3 VALUES(2, 'two');
146: INSERT INTO t3 VALUES(3, 'three');
147: }
148: } {}
149: do_test trigger9-3.2 {
150: execsql {
151: BEGIN;
152: CREATE VIEW v1 AS SELECT * FROM t3;
153: CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
154: INSERT INTO t2 VALUES(old.a);
155: END;
156: UPDATE v1 SET b = 'hello';
157: SELECT * FROM t2;
158: ROLLBACK;
159: }
160: } {1 2 3}
161: do_test trigger9-3.3 {
162: # In this test the 'c' column of the view is not required by
163: # the INSTEAD OF trigger, but the expression is reused internally as
164: # part of the view's WHERE clause. Check that this does not cause
165: # a problem.
166: #
167: execsql {
168: BEGIN;
169: CREATE VIEW v1 AS SELECT a, b AS c FROM t3 WHERE c > 'one';
170: CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
171: INSERT INTO t2 VALUES(old.a);
172: END;
173: UPDATE v1 SET c = 'hello';
174: SELECT * FROM t2;
175: ROLLBACK;
176: }
177: } {2 3}
178: do_test trigger9-3.4 {
179: execsql {
180: BEGIN;
181: INSERT INTO t3 VALUES(3, 'three');
182: INSERT INTO t3 VALUES(3, 'four');
183: CREATE VIEW v1 AS SELECT DISTINCT a, b FROM t3;
184: CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
185: INSERT INTO t2 VALUES(old.a);
186: END;
187: UPDATE v1 SET b = 'hello';
188: SELECT * FROM t2;
189: ROLLBACK;
190: }
191: } {1 2 3 3}
192:
193: ifcapable compound {
194: do_test trigger9-3.5 {
195: execsql {
196: BEGIN;
197: INSERT INTO t3 VALUES(1, 'uno');
198: CREATE VIEW v1 AS SELECT a, b FROM t3 EXCEPT SELECT 1, 'one';
199: CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
200: INSERT INTO t2 VALUES(old.a);
201: END;
202: UPDATE v1 SET b = 'hello';
203: SELECT * FROM t2;
204: ROLLBACK;
205: }
206: } {1 2 3}
207: do_test trigger9-3.6 {
208: execsql {
209: BEGIN;
210: INSERT INTO t3 VALUES(1, 'zero');
211: CREATE VIEW v1 AS
212: SELECT sum(a) AS a, max(b) AS b FROM t3 GROUP BY t3.a HAVING b>'two';
213: CREATE TRIGGER trig1 INSTEAD OF UPDATE ON v1 BEGIN
214: INSERT INTO t2 VALUES(old.a);
215: END;
216: UPDATE v1 SET b = 'hello';
217: SELECT * FROM t2;
218: ROLLBACK;
219: }
220: } {2}
221: }
222:
223: finish_test
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to trigger9.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