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