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.12 2007/10/09 08:29:32 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#---------------------------------------------------------------------------- 96# Tests for the update-hook. 97# 98# 4.1.* - Very simple tests. Test that the update hook is invoked correctly 99# for INSERT, DELETE and UPDATE statements, including DELETE 100# statements with no WHERE clause. 101# 4.2.* - Check that the update-hook is invoked for rows modified by trigger 102# bodies. Also that the database name is correctly reported when 103# an attached database is modified. 104# 4.3.* - Do some sorting, grouping, compound queries, population and 105# depopulation of indices, to make sure the update-hook is not 106# invoked incorrectly. 107# 108 109# Simple tests 110do_test hook-4.1.1 { 111 catchsql { 112 DROP TABLE t1; 113 } 114 execsql { 115 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 116 INSERT INTO t1 VALUES(1, 'one'); 117 INSERT INTO t1 VALUES(2, 'two'); 118 INSERT INTO t1 VALUES(3, 'three'); 119 } 120 db update_hook [list lappend ::update_hook] 121} {} 122do_test hook-4.1.2 { 123 execsql { 124 INSERT INTO t1 VALUES(4, 'four'); 125 DELETE FROM t1 WHERE b = 'two'; 126 UPDATE t1 SET b = '' WHERE a = 1 OR a = 3; 127 DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now) 128 } 129 set ::update_hook 130} [list \ 131 INSERT main t1 4 \ 132 DELETE main t1 2 \ 133 UPDATE main t1 1 \ 134 UPDATE main t1 3 \ 135 DELETE main t1 1 \ 136 DELETE main t1 3 \ 137 DELETE main t1 4 \ 138] 139 140set ::update_hook {} 141ifcapable trigger { 142 do_test hook-4.2.1 { 143 catchsql { 144 DROP TABLE t2; 145 } 146 execsql { 147 CREATE TABLE t2(c INTEGER PRIMARY KEY, d); 148 CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN 149 INSERT INTO t2 VALUES(new.a, new.b); 150 UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c; 151 DELETE FROM t2 WHERE new.a = c; 152 END; 153 } 154 } {} 155 do_test hook-4.2.2 { 156 execsql { 157 INSERT INTO t1 VALUES(1, 'one'); 158 INSERT INTO t1 VALUES(2, 'two'); 159 } 160 set ::update_hook 161 } [list \ 162 INSERT main t1 1 \ 163 INSERT main t2 1 \ 164 UPDATE main t2 1 \ 165 DELETE main t2 1 \ 166 INSERT main t1 2 \ 167 INSERT main t2 2 \ 168 UPDATE main t2 2 \ 169 DELETE main t2 2 \ 170 ] 171} else { 172 execsql { 173 INSERT INTO t1 VALUES(1, 'one'); 174 INSERT INTO t1 VALUES(2, 'two'); 175 } 176} 177 178# Update-hook + ATTACH 179set ::update_hook {} 180ifcapable attach { 181 do_test hook-4.2.3 { 182 file delete -force test2.db 183 execsql { 184 ATTACH 'test2.db' AS aux; 185 CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b); 186 INSERT INTO aux.t3 SELECT * FROM t1; 187 UPDATE t3 SET b = 'two or so' WHERE a = 2; 188 DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now) 189 } 190 set ::update_hook 191 } [list \ 192 INSERT aux t3 1 \ 193 INSERT aux t3 2 \ 194 UPDATE aux t3 2 \ 195 DELETE aux t3 1 \ 196 DELETE aux t3 2 \ 197 ] 198} 199 200ifcapable trigger { 201 execsql { 202 DROP TRIGGER t1_trigger; 203 } 204} 205 206# Test that other vdbe operations involving btree structures do not 207# incorrectly invoke the update-hook. 208set ::update_hook {} 209do_test hook-4.3.1 { 210 execsql { 211 CREATE INDEX t1_i ON t1(b); 212 INSERT INTO t1 VALUES(3, 'three'); 213 UPDATE t1 SET b = ''; 214 DELETE FROM t1 WHERE a > 1; 215 } 216 set ::update_hook 217} [list \ 218 INSERT main t1 3 \ 219 UPDATE main t1 1 \ 220 UPDATE main t1 2 \ 221 UPDATE main t1 3 \ 222 DELETE main t1 2 \ 223 DELETE main t1 3 \ 224] 225set ::update_hook {} 226ifcapable compound&&attach { 227 do_test hook-4.3.2 { 228 execsql { 229 SELECT * FROM t1 UNION SELECT * FROM t3; 230 SELECT * FROM t1 UNION ALL SELECT * FROM t3; 231 SELECT * FROM t1 INTERSECT SELECT * FROM t3; 232 SELECT * FROM t1 EXCEPT SELECT * FROM t3; 233 SELECT * FROM t1 ORDER BY b; 234 SELECT * FROM t1 GROUP BY b; 235 } 236 set ::update_hook 237 } [list] 238} 239db update_hook {} 240# 241#---------------------------------------------------------------------------- 242 243#---------------------------------------------------------------------------- 244# Test the rollback-hook. The rollback-hook is a bit more complicated than 245# either the commit or update hooks because a rollback can happen 246# explicitly (an sql ROLLBACK statement) or implicitly (a constraint or 247# error condition). 248# 249# hook-5.1.* - Test explicit rollbacks. 250# hook-5.2.* - Test implicit rollbacks caused by constraint failure. 251# 252# hook-5.3.* - Test implicit rollbacks caused by IO errors. 253# hook-5.4.* - Test implicit rollbacks caused by malloc() failure. 254# hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook 255# not be called for these? 256# 257 258do_test hook-5.0 { 259 # Configure the rollback hook to increment global variable 260 # $::rollback_hook each time it is invoked. 261 set ::rollback_hook 0 262 db rollback_hook [list incr ::rollback_hook] 263} {} 264 265# Test explicit rollbacks. Not much can really go wrong here. 266# 267do_test hook-5.1.1 { 268 set ::rollback_hook 0 269 execsql { 270 BEGIN; 271 ROLLBACK; 272 } 273 set ::rollback_hook 274} {1} 275 276# Test implicit rollbacks caused by constraints. 277# 278do_test hook-5.2.1 { 279 set ::rollback_hook 0 280 catchsql { 281 DROP TABLE t1; 282 CREATE TABLE t1(a PRIMARY KEY, b); 283 INSERT INTO t1 VALUES('one', 'I'); 284 INSERT INTO t1 VALUES('one', 'I'); 285 } 286 set ::rollback_hook 287} {1} 288do_test hook-5.2.2 { 289 # Check that the INSERT transaction above really was rolled back. 290 execsql { 291 SELECT count(*) FROM t1; 292 } 293} {1} 294 295# 296# End rollback-hook testing. 297#---------------------------------------------------------------------------- 298 299finish_test 300