Annotation of embedaddon/sqlite3/test/conflict.test, revision 1.1
1.1 ! misho 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
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to conflict.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / test