Debian Codegraph @

test/hook.test

raw
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 # 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
23 set testdir [file dirname $argv0]
24 source $testdir/tester.tcl
25 set ::testprefix hook
26
27 do_test hook-1.2 {
28 db commit_hook
29 } {}
30
31
32 do_test hook-3.1 {
33 set commit_cnt 0
34 proc commit_hook {} {
35 incr ::commit_cnt
36 return 0
37 }
38 db commit_hook ::commit_hook
39 db commit_hook
40 } {::commit_hook}
41 do_test hook-3.2 {
42 set commit_cnt
43 } {0}
44 do_test hook-3.3 {
45 execsql {
46 CREATE TABLE t2(a,b);
47 }
48 set commit_cnt
49 } {1}
50 do_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_cnt
57 } {4}
58 do_test hook-3.5 {
59 set commit_cnt {}
60 proc commit_hook {} {
61 set ::commit_cnt [execsql {SELECT * FROM t2}]
62 return 0
63 }
64 execsql {
65 INSERT INTO t2 VALUES(5,6);
66 }
67 set commit_cnt
68 } {1 2 2 3 3 4 4 5 5 6}
69 do_test hook-3.6 {
70 set commit_cnt {}
71 proc commit_hook {} {
72 set ::commit_cnt [execsql {SELECT * FROM t2}]
73 return 1
74 }
75 catchsql {
76 INSERT INTO t2 VALUES(6,7);
77 }
78 } {1 {constraint failed}}
79 verify_ex_errcode hook-3.6b SQLITE_CONSTRAINT_COMMITHOOK
80 do_test hook-3.7 {
81 set ::commit_cnt
82 } {1 2 2 3 3 4 4 5 5 6 6 7}
83 do_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 hook
88 #
89 do_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_cnt
96 } {}
97
98 # Ticket #3564.
99 #
100 do_test hook-3.10 {
101 forcedelete test2.db test2.db-journal
102 sqlite3 db2 test2.db
103 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_hook
109 catchsql {INSERT INTO t3 VALUES(1,2)} db2
110 catchsql {INSERT INTO t3 VALUES(11,12)} db2
111 catchsql {INSERT INTO t3 VALUES(3,4)} db2
112 db2 eval {
113 SELECT * FROM t3 ORDER BY x;
114 }
115 } {1 2 3 4}
116 db2 close
117
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 trigger
126 # 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() interface
133 # registers a callback function with the database connection identified
134 # by the first argument to be invoked whenever a row is updated,
135 # inserted or deleted in a rowid table.
136
137 # Simple tests
138 do_test hook-4.1.1a {
139 catchsql {
140 DROP TABLE t1;
141 }
142 unset -nocomplain ::update_hook
143 set ::update_hook {}
144 db update_hook [list lappend ::update_hook]
145 #
146 # EVIDENCE-OF: R-24531-54682 The update hook is not invoked when
147 # 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_hook
154 } {}
155 do_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 of
165 # SQLITE_INSERT, SQLITE_DELETE, or SQLITE_UPDATE, depending on the
166 # operation that caused the callback to be invoked.
167 #
168 # EVIDENCE-OF: R-29213-61195 The third and fourth arguments to the
169 # callback contain pointers to the database and table name containing
170 # the affected row.
171 #
172 # EVIDENCE-OF: R-30809-57812 The final callback parameter is the rowid
173 # of the row.
174 #
175 do_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_hook
184 } [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 does
195 # not fire callbacks for changes to a WITHOUT ROWID table.
196 #
197 # EVIDENCE-OF: R-33257-44249 The update hook is not invoked when WITHOUT
198 # ROWID tables are modified.
199 #
200 do_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_hook
209 } {}
210
211 ifcapable trigger {
212 # Update hook is not invoked for changes to sqlite_master
213 #
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_hook
220 } {}
221 do_test hook-4.1.4 {
222 set ::update_hook {}
223 execsql {
224 DROP TRIGGER r1;
225 }
226 set ::update_hook
227 } {}
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 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;
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_hook
249 } [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 + ATTACH
267 set ::update_hook {}
268 ifcapable attach {
269 do_test hook-4.2.3 {
270 forcedelete test2.db
271 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_hook
279 } [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
288 ifcapable 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.
296 set ::update_hook {}
297 do_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_hook
305 } [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 ]
313 set ::update_hook {}
314 ifcapable 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_hook
325 } [list]
326 }
327
328 do_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_hook
339 } [list INSERT main t4 3 ]
340 do_execsql_test hook-4.4.1 {
341 SELECT * FROM t4 ORDER BY a;
342 } {1 c 2 b}
343 do_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_hook
350 } [list INSERT main t4 4 ]
351 do_execsql_test hook-4.4.3 {
352 SELECT * FROM t4 ORDER BY a;
353 } {1 d 2 b}
354
355 db update_hook {}
356 #
357 #----------------------------------------------------------------------------
358
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
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
374 do_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 0
378 db rollback_hook [list incr ::rollback_hook]
379 } {}
380
381 # Test explicit rollbacks. Not much can really go wrong here.
382 #
383 do_test hook-5.1.1 {
384 set ::rollback_hook 0
385 execsql {
386 BEGIN;
387 ROLLBACK;
388 }
389 set ::rollback_hook
390 } {1}
391
392 # Test implicit rollbacks caused by constraints.
393 #
394 do_test hook-5.2.1 {
395 set ::rollback_hook 0
396 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_hook
403 } {1}
404 do_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), the
417 # rollback-hook is invoked.
418 #
419 proc commit_hook {} {
420 lappend ::hooks COMMIT
421 return 1
422 }
423 proc rollback_hook {} {
424 lappend ::hooks ROLLBACK
425 }
426 do_test hook-6.1 {
427 set ::hooks [list]
428 db commit_hook commit_hook
429 db rollback_hook rollback_hook
430 catchsql {
431 BEGIN;
432 INSERT INTO t1 VALUES('two', 'II');
433 COMMIT;
434 }
435 execsql { SELECT * FROM t1 }
436 } {one I}
437 do_test hook-6.2 {
438 set ::hooks
439 } {COMMIT ROLLBACK}
440 unset ::hooks
441
442 #----------------------------------------------------------------------------
443 # The following tests - hook-7.* - test the pre-update hook.
444 #
445 ifcapable !preupdate {
446 finish_test
447 return
448 }
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 being
466 # 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 should
470 # be invoked with SQLITE_INSERT as the opcode when inserting the
471 # 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
477 proc 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 ::preupdate
484 "] [list $X]
485 }
486
487 proc preupdate_hook {args} {
488 set type [lindex $args 0]
489 eval lappend ::preupdate $args
490 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 $v
509 }
510 }
511 }
512
513 db close
514 forcedelete test.db
515 sqlite3 db test.db
516 db preupdate hook preupdate_hook
517
518 # Set up a schema to use for tests 7.1.* to 7.3.*.
519 do_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
532 do_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.
537 do_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}
540 do_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
544 do_preupdate_test 7.1.3 {
545 REPLACE INTO t1(rowid, a, b) VALUES(1, 1, 1);
546 } {
547 DELETE main t1 1 1 x y
548 INSERT main t1 1 1 1 1
549 }
550
551 do_preupdate_test 7.1.4 {
552 REPLACE INTO t3 VALUES(4, NULL);
553 } {
554 DELETE main t3 1 1 4 16
555 INSERT main t3 4 4 4 {}
556 }
557
558 do_preupdate_test 7.1.5 {
559 REPLACE INTO t3(rowid, i, j) VALUES(2, 6, NULL);
560 } {
561 DELETE main t3 2 2 5 25
562 DELETE main t3 3 3 6 36
563 INSERT main t3 2 2 6 {}
564 }
565
566 do_execsql_test 7.2.0 { SELECT rowid FROM t1 } {1 2 3 4 5}
567
568 do_preupdate_test 7.2.1 {
569 DELETE FROM t1 WHERE rowid = 3
570 } {
571 DELETE main t1 3 3 d c
572 }
573 do_preupdate_test 7.2.2 {
574 DELETE FROM t1
575 } {
576 DELETE main t1 1 1 1 1
577 DELETE main t1 2 2 b a
578 DELETE main t1 4 4 a b
579 DELETE main t1 5 5 c d
580 }
581
582 do_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
595 do_preupdate_test 7.3.1 {
596 UPDATE t2 SET y = y||y;
597 } {
598 UPDATE main t2 1 1 a b a bb
599 UPDATE main t2 2 2 c d c dd
600 }
601
602 do_preupdate_test 7.3.2 {
603 UPDATE t2 SET rowid = rowid-1;
604 } {
605 UPDATE main t2 1 0 a bb a bb
606 UPDATE main t2 2 1 c dd c dd
607 }
608
609 do_preupdate_test 7.3.3 {
610 UPDATE OR REPLACE t2 SET rowid = 1 WHERE x = 'a'
611 } {
612 DELETE main t2 1 1 c dd
613 UPDATE main t2 0 1 a bb a bb
614 }
615
616 do_preupdate_test 7.3.4.1 {
617 UPDATE OR REPLACE t3 SET i = 5 WHERE i = 6
618 } {
619 DELETE main t3 2 2 5 25
620 UPDATE main t3 3 3 6 36 5 36
621 }
622
623 do_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
628 do_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 16
632 DELETE main t3 3 3 5 36
633 UPDATE main t3 4 1 10 100 5 100
634 }
635
636 do_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 BEGIN
643 DELETE FROM t4 WHERE b = 1;
644 END;
645 }
646
647 do_preupdate_test 7.4.1.1 {
648 DELETE FROM t4 WHERE b = 3
649 } {
650 DELETE main t4 1 1 a 1
651 DELETE main t4 3 3 c 3
652 }
653
654 do_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 }
658 do_preupdate_test 7.4.1.3 {
659 DELETE FROM t4 WHERE b = 1
660 } {
661 DELETE main t4 1 1 a 1
662 }
663
664 do_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 BEGIN
671 DELETE FROM t5 WHERE b = 1;
672 END;
673 }
674 do_preupdate_test 7.4.2.1 {
675 UPDATE t5 SET b = 4 WHERE a = 'c'
676 } {
677 DELETE main t5 1 1 a 1
678 UPDATE main t5 3 3 c 3 c 4
679 }
680
681 do_execsql_test 7.4.2.2 {
682 INSERT INTO t5(rowid, a, b) VALUES(1, 'a', 1);
683 }
684
685 do_preupdate_test 7.4.2.3 {
686 UPDATE t5 SET b = 5 WHERE a = 'a'
687 } {
688 DELETE main t5 1 1 a 1
689 }
690
691 ifcapable 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
719 if 1 {
720 # At time of writing, these two are broken. They demonstrate that the
721 # sqlite3_preupdate_old() method does not handle the case where ALTER TABLE
722 # 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 xxx
730 }
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 xxx
735 }
736 }
737
738 # This block of tests verifies that IPK values are correctly reported
739 # by the sqlite3_preupdate_old() and sqlite3_preupdate_new() functions.
740 #
741 do_execsql_test 7.6.1 { CREATE TABLE t9(a, b INTEGER PRIMARY KEY, c) }
742 do_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 3
748 UPDATE main t9 2 3 1 2 3 1 3 4
749 DELETE main t9 3 3 1 3 4
750 }
751
752 #--------------------------------------------------------------------------
753 # Test that the sqlite3_preupdate_depth() API seems to work.
754 #
755 proc preupdate_hook {args} {
756 set type [lindex $args 0]
757 eval lappend ::preupdate $args
758 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 $v
769 }
770 }
771 }
772
773 db close
774 forcedelete test.db
775 sqlite3 db test.db
776 db preupdate hook preupdate_hook
777
778 do_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
797 do_preupdate_test 7.6.2 {
798 INSERT INTO t1 VALUES('xyz');
799 } {
800 INSERT main t1 1 1 0 xyz
801 INSERT main t2 1 1 1 xyz
802 INSERT main t3 1 1 2 xyz
803 INSERT main t4 1 1 3 xyz
804 }
805 do_preupdate_test 7.6.3 {
806 UPDATE t1 SET x = 'abc';
807 } {
808 UPDATE main t1 1 1 0 xyz abc
809 UPDATE main t2 1 1 1 xyz abc
810 UPDATE main t3 1 1 2 xyz abc
811 UPDATE main t4 1 1 3 xyz abc
812 }
813 do_preupdate_test 7.6.4 {
814 DELETE FROM t1 WHERE 1;
815 } {
816 DELETE main t1 1 1 0 abc
817 DELETE main t2 1 1 1 abc
818 DELETE main t3 1 1 2 abc
819 DELETE main t4 1 1 3 abc
820 }
821
822 do_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
840 do_preupdate_test 7.6.6 {
841 INSERT INTO t1 VALUES('xyz');
842 } {
843 INSERT main t4 1 1 3 xyz
844 INSERT main t3 1 1 2 xyz
845 INSERT main t2 1 1 1 xyz
846 INSERT main t1 1 1 0 xyz
847 }
848 do_preupdate_test 7.6.3 {
849 UPDATE t1 SET x = 'abc';
850 } {
851 UPDATE main t4 1 1 3 xyz abc
852 UPDATE main t3 1 1 2 xyz abc
853 UPDATE main t2 1 1 1 xyz abc
854 UPDATE main t1 1 1 0 xyz abc
855 }
856 do_preupdate_test 7.6.4 {
857 DELETE FROM t1 WHERE 1;
858 } {
859 DELETE main t4 1 1 3 abc
860 DELETE main t3 1 1 2 abc
861 DELETE main t2 1 1 1 abc
862 DELETE main t1 1 1 0 abc
863 }
864
865 # No preupdate callbacks for modifying sqlite_master.
866 ifcapable 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 #-------------------------------------------------------------------------
876 reset_db
877 db preupdate hook preupdate_hook
878
879 ifcapable 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=456
899 } {
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 {} 1
906 }
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
915 do_execsql_test 10.0 {
916 CREATE TABLE t3(a, b INTEGER PRIMARY KEY);
917 }
918 do_preupdate_test 10.1 {
919 INSERT INTO t3 DEFAULT VALUES
920 } {
921 INSERT main t3 1 1 0 {} 1
922 }
923 do_execsql_test 10.2 { SELECT * FROM t3 } {{} 1}
924 do_preupdate_test 10.3 {
925 DELETE FROM t3 WHERE b=1
926 } {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 is
931 # registered.
932 ifcapable analyze {
933 reset_db
934 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_cb
946 db update_hook update_cb
947
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 ANALYZE
954 set ::res
955 } [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 ANALYZE
970 set ::res
971 } [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 use
981 # the xfer optimization on without rowid tables.
982 #
983 reset_db
984 do_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
996 db preupdate hook preupdate_cb
997 db update_hook update_cb
998
999 proc preupdate_cb {args} { lappend ::res "preupdate" $args }
1000 proc update_cb {args} { lappend ::res "update" $args }
1001
1002 set ::res [list]
1003 do_test 12.2 {
1004 execsql VACUUM
1005 set ::res
1006 } {}
1007
1008 do_test 12.3 {
1009 set ::res [list]
1010 execsql { INSERT INTO t3 SELECT a, b FROM t2 }
1011 set ::res
1012 } {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}
1013
1014 do_test 12.4 {
1015 execsql { DELETE FROM t3 }
1016 set ::res [list]
1017 execsql { INSERT INTO t3 SELECT * FROM t2 }
1018 set ::res
1019 } {preupdate {INSERT main t3 0 0} preupdate {INSERT main t3 0 0}}
1020
1021 do_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
1027 set ::res [list]
1028 do_test 12.6 {
1029 execsql VACUUM
1030 set ::res
1031 } {}
1032
1033 do_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 #
1040 reset_db
1041 db preupdate hook preupdate_hook
1042 do_execsql_test 13.0 {
1043 CREATE TABLE t1(a INTEGER PRIMARY KEY);
1044 INSERT INTO t1 VALUES(100), (200), (300), (400);
1045 }
1046
1047 do_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
1053 do_preupdate_test 13.2 {
1054 DELETE FROM t1 WHERE a=300
1055 } {DELETE main t1 300 300 0 300 1234 abcdef {}}
1056
1057 do_preupdate_test 13.3 {
1058 UPDATE t1 SET d='hello world' WHERE a=200
1059 } {
1060 UPDATE main t1 200 200 0 200 1234 abcdef {}
1061 200 1234 abcdef {hello world}
1062 }
1063
1064 do_preupdate_test 13.4 {
1065 INSERT INTO t1 DEFAULT VALUES;
1066 } {
1067 INSERT main t1 401 401 0 401 1234 abcdef {}
1068 }
1069
1070 finish_test