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 22set testdir [file dirname $argv0] 23source $testdir/tester.tcl 24set ::testprefix hook 25 26do_test hook-1.2 { 27 db commit_hook 28} {} 29 30 31do_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} 40do_test hook-3.2 { 41 set commit_cnt 42} {0} 43do_test hook-3.3 { 44 execsql { 45 CREATE TABLE t2(a,b); 46 } 47 set commit_cnt 48} {1} 49do_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} 57do_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} 68do_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}} 78do_test hook-3.7 { 79 set ::commit_cnt 80} {1 2 2 3 3 4 4 5 5 6 6 7} 81do_test hook-3.8 { 82 execsql {SELECT * FROM t2} 83} {1 2 2 3 3 4 4 5 5 6} 84 85# Test turnning off the commit hook 86# 87do_test hook-3.9 { 88 db commit_hook {} 89 set ::commit_cnt {} 90 execsql { 91 INSERT INTO t2 VALUES(7,8); 92 } 93 set ::commit_cnt 94} {} 95 96# Ticket #3564. 97# 98do_test hook-3.10 { 99 file delete -force test2.db test2.db-journal 100 sqlite3 db2 test2.db 101 proc commit_hook {} { 102 set y [db2 one {SELECT y FROM t3 WHERE y>10}] 103 return [expr {$y>10}] 104 } 105 db2 eval {CREATE TABLE t3(x,y)} 106 db2 commit_hook commit_hook 107 catchsql {INSERT INTO t3 VALUES(1,2)} db2 108 catchsql {INSERT INTO t3 VALUES(11,12)} db2 109 catchsql {INSERT INTO t3 VALUES(3,4)} db2 110 db2 eval { 111 SELECT * FROM t3 ORDER BY x; 112 } 113} {1 2 3 4} 114db2 close 115 116 117#---------------------------------------------------------------------------- 118# Tests for the update-hook. 119# 120# 4.1.* - Very simple tests. Test that the update hook is invoked correctly 121# for INSERT, DELETE and UPDATE statements, including DELETE 122# statements with no WHERE clause. 123# 4.2.* - Check that the update-hook is invoked for rows modified by trigger 124# bodies. Also that the database name is correctly reported when 125# an attached database is modified. 126# 4.3.* - Do some sorting, grouping, compound queries, population and 127# depopulation of indices, to make sure the update-hook is not 128# invoked incorrectly. 129# 130 131# Simple tests 132do_test hook-4.1.1 { 133 catchsql { 134 DROP TABLE t1; 135 } 136 execsql { 137 CREATE TABLE t1(a INTEGER PRIMARY KEY, b); 138 INSERT INTO t1 VALUES(1, 'one'); 139 INSERT INTO t1 VALUES(2, 'two'); 140 INSERT INTO t1 VALUES(3, 'three'); 141 } 142 db update_hook [list lappend ::update_hook] 143} {} 144do_test hook-4.1.2 { 145 execsql { 146 INSERT INTO t1 VALUES(4, 'four'); 147 DELETE FROM t1 WHERE b = 'two'; 148 UPDATE t1 SET b = '' WHERE a = 1 OR a = 3; 149 DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now) 150 } 151 set ::update_hook 152} [list \ 153 INSERT main t1 4 \ 154 DELETE main t1 2 \ 155 UPDATE main t1 1 \ 156 UPDATE main t1 3 \ 157 DELETE main t1 1 \ 158 DELETE main t1 3 \ 159 DELETE main t1 4 \ 160] 161 162ifcapable trigger { 163 # Update hook is not invoked for changes to sqlite_master 164 # 165 do_test hook-4.1.3 { 166 set ::update_hook {} 167 execsql { 168 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END; 169 } 170 set ::update_hook 171 } {} 172 do_test hook-4.1.4 { 173 set ::update_hook {} 174 execsql { 175 DROP TRIGGER r1; 176 } 177 set ::update_hook 178 } {} 179 180 set ::update_hook {} 181 do_test hook-4.2.1 { 182 catchsql { 183 DROP TABLE t2; 184 } 185 execsql { 186 CREATE TABLE t2(c INTEGER PRIMARY KEY, d); 187 CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN 188 INSERT INTO t2 VALUES(new.a, new.b); 189 UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c; 190 DELETE FROM t2 WHERE new.a = c; 191 END; 192 } 193 } {} 194 do_test hook-4.2.2 { 195 execsql { 196 INSERT INTO t1 VALUES(1, 'one'); 197 INSERT INTO t1 VALUES(2, 'two'); 198 } 199 set ::update_hook 200 } [list \ 201 INSERT main t1 1 \ 202 INSERT main t2 1 \ 203 UPDATE main t2 1 \ 204 DELETE main t2 1 \ 205 INSERT main t1 2 \ 206 INSERT main t2 2 \ 207 UPDATE main t2 2 \ 208 DELETE main t2 2 \ 209 ] 210} else { 211 execsql { 212 INSERT INTO t1 VALUES(1, 'one'); 213 INSERT INTO t1 VALUES(2, 'two'); 214 } 215} 216 217# Update-hook + ATTACH 218set ::update_hook {} 219ifcapable attach { 220 do_test hook-4.2.3 { 221 file delete -force test2.db 222 execsql { 223 ATTACH 'test2.db' AS aux; 224 CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b); 225 INSERT INTO aux.t3 SELECT * FROM t1; 226 UPDATE t3 SET b = 'two or so' WHERE a = 2; 227 DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now) 228 } 229 set ::update_hook 230 } [list \ 231 INSERT aux t3 1 \ 232 INSERT aux t3 2 \ 233 UPDATE aux t3 2 \ 234 DELETE aux t3 1 \ 235 DELETE aux t3 2 \ 236 ] 237} 238 239ifcapable trigger { 240 execsql { 241 DROP TRIGGER t1_trigger; 242 } 243} 244 245# Test that other vdbe operations involving btree structures do not 246# incorrectly invoke the update-hook. 247set ::update_hook {} 248do_test hook-4.3.1 { 249 execsql { 250 CREATE INDEX t1_i ON t1(b); 251 INSERT INTO t1 VALUES(3, 'three'); 252 UPDATE t1 SET b = ''; 253 DELETE FROM t1 WHERE a > 1; 254 } 255 set ::update_hook 256} [list \ 257 INSERT main t1 3 \ 258 UPDATE main t1 1 \ 259 UPDATE main t1 2 \ 260 UPDATE main t1 3 \ 261 DELETE main t1 2 \ 262 DELETE main t1 3 \ 263] 264set ::update_hook {} 265ifcapable compound&&attach { 266 do_test hook-4.3.2 { 267 execsql { 268 SELECT * FROM t1 UNION SELECT * FROM t3; 269 SELECT * FROM t1 UNION ALL SELECT * FROM t3; 270 SELECT * FROM t1 INTERSECT SELECT * FROM t3; 271 SELECT * FROM t1 EXCEPT SELECT * FROM t3; 272 SELECT * FROM t1 ORDER BY b; 273 SELECT * FROM t1 GROUP BY b; 274 } 275 set ::update_hook 276 } [list] 277} 278db update_hook {} 279# 280#---------------------------------------------------------------------------- 281 282#---------------------------------------------------------------------------- 283# Test the rollback-hook. The rollback-hook is a bit more complicated than 284# either the commit or update hooks because a rollback can happen 285# explicitly (an sql ROLLBACK statement) or implicitly (a constraint or 286# error condition). 287# 288# hook-5.1.* - Test explicit rollbacks. 289# hook-5.2.* - Test implicit rollbacks caused by constraint failure. 290# 291# hook-5.3.* - Test implicit rollbacks caused by IO errors. 292# hook-5.4.* - Test implicit rollbacks caused by malloc() failure. 293# hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook 294# not be called for these? 295# 296 297do_test hook-5.0 { 298 # Configure the rollback hook to increment global variable 299 # $::rollback_hook each time it is invoked. 300 set ::rollback_hook 0 301 db rollback_hook [list incr ::rollback_hook] 302} {} 303 304# Test explicit rollbacks. Not much can really go wrong here. 305# 306do_test hook-5.1.1 { 307 set ::rollback_hook 0 308 execsql { 309 BEGIN; 310 ROLLBACK; 311 } 312 set ::rollback_hook 313} {1} 314 315# Test implicit rollbacks caused by constraints. 316# 317do_test hook-5.2.1 { 318 set ::rollback_hook 0 319 catchsql { 320 DROP TABLE t1; 321 CREATE TABLE t1(a PRIMARY KEY, b); 322 INSERT INTO t1 VALUES('one', 'I'); 323 INSERT INTO t1 VALUES('one', 'I'); 324 } 325 set ::rollback_hook 326} {1} 327do_test hook-5.2.2 { 328 # Check that the INSERT transaction above really was rolled back. 329 execsql { 330 SELECT count(*) FROM t1; 331 } 332} {1} 333 334# 335# End rollback-hook testing. 336#---------------------------------------------------------------------------- 337 338#---------------------------------------------------------------------------- 339# Test that if a commit-hook returns non-zero (causing a rollback), the 340# rollback-hook is invoked. 341# 342proc commit_hook {} { 343 lappend ::hooks COMMIT 344 return 1 345} 346proc rollback_hook {} { 347 lappend ::hooks ROLLBACK 348} 349do_test hook-6.1 { 350 set ::hooks [list] 351 db commit_hook commit_hook 352 db rollback_hook rollback_hook 353 catchsql { 354 BEGIN; 355 INSERT INTO t1 VALUES('two', 'II'); 356 COMMIT; 357 } 358 execsql { SELECT * FROM t1 } 359} {one I} 360do_test hook-6.2 { 361 set ::hooks 362} {COMMIT ROLLBACK} 363unset ::hooks 364 365#---------------------------------------------------------------------------- 366# The following tests - hook-7.* - test the pre-update hook. 367# 368# 7.1.1 - INSERT statement. 369# 7.1.2 - INSERT INTO ... SELECT statement. 370# 7.1.3 - REPLACE INTO ... (rowid conflict) 371# 7.1.4 - REPLACE INTO ... (other index conflicts) 372# 7.1.5 - REPLACE INTO ... (both rowid and other index conflicts) 373# 374# 7.2.1 - DELETE statement. 375# 7.2.2 - DELETE statement that uses the truncate optimization. 376# 377# 7.3.1 - UPDATE statement. 378# 7.3.2 - UPDATE statement that modifies the rowid. 379# 7.3.3 - UPDATE OR REPLACE ... (rowid conflict). 380# 7.3.4 - UPDATE OR REPLACE ... (other index conflicts) 381# 7.3.4 - UPDATE OR REPLACE ... (both rowid and other index conflicts) 382# 383# 7.4.1 - Test that the pre-update-hook is invoked only once if a row being 384# deleted is removed by a BEFORE trigger. 385# 386# 7.4.2 - Test that the pre-update-hook is invoked if a BEFORE trigger 387# removes a row being updated. In this case the update hook should 388# be invoked with SQLITE_INSERT as the opcode when inserting the 389# new version of the row. 390# 391# TODO: Short records (those created before a column is added to a table 392# using ALTER TABLE) 393# 394 395proc do_preupdate_test {tn sql x} { 396 set X [list] 397 foreach elem $x {lappend X $elem} 398 uplevel do_test $tn [list " 399 set ::preupdate \[list\] 400 execsql { $sql } 401 set ::preupdate 402 "] [list $X] 403} 404 405proc preupdate_hook {args} { 406 set type [lindex $args 0] 407 eval lappend ::preupdate $args 408 if {$type != "INSERT"} { 409 for {set i 0} {$i < [db preupdate count]} {incr i} { 410 lappend ::preupdate [db preupdate old $i] 411 } 412 } 413 if {$type != "DELETE"} { 414 for {set i 0} {$i < [db preupdate count]} {incr i} { 415 set rc [catch { db preupdate new $i } v] 416 lappend ::preupdate $v 417 } 418 } 419} 420 421db close 422forcedelete test.db 423sqlite3 db test.db 424db preupdate hook preupdate_hook 425 426# Set up a schema to use for tests 7.1.* to 7.3.*. 427do_execsql_test 7.0 { 428 CREATE TABLE t1(a, b); 429 CREATE TABLE t2(x, y); 430 CREATE TABLE t3(i, j, UNIQUE(i)); 431 432 INSERT INTO t2 VALUES('a', 'b'); 433 INSERT INTO t2 VALUES('c', 'd'); 434 435 INSERT INTO t3 VALUES(4, 16); 436 INSERT INTO t3 VALUES(5, 25); 437 INSERT INTO t3 VALUES(6, 36); 438} 439 440do_preupdate_test 7.1.1 { 441 INSERT INTO t1 VALUES('x', 'y') 442} {INSERT main t1 1 1 x y} 443 444# 7.1.2.1 does not use the xfer optimization. 7.1.2.2 does. 445do_preupdate_test 7.1.2.1 { 446 INSERT INTO t1 SELECT y, x FROM t2; 447} {INSERT main t1 2 2 b a INSERT main t1 3 3 d c} 448do_preupdate_test 7.1.2.2 { 449 INSERT INTO t1 SELECT * FROM t2; 450} {INSERT main t1 4 4 a b INSERT main t1 5 5 c d} 451 452do_preupdate_test 7.1.3 { 453 REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1); 454} { 455 DELETE main t1 1 1 x y 456 INSERT main t1 1 1 1 1 457} 458 459do_preupdate_test 7.1.4 { 460 REPLACE INTO t3 VALUES(4, NULL); 461} { 462 DELETE main t3 1 1 4 16 463 INSERT main t3 4 4 4 {} 464} 465 466do_preupdate_test 7.1.5 { 467 REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL); 468} { 469 DELETE main t3 2 2 5 25 470 DELETE main t3 3 3 6 36 471 INSERT main t3 2 2 6 {} 472} 473 474do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5} 475 476do_preupdate_test 7.2.1 { 477 DELETE FROM t1 WHERE rowid = 3 478} { 479 DELETE main t1 3 3 d c 480} 481do_preupdate_test 7.2.2 { 482 DELETE FROM t1 483} { 484 DELETE main t3 1 1 1 1 485 DELETE main t3 2 2 b a 486 DELETE main t3 4 4 a b 487 DELETE main t3 5 5 c d 488} 489 490do_execsql_test 7.3.0 { 491 DELETE FROM t1; 492 DELETE FROM t2; 493 DELETE FROM t3; 494 495 INSERT INTO t2 VALUES('a', 'b'); 496 INSERT INTO t2 VALUES('c', 'd'); 497 498 INSERT INTO t3 VALUES(4, 16); 499 INSERT INTO t3 VALUES(5, 25); 500 INSERT INTO t3 VALUES(6, 36); 501} 502 503do_preupdate_test 7.3.1 { 504 UPDATE t2 SET y = y||y; 505} { 506 UPDATE main t2 1 1 a b a bb 507 UPDATE main t2 2 2 c d c dd 508} 509 510do_preupdate_test 7.3.2 { 511 UPDATE t2 SET rowid = rowid-1; 512} { 513 UPDATE main t2 1 0 a bb a bb 514 UPDATE main t2 2 1 c dd c dd 515} 516 517do_preupdate_test 7.3.3 { 518 UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a' 519} { 520 DELETE main t2 1 1 c dd 521 UPDATE main t2 0 1 a bb a bb 522} 523 524do_preupdate_test 7.3.4.1 { 525 UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6 526} { 527 DELETE main t3 2 2 5 25 528 UPDATE main t3 3 3 6 36 5 36 529} 530 531do_execsql_test 7.3.4.2 { 532 INSERT INTO t3 VALUES(10, 100); 533 SELECT rowid, * FROM t3; 534} {1 4 16 3 5 36 4 10 100} 535 536do_preupdate_test 7.3.5 { 537 UPDATE OR REPLACE t3 SET rowid = 1, i = 5 WHERE j = 100; 538} { 539 DELETE main t3 1 1 4 16 540 DELETE main t3 3 3 5 36 541 UPDATE main t3 4 1 10 100 5 100 542} 543 544do_execsql_test 7.4.1.0 { 545 CREATE TABLE t4(a, b); 546 INSERT INTO t4 VALUES('a', 1); 547 INSERT INTO t4 VALUES('b', 2); 548 INSERT INTO t4 VALUES('c', 3); 549 550 CREATE TRIGGER t4t BEFORE DELETE ON t4 BEGIN 551 DELETE FROM t4 WHERE b = 1; 552 END; 553} 554 555do_preupdate_test 7.4.1.1 { 556 DELETE FROM t4 WHERE b = 3 557} { 558 DELETE main t4 1 1 a 1 559 DELETE main t4 3 3 c 3 560} 561 562do_execsql_test 7.4.1.2 { 563 INSERT INTO t4(rowid, a, b) VALUES(1, 'a', 1); 564 INSERT INTO t4(rowid, a, b) VALUES(3, 'c', 3); 565} 566do_preupdate_test 7.4.1.3 { 567 DELETE FROM t4 WHERE b = 1 568} { 569 DELETE main t4 1 1 a 1 570} 571 572do_execsql_test 7.4.2.0 { 573 CREATE TABLE t5(a, b); 574 INSERT INTO t5 VALUES('a', 1); 575 INSERT INTO t5 VALUES('b', 2); 576 INSERT INTO t5 VALUES('c', 3); 577 578 CREATE TRIGGER t5t BEFORE UPDATE ON t5 BEGIN 579 DELETE FROM t5 WHERE b = 1; 580 END; 581} 582do_preupdate_test 7.4.2.1 { 583 UPDATE t5 SET b = 4 WHERE a = 'c' 584} { 585 DELETE main t5 1 1 a 1 586 UPDATE main t5 3 3 c 3 c 4 587} 588 589do_execsql_test 7.4.2.2 { 590 INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1); 591} 592 593do_preupdate_test 7.4.2.3 { 594 UPDATE t5 SET b = 5 WHERE a = 'a' 595} { 596 DELETE main t5 1 1 a 1 597} 598 599do_execsql_test 7.5.1.0 { 600 CREATE TABLE t7(a, b); 601 INSERT INTO t7 VALUES('one', 'two'); 602 INSERT INTO t7 VALUES('three', 'four'); 603 ALTER TABLE t7 ADD COLUMN c DEFAULT NULL; 604} 605 606do_preupdate_test 7.5.1.1 { 607 DELETE FROM t7 WHERE a = 'one' 608} { 609 DELETE main t7 1 1 one two {} 610} 611 612do_preupdate_test 7.5.1.2 { 613 UPDATE t7 SET b = 'five' 614} { 615 UPDATE main t7 2 2 three four {} three five {} 616} 617 618do_execsql_test 7.5.2.0 { 619 CREATE TABLE t8(a, b); 620 INSERT INTO t8 VALUES('one', 'two'); 621 INSERT INTO t8 VALUES('three', 'four'); 622 ALTER TABLE t8 ADD COLUMN c DEFAULT 'xxx'; 623} 624 625# At time of writing, these two are broken. They demonstraight that the 626# sqlite3_preupdate_old() method does not handle the case where ALTER TABLE 627# has been used to add a column with a default value other than NULL. 628# 629do_preupdate_test 7.5.2.1 { 630 DELETE FROM t8 WHERE a = 'one' 631} { 632 DELETE main t8 1 1 one two xxx 633} 634do_preupdate_test 7.5.2.2 { 635 UPDATE t8 SET b = 'five' 636} { 637 UPDATE main t8 2 2 three four xxx three five xxx 638} 639 640finish_test 641 642