gitlab.com/CoiaPrant/sqlite3@v1.19.1/testdata/tcl/hook.test (about) 1 # 2004 Jan 14 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 # This file implements regression tests for TCL interface to the 12 # SQLite library. 13 # 14 # The focus of the tests in this file is the following interface: 15 # 16 # sqlite_commit_hook (tests hook-1..hook-3 inclusive) 17 # sqlite_update_hook (tests hook-4-*) 18 # sqlite_rollback_hook (tests hook-5.*) 19 # 20 # $Id: hook.test,v 1.15 2009/04/07 14:14:23 danielk1977 Exp $ 21 22 set testdir [file dirname $argv0] 23 source $testdir/tester.tcl 24 set ::testprefix hook 25 26 do_test hook-1.2 { 27 db commit_hook 28 } {} 29 30 31 do_test hook-3.1 { 32 set commit_cnt 0 33 proc commit_hook {} { 34 incr ::commit_cnt 35 return 0 36 } 37 db commit_hook ::commit_hook 38 db commit_hook 39 } {::commit_hook} 40 do_test hook-3.2 { 41 set commit_cnt 42 } {0} 43 do_test hook-3.3 { 44 execsql { 45 CREATE TABLE t2(a,b); 46 } 47 set commit_cnt 48 } {1} 49 do_test hook-3.4 { 50 execsql { 51 INSERT INTO t2 VALUES(1,2); 52 INSERT INTO t2 SELECT a+1, b+1 FROM t2; 53 INSERT INTO t2 SELECT a+2, b+2 FROM t2; 54 } 55 set commit_cnt 56 } {4} 57 do_test hook-3.5 { 58 set commit_cnt {} 59 proc commit_hook {} { 60 set ::commit_cnt [execsql {SELECT * FROM t2}] 61 return 0 62 } 63 execsql { 64 INSERT INTO t2 VALUES(5,6); 65 } 66 set commit_cnt 67 } {1 2 2 3 3 4 4 5 5 6} 68 do_test hook-3.6 { 69 set commit_cnt {} 70 proc commit_hook {} { 71 set ::commit_cnt [execsql {SELECT * FROM t2}] 72 return 1 73 } 74 catchsql { 75 INSERT INTO t2 VALUES(6,7); 76 } 77 } {1 {constraint failed}} 78 verify_ex_errcode hook-3.6b SQLITE_CONSTRAINT_COMMITHOOK 79 do_test hook-3.7 { 80 set ::commit_cnt 81 } {1 2 2 3 3 4 4 5 5 6 6 7} 82 do_test hook-3.8 { 83 execsql {SELECT * FROM t2} 84 } {1 2 2 3 3 4 4 5 5 6} 85 86 # Test turnning off the commit hook 87 # 88 do_test hook-3.9 { 89 db commit_hook {} 90 set ::commit_cnt {} 91 execsql { 92 INSERT INTO t2 VALUES(7,8); 93 } 94 set ::commit_cnt 95 } {} 96 97 # Ticket #3564. 98 # 99 do_test hook-3.10 { 100 forcedelete test2.db test2.db-journal 101 sqlite3 db2 test2.db 102 proc commit_hook {} { 103 set y [db2 one {SELECT y FROM t3 WHERE y>10}] 104 return [expr {$y>10}] 105 } 106 db2 eval {CREATE TABLE t3(x,y)} 107 db2 commit_hook commit_hook 108 catchsql {INSERT INTO t3 VALUES(1,2)} db2 109 catchsql {INSERT INTO t3 VALUES(11,12)} db2 110 catchsql {INSERT INTO t3 VALUES(3,4)} db2 111 db2 eval { 112 SELECT * FROM t3 ORDER BY x; 113 } 114 } {1 2 3 4} 115 db2 close 116 117 118 #---------------------------------------------------------------------------- 119 # Tests for the update-hook. 120 # 121 # 4.1.* - Very simple tests. Test that the update hook is invoked correctly 122 # for INSERT, DELETE and UPDATE statements, including DELETE 123 # statements with no WHERE clause. 124 # 4.2.* - Check that the update-hook is invoked for rows modified by trigger 125 # bodies. Also that the database name is correctly reported when 126 # an attached database is modified. 127 # 4.3.* - Do some sorting, grouping, compound queries, population and 128 # depopulation of indices, to make sure the update-hook is not 129 # invoked incorrectly. 130 # 131 # EVIDENCE-OF: R-21999-45122 The sqlite3_update_hook() interface 132 # registers a callback function with the database connection identified 133 # by the first argument to be invoked whenever a row is updated, 134 # inserted or deleted in a rowid table. 135 136 # Simple tests 137 do_test hook-4.1.1a { 138 catchsql { 139 DROP TABLE t1; 140 } 141 unset -nocomplain ::update_hook 142 set ::update_hook {} 143 db update_hook [list lappend ::update_hook] 144 # 145 # EVIDENCE-OF: R-24531-54682 The update hook is not invoked when 146 # internal system tables are modified (i.e. sqlite_sequence). 147 # 148 execsql { 149 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 150 CREATE TABLE t1w(a INT PRIMARY KEY, b) WITHOUT ROWID; 151 } 152 set ::update_hook 153 } {} 154 do_test hook-4.1.1b { 155 execsql { 156 INSERT INTO t1 VALUES(1, 'one'); 157 INSERT INTO t1 VALUES(2, 'two'); 158 INSERT INTO t1 VALUES(3, 'three'); 159 INSERT INTO t1w SELECT * FROM t1; 160 } 161 } {} 162 163 # EVIDENCE-OF: R-15506-57666 The second callback argument is one of 164 # SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE, depending on the 165 # operation that caused the callback to be invoked. 166 # 167 # EVIDENCE-OF: R-29213-61195 The third and fourth arguments to the 168 # callback contain pointers to the database and table name containing 169 # the affected row. 170 # 171 # EVIDENCE-OF: R-30809-57812 The final callback parameter is the rowid 172 # of the row. 173 # 174 do_test hook-4.1.2 { 175 set ::update_hook {} 176 execsql { 177 INSERT INTO t1 VALUES(4, 'four'); 178 DELETE FROM t1 WHERE b = 'two'; 179 UPDATE t1 SET b = '' WHERE a = 1 OR a = 3; 180 DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now) 181 } 182 set ::update_hook 183 } [list \ 184 INSERT main t1 4 \ 185 DELETE main t1 2 \ 186 UPDATE main t1 1 \ 187 UPDATE main t1 3 \ 188 DELETE main t1 1 \ 189 DELETE main t1 3 \ 190 DELETE main t1 4 \ 191 ] 192 193 # EVIDENCE-OF: R-61808-14344 The sqlite3_update_hook() interface does 194 # not fire callbacks for changes to a WITHOUT ROWID table. 195 # 196 # EVIDENCE-OF: R-33257-44249 The update hook is not invoked when WITHOUT 197 # ROWID tables are modified. 198 # 199 do_test hook-4.1.2w { 200 set ::update_hook {} 201 execsql { 202 INSERT INTO t1w VALUES(4, 'four'); 203 DELETE FROM t1w WHERE b = 'two'; 204 UPDATE t1w SET b = '' WHERE a = 1 OR a = 3; 205 DELETE FROM t1w WHERE 1; -- Avoid the truncate optimization (for now) 206 } 207 set ::update_hook 208 } {} 209 210 ifcapable trigger { 211 # Update hook is not invoked for changes to sqlite_master 212 # 213 do_test hook-4.1.3 { 214 set ::update_hook {} 215 execsql { 216 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END; 217 } 218 set ::update_hook 219 } {} 220 do_test hook-4.1.4 { 221 set ::update_hook {} 222 execsql { 223 DROP TRIGGER r1; 224 } 225 set ::update_hook 226 } {} 227 228 set ::update_hook {} 229 do_test hook-4.2.1 { 230 catchsql { 231 DROP TABLE t2; 232 } 233 execsql { 234 CREATE TABLE t2(c INTEGER PRIMARY KEY, d); 235 CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN 236 INSERT INTO t2 VALUES(new.a, new.b); 237 UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c; 238 DELETE FROM t2 WHERE new.a = c; 239 END; 240 } 241 } {} 242 do_test hook-4.2.2 { 243 execsql { 244 INSERT INTO t1 VALUES(1, 'one'); 245 INSERT INTO t1 VALUES(2, 'two'); 246 } 247 set ::update_hook 248 } [list \ 249 INSERT main t1 1 \ 250 INSERT main t2 1 \ 251 UPDATE main t2 1 \ 252 DELETE main t2 1 \ 253 INSERT main t1 2 \ 254 INSERT main t2 2 \ 255 UPDATE main t2 2 \ 256 DELETE main t2 2 \ 257 ] 258 } else { 259 execsql { 260 INSERT INTO t1 VALUES(1, 'one'); 261 INSERT INTO t1 VALUES(2, 'two'); 262 } 263 } 264 265 # Update-hook + ATTACH 266 set ::update_hook {} 267 ifcapable attach { 268 do_test hook-4.2.3 { 269 forcedelete test2.db 270 execsql { 271 ATTACH 'test2.db' AS aux; 272 CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b); 273 INSERT INTO aux.t3 SELECT * FROM t1; 274 UPDATE t3 SET b = 'two or so' WHERE a = 2; 275 DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now) 276 } 277 set ::update_hook 278 } [list \ 279 INSERT aux t3 1 \ 280 INSERT aux t3 2 \ 281 UPDATE aux t3 2 \ 282 DELETE aux t3 1 \ 283 DELETE aux t3 2 \ 284 ] 285 } 286 287 ifcapable trigger { 288 execsql { 289 DROP TRIGGER t1_trigger; 290 } 291 } 292 293 # Test that other vdbe operations involving btree structures do not 294 # incorrectly invoke the update-hook. 295 set ::update_hook {} 296 do_test hook-4.3.1 { 297 execsql { 298 CREATE INDEX t1_i ON t1(b); 299 INSERT INTO t1 VALUES(3, 'three'); 300 UPDATE t1 SET b = ''; 301 DELETE FROM t1 WHERE a > 1; 302 } 303 set ::update_hook 304 } [list \ 305 INSERT main t1 3 \ 306 UPDATE main t1 1 \ 307 UPDATE main t1 2 \ 308 UPDATE main t1 3 \ 309 DELETE main t1 2 \ 310 DELETE main t1 3 \ 311 ] 312 set ::update_hook {} 313 ifcapable compound&&attach { 314 do_test hook-4.3.2 { 315 execsql { 316 SELECT * FROM t1 UNION SELECT * FROM t3; 317 SELECT * FROM t1 UNION ALL SELECT * FROM t3; 318 SELECT * FROM t1 INTERSECT SELECT * FROM t3; 319 SELECT * FROM t1 EXCEPT SELECT * FROM t3; 320 SELECT * FROM t1 ORDER BY b; 321 SELECT * FROM t1 GROUP BY b; 322 } 323 set ::update_hook 324 } [list] 325 } 326 327 do_test hook-4.4 { 328 execsql { 329 CREATE TABLE t4(a UNIQUE, b); 330 INSERT INTO t4 VALUES(1, 'a'); 331 INSERT INTO t4 VALUES(2, 'b'); 332 } 333 set ::update_hook [list] 334 execsql { 335 REPLACE INTO t4 VALUES(1, 'c'); 336 } 337 set ::update_hook 338 } [list INSERT main t4 3 ] 339 do_execsql_test hook-4.4.1 { 340 SELECT * FROM t4 ORDER BY a; 341 } {1 c 2 b} 342 do_test hook-4.4.2 { 343 set ::update_hook [list] 344 execsql { 345 PRAGMA recursive_triggers = on; 346 REPLACE INTO t4 VALUES(1, 'd'); 347 } 348 set ::update_hook 349 } [list INSERT main t4 4 ] 350 do_execsql_test hook-4.4.3 { 351 SELECT * FROM t4 ORDER BY a; 352 } {1 d 2 b} 353 354 db update_hook {} 355 # 356 #---------------------------------------------------------------------------- 357 358 #---------------------------------------------------------------------------- 359 # Test the rollback-hook. The rollback-hook is a bit more complicated than 360 # either the commit or update hooks because a rollback can happen 361 # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or 362 # error condition). 363 # 364 # hook-5.1.* - Test explicit rollbacks. 365 # hook-5.2.* - Test implicit rollbacks caused by constraint failure. 366 # 367 # hook-5.3.* - Test implicit rollbacks caused by IO errors. 368 # hook-5.4.* - Test implicit rollbacks caused by malloc() failure. 369 # hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook 370 # not be called for these? 371 # 372 373 do_test hook-5.0 { 374 # Configure the rollback hook to increment global variable 375 # $::rollback_hook each time it is invoked. 376 set ::rollback_hook 0 377 db rollback_hook [list incr ::rollback_hook] 378 } {} 379 380 # Test explicit rollbacks. Not much can really go wrong here. 381 # 382 do_test hook-5.1.1 { 383 set ::rollback_hook 0 384 execsql { 385 BEGIN; 386 ROLLBACK; 387 } 388 set ::rollback_hook 389 } {1} 390 391 # Test implicit rollbacks caused by constraints. 392 # 393 do_test hook-5.2.1 { 394 set ::rollback_hook 0 395 catchsql { 396 DROP TABLE t1; 397 CREATE TABLE t1(a PRIMARY KEY, b); 398 INSERT INTO t1 VALUES('one', 'I'); 399 INSERT INTO t1 VALUES('one', 'I'); 400 } 401 set ::rollback_hook 402 } {1} 403 do_test hook-5.2.2 { 404 # Check that the INSERT transaction above really was rolled back. 405 execsql { 406 SELECT count(*) FROM t1; 407 } 408 } {1} 409 410 # 411 # End rollback-hook testing. 412 #---------------------------------------------------------------------------- 413 414 #---------------------------------------------------------------------------- 415 # Test that if a commit-hook returns non-zero (causing a rollback), the 416 # rollback-hook is invoked. 417 # 418 proc commit_hook {} { 419 lappend ::hooks COMMIT 420 return 1 421 } 422 proc rollback_hook {} { 423 lappend ::hooks ROLLBACK 424 } 425 do_test hook-6.1 { 426 set ::hooks [list] 427 db commit_hook commit_hook 428 db rollback_hook rollback_hook 429 catchsql { 430 BEGIN; 431 INSERT INTO t1 VALUES('two', 'II'); 432 COMMIT; 433 } 434 execsql { SELECT * FROM t1 } 435 } {one I} 436 do_test hook-6.2 { 437 set ::hooks 438 } {COMMIT ROLLBACK} 439 unset ::hooks 440 441 #---------------------------------------------------------------------------- 442 # The following tests - hook-7.* - test the pre-update hook. 443 # 444 ifcapable !preupdate { 445 finish_test 446 return 447 } 448 # 449 # 7.1.1 - INSERT statement. 450 # 7.1.2 - INSERT INTO ... SELECT statement. 451 # 7.1.3 - REPLACE INTO ... (rowid conflict) 452 # 7.1.4 - REPLACE INTO ... (other index conflicts) 453 # 7.1.5 - REPLACE INTO ... (both rowid and other index conflicts) 454 # 455 # 7.2.1 - DELETE statement. 456 # 7.2.2 - DELETE statement that uses the truncate optimization. 457 # 458 # 7.3.1 - UPDATE statement. 459 # 7.3.2 - UPDATE statement that modifies the rowid. 460 # 7.3.3 - UPDATE OR REPLACE ... (rowid conflict). 461 # 7.3.4 - UPDATE OR REPLACE ... (other index conflicts) 462 # 7.3.4 - UPDATE OR REPLACE ... (both rowid and other index conflicts) 463 # 464 # 7.4.1 - Test that the pre-update-hook is invoked only once if a row being 465 # deleted is removed by a BEFORE trigger. 466 # 467 # 7.4.2 - Test that the pre-update-hook is invoked if a BEFORE trigger 468 # removes a row being updated. In this case the update hook should 469 # be invoked with SQLITE_INSERT as the opcode when inserting the 470 # new version of the row. 471 # 472 # TODO: Short records (those created before a column is added to a table 473 # using ALTER TABLE) 474 # 475 476 proc do_preupdate_test {tn sql x} { 477 set X [list] 478 foreach elem $x {lappend X $elem} 479 uplevel do_test $tn [list " 480 set ::preupdate \[list\] 481 execsql { $sql } 482 set ::preupdate 483 "] [list $X] 484 } 485 486 proc preupdate_hook {args} { 487 set type [lindex $args 0] 488 eval lappend ::preupdate $args 489 if {$type != "INSERT"} { 490 for {set i 0} {$i < [db preupdate count]} {incr i} { 491 lappend ::preupdate [db preupdate old $i] 492 } 493 } 494 if {$type != "DELETE"} { 495 for {set i 0} {$i < [db preupdate count]} {incr i} { 496 set rc [catch { db preupdate new $i } v] 497 lappend ::preupdate $v 498 } 499 } 500 } 501 502 db close 503 forcedelete test.db 504 sqlite3 db test.db 505 db preupdate hook preupdate_hook 506 507 # Set up a schema to use for tests 7.1.* to 7.3.*. 508 do_execsql_test 7.0 { 509 CREATE TABLE t1(a, b); 510 CREATE TABLE t2(x, y); 511 CREATE TABLE t3(i, j, UNIQUE(i)); 512 513 INSERT INTO t2 VALUES('a', 'b'); 514 INSERT INTO t2 VALUES('c', 'd'); 515 516 INSERT INTO t3 VALUES(4, 16); 517 INSERT INTO t3 VALUES(5, 25); 518 INSERT INTO t3 VALUES(6, 36); 519 } 520 521 do_preupdate_test 7.1.1 { 522 INSERT INTO t1 VALUES('x', 'y') 523 } {INSERT main t1 1 1 x y} 524 525 # 7.1.2.1 does not use the xfer optimization. 7.1.2.2 does. 526 do_preupdate_test 7.1.2.1 { 527 INSERT INTO t1 SELECT y, x FROM t2; 528 } {INSERT main t1 2 2 b a INSERT main t1 3 3 d c} 529 do_preupdate_test 7.1.2.2 { 530 INSERT INTO t1 SELECT * FROM t2; 531 } {INSERT main t1 4 4 a b INSERT main t1 5 5 c d} 532 533 do_preupdate_test 7.1.3 { 534 REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1); 535 } { 536 DELETE main t1 1 1 x y 537 INSERT main t1 1 1 1 1 538 } 539 540 do_preupdate_test 7.1.4 { 541 REPLACE INTO t3 VALUES(4, NULL); 542 } { 543 DELETE main t3 1 1 4 16 544 INSERT main t3 4 4 4 {} 545 } 546 547 do_preupdate_test 7.1.5 { 548 REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL); 549 } { 550 DELETE main t3 2 2 5 25 551 DELETE main t3 3 3 6 36 552 INSERT main t3 2 2 6 {} 553 } 554 555 do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5} 556 557 do_preupdate_test 7.2.1 { 558 DELETE FROM t1 WHERE rowid = 3 559 } { 560 DELETE main t1 3 3 d c 561 } 562 do_preupdate_test 7.2.2 { 563 DELETE FROM t1 564 } { 565 DELETE main t1 1 1 1 1 566 DELETE main t1 2 2 b a 567 DELETE main t1 4 4 a b 568 DELETE main t1 5 5 c d 569 } 570 571 do_execsql_test 7.3.0 { 572 DELETE FROM t1; 573 DELETE FROM t2; 574 DELETE FROM t3; 575 576 INSERT INTO t2 VALUES('a', 'b'); 577 INSERT INTO t2 VALUES('c', 'd'); 578 579 INSERT INTO t3 VALUES(4, 16); 580 INSERT INTO t3 VALUES(5, 25); 581 INSERT INTO t3 VALUES(6, 36); 582 } 583 584 do_preupdate_test 7.3.1 { 585 UPDATE t2 SET y = y||y; 586 } { 587 UPDATE main t2 1 1 a b a bb 588 UPDATE main t2 2 2 c d c dd 589 } 590 591 do_preupdate_test 7.3.2 { 592 UPDATE t2 SET rowid = rowid-1; 593 } { 594 UPDATE main t2 1 0 a bb a bb 595 UPDATE main t2 2 1 c dd c dd 596 } 597 598 do_preupdate_test 7.3.3 { 599 UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a' 600 } { 601 DELETE main t2 1 1 c dd 602 UPDATE main t2 0 1 a bb a bb 603 } 604 605 do_preupdate_test 7.3.4.1 { 606 UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6 607 } { 608 DELETE main t3 2 2 5 25 609 UPDATE main t3 3 3 6 36 5 36 610 } 611 612 do_execsql_test 7.3.4.2 { 613 INSERT INTO t3 VALUES(10, 100); 614 SELECT rowid, * FROM t3; 615 } {1 4 16 3 5 36 4 10 100} 616 617 do_preupdate_test 7.3.5 { 618 UPDATE OR REPLACE t3 SET rowid = 1, i = 5 WHERE j = 100; 619 } { 620 DELETE main t3 1 1 4 16 621 DELETE main t3 3 3 5 36 622 UPDATE main t3 4 1 10 100 5 100 623 } 624 625 do_execsql_test 7.4.1.0 { 626 CREATE TABLE t4(a, b); 627 INSERT INTO t4 VALUES('a', 1); 628 INSERT INTO t4 VALUES('b', 2); 629 INSERT INTO t4 VALUES('c', 3); 630 631 CREATE TRIGGER t4t BEFORE DELETE ON t4 BEGIN 632 DELETE FROM t4 WHERE b = 1; 633 END; 634 } 635 636 do_preupdate_test 7.4.1.1 { 637 DELETE FROM t4 WHERE b = 3 638 } { 639 DELETE main t4 1 1 a 1 640 DELETE main t4 3 3 c 3 641 } 642 643 do_execsql_test 7.4.1.2 { 644 INSERT INTO t4(rowid, a, b) VALUES(1, 'a', 1); 645 INSERT INTO t4(rowid, a, b) VALUES(3, 'c', 3); 646 } 647 do_preupdate_test 7.4.1.3 { 648 DELETE FROM t4 WHERE b = 1 649 } { 650 DELETE main t4 1 1 a 1 651 } 652 653 do_execsql_test 7.4.2.0 { 654 CREATE TABLE t5(a, b); 655 INSERT INTO t5 VALUES('a', 1); 656 INSERT INTO t5 VALUES('b', 2); 657 INSERT INTO t5 VALUES('c', 3); 658 659 CREATE TRIGGER t5t BEFORE UPDATE ON t5 BEGIN 660 DELETE FROM t5 WHERE b = 1; 661 END; 662 } 663 do_preupdate_test 7.4.2.1 { 664 UPDATE t5 SET b = 4 WHERE a = 'c' 665 } { 666 DELETE main t5 1 1 a 1 667 UPDATE main t5 3 3 c 3 c 4 668 } 669 670 do_execsql_test 7.4.2.2 { 671 INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1); 672 } 673 674 do_preupdate_test 7.4.2.3 { 675 UPDATE t5 SET b = 5 WHERE a = 'a' 676 } { 677 DELETE main t5 1 1 a 1 678 } 679 680 ifcapable altertable { 681 do_execsql_test 7.5.1.0 { 682 CREATE TABLE t7(a, b); 683 INSERT INTO t7 VALUES('one', 'two'); 684 INSERT INTO t7 VALUES('three', 'four'); 685 ALTER TABLE t7 ADD COLUMN c DEFAULT NULL; 686 } 687 688 do_preupdate_test 7.5.1.1 { 689 DELETE FROM t7 WHERE a = 'one' 690 } { 691 DELETE main t7 1 1 one two {} 692 } 693 694 do_preupdate_test 7.5.1.2 { 695 UPDATE t7 SET b = 'five' 696 } { 697 UPDATE main t7 2 2 three four {} three five {} 698 } 699 700 do_execsql_test 7.5.2.0 { 701 CREATE TABLE t8(a, b); 702 INSERT INTO t8 VALUES('one', 'two'); 703 INSERT INTO t8 VALUES('three', 'four'); 704 ALTER TABLE t8 ADD COLUMN c DEFAULT 'xxx'; 705 } 706 } 707 708 if 0 { 709 # At time of writing, these two are broken. They demonstrate that the 710 # sqlite3_preupdate_old() method does not handle the case where ALTER TABLE 711 # has been used to add a column with a default value other than NULL. 712 # 713 do_preupdate_test 7.5.2.1 { 714 DELETE FROM t8 WHERE a = 'one' 715 } { 716 DELETE main t8 1 1 one two xxx 717 } 718 do_preupdate_test 7.5.2.2 { 719 UPDATE t8 SET b = 'five' 720 } { 721 UPDATE main t8 2 2 three four xxx three five xxx 722 } 723 } 724 725 # This block of tests verifies that IPK values are correctly reported 726 # by the sqlite3_preupdate_old() and sqlite3_preupdate_new() functions. 727 # 728 do_execsql_test 7.6.1 { CREATE TABLE t9(a, b INTEGER PRIMARY KEY, c) } 729 do_preupdate_test 7.6.2 { 730 INSERT INTO t9 VALUES(1, 2, 3); 731 UPDATE t9 SET b = b+1, c = c+1; 732 DELETE FROM t9 WHERE a = 1; 733 } { 734 INSERT main t9 2 2 1 2 3 735 UPDATE main t9 2 3 1 2 3 1 3 4 736 DELETE main t9 3 3 1 3 4 737 } 738 739 #-------------------------------------------------------------------------- 740 # Test that the sqlite3_preupdate_depth() API seems to work. 741 # 742 proc preupdate_hook {args} { 743 set type [lindex $args 0] 744 eval lappend ::preupdate $args 745 eval lappend ::preupdate [db preupdate depth] 746 747 if {$type != "INSERT"} { 748 for {set i 0} {$i < [db preupdate count]} {incr i} { 749 lappend ::preupdate [db preupdate old $i] 750 } 751 } 752 if {$type != "DELETE"} { 753 for {set i 0} {$i < [db preupdate count]} {incr i} { 754 set rc [catch { db preupdate new $i } v] 755 lappend ::preupdate $v 756 } 757 } 758 } 759 760 db close 761 forcedelete test.db 762 sqlite3 db test.db 763 db preupdate hook preupdate_hook 764 765 do_execsql_test 7.6.1 { 766 CREATE TABLE t1(x PRIMARY KEY); 767 CREATE TABLE t2(x PRIMARY KEY); 768 CREATE TABLE t3(x PRIMARY KEY); 769 CREATE TABLE t4(x PRIMARY KEY); 770 771 CREATE TRIGGER a AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END; 772 CREATE TRIGGER b AFTER INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END; 773 CREATE TRIGGER c AFTER INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END; 774 775 CREATE TRIGGER d AFTER UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END; 776 CREATE TRIGGER e AFTER UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END; 777 CREATE TRIGGER f AFTER UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END; 778 779 CREATE TRIGGER g AFTER DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END; 780 CREATE TRIGGER h AFTER DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END; 781 CREATE TRIGGER i AFTER DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END; 782 } 783 784 do_preupdate_test 7.6.2 { 785 INSERT INTO t1 VALUES('xyz'); 786 } { 787 INSERT main t1 1 1 0 xyz 788 INSERT main t2 1 1 1 xyz 789 INSERT main t3 1 1 2 xyz 790 INSERT main t4 1 1 3 xyz 791 } 792 do_preupdate_test 7.6.3 { 793 UPDATE t1 SET x = 'abc'; 794 } { 795 UPDATE main t1 1 1 0 xyz abc 796 UPDATE main t2 1 1 1 xyz abc 797 UPDATE main t3 1 1 2 xyz abc 798 UPDATE main t4 1 1 3 xyz abc 799 } 800 do_preupdate_test 7.6.4 { 801 DELETE FROM t1 WHERE 1; 802 } { 803 DELETE main t1 1 1 0 abc 804 DELETE main t2 1 1 1 abc 805 DELETE main t3 1 1 2 abc 806 DELETE main t4 1 1 3 abc 807 } 808 809 do_execsql_test 7.6.5 { 810 DROP TRIGGER a; DROP TRIGGER b; DROP TRIGGER c; 811 DROP TRIGGER d; DROP TRIGGER e; DROP TRIGGER f; 812 DROP TRIGGER g; DROP TRIGGER h; DROP TRIGGER i; 813 814 CREATE TRIGGER a BEFORE INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END; 815 CREATE TRIGGER b BEFORE INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END; 816 CREATE TRIGGER c BEFORE INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END; 817 818 CREATE TRIGGER d BEFORE UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END; 819 CREATE TRIGGER e BEFORE UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END; 820 CREATE TRIGGER f BEFORE UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END; 821 822 CREATE TRIGGER g BEFORE DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END; 823 CREATE TRIGGER h BEFORE DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END; 824 CREATE TRIGGER i BEFORE DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END; 825 } 826 827 do_preupdate_test 7.6.6 { 828 INSERT INTO t1 VALUES('xyz'); 829 } { 830 INSERT main t4 1 1 3 xyz 831 INSERT main t3 1 1 2 xyz 832 INSERT main t2 1 1 1 xyz 833 INSERT main t1 1 1 0 xyz 834 } 835 do_preupdate_test 7.6.3 { 836 UPDATE t1 SET x = 'abc'; 837 } { 838 UPDATE main t4 1 1 3 xyz abc 839 UPDATE main t3 1 1 2 xyz abc 840 UPDATE main t2 1 1 1 xyz abc 841 UPDATE main t1 1 1 0 xyz abc 842 } 843 do_preupdate_test 7.6.4 { 844 DELETE FROM t1 WHERE 1; 845 } { 846 DELETE main t4 1 1 3 abc 847 DELETE main t3 1 1 2 abc 848 DELETE main t2 1 1 1 abc 849 DELETE main t1 1 1 0 abc 850 } 851 852 # No preupdate callbacks for modifying sqlite_master. 853 ifcapable altertable { 854 do_preupdate_test 8.1 { CREATE TABLE x1(x, y); } { } 855 do_preupdate_test 8.2 { ALTER TABLE x1 ADD COLUMN z } { } 856 do_preupdate_test 8.3 { ALTER TABLE x1 RENAME TO y1 } { } 857 do_preupdate_test 8.4 { CREATE INDEX y1x ON y1(x) } { } 858 do_preupdate_test 8.5 { CREATE VIEW v1 AS SELECT * FROM y1 } { } 859 do_preupdate_test 8.6 { DROP TABLE y1 } { } 860 } 861 862 #------------------------------------------------------------------------- 863 reset_db 864 db preupdate hook preupdate_hook 865 866 ifcapable altertable { 867 do_execsql_test 9.0 { 868 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c); 869 CREATE TABLE t2(a, b INTEGER PRIMARY KEY); 870 } 871 do_preupdate_test 9.1 { 872 INSERT INTO t1 VALUES(456, NULL, NULL); 873 } { 874 INSERT main t1 456 456 0 456 {} {} 875 } 876 do_execsql_test 9.2 { 877 ALTER TABLE t1 ADD COLUMN d; 878 } 879 do_preupdate_test 9.3 { 880 INSERT INTO t1(a, b, c) VALUES(457, NULL, NULL); 881 } { 882 INSERT main t1 457 457 0 457 {} {} {} 883 } 884 do_preupdate_test 9.4 { 885 DELETE FROM t1 WHERE a=456 886 } { 887 DELETE main t1 456 456 0 456 {} {} {} 888 } 889 do_preupdate_test 9.5 { 890 INSERT INTO t2 DEFAULT VALUES; 891 } { 892 INSERT main t2 1 1 0 {} 1 893 } 894 do_preupdate_test 9.6 { 895 INSERT INTO t1 DEFAULT VALUES; 896 } { 897 INSERT main t1 458 458 0 458 {} {} {} 898 } 899 } 900 901 902 do_execsql_test 10.0 { 903 CREATE TABLE t3(a, b INTEGER PRIMARY KEY); 904 } 905 do_preupdate_test 10.1 { 906 INSERT INTO t3 DEFAULT VALUES 907 } { 908 INSERT main t3 1 1 0 {} 1 909 } 910 do_execsql_test 10.2 { SELECT * FROM t3 } {{} 1} 911 do_preupdate_test 10.3 { 912 DELETE FROM t3 WHERE b=1 913 } {DELETE main t3 1 1 0 {} 1} 914 915 #------------------------------------------------------------------------- 916 # Test that the "update" hook is not fired for operations on the 917 # sqlite_stat1 table performed by ANALYZE, even if a pre-update hook is 918 # registered. 919 ifcapable analyze { 920 reset_db 921 do_execsql_test 11.1 { 922 CREATE TABLE t1(a, b); 923 CREATE INDEX idx1 ON t1(a); 924 CREATE INDEX idx2 ON t1(b); 925 926 INSERT INTO t1 VALUES(1, 2); 927 INSERT INTO t1 VALUES(3, 4); 928 INSERT INTO t1 VALUES(5, 6); 929 INSERT INTO t1 VALUES(7, 8); 930 } 931 932 db preupdate hook preupdate_cb 933 db update_hook update_cb 934 935 proc preupdate_cb {args} { lappend ::res "preupdate" $args } 936 proc update_cb {args} { lappend ::res "update" $args } 937 938 set ::res [list] 939 do_test 11.2 { 940 execsql ANALYZE 941 set ::res 942 } [list {*}{ 943 preupdate {INSERT main sqlite_stat1 1 1} 944 preupdate {INSERT main sqlite_stat1 2 2} 945 }] 946 947 do_execsql_test 11.3 { 948 INSERT INTO t1 VALUES(9, 10); 949 INSERT INTO t1 VALUES(11, 12); 950 INSERT INTO t1 VALUES(13, 14); 951 INSERT INTO t1 VALUES(15, 16); 952 } 953 954 set ::res [list] 955 do_test 11.4 { 956 execsql ANALYZE 957 set ::res 958 } [list {*}{ 959 preupdate {DELETE main sqlite_stat1 1 1} 960 preupdate {DELETE main sqlite_stat1 2 2} 961 preupdate {INSERT main sqlite_stat1 1 1} 962 preupdate {INSERT main sqlite_stat1 2 2} 963 }] 964 } 965 966 #------------------------------------------------------------------------- 967 # Test that the pre-update hook is fired for INSERT statements that use 968 # the xfer optimization on without rowid tables. 969 # 970 reset_db 971 do_execsql_test 12.1 { 972 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 973 CREATE TABLE t2(a INTEGER PRIMARY KEY, b) WITHOUT ROWID; 974 975 INSERT INTO t1 VALUES(1, 2); 976 INSERT INTO t1 VALUES(3, 4); 977 INSERT INTO t2 VALUES(5, 6); 978 INSERT INTO t2 VALUES(7, 8); 979 980 CREATE TABLE t3 (a INTEGER PRIMARY KEY, b) WITHOUT ROWID; 981 } 982 983 db preupdate hook preupdate_cb 984 db update_hook update_cb 985 986 proc preupdate_cb {args} { lappend ::res "preupdate" $args } 987 proc update_cb {args} { lappend ::res "update" $args } 988 989 set ::res [list] 990 do_test 12.2 { 991 execsql VACUUM 992 set ::res 993 } {} 994 995 do_test 12.3 { 996 set ::res [list] 997 execsql { INSERT INTO t3 SELECT a, b FROM t2 } 998 set ::res 999 } {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}} 1000 1001 do_test 12.4 { 1002 execsql { DELETE FROM t3 } 1003 set ::res [list] 1004 execsql { INSERT INTO t3 SELECT * FROM t2 } 1005 set ::res 1006 } {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}} 1007 1008 do_execsql_test 12.5 { 1009 CREATE TABLE t4(a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID; 1010 INSERT INTO t4 VALUES('abc', 1); 1011 INSERT INTO t4 VALUES('DEF', 2); 1012 } 1013 1014 set ::res [list] 1015 do_test 12.6 { 1016 execsql VACUUM 1017 set ::res 1018 } {} 1019 1020 do_catchsql_test 12.6 { 1021 INSERT INTO t4 VALUES('def', 3); 1022 } {1 {UNIQUE constraint failed: t4.a}} 1023 1024 finish_test