wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 1 | # 2004 Jan 14 |
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 TCL interface to the |
||
12 | # SQLite library. |
||
13 | # |
||
14 | # The focus of the tests in this file is the following interface: |
||
15 | # |
||
16 | # sqlite_commit_hook (tests hook-1..hook-3 inclusive) |
||
17 | # sqlite_update_hook (tests hook-4-*) |
||
18 | # sqlite_rollback_hook (tests hook-5.*) |
||
19 | # |
||
20 | # $Id: hook.test,v 1.15 2009/04/07 14:14:23 danielk1977 Exp $ |
||
21 | |||
22 | set testdir [file dirname $argv0] |
||
23 | source $testdir/tester.tcl |
||
24 | |||
25 | do_test hook-1.2 { |
||
26 | db commit_hook |
||
27 | } {} |
||
28 | |||
29 | |||
30 | do_test hook-3.1 { |
||
31 | set commit_cnt 0 |
||
32 | proc commit_hook {} { |
||
33 | incr ::commit_cnt |
||
34 | return 0 |
||
35 | } |
||
36 | db commit_hook ::commit_hook |
||
37 | db commit_hook |
||
38 | } {::commit_hook} |
||
39 | do_test hook-3.2 { |
||
40 | set commit_cnt |
||
41 | } {0} |
||
42 | do_test hook-3.3 { |
||
43 | execsql { |
||
44 | CREATE TABLE t2(a,b); |
||
45 | } |
||
46 | set commit_cnt |
||
47 | } {1} |
||
48 | do_test hook-3.4 { |
||
49 | execsql { |
||
50 | INSERT INTO t2 VALUES(1,2); |
||
51 | INSERT INTO t2 SELECT a+1, b+1 FROM t2; |
||
52 | INSERT INTO t2 SELECT a+2, b+2 FROM t2; |
||
53 | } |
||
54 | set commit_cnt |
||
55 | } {4} |
||
56 | do_test hook-3.5 { |
||
57 | set commit_cnt {} |
||
58 | proc commit_hook {} { |
||
59 | set ::commit_cnt [execsql {SELECT * FROM t2}] |
||
60 | return 0 |
||
61 | } |
||
62 | execsql { |
||
63 | INSERT INTO t2 VALUES(5,6); |
||
64 | } |
||
65 | set commit_cnt |
||
66 | } {1 2 2 3 3 4 4 5 5 6} |
||
67 | do_test hook-3.6 { |
||
68 | set commit_cnt {} |
||
69 | proc commit_hook {} { |
||
70 | set ::commit_cnt [execsql {SELECT * FROM t2}] |
||
71 | return 1 |
||
72 | } |
||
73 | catchsql { |
||
74 | INSERT INTO t2 VALUES(6,7); |
||
75 | } |
||
76 | } {1 {constraint failed}} |
||
77 | do_test hook-3.7 { |
||
78 | set ::commit_cnt |
||
79 | } {1 2 2 3 3 4 4 5 5 6 6 7} |
||
80 | do_test hook-3.8 { |
||
81 | execsql {SELECT * FROM t2} |
||
82 | } {1 2 2 3 3 4 4 5 5 6} |
||
83 | |||
84 | # Test turnning off the commit hook |
||
85 | # |
||
86 | do_test hook-3.9 { |
||
87 | db commit_hook {} |
||
88 | set ::commit_cnt {} |
||
89 | execsql { |
||
90 | INSERT INTO t2 VALUES(7,8); |
||
91 | } |
||
92 | set ::commit_cnt |
||
93 | } {} |
||
94 | |||
95 | # Ticket #3564. |
||
96 | # |
||
97 | do_test hook-3.10 { |
||
98 | file delete -force test2.db test2.db-journal |
||
99 | sqlite3 db2 test2.db |
||
100 | proc commit_hook {} { |
||
101 | set y [db2 one {SELECT y FROM t3 WHERE y>10}] |
||
102 | return [expr {$y>10}] |
||
103 | } |
||
104 | db2 eval {CREATE TABLE t3(x,y)} |
||
105 | db2 commit_hook commit_hook |
||
106 | catchsql {INSERT INTO t3 VALUES(1,2)} db2 |
||
107 | catchsql {INSERT INTO t3 VALUES(11,12)} db2 |
||
108 | catchsql {INSERT INTO t3 VALUES(3,4)} db2 |
||
109 | db2 eval { |
||
110 | SELECT * FROM t3 ORDER BY x; |
||
111 | } |
||
112 | } {1 2 3 4} |
||
113 | db2 close |
||
114 | |||
115 | |||
116 | #---------------------------------------------------------------------------- |
||
117 | # Tests for the update-hook. |
||
118 | # |
||
119 | # 4.1.* - Very simple tests. Test that the update hook is invoked correctly |
||
120 | # for INSERT, DELETE and UPDATE statements, including DELETE |
||
121 | # statements with no WHERE clause. |
||
122 | # 4.2.* - Check that the update-hook is invoked for rows modified by trigger |
||
123 | # bodies. Also that the database name is correctly reported when |
||
124 | # an attached database is modified. |
||
125 | # 4.3.* - Do some sorting, grouping, compound queries, population and |
||
126 | # depopulation of indices, to make sure the update-hook is not |
||
127 | # invoked incorrectly. |
||
128 | # |
||
129 | |||
130 | # Simple tests |
||
131 | do_test hook-4.1.1 { |
||
132 | catchsql { |
||
133 | DROP TABLE t1; |
||
134 | } |
||
135 | execsql { |
||
136 | CREATE TABLE t1(a INTEGER PRIMARY KEY, b); |
||
137 | INSERT INTO t1 VALUES(1, 'one'); |
||
138 | INSERT INTO t1 VALUES(2, 'two'); |
||
139 | INSERT INTO t1 VALUES(3, 'three'); |
||
140 | } |
||
141 | db update_hook [list lappend ::update_hook] |
||
142 | } {} |
||
143 | do_test hook-4.1.2 { |
||
144 | execsql { |
||
145 | INSERT INTO t1 VALUES(4, 'four'); |
||
146 | DELETE FROM t1 WHERE b = 'two'; |
||
147 | UPDATE t1 SET b = '' WHERE a = 1 OR a = 3; |
||
148 | DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now) |
||
149 | } |
||
150 | set ::update_hook |
||
151 | } [list \ |
||
152 | INSERT main t1 4 \ |
||
153 | DELETE main t1 2 \ |
||
154 | UPDATE main t1 1 \ |
||
155 | UPDATE main t1 3 \ |
||
156 | DELETE main t1 1 \ |
||
157 | DELETE main t1 3 \ |
||
158 | DELETE main t1 4 \ |
||
159 | ] |
||
160 | |||
161 | ifcapable trigger { |
||
162 | # Update hook is not invoked for changes to sqlite_master |
||
163 | # |
||
164 | do_test hook-4.1.3 { |
||
165 | set ::update_hook {} |
||
166 | execsql { |
||
167 | CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END; |
||
168 | } |
||
169 | set ::update_hook |
||
170 | } {} |
||
171 | do_test hook-4.1.4 { |
||
172 | set ::update_hook {} |
||
173 | execsql { |
||
174 | DROP TRIGGER r1; |
||
175 | } |
||
176 | set ::update_hook |
||
177 | } {} |
||
178 | |||
179 | set ::update_hook {} |
||
180 | do_test hook-4.2.1 { |
||
181 | catchsql { |
||
182 | DROP TABLE t2; |
||
183 | } |
||
184 | execsql { |
||
185 | CREATE TABLE t2(c INTEGER PRIMARY KEY, d); |
||
186 | CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN |
||
187 | INSERT INTO t2 VALUES(new.a, new.b); |
||
188 | UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c; |
||
189 | DELETE FROM t2 WHERE new.a = c; |
||
190 | END; |
||
191 | } |
||
192 | } {} |
||
193 | do_test hook-4.2.2 { |
||
194 | execsql { |
||
195 | INSERT INTO t1 VALUES(1, 'one'); |
||
196 | INSERT INTO t1 VALUES(2, 'two'); |
||
197 | } |
||
198 | set ::update_hook |
||
199 | } [list \ |
||
200 | INSERT main t1 1 \ |
||
201 | INSERT main t2 1 \ |
||
202 | UPDATE main t2 1 \ |
||
203 | DELETE main t2 1 \ |
||
204 | INSERT main t1 2 \ |
||
205 | INSERT main t2 2 \ |
||
206 | UPDATE main t2 2 \ |
||
207 | DELETE main t2 2 \ |
||
208 | ] |
||
209 | } else { |
||
210 | execsql { |
||
211 | INSERT INTO t1 VALUES(1, 'one'); |
||
212 | INSERT INTO t1 VALUES(2, 'two'); |
||
213 | } |
||
214 | } |
||
215 | |||
216 | # Update-hook + ATTACH |
||
217 | set ::update_hook {} |
||
218 | ifcapable attach { |
||
219 | do_test hook-4.2.3 { |
||
220 | file delete -force test2.db |
||
221 | execsql { |
||
222 | ATTACH 'test2.db' AS aux; |
||
223 | CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b); |
||
224 | INSERT INTO aux.t3 SELECT * FROM t1; |
||
225 | UPDATE t3 SET b = 'two or so' WHERE a = 2; |
||
226 | DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now) |
||
227 | } |
||
228 | set ::update_hook |
||
229 | } [list \ |
||
230 | INSERT aux t3 1 \ |
||
231 | INSERT aux t3 2 \ |
||
232 | UPDATE aux t3 2 \ |
||
233 | DELETE aux t3 1 \ |
||
234 | DELETE aux t3 2 \ |
||
235 | ] |
||
236 | } |
||
237 | |||
238 | ifcapable trigger { |
||
239 | execsql { |
||
240 | DROP TRIGGER t1_trigger; |
||
241 | } |
||
242 | } |
||
243 | |||
244 | # Test that other vdbe operations involving btree structures do not |
||
245 | # incorrectly invoke the update-hook. |
||
246 | set ::update_hook {} |
||
247 | do_test hook-4.3.1 { |
||
248 | execsql { |
||
249 | CREATE INDEX t1_i ON t1(b); |
||
250 | INSERT INTO t1 VALUES(3, 'three'); |
||
251 | UPDATE t1 SET b = ''; |
||
252 | DELETE FROM t1 WHERE a > 1; |
||
253 | } |
||
254 | set ::update_hook |
||
255 | } [list \ |
||
256 | INSERT main t1 3 \ |
||
257 | UPDATE main t1 1 \ |
||
258 | UPDATE main t1 2 \ |
||
259 | UPDATE main t1 3 \ |
||
260 | DELETE main t1 2 \ |
||
261 | DELETE main t1 3 \ |
||
262 | ] |
||
263 | set ::update_hook {} |
||
264 | ifcapable compound&&attach { |
||
265 | do_test hook-4.3.2 { |
||
266 | execsql { |
||
267 | SELECT * FROM t1 UNION SELECT * FROM t3; |
||
268 | SELECT * FROM t1 UNION ALL SELECT * FROM t3; |
||
269 | SELECT * FROM t1 INTERSECT SELECT * FROM t3; |
||
270 | SELECT * FROM t1 EXCEPT SELECT * FROM t3; |
||
271 | SELECT * FROM t1 ORDER BY b; |
||
272 | SELECT * FROM t1 GROUP BY b; |
||
273 | } |
||
274 | set ::update_hook |
||
275 | } [list] |
||
276 | } |
||
277 | |||
278 | do_test hook-4.4 { |
||
279 | execsql { |
||
280 | CREATE TABLE t4(a UNIQUE, b); |
||
281 | INSERT INTO t4 VALUES(1, 'a'); |
||
282 | INSERT INTO t4 VALUES(2, 'b'); |
||
283 | } |
||
284 | set ::update_hook [list] |
||
285 | execsql { |
||
286 | REPLACE INTO t4 VALUES(1, 'c'); |
||
287 | } |
||
288 | set ::update_hook |
||
289 | } [list INSERT main t4 3 ] |
||
290 | do_execsql_test hook-4.4.1 { |
||
291 | SELECT * FROM t4 ORDER BY a; |
||
292 | } {1 c 2 b} |
||
293 | do_test hook-4.4.2 { |
||
294 | set ::update_hook [list] |
||
295 | execsql { |
||
296 | PRAGMA recursive_triggers = on; |
||
297 | REPLACE INTO t4 VALUES(1, 'd'); |
||
298 | } |
||
299 | set ::update_hook |
||
300 | } [list INSERT main t4 4 ] |
||
301 | do_execsql_test hook-4.4.3 { |
||
302 | SELECT * FROM t4 ORDER BY a; |
||
303 | } {1 d 2 b} |
||
304 | |||
305 | db update_hook {} |
||
306 | # |
||
307 | #---------------------------------------------------------------------------- |
||
308 | |||
309 | #---------------------------------------------------------------------------- |
||
310 | # Test the rollback-hook. The rollback-hook is a bit more complicated than |
||
311 | # either the commit or update hooks because a rollback can happen |
||
312 | # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or |
||
313 | # error condition). |
||
314 | # |
||
315 | # hook-5.1.* - Test explicit rollbacks. |
||
316 | # hook-5.2.* - Test implicit rollbacks caused by constraint failure. |
||
317 | # |
||
318 | # hook-5.3.* - Test implicit rollbacks caused by IO errors. |
||
319 | # hook-5.4.* - Test implicit rollbacks caused by malloc() failure. |
||
320 | # hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook |
||
321 | # not be called for these? |
||
322 | # |
||
323 | |||
324 | do_test hook-5.0 { |
||
325 | # Configure the rollback hook to increment global variable |
||
326 | # $::rollback_hook each time it is invoked. |
||
327 | set ::rollback_hook 0 |
||
328 | db rollback_hook [list incr ::rollback_hook] |
||
329 | } {} |
||
330 | |||
331 | # Test explicit rollbacks. Not much can really go wrong here. |
||
332 | # |
||
333 | do_test hook-5.1.1 { |
||
334 | set ::rollback_hook 0 |
||
335 | execsql { |
||
336 | BEGIN; |
||
337 | ROLLBACK; |
||
338 | } |
||
339 | set ::rollback_hook |
||
340 | } {1} |
||
341 | |||
342 | # Test implicit rollbacks caused by constraints. |
||
343 | # |
||
344 | do_test hook-5.2.1 { |
||
345 | set ::rollback_hook 0 |
||
346 | catchsql { |
||
347 | DROP TABLE t1; |
||
348 | CREATE TABLE t1(a PRIMARY KEY, b); |
||
349 | INSERT INTO t1 VALUES('one', 'I'); |
||
350 | INSERT INTO t1 VALUES('one', 'I'); |
||
351 | } |
||
352 | set ::rollback_hook |
||
353 | } {1} |
||
354 | do_test hook-5.2.2 { |
||
355 | # Check that the INSERT transaction above really was rolled back. |
||
356 | execsql { |
||
357 | SELECT count(*) FROM t1; |
||
358 | } |
||
359 | } {1} |
||
360 | |||
361 | # |
||
362 | # End rollback-hook testing. |
||
363 | #---------------------------------------------------------------------------- |
||
364 | |||
365 | #---------------------------------------------------------------------------- |
||
366 | # Test that if a commit-hook returns non-zero (causing a rollback), the |
||
367 | # rollback-hook is invoked. |
||
368 | # |
||
369 | proc commit_hook {} { |
||
370 | lappend ::hooks COMMIT |
||
371 | return 1 |
||
372 | } |
||
373 | proc rollback_hook {} { |
||
374 | lappend ::hooks ROLLBACK |
||
375 | } |
||
376 | do_test hook-6.1 { |
||
377 | set ::hooks [list] |
||
378 | db commit_hook commit_hook |
||
379 | db rollback_hook rollback_hook |
||
380 | catchsql { |
||
381 | BEGIN; |
||
382 | INSERT INTO t1 VALUES('two', 'II'); |
||
383 | COMMIT; |
||
384 | } |
||
385 | execsql { SELECT * FROM t1 } |
||
386 | } {one I} |
||
387 | do_test hook-6.2 { |
||
388 | set ::hooks |
||
389 | } {COMMIT ROLLBACK} |
||
390 | unset ::hooks |
||
391 | |||
392 | finish_test |