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