test/e_changes.test

   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