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 13set testdir [file dirname $argv0] 14source $testdir/tester.tcl 15set 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# 20proc 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# 33do_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} 39foreach {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# 114reset_db 115do_changes_test 2.1 { CREATE TABLE t1(x) } 0 116do_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. 123do_changes_test 2.3 { SELECT count(x) FROM t1 } {47 47} 124do_changes_test 2.4 { DROP TABLE t1 } 47 125do_changes_test 2.5 { CREATE TABLE t1(x) } 47 126ifcapable 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# 141reset_db 142do_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 158do_changes_test 3.1.1 { 159 INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C'); 160} 3 161do_changes_test 3.1.2 { 162 UPDATE p1 SET two = two||two; 163} 3 164do_changes_test 3.1.3 { 165 DELETE FROM p1 WHERE one IN ('a', 'c'); 166} 2 167do_execsql_test 3.1.4 { 168 -- None of the inserts on table log were counted. 169 SELECT count(*) FROM log 170} 8 171 172do_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 194do_changes_test 3.2.1 { DELETE FROM p1 WHERE one = 'a' } 1 195do_changes_test 3.2.2 { DELETE FROM p1 WHERE one = 'b' } 1 196do_changes_test 3.2.3 { DELETE FROM p1 WHERE one = 'c' } 1 197do_execsql_test 3.2.4 { 198 SELECT * FROM c1; 199 SELECT * FROM c2; 200 SELECT * FROM c3; 201} {{} aaa {} bbb} 202 203do_changes_test 3.2.5 { UPDATE p1 SET one = 'g' WHERE one = 'd' } 1 204do_changes_test 3.2.6 { UPDATE p1 SET one = 'h' WHERE one = 'e' } 1 205do_changes_test 3.2.7 { UPDATE p1 SET one = 'i' WHERE one = 'f' } 1 206do_execsql_test 3.2.8 { 207 SELECT * FROM c4; 208 SELECT * FROM c5; 209 SELECT * FROM c6; 210} {{} aaa {} bbb i ccc} 211 212do_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 223do_changes_test 3.3.1 { INSERT OR REPLACE INTO r1 VALUES('i', 1) } 1 224do_changes_test 3.3.2 { INSERT OR REPLACE INTO r1 VALUES('iv', 'v') } 1 225do_changes_test 3.3.3 { UPDATE OR REPLACE r1 SET b='v' WHERE a='iii' } 1 226do_changes_test 3.3.4 { UPDATE OR REPLACE r1 SET b='vi',a='vii' WHERE a='ii' } 1 227do_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# 237reset_db 238do_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 257do_changes_test 4.2.1 { INSERT INTO t1 SELECT * FROM t1 } 3 258do_changes_test 4.2.2 { INSERT INTO v1 VALUES(1, 2) } 0 259 260do_changes_test 4.3.1 { INSERT INTO t1 SELECT * FROM t1 } 6 261do_changes_test 4.3.2 { UPDATE v1 SET y='xyz' WHERE x=1 } 0 262 263do_changes_test 4.4.1 { INSERT INTO t1 SELECT * FROM t1 } 12 264do_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# 272reset_db 273db func my_changes my_changes 274set ::changes [list] 275proc my_changes {x} { 276 set res [db changes] 277 lappend ::changes $x $res 278 return $res 279} 280 281do_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 293do_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. 300do_test 5.1.2 { 301 set ::changes 302} {update 2 trigger 3 update 2 trigger 3 update 2 trigger 3} 303 304 305reset_db 306do_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} 319do_execsql_test 5.2.1 { 320 SELECT * FROM t1; 321} {1 3 2 3 3 3} 322do_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. 334reset_db 335do_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 373do_changes_test 6.1 { 374 INSERT INTO t1 VALUES('+', 'o'); 375 SELECT * FROM log; 376} {t3->3 t3->3 t2->2 1} 377 378do_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 384do_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# 402reset_db 403do_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 426do_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 433do_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 443finish_test 444