|
1 # 2002 January 29 |
|
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 # $Id: conflict.test,v 1.31 2008/01/21 16:22:46 drh Exp $ |
|
17 |
|
18 set testdir [file dirname $argv0] |
|
19 source $testdir/tester.tcl |
|
20 |
|
21 ifcapable !conflict { |
|
22 finish_test |
|
23 return |
|
24 } |
|
25 |
|
26 # Create tables for the first group of tests. |
|
27 # |
|
28 do_test conflict-1.0 { |
|
29 execsql { |
|
30 CREATE TABLE t1(a, b, c, UNIQUE(a,b)); |
|
31 CREATE TABLE t2(x); |
|
32 SELECT c FROM t1 ORDER BY c; |
|
33 } |
|
34 } {} |
|
35 |
|
36 # Six columns of configuration data as follows: |
|
37 # |
|
38 # i The reference number of the test |
|
39 # cmd An INSERT or REPLACE command to execute against table t1 |
|
40 # t0 True if there is an error from $cmd |
|
41 # t1 Content of "c" column of t1 assuming no error in $cmd |
|
42 # t2 Content of "x" column of t2 |
|
43 # t3 Number of temporary files created by this test |
|
44 # |
|
45 foreach {i cmd t0 t1 t2 t3} { |
|
46 1 INSERT 1 {} 1 0 |
|
47 2 {INSERT OR IGNORE} 0 3 1 0 |
|
48 3 {INSERT OR REPLACE} 0 4 1 0 |
|
49 4 REPLACE 0 4 1 0 |
|
50 5 {INSERT OR FAIL} 1 {} 1 0 |
|
51 6 {INSERT OR ABORT} 1 {} 1 0 |
|
52 7 {INSERT OR ROLLBACK} 1 {} {} 0 |
|
53 } { |
|
54 do_test conflict-1.$i { |
|
55 set ::sqlite_opentemp_count 0 |
|
56 set r0 [catch {execsql [subst { |
|
57 DELETE FROM t1; |
|
58 DELETE FROM t2; |
|
59 INSERT INTO t1 VALUES(1,2,3); |
|
60 BEGIN; |
|
61 INSERT INTO t2 VALUES(1); |
|
62 $cmd INTO t1 VALUES(1,2,4); |
|
63 }]} r1] |
|
64 catch {execsql {COMMIT}} |
|
65 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} |
|
66 set r2 [execsql {SELECT x FROM t2}] |
|
67 set r3 $::sqlite_opentemp_count |
|
68 list $r0 $r1 $r2 $r3 |
|
69 } [list $t0 $t1 $t2 $t3] |
|
70 } |
|
71 |
|
72 # Create tables for the first group of tests. |
|
73 # |
|
74 do_test conflict-2.0 { |
|
75 execsql { |
|
76 DROP TABLE t1; |
|
77 DROP TABLE t2; |
|
78 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b)); |
|
79 CREATE TABLE t2(x); |
|
80 SELECT c FROM t1 ORDER BY c; |
|
81 } |
|
82 } {} |
|
83 |
|
84 # Six columns of configuration data as follows: |
|
85 # |
|
86 # i The reference number of the test |
|
87 # cmd An INSERT or REPLACE command to execute against table t1 |
|
88 # t0 True if there is an error from $cmd |
|
89 # t1 Content of "c" column of t1 assuming no error in $cmd |
|
90 # t2 Content of "x" column of t2 |
|
91 # |
|
92 foreach {i cmd t0 t1 t2} { |
|
93 1 INSERT 1 {} 1 |
|
94 2 {INSERT OR IGNORE} 0 3 1 |
|
95 3 {INSERT OR REPLACE} 0 4 1 |
|
96 4 REPLACE 0 4 1 |
|
97 5 {INSERT OR FAIL} 1 {} 1 |
|
98 6 {INSERT OR ABORT} 1 {} 1 |
|
99 7 {INSERT OR ROLLBACK} 1 {} {} |
|
100 } { |
|
101 do_test conflict-2.$i { |
|
102 set r0 [catch {execsql [subst { |
|
103 DELETE FROM t1; |
|
104 DELETE FROM t2; |
|
105 INSERT INTO t1 VALUES(1,2,3); |
|
106 BEGIN; |
|
107 INSERT INTO t2 VALUES(1); |
|
108 $cmd INTO t1 VALUES(1,2,4); |
|
109 }]} r1] |
|
110 catch {execsql {COMMIT}} |
|
111 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} |
|
112 set r2 [execsql {SELECT x FROM t2}] |
|
113 list $r0 $r1 $r2 |
|
114 } [list $t0 $t1 $t2] |
|
115 } |
|
116 |
|
117 # Create tables for the first group of tests. |
|
118 # |
|
119 do_test conflict-3.0 { |
|
120 execsql { |
|
121 DROP TABLE t1; |
|
122 DROP TABLE t2; |
|
123 CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b)); |
|
124 CREATE TABLE t2(x); |
|
125 SELECT c FROM t1 ORDER BY c; |
|
126 } |
|
127 } {} |
|
128 |
|
129 # Six columns of configuration data as follows: |
|
130 # |
|
131 # i The reference number of the test |
|
132 # cmd An INSERT or REPLACE command to execute against table t1 |
|
133 # t0 True if there is an error from $cmd |
|
134 # t1 Content of "c" column of t1 assuming no error in $cmd |
|
135 # t2 Content of "x" column of t2 |
|
136 # |
|
137 foreach {i cmd t0 t1 t2} { |
|
138 1 INSERT 1 {} 1 |
|
139 2 {INSERT OR IGNORE} 0 3 1 |
|
140 3 {INSERT OR REPLACE} 0 4 1 |
|
141 4 REPLACE 0 4 1 |
|
142 5 {INSERT OR FAIL} 1 {} 1 |
|
143 6 {INSERT OR ABORT} 1 {} 1 |
|
144 7 {INSERT OR ROLLBACK} 1 {} {} |
|
145 } { |
|
146 do_test conflict-3.$i { |
|
147 set r0 [catch {execsql [subst { |
|
148 DELETE FROM t1; |
|
149 DELETE FROM t2; |
|
150 INSERT INTO t1 VALUES(1,2,3); |
|
151 BEGIN; |
|
152 INSERT INTO t2 VALUES(1); |
|
153 $cmd INTO t1 VALUES(1,2,4); |
|
154 }]} r1] |
|
155 catch {execsql {COMMIT}} |
|
156 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} |
|
157 set r2 [execsql {SELECT x FROM t2}] |
|
158 list $r0 $r1 $r2 |
|
159 } [list $t0 $t1 $t2] |
|
160 } |
|
161 |
|
162 do_test conflict-4.0 { |
|
163 execsql { |
|
164 DROP TABLE t2; |
|
165 CREATE TABLE t2(x); |
|
166 SELECT x FROM t2; |
|
167 } |
|
168 } {} |
|
169 |
|
170 # Six columns of configuration data as follows: |
|
171 # |
|
172 # i The reference number of the test |
|
173 # conf1 The conflict resolution algorithm on the UNIQUE constraint |
|
174 # cmd An INSERT or REPLACE command to execute against table t1 |
|
175 # t0 True if there is an error from $cmd |
|
176 # t1 Content of "c" column of t1 assuming no error in $cmd |
|
177 # t2 Content of "x" column of t2 |
|
178 # |
|
179 foreach {i conf1 cmd t0 t1 t2} { |
|
180 1 {} INSERT 1 {} 1 |
|
181 2 REPLACE INSERT 0 4 1 |
|
182 3 IGNORE INSERT 0 3 1 |
|
183 4 FAIL INSERT 1 {} 1 |
|
184 5 ABORT INSERT 1 {} 1 |
|
185 6 ROLLBACK INSERT 1 {} {} |
|
186 7 REPLACE {INSERT OR IGNORE} 0 3 1 |
|
187 8 IGNORE {INSERT OR REPLACE} 0 4 1 |
|
188 9 FAIL {INSERT OR IGNORE} 0 3 1 |
|
189 10 ABORT {INSERT OR REPLACE} 0 4 1 |
|
190 11 ROLLBACK {INSERT OR IGNORE } 0 3 1 |
|
191 } { |
|
192 do_test conflict-4.$i { |
|
193 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} |
|
194 set r0 [catch {execsql [subst { |
|
195 DROP TABLE t1; |
|
196 CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1); |
|
197 DELETE FROM t2; |
|
198 INSERT INTO t1 VALUES(1,2,3); |
|
199 BEGIN; |
|
200 INSERT INTO t2 VALUES(1); |
|
201 $cmd INTO t1 VALUES(1,2,4); |
|
202 }]} r1] |
|
203 catch {execsql {COMMIT}} |
|
204 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} |
|
205 set r2 [execsql {SELECT x FROM t2}] |
|
206 list $r0 $r1 $r2 |
|
207 } [list $t0 $t1 $t2] |
|
208 } |
|
209 |
|
210 do_test conflict-5.0 { |
|
211 execsql { |
|
212 DROP TABLE t2; |
|
213 CREATE TABLE t2(x); |
|
214 SELECT x FROM t2; |
|
215 } |
|
216 } {} |
|
217 |
|
218 # Six columns of configuration data as follows: |
|
219 # |
|
220 # i The reference number of the test |
|
221 # conf1 The conflict resolution algorithm on the NOT NULL constraint |
|
222 # cmd An INSERT or REPLACE command to execute against table t1 |
|
223 # t0 True if there is an error from $cmd |
|
224 # t1 Content of "c" column of t1 assuming no error in $cmd |
|
225 # t2 Content of "x" column of t2 |
|
226 # |
|
227 foreach {i conf1 cmd t0 t1 t2} { |
|
228 1 {} INSERT 1 {} 1 |
|
229 2 REPLACE INSERT 0 5 1 |
|
230 3 IGNORE INSERT 0 {} 1 |
|
231 4 FAIL INSERT 1 {} 1 |
|
232 5 ABORT INSERT 1 {} 1 |
|
233 6 ROLLBACK INSERT 1 {} {} |
|
234 7 REPLACE {INSERT OR IGNORE} 0 {} 1 |
|
235 8 IGNORE {INSERT OR REPLACE} 0 5 1 |
|
236 9 FAIL {INSERT OR IGNORE} 0 {} 1 |
|
237 10 ABORT {INSERT OR REPLACE} 0 5 1 |
|
238 11 ROLLBACK {INSERT OR IGNORE} 0 {} 1 |
|
239 12 {} {INSERT OR IGNORE} 0 {} 1 |
|
240 13 {} {INSERT OR REPLACE} 0 5 1 |
|
241 14 {} {INSERT OR FAIL} 1 {} 1 |
|
242 15 {} {INSERT OR ABORT} 1 {} 1 |
|
243 16 {} {INSERT OR ROLLBACK} 1 {} {} |
|
244 } { |
|
245 if {$t0} {set t1 {t1.c may not be NULL}} |
|
246 do_test conflict-5.$i { |
|
247 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} |
|
248 set r0 [catch {execsql [subst { |
|
249 DROP TABLE t1; |
|
250 CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5); |
|
251 DELETE FROM t2; |
|
252 BEGIN; |
|
253 INSERT INTO t2 VALUES(1); |
|
254 $cmd INTO t1 VALUES(1,2,NULL); |
|
255 }]} r1] |
|
256 catch {execsql {COMMIT}} |
|
257 if {!$r0} {set r1 [execsql {SELECT c FROM t1}]} |
|
258 set r2 [execsql {SELECT x FROM t2}] |
|
259 list $r0 $r1 $r2 |
|
260 } [list $t0 $t1 $t2] |
|
261 } |
|
262 |
|
263 do_test conflict-6.0 { |
|
264 execsql { |
|
265 DROP TABLE t2; |
|
266 CREATE TABLE t2(a,b,c); |
|
267 INSERT INTO t2 VALUES(1,2,1); |
|
268 INSERT INTO t2 VALUES(2,3,2); |
|
269 INSERT INTO t2 VALUES(3,4,1); |
|
270 INSERT INTO t2 VALUES(4,5,4); |
|
271 SELECT c FROM t2 ORDER BY b; |
|
272 CREATE TABLE t3(x); |
|
273 INSERT INTO t3 VALUES(1); |
|
274 } |
|
275 } {1 2 1 4} |
|
276 |
|
277 # Six columns of configuration data as follows: |
|
278 # |
|
279 # i The reference number of the test |
|
280 # conf1 The conflict resolution algorithm on the UNIQUE constraint |
|
281 # cmd An UPDATE command to execute against table t1 |
|
282 # t0 True if there is an error from $cmd |
|
283 # t1 Content of "b" column of t1 assuming no error in $cmd |
|
284 # t2 Content of "x" column of t3 |
|
285 # t3 Number of temporary files for tables |
|
286 # t4 Number of temporary files for statement journals |
|
287 # |
|
288 # Update: Since temporary table files are now opened lazily, and none |
|
289 # of the following tests use large quantities of data, t3 is always 0. |
|
290 # |
|
291 foreach {i conf1 cmd t0 t1 t2 t3 t4} { |
|
292 1 {} UPDATE 1 {6 7 8 9} 1 0 1 |
|
293 2 REPLACE UPDATE 0 {7 6 9} 1 0 0 |
|
294 3 IGNORE UPDATE 0 {6 7 3 9} 1 0 0 |
|
295 4 FAIL UPDATE 1 {6 7 3 4} 1 0 0 |
|
296 5 ABORT UPDATE 1 {1 2 3 4} 1 0 1 |
|
297 6 ROLLBACK UPDATE 1 {1 2 3 4} 0 0 0 |
|
298 7 REPLACE {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 |
|
299 8 IGNORE {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0 |
|
300 9 FAIL {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 |
|
301 10 ABORT {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0 |
|
302 11 ROLLBACK {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 |
|
303 12 {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 |
|
304 13 {} {UPDATE OR REPLACE} 0 {7 6 9} 1 0 0 |
|
305 14 {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 0 0 |
|
306 15 {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 0 1 |
|
307 16 {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 0 0 |
|
308 } { |
|
309 if {$t0} {set t1 {column a is not unique}} |
|
310 if {[info exists TEMP_STORE] && $TEMP_STORE>=2} { |
|
311 set t3 $t4 |
|
312 } else { |
|
313 set t3 [expr {$t3+$t4}] |
|
314 } |
|
315 do_test conflict-6.$i { |
|
316 db close |
|
317 sqlite3 db test.db |
|
318 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} |
|
319 execsql {pragma temp_store=file} |
|
320 set ::sqlite_opentemp_count 0 |
|
321 set r0 [catch {execsql [subst { |
|
322 DROP TABLE t1; |
|
323 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); |
|
324 INSERT INTO t1 SELECT * FROM t2; |
|
325 UPDATE t3 SET x=0; |
|
326 BEGIN; |
|
327 $cmd t3 SET x=1; |
|
328 $cmd t1 SET b=b*2; |
|
329 $cmd t1 SET a=c+5; |
|
330 }]} r1] |
|
331 catch {execsql {COMMIT}} |
|
332 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} |
|
333 set r2 [execsql {SELECT x FROM t3}] |
|
334 list $r0 $r1 $r2 $::sqlite_opentemp_count |
|
335 } [list $t0 $t1 $t2 $t3] |
|
336 } |
|
337 |
|
338 # Test to make sure a lot of IGNOREs don't cause a stack overflow |
|
339 # |
|
340 do_test conflict-7.1 { |
|
341 execsql { |
|
342 DROP TABLE t1; |
|
343 DROP TABLE t2; |
|
344 DROP TABLE t3; |
|
345 CREATE TABLE t1(a unique, b); |
|
346 } |
|
347 for {set i 1} {$i<=50} {incr i} { |
|
348 execsql "INSERT into t1 values($i,[expr {$i+1}]);" |
|
349 } |
|
350 execsql { |
|
351 SELECT count(*), min(a), max(b) FROM t1; |
|
352 } |
|
353 } {50 1 51} |
|
354 do_test conflict-7.2 { |
|
355 execsql { |
|
356 PRAGMA count_changes=on; |
|
357 UPDATE OR IGNORE t1 SET a=1000; |
|
358 } |
|
359 } {1} |
|
360 do_test conflict-7.2.1 { |
|
361 db changes |
|
362 } {1} |
|
363 do_test conflict-7.3 { |
|
364 execsql { |
|
365 SELECT b FROM t1 WHERE a=1000; |
|
366 } |
|
367 } {2} |
|
368 do_test conflict-7.4 { |
|
369 execsql { |
|
370 SELECT count(*) FROM t1; |
|
371 } |
|
372 } {50} |
|
373 do_test conflict-7.5 { |
|
374 execsql { |
|
375 PRAGMA count_changes=on; |
|
376 UPDATE OR REPLACE t1 SET a=1001; |
|
377 } |
|
378 } {50} |
|
379 do_test conflict-7.5.1 { |
|
380 db changes |
|
381 } {50} |
|
382 do_test conflict-7.6 { |
|
383 execsql { |
|
384 SELECT b FROM t1 WHERE a=1001; |
|
385 } |
|
386 } {51} |
|
387 do_test conflict-7.7 { |
|
388 execsql { |
|
389 SELECT count(*) FROM t1; |
|
390 } |
|
391 } {1} |
|
392 |
|
393 # Update for version 3: A SELECT statement no longer resets the change |
|
394 # counter (Test result changes from 0 to 50). |
|
395 do_test conflict-7.7.1 { |
|
396 db changes |
|
397 } {50} |
|
398 |
|
399 # Make sure the row count is right for rows that are ignored on |
|
400 # an insert. |
|
401 # |
|
402 do_test conflict-8.1 { |
|
403 execsql { |
|
404 DELETE FROM t1; |
|
405 INSERT INTO t1 VALUES(1,2); |
|
406 } |
|
407 execsql { |
|
408 INSERT OR IGNORE INTO t1 VALUES(2,3); |
|
409 } |
|
410 } {1} |
|
411 do_test conflict-8.1.1 { |
|
412 db changes |
|
413 } {1} |
|
414 do_test conflict-8.2 { |
|
415 execsql { |
|
416 INSERT OR IGNORE INTO t1 VALUES(2,4); |
|
417 } |
|
418 } {0} |
|
419 do_test conflict-8.2.1 { |
|
420 db changes |
|
421 } {0} |
|
422 do_test conflict-8.3 { |
|
423 execsql { |
|
424 INSERT OR REPLACE INTO t1 VALUES(2,4); |
|
425 } |
|
426 } {1} |
|
427 do_test conflict-8.3.1 { |
|
428 db changes |
|
429 } {1} |
|
430 do_test conflict-8.4 { |
|
431 execsql { |
|
432 INSERT OR IGNORE INTO t1 SELECT * FROM t1; |
|
433 } |
|
434 } {0} |
|
435 do_test conflict-8.4.1 { |
|
436 db changes |
|
437 } {0} |
|
438 do_test conflict-8.5 { |
|
439 execsql { |
|
440 INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1; |
|
441 } |
|
442 } {2} |
|
443 do_test conflict-8.5.1 { |
|
444 db changes |
|
445 } {2} |
|
446 do_test conflict-8.6 { |
|
447 execsql { |
|
448 INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1; |
|
449 } |
|
450 } {3} |
|
451 do_test conflict-8.6.1 { |
|
452 db changes |
|
453 } {3} |
|
454 |
|
455 integrity_check conflict-8.99 |
|
456 |
|
457 do_test conflict-9.1 { |
|
458 execsql { |
|
459 PRAGMA count_changes=0; |
|
460 CREATE TABLE t2( |
|
461 a INTEGER UNIQUE ON CONFLICT IGNORE, |
|
462 b INTEGER UNIQUE ON CONFLICT FAIL, |
|
463 c INTEGER UNIQUE ON CONFLICT REPLACE, |
|
464 d INTEGER UNIQUE ON CONFLICT ABORT, |
|
465 e INTEGER UNIQUE ON CONFLICT ROLLBACK |
|
466 ); |
|
467 CREATE TABLE t3(x); |
|
468 INSERT INTO t3 VALUES(1); |
|
469 SELECT * FROM t3; |
|
470 } |
|
471 } {1} |
|
472 do_test conflict-9.2 { |
|
473 catchsql { |
|
474 INSERT INTO t2 VALUES(1,1,1,1,1); |
|
475 INSERT INTO t2 VALUES(2,2,2,2,2); |
|
476 SELECT * FROM t2; |
|
477 } |
|
478 } {0 {1 1 1 1 1 2 2 2 2 2}} |
|
479 do_test conflict-9.3 { |
|
480 catchsql { |
|
481 INSERT INTO t2 VALUES(1,3,3,3,3); |
|
482 SELECT * FROM t2; |
|
483 } |
|
484 } {0 {1 1 1 1 1 2 2 2 2 2}} |
|
485 do_test conflict-9.4 { |
|
486 catchsql { |
|
487 UPDATE t2 SET a=a+1 WHERE a=1; |
|
488 SELECT * FROM t2; |
|
489 } |
|
490 } {0 {1 1 1 1 1 2 2 2 2 2}} |
|
491 do_test conflict-9.5 { |
|
492 catchsql { |
|
493 INSERT INTO t2 VALUES(3,1,3,3,3); |
|
494 SELECT * FROM t2; |
|
495 } |
|
496 } {1 {column b is not unique}} |
|
497 do_test conflict-9.6 { |
|
498 catchsql { |
|
499 UPDATE t2 SET b=b+1 WHERE b=1; |
|
500 SELECT * FROM t2; |
|
501 } |
|
502 } {1 {column b is not unique}} |
|
503 do_test conflict-9.7 { |
|
504 catchsql { |
|
505 BEGIN; |
|
506 UPDATE t3 SET x=x+1; |
|
507 INSERT INTO t2 VALUES(3,1,3,3,3); |
|
508 SELECT * FROM t2; |
|
509 } |
|
510 } {1 {column b is not unique}} |
|
511 do_test conflict-9.8 { |
|
512 execsql {COMMIT} |
|
513 execsql {SELECT * FROM t3} |
|
514 } {2} |
|
515 do_test conflict-9.9 { |
|
516 catchsql { |
|
517 BEGIN; |
|
518 UPDATE t3 SET x=x+1; |
|
519 UPDATE t2 SET b=b+1 WHERE b=1; |
|
520 SELECT * FROM t2; |
|
521 } |
|
522 } {1 {column b is not unique}} |
|
523 do_test conflict-9.10 { |
|
524 execsql {COMMIT} |
|
525 execsql {SELECT * FROM t3} |
|
526 } {3} |
|
527 do_test conflict-9.11 { |
|
528 catchsql { |
|
529 INSERT INTO t2 VALUES(3,3,3,1,3); |
|
530 SELECT * FROM t2; |
|
531 } |
|
532 } {1 {column d is not unique}} |
|
533 do_test conflict-9.12 { |
|
534 catchsql { |
|
535 UPDATE t2 SET d=d+1 WHERE d=1; |
|
536 SELECT * FROM t2; |
|
537 } |
|
538 } {1 {column d is not unique}} |
|
539 do_test conflict-9.13 { |
|
540 catchsql { |
|
541 BEGIN; |
|
542 UPDATE t3 SET x=x+1; |
|
543 INSERT INTO t2 VALUES(3,3,3,1,3); |
|
544 SELECT * FROM t2; |
|
545 } |
|
546 } {1 {column d is not unique}} |
|
547 do_test conflict-9.14 { |
|
548 execsql {COMMIT} |
|
549 execsql {SELECT * FROM t3} |
|
550 } {4} |
|
551 do_test conflict-9.15 { |
|
552 catchsql { |
|
553 BEGIN; |
|
554 UPDATE t3 SET x=x+1; |
|
555 UPDATE t2 SET d=d+1 WHERE d=1; |
|
556 SELECT * FROM t2; |
|
557 } |
|
558 } {1 {column d is not unique}} |
|
559 do_test conflict-9.16 { |
|
560 execsql {COMMIT} |
|
561 execsql {SELECT * FROM t3} |
|
562 } {5} |
|
563 do_test conflict-9.17 { |
|
564 catchsql { |
|
565 INSERT INTO t2 VALUES(3,3,3,3,1); |
|
566 SELECT * FROM t2; |
|
567 } |
|
568 } {1 {column e is not unique}} |
|
569 do_test conflict-9.18 { |
|
570 catchsql { |
|
571 UPDATE t2 SET e=e+1 WHERE e=1; |
|
572 SELECT * FROM t2; |
|
573 } |
|
574 } {1 {column e is not unique}} |
|
575 do_test conflict-9.19 { |
|
576 catchsql { |
|
577 BEGIN; |
|
578 UPDATE t3 SET x=x+1; |
|
579 INSERT INTO t2 VALUES(3,3,3,3,1); |
|
580 SELECT * FROM t2; |
|
581 } |
|
582 } {1 {column e is not unique}} |
|
583 do_test conflict-9.20 { |
|
584 catch {execsql {COMMIT}} |
|
585 execsql {SELECT * FROM t3} |
|
586 } {5} |
|
587 do_test conflict-9.21 { |
|
588 catchsql { |
|
589 BEGIN; |
|
590 UPDATE t3 SET x=x+1; |
|
591 UPDATE t2 SET e=e+1 WHERE e=1; |
|
592 SELECT * FROM t2; |
|
593 } |
|
594 } {1 {column e is not unique}} |
|
595 do_test conflict-9.22 { |
|
596 catch {execsql {COMMIT}} |
|
597 execsql {SELECT * FROM t3} |
|
598 } {5} |
|
599 do_test conflict-9.23 { |
|
600 catchsql { |
|
601 INSERT INTO t2 VALUES(3,3,1,3,3); |
|
602 SELECT * FROM t2; |
|
603 } |
|
604 } {0 {2 2 2 2 2 3 3 1 3 3}} |
|
605 do_test conflict-9.24 { |
|
606 catchsql { |
|
607 UPDATE t2 SET c=c-1 WHERE c=2; |
|
608 SELECT * FROM t2; |
|
609 } |
|
610 } {0 {2 2 1 2 2}} |
|
611 do_test conflict-9.25 { |
|
612 catchsql { |
|
613 BEGIN; |
|
614 UPDATE t3 SET x=x+1; |
|
615 INSERT INTO t2 VALUES(3,3,1,3,3); |
|
616 SELECT * FROM t2; |
|
617 } |
|
618 } {0 {3 3 1 3 3}} |
|
619 do_test conflict-9.26 { |
|
620 catch {execsql {COMMIT}} |
|
621 execsql {SELECT * FROM t3} |
|
622 } {6} |
|
623 |
|
624 do_test conflict-10.1 { |
|
625 catchsql { |
|
626 DELETE FROM t1; |
|
627 BEGIN; |
|
628 INSERT OR ROLLBACK INTO t1 VALUES(1,2); |
|
629 INSERT OR ROLLBACK INTO t1 VALUES(1,3); |
|
630 COMMIT; |
|
631 } |
|
632 execsql {SELECT * FROM t1} |
|
633 } {} |
|
634 do_test conflict-10.2 { |
|
635 catchsql { |
|
636 CREATE TABLE t4(x); |
|
637 CREATE UNIQUE INDEX t4x ON t4(x); |
|
638 BEGIN; |
|
639 INSERT OR ROLLBACK INTO t4 VALUES(1); |
|
640 INSERT OR ROLLBACK INTO t4 VALUES(1); |
|
641 COMMIT; |
|
642 } |
|
643 execsql {SELECT * FROM t4} |
|
644 } {} |
|
645 |
|
646 # Ticket #1171. Make sure statement rollbacks do not |
|
647 # damage the database. |
|
648 # |
|
649 do_test conflict-11.1 { |
|
650 execsql { |
|
651 -- Create a database object (pages 2, 3 of the file) |
|
652 BEGIN; |
|
653 CREATE TABLE abc(a UNIQUE, b, c); |
|
654 INSERT INTO abc VALUES(1, 2, 3); |
|
655 INSERT INTO abc VALUES(4, 5, 6); |
|
656 INSERT INTO abc VALUES(7, 8, 9); |
|
657 COMMIT; |
|
658 } |
|
659 |
|
660 |
|
661 # Set a small cache size so that changes will spill into |
|
662 # the database file. |
|
663 execsql { |
|
664 PRAGMA cache_size = 10; |
|
665 } |
|
666 |
|
667 # Make lots of changes. Because of the small cache, some |
|
668 # (most?) of these changes will spill into the disk file. |
|
669 # In other words, some of the changes will not be held in |
|
670 # cache. |
|
671 # |
|
672 execsql { |
|
673 BEGIN; |
|
674 -- Make sure the pager is in EXCLUSIVE state. |
|
675 CREATE TABLE def(d, e, f); |
|
676 INSERT INTO def VALUES |
|
677 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); |
|
678 INSERT INTO def SELECT * FROM def; |
|
679 INSERT INTO def SELECT * FROM def; |
|
680 INSERT INTO def SELECT * FROM def; |
|
681 INSERT INTO def SELECT * FROM def; |
|
682 INSERT INTO def SELECT * FROM def; |
|
683 INSERT INTO def SELECT * FROM def; |
|
684 INSERT INTO def SELECT * FROM def; |
|
685 DELETE FROM abc WHERE a = 4; |
|
686 } |
|
687 |
|
688 # Execute a statement that does a statement rollback due to |
|
689 # a constraint failure. |
|
690 # |
|
691 catchsql { |
|
692 INSERT INTO abc SELECT 10, 20, 30 FROM def; |
|
693 } |
|
694 |
|
695 # Rollback the database. Verify that the state of the ABC table |
|
696 # is unchanged from the beginning of the transaction. In other words, |
|
697 # make sure the DELETE on table ABC that occurred within the transaction |
|
698 # had no effect. |
|
699 # |
|
700 execsql { |
|
701 ROLLBACK; |
|
702 SELECT * FROM abc; |
|
703 } |
|
704 } {1 2 3 4 5 6 7 8 9} |
|
705 integrity_check conflict-11.2 |
|
706 |
|
707 # Repeat test conflict-11.1 but this time commit. |
|
708 # |
|
709 do_test conflict-11.3 { |
|
710 execsql { |
|
711 BEGIN; |
|
712 -- Make sure the pager is in EXCLUSIVE state. |
|
713 UPDATE abc SET a=a+1; |
|
714 CREATE TABLE def(d, e, f); |
|
715 INSERT INTO def VALUES |
|
716 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); |
|
717 INSERT INTO def SELECT * FROM def; |
|
718 INSERT INTO def SELECT * FROM def; |
|
719 INSERT INTO def SELECT * FROM def; |
|
720 INSERT INTO def SELECT * FROM def; |
|
721 INSERT INTO def SELECT * FROM def; |
|
722 INSERT INTO def SELECT * FROM def; |
|
723 INSERT INTO def SELECT * FROM def; |
|
724 DELETE FROM abc WHERE a = 4; |
|
725 } |
|
726 catchsql { |
|
727 INSERT INTO abc SELECT 10, 20, 30 FROM def; |
|
728 } |
|
729 execsql { |
|
730 ROLLBACK; |
|
731 SELECT * FROM abc; |
|
732 } |
|
733 } {1 2 3 4 5 6 7 8 9} |
|
734 # Repeat test conflict-11.1 but this time commit. |
|
735 # |
|
736 do_test conflict-11.5 { |
|
737 execsql { |
|
738 BEGIN; |
|
739 -- Make sure the pager is in EXCLUSIVE state. |
|
740 CREATE TABLE def(d, e, f); |
|
741 INSERT INTO def VALUES |
|
742 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); |
|
743 INSERT INTO def SELECT * FROM def; |
|
744 INSERT INTO def SELECT * FROM def; |
|
745 INSERT INTO def SELECT * FROM def; |
|
746 INSERT INTO def SELECT * FROM def; |
|
747 INSERT INTO def SELECT * FROM def; |
|
748 INSERT INTO def SELECT * FROM def; |
|
749 INSERT INTO def SELECT * FROM def; |
|
750 DELETE FROM abc WHERE a = 4; |
|
751 } |
|
752 catchsql { |
|
753 INSERT INTO abc SELECT 10, 20, 30 FROM def; |
|
754 } |
|
755 execsql { |
|
756 COMMIT; |
|
757 SELECT * FROM abc; |
|
758 } |
|
759 } {1 2 3 7 8 9} |
|
760 integrity_check conflict-11.6 |
|
761 |
|
762 # Make sure UPDATE OR REPLACE works on tables that have only |
|
763 # an INTEGER PRIMARY KEY. |
|
764 # |
|
765 do_test conflict-12.1 { |
|
766 execsql { |
|
767 CREATE TABLE t5(a INTEGER PRIMARY KEY, b text); |
|
768 INSERT INTO t5 VALUES(1,'one'); |
|
769 INSERT INTO t5 VALUES(2,'two'); |
|
770 SELECT * FROM t5 |
|
771 } |
|
772 } {1 one 2 two} |
|
773 do_test conflict-12.2 { |
|
774 execsql { |
|
775 UPDATE OR IGNORE t5 SET a=a+1 WHERE a=1; |
|
776 SELECT * FROM t5; |
|
777 } |
|
778 } {1 one 2 two} |
|
779 do_test conflict-12.3 { |
|
780 catchsql { |
|
781 UPDATE t5 SET a=a+1 WHERE a=1; |
|
782 } |
|
783 } {1 {PRIMARY KEY must be unique}} |
|
784 do_test conflict-12.4 { |
|
785 execsql { |
|
786 UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1; |
|
787 SELECT * FROM t5; |
|
788 } |
|
789 } {2 one} |
|
790 |
|
791 |
|
792 finish_test |