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# 290foreach {i conf1 cmd t0 t1 t2 t3 t4} { 291 1 {} UPDATE 1 {6 7 8 9} 1 0 1 292 2 REPLACE UPDATE 0 {7 6 9} 1 0 0 293 3 IGNORE UPDATE 0 {6 7 3 9} 1 0 0 294 4 FAIL UPDATE 1 {6 7 3 4} 1 0 0 295 5 ABORT UPDATE 1 {1 2 3 4} 1 0 1 296 6 ROLLBACK UPDATE 1 {1 2 3 4} 0 0 0 297 7 REPLACE {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 298 8 IGNORE {UPDATE OR REPLACE} 0 {7 6 9} 1 0 1 299 9 FAIL {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 300 10 ABORT {UPDATE OR REPLACE} 0 {7 6 9} 1 0 1 301 11 ROLLBACK {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 302 12 {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 0 0 303 13 {} {UPDATE OR REPLACE} 0 {7 6 9} 1 0 1 304 14 {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 0 0 305 15 {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 0 1 306 16 {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 0 0 307} { 308 309 # When using in-memory journals, no temporary files are required for 310 # statement journals. 311 if {[permutation] == "inmemory_journal"} { set t4 0 } 312 313 if {$t0} {set t1 {UNIQUE constraint failed: t1.a}} 314 if {[info exists TEMP_STORE] && $TEMP_STORE==3} { 315 set t3 0 316 } else { 317 set t3 [expr {$t3+$t4}] 318 } 319 do_test conflict2-6.$i { 320 db close 321 sqlite3 db test.db 322 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 323 execsql {pragma temp_store=file} 324 set ::sqlite_opentemp_count 0 325 set r0 [catch {execsql [subst { 326 DROP TABLE t1; 327 CREATE TABLE t1(a,b,c, PRIMARY KEY(a) $conf1) WITHOUT rowid; 328 INSERT INTO t1 SELECT * FROM t2; 329 UPDATE t3 SET x=0; 330 BEGIN; 331 $cmd t3 SET x=1; 332 $cmd t1 SET b=b*2; 333 $cmd t1 SET a=c+5; 334 }]} r1] 335 catch {execsql {COMMIT}} 336 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} 337 set r2 [execsql {SELECT x FROM t3}] 338 list $r0 $r1 $r2 $::sqlite_opentemp_count 339 } [list $t0 $t1 $t2 $t3] 340} 341 342# Test to make sure a lot of IGNOREs don't cause a stack overflow 343# 344do_test conflict2-7.1 { 345 execsql { 346 DROP TABLE t1; 347 DROP TABLE t2; 348 DROP TABLE t3; 349 CREATE TABLE t1(a PRIMARY KEY, b) without rowid; 350 } 351 for {set i 1} {$i<=50} {incr i} { 352 execsql "INSERT into t1 values($i,[expr {$i+1}]);" 353 } 354 execsql { 355 SELECT count(*), min(a), max(b) FROM t1; 356 } 357} {50 1 51} 358do_test conflict2-7.2 { 359 execsql { 360 PRAGMA count_changes=on; 361 UPDATE OR IGNORE t1 SET a=1000; 362 } 363} {1} 364do_test conflict2-7.2.1 { 365 db changes 366} {1} 367do_test conflict2-7.3 { 368 execsql { 369 SELECT b FROM t1 WHERE a=1000; 370 } 371} {2} 372do_test conflict2-7.4 { 373 execsql { 374 SELECT count(*) FROM t1; 375 } 376} {50} 377do_test conflict2-7.5 { 378 execsql { 379 PRAGMA count_changes=on; 380 UPDATE OR REPLACE t1 SET a=1001; 381 } 382} {50} 383do_test conflict2-7.5.1 { 384 db changes 385} {50} 386do_test conflict2-7.7 { 387 execsql { 388 SELECT count(*) FROM t1; 389 } 390} {1} 391 392# Update for version 3: A SELECT statement no longer resets the change 393# counter (Test result changes from 0 to 50). 394do_test conflict2-7.7.1 { 395 db changes 396} {50} 397 398# Make sure the row count is right for rows that are ignored on 399# an insert. 400# 401do_test conflict2-8.1 { 402 execsql { 403 DELETE FROM t1; 404 INSERT INTO t1 VALUES(1,2); 405 } 406 execsql { 407 INSERT OR IGNORE INTO t1 VALUES(2,3); 408 } 409} {1} 410do_test conflict2-8.1.1 { 411 db changes 412} {1} 413do_test conflict2-8.2 { 414 execsql { 415 INSERT OR IGNORE INTO t1 VALUES(2,4); 416 } 417} {0} 418do_test conflict2-8.2.1 { 419 db changes 420} {0} 421do_test conflict2-8.3 { 422 execsql { 423 INSERT OR REPLACE INTO t1 VALUES(2,4); 424 } 425} {1} 426do_test conflict2-8.3.1 { 427 db changes 428} {1} 429do_test conflict2-8.4 { 430 execsql { 431 INSERT OR IGNORE INTO t1 SELECT * FROM t1; 432 } 433} {0} 434do_test conflict2-8.4.1 { 435 db changes 436} {0} 437do_test conflict2-8.5 { 438 execsql { 439 INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1; 440 } 441} {2} 442do_test conflict2-8.5.1 { 443 db changes 444} {2} 445do_test conflict2-8.6 { 446 execsql { 447 INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1; 448 } 449} {3} 450do_test conflict2-8.6.1 { 451 db changes 452} {3} 453 454integrity_check conflict2-8.99 455 456do_test conflict2-9.1 { 457 execsql { 458 PRAGMA count_changes=0; 459 CREATE TABLE t2( 460 a INTEGER PRIMARY KEY ON CONFLICT IGNORE, 461 b INTEGER UNIQUE ON CONFLICT FAIL, 462 c INTEGER UNIQUE ON CONFLICT REPLACE, 463 d INTEGER UNIQUE ON CONFLICT ABORT, 464 e INTEGER UNIQUE ON CONFLICT ROLLBACK 465 ) WITHOUT rowid; 466 CREATE TABLE t3(x); 467 INSERT INTO t3 VALUES(1); 468 SELECT * FROM t3; 469 } 470} {1} 471do_test conflict2-9.2 { 472 catchsql { 473 INSERT INTO t2 VALUES(1,1,1,1,1); 474 INSERT INTO t2 VALUES(2,2,2,2,2); 475 SELECT * FROM t2; 476 } 477} {0 {1 1 1 1 1 2 2 2 2 2}} 478do_test conflict2-9.3 { 479 catchsql { 480 INSERT INTO t2 VALUES(1,3,3,3,3); 481 SELECT * FROM t2; 482 } 483} {0 {1 1 1 1 1 2 2 2 2 2}} 484do_test conflict2-9.4 { 485 catchsql { 486 UPDATE t2 SET a=a+1 WHERE a=1; 487 SELECT * FROM t2; 488 } 489} {0 {1 1 1 1 1 2 2 2 2 2}} 490do_test conflict2-9.5 { 491 catchsql { 492 INSERT INTO t2 VALUES(3,1,3,3,3); 493 } 494} {1 {UNIQUE constraint failed: t2.b}} 495do_test conflict2-9.5b { 496 db eval {SELECT * FROM t2;} 497} {1 1 1 1 1 2 2 2 2 2} 498do_test conflict2-9.6 { 499 catchsql { 500 UPDATE t2 SET b=b+1 WHERE b=1; 501 SELECT * FROM t2; 502 } 503} {1 {UNIQUE constraint failed: t2.b}} 504do_test conflict2-9.6b { 505 db eval {SELECT * FROM t2;} 506} {1 1 1 1 1 2 2 2 2 2} 507do_test conflict2-9.7 { 508 catchsql { 509 BEGIN; 510 UPDATE t3 SET x=x+1; 511 INSERT INTO t2 VALUES(3,1,3,3,3); 512 SELECT * FROM t2; 513 } 514} {1 {UNIQUE constraint failed: t2.b}} 515do_test conflict2-9.8 { 516 execsql {COMMIT} 517 execsql {SELECT * FROM t3} 518} {2} 519do_test conflict2-9.9 { 520 catchsql { 521 BEGIN; 522 UPDATE t3 SET x=x+1; 523 UPDATE t2 SET b=b+1 WHERE b=1; 524 SELECT * FROM t2; 525 } 526} {1 {UNIQUE constraint failed: t2.b}} 527do_test conflict2-9.10 { 528 execsql {COMMIT} 529 execsql {SELECT * FROM t3} 530} {3} 531do_test conflict2-9.11 { 532 catchsql { 533 INSERT INTO t2 VALUES(3,3,3,1,3); 534 SELECT * FROM t2; 535 } 536} {1 {UNIQUE constraint failed: t2.d}} 537do_test conflict2-9.12 { 538 catchsql { 539 UPDATE t2 SET d=d+1 WHERE d=1; 540 SELECT * FROM t2; 541 } 542} {1 {UNIQUE constraint failed: t2.d}} 543do_test conflict2-9.13 { 544 catchsql { 545 BEGIN; 546 UPDATE t3 SET x=x+1; 547 INSERT INTO t2 VALUES(3,3,3,1,3); 548 SELECT * FROM t2; 549 } 550} {1 {UNIQUE constraint failed: t2.d}} 551do_test conflict2-9.14 { 552 execsql {COMMIT} 553 execsql {SELECT * FROM t3} 554} {4} 555do_test conflict2-9.15 { 556 catchsql { 557 BEGIN; 558 UPDATE t3 SET x=x+1; 559 UPDATE t2 SET d=d+1 WHERE d=1; 560 SELECT * FROM t2; 561 } 562} {1 {UNIQUE constraint failed: t2.d}} 563do_test conflict2-9.16 { 564 execsql {COMMIT} 565 execsql {SELECT * FROM t3} 566} {5} 567do_test conflict2-9.17 { 568 catchsql { 569 INSERT INTO t2 VALUES(3,3,3,3,1); 570 SELECT * FROM t2; 571 } 572} {1 {UNIQUE constraint failed: t2.e}} 573do_test conflict2-9.18 { 574 catchsql { 575 UPDATE t2 SET e=e+1 WHERE e=1; 576 SELECT * FROM t2; 577 } 578} {1 {UNIQUE constraint failed: t2.e}} 579do_test conflict2-9.19 { 580 catchsql { 581 BEGIN; 582 UPDATE t3 SET x=x+1; 583 INSERT INTO t2 VALUES(3,3,3,3,1); 584 SELECT * FROM t2; 585 } 586} {1 {UNIQUE constraint failed: t2.e}} 587verify_ex_errcode conflict2-9.21b SQLITE_CONSTRAINT_UNIQUE 588do_test conflict2-9.20 { 589 catch {execsql {COMMIT}} 590 execsql {SELECT * FROM t3} 591} {5} 592do_test conflict2-9.21 { 593 catchsql { 594 BEGIN; 595 UPDATE t3 SET x=x+1; 596 UPDATE t2 SET e=e+1 WHERE e=1; 597 SELECT * FROM t2; 598 } 599} {1 {UNIQUE constraint failed: t2.e}} 600verify_ex_errcode conflict2-9.21b SQLITE_CONSTRAINT_UNIQUE 601do_test conflict2-9.22 { 602 catch {execsql {COMMIT}} 603 execsql {SELECT * FROM t3} 604} {5} 605do_test conflict2-9.23 { 606 catchsql { 607 INSERT INTO t2 VALUES(3,3,1,3,3); 608 SELECT * FROM t2; 609 } 610} {0 {2 2 2 2 2 3 3 1 3 3}} 611do_test conflict2-9.24 { 612 catchsql { 613 UPDATE t2 SET c=c-1 WHERE c=2; 614 SELECT * FROM t2; 615 } 616} {0 {2 2 1 2 2}} 617do_test conflict2-9.25 { 618 catchsql { 619 BEGIN; 620 UPDATE t3 SET x=x+1; 621 INSERT INTO t2 VALUES(3,3,1,3,3); 622 SELECT * FROM t2; 623 } 624} {0 {3 3 1 3 3}} 625do_test conflict2-9.26 { 626 catch {execsql {COMMIT}} 627 execsql {SELECT * FROM t3} 628} {6} 629 630do_test conflict2-10.1 { 631 catchsql { 632 DELETE FROM t1; 633 BEGIN; 634 INSERT OR ROLLBACK INTO t1 VALUES(1,2); 635 INSERT OR ROLLBACK INTO t1 VALUES(1,3); 636 COMMIT; 637 } 638 execsql {SELECT * FROM t1} 639} {} 640do_test conflict2-10.2 { 641 catchsql { 642 CREATE TABLE t4(x); 643 CREATE UNIQUE INDEX t4x ON t4(x); 644 BEGIN; 645 INSERT OR ROLLBACK INTO t4 VALUES(1); 646 INSERT OR ROLLBACK INTO t4 VALUES(1); 647 COMMIT; 648 } 649 execsql {SELECT * FROM t4} 650} {} 651 652# Ticket #1171. Make sure statement rollbacks do not 653# damage the database. 654# 655do_test conflict2-11.1 { 656 execsql { 657 -- Create a database object (pages 2, 3 of the file) 658 BEGIN; 659 CREATE TABLE abc(a PRIMARY KEY, b, c) WITHOUT rowid; 660 INSERT INTO abc VALUES(1, 2, 3); 661 INSERT INTO abc VALUES(4, 5, 6); 662 INSERT INTO abc VALUES(7, 8, 9); 663 COMMIT; 664 } 665 666 667 # Set a small cache size so that changes will spill into 668 # the database file. 669 execsql { 670 PRAGMA cache_size = 10; 671 } 672 673 # Make lots of changes. Because of the small cache, some 674 # (most?) of these changes will spill into the disk file. 675 # In other words, some of the changes will not be held in 676 # cache. 677 # 678 execsql { 679 BEGIN; 680 -- Make sure the pager is in EXCLUSIVE state. 681 CREATE TABLE def(d, e, f); 682 INSERT INTO def VALUES 683 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); 684 INSERT INTO def SELECT * FROM def; 685 INSERT INTO def SELECT * FROM def; 686 INSERT INTO def SELECT * FROM def; 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 DELETE FROM abc WHERE a = 4; 692 } 693 694 # Execute a statement that does a statement rollback due to 695 # a constraint failure. 696 # 697 catchsql { 698 INSERT INTO abc SELECT 10, 20, 30 FROM def; 699 } 700 701 # Rollback the database. Verify that the state of the ABC table 702 # is unchanged from the beginning of the transaction. In other words, 703 # make sure the DELETE on table ABC that occurred within the transaction 704 # had no effect. 705 # 706 execsql { 707 ROLLBACK; 708 SELECT * FROM abc; 709 } 710} {1 2 3 4 5 6 7 8 9} 711integrity_check conflict2-11.2 712 713# Repeat test conflict2-11.1 but this time commit. 714# 715do_test conflict2-11.3 { 716 execsql { 717 BEGIN; 718 -- Make sure the pager is in EXCLUSIVE state. 719 UPDATE abc SET a=a+1; 720 CREATE TABLE def(d, e, f); 721 INSERT INTO def VALUES 722 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); 723 INSERT INTO def SELECT * FROM def; 724 INSERT INTO def SELECT * FROM def; 725 INSERT INTO def SELECT * FROM def; 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 DELETE FROM abc WHERE a = 4; 731 } 732 catchsql { 733 INSERT INTO abc SELECT 10, 20, 30 FROM def; 734 } 735 execsql { 736 ROLLBACK; 737 SELECT * FROM abc; 738 } 739} {1 2 3 4 5 6 7 8 9} 740# Repeat test conflict2-11.1 but this time commit. 741# 742do_test conflict2-11.5 { 743 execsql { 744 BEGIN; 745 -- Make sure the pager is in EXCLUSIVE state. 746 CREATE TABLE def(d, e, f); 747 INSERT INTO def VALUES 748 ('xxxxxxxxxxxxxxx', 'yyyyyyyyyyyyyyyy', 'zzzzzzzzzzzzzzzz'); 749 INSERT INTO def SELECT * FROM def; 750 INSERT INTO def SELECT * FROM def; 751 INSERT INTO def SELECT * FROM def; 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 DELETE FROM abc WHERE a = 4; 757 } 758 catchsql { 759 INSERT INTO abc SELECT 10, 20, 30 FROM def; 760 } 761 execsql { 762 COMMIT; 763 SELECT * FROM abc; 764 } 765} {1 2 3 7 8 9} 766integrity_check conflict2-11.6 767 768# Make sure UPDATE OR REPLACE works on tables that have only 769# an INTEGER PRIMARY KEY. 770# 771do_test conflict2-12.1 { 772 execsql { 773 CREATE TABLE t5(a INTEGER PRIMARY KEY, b text) WITHOUT rowid; 774 INSERT INTO t5 VALUES(1,'one'); 775 INSERT INTO t5 VALUES(2,'two'); 776 SELECT * FROM t5 777 } 778} {1 one 2 two} 779do_test conflict2-12.2 { 780 execsql { 781 UPDATE OR IGNORE t5 SET a=a+1 WHERE a=1; 782 SELECT * FROM t5; 783 } 784} {1 one 2 two} 785do_test conflict2-12.3 { 786 catchsql { 787 UPDATE t5 SET a=a+1 WHERE a=1; 788 } 789} {1 {UNIQUE constraint failed: t5.a}} 790verify_ex_errcode conflict2-12.3b SQLITE_CONSTRAINT_PRIMARYKEY 791do_test conflict2-12.4 { 792 execsql { 793 UPDATE OR REPLACE t5 SET a=a+1 WHERE a=1; 794 SELECT * FROM t5; 795 } 796} {2 one} 797 798 799# Ticket [c38baa3d969eab7946dc50ba9d9b4f0057a19437] 800# REPLACE works like ABORT on a CHECK constraint. 801# 802do_test conflict2-13.1 { 803 execsql { 804 CREATE TABLE t13(a PRIMARY KEY CHECK(a!=2)) WITHOUT rowid; 805 BEGIN; 806 REPLACE INTO t13 VALUES(1); 807 } 808 catchsql { 809 REPLACE INTO t13 VALUES(2); 810 } 811} {1 {CHECK constraint failed: t13}} 812verify_ex_errcode conflict2-13.1b SQLITE_CONSTRAINT_CHECK 813do_test conflict2-13.2 { 814 execsql { 815 REPLACE INTO t13 VALUES(3); 816 COMMIT; 817 SELECT * FROM t13; 818 } 819} {1 3} 820 821# Test for an unreleased bug in the REPLACE conflict resolution 822# discovered on 2013-11-09. 823# 824do_execsql_test conflict2-14.1 { 825 DROP TABLE IF EXISTS t1; 826 CREATE TABLE t1( 827 x TEXT PRIMARY KEY NOT NULL, 828 y TEXT NOT NULL, 829 z INTEGER 830 ); 831 INSERT INTO t1 VALUES('alpha','beta',1); 832 CREATE UNIQUE INDEX t1xy ON t1(x,y); 833 REPLACE INTO t1(x,y,z) VALUES('alpha','gamma',1); 834 PRAGMA integrity_check; 835 SELECT x,y FROM t1 INDEXED BY t1xy; 836 SELECT x,y,z FROM t1 NOT INDEXED; 837} {ok alpha gamma alpha gamma 1} 838do_execsql_test conflict2-14.2 { 839 DROP TABLE IF EXISTS t1; 840 CREATE TABLE t1( 841 x TEXT PRIMARY KEY NOT NULL, 842 y TEXT NOT NULL, 843 z INTEGER 844 ) WITHOUT ROWID; 845 INSERT INTO t1 VALUES('alpha','beta',1); 846 CREATE UNIQUE INDEX t1xy ON t1(x,y); 847 REPLACE INTO t1(x,y,z) VALUES('alpha','gamma',1); 848 PRAGMA integrity_check; 849 SELECT x,y FROM t1 INDEXED BY t1xy; 850 SELECT x,y,z FROM t1 NOT INDEXED; 851} {ok alpha gamma alpha gamma 1} 852 853 854 855finish_test 856