| blob | 82b95d641aecaa1570c10239842a9a77457b1f5a |
1 #
2 # 2002 January 29
3 #
4 # The author disclaims copyright to this source code. In place of
5 # a legal notice, here is a blessing:
6 #
7 # May you do good and not evil.
8 # May you find forgiveness for yourself and forgive others.
9 # May you share freely, never taking more than you give.
10 #
11 #***********************************************************************
12 # This file implements regression tests for SQLite library.
13 #
14 # This file implements tests for the conflict resolution extension
15 # to SQLite.
16 #
17 # $Id: conflict.test,v 1.19 2003/08/05 13:13:39 drh Exp $
19 set testdir [file dirname $argv0]
20 source $testdir/tester.tcl
22 # Create tables for the first group of tests.
23 #
24 do_test conflict-1.0 {
25 execsql {
26 CREATE TABLE t1(a, b, c, UNIQUE(a,b));
27 CREATE TABLE t2(x);
28 SELECT c FROM t1 ORDER BY c;
29 }
30 } {}
32 # Six columns of configuration data as follows:
33 #
34 # i The reference number of the test
35 # conf The conflict resolution algorithm on the BEGIN statement
36 # cmd An INSERT or REPLACE command to execute against table t1
37 # t0 True if there is an error from $cmd
38 # t1 Content of "c" column of t1 assuming no error in $cmd
39 # t2 Content of "x" column of t2
40 #
41 foreach {i conf cmd t0 t1 t2} {
42 1 {} INSERT 1 {} 1
43 2 {} {INSERT OR IGNORE} 0 3 1
44 3 {} {INSERT OR REPLACE} 0 4 1
45 4 {} REPLACE 0 4 1
46 5 {} {INSERT OR FAIL} 1 {} 1
47 6 {} {INSERT OR ABORT} 1 {} 1
48 7 {} {INSERT OR ROLLBACK} 1 {} {}
49 8 IGNORE INSERT 0 3 1
50 9 IGNORE {INSERT OR IGNORE} 0 3 1
51 10 IGNORE {INSERT OR REPLACE} 0 4 1
52 11 IGNORE REPLACE 0 4 1
53 12 IGNORE {INSERT OR FAIL} 1 {} 1
54 13 IGNORE {INSERT OR ABORT} 1 {} 1
55 14 IGNORE {INSERT OR ROLLBACK} 1 {} {}
56 15 REPLACE INSERT 0 4 1
57 16 FAIL INSERT 1 {} 1
58 17 ABORT INSERT 1 {} 1
59 18 ROLLBACK INSERT 1 {} {}
60 } {
61 do_test conflict-1.$i {
62 if {$conf!=""} {set conf "ON CONFLICT $conf"}
63 set r0 [catch {execsql [subst {
64 DELETE FROM t1;
65 DELETE FROM t2;
66 INSERT INTO t1 VALUES(1,2,3);
67 BEGIN $conf;
68 INSERT INTO t2 VALUES(1);
69 $cmd INTO t1 VALUES(1,2,4);
70 }]} r1]
71 catch {execsql {COMMIT}}
72 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
73 set r2 [execsql {SELECT x FROM t2}]
74 list $r0 $r1 $r2
75 } [list $t0 $t1 $t2]
76 }
78 # Create tables for the first group of tests.
79 #
80 do_test conflict-2.0 {
81 execsql {
82 DROP TABLE t1;
83 DROP TABLE t2;
84 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b));
85 CREATE TABLE t2(x);
86 SELECT c FROM t1 ORDER BY c;
87 }
88 } {}
90 # Six columns of configuration data as follows:
91 #
92 # i The reference number of the test
93 # conf The conflict resolution algorithm on the BEGIN statement
94 # cmd An INSERT or REPLACE command to execute against table t1
95 # t0 True if there is an error from $cmd
96 # t1 Content of "c" column of t1 assuming no error in $cmd
97 # t2 Content of "x" column of t2
98 #
99 foreach {i conf cmd t0 t1 t2} {
100 1 {} INSERT 1 {} 1
101 2 {} {INSERT OR IGNORE} 0 3 1
102 3 {} {INSERT OR REPLACE} 0 4 1
103 4 {} REPLACE 0 4 1
104 5 {} {INSERT OR FAIL} 1 {} 1
105 6 {} {INSERT OR ABORT} 1 {} 1
106 7 {} {INSERT OR ROLLBACK} 1 {} {}
107 8 IGNORE INSERT 0 3 1
108 9 IGNORE {INSERT OR IGNORE} 0 3 1
109 10 IGNORE {INSERT OR REPLACE} 0 4 1
110 11 IGNORE REPLACE 0 4 1
111 12 IGNORE {INSERT OR FAIL} 1 {} 1
112 13 IGNORE {INSERT OR ABORT} 1 {} 1
113 14 IGNORE {INSERT OR ROLLBACK} 1 {} {}
114 15 REPLACE INSERT 0 4 1
115 16 FAIL INSERT 1 {} 1
116 17 ABORT INSERT 1 {} 1
117 18 ROLLBACK INSERT 1 {} {}
118 } {
119 do_test conflict-2.$i {
120 if {$conf!=""} {set conf "ON CONFLICT $conf"}
121 set r0 [catch {execsql [subst {
122 DELETE FROM t1;
123 DELETE FROM t2;
124 INSERT INTO t1 VALUES(1,2,3);
125 BEGIN $conf;
126 INSERT INTO t2 VALUES(1);
127 $cmd INTO t1 VALUES(1,2,4);
128 }]} r1]
129 catch {execsql {COMMIT}}
130 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
131 set r2 [execsql {SELECT x FROM t2}]
132 list $r0 $r1 $r2
133 } [list $t0 $t1 $t2]
134 }
136 # Create tables for the first group of tests.
137 #
138 do_test conflict-3.0 {
139 execsql {
140 DROP TABLE t1;
141 DROP TABLE t2;
142 CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b));
143 CREATE TABLE t2(x);
144 SELECT c FROM t1 ORDER BY c;
145 }
146 } {}
148 # Six columns of configuration data as follows:
149 #
150 # i The reference number of the test
151 # conf The conflict resolution algorithm on the BEGIN statement
152 # cmd An INSERT or REPLACE command to execute against table t1
153 # t0 True if there is an error from $cmd
154 # t1 Content of "c" column of t1 assuming no error in $cmd
155 # t2 Content of "x" column of t2
156 #
157 foreach {i conf cmd t0 t1 t2} {
158 1 {} INSERT 1 {} 1
159 2 {} {INSERT OR IGNORE} 0 3 1
160 3 {} {INSERT OR REPLACE} 0 4 1
161 4 {} REPLACE 0 4 1
162 5 {} {INSERT OR FAIL} 1 {} 1
163 6 {} {INSERT OR ABORT} 1 {} 1
164 7 {} {INSERT OR ROLLBACK} 1 {} {}
165 8 IGNORE INSERT 0 3 1
166 9 IGNORE {INSERT OR IGNORE} 0 3 1
167 10 IGNORE {INSERT OR REPLACE} 0 4 1
168 11 IGNORE REPLACE 0 4 1
169 12 IGNORE {INSERT OR FAIL} 1 {} 1
170 13 IGNORE {INSERT OR ABORT} 1 {} 1
171 14 IGNORE {INSERT OR ROLLBACK} 1 {} {}
172 15 REPLACE INSERT 0 4 1
173 16 FAIL INSERT 1 {} 1
174 17 ABORT INSERT 1 {} 1
175 18 ROLLBACK INSERT 1 {} {}
176 } {
177 do_test conflict-3.$i {
178 if {$conf!=""} {set conf "ON CONFLICT $conf"}
179 set r0 [catch {execsql [subst {
180 DELETE FROM t1;
181 DELETE FROM t2;
182 INSERT INTO t1 VALUES(1,2,3);
183 BEGIN $conf;
184 INSERT INTO t2 VALUES(1);
185 $cmd INTO t1 VALUES(1,2,4);
186 }]} r1]
187 catch {execsql {COMMIT}}
188 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
189 set r2 [execsql {SELECT x FROM t2}]
190 list $r0 $r1 $r2
191 } [list $t0 $t1 $t2]
192 }
194 do_test conflict-4.0 {
195 execsql {
196 DROP TABLE t2;
197 CREATE TABLE t2(x);
198 SELECT x FROM t2;
199 }
200 } {}
202 # Six columns of configuration data as follows:
203 #
204 # i The reference number of the test
205 # conf1 The conflict resolution algorithm on the UNIQUE constraint
206 # conf2 The conflict resolution algorithm on the BEGIN statement
207 # cmd An INSERT or REPLACE command to execute against table t1
208 # t0 True if there is an error from $cmd
209 # t1 Content of "c" column of t1 assuming no error in $cmd
210 # t2 Content of "x" column of t2
211 #
212 foreach {i conf1 conf2 cmd t0 t1 t2} {
213 1 {} {} INSERT 1 {} 1
214 2 REPLACE {} INSERT 0 4 1
215 3 IGNORE {} INSERT 0 3 1
216 4 FAIL {} INSERT 1 {} 1
217 5 ABORT {} INSERT 1 {} 1
218 6 ROLLBACK {} INSERT 1 {} {}
219 7 REPLACE {} {INSERT OR IGNORE} 0 3 1
220 8 IGNORE {} {INSERT OR REPLACE} 0 4 1
221 9 FAIL {} {INSERT OR IGNORE} 0 3 1
222 10 ABORT {} {INSERT OR REPLACE} 0 4 1
223 11 ROLLBACK {} {INSERT OR IGNORE } 0 3 1
224 12 REPLACE IGNORE INSERT 0 3 1
225 13 IGNORE REPLACE INSERT 0 4 1
226 14 FAIL IGNORE INSERT 0 3 1
227 15 ABORT REPLACE INSERT 0 4 1
228 16 ROLLBACK IGNORE INSERT 0 3 1
229 12 IGNORE REPLACE INSERT 0 4 1
230 13 IGNORE FAIL INSERT 1 {} 1
231 14 IGNORE ABORT INSERT 1 {} 1
232 15 IGNORE ROLLBACK INSERT 1 {} {}
233 } {
234 do_test conflict-4.$i {
235 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
236 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
237 set r0 [catch {execsql [subst {
238 DROP TABLE t1;
239 CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1);
240 DELETE FROM t2;
241 INSERT INTO t1 VALUES(1,2,3);
242 BEGIN $conf2;
243 INSERT INTO t2 VALUES(1);
244 $cmd INTO t1 VALUES(1,2,4);
245 }]} r1]
246 catch {execsql {COMMIT}}
247 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]}
248 set r2 [execsql {SELECT x FROM t2}]
249 list $r0 $r1 $r2
250 } [list $t0 $t1 $t2]
251 }
253 do_test conflict-5.0 {
254 execsql {
255 DROP TABLE t2;
256 CREATE TABLE t2(x);
257 SELECT x FROM t2;
258 }
259 } {}
261 # Six columns of configuration data as follows:
262 #
263 # i The reference number of the test
264 # conf1 The conflict resolution algorithm on the NOT NULL constraint
265 # conf2 The conflict resolution algorithm on the BEGIN statement
266 # cmd An INSERT or REPLACE command to execute against table t1
267 # t0 True if there is an error from $cmd
268 # t1 Content of "c" column of t1 assuming no error in $cmd
269 # t2 Content of "x" column of t2
270 #
271 foreach {i conf1 conf2 cmd t0 t1 t2} {
272 1 {} {} INSERT 1 {} 1
273 2 REPLACE {} INSERT 0 5 1
274 3 IGNORE {} INSERT 0 {} 1
275 4 FAIL {} INSERT 1 {} 1
276 5 ABORT {} INSERT 1 {} 1
277 6 ROLLBACK {} INSERT 1 {} {}
278 7 REPLACE {} {INSERT OR IGNORE} 0 {} 1
279 8 IGNORE {} {INSERT OR REPLACE} 0 5 1
280 9 FAIL {} {INSERT OR IGNORE} 0 {} 1
281 10 ABORT {} {INSERT OR REPLACE} 0 5 1
282 11 ROLLBACK {} {INSERT OR IGNORE} 0 {} 1
283 12 {} {} {INSERT OR IGNORE} 0 {} 1
284 13 {} {} {INSERT OR REPLACE} 0 5 1
285 14 {} {} {INSERT OR FAIL} 1 {} 1
286 15 {} {} {INSERT OR ABORT} 1 {} 1
287 16 {} {} {INSERT OR ROLLBACK} 1 {} {}
288 17 {} IGNORE INSERT 0 {} 1
289 18 {} REPLACE INSERT 0 5 1
290 19 {} FAIL INSERT 1 {} 1
291 20 {} ABORT INSERT 1 {} 1
292 21 {} ROLLBACK INSERT 1 {} {}
293 22 REPLACE FAIL INSERT 1 {} 1
294 23 IGNORE ROLLBACK INSERT 1 {} {}
295 } {
296 if {$t0} {set t1 {t1.c may not be NULL}}
297 do_test conflict-5.$i {
298 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
299 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
300 set r0 [catch {execsql [subst {
301 DROP TABLE t1;
302 CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5);
303 DELETE FROM t2;
304 BEGIN $conf2;
305 INSERT INTO t2 VALUES(1);
306 $cmd INTO t1 VALUES(1,2,NULL);
307 }]} r1]
308 catch {execsql {COMMIT}}
309 if {!$r0} {set r1 [execsql {SELECT c FROM t1}]}
310 set r2 [execsql {SELECT x FROM t2}]
311 list $r0 $r1 $r2
312 } [list $t0 $t1 $t2]
313 }
315 do_test conflict-6.0 {
316 execsql {
317 DROP TABLE t2;
318 CREATE TABLE t2(a,b,c);
319 INSERT INTO t2 VALUES(1,2,1);
320 INSERT INTO t2 VALUES(2,3,2);
321 INSERT INTO t2 VALUES(3,4,1);
322 INSERT INTO t2 VALUES(4,5,4);
323 SELECT c FROM t2 ORDER BY b;
324 CREATE TABLE t3(x);
325 INSERT INTO t3 VALUES(1);
326 }
327 } {1 2 1 4}
329 # Six columns of configuration data as follows:
330 #
331 # i The reference number of the test
332 # conf1 The conflict resolution algorithm on the UNIQUE constraint
333 # conf2 The conflict resolution algorithm on the BEGIN statement
334 # cmd An UPDATE command to execute against table t1
335 # t0 True if there is an error from $cmd
336 # t1 Content of "b" column of t1 assuming no error in $cmd
337 # t2 Content of "x" column of t3
338 #
339 foreach {i conf1 conf2 cmd t0 t1 t2} {
340 1 {} {} UPDATE 1 {6 7 8 9} 1
341 2 REPLACE {} UPDATE 0 {7 6 9} 1
342 3 IGNORE {} UPDATE 0 {6 7 3 9} 1
343 4 FAIL {} UPDATE 1 {6 7 3 4} 1
344 5 ABORT {} UPDATE 1 {1 2 3 4} 1
345 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0
346 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1
347 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1
348 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1
349 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1
350 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1
351 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1
352 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1
353 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1
354 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1
355 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0
356 17 {} IGNORE UPDATE 0 {6 7 3 9} 1
357 18 {} REPLACE UPDATE 0 {7 6 9} 1
358 19 {} FAIL UPDATE 1 {6 7 3 4} 1
359 20 {} ABORT UPDATE 1 {1 2 3 4} 1
360 21 {} ROLLBACK UPDATE 1 {1 2 3 4} 0
361 22 REPLACE IGNORE UPDATE 0 {6 7 3 9} 1
362 23 IGNORE REPLACE UPDATE 0 {7 6 9} 1
363 24 REPLACE FAIL UPDATE 1 {6 7 3 4} 1
364 25 IGNORE ABORT UPDATE 1 {1 2 3 4} 1
365 26 REPLACE ROLLBACK UPDATE 1 {1 2 3 4} 0
366 } {
367 if {$t0} {set t1 {column a is not unique}}
368 do_test conflict-6.$i {
369 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"}
370 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"}
371 set r0 [catch {execsql [subst {
372 DROP TABLE t1;
373 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1);
374 INSERT INTO t1 SELECT * FROM t2;
375 UPDATE t3 SET x=0;
376 BEGIN $conf2;
377 $cmd t3 SET x=1;
378 $cmd t1 SET b=b*2;
379 $cmd t1 SET a=c+5;
380 }]} r1]
381 catch {execsql {COMMIT}}
382 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]}
383 set r2 [execsql {SELECT x FROM t3}]
384 list $r0 $r1 $r2
385 } [list $t0 $t1 $t2]
386 }
388 # Test to make sure a lot of IGNOREs don't cause a stack overflow
389 #
390 do_test conflict-7.1 {
391 execsql {
392 DROP TABLE t1;
393 DROP TABLE t2;
394 DROP TABLE t3;
395 CREATE TABLE t1(a unique, b);
396 }
397 for {set i 1} {$i<=50} {incr i} {
398 execsql "INSERT into t1 values($i,[expr {$i+1}]);"
399 }
400 execsql {
401 SELECT count(*), min(a), max(b) FROM t1;
402 }
403 } {50 1 51}
404 do_test conflict-7.2 {
405 execsql {
406 PRAGMA count_changes=on;
407 UPDATE OR IGNORE t1 SET a=1000;
408 }
409 } {1}
410 do_test conflict-7.2.1 {
411 db changes
412 } {1}
413 do_test conflict-7.3 {
414 execsql {
415 SELECT b FROM t1 WHERE a=1000;
416 }
417 } {2}
418 do_test conflict-7.4 {
419 execsql {
420 SELECT count(*) FROM t1;
421 }
422 } {50}
423 do_test conflict-7.5 {
424 execsql {
425 PRAGMA count_changes=on;
426 UPDATE OR REPLACE t1 SET a=1001;
427 }
428 } {50}
429 do_test conflict-7.5.1 {
430 db changes
431 } {50}
432 do_test conflict-7.6 {
433 execsql {
434 SELECT b FROM t1 WHERE a=1001;
435 }
436 } {51}
437 do_test conflict-7.7 {
438 execsql {
439 SELECT count(*) FROM t1;
440 }
441 } {1}
442 do_test conflict-7.7.1 {
443 db changes
444 } {0}
446 # Make sure the row count is right for rows that are ignored on
447 # an insert.
448 #
449 do_test conflict-8.1 {
450 execsql {
451 DELETE FROM t1;
452 INSERT INTO t1 VALUES(1,2);
453 }
454 execsql {
455 INSERT OR IGNORE INTO t1 VALUES(2,3);
456 }
457 } {1}
458 do_test conflict-8.1.1 {
459 db changes
460 } {1}
461 do_test conflict-8.2 {
462 execsql {
463 INSERT OR IGNORE INTO t1 VALUES(2,4);
464 }
465 } {0}
466 do_test conflict-8.2.1 {
467 db changes
468 } {0}
469 do_test conflict-8.3 {
470 execsql {
471 INSERT OR REPLACE INTO t1 VALUES(2,4);
472 }
473 } {1}
474 do_test conflict-8.3.1 {
475 db changes
476 } {1}
477 do_test conflict-8.4 {
478 execsql {
479 INSERT OR IGNORE INTO t1 SELECT * FROM t1;
480 }
481 } {0}
482 do_test conflict-8.4.1 {
483 db changes
484 } {0}
485 do_test conflict-8.5 {
486 execsql {
487 INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1;
488 }
489 } {2}
490 do_test conflict-8.5.1 {
491 db changes
492 } {2}
493 do_test conflict-8.6 {
494 execsql {
495 INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
496 }
497 } {3}
498 do_test conflict-8.6.1 {
499 db changes
500 } {3}
502 integrity_check conflict-8.99
504 do_test conflict-9.1 {
505 execsql {
506 PRAGMA count_changes=0;
507 CREATE TABLE t2(
508 a INTEGER UNIQUE ON CONFLICT IGNORE,
509 b INTEGER UNIQUE ON CONFLICT FAIL,
510 c INTEGER UNIQUE ON CONFLICT REPLACE,
511 d INTEGER UNIQUE ON CONFLICT ABORT,
512 e INTEGER UNIQUE ON CONFLICT ROLLBACK
513 );
514 CREATE TABLE t3(x);
515 INSERT INTO t3 VALUES(1);
516 SELECT * FROM t3;
517 }
518 } {1}
519 do_test conflict-9.2 {
520 catchsql {
521 INSERT INTO t2 VALUES(1,1,1,1,1);
522 INSERT INTO t2 VALUES(2,2,2,2,2);
523 SELECT * FROM t2;
524 }
525 } {0 {1 1 1 1 1 2 2 2 2 2}}
526 do_test conflict-9.3 {
527 catchsql {
528 INSERT INTO t2 VALUES(1,3,3,3,3);
529 SELECT * FROM t2;
530 }
531 } {0 {1 1 1 1 1 2 2 2 2 2}}
532 do_test conflict-9.4 {
533 catchsql {
534 UPDATE t2 SET a=a+1 WHERE a=1;
535 SELECT * FROM t2;
536 }
537 } {0 {1 1 1 1 1 2 2 2 2 2}}
538 do_test conflict-9.5 {
539 catchsql {
540 INSERT INTO t2 VALUES(3,1,3,3,3);
541 SELECT * FROM t2;
542 }
543 } {1 {column b is not unique}}
544 do_test conflict-9.6 {
545 catchsql {
546 UPDATE t2 SET b=b+1 WHERE b=1;
547 SELECT * FROM t2;
548 }
549 } {1 {column b is not unique}}
550 do_test conflict-9.7 {
551 catchsql {
552 BEGIN;
553 UPDATE t3 SET x=x+1;
554 INSERT INTO t2 VALUES(3,1,3,3,3);
555 SELECT * FROM t2;
556 }
557 } {1 {column b is not unique}}
558 do_test conflict-9.8 {
559 execsql {COMMIT}
560 execsql {SELECT * FROM t3}
561 } {2}
562 do_test conflict-9.9 {
563 catchsql {
564 BEGIN;
565 UPDATE t3 SET x=x+1;
566 UPDATE t2 SET b=b+1 WHERE b=1;
567 SELECT * FROM t2;
568 }
569 } {1 {column b is not unique}}
570 do_test conflict-9.10 {
571 execsql {COMMIT}
572 execsql {SELECT * FROM t3}
573 } {3}
574 do_test conflict-9.11 {
575 catchsql {
576 INSERT INTO t2 VALUES(3,3,3,1,3);
577 SELECT * FROM t2;
578 }
579 } {1 {column d is not unique}}
580 do_test conflict-9.12 {
581 catchsql {
582 UPDATE t2 SET d=d+1 WHERE d=1;
583 SELECT * FROM t2;
584 }
585 } {1 {column d is not unique}}
586 do_test conflict-9.13 {
587 catchsql {
588 BEGIN;
589 UPDATE t3 SET x=x+1;
590 INSERT INTO t2 VALUES(3,3,3,1,3);
591 SELECT * FROM t2;
592 }
593 } {1 {column d is not unique}}
594 do_test conflict-9.14 {
595 execsql {COMMIT}
596 execsql {SELECT * FROM t3}
597 } {4}
598 do_test conflict-9.15 {
599 catchsql {
600 BEGIN;
601 UPDATE t3 SET x=x+1;
602 UPDATE t2 SET d=d+1 WHERE d=1;
603 SELECT * FROM t2;
604 }
605 } {1 {column d is not unique}}
606 do_test conflict-9.16 {
607 execsql {COMMIT}
608 execsql {SELECT * FROM t3}
609 } {5}
610 do_test conflict-9.17 {
611 catchsql {
612 INSERT INTO t2 VALUES(3,3,3,3,1);
613 SELECT * FROM t2;
614 }
615 } {1 {column e is not unique}}
616 do_test conflict-9.18 {
617 catchsql {
618 UPDATE t2 SET e=e+1 WHERE e=1;
619 SELECT * FROM t2;
620 }
621 } {1 {column e is not unique}}
622 do_test conflict-9.19 {
623 catchsql {
624 BEGIN;
625 UPDATE t3 SET x=x+1;
626 INSERT INTO t2 VALUES(3,3,3,3,1);
627 SELECT * FROM t2;
628 }
629 } {1 {column e is not unique}}
630 do_test conflict-9.20 {
631 catch {execsql {COMMIT}}
632 execsql {SELECT * FROM t3}
633 } {5}
634 do_test conflict-9.21 {
635 catchsql {
636 BEGIN;
637 UPDATE t3 SET x=x+1;
638 UPDATE t2 SET e=e+1 WHERE e=1;
639 SELECT * FROM t2;
640 }
641 } {1 {column e is not unique}}
642 do_test conflict-9.22 {
643 catch {execsql {COMMIT}}
644 execsql {SELECT * FROM t3}
645 } {5}
646 do_test conflict-9.23 {
647 catchsql {
648 INSERT INTO t2 VALUES(3,3,1,3,3);
649 SELECT * FROM t2;
650 }
651 } {0 {2 2 2 2 2 3 3 1 3 3}}
652 do_test conflict-9.24 {
653 catchsql {
654 UPDATE t2 SET c=c-1 WHERE c=2;
655 SELECT * FROM t2;
656 }
657 } {0 {2 2 1 2 2}}
658 do_test conflict-9.25 {
659 catchsql {
660 BEGIN;
661 UPDATE t3 SET x=x+1;
662 INSERT INTO t2 VALUES(3,3,1,3,3);
663 SELECT * FROM t2;
664 }
665 } {0 {3 3 1 3 3}}
666 do_test conflict-9.26 {
667 catch {execsql {COMMIT}}
668 execsql {SELECT * FROM t3}
669 } {6}
671 do_test conflict-10.1 {
672 catchsql {
673 DELETE FROM t1;
674 BEGIN ON CONFLICT ROLLBACK;
675 INSERT INTO t1 VALUES(1,2);
676 INSERT INTO t1 VALUES(1,3);
677 COMMIT;
678 }
679 execsql {SELECT * FROM t1}
680 } {}
681 do_test conflict-10.2 {
682 catchsql {
683 CREATE TABLE t4(x);
684 CREATE UNIQUE INDEX t4x ON t4(x);
685 BEGIN ON CONFLICT ROLLBACK;
686 INSERT INTO t4 VALUES(1);
687 INSERT INTO t4 VALUES(1);
688 COMMIT;
689 }
690 execsql {SELECT * FROM t4}
691 } {}
693 integrity_check conflict-99.0
695 finish_test