Debian Codegraph @

test/conflict.test

raw
1 # 2002 January 29
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 SQLite library.
12 #
13 # This file implements tests for the conflict resolution extension
14 # to SQLite.
15 #
16
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
19
20 ifcapable !conflict {
21 finish_test
22 return
23 }
24
25 # Create tables for the first group of tests.
26 #
27 do_test conflict-1.0 {
28 execsql {
29 CREATE TABLE t1(a, b, c, UNIQUE(a,b));
30 CREATE TABLE t2(x);
31 SELECT c FROM t1 ORDER BY c;
32 }
33 } {}
34
35 # Six columns of configuration data as follows:
36 #
37 # i The reference number of the test
38 # cmd An INSERT or REPLACE command to execute against table t1
39 # t0 True if there is an error from $cmd
40 # t1 Content of "c" column of t1 assuming no error in $cmd
41 # t2 Content of "x" column of t2
42 # t3 Number of temporary files created by this test
43 #
44 foreach {i cmd t0 t1 t2 t3} {
45 1 INSERT 1 {} 1 0
46 2 {INSERT OR IGNORE} 0 3 1 0
47 3 {INSERT OR REPLACE} 0 4 1 0
48 4 REPLACE 0 4 1 0
49 5 {INSERT OR FAIL} 1 {} 1 0
50 6 {INSERT OR ABORT} 1 {} 1 0
51 7 {INSERT OR ROLLBACK} 1 {} {} 0
52 } {
53 do_test conflict-1.$i {
54 set ::sqlite_opentemp_count 0
55 set r0 [catch {execsql [subst {
56 DELETE FROM t1;
57 DELETE FROM t2;
58 INSERT INTO t1 VALUES(1,2,3);
59 BEGIN;
60 INSERT INTO t2 VALUES(1);
61 $cmd INTO t1 VALUES(1,2,4);
62 }]} r1]
63 catch {execsql {COMMIT}}
64 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
65 set r2 [execsql {SELECT x FROM t2}]
66 set r3 $::sqlite_opentemp_count
67 list $r0 $r1 $r2 $r3
68 } [list $t0 $t1 $t2 $t3]
69 }
70
71 # Create tables for the first group of tests.
72 #
73 do_test conflict-2.0 {
74 execsql {
75 DROP TABLE t1;
76 DROP TABLE t2;
77 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b));
78 CREATE TABLE t2(x);
79 SELECT c FROM t1 ORDER BY c;
80 }
81 } {}
82
83 # Six columns of configuration data as follows:
84 #
85 # i The reference number of the test
86 # cmd An INSERT or REPLACE command to execute against table t1
87 # t0 True if there is an error from $cmd
88 # t1 Content of "c" column of t1 assuming no error in $cmd
89 # t2 Content of "x" column of t2
90 #
91 foreach {i cmd t0 t1 t2} {
92 1 INSERT 1 {} 1
93 2 {INSERT OR IGNORE} 0 3 1
94 3 {INSERT OR REPLACE} 0 4 1
95 4 REPLACE 0 4 1
96 5 {INSERT OR FAIL} 1 {} 1
97 6 {INSERT OR ABORT} 1 {} 1
98 7 {INSERT OR ROLLBACK} 1 {} {}
99 } {
100 do_test conflict-2.$i {
101 set r0 [catch {execsql [subst {
102 DELETE FROM t1;
103 DELETE FROM t2;
104 INSERT INTO t1 VALUES(1,2,3);
105 BEGIN;
106 INSERT INTO t2 VALUES(1);
107 $cmd INTO t1 VALUES(1,2,4);
108 }]} r1]
109 catch {execsql {COMMIT}}
110 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
111 set r2 [execsql {SELECT x FROM t2}]
112 list $r0 $r1 $r2
113 } [list $t0 $t1 $t2]
114 }
115
116 # Create tables for the first group of tests.
117 #
118 do_test conflict-3.0 {
119 execsql {
120 DROP TABLE t1;
121 DROP TABLE t2;
122 CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b));
123 CREATE TABLE t2(x);
124 SELECT c FROM t1 ORDER BY c;
125 }
126 } {}
127
128 # Six columns of configuration data as follows:
129 #
130 # i The reference number of the test
131 # cmd An INSERT or REPLACE command to execute against table t1
132 # t0 True if there is an error from $cmd
133 # t1 Content of "c" column of t1 assuming no error in $cmd
134 # t2 Content of "x" column of t2
135 #
136 foreach {i cmd t0 t1 t2} {
137 1 INSERT 1 {} 1
138 2 {INSERT OR IGNORE} 0 3 1
139 3 {INSERT OR REPLACE} 0 4 1
140 4 REPLACE 0 4 1
141 5 {INSERT OR FAIL} 1 {} 1
142 6 {INSERT OR ABORT} 1 {} 1
143 7 {INSERT OR ROLLBACK} 1 {} {}
144 } {
145 do_test conflict-3.$i {
146 set r0 [catch {execsql [subst {
147 DELETE FROM t1;
148 DELETE FROM t2;
149 INSERT INTO t1 VALUES(1,2,3);
150 BEGIN;
151 INSERT INTO t2 VALUES(1);
152 $cmd INTO t1 VALUES(1,2,4);
153 }]} r1]
154 catch {execsql {COMMIT}}
155 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
156 set r2 [execsql {SELECT x FROM t2}]
157 list $r0 $r1 $r2
158 } [list $t0 $t1 $t2]
159 }
160
161 do_test conflict-4.0 {
162 execsql {
163 DROP TABLE t2;
164 CREATE TABLE t2(x);
165 SELECT x FROM t2;
166 }
167 } {}
168
169 # Six columns of configuration data as follows:
170 #
171 # i The reference number of the test
172 # conf1 The conflict resolution algorithm on the UNIQUE constraint
173 # cmd An INSERT or REPLACE command to execute against table t1
174 # t0 True if there is an error from $cmd
175 # t1 Content of "c" column of t1 assuming no error in $cmd
176 # t2 Content of "x" column of t2
177 #
178 foreach {i conf1 cmd t0 t1 t2} {
179 1 {} INSERT 1 {} 1
180 2 REPLACE INSERT 0 4 1
181 3 IGNORE INSERT 0 3 1
182 4 FAIL INSERT 1 {} 1
183 5 ABORT INSERT 1 {} 1
184 6 ROLLBACK INSERT 1 {} {}
185 7 REPLACE {INSERT OR IGNORE} 0 3 1
186 8 IGNORE {INSERT OR REPLACE} 0 4 1
187 9 FAIL {INSERT OR IGNORE} 0 3 1
188 10 ABORT {INSERT OR REPLACE} 0 4 1
189 11 ROLLBACK {INSERT OR IGNORE } 0 3 1
190 } {
191 do_test conflict-4.$i {
192 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
193 set r0 [catch {execsql [subst {
194 DROP TABLE t1;
195 CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);
196 DELETE FROM t2;
197 INSERT INTO t1 VALUES(1,2,3);
198 BEGIN;
199 INSERT INTO t2 VALUES(1);
200 $cmd INTO t1 VALUES(1,2,4);
201 }]} r1]
202 catch {execsql {COMMIT}}
203 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
204 set r2 [execsql {SELECT x FROM t2}]
205 list $r0 $r1 $r2
206 } [list $t0 $t1 $t2]
207 }
208
209 do_test conflict-5.0 {
210 execsql {
211 DROP TABLE t2;
212 CREATE TABLE t2(x);
213 SELECT x FROM t2;
214 }
215 } {}
216
217 # Six columns of configuration data as follows:
218 #
219 # i The reference number of the test
220 # conf1 The conflict resolution algorithm on the NOT NULL constraint
221 # cmd An INSERT or REPLACE command to execute against table t1
222 # t0 True if there is an error from $cmd
223 # t1 Content of "c" column of t1 assuming no error in $cmd
224 # t2 Content of "x" column of t2
225 #
226 foreach {i conf1 cmd t0 t1 t2} {
227 1 {} INSERT 1 {} 1
228 2 REPLACE INSERT 0 5 1
229 3 IGNORE INSERT 0 {} 1
230 4 FAIL INSERT 1 {} 1
231 5 ABORT INSERT 1 {} 1
232 6 ROLLBACK INSERT 1 {} {}
233 7 REPLACE {INSERT OR IGNORE} 0 {} 1
234 8 IGNORE {INSERT OR REPLACE} 0 5 1
235 9 FAIL {INSERT OR IGNORE} 0 {} 1
236 10 ABORT {INSERT OR REPLACE} 0 5 1
237 11 ROLLBACK {INSERT OR IGNORE} 0 {} 1
238 12 {} {INSERT OR IGNORE} 0 {} 1
239 13 {} {INSERT OR REPLACE} 0 5 1
240 14 {} {INSERT OR FAIL} 1 {} 1
241 15 {} {INSERT OR ABORT} 1 {} 1
242 16 {} {INSERT OR ROLLBACK} 1 {} {}
243 } {
244 if {$t0} {set t1 {NOT NULL constraint failed: t1.c}}
245 do_test conflict-5.$i {
246 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
247 set r0 [catch {execsql [subst {
248 DROP TABLE t1;
249 CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5);
250 DELETE FROM t2;
251 BEGIN;
252 INSERT INTO t2 VALUES(1);
253 $cmd INTO t1 VALUES(1,2,NULL);
254 }]} r1]
255 catch {execsql {COMMIT}}
256 if {!$r0} {set r1 [execsql {SELECT c FROM t1}]}
257 set r2 [execsql {SELECT x FROM t2}]
258 list $r0 $r1 $r2
259 } [list $t0 $t1 $t2]
260 }
261
262 do_test conflict-6.0 {
263 execsql {
264 DROP TABLE t2;
265 CREATE TABLE t2(a,b,c);
266 INSERT INTO t2 VALUES(1,2,1);
267 INSERT INTO t2 VALUES(2,3,2);
268 INSERT INTO t2 VALUES(3,4,1);
269 INSERT INTO t2 VALUES(4,5,4);
270 SELECT c FROM t2 ORDER BY b;
271 CREATE TABLE t3(x);
272 INSERT INTO t3 VALUES(1);
273 }
274 } {1 2 1 4}
275
276 # Six columns of configuration data as follows:
277 #
278 # i The reference number of the test
279 # conf1 The conflict resolution algorithm on the UNIQUE constraint
280 # cmd An UPDATE command to execute against table t1
281 # t0 True if there is an error from $cmd
282 # t1 Content of "b" column of t1 assuming no error in $cmd
283 # t2 Content of "x" column of t3
284 # t3 Number of temporary files for tables
285 # t4 Number of temporary files for statement journals
286 #
287 # Update (2007-08-21): Since temporary table files are now opened lazily,
288 # and none of the following tests use large quantities of data, t3 is always 0.
289 #
290 # Update (2016-03-04): Subjournals now also open lazily, so t4 is also always 0.
291 #
292 foreach {i conf1 cmd t0 t1 t2 t3 t4} {
293 1 {} UPDATE 1 {6 7 8 9} 1 0 0
294 2 REPLACE UPDATE 0 {7 6 9} 1 0 0
295 3 IGNORE UPDATE 0 {6 7 3 9} 1 0 0
296 4 FAIL UPDATE 1 {6 7 3 4} 1 0 0
297 5 ABORT UPDATE 1 {1 2 3 4} 1 0 0
298 6 ROLLBACK UPDATE 1 {1 2 3 4} 0 0 0
299 7 REPLACE {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
300 8 IGNORE {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0
301 9 FAIL {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
302 10 ABORT {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0
303 11 ROLLBACK {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
304 12 {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
305 13 {} {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0
306 14 {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 0 0
307 15 {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 0 0
308 16 {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 0 0
309 } {
310 if {$t0} {set t1 {UNIQUE constraint failed: t1.a}}
311 if {[info exists TEMP_STORE] && $TEMP_STORE==3} {
312 set t3 0
313 } else {
314 set t3 [expr {$t3+$t4}]
315 }
316 do_test conflict-6.$i {
317 db close
318 sqlite3 db test.db
319 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
320 execsql {pragma temp_store=file}
321 set ::sqlite_opentemp_count 0
322 set r0 [catch {execsql [subst {
323 DROP TABLE t1;
324 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
325 INSERT INTO t1 SELECT * FROM t2;
326 UPDATE t3 SET x=0;
327 BEGIN;
328 $cmd t3 SET x=1;
329 $cmd t1 SET b=b*2;
330 $cmd t1 SET a=c+5;
331 }]} r1]
332 catch {execsql {COMMIT}}
333 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}
334 set r2 [execsql {SELECT x FROM t3}]
335 list $r0 $r1 $r2 $::sqlite_opentemp_count
336 } [list $t0 $t1 $t2 $t3]
337 }
338
339 # Test to make sure a lot of IGNOREs don't cause a stack overflow
340 #
341 do_test conflict-7.1 {
342 execsql {
343 DROP TABLE t1;
344 DROP TABLE t2;
345 DROP TABLE t3;
346 CREATE TABLE t1(a unique, b);
347 }
348 for {set i 1} {$i<=50} {incr i} {
349 execsql "INSERT into t1 values($i,[expr {$i+1}]);"
350 }
351 execsql {
352 SELECT count(*), min(a), max(b) FROM t1;
353 }
354 } {50 1 51}
355 do_test conflict-7.2 {
356 execsql {
357 PRAGMA count_changes=on;
358 UPDATE OR IGNORE t1 SET a=1000;
359 }
360 } {1}
361 do_test conflict-7.2.1 {
362 db changes
363 } {1}
364 do_test conflict-7.3 {
365 execsql {
366 SELECT b FROM t1 WHERE a=1000;
367 }
368 } {2}
369 do_test conflict-7.4 {
370 execsql {
371 SELECT count(*) FROM t1;
372 }
373 } {50}
374 do_test conflict-7.5 {
375 execsql {
376 PRAGMA count_changes=on;
377 UPDATE OR REPLACE t1 SET a=1001;
378 }
379 } {50}
380 do_test conflict-7.5.1 {
381 db changes
382 } {50}
383 do_test conflict-7.6 {
384 execsql {
385 SELECT b FROM t1 WHERE a=1001;
386 }
387 } {51}
388 do_test conflict-7.7 {
389 execsql {
390 SELECT count(*) FROM t1;
391 }
392 } {1}
393
394 # Update for version 3: A SELECT statement no longer resets the change
395 # counter (Test result changes from 0 to 50).
396 do_test conflict-7.7.1 {
397 db changes
398 } {50}
399
400 # Make sure the row count is right for rows that are ignored on
401 # an insert.
402 #
403 do_test conflict-8.1 {
404 execsql {
405 DELETE FROM t1;
406 INSERT INTO t1 VALUES(1,2);
407 }
408 execsql {
409 INSERT OR IGNORE INTO t1 VALUES(2,3);
410 }
411 } {1}
412 do_test conflict-8.1.1 {
413 db changes
414 } {1}
415 do_test conflict-8.2 {
416 execsql {
417 INSERT OR IGNORE INTO t1 VALUES(2,4);
418 }
419 } {0}
420 do_test conflict-8.2.1 {
421 db changes
422 } {0}
423 do_test conflict-8.3 {
424 execsql {
425 INSERT OR REPLACE INTO t1 VALUES(2,4);
426 }
427 } {1}
428 do_test conflict-8.3.1 {
429 db changes
430 } {1}
431 do_test conflict-8.4 {
432 execsql {
433 INSERT OR IGNORE INTO t1 SELECT * FROM t1;
434 }
435 } {0}
436 do_test conflict-8.4.1 {
437 db changes
438 } {0}
439 do_test conflict-8.5 {
440 execsql {
441 INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1;
442 }
443 } {2}
444 do_test conflict-8.5.1 {
445 db changes
446 } {2}
447 do_test conflict-8.6 {
448 execsql {
449 INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
450 }
451 } {3}
452 do_test conflict-8.6.1 {
453 db changes
454 } {3}
455
456 integrity_check conflict-8.99
457
458 do_test conflict-9.1 {
459 execsql {
460 PRAGMA count_changes=0;
461 CREATE TABLE t2(
462 a INTEGER UNIQUE ON CONFLICT IGNORE,
463 b INTEGER UNIQUE ON CONFLICT FAIL,
464 c INTEGER UNIQUE ON CONFLICT REPLACE,
465 d INTEGER UNIQUE ON CONFLICT ABORT,
466 e INTEGER UNIQUE ON CONFLICT ROLLBACK
467 );
468 CREATE TABLE t3(x);
469 INSERT INTO t3 VALUES(1);
470 SELECT * FROM t3;
471 }
472 } {1}
473 do_test conflict-9.2 {
474 catchsql {
475 INSERT INTO t2 VALUES(1,1,1,1,1);
476 INSERT INTO t2 VALUES(2,2,2,2,2);
477 SELECT * FROM t2;
478 }
479 } {0 {1 1 1 1 1 2 2 2 2 2}}
480 do_test conflict-9.3 {
481 catchsql {
482 INSERT INTO t2 VALUES(1,3,3,3,3);
483 SELECT * FROM t2;
484 }
485 } {0 {1 1 1 1 1 2 2 2 2 2}}
486 do_test conflict-9.4 {
487 catchsql {
488 UPDATE t2 SET a=a+1 WHERE a=1;
489 SELECT * FROM t2;
490 }
491 } {0 {1 1 1 1 1 2 2 2 2 2}}
492 do_test conflict-9.5 {
493 catchsql {
494 INSERT INTO t2 VALUES(3,1,3,3,3);
495 SELECT * FROM t2;
496 }
497 } {1 {UNIQUE constraint failed: t2.b}}
498 do_test conflict-9.6 {
499 catchsql {
500 UPDATE t2 SET b=b+1 WHERE b=1;
501 SELECT * FROM t2;
502 }
503 } {1 {UNIQUE constraint failed: t2.b}}
504 do_test conflict-9.7 {
505 catchsql {
506 BEGIN;
507 UPDATE t3 SET x=x+1;
508 INSERT INTO t2 VALUES(3,1,3,3,3);
509 SELECT * FROM t2;
510 }
511 } {1 {UNIQUE constraint failed: t2.b}}
512 do_test conflict-9.8 {
513 execsql {COMMIT}
514 execsql {SELECT * FROM t3}
515 } {2}
516 do_test conflict-9.9 {
517 catchsql {
518 BEGIN;
519 UPDATE t3 SET x=x+1;
520 UPDATE t2 SET b=b+1 WHERE b=1;
521 SELECT * FROM t2;
522 }
523 } {1 {UNIQUE constraint failed: t2.b}}
524 do_test conflict-9.10 {
525 execsql {COMMIT}
526 execsql {SELECT * FROM t3}
527 } {3}
528 do_test conflict-9.11 {
529 catchsql {
530 INSERT INTO t2 VALUES(3,3,3,1,3);
531 SELECT * FROM t2;
532 }
533 } {1 {UNIQUE constraint failed: t2.d}}
534 do_test conflict-9.12 {
535 catchsql {
536 UPDATE t2 SET d=d+1 WHERE d=1;
537 SELECT * FROM t2;
538 }
539 } {1 {UNIQUE constraint failed: t2.d}}
540 do_test conflict-9.13 {
541 catchsql {
542 BEGIN;
543 UPDATE t3 SET x=x+1;
544 INSERT INTO t2 VALUES(3,3,3,1,3);
545 SELECT * FROM t2;
546 }
547 } {1 {UNIQUE constraint failed: t2.d}}
548 do_test conflict-9.14 {
549 execsql {COMMIT}
550 execsql {SELECT * FROM t3}
551 } {4}
552 do_test conflict-9.15 {
553 catchsql {
554 BEGIN;
555 UPDATE t3 SET x=x+1;
556 UPDATE t2 SET d=d+1 WHERE d=1;
557 SELECT * FROM t2;
558 }
559 } {1 {UNIQUE constraint failed: t2.d}}
560 do_test conflict-9.16 {
561 execsql {COMMIT}
562 execsql {SELECT * FROM t3}
563 } {5}
564 do_test conflict-9.17 {
565 catchsql {
566 INSERT INTO t2 VALUES(3,3,3,3,1);
567 SELECT * FROM t2;
568 }
569 } {1 {UNIQUE constraint failed: t2.e}}
570 do_test conflict-9.18 {
571 catchsql {
572 UPDATE t2 SET e=e+1 WHERE e=1;
573 SELECT * FROM t2;
574 }
575 } {1 {UNIQUE constraint failed: t2.e}}
576 do_test conflict-9.19 {
577 catchsql {
578 BEGIN;
579 UPDATE t3 SET x=x+1;
580 INSERT INTO t2 VALUES(3,3,3,3,1);
581 SELECT * FROM t2;
582 }
583 } {1 {UNIQUE constraint failed: t2.e}}
584 verify_ex_errcode conflict-9.21b SQLITE_CONSTRAINT_UNIQUE
585 do_test conflict-9.20 {
586 catch {execsql {COMMIT}}
587 execsql {SELECT * FROM t3}
588 } {5}
589 do_test conflict-9.21 {
590 catchsql {
591 BEGIN;
592 UPDATE t3 SET x=x+1;
593 UPDATE t2 SET e=e+1 WHERE e=1;
594 SELECT * FROM t2;
595 }
596 } {1 {UNIQUE constraint failed: t2.e}}
597 verify_ex_errcode conflict-9.21b SQLITE_CONSTRAINT_UNIQUE
598 do_test conflict-9.22 {
599 catch {execsql {COMMIT}}
600 execsql {SELECT * FROM t3}
601 } {5}
602 do_test conflict-9.23 {
603 catchsql {
604 INSERT INTO t2 VALUES(3,3,1,3,3);
605 SELECT * FROM t2;
606 }
607 } {0 {2 2 2 2 2 3 3 1 3 3}}
608 do_test conflict-9.24 {
609 catchsql {
610 UPDATE t2 SET c=c-1 WHERE c=2;
611 SELECT * FROM t2;
612 }
613 } {0 {2 2 1 2 2}}
614 do_test conflict-9.25 {
615 catchsql {
616 BEGIN;
617 UPDATE t3 SET x=x+1;
618 INSERT INTO t2 VALUES(3,3,1,3,3);
619 SELECT * FROM t2;
620 }
621 } {0 {3 3 1 3 3}}
622 do_test conflict-9.26 {
623 catch {execsql {COMMIT}}
624 execsql {SELECT * FROM t3}
625 } {6}
626
627 do_test conflict-10.1 {
628 catchsql {
629 DELETE FROM t1;
630 BEGIN;
631 INSERT OR ROLLBACK INTO t1 VALUES(1,2);
632 INSERT OR ROLLBACK INTO t1 VALUES(1,3);
633 COMMIT;
634 }
635 execsql {SELECT * FROM t1}
636 } {}
637 do_test conflict-10.2 {
638 catchsql {
639 CREATE TABLE t4(x);
640 CREATE UNIQUE INDEX t4x ON t4(x);
641 BEGIN;
642 INSERT OR ROLLBACK INTO t4 VALUES(1);
643 INSERT OR ROLLBACK INTO t4 VALUES(1);
644 COMMIT;
645 }
646 execsql {SELECT * FROM t4}
647 } {}
648
649 # Ticket #1171. Make sure statement rollbacks do not
650 # damage the database.
651 #
652 do_test conflict-11.1 {
653 execsql {
654 -- Create a database object (pages 2, 3 of the file)
655 BEGIN;
656 CREATE TABLE abc(a UNIQUE, b, c);
657 INSERT INTO abc VALUES(1, 2, 3);
658 INSERT INTO abc VALUES(4, 5, 6);
659 INSERT INTO abc VALUES(7, 8, 9);
660 COMMIT;
661 }
662
663
664 # Set a small cache size so that changes will spill into
665 # the database file.
666 execsql {
667 PRAGMA cache_size = 10;
668 }
669
670 # Make lots of changes. Because of the small cache, some
671 # (most?) of these changes will spill into the disk file.
672 # In other words, some of the changes will not be held in
673 # cache.
674 #
675 execsql {
676 BEGIN;
677 -- Make sure the pager is in EXCLUSIVE state.
678 CREATE TABLE def(d, e, f);
679 INSERT INTO def VALUES
680 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
681 INSERT INTO def SELECT * FROM def;
682 INSERT INTO def SELECT * FROM def;
683 INSERT INTO def SELECT * FROM def;
684 INSERT INTO def SELECT * FROM def;
685 INSERT INTO def SELECT * FROM def;
686 INSERT INTO def SELECT * FROM def;
687 INSERT INTO def SELECT * FROM def;
688 DELETE FROM abc WHERE a = 4;
689 }
690
691 # Execute a statement that does a statement rollback due to
692 # a constraint failure.
693 #
694 catchsql {
695 INSERT INTO abc SELECT 10, 20, 30 FROM def;
696 }
697
698 # Rollback the database. Verify that the state of the ABC table
699 # is unchanged from the beginning of the transaction. In other words,
700 # make sure the DELETE on table ABC that occurred within the transaction
701 # had no effect.
702 #
703 execsql {
704 ROLLBACK;
705 SELECT * FROM abc;
706 }
707 } {1 2 3 4 5 6 7 8 9}
708 integrity_check conflict-11.2
709
710 # Repeat test conflict-11.1 but this time commit.
711 #
712 do_test conflict-11.3 {
713 execsql {
714 BEGIN;
715 -- Make sure the pager is in EXCLUSIVE state.
716 UPDATE abc SET a=a+1;
717 CREATE TABLE def(d, e, f);
718 INSERT INTO def VALUES
719 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
720 INSERT INTO def SELECT * FROM def;
721 INSERT INTO def SELECT * FROM def;
722 INSERT INTO def SELECT * FROM def;
723 INSERT INTO def SELECT * FROM def;
724 INSERT INTO def SELECT * FROM def;
725 INSERT INTO def SELECT * FROM def;
726 INSERT INTO def SELECT * FROM def;
727 DELETE FROM abc WHERE a = 4;
728 }
729 catchsql {
730 INSERT INTO abc SELECT 10, 20, 30 FROM def;
731 }
732 execsql {
733 ROLLBACK;
734 SELECT * FROM abc;
735 }
736 } {1 2 3 4 5 6 7 8 9}
737 # Repeat test conflict-11.1 but this time commit.
738 #
739 do_test conflict-11.5 {
740 execsql {
741 BEGIN;
742 -- Make sure the pager is in EXCLUSIVE state.
743 CREATE TABLE def(d, e, f);
744 INSERT INTO def VALUES
745 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
746 INSERT INTO def SELECT * FROM def;
747 INSERT INTO def SELECT * FROM def;
748 INSERT INTO def SELECT * FROM def;
749 INSERT INTO def SELECT * FROM def;
750 INSERT INTO def SELECT * FROM def;
751 INSERT INTO def SELECT * FROM def;
752 INSERT INTO def SELECT * FROM def;
753 DELETE FROM abc WHERE a = 4;
754 }
755 catchsql {
756 INSERT INTO abc SELECT 10, 20, 30 FROM def;
757 }
758 execsql {
759 COMMIT;
760 SELECT * FROM abc;
761 }
762 } {1 2 3 7 8 9}
763 integrity_check conflict-11.6
764
765 # Make sure UPDATE OR REPLACE works on tables that have only
766 # an INTEGER PRIMARY KEY.
767 #
768 do_test conflict-12.1 {
769 execsql {
770 CREATE TABLE t5(a INTEGER PRIMARY KEY, b text);
771 INSERT INTO t5 VALUES(1,'one');
772 INSERT INTO t5 VALUES(2,'two');
773 SELECT * FROM t5
774 }
775 } {1 one 2 two}
776 do_test conflict-12.2 {
777 execsql {
778 UPDATE OR IGNORE t5 SET a=a+1 WHERE a=1;
779 SELECT * FROM t5;
780 }
781 } {1 one 2 two}
782 do_test conflict-12.3 {
783 catchsql {
784 UPDATE t5 SET a=a+1 WHERE a=1;
785 }
786 } {1 {UNIQUE constraint failed: t5.a}}
787 verify_ex_errcode conflict-12.3b SQLITE_CONSTRAINT_PRIMARYKEY
788 do_test conflict-12.4 {
789 execsql {
790 UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1;
791 SELECT * FROM t5;
792 }
793 } {2 one}
794 do_test conflict-12.5 {
795 catchsql {
796 CREATE TABLE t5b(x);
797 INSERT INTO t5b(rowid, x) VALUES(1,10),(2,11);
798 UPDATE t5b SET rowid=rowid+1 WHERE x=10;
799 }
800 } {1 {UNIQUE constraint failed: t5b.rowid}}
801 verify_ex_errcode conflict-12.5b SQLITE_CONSTRAINT_ROWID
802
803
804 # Ticket [c38baa3d969eab7946dc50ba9d9b4f0057a19437]
805 # REPLACE works like ABORT on a CHECK constraint.
806 #
807 do_test conflict-13.1 {
808 execsql {
809 CREATE TABLE t13(a CHECK(a!=2));
810 BEGIN;
811 REPLACE INTO t13 VALUES(1);
812 }
813 catchsql {
814 REPLACE INTO t13 VALUES(2);
815 }
816 } {1 {CHECK constraint failed: a!=2}}
817 verify_ex_errcode conflict-13.1b SQLITE_CONSTRAINT_CHECK
818 do_test conflict-13.2 {
819 execsql {
820 REPLACE INTO t13 VALUES(3);
821 COMMIT;
822 SELECT * FROM t13;
823 }
824 } {1 3}
825
826
827 # Ticket https://sqlite.org/src/tktview/e6f1f2e34dceeb1ed61531c7e9
828 # Verify that it is not possible to sneak a NULL value into a NOT NULL
829 # column using REPLACE.
830 #
831 do_catchsql_test conflict-14.1 {
832 DROP TABLE IF EXISTS t1;
833 CREATE TABLE t1(x NOT NULL DEFAULT NULL);
834 REPLACE INTO t1 DEFAULT VALUES;
835 } {1 {NOT NULL constraint failed: t1.x}}
836
837 # 2019-12-15 gramfuzz1 find
838 # Three UNIQUE constraints, where the third would is a duplicate except
839 # that it adds ON CONFLICT REPLACE. Verify that the indexes end up
840 # sorted in the correct order (REPLACE last) so that constraint processing
841 # works correctly.
842 #
843 reset_db
844 do_execsql_test conflict-15.10 {
845 CREATE TABLE t1(
846 x PRIMARY KEY,
847 UNIQUE(x,x),
848 UNIQUE(x,x) ON CONFLICT REPLACE
849 );
850 INSERT INTO t1(x) VALUES(1);
851 SELECT * FROM t1;
852 } {1}
853 do_catchsql_test conflict-15.20 {
854 INSERT INTO t1(x) VALUES(1);
855 } {1 {UNIQUE constraint failed: t1.x}}
856 do_execsql_test conflict-15.30 {
857 SELECT * FROM t1;
858 } {1}
859
860 # 2023-01-16 https://sqlite.org/forum/forumpost/aa580a5af38a58e3
861 # The parser accepts an ON CONFLICT clause on table CHECK constraints
862 # but not on column CHECK constraints. But the CHECK constraint is
863 # ignored. It has been like this since version 3.0.0.
864 #
865 # There might be applications and/or databases in the wild that have
866 # table CHECK constraints with ON CONFLICT clauses. In as much as this
867 # is a harmless quirk, we continue to support it in order to avoid
868 # breaking those legacy applications and databases.
869 #
870 reset_db
871 do_catchsql_test conflict-16.1 {
872 -- ON CONFLICT clauses are not allowed on column CHECK constraints
873 CREATE TABLE t1(a INT CHECK( a!=5 ) ON CONFLICT ignore);
874 } {1 {near "ON": syntax error}}
875 do_execsql_test conflict-16.2 {
876 -- ON CONFLICT is allowed on table CHECK constraints
877 CREATE TABLE t1(a INT, CHECK( a!=5 ) ON CONFLICT ignore);
878 } {}
879 do_catchsql_test conflict-16.3 {
880 -- The ON CONFLICT clause is in-op
881 INSERT INTO t1(a) VALUES(4),(5),(6);
882 } {1 {CHECK constraint failed: a!=5}}
883 do_execsql_test conflict-16.4 {
884 SELECT a FROM t1 ORDER BY a;
885 } {}
886 do_execsql_test conflict-16.5 {
887 INSERT OR IGNORE INTO t1(a) VALUES(4),(5),(6);
888 SELECT a FROM t1 ORDER BY a;
889 } {4 6}
890
891 finish_test