# 2002 January 29 # # The author disclaims copyright to this source code. In place of # a legal notice, here is a blessing: # # May you do good and not evil. # May you find forgiveness for yourself and forgive others. # May you share freely, never taking more than you give. # #*********************************************************************** # This file implements regression tests for SQLite library. # # This file implements tests for the conflict resolution extension # to SQLite. # # $Id: conflict.test,v 1.19 2003/08/05 13:13:39 drh Exp $ set testdir [file dirname $argv0] source $testdir/tester.tcl # Create tables for the first group of tests. # do_test conflict-1.0 { execsql { CREATE TABLE t1(a, b, c, UNIQUE(a,b)); CREATE TABLE t2(x); SELECT c FROM t1 ORDER BY c; } } {} # Six columns of configuration data as follows: # # i The reference number of the test # conf The conflict resolution algorithm on the BEGIN statement # cmd An INSERT or REPLACE command to execute against table t1 # t0 True if there is an error from $cmd # t1 Content of "c" column of t1 assuming no error in $cmd # t2 Content of "x" column of t2 # foreach {i conf cmd t0 t1 t2} { 1 {} INSERT 1 {} 1 2 {} {INSERT OR IGNORE} 0 3 1 3 {} {INSERT OR REPLACE} 0 4 1 4 {} REPLACE 0 4 1 5 {} {INSERT OR FAIL} 1 {} 1 6 {} {INSERT OR ABORT} 1 {} 1 7 {} {INSERT OR ROLLBACK} 1 {} {} 8 IGNORE INSERT 0 3 1 9 IGNORE {INSERT OR IGNORE} 0 3 1 10 IGNORE {INSERT OR REPLACE} 0 4 1 11 IGNORE REPLACE 0 4 1 12 IGNORE {INSERT OR FAIL} 1 {} 1 13 IGNORE {INSERT OR ABORT} 1 {} 1 14 IGNORE {INSERT OR ROLLBACK} 1 {} {} 15 REPLACE INSERT 0 4 1 16 FAIL INSERT 1 {} 1 17 ABORT INSERT 1 {} 1 18 ROLLBACK INSERT 1 {} {} } { do_test conflict-1.$i { if {$conf!=""} {set conf "ON CONFLICT $conf"} set r0 [catch {execsql [subst { DELETE FROM t1; DELETE FROM t2; INSERT INTO t1 VALUES(1,2,3); BEGIN $conf; INSERT INTO t2 VALUES(1); $cmd INTO t1 VALUES(1,2,4); }]} r1] catch {execsql {COMMIT}} if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} set r2 [execsql {SELECT x FROM t2}] list $r0 $r1 $r2 } [list $t0 $t1 $t2] } # Create tables for the first group of tests. # do_test conflict-2.0 { execsql { DROP TABLE t1; DROP TABLE t2; CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(a,b)); CREATE TABLE t2(x); SELECT c FROM t1 ORDER BY c; } } {} # Six columns of configuration data as follows: # # i The reference number of the test # conf The conflict resolution algorithm on the BEGIN statement # cmd An INSERT or REPLACE command to execute against table t1 # t0 True if there is an error from $cmd # t1 Content of "c" column of t1 assuming no error in $cmd # t2 Content of "x" column of t2 # foreach {i conf cmd t0 t1 t2} { 1 {} INSERT 1 {} 1 2 {} {INSERT OR IGNORE} 0 3 1 3 {} {INSERT OR REPLACE} 0 4 1 4 {} REPLACE 0 4 1 5 {} {INSERT OR FAIL} 1 {} 1 6 {} {INSERT OR ABORT} 1 {} 1 7 {} {INSERT OR ROLLBACK} 1 {} {} 8 IGNORE INSERT 0 3 1 9 IGNORE {INSERT OR IGNORE} 0 3 1 10 IGNORE {INSERT OR REPLACE} 0 4 1 11 IGNORE REPLACE 0 4 1 12 IGNORE {INSERT OR FAIL} 1 {} 1 13 IGNORE {INSERT OR ABORT} 1 {} 1 14 IGNORE {INSERT OR ROLLBACK} 1 {} {} 15 REPLACE INSERT 0 4 1 16 FAIL INSERT 1 {} 1 17 ABORT INSERT 1 {} 1 18 ROLLBACK INSERT 1 {} {} } { do_test conflict-2.$i { if {$conf!=""} {set conf "ON CONFLICT $conf"} set r0 [catch {execsql [subst { DELETE FROM t1; DELETE FROM t2; INSERT INTO t1 VALUES(1,2,3); BEGIN $conf; INSERT INTO t2 VALUES(1); $cmd INTO t1 VALUES(1,2,4); }]} r1] catch {execsql {COMMIT}} if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} set r2 [execsql {SELECT x FROM t2}] list $r0 $r1 $r2 } [list $t0 $t1 $t2] } # Create tables for the first group of tests. # do_test conflict-3.0 { execsql { DROP TABLE t1; DROP TABLE t2; CREATE TABLE t1(a, b, c INTEGER, PRIMARY KEY(c), UNIQUE(a,b)); CREATE TABLE t2(x); SELECT c FROM t1 ORDER BY c; } } {} # Six columns of configuration data as follows: # # i The reference number of the test # conf The conflict resolution algorithm on the BEGIN statement # cmd An INSERT or REPLACE command to execute against table t1 # t0 True if there is an error from $cmd # t1 Content of "c" column of t1 assuming no error in $cmd # t2 Content of "x" column of t2 # foreach {i conf cmd t0 t1 t2} { 1 {} INSERT 1 {} 1 2 {} {INSERT OR IGNORE} 0 3 1 3 {} {INSERT OR REPLACE} 0 4 1 4 {} REPLACE 0 4 1 5 {} {INSERT OR FAIL} 1 {} 1 6 {} {INSERT OR ABORT} 1 {} 1 7 {} {INSERT OR ROLLBACK} 1 {} {} 8 IGNORE INSERT 0 3 1 9 IGNORE {INSERT OR IGNORE} 0 3 1 10 IGNORE {INSERT OR REPLACE} 0 4 1 11 IGNORE REPLACE 0 4 1 12 IGNORE {INSERT OR FAIL} 1 {} 1 13 IGNORE {INSERT OR ABORT} 1 {} 1 14 IGNORE {INSERT OR ROLLBACK} 1 {} {} 15 REPLACE INSERT 0 4 1 16 FAIL INSERT 1 {} 1 17 ABORT INSERT 1 {} 1 18 ROLLBACK INSERT 1 {} {} } { do_test conflict-3.$i { if {$conf!=""} {set conf "ON CONFLICT $conf"} set r0 [catch {execsql [subst { DELETE FROM t1; DELETE FROM t2; INSERT INTO t1 VALUES(1,2,3); BEGIN $conf; INSERT INTO t2 VALUES(1); $cmd INTO t1 VALUES(1,2,4); }]} r1] catch {execsql {COMMIT}} if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} set r2 [execsql {SELECT x FROM t2}] list $r0 $r1 $r2 } [list $t0 $t1 $t2] } do_test conflict-4.0 { execsql { DROP TABLE t2; CREATE TABLE t2(x); SELECT x FROM t2; } } {} # Six columns of configuration data as follows: # # i The reference number of the test # conf1 The conflict resolution algorithm on the UNIQUE constraint # conf2 The conflict resolution algorithm on the BEGIN statement # cmd An INSERT or REPLACE command to execute against table t1 # t0 True if there is an error from $cmd # t1 Content of "c" column of t1 assuming no error in $cmd # t2 Content of "x" column of t2 # foreach {i conf1 conf2 cmd t0 t1 t2} { 1 {} {} INSERT 1 {} 1 2 REPLACE {} INSERT 0 4 1 3 IGNORE {} INSERT 0 3 1 4 FAIL {} INSERT 1 {} 1 5 ABORT {} INSERT 1 {} 1 6 ROLLBACK {} INSERT 1 {} {} 7 REPLACE {} {INSERT OR IGNORE} 0 3 1 8 IGNORE {} {INSERT OR REPLACE} 0 4 1 9 FAIL {} {INSERT OR IGNORE} 0 3 1 10 ABORT {} {INSERT OR REPLACE} 0 4 1 11 ROLLBACK {} {INSERT OR IGNORE } 0 3 1 12 REPLACE IGNORE INSERT 0 3 1 13 IGNORE REPLACE INSERT 0 4 1 14 FAIL IGNORE INSERT 0 3 1 15 ABORT REPLACE INSERT 0 4 1 16 ROLLBACK IGNORE INSERT 0 3 1 12 IGNORE REPLACE INSERT 0 4 1 13 IGNORE FAIL INSERT 1 {} 1 14 IGNORE ABORT INSERT 1 {} 1 15 IGNORE ROLLBACK INSERT 1 {} {} } { do_test conflict-4.$i { if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} set r0 [catch {execsql [subst { DROP TABLE t1; CREATE TABLE t1(a,b,c,UNIQUE(a,b) $conf1); DELETE FROM t2; INSERT INTO t1 VALUES(1,2,3); BEGIN $conf2; INSERT INTO t2 VALUES(1); $cmd INTO t1 VALUES(1,2,4); }]} r1] catch {execsql {COMMIT}} if {$r0} {set r1 {}} {set r1 [execsql {SELECT c FROM t1}]} set r2 [execsql {SELECT x FROM t2}] list $r0 $r1 $r2 } [list $t0 $t1 $t2] } do_test conflict-5.0 { execsql { DROP TABLE t2; CREATE TABLE t2(x); SELECT x FROM t2; } } {} # Six columns of configuration data as follows: # # i The reference number of the test # conf1 The conflict resolution algorithm on the NOT NULL constraint # conf2 The conflict resolution algorithm on the BEGIN statement # cmd An INSERT or REPLACE command to execute against table t1 # t0 True if there is an error from $cmd # t1 Content of "c" column of t1 assuming no error in $cmd # t2 Content of "x" column of t2 # foreach {i conf1 conf2 cmd t0 t1 t2} { 1 {} {} INSERT 1 {} 1 2 REPLACE {} INSERT 0 5 1 3 IGNORE {} INSERT 0 {} 1 4 FAIL {} INSERT 1 {} 1 5 ABORT {} INSERT 1 {} 1 6 ROLLBACK {} INSERT 1 {} {} 7 REPLACE {} {INSERT OR IGNORE} 0 {} 1 8 IGNORE {} {INSERT OR REPLACE} 0 5 1 9 FAIL {} {INSERT OR IGNORE} 0 {} 1 10 ABORT {} {INSERT OR REPLACE} 0 5 1 11 ROLLBACK {} {INSERT OR IGNORE} 0 {} 1 12 {} {} {INSERT OR IGNORE} 0 {} 1 13 {} {} {INSERT OR REPLACE} 0 5 1 14 {} {} {INSERT OR FAIL} 1 {} 1 15 {} {} {INSERT OR ABORT} 1 {} 1 16 {} {} {INSERT OR ROLLBACK} 1 {} {} 17 {} IGNORE INSERT 0 {} 1 18 {} REPLACE INSERT 0 5 1 19 {} FAIL INSERT 1 {} 1 20 {} ABORT INSERT 1 {} 1 21 {} ROLLBACK INSERT 1 {} {} 22 REPLACE FAIL INSERT 1 {} 1 23 IGNORE ROLLBACK INSERT 1 {} {} } { if {$t0} {set t1 {t1.c may not be NULL}} do_test conflict-5.$i { if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} set r0 [catch {execsql [subst { DROP TABLE t1; CREATE TABLE t1(a,b,c NOT NULL $conf1 DEFAULT 5); DELETE FROM t2; BEGIN $conf2; INSERT INTO t2 VALUES(1); $cmd INTO t1 VALUES(1,2,NULL); }]} r1] catch {execsql {COMMIT}} if {!$r0} {set r1 [execsql {SELECT c FROM t1}]} set r2 [execsql {SELECT x FROM t2}] list $r0 $r1 $r2 } [list $t0 $t1 $t2] } do_test conflict-6.0 { execsql { DROP TABLE t2; CREATE TABLE t2(a,b,c); INSERT INTO t2 VALUES(1,2,1); INSERT INTO t2 VALUES(2,3,2); INSERT INTO t2 VALUES(3,4,1); INSERT INTO t2 VALUES(4,5,4); SELECT c FROM t2 ORDER BY b; CREATE TABLE t3(x); INSERT INTO t3 VALUES(1); } } {1 2 1 4} # Six columns of configuration data as follows: # # i The reference number of the test # conf1 The conflict resolution algorithm on the UNIQUE constraint # conf2 The conflict resolution algorithm on the BEGIN statement # cmd An UPDATE command to execute against table t1 # t0 True if there is an error from $cmd # t1 Content of "b" column of t1 assuming no error in $cmd # t2 Content of "x" column of t3 # foreach {i conf1 conf2 cmd t0 t1 t2} { 1 {} {} UPDATE 1 {6 7 8 9} 1 2 REPLACE {} UPDATE 0 {7 6 9} 1 3 IGNORE {} UPDATE 0 {6 7 3 9} 1 4 FAIL {} UPDATE 1 {6 7 3 4} 1 5 ABORT {} UPDATE 1 {1 2 3 4} 1 6 ROLLBACK {} UPDATE 1 {1 2 3 4} 0 7 REPLACE {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 8 IGNORE {} {UPDATE OR REPLACE} 0 {7 6 9} 1 9 FAIL {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 10 ABORT {} {UPDATE OR REPLACE} 0 {7 6 9} 1 11 ROLLBACK {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 12 {} {} {UPDATE OR IGNORE} 0 {6 7 3 9} 1 13 {} {} {UPDATE OR REPLACE} 0 {7 6 9} 1 14 {} {} {UPDATE OR FAIL} 1 {6 7 3 4} 1 15 {} {} {UPDATE OR ABORT} 1 {1 2 3 4} 1 16 {} {} {UPDATE OR ROLLBACK} 1 {1 2 3 4} 0 17 {} IGNORE UPDATE 0 {6 7 3 9} 1 18 {} REPLACE UPDATE 0 {7 6 9} 1 19 {} FAIL UPDATE 1 {6 7 3 4} 1 20 {} ABORT UPDATE 1 {1 2 3 4} 1 21 {} ROLLBACK UPDATE 1 {1 2 3 4} 0 22 REPLACE IGNORE UPDATE 0 {6 7 3 9} 1 23 IGNORE REPLACE UPDATE 0 {7 6 9} 1 24 REPLACE FAIL UPDATE 1 {6 7 3 4} 1 25 IGNORE ABORT UPDATE 1 {1 2 3 4} 1 26 REPLACE ROLLBACK UPDATE 1 {1 2 3 4} 0 } { if {$t0} {set t1 {column a is not unique}} do_test conflict-6.$i { if {$conf1!=""} {set conf1 "ON CONFLICT $conf1"} if {$conf2!=""} {set conf2 "ON CONFLICT $conf2"} set r0 [catch {execsql [subst { DROP TABLE t1; CREATE TABLE t1(a,b,c, UNIQUE(a) $conf1); INSERT INTO t1 SELECT * FROM t2; UPDATE t3 SET x=0; BEGIN $conf2; $cmd t3 SET x=1; $cmd t1 SET b=b*2; $cmd t1 SET a=c+5; }]} r1] catch {execsql {COMMIT}} if {!$r0} {set r1 [execsql {SELECT a FROM t1 ORDER BY b}]} set r2 [execsql {SELECT x FROM t3}] list $r0 $r1 $r2 } [list $t0 $t1 $t2] } # Test to make sure a lot of IGNOREs don't cause a stack overflow # do_test conflict-7.1 { execsql { DROP TABLE t1; DROP TABLE t2; DROP TABLE t3; CREATE TABLE t1(a unique, b); } for {set i 1} {$i<=50} {incr i} { execsql "INSERT into t1 values($i,[expr {$i+1}]);" } execsql { SELECT count(*), min(a), max(b) FROM t1; } } {50 1 51} do_test conflict-7.2 { execsql { PRAGMA count_changes=on; UPDATE OR IGNORE t1 SET a=1000; } } {1} do_test conflict-7.2.1 { db changes } {1} do_test conflict-7.3 { execsql { SELECT b FROM t1 WHERE a=1000; } } {2} do_test conflict-7.4 { execsql { SELECT count(*) FROM t1; } } {50} do_test conflict-7.5 { execsql { PRAGMA count_changes=on; UPDATE OR REPLACE t1 SET a=1001; } } {50} do_test conflict-7.5.1 { db changes } {50} do_test conflict-7.6 { execsql { SELECT b FROM t1 WHERE a=1001; } } {51} do_test conflict-7.7 { execsql { SELECT count(*) FROM t1; } } {1} do_test conflict-7.7.1 { db changes } {0} # Make sure the row count is right for rows that are ignored on # an insert. # do_test conflict-8.1 { execsql { DELETE FROM t1; INSERT INTO t1 VALUES(1,2); } execsql { INSERT OR IGNORE INTO t1 VALUES(2,3); } } {1} do_test conflict-8.1.1 { db changes } {1} do_test conflict-8.2 { execsql { INSERT OR IGNORE INTO t1 VALUES(2,4); } } {0} do_test conflict-8.2.1 { db changes } {0} do_test conflict-8.3 { execsql { INSERT OR REPLACE INTO t1 VALUES(2,4); } } {1} do_test conflict-8.3.1 { db changes } {1} do_test conflict-8.4 { execsql { INSERT OR IGNORE INTO t1 SELECT * FROM t1; } } {0} do_test conflict-8.4.1 { db changes } {0} do_test conflict-8.5 { execsql { INSERT OR IGNORE INTO t1 SELECT a+2,b+2 FROM t1; } } {2} do_test conflict-8.5.1 { db changes } {2} do_test conflict-8.6 { execsql { INSERT OR IGNORE INTO t1 SELECT a+3,b+3 FROM t1; } } {3} do_test conflict-8.6.1 { db changes } {3} integrity_check conflict-8.99 do_test conflict-9.1 { execsql { PRAGMA count_changes=0; CREATE TABLE t2( a INTEGER UNIQUE ON CONFLICT IGNORE, b INTEGER UNIQUE ON CONFLICT FAIL, c INTEGER UNIQUE ON CONFLICT REPLACE, d INTEGER UNIQUE ON CONFLICT ABORT, e INTEGER UNIQUE ON CONFLICT ROLLBACK ); CREATE TABLE t3(x); INSERT INTO t3 VALUES(1); SELECT * FROM t3; } } {1} do_test conflict-9.2 { catchsql { INSERT INTO t2 VALUES(1,1,1,1,1); INSERT INTO t2 VALUES(2,2,2,2,2); SELECT * FROM t2; } } {0 {1 1 1 1 1 2 2 2 2 2}} do_test conflict-9.3 { catchsql { INSERT INTO t2 VALUES(1,3,3,3,3); SELECT * FROM t2; } } {0 {1 1 1 1 1 2 2 2 2 2}} do_test conflict-9.4 { catchsql { UPDATE t2 SET a=a+1 WHERE a=1; SELECT * FROM t2; } } {0 {1 1 1 1 1 2 2 2 2 2}} do_test conflict-9.5 { catchsql { INSERT INTO t2 VALUES(3,1,3,3,3); SELECT * FROM t2; } } {1 {column b is not unique}} do_test conflict-9.6 { catchsql { UPDATE t2 SET b=b+1 WHERE b=1; SELECT * FROM t2; } } {1 {column b is not unique}} do_test conflict-9.7 { catchsql { BEGIN; UPDATE t3 SET x=x+1; INSERT INTO t2 VALUES(3,1,3,3,3); SELECT * FROM t2; } } {1 {column b is not unique}} do_test conflict-9.8 { execsql {COMMIT} execsql {SELECT * FROM t3} } {2} do_test conflict-9.9 { catchsql { BEGIN; UPDATE t3 SET x=x+1; UPDATE t2 SET b=b+1 WHERE b=1; SELECT * FROM t2; } } {1 {column b is not unique}} do_test conflict-9.10 { execsql {COMMIT} execsql {SELECT * FROM t3} } {3} do_test conflict-9.11 { catchsql { INSERT INTO t2 VALUES(3,3,3,1,3); SELECT * FROM t2; } } {1 {column d is not unique}} do_test conflict-9.12 { catchsql { UPDATE t2 SET d=d+1 WHERE d=1; SELECT * FROM t2; } } {1 {column d is not unique}} do_test conflict-9.13 { catchsql { BEGIN; UPDATE t3 SET x=x+1; INSERT INTO t2 VALUES(3,3,3,1,3); SELECT * FROM t2; } } {1 {column d is not unique}} do_test conflict-9.14 { execsql {COMMIT} execsql {SELECT * FROM t3} } {4} do_test conflict-9.15 { catchsql { BEGIN; UPDATE t3 SET x=x+1; UPDATE t2 SET d=d+1 WHERE d=1; SELECT * FROM t2; } } {1 {column d is not unique}} do_test conflict-9.16 { execsql {COMMIT} execsql {SELECT * FROM t3} } {5} do_test conflict-9.17 { catchsql { INSERT INTO t2 VALUES(3,3,3,3,1); SELECT * FROM t2; } } {1 {column e is not unique}} do_test conflict-9.18 { catchsql { UPDATE t2 SET e=e+1 WHERE e=1; SELECT * FROM t2; } } {1 {column e is not unique}} do_test conflict-9.19 { catchsql { BEGIN; UPDATE t3 SET x=x+1; INSERT INTO t2 VALUES(3,3,3,3,1); SELECT * FROM t2; } } {1 {column e is not unique}} do_test conflict-9.20 { catch {execsql {COMMIT}} execsql {SELECT * FROM t3} } {5} do_test conflict-9.21 { catchsql { BEGIN; UPDATE t3 SET x=x+1; UPDATE t2 SET e=e+1 WHERE e=1; SELECT * FROM t2; } } {1 {column e is not unique}} do_test conflict-9.22 { catch {execsql {COMMIT}} execsql {SELECT * FROM t3} } {5} do_test conflict-9.23 { catchsql { INSERT INTO t2 VALUES(3,3,1,3,3); SELECT * FROM t2; } } {0 {2 2 2 2 2 3 3 1 3 3}} do_test conflict-9.24 { catchsql { UPDATE t2 SET c=c-1 WHERE c=2; SELECT * FROM t2; } } {0 {2 2 1 2 2}} do_test conflict-9.25 { catchsql { BEGIN; UPDATE t3 SET x=x+1; INSERT INTO t2 VALUES(3,3,1,3,3); SELECT * FROM t2; } } {0 {3 3 1 3 3}} do_test conflict-9.26 { catch {execsql {COMMIT}} execsql {SELECT * FROM t3} } {6} do_test conflict-10.1 { catchsql { DELETE FROM t1; BEGIN ON CONFLICT ROLLBACK; INSERT INTO t1 VALUES(1,2); INSERT INTO t1 VALUES(1,3); COMMIT; } execsql {SELECT * FROM t1} } {} do_test conflict-10.2 { catchsql { CREATE TABLE t4(x); CREATE UNIQUE INDEX t4x ON t4(x); BEGIN ON CONFLICT ROLLBACK; INSERT INTO t4 VALUES(1); INSERT INTO t4 VALUES(1); COMMIT; } execsql {SELECT * FROM t4} } {} integrity_check conflict-99.0 finish_test