wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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