• Home
  • Line#
  • Scopes#
  • Navigate#
  • Raw
  • Download
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
22set testdir [file dirname $argv0]
23source $testdir/tester.tcl
24
25do_test hook-1.2 {
26  db commit_hook
27} {}
28
29
30do_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}
39do_test hook-3.2 {
40  set commit_cnt
41} {0}
42do_test hook-3.3 {
43  execsql {
44    CREATE TABLE t2(a,b);
45  }
46  set commit_cnt
47} {1}
48do_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}
56do_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}
67do_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}}
77do_test hook-3.7 {
78  set ::commit_cnt
79} {1 2 2 3 3 4 4 5 5 6 6 7}
80do_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#
86do_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#
97do_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}
113db2 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
131do_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} {}
143do_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
161ifcapable 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
217set ::update_hook {}
218ifcapable 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
238ifcapable 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.
246set ::update_hook {}
247do_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]
263set ::update_hook {}
264ifcapable 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}
277db update_hook {}
278#
279#----------------------------------------------------------------------------
280
281#----------------------------------------------------------------------------
282# Test the rollback-hook. The rollback-hook is a bit more complicated than
283# either the commit or update hooks because a rollback can happen
284# explicitly (an sql ROLLBACK statement) or implicitly (a constraint or
285# error condition).
286#
287# hook-5.1.* - Test explicit rollbacks.
288# hook-5.2.* - Test implicit rollbacks caused by constraint failure.
289#
290# hook-5.3.* - Test implicit rollbacks caused by IO errors.
291# hook-5.4.* - Test implicit rollbacks caused by malloc() failure.
292# hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook
293#              not be called for these?
294#
295
296do_test hook-5.0 {
297  # Configure the rollback hook to increment global variable
298  # $::rollback_hook each time it is invoked.
299  set ::rollback_hook 0
300  db rollback_hook [list incr ::rollback_hook]
301} {}
302
303# Test explicit rollbacks. Not much can really go wrong here.
304#
305do_test hook-5.1.1 {
306  set ::rollback_hook 0
307  execsql {
308    BEGIN;
309    ROLLBACK;
310  }
311  set ::rollback_hook
312} {1}
313
314# Test implicit rollbacks caused by constraints.
315#
316do_test hook-5.2.1 {
317  set ::rollback_hook 0
318  catchsql {
319    DROP TABLE t1;
320    CREATE TABLE t1(a PRIMARY KEY, b);
321    INSERT INTO t1 VALUES('one', 'I');
322    INSERT INTO t1 VALUES('one', 'I');
323  }
324  set ::rollback_hook
325} {1}
326do_test hook-5.2.2 {
327  # Check that the INSERT transaction above really was rolled back.
328  execsql {
329    SELECT count(*) FROM t1;
330  }
331} {1}
332
333#
334# End rollback-hook testing.
335#----------------------------------------------------------------------------
336
337#----------------------------------------------------------------------------
338# Test that if a commit-hook returns non-zero (causing a rollback), the
339# rollback-hook is invoked.
340#
341proc commit_hook {} {
342  lappend ::hooks COMMIT
343  return 1
344}
345proc rollback_hook {} {
346  lappend ::hooks ROLLBACK
347}
348do_test hook-6.1 {
349  set ::hooks [list]
350  db commit_hook commit_hook
351  db rollback_hook rollback_hook
352  catchsql {
353    BEGIN;
354      INSERT INTO t1 VALUES('two', 'II');
355    COMMIT;
356  }
357  execsql { SELECT * FROM t1 }
358} {one I}
359do_test hook-6.2 {
360  set ::hooks
361} {COMMIT ROLLBACK}
362unset ::hooks
363
364finish_test
365