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