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