AryaWu/sqlite
0
1# 2004 Jan 142#3# The author disclaims copyright to this source code. In place of4# 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 the12# 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# sqlite_preupdate_hook (tests hook-7..hook-12)20#21# $Id: hook.test,v 1.15 2009/04/07 14:14:23 danielk1977 Exp $22 23set testdir [file dirname $argv0]24source $testdir/tester.tcl25set ::testprefix hook26 27do_test hook-1.2 {28 db commit_hook29} {}30 31 32do_test hook-3.1 {33 set commit_cnt 034 proc commit_hook {} {35 incr ::commit_cnt36 return 037 }38 db commit_hook ::commit_hook39 db commit_hook40} {::commit_hook}41do_test hook-3.2 {42 set commit_cnt43} {0}44do_test hook-3.3 {45 execsql {46 CREATE TABLE t2(a,b);47 }48 set commit_cnt49} {1}50do_test hook-3.4 {51 execsql {52 INSERT INTO t2 VALUES(1,2);53 INSERT INTO t2 SELECT a+1, b+1 FROM t2;54 INSERT INTO t2 SELECT a+2, b+2 FROM t2;55 }56 set commit_cnt57} {4}58do_test hook-3.5 {59 set commit_cnt {}60 proc commit_hook {} {61 set ::commit_cnt [execsql {SELECT * FROM t2}]62 return 063 }64 execsql {65 INSERT INTO t2 VALUES(5,6);66 }67 set commit_cnt68} {1 2 2 3 3 4 4 5 5 6}69do_test hook-3.6 {70 set commit_cnt {}71 proc commit_hook {} {72 set ::commit_cnt [execsql {SELECT * FROM t2}] 73 return 174 }75 catchsql {76 INSERT INTO t2 VALUES(6,7);77 }78} {1 {constraint failed}}79verify_ex_errcode hook-3.6b SQLITE_CONSTRAINT_COMMITHOOK80do_test hook-3.7 {81 set ::commit_cnt82} {1 2 2 3 3 4 4 5 5 6 6 7}83do_test hook-3.8 {84 execsql {SELECT * FROM t2}85} {1 2 2 3 3 4 4 5 5 6}86 87# Test turnning off the commit hook88#89do_test hook-3.9 {90 db commit_hook {}91 set ::commit_cnt {}92 execsql {93 INSERT INTO t2 VALUES(7,8);94 }95 set ::commit_cnt96} {}97 98# Ticket #3564.99#100do_test hook-3.10 {101 forcedelete test2.db test2.db-journal102 sqlite3 db2 test2.db103 proc commit_hook {} {104 set y [db2 one {SELECT y FROM t3 WHERE y>10}]105 return [expr {$y>10}]106 }107 db2 eval {CREATE TABLE t3(x,y)}108 db2 commit_hook commit_hook109 catchsql {INSERT INTO t3 VALUES(1,2)} db2110 catchsql {INSERT INTO t3 VALUES(11,12)} db2111 catchsql {INSERT INTO t3 VALUES(3,4)} db2112 db2 eval {113 SELECT * FROM t3 ORDER BY x;114 }115} {1 2 3 4}116db2 close117 118 119#----------------------------------------------------------------------------120# Tests for the update-hook.121#122# 4.1.* - Very simple tests. Test that the update hook is invoked correctly 123# for INSERT, DELETE and UPDATE statements, including DELETE 124# statements with no WHERE clause.125# 4.2.* - Check that the update-hook is invoked for rows modified by trigger126# bodies. Also that the database name is correctly reported when 127# an attached database is modified.128# 4.3.* - Do some sorting, grouping, compound queries, population and 129# depopulation of indices, to make sure the update-hook is not 130# invoked incorrectly.131#132# EVIDENCE-OF: R-21999-45122 The sqlite3_update_hook() interface133# registers a callback function with the database connection identified134# by the first argument to be invoked whenever a row is updated,135# inserted or deleted in a rowid table.136 137# Simple tests138do_test hook-4.1.1a {139 catchsql {140 DROP TABLE t1;141 }142 unset -nocomplain ::update_hook143 set ::update_hook {}144 db update_hook [list lappend ::update_hook]145 #146 # EVIDENCE-OF: R-24531-54682 The update hook is not invoked when147 # internal system tables are modified (i.e. sqlite_sequence).148 #149 execsql {150 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);151 CREATE TABLE t1w(a INT PRIMARY KEY, b) WITHOUT ROWID;152 }153 set ::update_hook154} {}155do_test hook-4.1.1b {156 execsql {157 INSERT INTO t1 VALUES(1, 'one');158 INSERT INTO t1 VALUES(2, 'two');159 INSERT INTO t1 VALUES(3, 'three');160 INSERT INTO t1w SELECT * FROM t1;161 }162} {}163 164# EVIDENCE-OF: R-15506-57666 The second callback argument is one of165# SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE, depending on the166# operation that caused the callback to be invoked.167#168# EVIDENCE-OF: R-29213-61195 The third and fourth arguments to the169# callback contain pointers to the database and table name containing170# the affected row.171#172# EVIDENCE-OF: R-30809-57812 The final callback parameter is the rowid173# of the row.174#175do_test hook-4.1.2 {176 set ::update_hook {}177 execsql {178 INSERT INTO t1 VALUES(4, 'four');179 DELETE FROM t1 WHERE b = 'two';180 UPDATE t1 SET b = '' WHERE a = 1 OR a = 3;181 DELETE FROM t1 WHERE 1; -- Avoid the truncate optimization (for now)182 }183 set ::update_hook184} [list \185 INSERT main t1 4 \186 DELETE main t1 2 \187 UPDATE main t1 1 \188 UPDATE main t1 3 \189 DELETE main t1 1 \190 DELETE main t1 3 \191 DELETE main t1 4 \192]193 194# EVIDENCE-OF: R-61808-14344 The sqlite3_update_hook() interface does195# not fire callbacks for changes to a WITHOUT ROWID table.196#197# EVIDENCE-OF: R-33257-44249 The update hook is not invoked when WITHOUT198# ROWID tables are modified.199#200do_test hook-4.1.2w {201 set ::update_hook {}202 execsql {203 INSERT INTO t1w VALUES(4, 'four');204 DELETE FROM t1w WHERE b = 'two';205 UPDATE t1w SET b = '' WHERE a = 1 OR a = 3;206 DELETE FROM t1w WHERE 1; -- Avoid the truncate optimization (for now)207 }208 set ::update_hook209} {}210 211ifcapable trigger {212 # Update hook is not invoked for changes to sqlite_master213 #214 do_test hook-4.1.3 {215 set ::update_hook {}216 execsql {217 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;218 }219 set ::update_hook220 } {}221 do_test hook-4.1.4 {222 set ::update_hook {}223 execsql {224 DROP TRIGGER r1;225 }226 set ::update_hook227 } {}228 229 set ::update_hook {}230 do_test hook-4.2.1 {231 catchsql {232 DROP TABLE t2;233 }234 execsql {235 CREATE TABLE t2(c INTEGER PRIMARY KEY, d);236 CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN237 INSERT INTO t2 VALUES(new.a, new.b);238 UPDATE t2 SET d = d || ' via trigger' WHERE new.a = c;239 DELETE FROM t2 WHERE new.a = c;240 END;241 }242 } {}243 do_test hook-4.2.2 {244 execsql {245 INSERT INTO t1 VALUES(1, 'one');246 INSERT INTO t1 VALUES(2, 'two');247 }248 set ::update_hook249 } [list \250 INSERT main t1 1 \251 INSERT main t2 1 \252 UPDATE main t2 1 \253 DELETE main t2 1 \254 INSERT main t1 2 \255 INSERT main t2 2 \256 UPDATE main t2 2 \257 DELETE main t2 2 \258 ]259} else {260 execsql {261 INSERT INTO t1 VALUES(1, 'one');262 INSERT INTO t1 VALUES(2, 'two');263 }264}265 266# Update-hook + ATTACH267set ::update_hook {}268ifcapable attach {269 do_test hook-4.2.3 {270 forcedelete test2.db271 execsql {272 ATTACH 'test2.db' AS aux;273 CREATE TABLE aux.t3(a INTEGER PRIMARY KEY, b);274 INSERT INTO aux.t3 SELECT * FROM t1;275 UPDATE t3 SET b = 'two or so' WHERE a = 2;276 DELETE FROM t3 WHERE 1; -- Avoid the truncate optimization (for now)277 }278 set ::update_hook279 } [list \280 INSERT aux t3 1 \281 INSERT aux t3 2 \282 UPDATE aux t3 2 \283 DELETE aux t3 1 \284 DELETE aux t3 2 \285 ]286}287 288ifcapable trigger {289 execsql {290 DROP TRIGGER t1_trigger;291 }292}293 294# Test that other vdbe operations involving btree structures do not 295# incorrectly invoke the update-hook.296set ::update_hook {}297do_test hook-4.3.1 {298 execsql {299 CREATE INDEX t1_i ON t1(b);300 INSERT INTO t1 VALUES(3, 'three');301 UPDATE t1 SET b = '';302 DELETE FROM t1 WHERE a > 1;303 }304 set ::update_hook305} [list \306 INSERT main t1 3 \307 UPDATE main t1 1 \308 UPDATE main t1 2 \309 UPDATE main t1 3 \310 DELETE main t1 2 \311 DELETE main t1 3 \312]313set ::update_hook {}314ifcapable compound&&attach {315 do_test hook-4.3.2 {316 execsql {317 SELECT * FROM t1 UNION SELECT * FROM t3;318 SELECT * FROM t1 UNION ALL SELECT * FROM t3;319 SELECT * FROM t1 INTERSECT SELECT * FROM t3;320 SELECT * FROM t1 EXCEPT SELECT * FROM t3;321 SELECT * FROM t1 ORDER BY b;322 SELECT * FROM t1 GROUP BY b;323 }324 set ::update_hook325 } [list]326}327 328do_test hook-4.4 {329 execsql {330 CREATE TABLE t4(a UNIQUE, b);331 INSERT INTO t4 VALUES(1, 'a');332 INSERT INTO t4 VALUES(2, 'b');333 }334 set ::update_hook [list]335 execsql {336 REPLACE INTO t4 VALUES(1, 'c');337 }338 set ::update_hook339} [list INSERT main t4 3 ]340do_execsql_test hook-4.4.1 {341 SELECT * FROM t4 ORDER BY a;342} {1 c 2 b}343do_test hook-4.4.2 {344 set ::update_hook [list]345 execsql {346 PRAGMA recursive_triggers = on;347 REPLACE INTO t4 VALUES(1, 'd');348 }349 set ::update_hook350} [list INSERT main t4 4 ]351do_execsql_test hook-4.4.3 {352 SELECT * FROM t4 ORDER BY a;353} {1 d 2 b}354 355db update_hook {}356#357#----------------------------------------------------------------------------358 359#----------------------------------------------------------------------------360# Test the rollback-hook. The rollback-hook is a bit more complicated than361# either the commit or update hooks because a rollback can happen 362# explicitly (an sql ROLLBACK statement) or implicitly (a constraint or 363# error condition).364#365# hook-5.1.* - Test explicit rollbacks.366# hook-5.2.* - Test implicit rollbacks caused by constraint failure.367#368# hook-5.3.* - Test implicit rollbacks caused by IO errors.369# hook-5.4.* - Test implicit rollbacks caused by malloc() failure.370# hook-5.5.* - Test hot-journal rollbacks. Or should the rollback hook 371# not be called for these?372#373 374do_test hook-5.0 {375 # Configure the rollback hook to increment global variable 376 # $::rollback_hook each time it is invoked.377 set ::rollback_hook 0378 db rollback_hook [list incr ::rollback_hook]379} {}380 381# Test explicit rollbacks. Not much can really go wrong here.382#383do_test hook-5.1.1 {384 set ::rollback_hook 0385 execsql {386 BEGIN;387 ROLLBACK;388 }389 set ::rollback_hook390} {1}391 392# Test implicit rollbacks caused by constraints.393#394do_test hook-5.2.1 {395 set ::rollback_hook 0396 catchsql {397 DROP TABLE t1;398 CREATE TABLE t1(a PRIMARY KEY, b);399 INSERT INTO t1 VALUES('one', 'I');400 INSERT INTO t1 VALUES('one', 'I');401 }402 set ::rollback_hook403} {1}404do_test hook-5.2.2 {405 # Check that the INSERT transaction above really was rolled back.406 execsql {407 SELECT count(*) FROM t1;408 }409} {1}410 411#412# End rollback-hook testing.413#----------------------------------------------------------------------------414 415#----------------------------------------------------------------------------416# Test that if a commit-hook returns non-zero (causing a rollback), the417# rollback-hook is invoked.418#419proc commit_hook {} {420 lappend ::hooks COMMIT421 return 1422}423proc rollback_hook {} {424 lappend ::hooks ROLLBACK425}426do_test hook-6.1 {427 set ::hooks [list]428 db commit_hook commit_hook429 db rollback_hook rollback_hook430 catchsql {431 BEGIN;432 INSERT INTO t1 VALUES('two', 'II');433 COMMIT;434 }435 execsql { SELECT * FROM t1 }436} {one I}437do_test hook-6.2 {438 set ::hooks439} {COMMIT ROLLBACK}440unset ::hooks441 442#----------------------------------------------------------------------------443# The following tests - hook-7.* - test the pre-update hook.444#445ifcapable !preupdate {446 finish_test447 return448}449#450# 7.1.1 - INSERT statement.451# 7.1.2 - INSERT INTO ... SELECT statement.452# 7.1.3 - REPLACE INTO ... (rowid conflict)453# 7.1.4 - REPLACE INTO ... (other index conflicts)454# 7.1.5 - REPLACE INTO ... (both rowid and other index conflicts)455#456# 7.2.1 - DELETE statement.457# 7.2.2 - DELETE statement that uses the truncate optimization.458#459# 7.3.1 - UPDATE statement.460# 7.3.2 - UPDATE statement that modifies the rowid.461# 7.3.3 - UPDATE OR REPLACE ... (rowid conflict).462# 7.3.4 - UPDATE OR REPLACE ... (other index conflicts)463# 7.3.4 - UPDATE OR REPLACE ... (both rowid and other index conflicts)464#465# 7.4.1 - Test that the pre-update-hook is invoked only once if a row being466# deleted is removed by a BEFORE trigger.467#468# 7.4.2 - Test that the pre-update-hook is invoked if a BEFORE trigger 469# removes a row being updated. In this case the update hook should470# be invoked with SQLITE_INSERT as the opcode when inserting the471# new version of the row.472# 473# TODO: Short records (those created before a column is added to a table 474# using ALTER TABLE)475#476 477proc do_preupdate_test {tn sql x} {478 set X [list]479 foreach elem $x {lappend X $elem}480 uplevel do_test $tn [list "481 set ::preupdate \[list\]482 execsql { $sql }483 set ::preupdate484 "] [list $X]485}486 487proc preupdate_hook {args} {488 set type [lindex $args 0]489 eval lappend ::preupdate $args490 if {$type != "INSERT"} {491 set x [catch {db preupdate old [db preupdate count]}]492 if {!$x} {493 lappend "ERROR: sqlite3_preupdate_old() accepted an out-of-bounds\494 column index"495 }496 for {set i 0} {$i < [db preupdate count]} {incr i} {497 lappend ::preupdate [db preupdate old $i]498 }499 }500 if {$type != "DELETE"} {501 set x [catch {db preupdate new [db preupdate count]}]502 if {!$x} {503 lappend "ERROR: sqlite3_preupdate_old() accepted an out-of-bounds\504 column index"505 }506 for {set i 0} {$i < [db preupdate count]} {incr i} {507 set rc [catch { db preupdate new $i } v]508 lappend ::preupdate $v509 }510 }511}512 513db close514forcedelete test.db515sqlite3 db test.db516db preupdate hook preupdate_hook517 518# Set up a schema to use for tests 7.1.* to 7.3.*.519do_execsql_test 7.0 { 520 CREATE TABLE t1(a, b); 521 CREATE TABLE t2(x, y); 522 CREATE TABLE t3(i, j, UNIQUE(i));523 524 INSERT INTO t2 VALUES('a', 'b');525 INSERT INTO t2 VALUES('c', 'd');526 527 INSERT INTO t3 VALUES(4, 16);528 INSERT INTO t3 VALUES(5, 25);529 INSERT INTO t3 VALUES(6, 36);530} 531 532do_preupdate_test 7.1.1 {533 INSERT INTO t1 VALUES('x', 'y')534} {INSERT main t1 1 1 x y}535 536# 7.1.2.1 does not use the xfer optimization. 7.1.2.2 does.537do_preupdate_test 7.1.2.1 {538 INSERT INTO t1 SELECT y, x FROM t2;539} {INSERT main t1 2 2 b a INSERT main t1 3 3 d c}540do_preupdate_test 7.1.2.2 {541 INSERT INTO t1 SELECT * FROM t2;542} {INSERT main t1 4 4 a b INSERT main t1 5 5 c d}543 544do_preupdate_test 7.1.3 {545 REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1);546} {547 DELETE main t1 1 1 x y548 INSERT main t1 1 1 1 1549}550 551do_preupdate_test 7.1.4 {552 REPLACE INTO t3 VALUES(4, NULL);553} {554 DELETE main t3 1 1 4 16555 INSERT main t3 4 4 4 {}556}557 558do_preupdate_test 7.1.5 {559 REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL);560} {561 DELETE main t3 2 2 5 25562 DELETE main t3 3 3 6 36563 INSERT main t3 2 2 6 {}564}565 566do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5}567 568do_preupdate_test 7.2.1 {569 DELETE FROM t1 WHERE rowid = 3570} {571 DELETE main t1 3 3 d c572}573do_preupdate_test 7.2.2 {574 DELETE FROM t1575} {576 DELETE main t1 1 1 1 1577 DELETE main t1 2 2 b a578 DELETE main t1 4 4 a b579 DELETE main t1 5 5 c d580}581 582do_execsql_test 7.3.0 { 583 DELETE FROM t1;584 DELETE FROM t2;585 DELETE FROM t3;586 587 INSERT INTO t2 VALUES('a', 'b');588 INSERT INTO t2 VALUES('c', 'd');589 590 INSERT INTO t3 VALUES(4, 16);591 INSERT INTO t3 VALUES(5, 25);592 INSERT INTO t3 VALUES(6, 36);593}594 595do_preupdate_test 7.3.1 {596 UPDATE t2 SET y = y||y;597} {598 UPDATE main t2 1 1 a b a bb599 UPDATE main t2 2 2 c d c dd600}601 602do_preupdate_test 7.3.2 {603 UPDATE t2 SET rowid = rowid-1;604} {605 UPDATE main t2 1 0 a bb a bb606 UPDATE main t2 2 1 c dd c dd607}608 609do_preupdate_test 7.3.3 {610 UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a'611} {612 DELETE main t2 1 1 c dd613 UPDATE main t2 0 1 a bb a bb614}615 616do_preupdate_test 7.3.4.1 {617 UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6618} {619 DELETE main t3 2 2 5 25620 UPDATE main t3 3 3 6 36 5 36621}622 623do_execsql_test 7.3.4.2 {624 INSERT INTO t3 VALUES(10, 100);625 SELECT rowid, * FROM t3;626} {1 4 16 3 5 36 4 10 100}627 628do_preupdate_test 7.3.5 {629 UPDATE OR REPLACE t3 SET rowid = 1, i = 5 WHERE j = 100;630} {631 DELETE main t3 1 1 4 16632 DELETE main t3 3 3 5 36633 UPDATE main t3 4 1 10 100 5 100634}635 636do_execsql_test 7.4.1.0 {637 CREATE TABLE t4(a, b);638 INSERT INTO t4 VALUES('a', 1);639 INSERT INTO t4 VALUES('b', 2);640 INSERT INTO t4 VALUES('c', 3);641 642 CREATE TRIGGER t4t BEFORE DELETE ON t4 BEGIN643 DELETE FROM t4 WHERE b = 1;644 END;645}646 647do_preupdate_test 7.4.1.1 {648 DELETE FROM t4 WHERE b = 3649} {650 DELETE main t4 1 1 a 1651 DELETE main t4 3 3 c 3652}653 654do_execsql_test 7.4.1.2 {655 INSERT INTO t4(rowid, a, b) VALUES(1, 'a', 1);656 INSERT INTO t4(rowid, a, b) VALUES(3, 'c', 3);657}658do_preupdate_test 7.4.1.3 {659 DELETE FROM t4 WHERE b = 1660} {661 DELETE main t4 1 1 a 1662}663 664do_execsql_test 7.4.2.0 {665 CREATE TABLE t5(a, b);666 INSERT INTO t5 VALUES('a', 1);667 INSERT INTO t5 VALUES('b', 2);668 INSERT INTO t5 VALUES('c', 3);669 670 CREATE TRIGGER t5t BEFORE UPDATE ON t5 BEGIN671 DELETE FROM t5 WHERE b = 1;672 END;673}674do_preupdate_test 7.4.2.1 {675 UPDATE t5 SET b = 4 WHERE a = 'c'676} {677 DELETE main t5 1 1 a 1678 UPDATE main t5 3 3 c 3 c 4679}680 681do_execsql_test 7.4.2.2 {682 INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1);683}684 685do_preupdate_test 7.4.2.3 {686 UPDATE t5 SET b = 5 WHERE a = 'a'687} {688 DELETE main t5 1 1 a 1689}690 691ifcapable altertable {692 do_execsql_test 7.5.1.0 {693 CREATE TABLE t7(a, b);694 INSERT INTO t7 VALUES('one', 'two');695 INSERT INTO t7 VALUES('three', 'four');696 ALTER TABLE t7 ADD COLUMN c DEFAULT NULL;697 }698 699 do_preupdate_test 7.5.1.1 {700 DELETE FROM t7 WHERE a = 'one'701 } {702 DELETE main t7 1 1 one two {}703 }704 705 do_preupdate_test 7.5.1.2 {706 UPDATE t7 SET b = 'five'707 } {708 UPDATE main t7 2 2 three four {} three five {}709 }710 711 do_execsql_test 7.5.2.0 {712 CREATE TABLE t8(a, b);713 INSERT INTO t8 VALUES('one', 'two');714 INSERT INTO t8 VALUES('three', 'four');715 ALTER TABLE t8 ADD COLUMN c DEFAULT 'xxx';716 }717}718 719if 1 {720 # At time of writing, these two are broken. They demonstrate that the721 # sqlite3_preupdate_old() method does not handle the case where ALTER TABLE722 # has been used to add a column with a default value other than NULL.723 #724 # 2024-09-18: These are now fixed.725 #726 do_preupdate_test 7.5.2.1 {727 DELETE FROM t8 WHERE a = 'one'728 } {729 DELETE main t8 1 1 one two xxx730 }731 do_preupdate_test 7.5.2.2 {732 UPDATE t8 SET b = 'five'733 } {734 UPDATE main t8 2 2 three four xxx three five xxx735 }736}737 738# This block of tests verifies that IPK values are correctly reported739# by the sqlite3_preupdate_old() and sqlite3_preupdate_new() functions.740#741do_execsql_test 7.6.1 { CREATE TABLE t9(a, b INTEGER PRIMARY KEY, c) }742do_preupdate_test 7.6.2 {743 INSERT INTO t9 VALUES(1, 2, 3);744 UPDATE t9 SET b = b+1, c = c+1;745 DELETE FROM t9 WHERE a = 1;746} {747 INSERT main t9 2 2 1 2 3748 UPDATE main t9 2 3 1 2 3 1 3 4749 DELETE main t9 3 3 1 3 4750}751 752#--------------------------------------------------------------------------753# Test that the sqlite3_preupdate_depth() API seems to work.754#755proc preupdate_hook {args} {756 set type [lindex $args 0]757 eval lappend ::preupdate $args758 eval lappend ::preupdate [db preupdate depth]759 760 if {$type != "INSERT"} {761 for {set i 0} {$i < [db preupdate count]} {incr i} {762 lappend ::preupdate [db preupdate old $i]763 }764 }765 if {$type != "DELETE"} {766 for {set i 0} {$i < [db preupdate count]} {incr i} {767 set rc [catch { db preupdate new $i } v]768 lappend ::preupdate $v769 }770 }771}772 773db close774forcedelete test.db775sqlite3 db test.db776db preupdate hook preupdate_hook777 778do_execsql_test 7.6.1 { 779 CREATE TABLE t1(x PRIMARY KEY);780 CREATE TABLE t2(x PRIMARY KEY);781 CREATE TABLE t3(x PRIMARY KEY);782 CREATE TABLE t4(x PRIMARY KEY);783 784 CREATE TRIGGER a AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;785 CREATE TRIGGER b AFTER INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;786 CREATE TRIGGER c AFTER INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;787 788 CREATE TRIGGER d AFTER UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;789 CREATE TRIGGER e AFTER UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;790 CREATE TRIGGER f AFTER UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;791 792 CREATE TRIGGER g AFTER DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;793 CREATE TRIGGER h AFTER DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;794 CREATE TRIGGER i AFTER DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;795}796 797do_preupdate_test 7.6.2 {798 INSERT INTO t1 VALUES('xyz');799} {800 INSERT main t1 1 1 0 xyz801 INSERT main t2 1 1 1 xyz802 INSERT main t3 1 1 2 xyz803 INSERT main t4 1 1 3 xyz804}805do_preupdate_test 7.6.3 {806 UPDATE t1 SET x = 'abc';807} {808 UPDATE main t1 1 1 0 xyz abc809 UPDATE main t2 1 1 1 xyz abc810 UPDATE main t3 1 1 2 xyz abc811 UPDATE main t4 1 1 3 xyz abc812}813do_preupdate_test 7.6.4 {814 DELETE FROM t1 WHERE 1;815} {816 DELETE main t1 1 1 0 abc817 DELETE main t2 1 1 1 abc818 DELETE main t3 1 1 2 abc819 DELETE main t4 1 1 3 abc820}821 822do_execsql_test 7.6.5 { 823 DROP TRIGGER a; DROP TRIGGER b; DROP TRIGGER c;824 DROP TRIGGER d; DROP TRIGGER e; DROP TRIGGER f;825 DROP TRIGGER g; DROP TRIGGER h; DROP TRIGGER i;826 827 CREATE TRIGGER a BEFORE INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;828 CREATE TRIGGER b BEFORE INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;829 CREATE TRIGGER c BEFORE INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;830 831 CREATE TRIGGER d BEFORE UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;832 CREATE TRIGGER e BEFORE UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;833 CREATE TRIGGER f BEFORE UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;834 835 CREATE TRIGGER g BEFORE DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;836 CREATE TRIGGER h BEFORE DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;837 CREATE TRIGGER i BEFORE DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;838}839 840do_preupdate_test 7.6.6 {841 INSERT INTO t1 VALUES('xyz');842} {843 INSERT main t4 1 1 3 xyz844 INSERT main t3 1 1 2 xyz845 INSERT main t2 1 1 1 xyz846 INSERT main t1 1 1 0 xyz847}848do_preupdate_test 7.6.3 {849 UPDATE t1 SET x = 'abc';850} {851 UPDATE main t4 1 1 3 xyz abc852 UPDATE main t3 1 1 2 xyz abc853 UPDATE main t2 1 1 1 xyz abc854 UPDATE main t1 1 1 0 xyz abc855}856do_preupdate_test 7.6.4 {857 DELETE FROM t1 WHERE 1;858} {859 DELETE main t4 1 1 3 abc860 DELETE main t3 1 1 2 abc861 DELETE main t2 1 1 1 abc862 DELETE main t1 1 1 0 abc863}864 865# No preupdate callbacks for modifying sqlite_master.866ifcapable altertable {867 do_preupdate_test 8.1 { CREATE TABLE x1(x, y); } { }868 do_preupdate_test 8.2 { ALTER TABLE x1 ADD COLUMN z } { }869 do_preupdate_test 8.3 { ALTER TABLE x1 RENAME TO y1 } { }870 do_preupdate_test 8.4 { CREATE INDEX y1x ON y1(x) } { }871 do_preupdate_test 8.5 { CREATE VIEW v1 AS SELECT * FROM y1 } { }872 do_preupdate_test 8.6 { DROP TABLE y1 } { }873}874 875#-------------------------------------------------------------------------876reset_db877db preupdate hook preupdate_hook878 879ifcapable altertable {880 do_execsql_test 9.0 {881 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);882 CREATE TABLE t2(a, b INTEGER PRIMARY KEY);883 }884 do_preupdate_test 9.1 {885 INSERT INTO t1 VALUES(456, NULL, NULL);886 } {887 INSERT main t1 456 456 0 456 {} {}888 }889 do_execsql_test 9.2 {890 ALTER TABLE t1 ADD COLUMN d;891 }892 do_preupdate_test 9.3 {893 INSERT INTO t1(a, b, c) VALUES(457, NULL, NULL);894 } {895 INSERT main t1 457 457 0 457 {} {} {}896 }897 do_preupdate_test 9.4 {898 DELETE FROM t1 WHERE a=456899 } {900 DELETE main t1 456 456 0 456 {} {} {}901 }902 do_preupdate_test 9.5 {903 INSERT INTO t2 DEFAULT VALUES;904 } {905 INSERT main t2 1 1 0 {} 1906 } 907 do_preupdate_test 9.6 {908 INSERT INTO t1 DEFAULT VALUES;909 } {910 INSERT main t1 458 458 0 458 {} {} {}911 } 912}913 914 915do_execsql_test 10.0 {916 CREATE TABLE t3(a, b INTEGER PRIMARY KEY);917}918do_preupdate_test 10.1 {919 INSERT INTO t3 DEFAULT VALUES920} {921 INSERT main t3 1 1 0 {} 1922}923do_execsql_test 10.2 { SELECT * FROM t3 } {{} 1}924do_preupdate_test 10.3 {925 DELETE FROM t3 WHERE b=1926} {DELETE main t3 1 1 0 {} 1}927 928#-------------------------------------------------------------------------929# Test that the "update" hook is not fired for operations on the 930# sqlite_stat1 table performed by ANALYZE, even if a pre-update hook is931# registered.932ifcapable analyze {933 reset_db934 do_execsql_test 11.1 {935 CREATE TABLE t1(a, b);936 CREATE INDEX idx1 ON t1(a);937 CREATE INDEX idx2 ON t1(b);938 939 INSERT INTO t1 VALUES(1, 2);940 INSERT INTO t1 VALUES(3, 4);941 INSERT INTO t1 VALUES(5, 6);942 INSERT INTO t1 VALUES(7, 8);943 }944 945 db preupdate hook preupdate_cb946 db update_hook update_cb947 948 proc preupdate_cb {args} { lappend ::res "preupdate" $args }949 proc update_cb {args} { lappend ::res "update" $args }950 951 set ::res [list]952 do_test 11.2 {953 execsql ANALYZE954 set ::res955 } [list {*}{956 preupdate {INSERT main sqlite_stat1 1 1}957 preupdate {INSERT main sqlite_stat1 2 2}958 }]959 960 do_execsql_test 11.3 {961 INSERT INTO t1 VALUES(9, 10);962 INSERT INTO t1 VALUES(11, 12);963 INSERT INTO t1 VALUES(13, 14);964 INSERT INTO t1 VALUES(15, 16);965 }966 967 set ::res [list]968 do_test 11.4 {969 execsql ANALYZE970 set ::res971 } [list {*}{972 preupdate {DELETE main sqlite_stat1 1 1}973 preupdate {DELETE main sqlite_stat1 2 2}974 preupdate {INSERT main sqlite_stat1 1 1}975 preupdate {INSERT main sqlite_stat1 2 2}976 }]977}978 979#-------------------------------------------------------------------------980# Test that the pre-update hook is fired for INSERT statements that use981# the xfer optimization on without rowid tables.982#983reset_db984do_execsql_test 12.1 {985 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);986 CREATE TABLE t2(a INTEGER PRIMARY KEY, b) WITHOUT ROWID;987 988 INSERT INTO t1 VALUES(1, 2);989 INSERT INTO t1 VALUES(3, 4);990 INSERT INTO t2 VALUES(5, 6);991 INSERT INTO t2 VALUES(7, 8);992 993 CREATE TABLE t3 (a INTEGER PRIMARY KEY, b) WITHOUT ROWID;994}995 996db preupdate hook preupdate_cb997db update_hook update_cb998 999proc preupdate_cb {args} { lappend ::res "preupdate" $args }1000proc update_cb {args} { lappend ::res "update" $args }1001 1002set ::res [list]1003do_test 12.2 {1004 execsql VACUUM1005 set ::res1006} {}1007 1008do_test 12.3 {1009 set ::res [list]1010 execsql { INSERT INTO t3 SELECT a, b FROM t2 }1011 set ::res1012} {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}1013 1014do_test 12.4 {1015 execsql { DELETE FROM t3 }1016 set ::res [list]1017 execsql { INSERT INTO t3 SELECT * FROM t2 }1018 set ::res1019} {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}1020 1021do_execsql_test 12.5 {1022 CREATE TABLE t4(a COLLATE nocase PRIMARY KEY, b) WITHOUT ROWID;1023 INSERT INTO t4 VALUES('abc', 1);1024 INSERT INTO t4 VALUES('DEF', 2);1025}1026 1027set ::res [list]1028do_test 12.6 {1029 execsql VACUUM1030 set ::res1031} {}1032 1033do_catchsql_test 12.6 {1034 INSERT INTO t4 VALUES('def', 3);1035} {1 {UNIQUE constraint failed: t4.a}}1036 1037#-------------------------------------------------------------------------1038# Test adding non-NULL default values using ALTER TABLE.1039#1040reset_db1041db preupdate hook preupdate_hook1042do_execsql_test 13.0 {1043 CREATE TABLE t1(a INTEGER PRIMARY KEY);1044 INSERT INTO t1 VALUES(100), (200), (300), (400);1045}1046 1047do_execsql_test 13.1 {1048 ALTER TABLE t1 ADD COLUMN b DEFAULT 1234;1049 ALTER TABLE t1 ADD COLUMN c DEFAULT 'abcdef';1050 ALTER TABLE t1 ADD COLUMN d DEFAULT NULL;1051}1052 1053do_preupdate_test 13.2 {1054 DELETE FROM t1 WHERE a=3001055} {DELETE main t1 300 300 0 300 1234 abcdef {}}1056 1057do_preupdate_test 13.3 {1058 UPDATE t1 SET d='hello world' WHERE a=2001059} {1060 UPDATE main t1 200 200 0 200 1234 abcdef {} 1061 200 1234 abcdef {hello world}1062}1063 1064do_preupdate_test 13.4 {1065 INSERT INTO t1 DEFAULT VALUES;1066} {1067 INSERT main t1 401 401 0 401 1234 abcdef {}1068}1069 1070finish_test1071