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-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 #
  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 # 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 do_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 #
 139 reset_db
 140 do_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
 156 do_changes_test 3.1.1 {
 157   INSERT INTO p1 VALUES('a', 'A'), ('b', 'B'), ('c', 'C');
 158 } 3
 159 do_changes_test 3.1.2 {
 160   UPDATE p1 SET two = two||two;
 161 } 3
 162 do_changes_test 3.1.3 {
 163   DELETE FROM p1 WHERE one IN ('a', 'c');
 164 } 2
 165 do_execsql_test 3.1.4 {
 166   -- None of the inserts on table log were counted.
 167   SELECT count(*) FROM log
 168 } 8
 169
 170 do_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
 192 do_changes_test 3.2.1 { DELETE FROM p1 WHERE one = 'a' } 1
 193 do_changes_test 3.2.2 { DELETE FROM p1 WHERE one = 'b' } 1
 194 do_changes_test 3.2.3 { DELETE FROM p1 WHERE one = 'c' } 1
 195 do_execsql_test 3.2.4 {
 196   SELECT * FROM c1;
 197   SELECT * FROM c2;
 198   SELECT * FROM c3;
 199 } {{} aaa {} bbb}
 200
 201 do_changes_test 3.2.5 { UPDATE p1 SET one = 'g' WHERE one = 'd' } 1
 202 do_changes_test 3.2.6 { UPDATE p1 SET one = 'h' WHERE one = 'e' } 1
 203 do_changes_test 3.2.7 { UPDATE p1 SET one = 'i' WHERE one = 'f' } 1
 204 do_execsql_test 3.2.8 {
 205   SELECT * FROM c4;
 206   SELECT * FROM c5;
 207   SELECT * FROM c6;
 208 } {{} aaa {} bbb i ccc}
 209
 210 do_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
 221 do_changes_test 3.3.1 { INSERT OR REPLACE INTO r1 VALUES('i', 1)    }   1
 222 do_changes_test 3.3.2 { INSERT OR REPLACE INTO r1 VALUES('iv', 'v') }   1
 223 do_changes_test 3.3.3 { UPDATE OR REPLACE r1 SET b='v' WHERE a='iii' }  1
 224 do_changes_test 3.3.4 { UPDATE OR REPLACE r1 SET b='vi',a='vii' WHERE a='ii' } 1
 225 do_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 #
 235 reset_db
 236 do_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
 255 do_changes_test 4.2.1 { INSERT INTO t1 SELECT * FROM t1 }  3
 256 do_changes_test 4.2.2 { INSERT INTO v1 VALUES(1, 2) }      0
 257
 258 do_changes_test 4.3.1 { INSERT INTO t1 SELECT * FROM t1 }  6
 259 do_changes_test 4.3.2 { UPDATE v1 SET y='xyz' WHERE x=1 }  0
 260
 261 do_changes_test 4.4.1 { INSERT INTO t1 SELECT * FROM t1 } 12
 262 do_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 #
 270 reset_db
 271 db func my_changes my_changes
 272 set ::changes [list]
 273 proc my_changes {x} {
 274   set res [db changes]
 275   lappend ::changes $x $res
 276   return $res
 277 }
 278
 279 do_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
 291 do_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.
 298 do_test 5.1.2 {
 299   set ::changes
 300 } {update 2 trigger 3 update 2 trigger 3 update 2 trigger 3}
 301
 302
 303 reset_db
 304 do_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 }
 317 do_execsql_test 5.2.1 {
 318   SELECT * FROM t1;
 319 } {1 3 2 3 3 3}
 320 do_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.
 332 reset_db
 333 do_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
 371 do_changes_test 6.1 {
 372   INSERT INTO t1 VALUES('+', 'o');
 373   SELECT * FROM log;
 374 } {t3->3 t3->3 t2->2 1}
 375
 376 do_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
 382 do_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 #
 400 reset_db
 401 do_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
 424 do_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
 431 do_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
 441 finish_test