wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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