# 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