gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/e_changes.test (about) 1 # 2011 October 28 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 # 12 13 set testdir [file dirname $argv0] 14 source $testdir/tester.tcl 15 set testprefix e_changes 16 17 # Like [do_execsql_test], except it appends the value returned by 18 # [db changes] to the result of executing the SQL script. 19 # 20 proc do_changes_test {tn sql res} { 21 uplevel [list \ 22 do_test $tn "concat \[execsql {$sql}\] \[db changes\]" $res 23 ] 24 } 25 26 27 #-------------------------------------------------------------------------- 28 # EVIDENCE-OF: R-58361-29089 The changes() function returns the number 29 # of database rows that were changed or inserted or deleted by the most 30 # recently completed INSERT, DELETE, or UPDATE statement, exclusive of 31 # statements in lower-level triggers. 32 # 33 do_execsql_test 1.0 { 34 CREATE TABLE t1(a, b); 35 CREATE TABLE t2(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID; 36 CREATE INDEX i1 ON t1(a); 37 CREATE INDEX i2 ON t2(y); 38 } 39 foreach {tn schema} { 40 1 { 41 CREATE TABLE t1(a, b); 42 CREATE INDEX i1 ON t1(b); 43 } 44 2 { 45 CREATE TABLE t1(a, b, PRIMARY KEY(a, b)) WITHOUT ROWID; 46 CREATE INDEX i1 ON t1(b); 47 } 48 } { 49 reset_db 50 execsql $schema 51 52 # Insert 1 row. 53 do_changes_test 1.$tn.1 { INSERT INTO t1 VALUES(0, 0) } 1 54 55 # Insert 10 rows. 56 do_changes_test 1.$tn.2 { 57 WITH rows(i, j) AS ( 58 SELECT 1, 1 UNION ALL SELECT i+1, j+i FROM rows WHERE i<10 59 ) 60 INSERT INTO t1 SELECT * FROM rows 61 } 10 62 63 # Modify 5 rows. 64 do_changes_test 1.$tn.3 { 65 UPDATE t1 SET b=b+1 WHERE a<5; 66 } 5 67 68 # Delete 4 rows 69 do_changes_test 1.$tn.4 { 70 DELETE FROM t1 WHERE a>6 71 } 4 72 73 # Check the "on the database connecton specified" part of hte 74 # requirement - changes made by other connections do not show up in 75 # the return value of sqlite3_changes(). 76 do_test 1.$tn.5 { 77 sqlite3 db2 test.db 78 execsql { INSERT INTO t1 VALUES(-1, -1) } db2 79 db2 changes 80 } 1 81 do_test 1.$tn.6 { 82 db changes 83 } 4 84 db2 close 85 86 # Test that statements that modify no rows because they hit UNIQUE 87 # constraints set the sqlite3_changes() value to 0. Regardless of 88 # whether or not they are executed inside an explicit transaction. 89 # 90 # 1.$tn.8-9: outside of a transaction 91 # 1.$tn.10-12: inside a transaction 92 # 93 do_changes_test 1.$tn.7 { 94 CREATE UNIQUE INDEX i2 ON t1(a); 95 } 4 96 do_catchsql_test 1.$tn.8 { 97 INSERT INTO t1 VALUES('a', 0), ('b', 0), ('c', 0), (0, 11); 98 } {1 {UNIQUE constraint failed: t1.a}} 99 do_test 1.$tn.9 { db changes } 0 100 do_catchsql_test 1.$tn.10 { 101 BEGIN; 102 INSERT INTO t1 VALUES('a', 0), ('b', 0), ('c', 0), (0, 11); 103 } {1 {UNIQUE constraint failed: t1.a}} 104 do_test 1.$tn.11 { db changes } 0 105 do_changes_test 1.$tn.12 COMMIT 0 106 107 } 108 109 110 #-------------------------------------------------------------------------- 111 # X-EVIDENCE-OF: R-44877-05564 Executing any other type of SQL statement 112 # does not modify the value returned by this function. 113 # 114 reset_db 115 do_changes_test 2.1 { CREATE TABLE t1(x) } 0 116 do_changes_test 2.2 { 117 WITH d(y) AS (SELECT 1 UNION ALL SELECT y+1 FROM d WHERE y<47) 118 INSERT INTO t1 SELECT y FROM d; 119 } 47 120 121 # The statement above set changes() to 47. Check that none of the following 122 # modify this. 123 do_changes_test 2.3 { SELECT count(x) FROM t1 } {47 47} 124 do_changes_test 2.4 { DROP TABLE t1 } 47 125 do_changes_test 2.5 { CREATE TABLE t1(x) } 47 126 ifcapable altertable { 127 do_changes_test 2.6 { ALTER TABLE t1 ADD COLUMN b } 47 128 } 129 130 131 #-------------------------------------------------------------------------- 132 # EVIDENCE-OF: R-53938-27527 Only changes made directly by the INSERT, 133 # UPDATE or DELETE statement are considered - auxiliary changes caused 134 # by triggers, foreign key actions or REPLACE constraint resolution are 135 # not counted. 136 # 137 # 3.1.*: triggers 138 # 3.2.*: foreign key actions 139 # 3.3.*: replace constraints 140 # 141 reset_db 142 do_execsql_test 3.1.0 { 143 CREATE TABLE log(x); 144 CREATE TABLE p1(one PRIMARY KEY, two); 145 146 CREATE TRIGGER tr_ai AFTER INSERT ON p1 BEGIN 147 INSERT INTO log VALUES('insert'); 148 END; 149 CREATE TRIGGER tr_bd BEFORE DELETE ON p1 BEGIN 150 INSERT INTO log VALUES('delete'); 151 END; 152 CREATE TRIGGER tr_au AFTER UPDATE ON p1 BEGIN 153 INSERT INTO log VALUES('update'); 154 END; 155 156 } 157 158 do_changes_test 3.1.1 { 159 INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C'); 160 } 3 161 do_changes_test 3.1.2 { 162 UPDATE p1 SET two = two||two; 163 } 3 164 do_changes_test 3.1.3 { 165 DELETE FROM p1 WHERE one IN ('a', 'c'); 166 } 2 167 do_execsql_test 3.1.4 { 168 -- None of the inserts on table log were counted. 169 SELECT count(*) FROM log 170 } 8 171 172 do_execsql_test 3.2.0 { 173 DELETE FROM p1; 174 INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C'); 175 176 CREATE TABLE c1(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET NULL); 177 CREATE TABLE c2(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE SET DEFAULT); 178 CREATE TABLE c3(a, b, FOREIGN KEY(a) REFERENCES p1 ON DELETE CASCADE); 179 INSERT INTO c1 VALUES('a', 'aaa'); 180 INSERT INTO c2 VALUES('b', 'bbb'); 181 INSERT INTO c3 VALUES('c', 'ccc'); 182 183 INSERT INTO p1 VALUES('d', 'D'), ('e', 'E'), ('f', 'F'); 184 CREATE TABLE c4(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET NULL); 185 CREATE TABLE c5(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE SET DEFAULT); 186 CREATE TABLE c6(a, b, FOREIGN KEY(a) REFERENCES p1 ON UPDATE CASCADE); 187 INSERT INTO c4 VALUES('d', 'aaa'); 188 INSERT INTO c5 VALUES('e', 'bbb'); 189 INSERT INTO c6 VALUES('f', 'ccc'); 190 191 PRAGMA foreign_keys = ON; 192 } 193 194 do_changes_test 3.2.1 { DELETE FROM p1 WHERE one = 'a' } 1 195 do_changes_test 3.2.2 { DELETE FROM p1 WHERE one = 'b' } 1 196 do_changes_test 3.2.3 { DELETE FROM p1 WHERE one = 'c' } 1 197 do_execsql_test 3.2.4 { 198 SELECT * FROM c1; 199 SELECT * FROM c2; 200 SELECT * FROM c3; 201 } {{} aaa {} bbb} 202 203 do_changes_test 3.2.5 { UPDATE p1 SET one = 'g' WHERE one = 'd' } 1 204 do_changes_test 3.2.6 { UPDATE p1 SET one = 'h' WHERE one = 'e' } 1 205 do_changes_test 3.2.7 { UPDATE p1 SET one = 'i' WHERE one = 'f' } 1 206 do_execsql_test 3.2.8 { 207 SELECT * FROM c4; 208 SELECT * FROM c5; 209 SELECT * FROM c6; 210 } {{} aaa {} bbb i ccc} 211 212 do_execsql_test 3.3.0 { 213 CREATE TABLE r1(a UNIQUE, b UNIQUE); 214 INSERT INTO r1 VALUES('i', 'i'); 215 INSERT INTO r1 VALUES('ii', 'ii'); 216 INSERT INTO r1 VALUES('iii', 'iii'); 217 INSERT INTO r1 VALUES('iv', 'iv'); 218 INSERT INTO r1 VALUES('v', 'v'); 219 INSERT INTO r1 VALUES('vi', 'vi'); 220 INSERT INTO r1 VALUES('vii', 'vii'); 221 } 222 223 do_changes_test 3.3.1 { INSERT OR REPLACE INTO r1 VALUES('i', 1) } 1 224 do_changes_test 3.3.2 { INSERT OR REPLACE INTO r1 VALUES('iv', 'v') } 1 225 do_changes_test 3.3.3 { UPDATE OR REPLACE r1 SET b='v' WHERE a='iii' } 1 226 do_changes_test 3.3.4 { UPDATE OR REPLACE r1 SET b='vi',a='vii' WHERE a='ii' } 1 227 do_execsql_test 3.3.5 { 228 SELECT * FROM r1 ORDER BY a; 229 } {i 1 iii v vii vi} 230 231 232 #-------------------------------------------------------------------------- 233 # EVIDENCE-OF: R-09813-48563 The value returned by sqlite3_changes() 234 # immediately after an INSERT, UPDATE or DELETE statement run on a view 235 # is always zero. 236 # 237 reset_db 238 do_execsql_test 4.1 { 239 CREATE TABLE log(log); 240 CREATE TABLE t1(x, y); 241 INSERT INTO t1 VALUES(1, 2); 242 INSERT INTO t1 VALUES(3, 4); 243 INSERT INTO t1 VALUES(5, 6); 244 245 CREATE VIEW v1 AS SELECT * FROM t1; 246 CREATE TRIGGER v1_i INSTEAD OF INSERT ON v1 BEGIN 247 INSERT INTO log VALUES('insert'); 248 END; 249 CREATE TRIGGER v1_u INSTEAD OF UPDATE ON v1 BEGIN 250 INSERT INTO log VALUES('update'), ('update'); 251 END; 252 CREATE TRIGGER v1_d INSTEAD OF DELETE ON v1 BEGIN 253 INSERT INTO log VALUES('delete'), ('delete'), ('delete'); 254 END; 255 } 256 257 do_changes_test 4.2.1 { INSERT INTO t1 SELECT * FROM t1 } 3 258 do_changes_test 4.2.2 { INSERT INTO v1 VALUES(1, 2) } 0 259 260 do_changes_test 4.3.1 { INSERT INTO t1 SELECT * FROM t1 } 6 261 do_changes_test 4.3.2 { UPDATE v1 SET y='xyz' WHERE x=1 } 0 262 263 do_changes_test 4.4.1 { INSERT INTO t1 SELECT * FROM t1 } 12 264 do_changes_test 4.4.2 { DELETE FROM v1 WHERE x=5 } 0 265 266 267 #-------------------------------------------------------------------------- 268 # EVIDENCE-OF: R-32918-61474 Before entering a trigger program the value 269 # returned by sqlite3_changes() function is saved. After the trigger 270 # program has finished, the original value is restored. 271 # 272 reset_db 273 db func my_changes my_changes 274 set ::changes [list] 275 proc my_changes {x} { 276 set res [db changes] 277 lappend ::changes $x $res 278 return $res 279 } 280 281 do_execsql_test 5.1.0 { 282 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 283 CREATE TABLE t2(x); 284 INSERT INTO t1 VALUES(1, NULL); 285 INSERT INTO t1 VALUES(2, NULL); 286 INSERT INTO t1 VALUES(3, NULL); 287 CREATE TRIGGER AFTER UPDATE ON t1 BEGIN 288 INSERT INTO t2 VALUES('a'), ('b'), ('c'); 289 SELECT my_changes('trigger'); 290 END; 291 } 292 293 do_execsql_test 5.1.1 { 294 INSERT INTO t2 VALUES('a'), ('b'); 295 UPDATE t1 SET b = my_changes('update'); 296 SELECT * FROM t1; 297 } {1 2 2 2 3 2} 298 299 # Value is being restored to "2" when the trigger program exits. 300 do_test 5.1.2 { 301 set ::changes 302 } {update 2 trigger 3 update 2 trigger 3 update 2 trigger 3} 303 304 305 reset_db 306 do_execsql_test 5.2.0 { 307 CREATE TABLE t1(a, b); 308 CREATE TABLE log(x); 309 INSERT INTO t1 VALUES(1, 0); 310 INSERT INTO t1 VALUES(2, 0); 311 INSERT INTO t1 VALUES(3, 0); 312 CREATE TRIGGER t1_a_u AFTER UPDATE ON t1 BEGIN 313 INSERT INTO log VALUES(old.b || ' -> ' || new.b || ' c = ' || changes() ); 314 END; 315 CREATE TABLE t2(a); 316 INSERT INTO t2 VALUES(1), (2), (3); 317 UPDATE t1 SET b = changes(); 318 } 319 do_execsql_test 5.2.1 { 320 SELECT * FROM t1; 321 } {1 3 2 3 3 3} 322 do_execsql_test 5.2.2 { 323 SELECT * FROM log; 324 } {{0 -> 3 c = 3} {0 -> 3 c = 3} {0 -> 3 c = 3}} 325 326 327 #-------------------------------------------------------------------------- 328 # EVIDENCE-OF: R-17146-37073 Within a trigger program each INSERT, 329 # UPDATE and DELETE statement sets the value returned by 330 # sqlite3_changes() upon completion as normal. Of course, this value 331 # will not include any changes performed by sub-triggers, as the 332 # sqlite3_changes() value will be saved and restored after each 333 # sub-trigger has run. 334 reset_db 335 do_execsql_test 6.0 { 336 337 CREATE TABLE t1(a, b); 338 CREATE TABLE t2(a, b); 339 CREATE TABLE t3(a, b); 340 CREATE TABLE log(x); 341 342 CREATE TRIGGER t1_i BEFORE INSERT ON t1 BEGIN 343 INSERT INTO t2 VALUES(new.a, new.b), (new.a, new.b); 344 INSERT INTO log VALUES('t2->' || changes()); 345 END; 346 347 CREATE TRIGGER t2_i AFTER INSERT ON t2 BEGIN 348 INSERT INTO t3 VALUES(new.a, new.b), (new.a, new.b), (new.a, new.b); 349 INSERT INTO log VALUES('t3->' || changes()); 350 END; 351 352 CREATE TRIGGER t1_u AFTER UPDATE ON t1 BEGIN 353 UPDATE t2 SET b=new.b WHERE a=old.a; 354 INSERT INTO log VALUES('t2->' || changes()); 355 END; 356 357 CREATE TRIGGER t2_u BEFORE UPDATE ON t2 BEGIN 358 UPDATE t3 SET b=new.b WHERE a=old.a; 359 INSERT INTO log VALUES('t3->' || changes()); 360 END; 361 362 CREATE TRIGGER t1_d AFTER DELETE ON t1 BEGIN 363 DELETE FROM t2 WHERE a=old.a AND b=old.b; 364 INSERT INTO log VALUES('t2->' || changes()); 365 END; 366 367 CREATE TRIGGER t2_d BEFORE DELETE ON t2 BEGIN 368 DELETE FROM t3 WHERE a=old.a AND b=old.b; 369 INSERT INTO log VALUES('t3->' || changes()); 370 END; 371 } 372 373 do_changes_test 6.1 { 374 INSERT INTO t1 VALUES('+', 'o'); 375 SELECT * FROM log; 376 } {t3->3 t3->3 t2->2 1} 377 378 do_changes_test 6.2 { 379 DELETE FROM log; 380 UPDATE t1 SET b='*'; 381 SELECT * FROM log; 382 } {t3->6 t3->6 t2->2 1} 383 384 do_changes_test 6.3 { 385 DELETE FROM log; 386 DELETE FROM t1; 387 SELECT * FROM log; 388 } {t3->6 t3->0 t2->2 1} 389 390 391 #-------------------------------------------------------------------------- 392 # EVIDENCE-OF: R-43399-09409 This means that if the changes() SQL 393 # function (or similar) is used by the first INSERT, UPDATE or DELETE 394 # statement within a trigger, it returns the value as set when the 395 # calling statement began executing. 396 # 397 # EVIDENCE-OF: R-53215-27584 If it is used by the second or subsequent 398 # such statement within a trigger program, the value returned reflects 399 # the number of rows modified by the previous INSERT, UPDATE or DELETE 400 # statement within the same trigger. 401 # 402 reset_db 403 do_execsql_test 7.1 { 404 CREATE TABLE q1(t); 405 CREATE TABLE q2(u, v); 406 CREATE TABLE q3(w); 407 408 CREATE TRIGGER q2_insert BEFORE INSERT ON q2 BEGIN 409 410 /* changes() returns value from previous I/U/D in callers context */ 411 INSERT INTO q1 VALUES('1:' || changes()); 412 413 /* changes() returns value of previous I/U/D in this context */ 414 INSERT INTO q3 VALUES(changes()), (2), (3); 415 INSERT INTO q1 VALUES('2:' || changes()); 416 INSERT INTO q3 VALUES(changes() + 3), (changes()+4); 417 SELECT 'this does not affect things!'; 418 INSERT INTO q1 VALUES('3:' || changes()); 419 UPDATE q3 SET w = w+10 WHERE w%2; 420 INSERT INTO q1 VALUES('4:' || changes()); 421 DELETE FROM q3; 422 INSERT INTO q1 VALUES('5:' || changes()); 423 END; 424 } 425 426 do_execsql_test 7.2 { 427 INSERT INTO q2 VALUES('x', 'y'); 428 SELECT * FROM q1; 429 } { 430 1:0 2:3 3:2 4:3 5:5 431 } 432 433 do_execsql_test 7.3 { 434 DELETE FROM q1; 435 INSERT INTO q2 VALUES('x', 'y'); 436 SELECT * FROM q1; 437 } { 438 1:5 2:3 3:2 4:3 5:5 439 } 440 441 442 443 finish_test