wasCSharpSQLite – Blame information for rev 7
?pathlinks?
Rev | Author | Line No. | Line |
---|---|---|---|
1 | office | 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.32 2009/04/30 09:10:38 danielk1977 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==3} { |
||
311 | set t3 0 |
||
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 | # Ticket [c38baa3d969eab7946dc50ba9d9b4f0057a19437] |
||
793 | # REPLACE works like ABORT on a CHECK constraint. |
||
794 | # |
||
795 | do_test conflict-13.1 { |
||
796 | execsql { |
||
797 | CREATE TABLE t13(a CHECK(a!=2)); |
||
798 | BEGIN; |
||
799 | REPLACE INTO t13 VALUES(1); |
||
800 | } |
||
801 | catchsql { |
||
802 | REPLACE INTO t13 VALUES(2); |
||
803 | } |
||
804 | } {1 {constraint failed}} |
||
805 | do_test conflict-13.2 { |
||
806 | execsql { |
||
807 | REPLACE INTO t13 VALUES(3); |
||
808 | COMMIT; |
||
809 | SELECT * FROM t13; |
||
810 | } |
||
811 | } {1 3} |
||
812 | |||
813 | |||
814 | finish_test |