blob: b51a55ee71b15d497e616674bb325dd1be09ccef [file] [log] [blame]
drh8d1b82e2013-11-05 19:41:32 +00001# 2013-11-05
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# This file focuses on making sure that combinations of REPLACE,
17# IGNORE, and FAIL conflict resolution play well together.
18#
19
20set testdir [file dirname $argv0]
21source $testdir/tester.tcl
22
23ifcapable !conflict {
24 finish_test
25 return
26}
27
28do_execsql_test conflict-1.1 {
29 CREATE TABLE t1(
30 a INTEGER PRIMARY KEY ON CONFLICT REPLACE,
31 b UNIQUE ON CONFLICT IGNORE,
32 c UNIQUE ON CONFLICT FAIL
33 );
34 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
35 SELECT a,b,c FROM t1 ORDER BY a;
36} {1 2 3 2 3 4}
37
38# Insert a row that conflicts on column B. The insert should be ignored.
39#
40do_execsql_test conflict-1.2 {
41 INSERT INTO t1(a,b,c) VALUES(3,2,5);
42 SELECT a,b,c FROM t1 ORDER BY a;
43} {1 2 3 2 3 4}
44
45# Insert two rows where the second conflicts on C. The first row show go
46# and and then there should be a constraint error.
47#
48do_test conflict-1.3 {
49 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
50} {1 {UNIQUE constraint failed: t1.c}}
51do_execsql_test conflict-1.4 {
52 SELECT a,b,c FROM t1 ORDER BY a;
53} {1 2 3 2 3 4 4 5 6}
54
55# Replete the tests above, but this time on a table non-INTEGER primary key.
56#
57do_execsql_test conflict-2.1 {
58 DROP TABLE t1;
59 CREATE TABLE t1(
60 a INT PRIMARY KEY ON CONFLICT REPLACE,
61 b UNIQUE ON CONFLICT IGNORE,
62 c UNIQUE ON CONFLICT FAIL
63 );
64 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
65 SELECT a,b,c FROM t1 ORDER BY a;
66} {1 2 3 2 3 4}
67
68# Insert a row that conflicts on column B. The insert should be ignored.
69#
70do_execsql_test conflict-2.2 {
71 INSERT INTO t1(a,b,c) VALUES(3,2,5);
72 SELECT a,b,c FROM t1 ORDER BY a;
73} {1 2 3 2 3 4}
74
75# Insert two rows where the second conflicts on C. The first row show go
76# and and then there should be a constraint error.
77#
78do_test conflict-2.3 {
79 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
80} {1 {UNIQUE constraint failed: t1.c}}
81do_execsql_test conflict-2.4 {
82 SELECT a,b,c FROM t1 ORDER BY a;
83} {1 2 3 2 3 4 4 5 6}
84
85# Replete again on a WITHOUT ROWID table.
86#
87do_execsql_test conflict-3.1 {
88 DROP TABLE t1;
89 CREATE TABLE t1(
90 a INT PRIMARY KEY ON CONFLICT REPLACE,
91 b UNIQUE ON CONFLICT IGNORE,
92 c UNIQUE ON CONFLICT FAIL
93 ) WITHOUT ROWID;
94 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
95 SELECT a,b,c FROM t1 ORDER BY a;
96} {1 2 3 2 3 4}
97
98# Insert a row that conflicts on column B. The insert should be ignored.
99#
100do_execsql_test conflict-3.2 {
101 INSERT INTO t1(a,b,c) VALUES(3,2,5);
102 SELECT a,b,c FROM t1 ORDER BY a;
103} {1 2 3 2 3 4}
104
105# Insert two rows where the second conflicts on C. The first row show go
106# and and then there should be a constraint error.
107#
108do_test conflict-3.3 {
109 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
110} {1 {UNIQUE constraint failed: t1.c}}
111do_execsql_test conflict-3.4 {
112 SELECT a,b,c FROM t1 ORDER BY a;
113} {1 2 3 2 3 4 4 5 6}
114
115# Arrange the table rows in a different order and repeat.
116#
117do_execsql_test conflict-4.1 {
118 DROP TABLE t1;
119 CREATE TABLE t1(
120 b UNIQUE ON CONFLICT IGNORE,
121 c UNIQUE ON CONFLICT FAIL,
122 a INT PRIMARY KEY ON CONFLICT REPLACE
123 ) WITHOUT ROWID;
124 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
125 SELECT a,b,c FROM t1 ORDER BY a;
126} {1 2 3 2 3 4}
127
128# Insert a row that conflicts on column B. The insert should be ignored.
129#
130do_execsql_test conflict-4.2 {
131 INSERT INTO t1(a,b,c) VALUES(3,2,5);
132 SELECT a,b,c FROM t1 ORDER BY a;
133} {1 2 3 2 3 4}
134
135# Insert two rows where the second conflicts on C. The first row show go
136# and and then there should be a constraint error.
137#
138do_test conflict-4.3 {
139 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
140} {1 {UNIQUE constraint failed: t1.c}}
141do_execsql_test conflict-4.4 {
142 SELECT a,b,c FROM t1 ORDER BY a;
143} {1 2 3 2 3 4 4 5 6}
144
145# Arrange the table rows in a different order and repeat.
146#
147do_execsql_test conflict-5.1 {
148 DROP TABLE t1;
149 CREATE TABLE t1(
150 b UNIQUE ON CONFLICT IGNORE,
151 a INT PRIMARY KEY ON CONFLICT REPLACE,
152 c UNIQUE ON CONFLICT FAIL
153 );
154 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
155 SELECT a,b,c FROM t1 ORDER BY a;
156} {1 2 3 2 3 4}
157
158# Insert a row that conflicts on column B. The insert should be ignored.
159#
160do_execsql_test conflict-5.2 {
161 INSERT INTO t1(a,b,c) VALUES(3,2,5);
162 SELECT a,b,c FROM t1 ORDER BY a;
163} {1 2 3 2 3 4}
164
165# Insert two rows where the second conflicts on C. The first row show go
166# and and then there should be a constraint error.
167#
168do_test conflict-5.3 {
169 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
170} {1 {UNIQUE constraint failed: t1.c}}
171do_execsql_test conflict-5.4 {
172 SELECT a,b,c FROM t1 ORDER BY a;
173} {1 2 3 2 3 4 4 5 6}
174
175# Arrange the table rows in a different order and repeat.
176#
177do_execsql_test conflict-6.1 {
178 DROP TABLE t1;
179 CREATE TABLE t1(
180 c UNIQUE ON CONFLICT FAIL,
181 a INT PRIMARY KEY ON CONFLICT REPLACE,
182 b UNIQUE ON CONFLICT IGNORE
183 );
184 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
185 SELECT a,b,c FROM t1 ORDER BY a;
186} {1 2 3 2 3 4}
187
188# Insert a row that conflicts on column B. The insert should be ignored.
189#
190do_execsql_test conflict-6.2 {
191 INSERT INTO t1(a,b,c) VALUES(3,2,5);
192 SELECT a,b,c FROM t1 ORDER BY a;
193} {1 2 3 2 3 4}
194
195# Insert two rows where the second conflicts on C. The first row show go
196# and and then there should be a constraint error.
197#
198do_test conflict-6.3 {
199 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
200} {1 {UNIQUE constraint failed: t1.c}}
201do_execsql_test conflict-6.4 {
202 SELECT a,b,c FROM t1 ORDER BY a;
203} {1 2 3 2 3 4 4 5 6}
204
205# Change which column is the PRIMARY KEY
206#
207do_execsql_test conflict-7.1 {
208 DROP TABLE t1;
209 CREATE TABLE t1(
210 a UNIQUE ON CONFLICT REPLACE,
211 b INTEGER PRIMARY KEY ON CONFLICT IGNORE,
212 c UNIQUE ON CONFLICT FAIL
213 );
214 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
215 SELECT a,b,c FROM t1 ORDER BY a;
216} {1 2 3 2 3 4}
217
218# Insert a row that conflicts on column B. The insert should be ignored.
219#
220do_execsql_test conflict-7.2 {
221 INSERT INTO t1(a,b,c) VALUES(3,2,5);
222 SELECT a,b,c FROM t1 ORDER BY a;
223} {1 2 3 2 3 4}
224
225# Insert two rows where the second conflicts on C. The first row show go
226# and and then there should be a constraint error.
227#
228do_test conflict-7.3 {
229 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
230} {1 {UNIQUE constraint failed: t1.c}}
231do_execsql_test conflict-7.4 {
232 SELECT a,b,c FROM t1 ORDER BY a;
233} {1 2 3 2 3 4 4 5 6}
234
235# Change which column is the PRIMARY KEY
236#
237do_execsql_test conflict-8.1 {
238 DROP TABLE t1;
239 CREATE TABLE t1(
240 a UNIQUE ON CONFLICT REPLACE,
241 b INT PRIMARY KEY ON CONFLICT IGNORE,
242 c UNIQUE ON CONFLICT FAIL
243 );
244 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
245 SELECT a,b,c FROM t1 ORDER BY a;
246} {1 2 3 2 3 4}
247
248# Insert a row that conflicts on column B. The insert should be ignored.
249#
250do_execsql_test conflict-8.2 {
251 INSERT INTO t1(a,b,c) VALUES(3,2,5);
252 SELECT a,b,c FROM t1 ORDER BY a;
253} {1 2 3 2 3 4}
254
255# Insert two rows where the second conflicts on C. The first row show go
256# and and then there should be a constraint error.
257#
258do_test conflict-8.3 {
259 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
260} {1 {UNIQUE constraint failed: t1.c}}
261do_execsql_test conflict-8.4 {
262 SELECT a,b,c FROM t1 ORDER BY a;
263} {1 2 3 2 3 4 4 5 6}
264
265# Change which column is the PRIMARY KEY
266#
267do_execsql_test conflict-9.1 {
268 DROP TABLE t1;
269 CREATE TABLE t1(
270 a UNIQUE ON CONFLICT REPLACE,
271 b INT PRIMARY KEY ON CONFLICT IGNORE,
272 c UNIQUE ON CONFLICT FAIL
273 ) WITHOUT ROWID;
274 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
275 SELECT a,b,c FROM t1 ORDER BY a;
276} {1 2 3 2 3 4}
277
278# Insert a row that conflicts on column B. The insert should be ignored.
279#
280do_execsql_test conflict-9.2 {
281 INSERT INTO t1(a,b,c) VALUES(3,2,5);
282 SELECT a,b,c FROM t1 ORDER BY a;
283} {1 2 3 2 3 4}
284
285# Insert two rows where the second conflicts on C. The first row show go
286# and and then there should be a constraint error.
287#
288do_test conflict-9.3 {
289 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
290} {1 {UNIQUE constraint failed: t1.c}}
291do_execsql_test conflict-9.4 {
292 SELECT a,b,c FROM t1 ORDER BY a;
293} {1 2 3 2 3 4 4 5 6}
294
295# Change which column is the PRIMARY KEY
296#
297do_execsql_test conflict-10.1 {
298 DROP TABLE t1;
299 CREATE TABLE t1(
300 a UNIQUE ON CONFLICT REPLACE,
301 b UNIQUE ON CONFLICT IGNORE,
302 c INTEGER PRIMARY KEY ON CONFLICT FAIL
303 );
304 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
305 SELECT a,b,c FROM t1 ORDER BY a;
306} {1 2 3 2 3 4}
307
308# Insert a row that conflicts on column B. The insert should be ignored.
309#
310do_execsql_test conflict-10.2 {
311 INSERT INTO t1(a,b,c) VALUES(3,2,5);
312 SELECT a,b,c FROM t1 ORDER BY a;
313} {1 2 3 2 3 4}
314
315# Insert two rows where the second conflicts on C. The first row show go
316# and and then there should be a constraint error.
317#
318do_test conflict-10.3 {
319 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
320} {1 {UNIQUE constraint failed: t1.c}}
321do_execsql_test conflict-10.4 {
322 SELECT a,b,c FROM t1 ORDER BY a;
323} {1 2 3 2 3 4 4 5 6}
324
325# Change which column is the PRIMARY KEY
326#
327do_execsql_test conflict-11.1 {
328 DROP TABLE t1;
329 CREATE TABLE t1(
330 a UNIQUE ON CONFLICT REPLACE,
331 b UNIQUE ON CONFLICT IGNORE,
332 c PRIMARY KEY ON CONFLICT FAIL
333 ) WITHOUT ROWID;
334 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
335 SELECT a,b,c FROM t1 ORDER BY a;
336} {1 2 3 2 3 4}
337
338# Insert a row that conflicts on column B. The insert should be ignored.
339#
340do_execsql_test conflict-11.2 {
341 INSERT INTO t1(a,b,c) VALUES(3,2,5);
342 SELECT a,b,c FROM t1 ORDER BY a;
343} {1 2 3 2 3 4}
344
345# Insert two rows where the second conflicts on C. The first row show go
346# and and then there should be a constraint error.
347#
348do_test conflict-11.3 {
349 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
350} {1 {UNIQUE constraint failed: t1.c}}
351do_execsql_test conflict-11.4 {
352 SELECT a,b,c FROM t1 ORDER BY a;
353} {1 2 3 2 3 4 4 5 6}
354
355
356finish_test