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