| blob | 8419f1a3ff3f7a65e8100ae94859e0518d805108 |
1 # 2013-11-04
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 # in WITHOUT ROWID tables
15 #
17 set testdir [file dirname $argv0]
18 source $testdir/tester.tcl
20 ifcapable !conflict {
21 finish_test
22 return
23 }
25 # Create tables for the first group of tests.
26 #
27 do_test conflict2-1.0 {
28 execsql {
29 CREATE TABLE t1(a, b, c, PRIMARY KEY(a,b)) WITHOUT rowid;
30 CREATE TABLE t2(x);
31 SELECT c FROM t1 ORDER BY c;
32 }
33 } {}
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 conflict2-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 }
71 # Create tables for the first group of tests.
72 #
73 do_test conflict2-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)) WITHOUT rowid;
78 CREATE TABLE t2(x);
79 SELECT c FROM t1 ORDER BY c;
80 }
81 } {}
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 conflict2-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 }
116 # Create tables for the first group of tests.
117 #
118 do_test conflict2-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)) WITHOUT rowid;
123 CREATE TABLE t2(x);
124 SELECT c FROM t1 ORDER BY c;
125 }
126 } {}
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 conflict2-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 }
161 do_test conflict2-4.0 {
162 execsql {
163 DROP TABLE t2;
164 CREATE TABLE t2(x);
165 SELECT x FROM t2;
166 }
167 } {}
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 conflict2-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,PRIMARY KEY(a,b) $conf1) WITHOUT rowid;
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 }
209 do_test conflict2-5.0 {
210 execsql {
211 DROP TABLE t2;
212 CREATE TABLE t2(x);
213 SELECT x FROM t2;
214 }
215 } {}
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 conflict2-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 }
262 do_test conflict2-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}
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: Since temporary table files are now opened lazily, and none
288 # of the following tests use large quantities of data, t3 is always 0.
289 #
290 foreach {i conf1 cmd t0 t1 t2 t3 t4} {
291 1 {} UPDATE 1 {6 7 8 9} 1 0 1
292 2 REPLACE UPDATE 0 {7 6 9} 1 0 0
293 3 IGNORE UPDATE 0 {6 7 3 9} 1 0 0
294 4 FAIL UPDATE 1 {6 7 3 4} 1 0 0
295 5 ABORT UPDATE 1 {1 2 3 4} 1 0 1
296 6 ROLLBACK UPDATE 1 {1 2 3 4} 0 0 0
297 7 REPLACE {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
298 8 IGNORE {UPDATE OR REPLACE} 0 {7 6 9} 1 0 1
299 9 FAIL {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
300 10 ABORT {UPDATE OR REPLACE} 0 {7 6 9} 1 0 1
301 11 ROLLBACK {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
302 12 {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0
303 13 {} {UPDATE OR REPLACE} 0 {7 6 9} 1 0 1
304 14 {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 0 0
305 15 {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 0 1
306 16 {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 0 0
307 } {
309 # When using in-memory journals, no temporary files are required for
310 # statement journals.
311 if {[permutation] == "inmemory_journal"} { set t4 0 }
313 if {$t0} {set t1 {UNIQUE constraint failed: t1.a}}
314 if {[info exists TEMP_STORE] && $TEMP_STORE==3} {
315 set t3 0
316 } else {
317 set t3 [expr {$t3+$t4}]
318 }
319 do_test conflict2-6.$i {
320 db close
321 sqlite3 db test.db
322 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
323 execsql {pragma temp_store=file}
324 set ::sqlite_opentemp_count 0
325 set r0 [catch {execsql [subst {
326 DROP TABLE t1;
327 CREATE TABLE t1(a,b,c, PRIMARY KEY(a) $conf1) WITHOUT rowid;
328 INSERT INTO t1 SELECT * FROM t2;
329 UPDATE t3 SET x=0;
330 BEGIN;
331 $cmd t3 SET x=1;
332 $cmd t1 SET b=b*2;
333 $cmd t1 SET a=c+5;
334 }]} r1]
335 catch {execsql {COMMIT}}
336 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}
337 set r2 [execsql {SELECT x FROM t3}]
338 list $r0 $r1 $r2 $::sqlite_opentemp_count
339 } [list $t0 $t1 $t2 $t3]
340 }
342 # Test to make sure a lot of IGNOREs don't cause a stack overflow
343 #
344 do_test conflict2-7.1 {
345 execsql {
346 DROP TABLE t1;
347 DROP TABLE t2;
348 DROP TABLE t3;
349 CREATE TABLE t1(a PRIMARY KEY, b) without rowid;
350 }
351 for {set i 1} {$i<=50} {incr i} {
352 execsql "INSERT into t1 values($i,[expr {$i+1}]);"
353 }
354 execsql {
355 SELECT count(*), min(a), max(b) FROM t1;
356 }
357 } {50 1 51}
358 do_test conflict2-7.2 {
359 execsql {
360 PRAGMA count_changes=on;
361 UPDATE OR IGNORE t1 SET a=1000;
362 }
363 } {1}
364 do_test conflict2-7.2.1 {
365 db changes
366 } {1}
367 do_test conflict2-7.3 {
368 execsql {
369 SELECT b FROM t1 WHERE a=1000;
370 }
371 } {2}
372 do_test conflict2-7.4 {
373 execsql {
374 SELECT count(*) FROM t1;
375 }
376 } {50}
377 do_test conflict2-7.5 {
378 execsql {
379 PRAGMA count_changes=on;
380 UPDATE OR REPLACE t1 SET a=1001;
381 }
382 } {50}
383 do_test conflict2-7.5.1 {
384 db changes
385 } {50}
386 do_test conflict2-7.7 {
387 execsql {
388 SELECT count(*) FROM t1;
389 }
390 } {1}
392 # Update for version 3: A SELECT statement no longer resets the change
393 # counter (Test result changes from 0 to 50).
394 do_test conflict2-7.7.1 {
395 db changes
396 } {50}
398 # Make sure the row count is right for rows that are ignored on
399 # an insert.
400 #
401 do_test conflict2-8.1 {
402 execsql {
403 DELETE FROM t1;
404 INSERT INTO t1 VALUES(1,2);
405 }
406 execsql {
407 INSERT OR IGNORE INTO t1 VALUES(2,3);
408 }
409 } {1}
410 do_test conflict2-8.1.1 {
411 db changes
412 } {1}
413 do_test conflict2-8.2 {
414 execsql {
415 INSERT OR IGNORE INTO t1 VALUES(2,4);
416 }
417 } {0}
418 do_test conflict2-8.2.1 {
419 db changes
420 } {0}
421 do_test conflict2-8.3 {
422 execsql {
423 INSERT OR REPLACE INTO t1 VALUES(2,4);
424 }
425 } {1}
426 do_test conflict2-8.3.1 {
427 db changes
428 } {1}
429 do_test conflict2-8.4 {
430 execsql {
431 INSERT OR IGNORE INTO t1 SELECT * FROM t1;
432 }
433 } {0}
434 do_test conflict2-8.4.1 {
435 db changes
436 } {0}
437 do_test conflict2-8.5 {
438 execsql {
439 INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1;
440 }
441 } {2}
442 do_test conflict2-8.5.1 {
443 db changes
444 } {2}
445 do_test conflict2-8.6 {
446 execsql {
447 INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
448 }
449 } {3}
450 do_test conflict2-8.6.1 {
451 db changes
452 } {3}
454 integrity_check conflict2-8.99
456 do_test conflict2-9.1 {
457 execsql {
458 PRAGMA count_changes=0;
459 CREATE TABLE t2(
460 a INTEGER PRIMARY KEY ON CONFLICT IGNORE,
461 b INTEGER UNIQUE ON CONFLICT FAIL,
462 c INTEGER UNIQUE ON CONFLICT REPLACE,
463 d INTEGER UNIQUE ON CONFLICT ABORT,
464 e INTEGER UNIQUE ON CONFLICT ROLLBACK
465 ) WITHOUT rowid;
466 CREATE TABLE t3(x);
467 INSERT INTO t3 VALUES(1);
468 SELECT * FROM t3;
469 }
470 } {1}
471 do_test conflict2-9.2 {
472 catchsql {
473 INSERT INTO t2 VALUES(1,1,1,1,1);
474 INSERT INTO t2 VALUES(2,2,2,2,2);
475 SELECT * FROM t2;
476 }
477 } {0 {1 1 1 1 1 2 2 2 2 2}}
478 do_test conflict2-9.3 {
479 catchsql {
480 INSERT INTO t2 VALUES(1,3,3,3,3);
481 SELECT * FROM t2;
482 }
483 } {0 {1 1 1 1 1 2 2 2 2 2}}
484 do_test conflict2-9.4 {
485 catchsql {
486 UPDATE t2 SET a=a+1 WHERE a=1;
487 SELECT * FROM t2;
488 }
489 } {0 {1 1 1 1 1 2 2 2 2 2}}
490 do_test conflict2-9.5 {
491 catchsql {
492 INSERT INTO t2 VALUES(3,1,3,3,3);
493 }
494 } {1 {UNIQUE constraint failed: t2.b}}
495 do_test conflict2-9.5b {
496 db eval {SELECT * FROM t2;}
497 } {1 1 1 1 1 2 2 2 2 2}
498 do_test conflict2-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 conflict2-9.6b {
505 db eval {SELECT * FROM t2;}
506 } {1 1 1 1 1 2 2 2 2 2}
507 do_test conflict2-9.7 {
508 catchsql {
509 BEGIN;
510 UPDATE t3 SET x=x+1;
511 INSERT INTO t2 VALUES(3,1,3,3,3);
512 SELECT * FROM t2;
513 }
514 } {1 {UNIQUE constraint failed: t2.b}}
515 do_test conflict2-9.8 {
516 execsql {COMMIT}
517 execsql {SELECT * FROM t3}
518 } {2}
519 do_test conflict2-9.9 {
520 catchsql {
521 BEGIN;
522 UPDATE t3 SET x=x+1;
523 UPDATE t2 SET b=b+1 WHERE b=1;
524 SELECT * FROM t2;
525 }
526 } {1 {UNIQUE constraint failed: t2.b}}
527 do_test conflict2-9.10 {
528 execsql {COMMIT}
529 execsql {SELECT * FROM t3}
530 } {3}
531 do_test conflict2-9.11 {
532 catchsql {
533 INSERT INTO t2 VALUES(3,3,3,1,3);
534 SELECT * FROM t2;
535 }
536 } {1 {UNIQUE constraint failed: t2.d}}
537 do_test conflict2-9.12 {
538 catchsql {
539 UPDATE t2 SET d=d+1 WHERE d=1;
540 SELECT * FROM t2;
541 }
542 } {1 {UNIQUE constraint failed: t2.d}}
543 do_test conflict2-9.13 {
544 catchsql {
545 BEGIN;
546 UPDATE t3 SET x=x+1;
547 INSERT INTO t2 VALUES(3,3,3,1,3);
548 SELECT * FROM t2;
549 }
550 } {1 {UNIQUE constraint failed: t2.d}}
551 do_test conflict2-9.14 {
552 execsql {COMMIT}
553 execsql {SELECT * FROM t3}
554 } {4}
555 do_test conflict2-9.15 {
556 catchsql {
557 BEGIN;
558 UPDATE t3 SET x=x+1;
559 UPDATE t2 SET d=d+1 WHERE d=1;
560 SELECT * FROM t2;
561 }
562 } {1 {UNIQUE constraint failed: t2.d}}
563 do_test conflict2-9.16 {
564 execsql {COMMIT}
565 execsql {SELECT * FROM t3}
566 } {5}
567 do_test conflict2-9.17 {
568 catchsql {
569 INSERT INTO t2 VALUES(3,3,3,3,1);
570 SELECT * FROM t2;
571 }
572 } {1 {UNIQUE constraint failed: t2.e}}
573 do_test conflict2-9.18 {
574 catchsql {
575 UPDATE t2 SET e=e+1 WHERE e=1;
576 SELECT * FROM t2;
577 }
578 } {1 {UNIQUE constraint failed: t2.e}}
579 do_test conflict2-9.19 {
580 catchsql {
581 BEGIN;
582 UPDATE t3 SET x=x+1;
583 INSERT INTO t2 VALUES(3,3,3,3,1);
584 SELECT * FROM t2;
585 }
586 } {1 {UNIQUE constraint failed: t2.e}}
587 verify_ex_errcode conflict2-9.21b SQLITE_CONSTRAINT_UNIQUE
588 do_test conflict2-9.20 {
589 catch {execsql {COMMIT}}
590 execsql {SELECT * FROM t3}
591 } {5}
592 do_test conflict2-9.21 {
593 catchsql {
594 BEGIN;
595 UPDATE t3 SET x=x+1;
596 UPDATE t2 SET e=e+1 WHERE e=1;
597 SELECT * FROM t2;
598 }
599 } {1 {UNIQUE constraint failed: t2.e}}
600 verify_ex_errcode conflict2-9.21b SQLITE_CONSTRAINT_UNIQUE
601 do_test conflict2-9.22 {
602 catch {execsql {COMMIT}}
603 execsql {SELECT * FROM t3}
604 } {5}
605 do_test conflict2-9.23 {
606 catchsql {
607 INSERT INTO t2 VALUES(3,3,1,3,3);
608 SELECT * FROM t2;
609 }
610 } {0 {2 2 2 2 2 3 3 1 3 3}}
611 do_test conflict2-9.24 {
612 catchsql {
613 UPDATE t2 SET c=c-1 WHERE c=2;
614 SELECT * FROM t2;
615 }
616 } {0 {2 2 1 2 2}}
617 do_test conflict2-9.25 {
618 catchsql {
619 BEGIN;
620 UPDATE t3 SET x=x+1;
621 INSERT INTO t2 VALUES(3,3,1,3,3);
622 SELECT * FROM t2;
623 }
624 } {0 {3 3 1 3 3}}
625 do_test conflict2-9.26 {
626 catch {execsql {COMMIT}}
627 execsql {SELECT * FROM t3}
628 } {6}
630 do_test conflict2-10.1 {
631 catchsql {
632 DELETE FROM t1;
633 BEGIN;
634 INSERT OR ROLLBACK INTO t1 VALUES(1,2);
635 INSERT OR ROLLBACK INTO t1 VALUES(1,3);
636 COMMIT;
637 }
638 execsql {SELECT * FROM t1}
639 } {}
640 do_test conflict2-10.2 {
641 catchsql {
642 CREATE TABLE t4(x);
643 CREATE UNIQUE INDEX t4x ON t4(x);
644 BEGIN;
645 INSERT OR ROLLBACK INTO t4 VALUES(1);
646 INSERT OR ROLLBACK INTO t4 VALUES(1);
647 COMMIT;
648 }
649 execsql {SELECT * FROM t4}
650 } {}
652 # Ticket #1171. Make sure statement rollbacks do not
653 # damage the database.
654 #
655 do_test conflict2-11.1 {
656 execsql {
657 -- Create a database object (pages 2, 3 of the file)
658 BEGIN;
659 CREATE TABLE abc(a PRIMARY KEY, b, c) WITHOUT rowid;
660 INSERT INTO abc VALUES(1, 2, 3);
661 INSERT INTO abc VALUES(4, 5, 6);
662 INSERT INTO abc VALUES(7, 8, 9);
663 COMMIT;
664 }
667 # Set a small cache size so that changes will spill into
668 # the database file.
669 execsql {
670 PRAGMA cache_size = 10;
671 }
673 # Make lots of changes. Because of the small cache, some
674 # (most?) of these changes will spill into the disk file.
675 # In other words, some of the changes will not be held in
676 # cache.
677 #
678 execsql {
679 BEGIN;
680 -- Make sure the pager is in EXCLUSIVE state.
681 CREATE TABLE def(d, e, f);
682 INSERT INTO def VALUES
683 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
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 INSERT INTO def SELECT * FROM def;
689 INSERT INTO def SELECT * FROM def;
690 INSERT INTO def SELECT * FROM def;
691 DELETE FROM abc WHERE a = 4;
692 }
694 # Execute a statement that does a statement rollback due to
695 # a constraint failure.
696 #
697 catchsql {
698 INSERT INTO abc SELECT 10, 20, 30 FROM def;
699 }
701 # Rollback the database. Verify that the state of the ABC table
702 # is unchanged from the beginning of the transaction. In other words,
703 # make sure the DELETE on table ABC that occurred within the transaction
704 # had no effect.
705 #
706 execsql {
707 ROLLBACK;
708 SELECT * FROM abc;
709 }
710 } {1 2 3 4 5 6 7 8 9}
711 integrity_check conflict2-11.2
713 # Repeat test conflict2-11.1 but this time commit.
714 #
715 do_test conflict2-11.3 {
716 execsql {
717 BEGIN;
718 -- Make sure the pager is in EXCLUSIVE state.
719 UPDATE abc SET a=a+1;
720 CREATE TABLE def(d, e, f);
721 INSERT INTO def VALUES
722 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
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 INSERT INTO def SELECT * FROM def;
728 INSERT INTO def SELECT * FROM def;
729 INSERT INTO def SELECT * FROM def;
730 DELETE FROM abc WHERE a = 4;
731 }
732 catchsql {
733 INSERT INTO abc SELECT 10, 20, 30 FROM def;
734 }
735 execsql {
736 ROLLBACK;
737 SELECT * FROM abc;
738 }
739 } {1 2 3 4 5 6 7 8 9}
740 # Repeat test conflict2-11.1 but this time commit.
741 #
742 do_test conflict2-11.5 {
743 execsql {
744 BEGIN;
745 -- Make sure the pager is in EXCLUSIVE state.
746 CREATE TABLE def(d, e, f);
747 INSERT INTO def VALUES
748 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz');
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 INSERT INTO def SELECT * FROM def;
754 INSERT INTO def SELECT * FROM def;
755 INSERT INTO def SELECT * FROM def;
756 DELETE FROM abc WHERE a = 4;
757 }
758 catchsql {
759 INSERT INTO abc SELECT 10, 20, 30 FROM def;
760 }
761 execsql {
762 COMMIT;
763 SELECT * FROM abc;
764 }
765 } {1 2 3 7 8 9}
766 integrity_check conflict2-11.6
768 # Make sure UPDATE OR REPLACE works on tables that have only
769 # an INTEGER PRIMARY KEY.
770 #
771 do_test conflict2-12.1 {
772 execsql {
773 CREATE TABLE t5(a INTEGER PRIMARY KEY, b text) WITHOUT rowid;
774 INSERT INTO t5 VALUES(1,'one');
775 INSERT INTO t5 VALUES(2,'two');
776 SELECT * FROM t5
777 }
778 } {1 one 2 two}
779 do_test conflict2-12.2 {
780 execsql {
781 UPDATE OR IGNORE t5 SET a=a+1 WHERE a=1;
782 SELECT * FROM t5;
783 }
784 } {1 one 2 two}
785 do_test conflict2-12.3 {
786 catchsql {
787 UPDATE t5 SET a=a+1 WHERE a=1;
788 }
789 } {1 {UNIQUE constraint failed: t5.a}}
790 verify_ex_errcode conflict2-12.3b SQLITE_CONSTRAINT_PRIMARYKEY
791 do_test conflict2-12.4 {
792 execsql {
793 UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1;
794 SELECT * FROM t5;
795 }
796 } {2 one}
799 # Ticket [c38baa3d969eab7946dc50ba9d9b4f0057a19437]
800 # REPLACE works like ABORT on a CHECK constraint.
801 #
802 do_test conflict2-13.1 {
803 execsql {
804 CREATE TABLE t13(a PRIMARY KEY CHECK(a!=2)) WITHOUT rowid;
805 BEGIN;
806 REPLACE INTO t13 VALUES(1);
807 }
808 catchsql {
809 REPLACE INTO t13 VALUES(2);
810 }
811 } {1 {CHECK constraint failed: t13}}
812 verify_ex_errcode conflict2-13.1b SQLITE_CONSTRAINT_CHECK
813 do_test conflict2-13.2 {
814 execsql {
815 REPLACE INTO t13 VALUES(3);
816 COMMIT;
817 SELECT * FROM t13;
818 }
819 } {1 3}
821 # Test for an unreleased bug in the REPLACE conflict resolution
822 # discovered on 2013-11-09.
823 #
824 do_execsql_test conflict2-14.1 {
825 DROP TABLE IF EXISTS t1;
826 CREATE TABLE t1(
827 x TEXT PRIMARY KEY NOT NULL,
828 y TEXT NOT NULL,
829 z INTEGER
830 );
831 INSERT INTO t1 VALUES('alpha','beta',1);
832 CREATE UNIQUE INDEX t1xy ON t1(x,y);
833 REPLACE INTO t1(x,y,z) VALUES('alpha','gamma',1);
834 PRAGMA integrity_check;
835 SELECT x,y FROM t1 INDEXED BY t1xy;
836 SELECT x,y,z FROM t1 NOT INDEXED;
837 } {ok alpha gamma alpha gamma 1}
838 do_execsql_test conflict2-14.2 {
839 DROP TABLE IF EXISTS t1;
840 CREATE TABLE t1(
841 x TEXT PRIMARY KEY NOT NULL,
842 y TEXT NOT NULL,
843 z INTEGER
844 ) WITHOUT ROWID;
845 INSERT INTO t1 VALUES('alpha','beta',1);
846 CREATE UNIQUE INDEX t1xy ON t1(x,y);
847 REPLACE INTO t1(x,y,z) VALUES('alpha','gamma',1);
848 PRAGMA integrity_check;
849 SELECT x,y FROM t1 INDEXED BY t1xy;
850 SELECT x,y,z FROM t1 NOT INDEXED;
851 } {ok alpha gamma alpha gamma 1}
855 finish_test