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