--- /dev/null Thu Jan 01 00:00:00 1970 +0000
+++ b/persistentstorage/sqlite3api/TEST/TclScript/hook.test Fri Jan 22 11:06:30 2010 +0200
@@ -0,0 +1,317 @@
+# 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