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