Debian Codegraph @

test/conflict3.test

raw
1 # 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
20 set testdir [file dirname $argv0]
21 source $testdir/tester.tcl
22 set testprefix conflict3
23
24 ifcapable !conflict {
25 finish_test
26 return
27 }
28
29 do_execsql_test 1.1 {
30 CREATE TABLE t1(
31 a INTEGER PRIMARY KEY ON CONFLICT REPLACE,
32 b UNIQUE ON CONFLICT IGNORE,
33 c UNIQUE ON CONFLICT FAIL
34 );
35 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
36 SELECT a,b,c FROM t1 ORDER BY a;
37 } {1 2 3 2 3 4}
38
39 # Insert a row that conflicts on column B. The insert should be ignored.
40 #
41 do_execsql_test 1.2 {
42 INSERT INTO t1(a,b,c) VALUES(3,2,5);
43 SELECT a,b,c FROM t1 ORDER BY a;
44 } {1 2 3 2 3 4}
45
46 # Insert two rows where the second conflicts on C. The first row show go
47 # and and then there should be a constraint error.
48 #
49 do_test 1.3 {
50 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
51 } {1 {UNIQUE constraint failed: t1.c}}
52 do_execsql_test 1.4 {
53 SELECT a,b,c FROM t1 ORDER BY a;
54 } {1 2 3 2 3 4 4 5 6}
55
56 # Replete the tests above, but this time on a table non-INTEGER primary key.
57 #
58 do_execsql_test 2.1 {
59 DROP TABLE t1;
60 CREATE TABLE t1(
61 a INT PRIMARY KEY ON CONFLICT REPLACE,
62 b UNIQUE ON CONFLICT IGNORE,
63 c UNIQUE ON CONFLICT FAIL
64 );
65 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
66 SELECT a,b,c FROM t1 ORDER BY a;
67 } {1 2 3 2 3 4}
68
69 # Insert a row that conflicts on column B. The insert should be ignored.
70 #
71 do_execsql_test 2.2 {
72 INSERT INTO t1(a,b,c) VALUES(3,2,5);
73 SELECT a,b,c FROM t1 ORDER BY a;
74 } {1 2 3 2 3 4}
75
76 # Insert two rows where the second conflicts on C. The first row show go
77 # and and then there should be a constraint error.
78 #
79 do_test 2.3 {
80 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
81 } {1 {UNIQUE constraint failed: t1.c}}
82 do_execsql_test 2.4 {
83 SELECT a,b,c FROM t1 ORDER BY a;
84 } {1 2 3 2 3 4 4 5 6}
85
86 # Replete again on a WITHOUT ROWID table.
87 #
88 do_execsql_test 3.1 {
89 DROP TABLE t1;
90 CREATE TABLE t1(
91 a INT PRIMARY KEY ON CONFLICT REPLACE,
92 b UNIQUE ON CONFLICT IGNORE,
93 c UNIQUE ON CONFLICT FAIL
94 ) WITHOUT ROWID;
95 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
96 SELECT a,b,c FROM t1 ORDER BY a;
97 } {1 2 3 2 3 4}
98
99 # Insert a row that conflicts on column B. The insert should be ignored.
100 #
101 do_execsql_test 3.2 {
102 INSERT INTO t1(a,b,c) VALUES(3,2,5);
103 SELECT a,b,c FROM t1 ORDER BY a;
104 } {1 2 3 2 3 4}
105
106 # Insert two rows where the second conflicts on C. The first row show go
107 # and and then there should be a constraint error.
108 #
109 do_test 3.3 {
110 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
111 } {1 {UNIQUE constraint failed: t1.c}}
112 do_execsql_test 3.4 {
113 SELECT a,b,c FROM t1 ORDER BY a;
114 } {1 2 3 2 3 4 4 5 6}
115
116 # Arrange the table rows in a different order and repeat.
117 #
118 do_execsql_test 4.1 {
119 DROP TABLE t1;
120 CREATE TABLE t1(
121 b UNIQUE ON CONFLICT IGNORE,
122 c UNIQUE ON CONFLICT FAIL,
123 a INT PRIMARY KEY ON CONFLICT REPLACE
124 ) WITHOUT ROWID;
125 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
126 SELECT a,b,c FROM t1 ORDER BY a;
127 } {1 2 3 2 3 4}
128
129 # Insert a row that conflicts on column B. The insert should be ignored.
130 #
131 do_execsql_test 4.2 {
132 INSERT INTO t1(a,b,c) VALUES(3,2,5);
133 SELECT a,b,c FROM t1 ORDER BY a;
134 } {1 2 3 2 3 4}
135
136 # Insert two rows where the second conflicts on C. The first row show go
137 # and and then there should be a constraint error.
138 #
139 do_test 4.3 {
140 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
141 } {1 {UNIQUE constraint failed: t1.c}}
142 do_execsql_test 4.4 {
143 SELECT a,b,c FROM t1 ORDER BY a;
144 } {1 2 3 2 3 4 4 5 6}
145
146 # Arrange the table rows in a different order and repeat.
147 #
148 do_execsql_test 5.1 {
149 DROP TABLE t1;
150 CREATE TABLE t1(
151 b UNIQUE ON CONFLICT IGNORE,
152 a INT PRIMARY KEY ON CONFLICT REPLACE,
153 c UNIQUE ON CONFLICT FAIL
154 );
155 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
156 SELECT a,b,c FROM t1 ORDER BY a;
157 } {1 2 3 2 3 4}
158
159 # Insert a row that conflicts on column B. The insert should be ignored.
160 #
161 do_execsql_test 5.2 {
162 INSERT INTO t1(a,b,c) VALUES(3,2,5);
163 SELECT a,b,c FROM t1 ORDER BY a;
164 } {1 2 3 2 3 4}
165
166 # Insert two rows where the second conflicts on C. The first row show go
167 # and and then there should be a constraint error.
168 #
169 do_test 5.3 {
170 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
171 } {1 {UNIQUE constraint failed: t1.c}}
172 do_execsql_test 5.4 {
173 SELECT a,b,c FROM t1 ORDER BY a;
174 } {1 2 3 2 3 4 4 5 6}
175
176 # Arrange the table rows in a different order and repeat.
177 #
178 do_execsql_test 6.1 {
179 DROP TABLE t1;
180 CREATE TABLE t1(
181 c UNIQUE ON CONFLICT FAIL,
182 a INT PRIMARY KEY ON CONFLICT REPLACE,
183 b UNIQUE ON CONFLICT IGNORE
184 );
185 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
186 SELECT a,b,c FROM t1 ORDER BY a;
187 } {1 2 3 2 3 4}
188
189 # Insert a row that conflicts on column B. The insert should be ignored.
190 #
191 do_execsql_test 6.2 {
192 INSERT INTO t1(a,b,c) VALUES(3,2,5);
193 SELECT a,b,c FROM t1 ORDER BY a;
194 } {1 2 3 2 3 4}
195
196 # Insert two rows where the second conflicts on C. The first row show go
197 # and and then there should be a constraint error.
198 #
199 do_test 6.3 {
200 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
201 } {1 {UNIQUE constraint failed: t1.c}}
202 do_execsql_test 6.4 {
203 SELECT a,b,c FROM t1 ORDER BY a;
204 } {1 2 3 2 3 4 4 5 6}
205
206 # Change which column is the PRIMARY KEY
207 #
208 do_execsql_test 7.1 {
209 DROP TABLE t1;
210 CREATE TABLE t1(
211 a UNIQUE ON CONFLICT REPLACE,
212 b INTEGER PRIMARY KEY ON CONFLICT IGNORE,
213 c UNIQUE ON CONFLICT FAIL
214 );
215 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
216 SELECT a,b,c FROM t1 ORDER BY a;
217 } {1 2 3 2 3 4}
218
219 # Insert a row that conflicts on column B. The insert should be ignored.
220 #
221 do_execsql_test 7.2 {
222 INSERT INTO t1(a,b,c) VALUES(3,2,5);
223 SELECT a,b,c FROM t1 ORDER BY a;
224 } {1 2 3 2 3 4}
225
226 # Insert two rows where the second conflicts on C. The first row show go
227 # and and then there should be a constraint error.
228 #
229 do_test 7.3 {
230 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
231 } {1 {UNIQUE constraint failed: t1.c}}
232 do_execsql_test 7.4 {
233 SELECT a,b,c FROM t1 ORDER BY a;
234 } {1 2 3 2 3 4 4 5 6}
235
236 # Change which column is the PRIMARY KEY
237 #
238 do_execsql_test 8.1 {
239 DROP TABLE t1;
240 CREATE TABLE t1(
241 a UNIQUE ON CONFLICT REPLACE,
242 b INT PRIMARY KEY ON CONFLICT IGNORE,
243 c UNIQUE ON CONFLICT FAIL
244 );
245 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
246 SELECT a,b,c FROM t1 ORDER BY a;
247 } {1 2 3 2 3 4}
248
249 # Insert a row that conflicts on column B. The insert should be ignored.
250 #
251 do_execsql_test 8.2 {
252 INSERT INTO t1(a,b,c) VALUES(3,2,5);
253 SELECT a,b,c FROM t1 ORDER BY a;
254 } {1 2 3 2 3 4}
255
256 # Insert two rows where the second conflicts on C. The first row show go
257 # and and then there should be a constraint error.
258 #
259 do_test 8.3 {
260 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
261 } {1 {UNIQUE constraint failed: t1.c}}
262 do_execsql_test 8.4 {
263 SELECT a,b,c FROM t1 ORDER BY a;
264 } {1 2 3 2 3 4 4 5 6}
265
266 # Change which column is the PRIMARY KEY
267 #
268 do_execsql_test 9.1 {
269 DROP TABLE t1;
270 CREATE TABLE t1(
271 a UNIQUE ON CONFLICT REPLACE,
272 b INT PRIMARY KEY ON CONFLICT IGNORE,
273 c UNIQUE ON CONFLICT FAIL
274 ) WITHOUT ROWID;
275 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
276 SELECT a,b,c FROM t1 ORDER BY a;
277 } {1 2 3 2 3 4}
278
279 # Insert a row that conflicts on column B. The insert should be ignored.
280 #
281 do_execsql_test 9.2 {
282 INSERT INTO t1(a,b,c) VALUES(3,2,5);
283 SELECT a,b,c FROM t1 ORDER BY a;
284 } {1 2 3 2 3 4}
285
286 # Insert two rows where the second conflicts on C. The first row show go
287 # and and then there should be a constraint error.
288 #
289 do_test 9.3 {
290 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
291 } {1 {UNIQUE constraint failed: t1.c}}
292 do_execsql_test 9.4 {
293 SELECT a,b,c FROM t1 ORDER BY a;
294 } {1 2 3 2 3 4 4 5 6}
295
296 # Change which column is the PRIMARY KEY
297 #
298 do_execsql_test 10.1 {
299 DROP TABLE t1;
300 CREATE TABLE t1(
301 a UNIQUE ON CONFLICT REPLACE,
302 b UNIQUE ON CONFLICT IGNORE,
303 c INTEGER PRIMARY KEY ON CONFLICT FAIL
304 );
305 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
306 SELECT a,b,c FROM t1 ORDER BY a;
307 } {1 2 3 2 3 4}
308
309 # Insert a row that conflicts on column B. The insert should be ignored.
310 #
311 do_execsql_test 10.2 {
312 INSERT INTO t1(a,b,c) VALUES(3,2,5);
313 SELECT a,b,c FROM t1 ORDER BY a;
314 } {1 2 3 2 3 4}
315
316 # Insert two rows where the second conflicts on C. The first row show go
317 # and and then there should be a constraint error.
318 #
319 do_test 10.3 {
320 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
321 } {1 {UNIQUE constraint failed: t1.c}}
322 do_execsql_test 10.4 {
323 SELECT a,b,c FROM t1 ORDER BY a;
324 } {1 2 3 2 3 4 4 5 6}
325
326 # Change which column is the PRIMARY KEY
327 #
328 do_execsql_test 11.1 {
329 DROP TABLE t1;
330 CREATE TABLE t1(
331 a UNIQUE ON CONFLICT REPLACE,
332 b UNIQUE ON CONFLICT IGNORE,
333 c PRIMARY KEY ON CONFLICT FAIL
334 ) WITHOUT ROWID;
335 INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4);
336 SELECT a,b,c FROM t1 ORDER BY a;
337 } {1 2 3 2 3 4}
338
339 # Insert a row that conflicts on column B. The insert should be ignored.
340 #
341 do_execsql_test 11.2 {
342 INSERT INTO t1(a,b,c) VALUES(3,2,5);
343 SELECT a,b,c FROM t1 ORDER BY a;
344 } {1 2 3 2 3 4}
345
346 # Insert two rows where the second conflicts on C. The first row show go
347 # and and then there should be a constraint error.
348 #
349 do_test 11.3 {
350 catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);}
351 } {1 {UNIQUE constraint failed: t1.c}}
352 do_execsql_test 11.4 {
353 SELECT a,b,c FROM t1 ORDER BY a;
354 } {1 2 3 2 3 4 4 5 6}
355
356 # Check that ticket [f68dc596c4] has been fixed.
357 #
358 do_execsql_test 12.1 {
359 CREATE TABLE t2(a INTEGER PRIMARY KEY, b TEXT);
360 INSERT INTO t2 VALUES(111, '111');
361 }
362 do_execsql_test 12.2 {
363 REPLACE INTO t2 VALUES(NULL, '112'), (111, '111B');
364 }
365 do_execsql_test 12.3 {
366 SELECT * FROM t2;
367 } {111 111B 112 112}
368
369 #-------------------------------------------------------------------------
370 ifcapable trigger {
371 reset_db
372 do_execsql_test 13.1.0 {
373 PRAGMA recursive_triggers = true;
374 CREATE TABLE t0 (c0 UNIQUE, c1 UNIQUE);
375 CREATE TRIGGER tr0 AFTER DELETE ON t0 BEGIN
376 DELETE FROM t0;
377 END;
378
379 INSERT INTO t0 VALUES(1, NULL);
380 INSERT INTO t0 VALUES(0, NULL);
381 }
382
383 do_catchsql_test 13.1.1 {
384 UPDATE OR REPLACE t0 SET c1 = 1;
385 } {1 {constraint failed}}
386
387 integrity_check 13.1.2
388
389 do_execsql_test 13.1.3 {
390 SELECT * FROM t0
391 } {1 {} 0 {}}
392
393 do_execsql_test 13.2.0 {
394 CREATE TABLE t2 (a PRIMARY KEY, b UNIQUE, c UNIQUE) WITHOUT ROWID;
395 CREATE TRIGGER tr3 AFTER DELETE ON t2 BEGIN
396 DELETE FROM t2;
397 END;
398
399 INSERT INTO t2 VALUES(1, 1, 1);
400 INSERT INTO t2 VALUES(2, 2, 2);
401 }
402
403 do_catchsql_test 13.2.1 {
404 UPDATE OR REPLACE t2 SET c = 0;
405 } {1 {constraint failed}}
406
407 integrity_check 13.2.2
408
409 do_execsql_test 13.2.3 {
410 SELECT * FROM t2
411 } {1 1 1 2 2 2}
412
413 do_execsql_test 13.3.0 {
414 CREATE TABLE t1(a, b);
415 CREATE TABLE log(x);
416 CREATE INDEX i1 ON t1(a);
417 INSERT INTO t1 VALUES(1, 2);
418
419 CREATE TRIGGER tb BEFORE UPDATE ON t1 BEGIN
420 DELETE FROM t1;
421 END;
422 CREATE TRIGGER ta AFTER UPDATE ON t1 BEGIN
423 INSERT INTO log VALUES('fired!');
424 END;
425
426 UPDATE t1 SET b=3;
427 }
428
429 do_execsql_test 13.3.1 {
430 SELECT * FROM t1;
431 } {}
432 do_execsql_test 13.3.2 {
433 SELECT * FROM log;
434 } {}
435 }
436
437 finish_test