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