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.1.1.1 2012/02/21 17:04:16 misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to conflict.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test