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.1.1.1 2012/02/21 17:04:16 misho 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: forcedelete 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: forcedelete 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to hook.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