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.26 2005/09/25 01:13:09 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 1 285 3 IGNORE UPDATE 0 {6 7 3 9} 1 1 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 1 290 8 IGNORE {UPDATE OR REPLACE} 0 {7 6 9} 1 1 291 9 FAIL {UPDATE OR IGNORE} 0 {6 7 3 9} 1 1 292 10 ABORT {UPDATE OR REPLACE} 0 {7 6 9} 1 1 293 11 ROLLBACK {UPDATE OR IGNORE} 0 {6 7 3 9} 1 1 294 12 {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 1 295 13 {} {UPDATE OR REPLACE} 0 {7 6 9} 1 1 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 execsql {pragma temp_store=file} 306 set ::sqlite_opentemp_count 0 307 set r0 [catch {execsql [subst { 308 DROP TABLE t1; 309 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); 310 INSERT INTO t1 SELECT * FROM t2; 311 UPDATE t3 SET x=0; 312 BEGIN; 313 $cmd t3 SET x=1; 314 $cmd t1 SET b=b*2; 315 $cmd t1 SET a=c+5; 316 }]} r1] 317 catch {execsql {COMMIT}} 318 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} 319 set r2 [execsql {SELECT x FROM t3}] 320 list $r0 $r1 $r2 $::sqlite_opentemp_count 321 } [list $t0 $t1 $t2 $t3] 322} 323 324# Test to make sure a lot of IGNOREs don't cause a stack overflow 325# 326do_test conflict-7.1 { 327 execsql { 328 DROP TABLE t1; 329 DROP TABLE t2; 330 DROP TABLE t3; 331 CREATE TABLE t1(a unique, b); 332 } 333 for {set i 1} {$i<=50} {incr i} { 334 execsql "INSERT into t1 values($i,[expr {$i+1}]);" 335 } 336 execsql { 337 SELECT count(*), min(a), max(b) FROM t1; 338 } 339} {50 1 51} 340do_test conflict-7.2 { 341 execsql { 342 PRAGMA count_changes=on; 343 UPDATE OR IGNORE t1 SET a=1000; 344 } 345} {1} 346do_test conflict-7.2.1 { 347 db changes 348} {1} 349do_test conflict-7.3 { 350 execsql { 351 SELECT b FROM t1 WHERE a=1000; 352 } 353} {2} 354do_test conflict-7.4 { 355 execsql { 356 SELECT count(*) FROM t1; 357 } 358} {50} 359do_test conflict-7.5 { 360 execsql { 361 PRAGMA count_changes=on; 362 UPDATE OR REPLACE t1 SET a=1001; 363 } 364} {50} 365do_test conflict-7.5.1 { 366 db changes 367} {50} 368do_test conflict-7.6 { 369 execsql { 370 SELECT b FROM t1 WHERE a=1001; 371 } 372} {51} 373do_test conflict-7.7 { 374 execsql { 375 SELECT count(*) FROM t1; 376 } 377} {1} 378 379# Update for version 3: A SELECT statement no longer resets the change 380# counter (Test result changes from 0 to 50). 381do_test conflict-7.7.1 { 382 db changes 383} {50} 384 385# Make sure the row count is right for rows that are ignored on 386# an insert. 387# 388do_test conflict-8.1 { 389 execsql { 390 DELETE FROM t1; 391 INSERT INTO t1 VALUES(1,2); 392 } 393 execsql { 394 INSERT OR IGNORE INTO t1 VALUES(2,3); 395 } 396} {1} 397do_test conflict-8.1.1 { 398 db changes 399} {1} 400do_test conflict-8.2 { 401 execsql { 402 INSERT OR IGNORE INTO t1 VALUES(2,4); 403 } 404} {0} 405do_test conflict-8.2.1 { 406 db changes 407} {0} 408do_test conflict-8.3 { 409 execsql { 410 INSERT OR REPLACE INTO t1 VALUES(2,4); 411 } 412} {1} 413do_test conflict-8.3.1 { 414 db changes 415} {1} 416do_test conflict-8.4 { 417 execsql { 418 INSERT OR IGNORE INTO t1 SELECT * FROM t1; 419 } 420} {0} 421do_test conflict-8.4.1 { 422 db changes 423} {0} 424do_test conflict-8.5 { 425 execsql { 426 INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1; 427 } 428} {2} 429do_test conflict-8.5.1 { 430 db changes 431} {2} 432do_test conflict-8.6 { 433 execsql { 434 INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1; 435 } 436} {3} 437do_test conflict-8.6.1 { 438 db changes 439} {3} 440 441integrity_check conflict-8.99 442 443do_test conflict-9.1 { 444 execsql { 445 PRAGMA count_changes=0; 446 CREATE TABLE t2( 447 a INTEGER UNIQUE ON CONFLICT IGNORE, 448 b INTEGER UNIQUE ON CONFLICT FAIL, 449 c INTEGER UNIQUE ON CONFLICT REPLACE, 450 d INTEGER UNIQUE ON CONFLICT ABORT, 451 e INTEGER UNIQUE ON CONFLICT ROLLBACK 452 ); 453 CREATE TABLE t3(x); 454 INSERT INTO t3 VALUES(1); 455 SELECT * FROM t3; 456 } 457} {1} 458do_test conflict-9.2 { 459 catchsql { 460 INSERT INTO t2 VALUES(1,1,1,1,1); 461 INSERT INTO t2 VALUES(2,2,2,2,2); 462 SELECT * FROM t2; 463 } 464} {0 {1 1 1 1 1 2 2 2 2 2}} 465do_test conflict-9.3 { 466 catchsql { 467 INSERT INTO t2 VALUES(1,3,3,3,3); 468 SELECT * FROM t2; 469 } 470} {0 {1 1 1 1 1 2 2 2 2 2}} 471do_test conflict-9.4 { 472 catchsql { 473 UPDATE t2 SET a=a+1 WHERE a=1; 474 SELECT * FROM t2; 475 } 476} {0 {1 1 1 1 1 2 2 2 2 2}} 477do_test conflict-9.5 { 478 catchsql { 479 INSERT INTO t2 VALUES(3,1,3,3,3); 480 SELECT * FROM t2; 481 } 482} {1 {column b is not unique}} 483do_test conflict-9.6 { 484 catchsql { 485 UPDATE t2 SET b=b+1 WHERE b=1; 486 SELECT * FROM t2; 487 } 488} {1 {column b is not unique}} 489do_test conflict-9.7 { 490 catchsql { 491 BEGIN; 492 UPDATE t3 SET x=x+1; 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.8 { 498 execsql {COMMIT} 499 execsql {SELECT * FROM t3} 500} {2} 501do_test conflict-9.9 { 502 catchsql { 503 BEGIN; 504 UPDATE t3 SET x=x+1; 505 UPDATE t2 SET b=b+1 WHERE b=1; 506 SELECT * FROM t2; 507 } 508} {1 {column b is not unique}} 509do_test conflict-9.10 { 510 execsql {COMMIT} 511 execsql {SELECT * FROM t3} 512} {3} 513do_test conflict-9.11 { 514 catchsql { 515 INSERT INTO t2 VALUES(3,3,3,1,3); 516 SELECT * FROM t2; 517 } 518} {1 {column d is not unique}} 519do_test conflict-9.12 { 520 catchsql { 521 UPDATE t2 SET d=d+1 WHERE d=1; 522 SELECT * FROM t2; 523 } 524} {1 {column d is not unique}} 525do_test conflict-9.13 { 526 catchsql { 527 BEGIN; 528 UPDATE t3 SET x=x+1; 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.14 { 534 execsql {COMMIT} 535 execsql {SELECT * FROM t3} 536} {4} 537do_test conflict-9.15 { 538 catchsql { 539 BEGIN; 540 UPDATE t3 SET x=x+1; 541 UPDATE t2 SET d=d+1 WHERE d=1; 542 SELECT * FROM t2; 543 } 544} {1 {column d is not unique}} 545do_test conflict-9.16 { 546 execsql {COMMIT} 547 execsql {SELECT * FROM t3} 548} {5} 549do_test conflict-9.17 { 550 catchsql { 551 INSERT INTO t2 VALUES(3,3,3,3,1); 552 SELECT * FROM t2; 553 } 554} {1 {column e is not unique}} 555do_test conflict-9.18 { 556 catchsql { 557 UPDATE t2 SET e=e+1 WHERE e=1; 558 SELECT * FROM t2; 559 } 560} {1 {column e is not unique}} 561do_test conflict-9.19 { 562 catchsql { 563 BEGIN; 564 UPDATE t3 SET x=x+1; 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.20 { 570 catch {execsql {COMMIT}} 571 execsql {SELECT * FROM t3} 572} {5} 573do_test conflict-9.21 { 574 catchsql { 575 BEGIN; 576 UPDATE t3 SET x=x+1; 577 UPDATE t2 SET e=e+1 WHERE e=1; 578 SELECT * FROM t2; 579 } 580} {1 {column e is not unique}} 581do_test conflict-9.22 { 582 catch {execsql {COMMIT}} 583 execsql {SELECT * FROM t3} 584} {5} 585do_test conflict-9.23 { 586 catchsql { 587 INSERT INTO t2 VALUES(3,3,1,3,3); 588 SELECT * FROM t2; 589 } 590} {0 {2 2 2 2 2 3 3 1 3 3}} 591do_test conflict-9.24 { 592 catchsql { 593 UPDATE t2 SET c=c-1 WHERE c=2; 594 SELECT * FROM t2; 595 } 596} {0 {2 2 1 2 2}} 597do_test conflict-9.25 { 598 catchsql { 599 BEGIN; 600 UPDATE t3 SET x=x+1; 601 INSERT INTO t2 VALUES(3,3,1,3,3); 602 SELECT * FROM t2; 603 } 604} {0 {3 3 1 3 3}} 605do_test conflict-9.26 { 606 catch {execsql {COMMIT}} 607 execsql {SELECT * FROM t3} 608} {6} 609 610do_test conflict-10.1 { 611 catchsql { 612 DELETE FROM t1; 613 BEGIN; 614 INSERT OR ROLLBACK INTO t1 VALUES(1,2); 615 INSERT OR ROLLBACK INTO t1 VALUES(1,3); 616 COMMIT; 617 } 618 execsql {SELECT * FROM t1} 619} {} 620do_test conflict-10.2 { 621 catchsql { 622 CREATE TABLE t4(x); 623 CREATE UNIQUE INDEX t4x ON t4(x); 624 BEGIN; 625 INSERT OR ROLLBACK INTO t4 VALUES(1); 626 INSERT OR ROLLBACK INTO t4 VALUES(1); 627 COMMIT; 628 } 629 execsql {SELECT * FROM t4} 630} {} 631 632# Ticket #1171. Make sure statement rollbacks do not 633# damage the database. 634# 635do_test conflict-11.1 { 636 execsql { 637 -- Create a database object (pages 2, 3 of the file) 638 BEGIN; 639 CREATE TABLE abc(a UNIQUE, b, c); 640 INSERT INTO abc VALUES(1, 2, 3); 641 INSERT INTO abc VALUES(4, 5, 6); 642 INSERT INTO abc VALUES(7, 8, 9); 643 COMMIT; 644 } 645 646 647 # Set a small cache size so that changes will spill into 648 # the database file. 649 execsql { 650 PRAGMA cache_size = 10; 651 } 652 653 # Make lots of changes. Because of the small cache, some 654 # (most?) of these changes will spill into the disk file. 655 # In other words, some of the changes will not be held in 656 # cache. 657 # 658 execsql { 659 BEGIN; 660 -- Make sure the pager is in EXCLUSIVE state. 661 CREATE TABLE def(d, e, f); 662 INSERT INTO def VALUES 663 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); 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 INSERT INTO def SELECT * FROM def; 671 DELETE FROM abc WHERE a = 4; 672 } 673 674 # Execute a statement that does a statement rollback due to 675 # a constraint failure. 676 # 677 catchsql { 678 INSERT INTO abc SELECT 10, 20, 30 FROM def; 679 } 680 681 # Rollback the database. Verify that the state of the ABC table 682 # is unchanged from the beginning of the transaction. In other words, 683 # make sure the DELETE on table ABC that occurred within the transaction 684 # had no effect. 685 # 686 execsql { 687 ROLLBACK; 688 SELECT * FROM abc; 689 } 690} {1 2 3 4 5 6 7 8 9} 691integrity_check conflict-11.2 692 693# Repeat test conflict-11.1 but this time commit. 694# 695do_test conflict-11.3 { 696 execsql { 697 BEGIN; 698 -- Make sure the pager is in EXCLUSIVE state. 699 UPDATE abc SET a=a+1; 700 CREATE TABLE def(d, e, f); 701 INSERT INTO def VALUES 702 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); 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 INSERT INTO def SELECT * FROM def; 710 DELETE FROM abc WHERE a = 4; 711 } 712 catchsql { 713 INSERT INTO abc SELECT 10, 20, 30 FROM def; 714 } 715 execsql { 716 ROLLBACK; 717 SELECT * FROM abc; 718 } 719} {1 2 3 4 5 6 7 8 9} 720# Repeat test conflict-11.1 but this time commit. 721# 722do_test conflict-11.5 { 723 execsql { 724 BEGIN; 725 -- Make sure the pager is in EXCLUSIVE state. 726 CREATE TABLE def(d, e, f); 727 INSERT INTO def VALUES 728 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); 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 INSERT INTO def SELECT * FROM def; 736 DELETE FROM abc WHERE a = 4; 737 } 738 catchsql { 739 INSERT INTO abc SELECT 10, 20, 30 FROM def; 740 } 741 execsql { 742 COMMIT; 743 SELECT * FROM abc; 744 } 745} {1 2 3 7 8 9} 746integrity_check conflict-11.6 747 748 749finish_test 750