# 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.13 2008/01/19 20:11:26 drh 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 \

 ]

 # Update hook is not invoked for changes to sqlite_master

 #

 do_test hook-4.1.3 {

   set ::update_hook {}

   execsql {

     CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;

   }

   set ::update_hook

 } {}

 do_test hook-4.1.4 {

   set ::update_hook {}

   execsql {

     DROP TRIGGER r1;

   }

   set ::update_hook

 } {}

 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 + ATTACH

 set ::update_hook {}

 ifcapable attach {

   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&&attach {

   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