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