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