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 SQLite library.
13 # This file implements tests for the conflict resolution extension
16 # $Id: conflict.test,v 1.24 2005/06/07 02:12:30 drh Exp $
18 set testdir [file dirname $argv0]
19 source $testdir/tester.tcl
21 # Create tables for the first group of tests.
23 do_test conflict-1.0 {
25 CREATE TABLE t1(a, b, c, UNIQUE(a,b));
27 SELECT c FROM t1 ORDER BY c;
31 # Six columns of configuration data as follows:
33 # i The reference number of the test
34 # cmd An INSERT or REPLACE command to execute against table t1
35 # t0 True if there is an error from $cmd
36 # t1 Content of "c" column of t1 assuming no error in $cmd
37 # t2 Content of "x" column of t2
38 # t3 Number of temporary files created by this test
40 foreach {i cmd t0 t1 t2 t3} {
42 2 {INSERT OR IGNORE} 0 3 1 0
43 3 {INSERT OR REPLACE} 0 4 1 0
45 5 {INSERT OR FAIL} 1 {} 1 0
46 6 {INSERT OR ABORT} 1 {} 1 0
47 7 {INSERT OR ROLLBACK} 1 {} {} 0
49 do_test conflict-1.$i {
50 set ::sqlite_opentemp_count 0
51 set r0 [catch {execsql [subst {
54 INSERT INTO t1 VALUES(1,2,3);
56 INSERT INTO t2 VALUES(1);
57 $cmd INTO t1 VALUES(1,2,4);
59 catch {execsql {COMMIT}}
60 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
61 set r2 [execsql {SELECT x FROM t2}]
62 set r3 $::sqlite_opentemp_count
64 } [list $t0 $t1 $t2 $t3]
67 # Create tables for the first group of tests.
69 do_test conflict-2.0 {
73 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b));
75 SELECT c FROM t1 ORDER BY c;
79 # Six columns of configuration data as follows:
81 # i The reference number of the test
82 # cmd An INSERT or REPLACE command to execute against table t1
83 # t0 True if there is an error from $cmd
84 # t1 Content of "c" column of t1 assuming no error in $cmd
85 # t2 Content of "x" column of t2
87 foreach {i cmd t0 t1 t2} {
89 2 {INSERT OR IGNORE} 0 3 1
90 3 {INSERT OR REPLACE} 0 4 1
92 5 {INSERT OR FAIL} 1 {} 1
93 6 {INSERT OR ABORT} 1 {} 1
94 7 {INSERT OR ROLLBACK} 1 {} {}
96 do_test conflict-2.$i {
97 set r0 [catch {execsql [subst {
100 INSERT INTO t1 VALUES(1,2,3);
102 INSERT INTO t2 VALUES(1);
103 $cmd INTO t1 VALUES(1,2,4);
105 catch {execsql {COMMIT}}
106 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
107 set r2 [execsql {SELECT x FROM t2}]
112 # Create tables for the first group of tests.
114 do_test conflict-3.0 {
118 CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b));
120 SELECT c FROM t1 ORDER BY c;
124 # Six columns of configuration data as follows:
126 # i The reference number of the test
127 # cmd An INSERT or REPLACE command to execute against table t1
128 # t0 True if there is an error from $cmd
129 # t1 Content of "c" column of t1 assuming no error in $cmd
130 # t2 Content of "x" column of t2
132 foreach {i cmd t0 t1 t2} {
134 2 {INSERT OR IGNORE} 0 3 1
135 3 {INSERT OR REPLACE} 0 4 1
137 5 {INSERT OR FAIL} 1 {} 1
138 6 {INSERT OR ABORT} 1 {} 1
139 7 {INSERT OR ROLLBACK} 1 {} {}
141 do_test conflict-3.$i {
142 set r0 [catch {execsql [subst {
145 INSERT INTO t1 VALUES(1,2,3);
147 INSERT INTO t2 VALUES(1);
148 $cmd INTO t1 VALUES(1,2,4);
150 catch {execsql {COMMIT}}
151 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
152 set r2 [execsql {SELECT x FROM t2}]
157 do_test conflict-4.0 {
165 # Six columns of configuration data as follows:
167 # i The reference number of the test
168 # conf1 The conflict resolution algorithm on the UNIQUE constraint
169 # cmd An INSERT or REPLACE command to execute against table t1
170 # t0 True if there is an error from $cmd
171 # t1 Content of "c" column of t1 assuming no error in $cmd
172 # t2 Content of "x" column of t2
174 foreach {i conf1 cmd t0 t1 t2} {
176 2 REPLACE INSERT 0 4 1
177 3 IGNORE INSERT 0 3 1
179 5 ABORT INSERT 1 {} 1
180 6 ROLLBACK INSERT 1 {} {}
181 7 REPLACE {INSERT OR IGNORE} 0 3 1
182 8 IGNORE {INSERT OR REPLACE} 0 4 1
183 9 FAIL {INSERT OR IGNORE} 0 3 1
184 10 ABORT {INSERT OR REPLACE} 0 4 1
185 11 ROLLBACK {INSERT OR IGNORE } 0 3 1
187 do_test conflict-4.$i {
188 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
189 set r0 [catch {execsql [subst {
191 CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);
193 INSERT INTO t1 VALUES(1,2,3);
195 INSERT INTO t2 VALUES(1);
196 $cmd INTO t1 VALUES(1,2,4);
198 catch {execsql {COMMIT}}
199 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
200 set r2 [execsql {SELECT x FROM t2}]
205 do_test conflict-5.0 {
213 # Six columns of configuration data as follows:
215 # i The reference number of the test
216 # conf1 The conflict resolution algorithm on the NOT NULL constraint
217 # cmd An INSERT or REPLACE command to execute against table t1
218 # t0 True if there is an error from $cmd
219 # t1 Content of "c" column of t1 assuming no error in $cmd
220 # t2 Content of "x" column of t2
222 foreach {i conf1 cmd t0 t1 t2} {
224 2 REPLACE INSERT 0 5 1
225 3 IGNORE INSERT 0 {} 1
227 5 ABORT INSERT 1 {} 1
228 6 ROLLBACK INSERT 1 {} {}
229 7 REPLACE {INSERT OR IGNORE} 0 {} 1
230 8 IGNORE {INSERT OR REPLACE} 0 5 1
231 9 FAIL {INSERT OR IGNORE} 0 {} 1
232 10 ABORT {INSERT OR REPLACE} 0 5 1
233 11 ROLLBACK {INSERT OR IGNORE} 0 {} 1
234 12 {} {INSERT OR IGNORE} 0 {} 1
235 13 {} {INSERT OR REPLACE} 0 5 1
236 14 {} {INSERT OR FAIL} 1 {} 1
237 15 {} {INSERT OR ABORT} 1 {} 1
238 16 {} {INSERT OR ROLLBACK} 1 {} {}
240 if {$t0} {set t1 {t1.c may not be NULL}}
241 do_test conflict-5.$i {
242 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
243 set r0 [catch {execsql [subst {
245 CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5);
248 INSERT INTO t2 VALUES(1);
249 $cmd INTO t1 VALUES(1,2,NULL);
251 catch {execsql {COMMIT}}
252 if {!$r0} {set r1 [execsql {SELECT c FROM t1}]}
253 set r2 [execsql {SELECT x FROM t2}]
258 do_test conflict-6.0 {
261 CREATE TABLE t2(a,b,c);
262 INSERT INTO t2 VALUES(1,2,1);
263 INSERT INTO t2 VALUES(2,3,2);
264 INSERT INTO t2 VALUES(3,4,1);
265 INSERT INTO t2 VALUES(4,5,4);
266 SELECT c FROM t2 ORDER BY b;
268 INSERT INTO t3 VALUES(1);
272 # Six columns of configuration data as follows:
274 # i The reference number of the test
275 # conf1 The conflict resolution algorithm on the UNIQUE constraint
276 # cmd An UPDATE command to execute against table t1
277 # t0 True if there is an error from $cmd
278 # t1 Content of "b" column of t1 assuming no error in $cmd
279 # t2 Content of "x" column of t3
280 # t3 Number of temporary files created
282 foreach {i conf1 cmd t0 t1 t2 t3} {
283 1 {} UPDATE 1 {6 7 8 9} 1 1
284 2 REPLACE UPDATE 0 {7 6 9} 1 0
285 3 IGNORE UPDATE 0 {6 7 3 9} 1 0
286 4 FAIL UPDATE 1 {6 7 3 4} 1 0
287 5 ABORT UPDATE 1 {1 2 3 4} 1 1
288 6 ROLLBACK UPDATE 1 {1 2 3 4} 0 0
289 7 REPLACE {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0
290 8 IGNORE {UPDATE OR REPLACE} 0 {7 6 9} 1 0
291 9 FAIL {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0
292 10 ABORT {UPDATE OR REPLACE} 0 {7 6 9} 1 0
293 11 ROLLBACK {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0
294 12 {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0
295 13 {} {UPDATE OR REPLACE} 0 {7 6 9} 1 0
296 14 {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 0
297 15 {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 1
298 16 {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 0
300 if {$t0} {set t1 {column a is not unique}}
301 do_test conflict-6.$i {
304 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
305 set ::sqlite_opentemp_count 0
306 set r0 [catch {execsql [subst {
308 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
309 INSERT INTO t1 SELECT * FROM t2;
316 catch {execsql {COMMIT}}
317 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}
318 set r2 [execsql {SELECT x FROM t3}]
319 list $r0 $r1 $r2 $::sqlite_opentemp_count
320 } [list $t0 $t1 $t2 $t3]
323 # Test to make sure a lot of IGNOREs don't cause a stack overflow
325 do_test conflict-7.1 {
330 CREATE TABLE t1(a unique, b);
332 for {set i 1} {$i<=50} {incr i} {
333 execsql "INSERT into t1 values($i,[expr {$i+1}]);"
336 SELECT count(*), min(a), max(b) FROM t1;
339 do_test conflict-7.2 {
341 PRAGMA count_changes=on;
342 UPDATE OR IGNORE t1 SET a=1000;
345 do_test conflict-7.2.1 {
348 do_test conflict-7.3 {
350 SELECT b FROM t1 WHERE a=1000;
353 do_test conflict-7.4 {
355 SELECT count(*) FROM t1;
358 do_test conflict-7.5 {
360 PRAGMA count_changes=on;
361 UPDATE OR REPLACE t1 SET a=1001;
364 do_test conflict-7.5.1 {
367 do_test conflict-7.6 {
369 SELECT b FROM t1 WHERE a=1001;
372 do_test conflict-7.7 {
374 SELECT count(*) FROM t1;
378 # Update for version 3: A SELECT statement no longer resets the change
379 # counter (Test result changes from 0 to 50).
380 do_test conflict-7.7.1 {
384 # Make sure the row count is right for rows that are ignored on
387 do_test conflict-8.1 {
390 INSERT INTO t1 VALUES(1,2);
393 INSERT OR IGNORE INTO t1 VALUES(2,3);
396 do_test conflict-8.1.1 {
399 do_test conflict-8.2 {
401 INSERT OR IGNORE INTO t1 VALUES(2,4);
404 do_test conflict-8.2.1 {
407 do_test conflict-8.3 {
409 INSERT OR REPLACE INTO t1 VALUES(2,4);
412 do_test conflict-8.3.1 {
415 do_test conflict-8.4 {
417 INSERT OR IGNORE INTO t1 SELECT * FROM t1;
420 do_test conflict-8.4.1 {
423 do_test conflict-8.5 {
425 INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1;
428 do_test conflict-8.5.1 {
431 do_test conflict-8.6 {
433 INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
436 do_test conflict-8.6.1 {
440 integrity_check conflict-8.99
442 do_test conflict-9.1 {
444 PRAGMA count_changes=0;
446 a INTEGER UNIQUE ON CONFLICT IGNORE,
447 b INTEGER UNIQUE ON CONFLICT FAIL,
448 c INTEGER UNIQUE ON CONFLICT REPLACE,
449 d INTEGER UNIQUE ON CONFLICT ABORT,
450 e INTEGER UNIQUE ON CONFLICT ROLLBACK
453 INSERT INTO t3 VALUES(1);
457 do_test conflict-9.2 {
459 INSERT INTO t2 VALUES(1,1,1,1,1);
460 INSERT INTO t2 VALUES(2,2,2,2,2);
463 } {0 {1 1 1 1 1 2 2 2 2 2}}
464 do_test conflict-9.3 {
466 INSERT INTO t2 VALUES(1,3,3,3,3);
469 } {0 {1 1 1 1 1 2 2 2 2 2}}
470 do_test conflict-9.4 {
472 UPDATE t2 SET a=a+1 WHERE a=1;
475 } {0 {1 1 1 1 1 2 2 2 2 2}}
476 do_test conflict-9.5 {
478 INSERT INTO t2 VALUES(3,1,3,3,3);
481 } {1 {column b is not unique}}
482 do_test conflict-9.6 {
484 UPDATE t2 SET b=b+1 WHERE b=1;
487 } {1 {column b is not unique}}
488 do_test conflict-9.7 {
492 INSERT INTO t2 VALUES(3,1,3,3,3);
495 } {1 {column b is not unique}}
496 do_test conflict-9.8 {
498 execsql {SELECT * FROM t3}
500 do_test conflict-9.9 {
504 UPDATE t2 SET b=b+1 WHERE b=1;
507 } {1 {column b is not unique}}
508 do_test conflict-9.10 {
510 execsql {SELECT * FROM t3}
512 do_test conflict-9.11 {
514 INSERT INTO t2 VALUES(3,3,3,1,3);
517 } {1 {column d is not unique}}
518 do_test conflict-9.12 {
520 UPDATE t2 SET d=d+1 WHERE d=1;
523 } {1 {column d is not unique}}
524 do_test conflict-9.13 {
528 INSERT INTO t2 VALUES(3,3,3,1,3);
531 } {1 {column d is not unique}}
532 do_test conflict-9.14 {
534 execsql {SELECT * FROM t3}
536 do_test conflict-9.15 {
540 UPDATE t2 SET d=d+1 WHERE d=1;
543 } {1 {column d is not unique}}
544 do_test conflict-9.16 {
546 execsql {SELECT * FROM t3}
548 do_test conflict-9.17 {
550 INSERT INTO t2 VALUES(3,3,3,3,1);
553 } {1 {column e is not unique}}
554 do_test conflict-9.18 {
556 UPDATE t2 SET e=e+1 WHERE e=1;
559 } {1 {column e is not unique}}
560 do_test conflict-9.19 {
564 INSERT INTO t2 VALUES(3,3,3,3,1);
567 } {1 {column e is not unique}}
568 do_test conflict-9.20 {
569 catch {execsql {COMMIT}}
570 execsql {SELECT * FROM t3}
572 do_test conflict-9.21 {
576 UPDATE t2 SET e=e+1 WHERE e=1;
579 } {1 {column e is not unique}}
580 do_test conflict-9.22 {
581 catch {execsql {COMMIT}}
582 execsql {SELECT * FROM t3}
584 do_test conflict-9.23 {
586 INSERT INTO t2 VALUES(3,3,1,3,3);
589 } {0 {2 2 2 2 2 3 3 1 3 3}}
590 do_test conflict-9.24 {
592 UPDATE t2 SET c=c-1 WHERE c=2;
596 do_test conflict-9.25 {
600 INSERT INTO t2 VALUES(3,3,1,3,3);
604 do_test conflict-9.26 {
605 catch {execsql {COMMIT}}
606 execsql {SELECT * FROM t3}
609 do_test conflict-10.1 {
613 INSERT OR ROLLBACK INTO t1 VALUES(1,2);
614 INSERT OR ROLLBACK INTO t1 VALUES(1,3);
617 execsql {SELECT * FROM t1}
619 do_test conflict-10.2 {
622 CREATE UNIQUE INDEX t4x ON t4(x);
624 INSERT OR ROLLBACK INTO t4 VALUES(1);
625 INSERT OR ROLLBACK INTO t4 VALUES(1);
628 execsql {SELECT * FROM t4}
631 # Ticket #1171. Make sure statement rollbacks do not
632 # damage the database.
634 do_test conflict-11.1 {
636 -- Create a database object (pages 2, 3 of the file)
638 CREATE TABLE abc(a UNIQUE, b, c);
639 INSERT INTO abc VALUES(1, 2, 3);
640 INSERT INTO abc VALUES(4, 5, 6);
641 INSERT INTO abc VALUES(7, 8, 9);
646 # Set a small cache size so that changes will spill into
649 PRAGMA cache_size = 10;
652 # Make lots of changes. Because of the small cache, some
653 # (most?) of these changes will spill into the disk file.
654 # In other words, some of the changes will not be held in
659 -- Make sure the pager is in EXCLUSIVE state.
660 CREATE TABLE def(d, e, f);
661 INSERT INTO def VALUES
662 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
663 INSERT INTO def SELECT * FROM def;
664 INSERT INTO def SELECT * FROM def;
665 INSERT INTO def SELECT * FROM def;
666 INSERT INTO def SELECT * FROM def;
667 INSERT INTO def SELECT * FROM def;
668 INSERT INTO def SELECT * FROM def;
669 INSERT INTO def SELECT * FROM def;
670 DELETE FROM abc WHERE a = 4;
673 # Execute a statement that does a statement rollback due to
674 # a constraint failure.
677 INSERT INTO abc SELECT 10, 20, 30 FROM def;
680 # Rollback the database. Verify that the state of the ABC table
681 # is unchanged from the beginning of the transaction. In other words,
682 # make sure the DELETE on table ABC that occurred within the transaction
689 } {1 2 3 4 5 6 7 8 9}
690 integrity_check conflict-11.2
692 # Repeat test conflict-11.1 but this time commit.
694 do_test conflict-11.3 {
697 -- Make sure the pager is in EXCLUSIVE state.
698 UPDATE abc SET a=a+1;
699 CREATE TABLE def(d, e, f);
700 INSERT INTO def VALUES
701 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
702 INSERT INTO def SELECT * FROM def;
703 INSERT INTO def SELECT * FROM def;
704 INSERT INTO def SELECT * FROM def;
705 INSERT INTO def SELECT * FROM def;
706 INSERT INTO def SELECT * FROM def;
707 INSERT INTO def SELECT * FROM def;
708 INSERT INTO def SELECT * FROM def;
709 DELETE FROM abc WHERE a = 4;
712 INSERT INTO abc SELECT 10, 20, 30 FROM def;
718 } {1 2 3 4 5 6 7 8 9}
719 # Repeat test conflict-11.1 but this time commit.
721 do_test conflict-11.5 {
724 -- Make sure the pager is in EXCLUSIVE state.
725 CREATE TABLE def(d, e, f);
726 INSERT INTO def VALUES
727 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
728 INSERT INTO def SELECT * FROM def;
729 INSERT INTO def SELECT * FROM def;
730 INSERT INTO def SELECT * FROM def;
731 INSERT INTO def SELECT * FROM def;
732 INSERT INTO def SELECT * FROM def;
733 INSERT INTO def SELECT * FROM def;
734 INSERT INTO def SELECT * FROM def;
735 DELETE FROM abc WHERE a = 4;
738 INSERT INTO abc SELECT 10, 20, 30 FROM def;
745 integrity_check conflict-11.6