000001 # 2004 Jan 14
000002 #
000003 # The author disclaims copyright to this source code. In place of
000004 # a legal notice, here is a blessing:
000005 #
000006 # May you do good and not evil.
000007 # May you find forgiveness for yourself and forgive others.
000008 # May you share freely, never taking more than you give.
000009 #
000010 #***********************************************************************
000011 # This file implements regression tests for TCL interface to the
000012 # SQLite library.
000013 #
000014 # The focus of the tests in this file is the following interface:
000015 #
000016 # sqlite_commit_hook (tests hook-1..hook-3 inclusive)
000017 # sqlite_update_hook (tests hook-4-*)
000018 # sqlite_rollback_hook (tests hook-5.*)
000019 # sqlite_preupdate_hook (tests hook-7..hook-12)
000020 #
000021 # $Id: hook.test,v 1.15 2009/04/07 14:14:23 danielk1977 Exp $
000022
000023 set testdir [file dirname $argv0]
000024 source $testdir/tester.tcl
000025 set ::testprefix hook
000026
000027 do_test hook-1.2 {
000028 db commit_hook
000029 } {}
000030
000031
000032 do_test hook-3.1 {
000033 set commit_cnt 0
000034 proc commit_hook {} {
000035 incr ::commit_cnt
000036 return 0
000037 }
000038 db commit_hook ::commit_hook
000039 db commit_hook
000040 } {::commit_hook}
000041 do_test hook-3.2 {
000042 set commit_cnt
000043 } {0}
000044 do_test hook-3.3 {
000045 execsql {
000046 CREATE TABLE t2(a,b);
000047 }
000048 set commit_cnt
000049 } {1}
000050 do_test hook-3.4 {
000051 execsql {
000052 INSERT INTO t2 VALUES(1,2);
000053 INSERT INTO t2 SELECT a+1, b+1 FROM t2;
000054 INSERT INTO t2 SELECT a+2, b+2 FROM t2;
000055 }
000056 set commit_cnt
000057 } {4}
000058 do_test hook-3.5 {
000059 set commit_cnt {}
000060 proc commit_hook {} {
000061 set ::commit_cnt [execsql {SELECT * FROM t2}]
000062 return 0
000063 }
000064 execsql {
000065 INSERT INTO t2 VALUES(5,6);
000066 }
000067 set commit_cnt
000068 } {1 2 2 3 3 4 4 5 5 6}
000069 do_test hook-3.6 {
000070 set commit_cnt {}
000071 proc commit_hook {} {
000072 set ::commit_cnt [execsql {SELECT * FROM t2}]
000073 return 1
000074 }
000075 catchsql {
000076 INSERT INTO t2 VALUES(6,7);
000077 }
000078 } {1 {constraint failed}}
000079 verify_ex_errcode hook-3.6b SQLITE_CONSTRAINT_COMMITHOOK
000080 do_test hook-3.7 {
000081 set ::commit_cnt
000082 } {1 2 2 3 3 4 4 5 5 6 6 7}
000083 do_test hook-3.8 {
000084 execsql {SELECT * FROM t2}
000085 } {1 2 2 3 3 4 4 5 5 6}
000086
000087 # Test turnning off the commit hook
000088 #
000089 do_test hook-3.9 {
000090 db commit_hook {}
000091 set ::commit_cnt {}
000092 execsql {
000093 INSERT INTO t2 VALUES(7,8);
000094 }
000095 set ::commit_cnt
000096 } {}
000097
000098 # Ticket #3564.
000099 #
000100 do_test hook-3.10 {
000101 forcedelete test2.db test2.db-journal
000102 sqlite3 db2 test2.db
000103 proc commit_hook {} {
000104 set y [db2 one {SELECT y FROM t3 WHERE y>10}]
000105 return [expr {$y>10}]
000106 }
000107 db2 eval {CREATE TABLE t3(x,y)}
000108 db2 commit_hook commit_hook
000109 catchsql {INSERT INTO t3 VALUES(1,2)} db2
000110 catchsql {INSERT INTO t3 VALUES(11,12)} db2
000111 catchsql {INSERT INTO t3 VALUES(3,4)} db2
000112 db2 eval {
000113 SELECT * FROM t3 ORDER BY x;
000114 }
000115 } {1 2 3 4}
000116 db2 close
000117
000118
000119 #----------------------------------------------------------------------------
000120 # Tests for the update-hook.
000121 #
000122 # 4.1.* - Very simple tests. Test that the update hook is invoked correctly
000123 # for INSERT, DELETE and UPDATE statements, including DELETE
000124 # statements with no WHERE clause.
000125 # 4.2.* - Check that the update-hook is invoked for rows modified by trigger
000126 # bodies. Also that the database name is correctly reported when
000127 # an attached database is modified.
000128 # 4.3.* - Do some sorting, grouping, compound queries, population and
000129 # depopulation of indices, to make sure the update-hook is not
000130 # invoked incorrectly.
000131 #
000132 # EVIDENCE-OF: R-21999-45122 The sqlite3_update_hook() interface
000133 # registers a callback function with the database connection identified
000134 # by the first argument to be invoked whenever a row is updated,
000135 # inserted or deleted in a rowid table.
000136
000137 # Simple tests
000138 do_test hook-4.1.1a {
000139 catchsql {
000140 DROP TABLE t1;
000141 }
000142 unset -nocomplain ::update_hook
000143 set ::update_hook {}
000144 db update_hook [list lappend ::update_hook]
000145 #
000146 # EVIDENCE-OF: R-24531-54682 The update hook is not invoked when
000147 # internal system tables are modified (i.e. sqlite_sequence).
000148 #
000149 execsql {
000150 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
000151 CREATE TABLE t1w(a INT PRIMARY KEY, b) WITHOUT ROWID;
000152 }
000153 set ::update_hook
000154 } {}
000155 do_test hook-4.1.1b {
000156 execsql {
000157 INSERT INTO t1 VALUES(1, 'one');
000158 INSERT INTO t1 VALUES(2, 'two');
000159 INSERT INTO t1 VALUES(3, 'three');
000160 INSERT INTO t1w SELECT * FROM t1;
000161 }
000162 } {}
000163
000164 # EVIDENCE-OF: R-15506-57666 The second callback argument is one of
000165 # SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE, depending on the
000166 # operation that caused the callback to be invoked.
000167 #
000168 # EVIDENCE-OF: R-29213-61195 The third and fourth arguments to the
000169 # callback contain pointers to the database and table name containing
000170 # the affected row.
000171 #
000172 # EVIDENCE-OF: R-30809-57812 The final callback parameter is the rowid
000173 # of the row.
000174 #
000175 do_test hook-4.1.2 {
000176 set ::update_hook {}
000177 execsql {
000178 INSERT INTO t1 VALUES(4, 'four');
000179 DELETE FROM t1 WHERE b = 'two';
000180 UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;
000181 DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)
000182 }
000183 set ::update_hook
000184 } [list \
000185 INSERT main t1 4 \
000186 DELETE main t1 2 \
000187 UPDATE main t1 1 \
000188 UPDATE main t1 3 \
000189 DELETE main t1 1 \
000190 DELETE main t1 3 \
000191 DELETE main t1 4 \
000192 ]
000193
000194 # EVIDENCE-OF: R-61808-14344 The sqlite3_update_hook() interface does
000195 # not fire callbacks for changes to a WITHOUT ROWID table.
000196 #
000197 # EVIDENCE-OF: R-33257-44249 The update hook is not invoked when WITHOUT
000198 # ROWID tables are modified.
000199 #
000200 do_test hook-4.1.2w {
000201 set ::update_hook {}
000202 execsql {
000203 INSERT INTO t1w VALUES(4, 'four');
000204 DELETE FROM t1w WHERE b = 'two';
000205 UPDATE t1w SET b = '' WHERE a = 1 OR a = 3;
000206 DELETE FROM t1w WHERE 1; -- Avoid the truncate optimization (for now)
000207 }
000208 set ::update_hook
000209 } {}
000210
000211 ifcapable trigger {
000212 # Update hook is not invoked for changes to sqlite_master
000213 #
000214 do_test hook-4.1.3 {
000215 set ::update_hook {}
000216 execsql {
000217 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;
000218 }
000219 set ::update_hook
000220 } {}
000221 do_test hook-4.1.4 {
000222 set ::update_hook {}
000223 execsql {
000224 DROP TRIGGER r1;
000225 }
000226 set ::update_hook
000227 } {}
000228
000229 set ::update_hook {}
000230 do_test hook-4.2.1 {
000231 catchsql {
000232 DROP TABLE t2;
000233 }
000234 execsql {
000235 CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
000236 CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN
000237 INSERT INTO t2 VALUES(new.a, new.b);
000238 UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c;
000239 DELETE FROM t2 WHERE new.a = c;
000240 END;
000241 }
000242 } {}
000243 do_test hook-4.2.2 {
000244 execsql {
000245 INSERT INTO t1 VALUES(1, 'one');
000246 INSERT INTO t1 VALUES(2, 'two');
000247 }
000248 set ::update_hook
000249 } [list \
000250 INSERT main t1 1 \
000251 INSERT main t2 1 \
000252 UPDATE main t2 1 \
000253 DELETE main t2 1 \
000254 INSERT main t1 2 \
000255 INSERT main t2 2 \
000256 UPDATE main t2 2 \
000257 DELETE main t2 2 \
000258 ]
000259 } else {
000260 execsql {
000261 INSERT INTO t1 VALUES(1, 'one');
000262 INSERT INTO t1 VALUES(2, 'two');
000263 }
000264 }
000265
000266 # Update-hook + ATTACH
000267 set ::update_hook {}
000268 ifcapable attach {
000269 do_test hook-4.2.3 {
000270 forcedelete test2.db
000271 execsql {
000272 ATTACH 'test2.db' AS aux;
000273 CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);
000274 INSERT INTO aux.t3 SELECT * FROM t1;
000275 UPDATE t3 SET b = 'two or so' WHERE a = 2;
000276 DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)
000277 }
000278 set ::update_hook
000279 } [list \
000280 INSERT aux t3 1 \
000281 INSERT aux t3 2 \
000282 UPDATE aux t3 2 \
000283 DELETE aux t3 1 \
000284 DELETE aux t3 2 \
000285 ]
000286 }
000287
000288 ifcapable trigger {
000289 execsql {
000290 DROP TRIGGER t1_trigger;
000291 }
000292 }
000293
000294 # Test that other vdbe operations involving btree structures do not
000295 # incorrectly invoke the update-hook.
000296 set ::update_hook {}
000297 do_test hook-4.3.1 {
000298 execsql {
000299 CREATE INDEX t1_i ON t1(b);
000300 INSERT INTO t1 VALUES(3, 'three');
000301 UPDATE t1 SET b = '';
000302 DELETE FROM t1 WHERE a > 1;
000303 }
000304 set ::update_hook
000305 } [list \
000306 INSERT main t1 3 \
000307 UPDATE main t1 1 \
000308 UPDATE main t1 2 \
000309 UPDATE main t1 3 \
000310 DELETE main t1 2 \
000311 DELETE main t1 3 \
000312 ]
000313 set ::update_hook {}
000314 ifcapable compound&&attach {
000315 do_test hook-4.3.2 {
000316 execsql {
000317 SELECT * FROM t1 UNION SELECT * FROM t3;
000318 SELECT * FROM t1 UNION ALL SELECT * FROM t3;
000319 SELECT * FROM t1 INTERSECT SELECT * FROM t3;
000320 SELECT * FROM t1 EXCEPT SELECT * FROM t3;
000321 SELECT * FROM t1 ORDER BY b;
000322 SELECT * FROM t1 GROUP BY b;
000323 }
000324 set ::update_hook
000325 } [list]
000326 }
000327
000328 do_test hook-4.4 {
000329 execsql {
000330 CREATE TABLE t4(a UNIQUE, b);
000331 INSERT INTO t4 VALUES(1, 'a');
000332 INSERT INTO t4 VALUES(2, 'b');
000333 }
000334 set ::update_hook [list]
000335 execsql {
000336 REPLACE INTO t4 VALUES(1, 'c');
000337 }
000338 set ::update_hook
000339 } [list INSERT main t4 3 ]
000340 do_execsql_test hook-4.4.1 {
000341 SELECT * FROM t4 ORDER BY a;
000342 } {1 c 2 b}
000343 do_test hook-4.4.2 {
000344 set ::update_hook [list]
000345 execsql {
000346 PRAGMA recursive_triggers = on;
000347 REPLACE INTO t4 VALUES(1, 'd');
000348 }
000349 set ::update_hook
000350 } [list INSERT main t4 4 ]
000351 do_execsql_test hook-4.4.3 {
000352 SELECT * FROM t4 ORDER BY a;
000353 } {1 d 2 b}
000354
000355 db update_hook {}
000356 #
000357 #----------------------------------------------------------------------------
000358
000359 #----------------------------------------------------------------------------
000360 # Test the rollback-hook. The rollback-hook is a bit more complicated than
000361 # either the commit or update hooks because a rollback can happen
000362 # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or
000363 # error condition).
000364 #
000365 # hook-5.1.* - Test explicit rollbacks.
000366 # hook-5.2.* - Test implicit rollbacks caused by constraint failure.
000367 #
000368 # hook-5.3.* - Test implicit rollbacks caused by IO errors.
000369 # hook-5.4.* - Test implicit rollbacks caused by malloc() failure.
000370 # hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook
000371 # not be called for these?
000372 #
000373
000374 do_test hook-5.0 {
000375 # Configure the rollback hook to increment global variable
000376 # $::rollback_hook each time it is invoked.
000377 set ::rollback_hook 0
000378 db rollback_hook [list incr ::rollback_hook]
000379 } {}
000380
000381 # Test explicit rollbacks. Not much can really go wrong here.
000382 #
000383 do_test hook-5.1.1 {
000384 set ::rollback_hook 0
000385 execsql {
000386 BEGIN;
000387 ROLLBACK;
000388 }
000389 set ::rollback_hook
000390 } {1}
000391
000392 # Test implicit rollbacks caused by constraints.
000393 #
000394 do_test hook-5.2.1 {
000395 set ::rollback_hook 0
000396 catchsql {
000397 DROP TABLE t1;
000398 CREATE TABLE t1(a PRIMARY KEY, b);
000399 INSERT INTO t1 VALUES('one', 'I');
000400 INSERT INTO t1 VALUES('one', 'I');
000401 }
000402 set ::rollback_hook
000403 } {1}
000404 do_test hook-5.2.2 {
000405 # Check that the INSERT transaction above really was rolled back.
000406 execsql {
000407 SELECT count(*) FROM t1;
000408 }
000409 } {1}
000410
000411 #
000412 # End rollback-hook testing.
000413 #----------------------------------------------------------------------------
000414
000415 #----------------------------------------------------------------------------
000416 # Test that if a commit-hook returns non-zero (causing a rollback), the
000417 # rollback-hook is invoked.
000418 #
000419 proc commit_hook {} {
000420 lappend ::hooks COMMIT
000421 return 1
000422 }
000423 proc rollback_hook {} {
000424 lappend ::hooks ROLLBACK
000425 }
000426 do_test hook-6.1 {
000427 set ::hooks [list]
000428 db commit_hook commit_hook
000429 db rollback_hook rollback_hook
000430 catchsql {
000431 BEGIN;
000432 INSERT INTO t1 VALUES('two', 'II');
000433 COMMIT;
000434 }
000435 execsql { SELECT * FROM t1 }
000436 } {one I}
000437 do_test hook-6.2 {
000438 set ::hooks
000439 } {COMMIT ROLLBACK}
000440 unset ::hooks
000441
000442 #----------------------------------------------------------------------------
000443 # The following tests - hook-7.* - test the pre-update hook.
000444 #
000445 ifcapable !preupdate {
000446 finish_test
000447 return
000448 }
000449 #
000450 # 7.1.1 - INSERT statement.
000451 # 7.1.2 - INSERT INTO ... SELECT statement.
000452 # 7.1.3 - REPLACE INTO ... (rowid conflict)
000453 # 7.1.4 - REPLACE INTO ... (other index conflicts)
000454 # 7.1.5 - REPLACE INTO ... (both rowid and other index conflicts)
000455 #
000456 # 7.2.1 - DELETE statement.
000457 # 7.2.2 - DELETE statement that uses the truncate optimization.
000458 #
000459 # 7.3.1 - UPDATE statement.
000460 # 7.3.2 - UPDATE statement that modifies the rowid.
000461 # 7.3.3 - UPDATE OR REPLACE ... (rowid conflict).
000462 # 7.3.4 - UPDATE OR REPLACE ... (other index conflicts)
000463 # 7.3.4 - UPDATE OR REPLACE ... (both rowid and other index conflicts)
000464 #
000465 # 7.4.1 - Test that the pre-update-hook is invoked only once if a row being
000466 # deleted is removed by a BEFORE trigger.
000467 #
000468 # 7.4.2 - Test that the pre-update-hook is invoked if a BEFORE trigger
000469 # removes a row being updated. In this case the update hook should
000470 # be invoked with SQLITE_INSERT as the opcode when inserting the
000471 # new version of the row.
000472 #
000473 # TODO: Short records (those created before a column is added to a table
000474 # using ALTER TABLE)
000475 #
000476
000477 proc do_preupdate_test {tn sql x} {
000478 set X [list]
000479 foreach elem $x {lappend X $elem}
000480 uplevel do_test $tn [list "
000481 set ::preupdate \[list\]
000482 execsql { $sql }
000483 set ::preupdate
000484 "] [list $X]
000485 }
000486
000487 proc preupdate_hook {args} {
000488 set type [lindex $args 0]
000489 eval lappend ::preupdate $args
000490 if {$type != "INSERT"} {
000491 for {set i 0} {$i < [db preupdate count]} {incr i} {
000492 lappend ::preupdate [db preupdate old $i]
000493 }
000494 }
000495 if {$type != "DELETE"} {
000496 for {set i 0} {$i < [db preupdate count]} {incr i} {
000497 set rc [catch { db preupdate new $i } v]
000498 lappend ::preupdate $v
000499 }
000500 }
000501 }
000502
000503 db close
000504 forcedelete test.db
000505 sqlite3 db test.db
000506 db preupdate hook preupdate_hook
000507
000508 # Set up a schema to use for tests 7.1.* to 7.3.*.
000509 do_execsql_test 7.0 {
000510 CREATE TABLE t1(a, b);
000511 CREATE TABLE t2(x, y);
000512 CREATE TABLE t3(i, j, UNIQUE(i));
000513
000514 INSERT INTO t2 VALUES('a', 'b');
000515 INSERT INTO t2 VALUES('c', 'd');
000516
000517 INSERT INTO t3 VALUES(4, 16);
000518 INSERT INTO t3 VALUES(5, 25);
000519 INSERT INTO t3 VALUES(6, 36);
000520 }
000521
000522 do_preupdate_test 7.1.1 {
000523 INSERT INTO t1 VALUES('x', 'y')
000524 } {INSERT main t1 1 1 x y}
000525
000526 # 7.1.2.1 does not use the xfer optimization. 7.1.2.2 does.
000527 do_preupdate_test 7.1.2.1 {
000528 INSERT INTO t1 SELECT y, x FROM t2;
000529 } {INSERT main t1 2 2 b a INSERT main t1 3 3 d c}
000530 do_preupdate_test 7.1.2.2 {
000531 INSERT INTO t1 SELECT * FROM t2;
000532 } {INSERT main t1 4 4 a b INSERT main t1 5 5 c d}
000533
000534 do_preupdate_test 7.1.3 {
000535 REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1);
000536 } {
000537 DELETE main t1 1 1 x y
000538 INSERT main t1 1 1 1 1
000539 }
000540
000541 do_preupdate_test 7.1.4 {
000542 REPLACE INTO t3 VALUES(4, NULL);
000543 } {
000544 DELETE main t3 1 1 4 16
000545 INSERT main t3 4 4 4 {}
000546 }
000547
000548 do_preupdate_test 7.1.5 {
000549 REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL);
000550 } {
000551 DELETE main t3 2 2 5 25
000552 DELETE main t3 3 3 6 36
000553 INSERT main t3 2 2 6 {}
000554 }
000555
000556 do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5}
000557
000558 do_preupdate_test 7.2.1 {
000559 DELETE FROM t1 WHERE rowid = 3
000560 } {
000561 DELETE main t1 3 3 d c
000562 }
000563 do_preupdate_test 7.2.2 {
000564 DELETE FROM t1
000565 } {
000566 DELETE main t1 1 1 1 1
000567 DELETE main t1 2 2 b a
000568 DELETE main t1 4 4 a b
000569 DELETE main t1 5 5 c d
000570 }
000571
000572 do_execsql_test 7.3.0 {
000573 DELETE FROM t1;
000574 DELETE FROM t2;
000575 DELETE FROM t3;
000576
000577 INSERT INTO t2 VALUES('a', 'b');
000578 INSERT INTO t2 VALUES('c', 'd');
000579
000580 INSERT INTO t3 VALUES(4, 16);
000581 INSERT INTO t3 VALUES(5, 25);
000582 INSERT INTO t3 VALUES(6, 36);
000583 }
000584
000585 do_preupdate_test 7.3.1 {
000586 UPDATE t2 SET y = y||y;
000587 } {
000588 UPDATE main t2 1 1 a b a bb
000589 UPDATE main t2 2 2 c d c dd
000590 }
000591
000592 do_preupdate_test 7.3.2 {
000593 UPDATE t2 SET rowid = rowid-1;
000594 } {
000595 UPDATE main t2 1 0 a bb a bb
000596 UPDATE main t2 2 1 c dd c dd
000597 }
000598
000599 do_preupdate_test 7.3.3 {
000600 UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a'
000601 } {
000602 DELETE main t2 1 1 c dd
000603 UPDATE main t2 0 1 a bb a bb
000604 }
000605
000606 do_preupdate_test 7.3.4.1 {
000607 UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6
000608 } {
000609 DELETE main t3 2 2 5 25
000610 UPDATE main t3 3 3 6 36 5 36
000611 }
000612
000613 do_execsql_test 7.3.4.2 {
000614 INSERT INTO t3 VALUES(10, 100);
000615 SELECT rowid, * FROM t3;
000616 } {1 4 16 3 5 36 4 10 100}
000617
000618 do_preupdate_test 7.3.5 {
000619 UPDATE OR REPLACE t3 SET rowid = 1, i = 5 WHERE j = 100;
000620 } {
000621 DELETE main t3 1 1 4 16
000622 DELETE main t3 3 3 5 36
000623 UPDATE main t3 4 1 10 100 5 100
000624 }
000625
000626 do_execsql_test 7.4.1.0 {
000627 CREATE TABLE t4(a, b);
000628 INSERT INTO t4 VALUES('a', 1);
000629 INSERT INTO t4 VALUES('b', 2);
000630 INSERT INTO t4 VALUES('c', 3);
000631
000632 CREATE TRIGGER t4t BEFORE DELETE ON t4 BEGIN
000633 DELETE FROM t4 WHERE b = 1;
000634 END;
000635 }
000636
000637 do_preupdate_test 7.4.1.1 {
000638 DELETE FROM t4 WHERE b = 3
000639 } {
000640 DELETE main t4 1 1 a 1
000641 DELETE main t4 3 3 c 3
000642 }
000643
000644 do_execsql_test 7.4.1.2 {
000645 INSERT INTO t4(rowid, a, b) VALUES(1, 'a', 1);
000646 INSERT INTO t4(rowid, a, b) VALUES(3, 'c', 3);
000647 }
000648 do_preupdate_test 7.4.1.3 {
000649 DELETE FROM t4 WHERE b = 1
000650 } {
000651 DELETE main t4 1 1 a 1
000652 }
000653
000654 do_execsql_test 7.4.2.0 {
000655 CREATE TABLE t5(a, b);
000656 INSERT INTO t5 VALUES('a', 1);
000657 INSERT INTO t5 VALUES('b', 2);
000658 INSERT INTO t5 VALUES('c', 3);
000659
000660 CREATE TRIGGER t5t BEFORE UPDATE ON t5 BEGIN
000661 DELETE FROM t5 WHERE b = 1;
000662 END;
000663 }
000664 do_preupdate_test 7.4.2.1 {
000665 UPDATE t5 SET b = 4 WHERE a = 'c'
000666 } {
000667 DELETE main t5 1 1 a 1
000668 UPDATE main t5 3 3 c 3 c 4
000669 }
000670
000671 do_execsql_test 7.4.2.2 {
000672 INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1);
000673 }
000674
000675 do_preupdate_test 7.4.2.3 {
000676 UPDATE t5 SET b = 5 WHERE a = 'a'
000677 } {
000678 DELETE main t5 1 1 a 1
000679 }
000680
000681 ifcapable altertable {
000682 do_execsql_test 7.5.1.0 {
000683 CREATE TABLE t7(a, b);
000684 INSERT INTO t7 VALUES('one', 'two');
000685 INSERT INTO t7 VALUES('three', 'four');
000686 ALTER TABLE t7 ADD COLUMN c DEFAULT NULL;
000687 }
000688
000689 do_preupdate_test 7.5.1.1 {
000690 DELETE FROM t7 WHERE a = 'one'
000691 } {
000692 DELETE main t7 1 1 one two {}
000693 }
000694
000695 do_preupdate_test 7.5.1.2 {
000696 UPDATE t7 SET b = 'five'
000697 } {
000698 UPDATE main t7 2 2 three four {} three five {}
000699 }
000700
000701 do_execsql_test 7.5.2.0 {
000702 CREATE TABLE t8(a, b);
000703 INSERT INTO t8 VALUES('one', 'two');
000704 INSERT INTO t8 VALUES('three', 'four');
000705 ALTER TABLE t8 ADD COLUMN c DEFAULT 'xxx';
000706 }
000707 }
000708
000709 if 1 {
000710 # At time of writing, these two are broken. They demonstrate that the
000711 # sqlite3_preupdate_old() method does not handle the case where ALTER TABLE
000712 # has been used to add a column with a default value other than NULL.
000713 #
000714 # 2024-09-18: These are now fixed.
000715 #
000716 do_preupdate_test 7.5.2.1 {
000717 DELETE FROM t8 WHERE a = 'one'
000718 } {
000719 DELETE main t8 1 1 one two xxx
000720 }
000721 do_preupdate_test 7.5.2.2 {
000722 UPDATE t8 SET b = 'five'
000723 } {
000724 UPDATE main t8 2 2 three four xxx three five xxx
000725 }
000726 }
000727
000728 # This block of tests verifies that IPK values are correctly reported
000729 # by the sqlite3_preupdate_old() and sqlite3_preupdate_new() functions.
000730 #
000731 do_execsql_test 7.6.1 { CREATE TABLE t9(a, b INTEGER PRIMARY KEY, c) }
000732 do_preupdate_test 7.6.2 {
000733 INSERT INTO t9 VALUES(1, 2, 3);
000734 UPDATE t9 SET b = b+1, c = c+1;
000735 DELETE FROM t9 WHERE a = 1;
000736 } {
000737 INSERT main t9 2 2 1 2 3
000738 UPDATE main t9 2 3 1 2 3 1 3 4
000739 DELETE main t9 3 3 1 3 4
000740 }
000741
000742 #--------------------------------------------------------------------------
000743 # Test that the sqlite3_preupdate_depth() API seems to work.
000744 #
000745 proc preupdate_hook {args} {
000746 set type [lindex $args 0]
000747 eval lappend ::preupdate $args
000748 eval lappend ::preupdate [db preupdate depth]
000749
000750 if {$type != "INSERT"} {
000751 for {set i 0} {$i < [db preupdate count]} {incr i} {
000752 lappend ::preupdate [db preupdate old $i]
000753 }
000754 }
000755 if {$type != "DELETE"} {
000756 for {set i 0} {$i < [db preupdate count]} {incr i} {
000757 set rc [catch { db preupdate new $i } v]
000758 lappend ::preupdate $v
000759 }
000760 }
000761 }
000762
000763 db close
000764 forcedelete test.db
000765 sqlite3 db test.db
000766 db preupdate hook preupdate_hook
000767
000768 do_execsql_test 7.6.1 {
000769 CREATE TABLE t1(x PRIMARY KEY);
000770 CREATE TABLE t2(x PRIMARY KEY);
000771 CREATE TABLE t3(x PRIMARY KEY);
000772 CREATE TABLE t4(x PRIMARY KEY);
000773
000774 CREATE TRIGGER a AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
000775 CREATE TRIGGER b AFTER INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
000776 CREATE TRIGGER c AFTER INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
000777
000778 CREATE TRIGGER d AFTER UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
000779 CREATE TRIGGER e AFTER UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
000780 CREATE TRIGGER f AFTER UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
000781
000782 CREATE TRIGGER g AFTER DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
000783 CREATE TRIGGER h AFTER DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
000784 CREATE TRIGGER i AFTER DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
000785 }
000786
000787 do_preupdate_test 7.6.2 {
000788 INSERT INTO t1 VALUES('xyz');
000789 } {
000790 INSERT main t1 1 1 0 xyz
000791 INSERT main t2 1 1 1 xyz
000792 INSERT main t3 1 1 2 xyz
000793 INSERT main t4 1 1 3 xyz
000794 }
000795 do_preupdate_test 7.6.3 {
000796 UPDATE t1 SET x = 'abc';
000797 } {
000798 UPDATE main t1 1 1 0 xyz abc
000799 UPDATE main t2 1 1 1 xyz abc
000800 UPDATE main t3 1 1 2 xyz abc
000801 UPDATE main t4 1 1 3 xyz abc
000802 }
000803 do_preupdate_test 7.6.4 {
000804 DELETE FROM t1 WHERE 1;
000805 } {
000806 DELETE main t1 1 1 0 abc
000807 DELETE main t2 1 1 1 abc
000808 DELETE main t3 1 1 2 abc
000809 DELETE main t4 1 1 3 abc
000810 }
000811
000812 do_execsql_test 7.6.5 {
000813 DROP TRIGGER a; DROP TRIGGER b; DROP TRIGGER c;
000814 DROP TRIGGER d; DROP TRIGGER e; DROP TRIGGER f;
000815 DROP TRIGGER g; DROP TRIGGER h; DROP TRIGGER i;
000816
000817 CREATE TRIGGER a BEFORE INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
000818 CREATE TRIGGER b BEFORE INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
000819 CREATE TRIGGER c BEFORE INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
000820
000821 CREATE TRIGGER d BEFORE UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
000822 CREATE TRIGGER e BEFORE UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
000823 CREATE TRIGGER f BEFORE UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
000824
000825 CREATE TRIGGER g BEFORE DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
000826 CREATE TRIGGER h BEFORE DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
000827 CREATE TRIGGER i BEFORE DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
000828 }
000829
000830 do_preupdate_test 7.6.6 {
000831 INSERT INTO t1 VALUES('xyz');
000832 } {
000833 INSERT main t4 1 1 3 xyz
000834 INSERT main t3 1 1 2 xyz
000835 INSERT main t2 1 1 1 xyz
000836 INSERT main t1 1 1 0 xyz
000837 }
000838 do_preupdate_test 7.6.3 {
000839 UPDATE t1 SET x = 'abc';
000840 } {
000841 UPDATE main t4 1 1 3 xyz abc
000842 UPDATE main t3 1 1 2 xyz abc
000843 UPDATE main t2 1 1 1 xyz abc
000844 UPDATE main t1 1 1 0 xyz abc
000845 }
000846 do_preupdate_test 7.6.4 {
000847 DELETE FROM t1 WHERE 1;
000848 } {
000849 DELETE main t4 1 1 3 abc
000850 DELETE main t3 1 1 2 abc
000851 DELETE main t2 1 1 1 abc
000852 DELETE main t1 1 1 0 abc
000853 }
000854
000855 # No preupdate callbacks for modifying sqlite_master.
000856 ifcapable altertable {
000857 do_preupdate_test 8.1 { CREATE TABLE x1(x, y); } { }
000858 do_preupdate_test 8.2 { ALTER TABLE x1 ADD COLUMN z } { }
000859 do_preupdate_test 8.3 { ALTER TABLE x1 RENAME TO y1 } { }
000860 do_preupdate_test 8.4 { CREATE INDEX y1x ON y1(x) } { }
000861 do_preupdate_test 8.5 { CREATE VIEW v1 AS SELECT * FROM y1 } { }
000862 do_preupdate_test 8.6 { DROP TABLE y1 } { }
000863 }
000864
000865 #-------------------------------------------------------------------------
000866 reset_db
000867 db preupdate hook preupdate_hook
000868
000869 ifcapable altertable {
000870 do_execsql_test 9.0 {
000871 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
000872 CREATE TABLE t2(a, b INTEGER PRIMARY KEY);
000873 }
000874 do_preupdate_test 9.1 {
000875 INSERT INTO t1 VALUES(456, NULL, NULL);
000876 } {
000877 INSERT main t1 456 456 0 456 {} {}
000878 }
000879 do_execsql_test 9.2 {
000880 ALTER TABLE t1 ADD COLUMN d;
000881 }
000882 do_preupdate_test 9.3 {
000883 INSERT INTO t1(a, b, c) VALUES(457, NULL, NULL);
000884 } {
000885 INSERT main t1 457 457 0 457 {} {} {}
000886 }
000887 do_preupdate_test 9.4 {
000888 DELETE FROM t1 WHERE a=456
000889 } {
000890 DELETE main t1 456 456 0 456 {} {} {}
000891 }
000892 do_preupdate_test 9.5 {
000893 INSERT INTO t2 DEFAULT VALUES;
000894 } {
000895 INSERT main t2 1 1 0 {} 1
000896 }
000897 do_preupdate_test 9.6 {
000898 INSERT INTO t1 DEFAULT VALUES;
000899 } {
000900 INSERT main t1 458 458 0 458 {} {} {}
000901 }
000902 }
000903
000904
000905 do_execsql_test 10.0 {
000906 CREATE TABLE t3(a, b INTEGER PRIMARY KEY);
000907 }
000908 do_preupdate_test 10.1 {
000909 INSERT INTO t3 DEFAULT VALUES
000910 } {
000911 INSERT main t3 1 1 0 {} 1
000912 }
000913 do_execsql_test 10.2 { SELECT * FROM t3 } {{} 1}
000914 do_preupdate_test 10.3 {
000915 DELETE FROM t3 WHERE b=1
000916 } {DELETE main t3 1 1 0 {} 1}
000917
000918 #-------------------------------------------------------------------------
000919 # Test that the "update" hook is not fired for operations on the
000920 # sqlite_stat1 table performed by ANALYZE, even if a pre-update hook is
000921 # registered.
000922 ifcapable analyze {
000923 reset_db
000924 do_execsql_test 11.1 {
000925 CREATE TABLE t1(a, b);
000926 CREATE INDEX idx1 ON t1(a);
000927 CREATE INDEX idx2 ON t1(b);
000928
000929 INSERT INTO t1 VALUES(1, 2);
000930 INSERT INTO t1 VALUES(3, 4);
000931 INSERT INTO t1 VALUES(5, 6);
000932 INSERT INTO t1 VALUES(7, 8);
000933 }
000934
000935 db preupdate hook preupdate_cb
000936 db update_hook update_cb
000937
000938 proc preupdate_cb {args} { lappend ::res "preupdate" $args }
000939 proc update_cb {args} { lappend ::res "update" $args }
000940
000941 set ::res [list]
000942 do_test 11.2 {
000943 execsql ANALYZE
000944 set ::res
000945 } [list {*}{
000946 preupdate {INSERT main sqlite_stat1 1 1}
000947 preupdate {INSERT main sqlite_stat1 2 2}
000948 }]
000949
000950 do_execsql_test 11.3 {
000951 INSERT INTO t1 VALUES(9, 10);
000952 INSERT INTO t1 VALUES(11, 12);
000953 INSERT INTO t1 VALUES(13, 14);
000954 INSERT INTO t1 VALUES(15, 16);
000955 }
000956
000957 set ::res [list]
000958 do_test 11.4 {
000959 execsql ANALYZE
000960 set ::res
000961 } [list {*}{
000962 preupdate {DELETE main sqlite_stat1 1 1}
000963 preupdate {DELETE main sqlite_stat1 2 2}
000964 preupdate {INSERT main sqlite_stat1 1 1}
000965 preupdate {INSERT main sqlite_stat1 2 2}
000966 }]
000967 }
000968
000969 #-------------------------------------------------------------------------
000970 # Test that the pre-update hook is fired for INSERT statements that use
000971 # the xfer optimization on without rowid tables.
000972 #
000973 reset_db
000974 do_execsql_test 12.1 {
000975 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
000976 CREATE TABLE t2(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
000977
000978 INSERT INTO t1 VALUES(1, 2);
000979 INSERT INTO t1 VALUES(3, 4);
000980 INSERT INTO t2 VALUES(5, 6);
000981 INSERT INTO t2 VALUES(7, 8);
000982
000983 CREATE TABLE t3 (a INTEGER PRIMARY KEY, b) WITHOUT ROWID;
000984 }
000985
000986 db preupdate hook preupdate_cb
000987 db update_hook update_cb
000988
000989 proc preupdate_cb {args} { lappend ::res "preupdate" $args }
000990 proc update_cb {args} { lappend ::res "update" $args }
000991
000992 set ::res [list]
000993 do_test 12.2 {
000994 execsql VACUUM
000995 set ::res
000996 } {}
000997
000998 do_test 12.3 {
000999 set ::res [list]
001000 execsql { INSERT INTO t3 SELECT a, b FROM t2 }
001001 set ::res
001002 } {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}
001003
001004 do_test 12.4 {
001005 execsql { DELETE FROM t3 }
001006 set ::res [list]
001007 execsql { INSERT INTO t3 SELECT * FROM t2 }
001008 set ::res
001009 } {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}
001010
001011 do_execsql_test 12.5 {
001012 CREATE TABLE t4(a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;
001013 INSERT INTO t4 VALUES('abc', 1);
001014 INSERT INTO t4 VALUES('DEF', 2);
001015 }
001016
001017 set ::res [list]
001018 do_test 12.6 {
001019 execsql VACUUM
001020 set ::res
001021 } {}
001022
001023 do_catchsql_test 12.6 {
001024 INSERT INTO t4 VALUES('def', 3);
001025 } {1 {UNIQUE constraint failed: t4.a}}
001026
001027 #-------------------------------------------------------------------------
001028 # Test adding non-NULL default values using ALTER TABLE.
001029 #
001030 reset_db
001031 db preupdate hook preupdate_hook
001032 do_execsql_test 13.0 {
001033 CREATE TABLE t1(a INTEGER PRIMARY KEY);
001034 INSERT INTO t1 VALUES(100), (200), (300), (400);
001035 }
001036
001037 do_execsql_test 13.1 {
001038 ALTER TABLE t1 ADD COLUMN b DEFAULT 1234;
001039 ALTER TABLE t1 ADD COLUMN c DEFAULT 'abcdef';
001040 ALTER TABLE t1 ADD COLUMN d DEFAULT NULL;
001041 }
001042
001043 do_preupdate_test 13.2 {
001044 DELETE FROM t1 WHERE a=300
001045 } {DELETE main t1 300 300 0 300 1234 abcdef {}}
001046
001047 do_preupdate_test 13.3 {
001048 UPDATE t1 SET d='hello world' WHERE a=200
001049 } {
001050 UPDATE main t1 200 200 0 200 1234 abcdef {}
001051 200 1234 abcdef {hello world}
001052 }
001053
001054 do_preupdate_test 13.4 {
001055 INSERT INTO t1 DEFAULT VALUES;
001056 } {
001057 INSERT main t1 401 401 0 401 1234 abcdef {}
001058 }
001059
001060 finish_test