# 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.11 2006/01/17 09:35:02 danielk1977 Exp $set testdir [file dirname $argv0]source $testdir/tester.tcldo_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 testsdo_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 {}ifcapable trigger {  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 \  ]} else {  execsql {    INSERT INTO t1 VALUES(1, 'one');    INSERT INTO t1 VALUES(2, 'two');  }}# Update-hook + ATTACHset ::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 \]ifcapable trigger {  execsql {    DROP TRIGGER t1_trigger;  }}# 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 {    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