3 # The author disclaims copyright to this source code. In place of
4 # a legal notice, here is a blessing:
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.
10 #***********************************************************************
11 # This file implements regression tests for TCL interface to the
14 # The focus of the tests in this file is the following interface:
16 # sqlite_commit_hook (tests hook-1..hook-3 inclusive)
17 # sqlite_update_hook (tests hook-4-*)
18 # sqlite_rollback_hook (tests hook-5.*)
20 # $Id: hook.test,v 1.15 2009/04/07 14:14:23 danielk1977 Exp $
22 set testdir [file dirname $argv0]
23 source $testdir/tester.tcl
37 db commit_hook ::commit_hook
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;
60 set ::commit_cnt [execsql {SELECT * FROM t2}]
64 INSERT INTO t2 VALUES(5,6);
67 } {1 2 2 3 3 4 4 5 5 6}
71 set ::commit_cnt [execsql {SELECT * FROM t2}]
75 INSERT INTO t2 VALUES(6,7);
77 } {1 {constraint failed}}
78 verify_ex_errcode hook-3.6b SQLITE_CONSTRAINT_COMMITHOOK
81 } {1 2 2 3 3 4 4 5 5 6 6 7}
83 execsql {SELECT * FROM t2}
84 } {1 2 2 3 3 4 4 5 5 6}
86 # Test turnning off the commit hook
92 INSERT INTO t2 VALUES(7,8);
100 forcedelete test2.db test2.db-journal
102 proc commit_hook {} {
103 set y [db2 one {SELECT y FROM t3 WHERE y>10}]
104 return [expr {$y>10}]
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
112 SELECT * FROM t3 ORDER BY x;
118 #----------------------------------------------------------------------------
119 # Tests for the update-hook.
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.
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.
137 do_test hook-4.1.1a {
141 unset -nocomplain ::update_hook
143 db update_hook [list lappend ::update_hook]
145 # EVIDENCE-OF: R-52223-27275 The update hook is not invoked when
146 # internal system tables are modified (i.e. sqlite_master and
150 CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
151 CREATE TABLE t1w(a INT PRIMARY KEY, b) WITHOUT ROWID;
155 do_test hook-4.1.1b {
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;
164 # EVIDENCE-OF: R-15506-57666 The second callback argument is one of
165 # SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE, depending on the
166 # operation that caused the callback to be invoked.
168 # EVIDENCE-OF: R-29213-61195 The third and fourth arguments to the
169 # callback contain pointers to the database and table name containing
172 # EVIDENCE-OF: R-30809-57812 The final callback parameter is the rowid
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)
194 # EVIDENCE-OF: R-61808-14344 The sqlite3_update_hook() interface does
195 # not fire callbacks for changes to a WITHOUT ROWID table.
197 # EVIDENCE-OF: R-33257-44249 The update hook is not invoked when WITHOUT
198 # ROWID tables are modified.
200 do_test hook-4.1.2w {
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)
212 # Update hook is not invoked for changes to sqlite_master
217 CREATE TRIGGER r1 AFTER INSERT ON t1 BEGIN SELECT RAISE(IGNORE); END;
235 CREATE TABLE t2(c INTEGER PRIMARY KEY, d);
236 CREATE TRIGGER t1_trigger AFTER INSERT ON t1 BEGIN
237 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;
245 INSERT INTO t1 VALUES(1, 'one');
246 INSERT INTO t1 VALUES(2, 'two');
261 INSERT INTO t1 VALUES(1, 'one');
262 INSERT INTO t1 VALUES(2, 'two');
266 # Update-hook + ATTACH
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)
290 DROP TRIGGER t1_trigger;
294 # Test that other vdbe operations involving btree structures do not
295 # incorrectly invoke the update-hook.
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;
314 ifcapable compound&&attach {
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;
330 CREATE TABLE t4(a UNIQUE, b);
331 INSERT INTO t4 VALUES(1, 'a');
332 INSERT INTO t4 VALUES(2, 'b');
334 set ::update_hook [list]
336 REPLACE INTO t4 VALUES(1, 'c');
339 } [list INSERT main t4 3 ]
340 do_execsql_test hook-4.4.1 {
341 SELECT * FROM t4 ORDER BY a;
344 set ::update_hook [list]
346 PRAGMA recursive_triggers = on;
347 REPLACE INTO t4 VALUES(1, 'd');
350 } [list INSERT main t4 4 ]
351 do_execsql_test hook-4.4.3 {
352 SELECT * FROM t4 ORDER BY a;
357 #----------------------------------------------------------------------------
359 #----------------------------------------------------------------------------
360 # Test the rollback-hook. The rollback-hook is a bit more complicated than
361 # either the commit or update hooks because a rollback can happen
362 # explicitly (an sql ROLLBACK statement) or implicitly (a constraint or
365 # hook-5.1.* - Test explicit rollbacks.
366 # hook-5.2.* - Test implicit rollbacks caused by constraint failure.
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?
375 # Configure the rollback hook to increment global variable
376 # $::rollback_hook each time it is invoked.
377 set ::rollback_hook 0
378 db rollback_hook [list incr ::rollback_hook]
381 # Test explicit rollbacks. Not much can really go wrong here.
384 set ::rollback_hook 0
392 # Test implicit rollbacks caused by constraints.
395 set ::rollback_hook 0
398 CREATE TABLE t1(a PRIMARY KEY, b);
399 INSERT INTO t1 VALUES('one', 'I');
400 INSERT INTO t1 VALUES('one', 'I');
405 # Check that the INSERT transaction above really was rolled back.
407 SELECT count(*) FROM t1;
412 # End rollback-hook testing.
413 #----------------------------------------------------------------------------
415 #----------------------------------------------------------------------------
416 # Test that if a commit-hook returns non-zero (causing a rollback), the
417 # rollback-hook is invoked.
419 proc commit_hook {} {
420 lappend ::hooks COMMIT
423 proc rollback_hook {} {
424 lappend ::hooks ROLLBACK
428 db commit_hook commit_hook
429 db rollback_hook rollback_hook
432 INSERT INTO t1 VALUES('two', 'II');
435 execsql { SELECT * FROM t1 }
442 #----------------------------------------------------------------------------
443 # The following tests - hook-7.* - test the pre-update hook.
445 ifcapable !preupdate {
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)
456 # 7.2.1 - DELETE statement.
457 # 7.2.2 - DELETE statement that uses the truncate optimization.
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)
465 # 7.4.1 - Test that the pre-update-hook is invoked only once if a row being
466 # deleted is removed by a BEFORE trigger.
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 should
470 # be invoked with SQLITE_INSERT as the opcode when inserting the
471 # new version of the row.
473 # TODO: Short records (those created before a column is added to a table
477 proc do_preupdate_test {tn sql x} {
479 foreach elem $x {lappend X $elem}
480 uplevel do_test $tn [list "
481 set ::preupdate \[list\]
487 proc preupdate_hook {args} {
488 set type [lindex $args 0]
489 eval lappend ::preupdate $args
490 if {$type != "INSERT"} {
491 for {set i 0} {$i < [db preupdate count]} {incr i} {
492 lappend ::preupdate [db preupdate old $i]
495 if {$type != "DELETE"} {
496 for {set i 0} {$i < [db preupdate count]} {incr i} {
497 set rc [catch { db preupdate new $i } v]
498 lappend ::preupdate $v
506 db preupdate hook preupdate_hook
508 # Set up a schema to use for tests 7.1.* to 7.3.*.
509 do_execsql_test 7.0 {
510 CREATE TABLE t1(a, b);
511 CREATE TABLE t2(x, y);
512 CREATE TABLE t3(i, j, UNIQUE(i));
514 INSERT INTO t2 VALUES('a', 'b');
515 INSERT INTO t2 VALUES('c', 'd');
517 INSERT INTO t3 VALUES(4, 16);
518 INSERT INTO t3 VALUES(5, 25);
519 INSERT INTO t3 VALUES(6, 36);
522 do_preupdate_test 7.1.1 {
523 INSERT INTO t1 VALUES('x', 'y')
524 } {INSERT main t1 1 1 x y}
526 # 7.1.2.1 does not use the xfer optimization. 7.1.2.2 does.
527 do_preupdate_test 7.1.2.1 {
528 INSERT INTO t1 SELECT y, x FROM t2;
529 } {INSERT main t1 2 2 b a INSERT main t1 3 3 d c}
530 do_preupdate_test 7.1.2.2 {
531 INSERT INTO t1 SELECT * FROM t2;
532 } {INSERT main t1 4 4 a b INSERT main t1 5 5 c d}
534 do_preupdate_test 7.1.3 {
535 REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1);
537 DELETE main t1 1 1 x y
538 INSERT main t1 1 1 1 1
541 do_preupdate_test 7.1.4 {
542 REPLACE INTO t3 VALUES(4, NULL);
544 DELETE main t3 1 1 4 16
545 INSERT main t3 4 4 4 {}
548 do_preupdate_test 7.1.5 {
549 REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL);
551 DELETE main t3 2 2 5 25
552 DELETE main t3 3 3 6 36
553 INSERT main t3 2 2 6 {}
556 do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5}
558 do_preupdate_test 7.2.1 {
559 DELETE FROM t1 WHERE rowid = 3
561 DELETE main t1 3 3 d c
563 do_preupdate_test 7.2.2 {
566 DELETE main t1 1 1 1 1
567 DELETE main t1 2 2 b a
568 DELETE main t1 4 4 a b
569 DELETE main t1 5 5 c d
572 do_execsql_test 7.3.0 {
577 INSERT INTO t2 VALUES('a', 'b');
578 INSERT INTO t2 VALUES('c', 'd');
580 INSERT INTO t3 VALUES(4, 16);
581 INSERT INTO t3 VALUES(5, 25);
582 INSERT INTO t3 VALUES(6, 36);
585 do_preupdate_test 7.3.1 {
586 UPDATE t2 SET y = y||y;
588 UPDATE main t2 1 1 a b a bb
589 UPDATE main t2 2 2 c d c dd
592 do_preupdate_test 7.3.2 {
593 UPDATE t2 SET rowid = rowid-1;
595 UPDATE main t2 1 0 a bb a bb
596 UPDATE main t2 2 1 c dd c dd
599 do_preupdate_test 7.3.3 {
600 UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a'
602 DELETE main t2 1 1 c dd
603 UPDATE main t2 0 1 a bb a bb
606 do_preupdate_test 7.3.4.1 {
607 UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6
609 DELETE main t3 2 2 5 25
610 UPDATE main t3 3 3 6 36 5 36
613 do_execsql_test 7.3.4.2 {
614 INSERT INTO t3 VALUES(10, 100);
615 SELECT rowid, * FROM t3;
616 } {1 4 16 3 5 36 4 10 100}
618 do_preupdate_test 7.3.5 {
619 UPDATE OR REPLACE t3 SET rowid = 1, i = 5 WHERE j = 100;
621 DELETE main t3 1 1 4 16
622 DELETE main t3 3 3 5 36
623 UPDATE main t3 4 1 10 100 5 100
626 do_execsql_test 7.4.1.0 {
627 CREATE TABLE t4(a, b);
628 INSERT INTO t4 VALUES('a', 1);
629 INSERT INTO t4 VALUES('b', 2);
630 INSERT INTO t4 VALUES('c', 3);
632 CREATE TRIGGER t4t BEFORE DELETE ON t4 BEGIN
633 DELETE FROM t4 WHERE b = 1;
637 do_preupdate_test 7.4.1.1 {
638 DELETE FROM t4 WHERE b = 3
640 DELETE main t4 1 1 a 1
641 DELETE main t4 3 3 c 3
644 do_execsql_test 7.4.1.2 {
645 INSERT INTO t4(rowid, a, b) VALUES(1, 'a', 1);
646 INSERT INTO t4(rowid, a, b) VALUES(3, 'c', 3);
648 do_preupdate_test 7.4.1.3 {
649 DELETE FROM t4 WHERE b = 1
651 DELETE main t4 1 1 a 1
654 do_execsql_test 7.4.2.0 {
655 CREATE TABLE t5(a, b);
656 INSERT INTO t5 VALUES('a', 1);
657 INSERT INTO t5 VALUES('b', 2);
658 INSERT INTO t5 VALUES('c', 3);
660 CREATE TRIGGER t5t BEFORE UPDATE ON t5 BEGIN
661 DELETE FROM t5 WHERE b = 1;
664 do_preupdate_test 7.4.2.1 {
665 UPDATE t5 SET b = 4 WHERE a = 'c'
667 DELETE main t5 1 1 a 1
668 UPDATE main t5 3 3 c 3 c 4
671 do_execsql_test 7.4.2.2 {
672 INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1);
675 do_preupdate_test 7.4.2.3 {
676 UPDATE t5 SET b = 5 WHERE a = 'a'
678 DELETE main t5 1 1 a 1
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;
688 do_preupdate_test 7.5.1.1 {
689 DELETE FROM t7 WHERE a = 'one'
691 DELETE main t7 1 1 one two {}
694 do_preupdate_test 7.5.1.2 {
695 UPDATE t7 SET b = 'five'
697 UPDATE main t7 2 2 three four {} three five {}
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';
708 # At time of writing, these two are broken. They demonstrate that the
709 # sqlite3_preupdate_old() method does not handle the case where ALTER TABLE
710 # has been used to add a column with a default value other than NULL.
712 do_preupdate_test 7.5.2.1 {
713 DELETE FROM t8 WHERE a = 'one'
715 DELETE main t8 1 1 one two xxx
717 do_preupdate_test 7.5.2.2 {
718 UPDATE t8 SET b = 'five'
720 UPDATE main t8 2 2 three four xxx three five xxx
724 # This block of tests verifies that IPK values are correctly reported
725 # by the sqlite3_preupdate_old() and sqlite3_preupdate_new() functions.
727 do_execsql_test 7.6.1 { CREATE TABLE t9(a, b INTEGER PRIMARY KEY, c) }
728 do_preupdate_test 7.6.2 {
729 INSERT INTO t9 VALUES(1, 2, 3);
730 UPDATE t9 SET b = b+1, c = c+1;
731 DELETE FROM t9 WHERE a = 1;
733 INSERT main t9 2 2 1 2 3
734 UPDATE main t9 2 3 1 2 3 1 3 4
735 DELETE main t9 3 3 1 3 4
738 #--------------------------------------------------------------------------
739 # Test that the sqlite3_preupdate_depth() API seems to work.
741 proc preupdate_hook {args} {
742 set type [lindex $args 0]
743 eval lappend ::preupdate $args
744 eval lappend ::preupdate [db preupdate depth]
746 if {$type != "INSERT"} {
747 for {set i 0} {$i < [db preupdate count]} {incr i} {
748 lappend ::preupdate [db preupdate old $i]
751 if {$type != "DELETE"} {
752 for {set i 0} {$i < [db preupdate count]} {incr i} {
753 set rc [catch { db preupdate new $i } v]
754 lappend ::preupdate $v
762 db preupdate hook preupdate_hook
764 do_execsql_test 7.6.1 {
765 CREATE TABLE t1(x PRIMARY KEY);
766 CREATE TABLE t2(x PRIMARY KEY);
767 CREATE TABLE t3(x PRIMARY KEY);
768 CREATE TABLE t4(x PRIMARY KEY);
770 CREATE TRIGGER a AFTER INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
771 CREATE TRIGGER b AFTER INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
772 CREATE TRIGGER c AFTER INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
774 CREATE TRIGGER d AFTER UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
775 CREATE TRIGGER e AFTER UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
776 CREATE TRIGGER f AFTER UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
778 CREATE TRIGGER g AFTER DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
779 CREATE TRIGGER h AFTER DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
780 CREATE TRIGGER i AFTER DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
783 do_preupdate_test 7.6.2 {
784 INSERT INTO t1 VALUES('xyz');
786 INSERT main t1 1 1 0 xyz
787 INSERT main t2 1 1 1 xyz
788 INSERT main t3 1 1 2 xyz
789 INSERT main t4 1 1 3 xyz
791 do_preupdate_test 7.6.3 {
792 UPDATE t1 SET x = 'abc';
794 UPDATE main t1 1 1 0 xyz abc
795 UPDATE main t2 1 1 1 xyz abc
796 UPDATE main t3 1 1 2 xyz abc
797 UPDATE main t4 1 1 3 xyz abc
799 do_preupdate_test 7.6.4 {
800 DELETE FROM t1 WHERE 1;
802 DELETE main t1 1 1 0 abc
803 DELETE main t2 1 1 1 abc
804 DELETE main t3 1 1 2 abc
805 DELETE main t4 1 1 3 abc
808 do_execsql_test 7.6.5 {
809 DROP TRIGGER a; DROP TRIGGER b; DROP TRIGGER c;
810 DROP TRIGGER d; DROP TRIGGER e; DROP TRIGGER f;
811 DROP TRIGGER g; DROP TRIGGER h; DROP TRIGGER i;
813 CREATE TRIGGER a BEFORE INSERT ON t1 BEGIN INSERT INTO t2 VALUES(new.x); END;
814 CREATE TRIGGER b BEFORE INSERT ON t2 BEGIN INSERT INTO t3 VALUES(new.x); END;
815 CREATE TRIGGER c BEFORE INSERT ON t3 BEGIN INSERT INTO t4 VALUES(new.x); END;
817 CREATE TRIGGER d BEFORE UPDATE ON t1 BEGIN UPDATE t2 SET x = new.x; END;
818 CREATE TRIGGER e BEFORE UPDATE ON t2 BEGIN UPDATE t3 SET x = new.x; END;
819 CREATE TRIGGER f BEFORE UPDATE ON t3 BEGIN UPDATE t4 SET x = new.x; END;
821 CREATE TRIGGER g BEFORE DELETE ON t1 BEGIN DELETE FROM t2 WHERE 1; END;
822 CREATE TRIGGER h BEFORE DELETE ON t2 BEGIN DELETE FROM t3 WHERE 1; END;
823 CREATE TRIGGER i BEFORE DELETE ON t3 BEGIN DELETE FROM t4 WHERE 1; END;
826 do_preupdate_test 7.6.6 {
827 INSERT INTO t1 VALUES('xyz');
829 INSERT main t4 1 1 3 xyz
830 INSERT main t3 1 1 2 xyz
831 INSERT main t2 1 1 1 xyz
832 INSERT main t1 1 1 0 xyz
834 do_preupdate_test 7.6.3 {
835 UPDATE t1 SET x = 'abc';
837 UPDATE main t4 1 1 3 xyz abc
838 UPDATE main t3 1 1 2 xyz abc
839 UPDATE main t2 1 1 1 xyz abc
840 UPDATE main t1 1 1 0 xyz abc
842 do_preupdate_test 7.6.4 {
843 DELETE FROM t1 WHERE 1;
845 DELETE main t4 1 1 3 abc
846 DELETE main t3 1 1 2 abc
847 DELETE main t2 1 1 1 abc
848 DELETE main t1 1 1 0 abc
851 # No preupdate callbacks for modifying sqlite_master.
852 do_preupdate_test 8.1 { CREATE TABLE x1(x, y); } { }
853 do_preupdate_test 8.2 { ALTER TABLE x1 ADD COLUMN z } { }
854 do_preupdate_test 8.3 { ALTER TABLE x1 RENAME TO y1 } { }
855 do_preupdate_test 8.4 { CREATE INDEX y1x ON y1(x) } { }
856 do_preupdate_test 8.5 { CREATE VIEW v1 AS SELECT * FROM y1 } { }
857 do_preupdate_test 8.6 { DROP TABLE y1 } { }
859 #-------------------------------------------------------------------------
861 db preupdate hook preupdate_hook
862 do_execsql_test 9.0 {
863 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c);
864 CREATE TABLE t2(a, b INTEGER PRIMARY KEY);
866 do_preupdate_test 9.1 {
867 INSERT INTO t1 VALUES(456, NULL, NULL);
869 INSERT main t1 456 456 0 456 {} {}
871 do_execsql_test 9.2 {
872 ALTER TABLE t1 ADD COLUMN d;
874 do_preupdate_test 9.3 {
875 INSERT INTO t1(a, b, c) VALUES(457, NULL, NULL);
877 INSERT main t1 457 457 0 457 {} {} {}
879 do_preupdate_test 9.4 {
880 DELETE FROM t1 WHERE a=456
882 DELETE main t1 456 456 0 456 {} {} {}
884 do_preupdate_test 9.5 {
885 INSERT INTO t2 DEFAULT VALUES;
887 INSERT main t2 1 1 0 {} 1
889 do_preupdate_test 9.6 {
890 INSERT INTO t1 DEFAULT VALUES;
892 INSERT main t1 458 458 0 458 {} {} {}
896 do_execsql_test 10.0 {
897 CREATE TABLE t3(a, b INTEGER PRIMARY KEY);
899 do_preupdate_test 10.1 {
900 INSERT INTO t3 DEFAULT VALUES
902 INSERT main t3 1 1 0 {} 1
904 do_execsql_test 10.2 { SELECT * FROM t3 } {{} 1}
905 do_preupdate_test 10.3 {
906 DELETE FROM t3 WHERE b=1
907 } {DELETE main t3 1 1 0 {} 1}
909 #-------------------------------------------------------------------------
910 # Test that the "update" hook is not fired for operations on the
911 # sqlite_stat1 table performed by ANALYZE, even if a pre-update hook is
915 do_execsql_test 11.1 {
916 CREATE TABLE t1(a, b);
917 CREATE INDEX idx1 ON t1(a);
918 CREATE INDEX idx2 ON t1(b);
920 INSERT INTO t1 VALUES(1, 2);
921 INSERT INTO t1 VALUES(3, 4);
922 INSERT INTO t1 VALUES(5, 6);
923 INSERT INTO t1 VALUES(7, 8);
926 db preupdate hook preupdate_cb
927 db update_hook update_cb
929 proc preupdate_cb {args} { lappend ::res "preupdate" $args }
930 proc update_cb {args} { lappend ::res "update" $args }
937 preupdate {INSERT main sqlite_stat1 1 1}
938 preupdate {INSERT main sqlite_stat1 2 2}
941 do_execsql_test 11.3 {
942 INSERT INTO t1 VALUES(9, 10);
943 INSERT INTO t1 VALUES(11, 12);
944 INSERT INTO t1 VALUES(13, 14);
945 INSERT INTO t1 VALUES(15, 16);
953 preupdate {DELETE main sqlite_stat1 1 1}
954 preupdate {DELETE main sqlite_stat1 2 2}
955 preupdate {INSERT main sqlite_stat1 1 1}
956 preupdate {INSERT main sqlite_stat1 2 2}