aboutsummaryrefslogtreecommitdiff
path: root/test/hook.test
diff options
context:
space:
mode:
Diffstat (limited to 'test/hook.test')
-rw-r--r--test/hook.test413
1 files changed, 413 insertions, 0 deletions
diff --git a/test/hook.test b/test/hook.test
index de6fbdd25..27705ff8c 100644
--- a/test/hook.test
+++ b/test/hook.test
@@ -21,6 +21,7 @@
set testdir [file dirname $argv0]
source $testdir/tester.tcl
+set ::testprefix hook
do_test hook-1.2 {
db commit_hook
@@ -196,10 +197,13 @@ do_test hook-4.1.2 {
# EVIDENCE-OF: R-33257-44249 The update hook is not invoked when WITHOUT
# ROWID tables are modified.
#
+breakpoint
do_test hook-4.1.2w {
set ::update_hook {}
execsql {
INSERT INTO t1w VALUES(4, 'four');
+PRAGMA vdbe_debug=on;
+PRAGMA vdbe_addoptrace=on;
DELETE FROM t1w WHERE b = 'two';
UPDATE t1w SET b = '' WHERE a = 1 OR a = 3;
DELETE FROM t1w WHERE 1; -- Avoid the truncate optimization (for now)
@@ -438,4 +442,413 @@ do_test hook-6.2 {
} {COMMIT ROLLBACK}
unset ::hooks
+#----------------------------------------------------------------------------
+# The following tests - hook-7.* - test the pre-update hook.
+#
+ifcapable !preupdate {
+ finish_test
+ return
+}
+#
+# 7.1.1 - INSERT statement.
+# 7.1.2 - INSERT INTO ... SELECT statement.
+# 7.1.3 - REPLACE INTO ... (rowid conflict)
+# 7.1.4 - REPLACE INTO ... (other index conflicts)
+# 7.1.5 - REPLACE INTO ... (both rowid and other index conflicts)
+#
+# 7.2.1 - DELETE statement.
+# 7.2.2 - DELETE statement that uses the truncate optimization.
+#
+# 7.3.1 - UPDATE statement.
+# 7.3.2 - UPDATE statement that modifies the rowid.
+# 7.3.3 - UPDATE OR REPLACE ... (rowid conflict).
+# 7.3.4 - UPDATE OR REPLACE ... (other index conflicts)
+# 7.3.4 - UPDATE OR REPLACE ... (both rowid and other index conflicts)
+#
+# 7.4.1 - Test that the pre-update-hook is invoked only once if a row being
+# deleted is removed by a BEFORE trigger.
+#
+# 7.4.2 - Test that the pre-update-hook is invoked if a BEFORE trigger
+# removes a row being updated. In this case the update hook should
+# be invoked with SQLITE_INSERT as the opcode when inserting the
+# new version of the row.
+#
+# TODO: Short records (those created before a column is added to a table
+# using ALTER TABLE)
+#
+
+proc do_preupdate_test {tn sql x} {
+ set X [list]
+ foreach elem $x {lappend X $elem}
+ uplevel do_test $tn [list "
+ set ::preupdate \[list\]
+ execsql { $sql }
+ set ::preupdate
+ "] [list $X]
+}
+
+proc preupdate_hook {args} {
+ set type [lindex $args 0]
+ eval lappend ::preupdate $args
+ if {$type != "INSERT"} {
+ for {set i 0} {$i < [db preupdate count]} {incr i} {
+ lappend ::preupdate [db preupdate old $i]
+ }
+ }
+ if {$type != "DELETE"} {
+ for {set i 0} {$i < [db preupdate count]} {incr i} {
+ set rc [catch { db preupdate new $i } v]
+ lappend ::preupdate $v
+ }
+ }
+}
+
+db close
+forcedelete test.db
+sqlite3 db test.db
+db preupdate hook preupdate_hook
+
+# Set up a schema to use for tests 7.1.* to 7.3.*.
+do_execsql_test 7.0 {
+ CREATE TABLE t1(a, b);
+ CREATE TABLE t2(x, y);
+ CREATE TABLE t3(i, j, UNIQUE(i));
+
+ INSERT INTO t2 VALUES('a', 'b');
+ INSERT INTO t2 VALUES('c', 'd');
+
+ INSERT INTO t3 VALUES(4, 16);
+ INSERT INTO t3 VALUES(5, 25);
+ INSERT INTO t3 VALUES(6, 36);
+}
+
+do_preupdate_test 7.1.1 {
+ INSERT INTO t1 VALUES('x', 'y')
+} {INSERT main t1 1 1 x y}
+
+# 7.1.2.1 does not use the xfer optimization. 7.1.2.2 does.
+do_preupdate_test 7.1.2.1 {
+ INSERT INTO t1 SELECT y, x FROM t2;
+} {INSERT main t1 2 2 b a INSERT main t1 3 3 d c}
+do_preupdate_test 7.1.2.2 {
+ INSERT INTO t1 SELECT * FROM t2;
+} {INSERT main t1 4 4 a b INSERT main t1 5 5 c d}
+
+do_preupdate_test 7.1.3 {
+ REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1);
+} {
+ DELETE main t1 1 1 x y
+ INSERT main t1 1 1 1 1
+}
+
+do_preupdate_test 7.1.4 {
+ REPLACE INTO t3 VALUES(4, NULL);
+} {
+ DELETE main t3 1 1 4 16
+ INSERT main t3 4 4 4 {}
+}
+
+do_preupdate_test 7.1.5 {
+ REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL);
+} {
+ DELETE main t3 2 2 5 25
+ DELETE main t3 3 3 6 36
+ INSERT main t3 2 2 6 {}
+}
+
+do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5}
+
+do_preupdate_test 7.2.1 {
+ DELETE FROM t1 WHERE rowid = 3
+} {
+ DELETE main t1 3 3 d c
+}
+do_preupdate_test 7.2.2 {
+ DELETE FROM t1
+} {
+ DELETE main t1 1 1 1 1
+ DELETE main t1 2 2 b a
+ DELETE main t1 4 4 a b
+ DELETE main t1 5 5 c d
+}
+
+do_execsql_test 7.3.0 {
+ DELETE FROM t1;
+ DELETE FROM t2;
+ DELETE FROM t3;
+
+ INSERT INTO t2 VALUES('a', 'b');
+ INSERT INTO t2 VALUES('c', 'd');
+
+ INSERT INTO t3 VALUES(4, 16);
+ INSERT INTO t3 VALUES(5, 25);
+ INSERT INTO t3 VALUES(6, 36);
+}
+
+do_preupdate_test 7.3.1 {
+ UPDATE t2 SET y = y||y;
+} {
+ UPDATE main t2 1 1 a b a bb
+ UPDATE main t2 2 2 c d c dd
+}
+
+do_preupdate_test 7.3.2 {
+ UPDATE t2 SET rowid = rowid-1;
+} {
+ UPDATE main t2 1 0 a bb a bb
+ UPDATE main t2 2 1 c dd c dd
+}
+
+do_preupdate_test 7.3.3 {
+ UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a'
+} {
+ DELETE main t2 1 1 c dd
+ UPDATE main t2 0 1 a bb a bb
+}
+
+do_preupdate_test 7.3.4.1 {
+ UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6
+} {
+ DELETE main t3 2 2 5 25
+ UPDATE main t3 3 3 6 36 5 36
+}
+
+do_execsql_test 7.3.4.2 {
+ INSERT INTO t3 VALUES(10, 100);
+ SELECT rowid, * FROM t3;
+} {1 4 16 3 5 36 4 10 100}
+
+do_preupdate_test 7.3.5 {
+ UPDATE OR REPLACE t3 SET rowid = 1, i = 5 WHERE j = 100;
+} {
+ DELETE main t3 1 1 4 16
+ DELETE main t3 3 3 5 36
+ UPDATE main t3 4 1 10 100 5 100
+}
+
+do_execsql_test 7.4.1.0 {
+ CREATE TABLE t4(a, b);
+ INSERT INTO t4 VALUES('a', 1);
+ INSERT INTO t4 VALUES('b', 2);
+ INSERT INTO t4 VALUES('c', 3);
+
+ CREATE TRIGGER t4t BEFORE DELETE ON t4 BEGIN
+ DELETE FROM t4 WHERE b = 1;
+ END;
+}
+
+do_preupdate_test 7.4.1.1 {
+ DELETE FROM t4 WHERE b = 3
+} {
+ DELETE main t4 1 1 a 1
+ DELETE main t4 3 3 c 3
+}
+
+do_execsql_test 7.4.1.2 {
+ INSERT INTO t4(rowid, a, b) VALUES(1, 'a', 1);
+ INSERT INTO t4(rowid, a, b) VALUES(3, 'c', 3);
+}
+do_preupdate_test 7.4.1.3 {
+ DELETE FROM t4 WHERE b = 1
+} {
+ DELETE main t4 1 1 a 1
+}
+
+do_execsql_test 7.4.2.0 {
+ CREATE TABLE t5(a, b);
+ INSERT INTO t5 VALUES('a', 1);
+ INSERT INTO t5 VALUES('b', 2);
+ INSERT INTO t5 VALUES('c', 3);
+
+ CREATE TRIGGER t5t BEFORE UPDATE ON t5 BEGIN
+ DELETE FROM t5 WHERE b = 1;
+ END;
+}
+do_preupdate_test 7.4.2.1 {
+ UPDATE t5 SET b = 4 WHERE a = 'c'
+} {
+ DELETE main t5 1 1 a 1
+ UPDATE main t5 3 3 c 3 c 4
+}
+
+do_execsql_test 7.4.2.2 {
+ INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1);
+}
+
+do_preupdate_test 7.4.2.3 {
+ UPDATE t5 SET b = 5 WHERE a = 'a'
+} {
+ DELETE main t5 1 1 a 1
+}
+
+do_execsql_test 7.5.1.0 {
+ CREATE TABLE t7(a, b);
+ INSERT INTO t7 VALUES('one', 'two');
+ INSERT INTO t7 VALUES('three', 'four');
+ ALTER TABLE t7 ADD COLUMN c DEFAULT NULL;
+}
+
+do_preupdate_test 7.5.1.1 {
+ DELETE FROM t7 WHERE a = 'one'
+} {
+ DELETE main t7 1 1 one two {}
+}
+
+do_preupdate_test 7.5.1.2 {
+ UPDATE t7 SET b = 'five'
+} {
+ UPDATE main t7 2 2 three four {} three five {}
+}
+
+do_execsql_test 7.5.2.0 {
+ CREATE TABLE t8(a, b);
+ INSERT INTO t8 VALUES('one', 'two');
+ INSERT INTO t8 VALUES('three', 'four');
+ ALTER TABLE t8 ADD COLUMN c DEFAULT 'xxx';
+}
+
+ifcapable !session {
+ # At time of writing, these two are broken. They demonstrate that the
+ # sqlite3_preupdate_old() method does not handle the case where ALTER TABLE
+ # has been used to add a column with a default value other than NULL.
+ #
+ do_preupdate_test 7.5.2.1 {
+ DELETE FROM t8 WHERE a = 'one'
+ } {
+ DELETE main t8 1 1 one two xxx
+ }
+ do_preupdate_test 7.5.2.2 {
+ UPDATE t8 SET b = 'five'
+ } {
+ UPDATE main t8 2 2 three four xxx three five xxx
+ }
+}
+
+# This block of tests verifies that IPK values are correctly reported
+# by the sqlite3_preupdate_old() and sqlite3_preupdate_new() functions.
+#
+do_execsql_test 7.6.1 { CREATE TABLE t9(a, b INTEGER PRIMARY KEY, c) }
+do_preupdate_test 7.6.2 {
+ INSERT INTO t9 VALUES(1, 2, 3);
+ UPDATE t9 SET b = b+1, c = c+1;
+ DELETE FROM t9 WHERE a = 1;
+} {
+ INSERT main t9 2 2 1 2 3
+ UPDATE main t9 2 3 1 2 3 1 3 4
+ DELETE main t9 3 3 1 3 4
+}
+
+#--------------------------------------------------------------------------
+# Test that the sqlite3_preupdate_depth() API seems to work.
+#
+proc preupdate_hook {args} {
+ set type [lindex $args 0]
+ eval lappend ::preupdate $args
+ eval lappend ::preupdate [db preupdate depth]
+
+ if {$type != "INSERT"} {
+ for {set i 0} {$i < [db preupdate count]} {incr i} {
+ lappend ::preupdate [db preupdate old $i]
+ }
+ }
+ if {$type != "DELETE"} {
+ for {set i 0} {$i < [db preupdate count]} {incr i} {
+ set rc [catch { db preupdate new $i } v]
+ lappend ::preupdate $v
+ }
+ }
+}
+
+db close
+forcedelete test.db
+sqlite3 db test.db
+db preupdate hook preupdate_hook
+
+do_execsql_test 7.6.1 {
+ CREATE TABLE t1(x PRIMARY KEY);
+ CREATE TABLE t2(x PRIMARY KEY);
+ CREATE TABLE t3(x PRIMARY KEY);
+ CREATE TABLE t4(x PRIMARY KEY);
+
+ CREATE TRIGGER a AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
+ CREATE TRIGGER b AFTER INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
+ CREATE TRIGGER c AFTER INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
+
+ CREATE TRIGGER d AFTER UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
+ CREATE TRIGGER e AFTER UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
+ CREATE TRIGGER f AFTER UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
+
+ CREATE TRIGGER g AFTER DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
+ CREATE TRIGGER h AFTER DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
+ CREATE TRIGGER i AFTER DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
+}
+
+do_preupdate_test 7.6.2 {
+ INSERT INTO t1 VALUES('xyz');
+} {
+ INSERT main t1 1 1 0 xyz
+ INSERT main t2 1 1 1 xyz
+ INSERT main t3 1 1 2 xyz
+ INSERT main t4 1 1 3 xyz
+}
+do_preupdate_test 7.6.3 {
+ UPDATE t1 SET x = 'abc';
+} {
+ UPDATE main t1 1 1 0 xyz abc
+ UPDATE main t2 1 1 1 xyz abc
+ UPDATE main t3 1 1 2 xyz abc
+ UPDATE main t4 1 1 3 xyz abc
+}
+do_preupdate_test 7.6.4 {
+ DELETE FROM t1 WHERE 1;
+} {
+ DELETE main t1 1 1 0 abc
+ DELETE main t2 1 1 1 abc
+ DELETE main t3 1 1 2 abc
+ DELETE main t4 1 1 3 abc
+}
+
+do_execsql_test 7.6.5 {
+ DROP TRIGGER a; DROP TRIGGER b; DROP TRIGGER c;
+ DROP TRIGGER d; DROP TRIGGER e; DROP TRIGGER f;
+ DROP TRIGGER g; DROP TRIGGER h; DROP TRIGGER i;
+
+ CREATE TRIGGER a BEFORE INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
+ CREATE TRIGGER b BEFORE INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
+ CREATE TRIGGER c BEFORE INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
+
+ CREATE TRIGGER d BEFORE UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
+ CREATE TRIGGER e BEFORE UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
+ CREATE TRIGGER f BEFORE UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
+
+ CREATE TRIGGER g BEFORE DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
+ CREATE TRIGGER h BEFORE DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
+ CREATE TRIGGER i BEFORE DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
+}
+
+do_preupdate_test 7.6.6 {
+ INSERT INTO t1 VALUES('xyz');
+} {
+ INSERT main t4 1 1 3 xyz
+ INSERT main t3 1 1 2 xyz
+ INSERT main t2 1 1 1 xyz
+ INSERT main t1 1 1 0 xyz
+}
+do_preupdate_test 7.6.3 {
+ UPDATE t1 SET x = 'abc';
+} {
+ UPDATE main t4 1 1 3 xyz abc
+ UPDATE main t3 1 1 2 xyz abc
+ UPDATE main t2 1 1 1 xyz abc
+ UPDATE main t1 1 1 0 xyz abc
+}
+do_preupdate_test 7.6.4 {
+ DELETE FROM t1 WHERE 1;
+} {
+ DELETE main t4 1 1 3 abc
+ DELETE main t3 1 1 2 abc
+ DELETE main t2 1 1 1 abc
+ DELETE main t1 1 1 0 abc
+}
+
finish_test
generated by cgit v1.2.3 (git 2.25.1) at 2025-07-18 11:14:19 +0000