# 2004 Jan 14 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for TCL interface to the # SQLite library. # # The focus of the tests in this file is the following interface: # # sqlite_commit_hook (tests hook-1..hook-3 inclusive) # sqlite_update_hook (tests hook-4-*) # sqlite_rollback_hook (tests hook-5.*) # # $Id: hook.test,v 1.10 2006/01/16 16:24:25 danielk1977 Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl do_test hook-1.2 { db commit_hook } {} do_test hook-3.1 { set commit_cnt 0 proc commit_hook {} { incr ::commit_cnt return 0 } db commit_hook ::commit_hook db commit_hook } {::commit_hook} do_test hook-3.2 { set commit_cnt } {0} do_test hook-3.3 { execsql { CREATE TABLE t2(a,b); } set commit_cnt } {1} do_test hook-3.4 { execsql { INSERT INTO t2 VALUES(1,2); INSERT INTO t2 SELECT a+1, b+1 FROM t2; INSERT INTO t2 SELECT a+2, b+2 FROM t2; } set commit_cnt } {4} do_test hook-3.5 { set commit_cnt {} proc commit_hook {} { set ::commit_cnt [execsql {SELECT * FROM t2}] return 0 } execsql { INSERT INTO t2 VALUES(5,6); } set commit_cnt } {1 2 2 3 3 4 4 5 5 6} do_test hook-3.6 { set commit_cnt {} proc commit_hook {} { set ::commit_cnt [execsql {SELECT * FROM t2}] return 1 } catchsql { INSERT INTO t2 VALUES(6,7); } } {1 {constraint failed}} do_test hook-3.7 { set ::commit_cnt } {1 2 2 3 3 4 4 5 5 6 6 7} do_test hook-3.8 { execsql {SELECT * FROM t2} } {1 2 2 3 3 4 4 5 5 6} # Test turnning off the commit hook # do_test hook-3.9 { db commit_hook {} set ::commit_cnt {} execsql { INSERT INTO t2 VALUES(7,8); } set ::commit_cnt } {} #---------------------------------------------------------------------------- # Tests for the update-hook. # # 4.1.* - Very simple tests. Test that the update hook is invoked correctly # for INSERT, DELETE and UPDATE statements, including DELETE # statements with no WHERE clause. # 4.2.* - Check that the update-hook is invoked for rows modified by trigger # bodies. Also that the database name is correctly reported when # an attached database is modified. # 4.3.* - Do some sorting, grouping, compound queries, population and # depopulation of indices, to make sure the update-hook is not # invoked incorrectly. # # Simple tests do_test hook-4.1.1 { catchsql { DROP TABLE t1; } execsql { CREATE TABLE t1(a INTEGER PRIMARY KEY, b); INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); INSERT INTO t1 VALUES(3, 'three'); } db update_hook [list lappend ::update_hook] } {} do_test hook-4.1.2 { execsql { INSERT INTO t1 VALUES(4, 'four'); DELETE FROM t1 WHERE b = 'two'; UPDATE t1 SET b = '' WHERE a = 1 OR a = 3; DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now) } set ::update_hook } [list \ INSERT main t1 4 \ DELETE main t1 2 \ UPDATE main t1 1 \ UPDATE main t1 3 \ DELETE main t1 1 \ DELETE main t1 3 \ DELETE main t1 4 \ ] set ::update_hook {} do_test hook-4.2.1 { catchsql { DROP TABLE t2; } execsql { CREATE TABLE t2(c INTEGER PRIMARY KEY, d); CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.a, new.b); UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c; DELETE FROM t2 WHERE new.a = c; END; } } {} do_test hook-4.2.2 { execsql { INSERT INTO t1 VALUES(1, 'one'); INSERT INTO t1 VALUES(2, 'two'); } set ::update_hook } [list \ INSERT main t1 1 \ INSERT main t2 1 \ UPDATE main t2 1 \ DELETE main t2 1 \ INSERT main t1 2 \ INSERT main t2 2 \ UPDATE main t2 2 \ DELETE main t2 2 \ ] # Triggers + ATTACH set ::update_hook {} do_test hook-4.2.3 { file delete -force test2.db execsql { ATTACH 'test2.db' AS aux; CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b); INSERT INTO aux.t3 SELECT * FROM t1; UPDATE t3 SET b = 'two or so' WHERE a = 2; DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now) } set ::update_hook } [list \ INSERT aux t3 1 \ INSERT aux t3 2 \ UPDATE aux t3 2 \ DELETE aux t3 1 \ DELETE aux t3 2 \ ] # Test that other vdbe operations involving btree structures do not # incorrectly invoke the update-hook. set ::update_hook {} do_test hook-4.3.1 { execsql { DROP TRIGGER t1_trigger; CREATE INDEX t1_i ON t1(b); INSERT INTO t1 VALUES(3, 'three'); UPDATE t1 SET b = ''; DELETE FROM t1 WHERE a > 1; } set ::update_hook } [list \ INSERT main t1 3 \ UPDATE main t1 1 \ UPDATE main t1 2 \ UPDATE main t1 3 \ DELETE main t1 2 \ DELETE main t1 3 \ ] set ::update_hook {} ifcapable compound { do_test hook-4.3.2 { execsql { SELECT * FROM t1 UNION SELECT * FROM t3; SELECT * FROM t1 UNION ALL SELECT * FROM t3; SELECT * FROM t1 INTERSECT SELECT * FROM t3; SELECT * FROM t1 EXCEPT SELECT * FROM t3; SELECT * FROM t1 ORDER BY b; SELECT * FROM t1 GROUP BY b; } set ::update_hook } [list] } db update_hook {} # #---------------------------------------------------------------------------- #---------------------------------------------------------------------------- # Test the rollback-hook. The rollback-hook is a bit more complicated than # either the commit or update hooks because a rollback can happen # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or # error condition). # # hook-5.1.* - Test explicit rollbacks. # hook-5.2.* - Test implicit rollbacks caused by constraint failure. # # hook-5.3.* - Test implicit rollbacks caused by IO errors. # hook-5.4.* - Test implicit rollbacks caused by malloc() failure. # hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook # not be called for these? # do_test hook-5.0 { # Configure the rollback hook to increment global variable # $::rollback_hook each time it is invoked. set ::rollback_hook 0 db rollback_hook [list incr ::rollback_hook] } {} # Test explicit rollbacks. Not much can really go wrong here. # do_test hook-5.1.1 { set ::rollback_hook 0 execsql { BEGIN; ROLLBACK; } set ::rollback_hook } {1} # Test implicit rollbacks caused by constraints. # do_test hook-5.2.1 { set ::rollback_hook 0 catchsql { DROP TABLE t1; CREATE TABLE t1(a PRIMARY KEY, b); INSERT INTO t1 VALUES('one', 'I'); INSERT INTO t1 VALUES('one', 'I'); } set ::rollback_hook } {1} do_test hook-5.2.2 { # Check that the INSERT transaction above really was rolled back. execsql { SELECT count(*) FROM t1; } } {1} # # End rollback-hook testing. #---------------------------------------------------------------------------- finish_test