1# 2# 2002 January 29 3# 4# The author disclaims copyright to this source code. In place of 5# a legal notice, here is a blessing: 6# 7# May you do good and not evil. 8# May you find forgiveness for yourself and forgive others. 9# May you share freely, never taking more than you give. 10# 11#*********************************************************************** 12# This file implements regression tests for SQLite library. 13# 14# This file implements tests for the conflict resolution extension 15# to SQLite. 16# 17# $Id: conflict.test,v 1.19 2003/08/05 13:13:39 drh Exp $ 18 19set testdir [file dirname $argv0] 20source $testdir/tester.tcl 21 22# Create tables for the first group of tests. 23# 24do_test conflict-1.0 { 25 execsql { 26 CREATE TABLE t1(a, b, c, UNIQUE(a,b)); 27 CREATE TABLE t2(x); 28 SELECT c FROM t1 ORDER BY c; 29 } 30} {} 31 32# Six columns of configuration data as follows: 33# 34# i The reference number of the test 35# conf The conflict resolution algorithm on the BEGIN statement 36# cmd An INSERT or REPLACE command to execute against table t1 37# t0 True if there is an error from $cmd 38# t1 Content of "c" column of t1 assuming no error in $cmd 39# t2 Content of "x" column of t2 40# 41foreach {i conf cmd t0 t1 t2} { 42 1 {} INSERT 1 {} 1 43 2 {} {INSERT OR IGNORE} 0 3 1 44 3 {} {INSERT OR REPLACE} 0 4 1 45 4 {} REPLACE 0 4 1 46 5 {} {INSERT OR FAIL} 1 {} 1 47 6 {} {INSERT OR ABORT} 1 {} 1 48 7 {} {INSERT OR ROLLBACK} 1 {} {} 49 8 IGNORE INSERT 0 3 1 50 9 IGNORE {INSERT OR IGNORE} 0 3 1 51 10 IGNORE {INSERT OR REPLACE} 0 4 1 52 11 IGNORE REPLACE 0 4 1 53 12 IGNORE {INSERT OR FAIL} 1 {} 1 54 13 IGNORE {INSERT OR ABORT} 1 {} 1 55 14 IGNORE {INSERT OR ROLLBACK} 1 {} {} 56 15 REPLACE INSERT 0 4 1 57 16 FAIL INSERT 1 {} 1 58 17 ABORT INSERT 1 {} 1 59 18 ROLLBACK INSERT 1 {} {} 60} { 61 do_test conflict-1.$i { 62 if {$conf!=""} {set conf "ON CONFLICT $conf"} 63 set r0 [catch {execsql [subst { 64 DELETE FROM t1; 65 DELETE FROM t2; 66 INSERT INTO t1 VALUES(1,2,3); 67 BEGIN $conf; 68 INSERT INTO t2 VALUES(1); 69 $cmd INTO t1 VALUES(1,2,4); 70 }]} r1] 71 catch {execsql {COMMIT}} 72 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 73 set r2 [execsql {SELECT x FROM t2}] 74 list $r0 $r1 $r2 75 } [list $t0 $t1 $t2] 76} 77 78# Create tables for the first group of tests. 79# 80do_test conflict-2.0 { 81 execsql { 82 DROP TABLE t1; 83 DROP TABLE t2; 84 CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b)); 85 CREATE TABLE t2(x); 86 SELECT c FROM t1 ORDER BY c; 87 } 88} {} 89 90# Six columns of configuration data as follows: 91# 92# i The reference number of the test 93# conf The conflict resolution algorithm on the BEGIN statement 94# cmd An INSERT or REPLACE command to execute against table t1 95# t0 True if there is an error from $cmd 96# t1 Content of "c" column of t1 assuming no error in $cmd 97# t2 Content of "x" column of t2 98# 99foreach {i conf cmd t0 t1 t2} { 100 1 {} INSERT 1 {} 1 101 2 {} {INSERT OR IGNORE} 0 3 1 102 3 {} {INSERT OR REPLACE} 0 4 1 103 4 {} REPLACE 0 4 1 104 5 {} {INSERT OR FAIL} 1 {} 1 105 6 {} {INSERT OR ABORT} 1 {} 1 106 7 {} {INSERT OR ROLLBACK} 1 {} {} 107 8 IGNORE INSERT 0 3 1 108 9 IGNORE {INSERT OR IGNORE} 0 3 1 109 10 IGNORE {INSERT OR REPLACE} 0 4 1 110 11 IGNORE REPLACE 0 4 1 111 12 IGNORE {INSERT OR FAIL} 1 {} 1 112 13 IGNORE {INSERT OR ABORT} 1 {} 1 113 14 IGNORE {INSERT OR ROLLBACK} 1 {} {} 114 15 REPLACE INSERT 0 4 1 115 16 FAIL INSERT 1 {} 1 116 17 ABORT INSERT 1 {} 1 117 18 ROLLBACK INSERT 1 {} {} 118} { 119 do_test conflict-2.$i { 120 if {$conf!=""} {set conf "ON CONFLICT $conf"} 121 set r0 [catch {execsql [subst { 122 DELETE FROM t1; 123 DELETE FROM t2; 124 INSERT INTO t1 VALUES(1,2,3); 125 BEGIN $conf; 126 INSERT INTO t2 VALUES(1); 127 $cmd INTO t1 VALUES(1,2,4); 128 }]} r1] 129 catch {execsql {COMMIT}} 130 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 131 set r2 [execsql {SELECT x FROM t2}] 132 list $r0 $r1 $r2 133 } [list $t0 $t1 $t2] 134} 135 136# Create tables for the first group of tests. 137# 138do_test conflict-3.0 { 139 execsql { 140 DROP TABLE t1; 141 DROP TABLE t2; 142 CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b)); 143 CREATE TABLE t2(x); 144 SELECT c FROM t1 ORDER BY c; 145 } 146} {} 147 148# Six columns of configuration data as follows: 149# 150# i The reference number of the test 151# conf The conflict resolution algorithm on the BEGIN statement 152# cmd An INSERT or REPLACE command to execute against table t1 153# t0 True if there is an error from $cmd 154# t1 Content of "c" column of t1 assuming no error in $cmd 155# t2 Content of "x" column of t2 156# 157foreach {i conf cmd t0 t1 t2} { 158 1 {} INSERT 1 {} 1 159 2 {} {INSERT OR IGNORE} 0 3 1 160 3 {} {INSERT OR REPLACE} 0 4 1 161 4 {} REPLACE 0 4 1 162 5 {} {INSERT OR FAIL} 1 {} 1 163 6 {} {INSERT OR ABORT} 1 {} 1 164 7 {} {INSERT OR ROLLBACK} 1 {} {} 165 8 IGNORE INSERT 0 3 1 166 9 IGNORE {INSERT OR IGNORE} 0 3 1 167 10 IGNORE {INSERT OR REPLACE} 0 4 1 168 11 IGNORE REPLACE 0 4 1 169 12 IGNORE {INSERT OR FAIL} 1 {} 1 170 13 IGNORE {INSERT OR ABORT} 1 {} 1 171 14 IGNORE {INSERT OR ROLLBACK} 1 {} {} 172 15 REPLACE INSERT 0 4 1 173 16 FAIL INSERT 1 {} 1 174 17 ABORT INSERT 1 {} 1 175 18 ROLLBACK INSERT 1 {} {} 176} { 177 do_test conflict-3.$i { 178 if {$conf!=""} {set conf "ON CONFLICT $conf"} 179 set r0 [catch {execsql [subst { 180 DELETE FROM t1; 181 DELETE FROM t2; 182 INSERT INTO t1 VALUES(1,2,3); 183 BEGIN $conf; 184 INSERT INTO t2 VALUES(1); 185 $cmd INTO t1 VALUES(1,2,4); 186 }]} r1] 187 catch {execsql {COMMIT}} 188 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 189 set r2 [execsql {SELECT x FROM t2}] 190 list $r0 $r1 $r2 191 } [list $t0 $t1 $t2] 192} 193 194do_test conflict-4.0 { 195 execsql { 196 DROP TABLE t2; 197 CREATE TABLE t2(x); 198 SELECT x FROM t2; 199 } 200} {} 201 202# Six columns of configuration data as follows: 203# 204# i The reference number of the test 205# conf1 The conflict resolution algorithm on the UNIQUE constraint 206# conf2 The conflict resolution algorithm on the BEGIN statement 207# cmd An INSERT or REPLACE command to execute against table t1 208# t0 True if there is an error from $cmd 209# t1 Content of "c" column of t1 assuming no error in $cmd 210# t2 Content of "x" column of t2 211# 212foreach {i conf1 conf2 cmd t0 t1 t2} { 213 1 {} {} INSERT 1 {} 1 214 2 REPLACE {} INSERT 0 4 1 215 3 IGNORE {} INSERT 0 3 1 216 4 FAIL {} INSERT 1 {} 1 217 5 ABORT {} INSERT 1 {} 1 218 6 ROLLBACK {} INSERT 1 {} {} 219 7 REPLACE {} {INSERT OR IGNORE} 0 3 1 220 8 IGNORE {} {INSERT OR REPLACE} 0 4 1 221 9 FAIL {} {INSERT OR IGNORE} 0 3 1 222 10 ABORT {} {INSERT OR REPLACE} 0 4 1 223 11 ROLLBACK {} {INSERT OR IGNORE } 0 3 1 224 12 REPLACE IGNORE INSERT 0 3 1 225 13 IGNORE REPLACE INSERT 0 4 1 226 14 FAIL IGNORE INSERT 0 3 1 227 15 ABORT REPLACE INSERT 0 4 1 228 16 ROLLBACK IGNORE INSERT 0 3 1 229 12 IGNORE REPLACE INSERT 0 4 1 230 13 IGNORE FAIL INSERT 1 {} 1 231 14 IGNORE ABORT INSERT 1 {} 1 232 15 IGNORE ROLLBACK INSERT 1 {} {} 233} { 234 do_test conflict-4.$i { 235 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 236 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} 237 set r0 [catch {execsql [subst { 238 DROP TABLE t1; 239 CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1); 240 DELETE FROM t2; 241 INSERT INTO t1 VALUES(1,2,3); 242 BEGIN $conf2; 243 INSERT INTO t2 VALUES(1); 244 $cmd INTO t1 VALUES(1,2,4); 245 }]} r1] 246 catch {execsql {COMMIT}} 247 if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} 248 set r2 [execsql {SELECT x FROM t2}] 249 list $r0 $r1 $r2 250 } [list $t0 $t1 $t2] 251} 252 253do_test conflict-5.0 { 254 execsql { 255 DROP TABLE t2; 256 CREATE TABLE t2(x); 257 SELECT x FROM t2; 258 } 259} {} 260 261# Six columns of configuration data as follows: 262# 263# i The reference number of the test 264# conf1 The conflict resolution algorithm on the NOT NULL constraint 265# conf2 The conflict resolution algorithm on the BEGIN statement 266# cmd An INSERT or REPLACE command to execute against table t1 267# t0 True if there is an error from $cmd 268# t1 Content of "c" column of t1 assuming no error in $cmd 269# t2 Content of "x" column of t2 270# 271foreach {i conf1 conf2 cmd t0 t1 t2} { 272 1 {} {} INSERT 1 {} 1 273 2 REPLACE {} INSERT 0 5 1 274 3 IGNORE {} INSERT 0 {} 1 275 4 FAIL {} INSERT 1 {} 1 276 5 ABORT {} INSERT 1 {} 1 277 6 ROLLBACK {} INSERT 1 {} {} 278 7 REPLACE {} {INSERT OR IGNORE} 0 {} 1 279 8 IGNORE {} {INSERT OR REPLACE} 0 5 1 280 9 FAIL {} {INSERT OR IGNORE} 0 {} 1 281 10 ABORT {} {INSERT OR REPLACE} 0 5 1 282 11 ROLLBACK {} {INSERT OR IGNORE} 0 {} 1 283 12 {} {} {INSERT OR IGNORE} 0 {} 1 284 13 {} {} {INSERT OR REPLACE} 0 5 1 285 14 {} {} {INSERT OR FAIL} 1 {} 1 286 15 {} {} {INSERT OR ABORT} 1 {} 1 287 16 {} {} {INSERT OR ROLLBACK} 1 {} {} 288 17 {} IGNORE INSERT 0 {} 1 289 18 {} REPLACE INSERT 0 5 1 290 19 {} FAIL INSERT 1 {} 1 291 20 {} ABORT INSERT 1 {} 1 292 21 {} ROLLBACK INSERT 1 {} {} 293 22 REPLACE FAIL INSERT 1 {} 1 294 23 IGNORE ROLLBACK INSERT 1 {} {} 295} { 296 if {$t0} {set t1 {t1.c may not be NULL}} 297 do_test conflict-5.$i { 298 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 299 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} 300 set r0 [catch {execsql [subst { 301 DROP TABLE t1; 302 CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5); 303 DELETE FROM t2; 304 BEGIN $conf2; 305 INSERT INTO t2 VALUES(1); 306 $cmd INTO t1 VALUES(1,2,NULL); 307 }]} r1] 308 catch {execsql {COMMIT}} 309 if {!$r0} {set r1 [execsql {SELECT c FROM t1}]} 310 set r2 [execsql {SELECT x FROM t2}] 311 list $r0 $r1 $r2 312 } [list $t0 $t1 $t2] 313} 314 315do_test conflict-6.0 { 316 execsql { 317 DROP TABLE t2; 318 CREATE TABLE t2(a,b,c); 319 INSERT INTO t2 VALUES(1,2,1); 320 INSERT INTO t2 VALUES(2,3,2); 321 INSERT INTO t2 VALUES(3,4,1); 322 INSERT INTO t2 VALUES(4,5,4); 323 SELECT c FROM t2 ORDER BY b; 324 CREATE TABLE t3(x); 325 INSERT INTO t3 VALUES(1); 326 } 327} {1 2 1 4} 328 329# Six columns of configuration data as follows: 330# 331# i The reference number of the test 332# conf1 The conflict resolution algorithm on the UNIQUE constraint 333# conf2 The conflict resolution algorithm on the BEGIN statement 334# cmd An UPDATE command to execute against table t1 335# t0 True if there is an error from $cmd 336# t1 Content of "b" column of t1 assuming no error in $cmd 337# t2 Content of "x" column of t3 338# 339foreach {i conf1 conf2 cmd t0 t1 t2} { 340 1 {} {} UPDATE 1 {6 7 8 9} 1 341 2 REPLACE {} UPDATE 0 {7 6 9} 1 342 3 IGNORE {} UPDATE 0 {6 7 3 9} 1 343 4 FAIL {} UPDATE 1 {6 7 3 4} 1 344 5 ABORT {} UPDATE 1 {1 2 3 4} 1 345 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0 346 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 347 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1 348 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 349 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1 350 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 351 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 352 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1 353 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 354 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 355 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 356 17 {} IGNORE UPDATE 0 {6 7 3 9} 1 357 18 {} REPLACE UPDATE 0 {7 6 9} 1 358 19 {} FAIL UPDATE 1 {6 7 3 4} 1 359 20 {} ABORT UPDATE 1 {1 2 3 4} 1 360 21 {} ROLLBACK UPDATE 1 {1 2 3 4} 0 361 22 REPLACE IGNORE UPDATE 0 {6 7 3 9} 1 362 23 IGNORE REPLACE UPDATE 0 {7 6 9} 1 363 24 REPLACE FAIL UPDATE 1 {6 7 3 4} 1 364 25 IGNORE ABORT UPDATE 1 {1 2 3 4} 1 365 26 REPLACE ROLLBACK UPDATE 1 {1 2 3 4} 0 366} { 367 if {$t0} {set t1 {column a is not unique}} 368 do_test conflict-6.$i { 369 if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} 370 if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} 371 set r0 [catch {execsql [subst { 372 DROP TABLE t1; 373 CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); 374 INSERT INTO t1 SELECT * FROM t2; 375 UPDATE t3 SET x=0; 376 BEGIN $conf2; 377 $cmd t3 SET x=1; 378 $cmd t1 SET b=b*2; 379 $cmd t1 SET a=c+5; 380 }]} r1] 381 catch {execsql {COMMIT}} 382 if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} 383 set r2 [execsql {SELECT x FROM t3}] 384 list $r0 $r1 $r2 385 } [list $t0 $t1 $t2] 386} 387 388# Test to make sure a lot of IGNOREs don't cause a stack overflow 389# 390do_test conflict-7.1 { 391 execsql { 392 DROP TABLE t1; 393 DROP TABLE t2; 394 DROP TABLE t3; 395 CREATE TABLE t1(a unique, b); 396 } 397 for {set i 1} {$i<=50} {incr i} { 398 execsql "INSERT into t1 values($i,[expr {$i+1}]);" 399 } 400 execsql { 401 SELECT count(*), min(a), max(b) FROM t1; 402 } 403} {50 1 51} 404do_test conflict-7.2 { 405 execsql { 406 PRAGMA count_changes=on; 407 UPDATE OR IGNORE t1 SET a=1000; 408 } 409} {1} 410do_test conflict-7.2.1 { 411 db changes 412} {1} 413do_test conflict-7.3 { 414 execsql { 415 SELECT b FROM t1 WHERE a=1000; 416 } 417} {2} 418do_test conflict-7.4 { 419 execsql { 420 SELECT count(*) FROM t1; 421 } 422} {50} 423do_test conflict-7.5 { 424 execsql { 425 PRAGMA count_changes=on; 426 UPDATE OR REPLACE t1 SET a=1001; 427 } 428} {50} 429do_test conflict-7.5.1 { 430 db changes 431} {50} 432do_test conflict-7.6 { 433 execsql { 434 SELECT b FROM t1 WHERE a=1001; 435 } 436} {51} 437do_test conflict-7.7 { 438 execsql { 439 SELECT count(*) FROM t1; 440 } 441} {1} 442do_test conflict-7.7.1 { 443 db changes 444} {0} 445 446# Make sure the row count is right for rows that are ignored on 447# an insert. 448# 449do_test conflict-8.1 { 450 execsql { 451 DELETE FROM t1; 452 INSERT INTO t1 VALUES(1,2); 453 } 454 execsql { 455 INSERT OR IGNORE INTO t1 VALUES(2,3); 456 } 457} {1} 458do_test conflict-8.1.1 { 459 db changes 460} {1} 461do_test conflict-8.2 { 462 execsql { 463 INSERT OR IGNORE INTO t1 VALUES(2,4); 464 } 465} {0} 466do_test conflict-8.2.1 { 467 db changes 468} {0} 469do_test conflict-8.3 { 470 execsql { 471 INSERT OR REPLACE INTO t1 VALUES(2,4); 472 } 473} {1} 474do_test conflict-8.3.1 { 475 db changes 476} {1} 477do_test conflict-8.4 { 478 execsql { 479 INSERT OR IGNORE INTO t1 SELECT * FROM t1; 480 } 481} {0} 482do_test conflict-8.4.1 { 483 db changes 484} {0} 485do_test conflict-8.5 { 486 execsql { 487 INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1; 488 } 489} {2} 490do_test conflict-8.5.1 { 491 db changes 492} {2} 493do_test conflict-8.6 { 494 execsql { 495 INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1; 496 } 497} {3} 498do_test conflict-8.6.1 { 499 db changes 500} {3} 501 502integrity_check conflict-8.99 503 504do_test conflict-9.1 { 505 execsql { 506 PRAGMA count_changes=0; 507 CREATE TABLE t2( 508 a INTEGER UNIQUE ON CONFLICT IGNORE, 509 b INTEGER UNIQUE ON CONFLICT FAIL, 510 c INTEGER UNIQUE ON CONFLICT REPLACE, 511 d INTEGER UNIQUE ON CONFLICT ABORT, 512 e INTEGER UNIQUE ON CONFLICT ROLLBACK 513 ); 514 CREATE TABLE t3(x); 515 INSERT INTO t3 VALUES(1); 516 SELECT * FROM t3; 517 } 518} {1} 519do_test conflict-9.2 { 520 catchsql { 521 INSERT INTO t2 VALUES(1,1,1,1,1); 522 INSERT INTO t2 VALUES(2,2,2,2,2); 523 SELECT * FROM t2; 524 } 525} {0 {1 1 1 1 1 2 2 2 2 2}} 526do_test conflict-9.3 { 527 catchsql { 528 INSERT INTO t2 VALUES(1,3,3,3,3); 529 SELECT * FROM t2; 530 } 531} {0 {1 1 1 1 1 2 2 2 2 2}} 532do_test conflict-9.4 { 533 catchsql { 534 UPDATE t2 SET a=a+1 WHERE a=1; 535 SELECT * FROM t2; 536 } 537} {0 {1 1 1 1 1 2 2 2 2 2}} 538do_test conflict-9.5 { 539 catchsql { 540 INSERT INTO t2 VALUES(3,1,3,3,3); 541 SELECT * FROM t2; 542 } 543} {1 {column b is not unique}} 544do_test conflict-9.6 { 545 catchsql { 546 UPDATE t2 SET b=b+1 WHERE b=1; 547 SELECT * FROM t2; 548 } 549} {1 {column b is not unique}} 550do_test conflict-9.7 { 551 catchsql { 552 BEGIN; 553 UPDATE t3 SET x=x+1; 554 INSERT INTO t2 VALUES(3,1,3,3,3); 555 SELECT * FROM t2; 556 } 557} {1 {column b is not unique}} 558do_test conflict-9.8 { 559 execsql {COMMIT} 560 execsql {SELECT * FROM t3} 561} {2} 562do_test conflict-9.9 { 563 catchsql { 564 BEGIN; 565 UPDATE t3 SET x=x+1; 566 UPDATE t2 SET b=b+1 WHERE b=1; 567 SELECT * FROM t2; 568 } 569} {1 {column b is not unique}} 570do_test conflict-9.10 { 571 execsql {COMMIT} 572 execsql {SELECT * FROM t3} 573} {3} 574do_test conflict-9.11 { 575 catchsql { 576 INSERT INTO t2 VALUES(3,3,3,1,3); 577 SELECT * FROM t2; 578 } 579} {1 {column d is not unique}} 580do_test conflict-9.12 { 581 catchsql { 582 UPDATE t2 SET d=d+1 WHERE d=1; 583 SELECT * FROM t2; 584 } 585} {1 {column d is not unique}} 586do_test conflict-9.13 { 587 catchsql { 588 BEGIN; 589 UPDATE t3 SET x=x+1; 590 INSERT INTO t2 VALUES(3,3,3,1,3); 591 SELECT * FROM t2; 592 } 593} {1 {column d is not unique}} 594do_test conflict-9.14 { 595 execsql {COMMIT} 596 execsql {SELECT * FROM t3} 597} {4} 598do_test conflict-9.15 { 599 catchsql { 600 BEGIN; 601 UPDATE t3 SET x=x+1; 602 UPDATE t2 SET d=d+1 WHERE d=1; 603 SELECT * FROM t2; 604 } 605} {1 {column d is not unique}} 606do_test conflict-9.16 { 607 execsql {COMMIT} 608 execsql {SELECT * FROM t3} 609} {5} 610do_test conflict-9.17 { 611 catchsql { 612 INSERT INTO t2 VALUES(3,3,3,3,1); 613 SELECT * FROM t2; 614 } 615} {1 {column e is not unique}} 616do_test conflict-9.18 { 617 catchsql { 618 UPDATE t2 SET e=e+1 WHERE e=1; 619 SELECT * FROM t2; 620 } 621} {1 {column e is not unique}} 622do_test conflict-9.19 { 623 catchsql { 624 BEGIN; 625 UPDATE t3 SET x=x+1; 626 INSERT INTO t2 VALUES(3,3,3,3,1); 627 SELECT * FROM t2; 628 } 629} {1 {column e is not unique}} 630do_test conflict-9.20 { 631 catch {execsql {COMMIT}} 632 execsql {SELECT * FROM t3} 633} {5} 634do_test conflict-9.21 { 635 catchsql { 636 BEGIN; 637 UPDATE t3 SET x=x+1; 638 UPDATE t2 SET e=e+1 WHERE e=1; 639 SELECT * FROM t2; 640 } 641} {1 {column e is not unique}} 642do_test conflict-9.22 { 643 catch {execsql {COMMIT}} 644 execsql {SELECT * FROM t3} 645} {5} 646do_test conflict-9.23 { 647 catchsql { 648 INSERT INTO t2 VALUES(3,3,1,3,3); 649 SELECT * FROM t2; 650 } 651} {0 {2 2 2 2 2 3 3 1 3 3}} 652do_test conflict-9.24 { 653 catchsql { 654 UPDATE t2 SET c=c-1 WHERE c=2; 655 SELECT * FROM t2; 656 } 657} {0 {2 2 1 2 2}} 658do_test conflict-9.25 { 659 catchsql { 660 BEGIN; 661 UPDATE t3 SET x=x+1; 662 INSERT INTO t2 VALUES(3,3,1,3,3); 663 SELECT * FROM t2; 664 } 665} {0 {3 3 1 3 3}} 666do_test conflict-9.26 { 667 catch {execsql {COMMIT}} 668 execsql {SELECT * FROM t3} 669} {6} 670 671do_test conflict-10.1 { 672 catchsql { 673 DELETE FROM t1; 674 BEGIN ON CONFLICT ROLLBACK; 675 INSERT INTO t1 VALUES(1,2); 676 INSERT INTO t1 VALUES(1,3); 677 COMMIT; 678 } 679 execsql {SELECT * FROM t1} 680} {} 681do_test conflict-10.2 { 682 catchsql { 683 CREATE TABLE t4(x); 684 CREATE UNIQUE INDEX t4x ON t4(x); 685 BEGIN ON CONFLICT ROLLBACK; 686 INSERT INTO t4 VALUES(1); 687 INSERT INTO t4 VALUES(1); 688 COMMIT; 689 } 690 execsql {SELECT * FROM t4} 691} {} 692 693integrity_check conflict-99.0 694 695finish_test 696