wasCSharpSQLite – Blame information for rev

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 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 file delete -force test.db-triggerA
209 file copy 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 file delete -force test.db test.db-journal
217 file copy -force 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 file delete -force test.db-triggerA
229  
230 finish_test