aboutsummaryrefslogtreecommitdiffstatshomepage
path: root/libraries/sqlite/unix/sqlite-3.5.1/test/conflict.test
diff options
context:
space:
mode:
Diffstat (limited to 'libraries/sqlite/unix/sqlite-3.5.1/test/conflict.test')
-rw-r--r--libraries/sqlite/unix/sqlite-3.5.1/test/conflict.test763
1 files changed, 763 insertions, 0 deletions
diff --git a/libraries/sqlite/unix/sqlite-3.5.1/test/conflict.test b/libraries/sqlite/unix/sqlite-3.5.1/test/conflict.test
new file mode 100644
index 0000000..6bca1e8
--- /dev/null
+++ b/libraries/sqlite/unix/sqlite-3.5.1/test/conflict.test
@@ -0,0 +1,763 @@
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.30 2007/08/21 14:27:02 danielk1977 Exp $
17
18set testdir [file dirname $argv0]
19source $testdir/tester.tcl
20
21ifcapable !conflict {
22 finish_test
23 return
24}
25
26# Create tables for the first group of tests.
27#
28do_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#
45foreach {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#
74do_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#
92foreach {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#
119do_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#
137foreach {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
162do_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#
179foreach {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
210do_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#
227foreach {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
263do_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#
291foreach {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#
340do_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}
354do_test conflict-7.2 {
355 execsql {
356 PRAGMA count_changes=on;
357 UPDATE OR IGNORE t1 SET a=1000;
358 }
359} {1}
360do_test conflict-7.2.1 {
361 db changes
362} {1}
363do_test conflict-7.3 {
364 execsql {
365 SELECT b FROM t1 WHERE a=1000;
366 }
367} {2}
368do_test conflict-7.4 {
369 execsql {
370 SELECT count(*) FROM t1;
371 }
372} {50}
373do_test conflict-7.5 {
374 execsql {
375 PRAGMA count_changes=on;
376 UPDATE OR REPLACE t1 SET a=1001;
377 }
378} {50}
379do_test conflict-7.5.1 {
380 db changes
381} {50}
382do_test conflict-7.6 {
383 execsql {
384 SELECT b FROM t1 WHERE a=1001;
385 }
386} {51}
387do_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).
395do_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#
402do_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}
411do_test conflict-8.1.1 {
412 db changes
413} {1}
414do_test conflict-8.2 {
415 execsql {
416 INSERT OR IGNORE INTO t1 VALUES(2,4);
417 }
418} {0}
419do_test conflict-8.2.1 {
420 db changes
421} {0}
422do_test conflict-8.3 {
423 execsql {
424 INSERT OR REPLACE INTO t1 VALUES(2,4);
425 }
426} {1}
427do_test conflict-8.3.1 {
428 db changes
429} {1}
430do_test conflict-8.4 {
431 execsql {
432 INSERT OR IGNORE INTO t1 SELECT * FROM t1;
433 }
434} {0}
435do_test conflict-8.4.1 {
436 db changes
437} {0}
438do_test conflict-8.5 {
439 execsql {
440 INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1;
441 }
442} {2}
443do_test conflict-8.5.1 {
444 db changes
445} {2}
446do_test conflict-8.6 {
447 execsql {
448 INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1;
449 }
450} {3}
451do_test conflict-8.6.1 {
452 db changes
453} {3}
454
455integrity_check conflict-8.99
456
457do_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}
472do_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}}
479do_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}}
485do_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}}
491do_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}}
497do_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}}
503do_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}}
511do_test conflict-9.8 {
512 execsql {COMMIT}
513 execsql {SELECT * FROM t3}
514} {2}
515do_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}}
523do_test conflict-9.10 {
524 execsql {COMMIT}
525 execsql {SELECT * FROM t3}
526} {3}
527do_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}}
533do_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}}
539do_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}}
547do_test conflict-9.14 {
548 execsql {COMMIT}
549 execsql {SELECT * FROM t3}
550} {4}
551do_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}}
559do_test conflict-9.16 {
560 execsql {COMMIT}
561 execsql {SELECT * FROM t3}
562} {5}
563do_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}}
569do_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}}
575do_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}}
583do_test conflict-9.20 {
584 catch {execsql {COMMIT}}
585 execsql {SELECT * FROM t3}
586} {5}
587do_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}}
595do_test conflict-9.22 {
596 catch {execsql {COMMIT}}
597 execsql {SELECT * FROM t3}
598} {5}
599do_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}}
605do_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}}
611do_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}}
619do_test conflict-9.26 {
620 catch {execsql {COMMIT}}
621 execsql {SELECT * FROM t3}
622} {6}
623
624do_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} {}
634do_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#
649do_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}
705integrity_check conflict-11.2
706
707# Repeat test conflict-11.1 but this time commit.
708#
709do_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#
736do_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}
760integrity_check conflict-11.6
761
762
763finish_test
generated by cgit v1.1 at 2025-04-25 04:49:18 +0000