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