gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/hook2.test (about) 1 # 2017 Jan 30 2 # 3 # The author disclaims copyright to this source code. In place of 4 # a legal notice, here is a blessing: 5 # 6 # May you do good and not evil. 7 # May you find forgiveness for yourself and forgive others. 8 # May you share freely, never taking more than you give. 9 # 10 #*********************************************************************** 11 # The tests in this file focus on the pre-update hook. 12 # 13 14 set testdir [file dirname $argv0] 15 source $testdir/tester.tcl 16 set ::testprefix hook2 17 18 ifcapable !preupdate { 19 finish_test 20 return 21 } 22 23 #------------------------------------------------------------------------- 24 proc do_preupdate_test {tn sql x} { 25 set X [list] 26 foreach elem $x {lappend X $elem} 27 uplevel do_test $tn [list " 28 set ::preupdate \[list\] 29 execsql { $sql } 30 set ::preupdate 31 "] [list $X] 32 } 33 34 proc preupdate_hook {args} { 35 set type [lindex $args 0] 36 eval lappend ::preupdate $args 37 if {$type != "INSERT"} { 38 for {set i 0} {$i < [db preupdate count]} {incr i} { 39 lappend ::preupdate [db preupdate old $i] 40 } 41 } 42 if {$type != "DELETE"} { 43 for {set i 0} {$i < [db preupdate count]} {incr i} { 44 set rc [catch { db preupdate new $i } v] 45 lappend ::preupdate $v 46 } 47 } 48 } 49 50 #------------------------------------------------------------------------- 51 # Simple tests - INSERT, UPDATE and DELETE on a WITHOUT ROWID table. 52 # 53 db preupdate hook preupdate_hook 54 do_execsql_test 1.0 { 55 CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT ROWID; 56 } 57 do_preupdate_test 1.1 { 58 INSERT INTO t1 VALUES('one', 1); 59 } { 60 INSERT main t1 0 0 one 1 61 } 62 do_preupdate_test 1.2 { 63 UPDATE t1 SET b=2 WHERE a='one'; 64 } { 65 UPDATE main t1 0 0 one 1 one 2 66 } 67 do_preupdate_test 1.3 { 68 DELETE FROM t1 WHERE a='one'; 69 } { 70 DELETE main t1 0 0 one 2 71 } 72 73 #------------------------------------------------------------------------- 74 # Some more complex tests for the pre-update callback on WITHOUT ROWID 75 # tables. 76 # 77 # 2.1.1 - INSERT statement. 78 # 2.1.2 - INSERT INTO ... SELECT statement. 79 # 2.1.3 - REPLACE INTO ... (PK conflict) 80 # 2.1.4 - REPLACE INTO ... (other index conflicts) 81 # 2.1.5 - REPLACE INTO ... (both PK and other index conflicts) 82 # 83 # 2.2.1 - DELETE statement. 84 # 2.2.2 - DELETE statement that uses the truncate optimization. 85 # 86 # 2.3.1 - UPDATE statement. 87 # 2.3.2 - UPDATE statement that modifies the PK. 88 # 2.3.3 - UPDATE OR REPLACE ... (PK conflict). 89 # 2.3.4 - UPDATE OR REPLACE ... (other index conflicts) 90 # 2.3.4 - UPDATE OR REPLACE ... (both PK and other index conflicts) 91 # 92 do_execsql_test 2.0 { 93 CREATE TABLE t2(a DEFAULT 4, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; 94 CREATE UNIQUE INDEX t2a ON t2(a); 95 } 96 97 do_preupdate_test 2.1.1 { 98 INSERT INTO t2(b, c) VALUES(1, 1); 99 } { 100 INSERT main t2 0 0 4 1 1 101 } 102 103 do_execsql_test 2.1.2.0 { 104 CREATE TABLE d1(a DEFAULT 4, b, c, PRIMARY KEY(b, c)) WITHOUT ROWID; 105 CREATE UNIQUE INDEX d1a ON d1(a); 106 INSERT INTO d1 VALUES(1, 2, 3); 107 INSERT INTO d1 VALUES(11, 12, 13); 108 } 109 do_preupdate_test 2.1.2.1 { 110 INSERT INTO t2 SELECT * FROM d1; 111 } { 112 INSERT main t2 0 0 1 2 3 113 INSERT main t2 0 0 11 12 13 114 } 115 do_preupdate_test 2.1.2.2 { 116 INSERT INTO t2 SELECT a+20, b+20, c+20 FROM d1; 117 } { 118 INSERT main t2 0 0 21 22 23 119 INSERT main t2 0 0 31 32 33 120 } 121 do_execsql_test 2.1.2.3 { 122 SELECT * FROM t2 ORDER BY b, c; 123 } { 124 4 1 1 125 1 2 3 126 11 12 13 127 21 22 23 128 31 32 33 129 } 130 do_preupdate_test 2.1.3 { 131 REPLACE INTO t2 VALUES(45, 22, 23); 132 } { 133 DELETE main t2 0 0 21 22 23 134 INSERT main t2 0 0 45 22 23 135 } 136 do_preupdate_test 2.1.4 { 137 REPLACE INTO t2 VALUES(11, 100, 100); 138 } { 139 DELETE main t2 0 0 11 12 13 140 INSERT main t2 0 0 11 100 100 141 } 142 do_preupdate_test 2.1.5 { 143 REPLACE INTO t2(c, b) VALUES(33, 32) 144 } { 145 DELETE main t2 0 0 4 1 1 146 DELETE main t2 0 0 31 32 33 147 INSERT main t2 0 0 4 32 33 148 } 149 150 do_execsql_test 2.2.0 { 151 SELECT * FROM t2 ORDER BY b,c; 152 } { 153 1 2 3 154 45 22 23 155 4 32 33 156 11 100 100 157 } 158 do_preupdate_test 2.2.1 { 159 DELETE FROM t2 WHERE b=22; 160 } { 161 DELETE main t2 0 0 45 22 23 162 } 163 do_preupdate_test 2.2.2 { 164 DELETE FROM t2; 165 } { 166 DELETE main t2 0 0 1 2 3 167 DELETE main t2 0 0 4 32 33 168 DELETE main t2 0 0 11 100 100 169 } 170 171 do_execsql_test 2.3.0 { 172 CREATE TABLE t3(x, y PRIMARY KEY, z UNIQUE) WITHOUT ROWID; 173 INSERT INTO t3 VALUES('a', 'b', 'c'); 174 INSERT INTO t3 VALUES('d', 'e', 'f'); 175 176 INSERT INTO t3 VALUES(1, 1, 1); 177 INSERT INTO t3 VALUES(2, 2, 2); 178 INSERT INTO t3 VALUES(3, 3, 3); 179 } 180 181 do_preupdate_test 2.3.1 { 182 UPDATE t3 SET x=4 WHERE y IN ('b', 'e', 'x'); 183 } { 184 UPDATE main t3 0 0 a b c 4 b c 185 UPDATE main t3 0 0 d e f 4 e f 186 } 187 188 do_preupdate_test 2.3.2 { 189 UPDATE t3 SET y=y||y WHERE z IN('c', 'f'); 190 } { 191 UPDATE main t3 0 0 4 b c 4 bb c 192 UPDATE main t3 0 0 4 e f 4 ee f 193 } 194 195 do_preupdate_test 2.3.3 { 196 UPDATE OR REPLACE t3 SET y='bb' WHERE z='f' 197 } { 198 DELETE main t3 0 0 4 bb c 199 UPDATE main t3 0 0 4 ee f 4 bb f 200 } 201 202 do_preupdate_test 2.3.4 { 203 UPDATE OR REPLACE t3 SET z=2 WHERE y=1; 204 } { 205 DELETE main t3 0 0 2 2 2 206 UPDATE main t3 0 0 1 1 1 1 1 2 207 } 208 209 do_preupdate_test 2.3.5 { 210 UPDATE OR REPLACE t3 SET z=2, y='bb' WHERE y=3; 211 } { 212 DELETE main t3 0 0 1 1 2 213 DELETE main t3 0 0 4 bb f 214 UPDATE main t3 0 0 3 3 3 3 bb 2 215 } 216 217 218 finish_test