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