# 2017 Jan 30 # # 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. # #*********************************************************************** # The tests in this file focus on the pre-update hook. # set testdir [file dirname $argv0] source $testdir/tester.tcl set ::testprefix hook2 ifcapable !preupdate { finish_test return } #------------------------------------------------------------------------- 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 } } } #------------------------------------------------------------------------- # Simple tests - INSERT, UPDATE and DELETE on a WITHOUT ROWID table. # db preupdate hook preupdate_hook do_execsql_test 1.0 { CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT ROWID; } do_preupdate_test 1.1 { INSERT INTO t1 VALUES('one', 1); } { INSERT main t1 0 0 one 1 } do_preupdate_test 1.2 { UPDATE t1 SET b=2 WHERE a='one'; } { UPDATE main t1 0 0 one 1 one 2 } do_preupdate_test 1.3 { DELETE FROM t1 WHERE a='one'; } { DELETE main t1 0 0 one 2 } #------------------------------------------------------------------------- # Some more complex tests for the pre-update callback on WITHOUT ROWID # tables. # # 2.1.1 - INSERT statement. # 2.1.2 - INSERT INTO ... SELECT statement. # 2.1.3 - REPLACE INTO ... (PK conflict) # 2.1.4 - REPLACE INTO ... (other index conflicts) # 2.1.5 - REPLACE INTO ... (both PK and other index conflicts) # # 2.2.1 - DELETE statement. # 2.2.2 - DELETE statement that uses the truncate optimization. # # 2.3.1 - UPDATE statement. # 2.3.2 - UPDATE statement that modifies the PK. # 2.3.3 - UPDATE OR REPLACE ... (PK conflict). # 2.3.4 - UPDATE OR REPLACE ... (other index conflicts) # 2.3.4 - UPDATE OR REPLACE ... (both PK and other index conflicts) # do_execsql_test 2.0 { CREATE TABLE t2(a DEFAULT 4, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; CREATE UNIQUE INDEX t2a ON t2(a); } do_preupdate_test 2.1.1 { INSERT INTO t2(b, c) VALUES(1, 1); } { INSERT main t2 0 0 4 1 1 } do_execsql_test 2.1.2.0 { CREATE TABLE d1(a DEFAULT 4, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; CREATE UNIQUE INDEX d1a ON d1(a); INSERT INTO d1 VALUES(1, 2, 3); INSERT INTO d1 VALUES(11, 12, 13); } do_preupdate_test 2.1.2.1 { INSERT INTO t2 SELECT * FROM d1; } { INSERT main t2 0 0 1 2 3 INSERT main t2 0 0 11 12 13 } do_preupdate_test 2.1.2.2 { INSERT INTO t2 SELECT a+20, b+20, c+20 FROM d1; } { INSERT main t2 0 0 21 22 23 INSERT main t2 0 0 31 32 33 } do_execsql_test 2.1.2.3 { SELECT * FROM t2 ORDER BY b, c; } { 4 1 1 1 2 3 11 12 13 21 22 23 31 32 33 } do_preupdate_test 2.1.3 { REPLACE INTO t2 VALUES(45, 22, 23); } { DELETE main t2 0 0 21 22 23 INSERT main t2 0 0 45 22 23 } do_preupdate_test 2.1.4 { REPLACE INTO t2 VALUES(11, 100, 100); } { DELETE main t2 0 0 11 12 13 INSERT main t2 0 0 11 100 100 } do_preupdate_test 2.1.5 { REPLACE INTO t2(c, b) VALUES(33, 32) } { DELETE main t2 0 0 4 1 1 DELETE main t2 0 0 31 32 33 INSERT main t2 0 0 4 32 33 } do_execsql_test 2.2.0 { SELECT * FROM t2 ORDER BY b,c; } { 1 2 3 45 22 23 4 32 33 11 100 100 } do_preupdate_test 2.2.1 { DELETE FROM t2 WHERE b=22; } { DELETE main t2 0 0 45 22 23 } do_preupdate_test 2.2.2 { DELETE FROM t2; } { DELETE main t2 0 0 1 2 3 DELETE main t2 0 0 4 32 33 DELETE main t2 0 0 11 100 100 } do_execsql_test 2.3.0 { CREATE TABLE t3(x, y PRIMARY KEY, z UNIQUE) WITHOUT ROWID; INSERT INTO t3 VALUES('a', 'b', 'c'); INSERT INTO t3 VALUES('d', 'e', 'f'); INSERT INTO t3 VALUES(1, 1, 1); INSERT INTO t3 VALUES(2, 2, 2); INSERT INTO t3 VALUES(3, 3, 3); } do_preupdate_test 2.3.1 { UPDATE t3 SET x=4 WHERE y IN ('b', 'e', 'x'); } { UPDATE main t3 0 0 a b c 4 b c UPDATE main t3 0 0 d e f 4 e f } do_preupdate_test 2.3.2 { UPDATE t3 SET y=y||y WHERE z IN('c', 'f'); } { UPDATE main t3 0 0 4 b c 4 bb c UPDATE main t3 0 0 4 e f 4 ee f } do_preupdate_test 2.3.3 { UPDATE OR REPLACE t3 SET y='bb' WHERE z='f' } { DELETE main t3 0 0 4 bb c UPDATE main t3 0 0 4 ee f 4 bb f } do_preupdate_test 2.3.4 { UPDATE OR REPLACE t3 SET z=2 WHERE y=1; } { DELETE main t3 0 0 2 2 2 UPDATE main t3 0 0 1 1 1 1 1 2 } do_preupdate_test 2.3.5 { UPDATE OR REPLACE t3 SET z=2, y='bb' WHERE y=3; } { DELETE main t3 0 0 1 1 2 DELETE main t3 0 0 4 bb f UPDATE main t3 0 0 3 3 3 3 bb 2 } finish_test