wasCSharpSQLite – Blame information for rev 1

Subversion Repositories:
Rev:
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