# 2013-11-05 # # 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. # # This file focuses on making sure that combinations of REPLACE, # IGNORE, and FAIL conflict resolution play well together. # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable !conflict { finish_test return } do_execsql_test conflict-1.1 { CREATE TABLE t1( a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-1.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-1.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-1.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Replete the tests above, but this time on a table non-INTEGER primary key. # do_execsql_test conflict-2.1 { DROP TABLE t1; CREATE TABLE t1( a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-2.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-2.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-2.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Replete again on a WITHOUT ROWID table. # do_execsql_test conflict-3.1 { DROP TABLE t1; CREATE TABLE t1( a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-3.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-3.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-3.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # do_execsql_test conflict-4.1 { DROP TABLE t1; CREATE TABLE t1( b UNIQUE ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL, a INT PRIMARY KEY ON CONFLICT REPLACE ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-4.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-4.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-4.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # do_execsql_test conflict-5.1 { DROP TABLE t1; CREATE TABLE t1( b UNIQUE ON CONFLICT IGNORE, a INT PRIMARY KEY ON CONFLICT REPLACE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-5.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-5.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-5.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Arrange the table rows in a different order and repeat. # do_execsql_test conflict-6.1 { DROP TABLE t1; CREATE TABLE t1( c UNIQUE ON CONFLICT FAIL, a INT PRIMARY KEY ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-6.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-6.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-6.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test conflict-7.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INTEGER PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-7.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-7.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-7.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test conflict-8.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INT PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-8.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-8.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-8.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test conflict-9.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b INT PRIMARY KEY ON CONFLICT IGNORE, c UNIQUE ON CONFLICT FAIL ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-9.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-9.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-9.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test conflict-10.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c INTEGER PRIMARY KEY ON CONFLICT FAIL ); INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-10.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-10.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-10.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} # Change which column is the PRIMARY KEY # do_execsql_test conflict-11.1 { DROP TABLE t1; CREATE TABLE t1( a UNIQUE ON CONFLICT REPLACE, b UNIQUE ON CONFLICT IGNORE, c PRIMARY KEY ON CONFLICT FAIL ) WITHOUT ROWID; INSERT INTO t1(a,b,c) VALUES(1,2,3), (2,3,4); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert a row that conflicts on column B. The insert should be ignored. # do_execsql_test conflict-11.2 { INSERT INTO t1(a,b,c) VALUES(3,2,5); SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4} # Insert two rows where the second conflicts on C. The first row show go # and and then there should be a constraint error. # do_test conflict-11.3 { catchsql {INSERT INTO t1(a,b,c) VALUES(4,5,6), (5,6,4);} } {1 {UNIQUE constraint failed: t1.c}} do_execsql_test conflict-11.4 { SELECT a,b,c FROM t1 ORDER BY a; } {1 2 3 2 3 4 4 5 6} finish_test