| blob | de6fbdd254299abd8f6343f0bc52a57ff5e10686 |
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 $
22 set testdir [file dirname $argv0]
23 source $testdir/tester.tcl
25 do_test hook-1.2 {
26 db commit_hook
27 } {}
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 verify_ex_errcode hook-3.6b SQLITE_CONSTRAINT_COMMITHOOK
78 do_test hook-3.7 {
79 set ::commit_cnt
80 } {1 2 2 3 3 4 4 5 5 6 6 7}
81 do_test hook-3.8 {
82 execsql {SELECT * FROM t2}
83 } {1 2 2 3 3 4 4 5 5 6}
85 # Test turnning off the commit hook
86 #
87 do_test hook-3.9 {
88 db commit_hook {}
89 set ::commit_cnt {}
90 execsql {
91 INSERT INTO t2 VALUES(7,8);
92 }
93 set ::commit_cnt
94 } {}
96 # Ticket #3564.
97 #
98 do_test hook-3.10 {
99 forcedelete test2.db test2.db-journal
100 sqlite3 db2 test2.db
101 proc commit_hook {} {
102 set y [db2 one {SELECT y FROM t3 WHERE y>10}]
103 return [expr {$y>10}]
104 }
105 db2 eval {CREATE TABLE t3(x,y)}
106 db2 commit_hook commit_hook
107 catchsql {INSERT INTO t3 VALUES(1,2)} db2
108 catchsql {INSERT INTO t3 VALUES(11,12)} db2
109 catchsql {INSERT INTO t3 VALUES(3,4)} db2
110 db2 eval {
111 SELECT * FROM t3 ORDER BY x;
112 }
113 } {1 2 3 4}
114 db2 close
117 #----------------------------------------------------------------------------
118 # Tests for the update-hook.
119 #
120 # 4.1.* - Very simple tests. Test that the update hook is invoked correctly
121 # for INSERT, DELETE and UPDATE statements, including DELETE
122 # statements with no WHERE clause.
123 # 4.2.* - Check that the update-hook is invoked for rows modified by trigger
124 # bodies. Also that the database name is correctly reported when
125 # an attached database is modified.
126 # 4.3.* - Do some sorting, grouping, compound queries, population and
127 # depopulation of indices, to make sure the update-hook is not
128 # invoked incorrectly.
129 #
130 # EVIDENCE-OF: R-21999-45122 The sqlite3_update_hook() interface
131 # registers a callback function with the database connection identified
132 # by the first argument to be invoked whenever a row is updated,
133 # inserted or deleted in a rowid table.
135 # Simple tests
136 do_test hook-4.1.1a {
137 catchsql {
138 DROP TABLE t1;
139 }
140 unset -nocomplain ::update_hook
141 set ::update_hook {}
142 db update_hook [list lappend ::update_hook]
143 #
144 # EVIDENCE-OF: R-52223-27275 The update hook is not invoked when
145 # internal system tables are modified (i.e. sqlite_master and
146 # sqlite_sequence).
147 #
148 execsql {
149 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
150 CREATE TABLE t1w(a INT PRIMARY KEY, b) WITHOUT ROWID;
151 }
152 set ::update_hook
153 } {}
154 do_test hook-4.1.1b {
155 execsql {
156 INSERT INTO t1 VALUES(1, 'one');
157 INSERT INTO t1 VALUES(2, 'two');
158 INSERT INTO t1 VALUES(3, 'three');
159 INSERT INTO t1w SELECT * FROM t1;
160 }
161 } {}
163 # EVIDENCE-OF: R-15506-57666 The second callback argument is one of
164 # SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE, depending on the
165 # operation that caused the callback to be invoked.
166 #
167 # EVIDENCE-OF: R-29213-61195 The third and fourth arguments to the
168 # callback contain pointers to the database and table name containing
169 # the affected row.
170 #
171 # EVIDENCE-OF: R-30809-57812 The final callback parameter is the rowid
172 # of the row.
173 #
174 do_test hook-4.1.2 {
175 set ::update_hook {}
176 execsql {
177 INSERT INTO t1 VALUES(4, 'four');
178 DELETE FROM t1 WHERE b = 'two';
179 UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
180 DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
181 }
182 set ::update_hook
183 } [list \
184 INSERT main t1 4 \
185 DELETE main t1 2 \
186 UPDATE main t1 1 \
187 UPDATE main t1 3 \
188 DELETE main t1 1 \
189 DELETE main t1 3 \
190 DELETE main t1 4 \
191 ]
193 # EVIDENCE-OF: R-61808-14344 The sqlite3_update_hook() interface does
194 # not fire callbacks for changes to a WITHOUT ROWID table.
195 #
196 # EVIDENCE-OF: R-33257-44249 The update hook is not invoked when WITHOUT
197 # ROWID tables are modified.
198 #
199 do_test hook-4.1.2w {
200 set ::update_hook {}
201 execsql {
202 INSERT INTO t1w VALUES(4, 'four');
203 DELETE FROM t1w WHERE b = 'two';
204 UPDATE t1w SET b = '' WHERE a = 1 OR a = 3;
205 DELETE FROM t1w WHERE 1; -- Avoid the truncate optimization (for now)
206 }
207 set ::update_hook
208 } {}
210 ifcapable trigger {
211 # Update hook is not invoked for changes to sqlite_master
212 #
213 do_test hook-4.1.3 {
214 set ::update_hook {}
215 execsql {
216 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;
217 }
218 set ::update_hook
219 } {}
220 do_test hook-4.1.4 {
221 set ::update_hook {}
222 execsql {
223 DROP TRIGGER r1;
224 }
225 set ::update_hook
226 } {}
228 set ::update_hook {}
229 do_test hook-4.2.1 {
230 catchsql {
231 DROP TABLE t2;
232 }
233 execsql {
234 CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
235 CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN
236 INSERT INTO t2 VALUES(new.a, new.b);
237 UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c;
238 DELETE FROM t2 WHERE new.a = c;
239 END;
240 }
241 } {}
242 do_test hook-4.2.2 {
243 execsql {
244 INSERT INTO t1 VALUES(1, 'one');
245 INSERT INTO t1 VALUES(2, 'two');
246 }
247 set ::update_hook
248 } [list \
249 INSERT main t1 1 \
250 INSERT main t2 1 \
251 UPDATE main t2 1 \
252 DELETE main t2 1 \
253 INSERT main t1 2 \
254 INSERT main t2 2 \
255 UPDATE main t2 2 \
256 DELETE main t2 2 \
257 ]
258 } else {
259 execsql {
260 INSERT INTO t1 VALUES(1, 'one');
261 INSERT INTO t1 VALUES(2, 'two');
262 }
263 }
265 # Update-hook + ATTACH
266 set ::update_hook {}
267 ifcapable attach {
268 do_test hook-4.2.3 {
269 forcedelete test2.db
270 execsql {
271 ATTACH 'test2.db' AS aux;
272 CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);
273 INSERT INTO aux.t3 SELECT * FROM t1;
274 UPDATE t3 SET b = 'two or so' WHERE a = 2;
275 DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)
276 }
277 set ::update_hook
278 } [list \
279 INSERT aux t3 1 \
280 INSERT aux t3 2 \
281 UPDATE aux t3 2 \
282 DELETE aux t3 1 \
283 DELETE aux t3 2 \
284 ]
285 }
287 ifcapable trigger {
288 execsql {
289 DROP TRIGGER t1_trigger;
290 }
291 }
293 # Test that other vdbe operations involving btree structures do not
294 # incorrectly invoke the update-hook.
295 set ::update_hook {}
296 do_test hook-4.3.1 {
297 execsql {
298 CREATE INDEX t1_i ON t1(b);
299 INSERT INTO t1 VALUES(3, 'three');
300 UPDATE t1 SET b = '';
301 DELETE FROM t1 WHERE a > 1;
302 }
303 set ::update_hook
304 } [list \
305 INSERT main t1 3 \
306 UPDATE main t1 1 \
307 UPDATE main t1 2 \
308 UPDATE main t1 3 \
309 DELETE main t1 2 \
310 DELETE main t1 3 \
311 ]
312 set ::update_hook {}
313 ifcapable compound&&attach {
314 do_test hook-4.3.2 {
315 execsql {
316 SELECT * FROM t1 UNION SELECT * FROM t3;
317 SELECT * FROM t1 UNION ALL SELECT * FROM t3;
318 SELECT * FROM t1 INTERSECT SELECT * FROM t3;
319 SELECT * FROM t1 EXCEPT SELECT * FROM t3;
320 SELECT * FROM t1 ORDER BY b;
321 SELECT * FROM t1 GROUP BY b;
322 }
323 set ::update_hook
324 } [list]
325 }
327 do_test hook-4.4 {
328 execsql {
329 CREATE TABLE t4(a UNIQUE, b);
330 INSERT INTO t4 VALUES(1, 'a');
331 INSERT INTO t4 VALUES(2, 'b');
332 }
333 set ::update_hook [list]
334 execsql {
335 REPLACE INTO t4 VALUES(1, 'c');
336 }
337 set ::update_hook
338 } [list INSERT main t4 3 ]
339 do_execsql_test hook-4.4.1 {
340 SELECT * FROM t4 ORDER BY a;
341 } {1 c 2 b}
342 do_test hook-4.4.2 {
343 set ::update_hook [list]
344 execsql {
345 PRAGMA recursive_triggers = on;
346 REPLACE INTO t4 VALUES(1, 'd');
347 }
348 set ::update_hook
349 } [list INSERT main t4 4 ]
350 do_execsql_test hook-4.4.3 {
351 SELECT * FROM t4 ORDER BY a;
352 } {1 d 2 b}
354 db update_hook {}
355 #
356 #----------------------------------------------------------------------------
358 #----------------------------------------------------------------------------
359 # Test the rollback-hook. The rollback-hook is a bit more complicated than
360 # either the commit or update hooks because a rollback can happen
361 # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or
362 # error condition).
363 #
364 # hook-5.1.* - Test explicit rollbacks.
365 # hook-5.2.* - Test implicit rollbacks caused by constraint failure.
366 #
367 # hook-5.3.* - Test implicit rollbacks caused by IO errors.
368 # hook-5.4.* - Test implicit rollbacks caused by malloc() failure.
369 # hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook
370 # not be called for these?
371 #
373 do_test hook-5.0 {
374 # Configure the rollback hook to increment global variable
375 # $::rollback_hook each time it is invoked.
376 set ::rollback_hook 0
377 db rollback_hook [list incr ::rollback_hook]
378 } {}
380 # Test explicit rollbacks. Not much can really go wrong here.
381 #
382 do_test hook-5.1.1 {
383 set ::rollback_hook 0
384 execsql {
385 BEGIN;
386 ROLLBACK;
387 }
388 set ::rollback_hook
389 } {1}
391 # Test implicit rollbacks caused by constraints.
392 #
393 do_test hook-5.2.1 {
394 set ::rollback_hook 0
395 catchsql {
396 DROP TABLE t1;
397 CREATE TABLE t1(a PRIMARY KEY, b);
398 INSERT INTO t1 VALUES('one', 'I');
399 INSERT INTO t1 VALUES('one', 'I');
400 }
401 set ::rollback_hook
402 } {1}
403 do_test hook-5.2.2 {
404 # Check that the INSERT transaction above really was rolled back.
405 execsql {
406 SELECT count(*) FROM t1;
407 }
408 } {1}
410 #
411 # End rollback-hook testing.
412 #----------------------------------------------------------------------------
414 #----------------------------------------------------------------------------
415 # Test that if a commit-hook returns non-zero (causing a rollback), the
416 # rollback-hook is invoked.
417 #
418 proc commit_hook {} {
419 lappend ::hooks COMMIT
420 return 1
421 }
422 proc rollback_hook {} {
423 lappend ::hooks ROLLBACK
424 }
425 do_test hook-6.1 {
426 set ::hooks [list]
427 db commit_hook commit_hook
428 db rollback_hook rollback_hook
429 catchsql {
430 BEGIN;
431 INSERT INTO t1 VALUES('two', 'II');
432 COMMIT;
433 }
434 execsql { SELECT * FROM t1 }
435 } {one I}
436 do_test hook-6.2 {
437 set ::hooks
438 } {COMMIT ROLLBACK}
439 unset ::hooks
441 finish_test