mirror of
https://github.com/sqlite/sqlite.git
synced 2026-10-23 21:49:02 +03:00
676 lines
15 KiB
Plaintext
Executable File
676 lines
15 KiB
Plaintext
Executable File
# 2026 May 18
|
|
#
|
|
# 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.
|
|
#
|
|
|
|
if {![info exists testdir]} {
|
|
set testdir [file join [file dirname [info script]] .. .. test]
|
|
}
|
|
source [file join [file dirname [info script]] session_common.tcl]
|
|
source $testdir/tester.tcl
|
|
ifcapable !session {finish_test; return}
|
|
|
|
set testprefix sessionconflict2
|
|
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 1.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(a PRIMARY KEY, b, c UNIQUE);
|
|
INSERT INTO t1 VALUES(1, 1, 1);
|
|
INSERT INTO t1 VALUES(2, 2, 2);
|
|
INSERT INTO t1 VALUES(3, 3, 3);
|
|
}
|
|
} {}
|
|
|
|
do_test 1.1 {
|
|
do_then_apply_sql {
|
|
UPDATE t1 SET c=NULL WHERE a=1;
|
|
UPDATE t1 SET c=1 WHERE a=3;
|
|
UPDATE t1 SET c=3 WHERE a=1;
|
|
}
|
|
} {}
|
|
|
|
do_execsql_test -db db 1.2 {
|
|
SELECT rowid, * FROM t1
|
|
} {
|
|
1 1 1 3
|
|
2 2 2 2
|
|
3 3 3 1
|
|
}
|
|
|
|
do_execsql_test -db db2 1.3 {
|
|
SELECT rowid, * FROM t1
|
|
} {
|
|
1 1 1 3
|
|
2 2 2 2
|
|
3 3 3 1
|
|
}
|
|
|
|
#--------------------------------------------------------------------------
|
|
reset_db
|
|
db2 close
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 2.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(a PRIMARY KEY, b, c UNIQUE) WITHOUT ROWID;
|
|
INSERT INTO t1 VALUES(1, 1, 1);
|
|
INSERT INTO t1 VALUES(2, 2, 2);
|
|
INSERT INTO t1 VALUES(3, 3, 3);
|
|
}
|
|
} {}
|
|
|
|
do_test 2.1 {
|
|
do_then_apply_sql {
|
|
UPDATE t1 SET c=NULL WHERE a=1;
|
|
UPDATE t1 SET c=1 WHERE a=3;
|
|
UPDATE t1 SET c=3 WHERE a=1;
|
|
}
|
|
} {}
|
|
|
|
do_execsql_test -db db 2.2 {
|
|
SELECT * FROM t1
|
|
} {
|
|
1 1 3
|
|
2 2 2
|
|
3 3 1
|
|
}
|
|
|
|
do_execsql_test -db db2 2.3 {
|
|
SELECT * FROM t1
|
|
} {
|
|
1 1 3
|
|
2 2 2
|
|
3 3 1
|
|
}
|
|
|
|
#--------------------------------------------------------------------------
|
|
reset_db
|
|
db2 close
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 3.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c UNIQUE);
|
|
INSERT INTO t1 VALUES(1, 1, 1);
|
|
INSERT INTO t1 VALUES(2, 2, 2);
|
|
INSERT INTO t1 VALUES(3, 3, 3);
|
|
}
|
|
} {}
|
|
|
|
do_test 3.1 {
|
|
do_then_apply_sql {
|
|
UPDATE t1 SET c=NULL WHERE a=1;
|
|
UPDATE t1 SET c=1 WHERE a=3;
|
|
UPDATE t1 SET c=3 WHERE a=1;
|
|
}
|
|
} {}
|
|
|
|
do_execsql_test -db db 3.2 {
|
|
SELECT rowid, * FROM t1
|
|
} {
|
|
1 1 1 3
|
|
2 2 2 2
|
|
3 3 3 1
|
|
}
|
|
|
|
do_execsql_test -db db2 3.3 {
|
|
SELECT rowid, * FROM t1
|
|
} {
|
|
1 1 1 3
|
|
2 2 2 2
|
|
3 3 3 1
|
|
}
|
|
|
|
#-------------------------------------------------------------------------
|
|
db2 close
|
|
reset_db
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
set ::conflict_list [list]
|
|
proc xConflict {args} {
|
|
lappend ::conflict_list $args
|
|
return "OMIT"
|
|
}
|
|
|
|
proc do_conflict_test {tn bNoUpdateLoop script clist} {
|
|
|
|
uplevel [list do_test $tn.1 [subst -nocommands {
|
|
sqlite3session S db "main"
|
|
S attach *
|
|
eval {$script}
|
|
set ::changeset [S changeset]
|
|
S delete
|
|
}] {}]
|
|
|
|
if {$bNoUpdateLoop} {
|
|
uplevel [list do_test $tn.2.no {
|
|
set ::conflict_list [list]
|
|
sqlite3changeset_apply_v2 -noupdateloop db2 $::changeset xConflict
|
|
set ::conflict_list
|
|
} [list {*}$clist]]
|
|
} else {
|
|
uplevel [list do_test $tn.2 {
|
|
set ::conflict_list [list]
|
|
sqlite3changeset_apply_v2 db2 $::changeset xConflict
|
|
set ::conflict_list
|
|
} [list {*}$clist]]
|
|
}
|
|
}
|
|
|
|
do_test 4.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b, c UNIQUE, d UNIQUE);
|
|
WITH s(i) AS (
|
|
SELECT 1 UNION ALL SELECT i+1 FROM s WHERE i<10
|
|
)
|
|
INSERT INTO t1 SELECT i, i, i, i FROM s;
|
|
}
|
|
} {}
|
|
|
|
proc swap {tbl pkcol valcol pk1 pk2} {
|
|
set val1 [db one "SELECT $valcol FROM $tbl WHERE $pkcol = \$pk1"]
|
|
set val2 [db one "SELECT $valcol FROM $tbl WHERE $pkcol = \$pk2"]
|
|
|
|
db eval "
|
|
UPDATE $tbl SET $valcol = NULL WHERE $pkcol IN (\$pk1, \$pk2);
|
|
UPDATE $tbl SET $valcol = \$val2 WHERE $pkcol = \$pk1;
|
|
UPDATE $tbl SET $valcol = \$val1 WHERE $pkcol = \$pk2;
|
|
"
|
|
}
|
|
|
|
do_conflict_test 4.1.1 0 {
|
|
swap t1 a c 4 5
|
|
swap t1 a c 2 3
|
|
swap t1 a c 8 1
|
|
} {
|
|
}
|
|
|
|
do_execsql_test -db db 4.1.2 {
|
|
SELECT a, c FROM t1
|
|
} {
|
|
1 8 2 3 3 2 4 5 5 4 6 6 7 7 8 1 9 9 10 10
|
|
}
|
|
do_execsql_test -db db2 4.1.3 {
|
|
SELECT a, c FROM t1
|
|
} {
|
|
1 8 2 3 3 2 4 5 5 4 6 6 7 7 8 1 9 9 10 10
|
|
}
|
|
|
|
do_conflict_test 4.2.1 0 {
|
|
swap t1 a d 10 9
|
|
swap t1 a d 8 7
|
|
swap t1 a d 7 6
|
|
swap t1 a d 5 4
|
|
swap t1 a d 4 8
|
|
} {
|
|
}
|
|
|
|
do_execsql_test -db db 4.2.2 {
|
|
SELECT a, d FROM t1
|
|
} {
|
|
1 1 2 2 3 3 4 7 5 4 6 8 7 6 8 5 9 10 10 9
|
|
}
|
|
do_execsql_test -db db2 4.2.3 {
|
|
SELECT a, d FROM t1
|
|
} {
|
|
1 1 2 2 3 3 4 7 5 4 6 8 7 6 8 5 9 10 10 9
|
|
}
|
|
|
|
do_execsql_test -db db2 4.3 {
|
|
INSERT INTO t1(a, b, c, d) VALUES(11, 11, 11, 11);
|
|
}
|
|
|
|
do_conflict_test 4.3.1 0 {
|
|
swap t1 a c 3 6
|
|
db eval { UPDATE t1 SET d=11 WHERE a=10; }
|
|
swap t1 a d 4 8
|
|
} {
|
|
{UPDATE t1 CONSTRAINT {i 10 {} {} {} {} i 9} {{} {} {} {} {} {} i 11}}
|
|
}
|
|
|
|
do_conflict_test 4.3.2 0 {
|
|
swap t1 a c 1 2
|
|
swap t1 a c 3 4
|
|
db eval { UPDATE t1 SET c=11 WHERE a=10; }
|
|
swap t1 a d 5 6
|
|
swap t1 a d 4 5
|
|
} {
|
|
{UPDATE t1 CONSTRAINT {i 10 {} {} i 10 {} {}} {{} {} {} {} i 11 {} {}}}
|
|
}
|
|
|
|
do_conflict_test 4.3.3 0 {
|
|
swap t1 a c 1 2
|
|
swap t1 a c 2 3
|
|
swap t1 a c 3 4
|
|
swap t1 a c 4 1
|
|
} {
|
|
}
|
|
db2 close
|
|
|
|
#-------------------------------------------------------------------------
|
|
reset_db
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 5.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(a INT PRIMARY KEY, b UNIQUE);
|
|
INSERT INTO t1 VALUES('one', 'one');
|
|
INSERT INTO t1 VALUES('two', 'two');
|
|
INSERT INTO t1 VALUES('three', 'three');
|
|
INSERT INTO t1 VALUES('four', 'four');
|
|
INSERT INTO t1 VALUES('five', 'five');
|
|
}
|
|
} {}
|
|
|
|
do_conflict_test 5.1 1 {
|
|
} {
|
|
}
|
|
|
|
do_conflict_test 5.2 1 {
|
|
swap t1 a b one two
|
|
} {
|
|
{UPDATE t1 CONSTRAINT {t two t two} {{} {} t one}}
|
|
{UPDATE t1 CONSTRAINT {t one t one} {{} {} t two}}
|
|
}
|
|
|
|
do_conflict_test 5.2 0 {
|
|
swap t1 a b four five
|
|
} {
|
|
}
|
|
|
|
db2 close
|
|
|
|
#-------------------------------------------------------------------------
|
|
#
|
|
reset_db
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 6.0 {
|
|
do_common_sql {
|
|
PRAGMA foreign_keys=ON;
|
|
CREATE TABLE parent(
|
|
id INTEGER PRIMARY KEY,
|
|
code TEXT NOT NULL UNIQUE
|
|
);
|
|
CREATE TABLE child(
|
|
parent_id INTEGER PRIMARY KEY
|
|
REFERENCES parent(id) ON DELETE CASCADE,
|
|
payload TEXT NOT NULL
|
|
);
|
|
INSERT INTO parent VALUES(1, 'A'), (2, 'B');
|
|
INSERT INTO child VALUES(1, 'child A'), (2, 'child B');
|
|
}
|
|
} {}
|
|
|
|
do_test 6.1 {
|
|
do_then_apply_sql {
|
|
UPDATE parent SET code='temporary' WHERE id=1;
|
|
UPDATE parent SET code='A' WHERE id=2;
|
|
UPDATE parent SET code='B' WHERE id=1;
|
|
}
|
|
} {}
|
|
|
|
do_test 6.2 {
|
|
compare_db db db2
|
|
} {}
|
|
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Test that the ON DELETE actions of a foreign key are not triggered when
|
|
# the update loop is used to resolve a constraint conflict.
|
|
#
|
|
db2 close
|
|
reset_db
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 7.0 {
|
|
do_common_sql {
|
|
PRAGMA foreign_keys=ON;
|
|
CREATE TABLE parent(id INTEGER PRIMARY KEY, code TEXT UNIQUE);
|
|
CREATE TABLE child(
|
|
cid INTEGER PRIMARY KEY,
|
|
pid INTEGER REFERENCES parent(id) ON DELETE SET NULL
|
|
);
|
|
INSERT INTO parent VALUES(1, 'A'), (2, 'B'), (3, 'C');
|
|
INSERT INTO child VALUES(10, 1), (20, 2), (30, 3);
|
|
}
|
|
} {}
|
|
|
|
do_test 7.1 {
|
|
do_then_apply_sql {
|
|
UPDATE parent SET code=NULL WHERE id IN (1, 2, 3);
|
|
UPDATE parent SET code='A' WHERE id=2;
|
|
UPDATE parent SET code='B' WHERE id=3;
|
|
UPDATE parent SET code='C' WHERE id=1;
|
|
}
|
|
} {}
|
|
|
|
do_execsql_test -db db2 7.2 {
|
|
SELECT * FROM parent;
|
|
SELECT * FROM child;
|
|
} {1 C 2 A 3 B 10 1 20 2 30 3}
|
|
|
|
do_test 7.3 { compare_db db db2 } {}
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Test that an UPDATE does not fire DELETE or INSERT triggers.
|
|
#
|
|
db2 close
|
|
reset_db
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 8.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(a INTEGER PRIMARY KEY, b UNIQUE, c);
|
|
INSERT INTO t1 VALUES(1, 'one', 1), (2, 'two', 2);
|
|
}
|
|
execsql {
|
|
UPDATE t1 SET c = c*100;
|
|
CREATE TABLE log(x);
|
|
CREATE TRIGGER t1d AFTER DELETE ON t1 BEGIN
|
|
INSERT INTO log VALUES('delete ' || old.a);
|
|
END;
|
|
CREATE TRIGGER t1i AFTER INSERT ON t1 BEGIN
|
|
INSERT INTO log VALUES('insert ' || new.a);
|
|
END;
|
|
CREATE TRIGGER t1u AFTER UPDATE ON t1 BEGIN
|
|
INSERT INTO log VALUES('update ' || new.a);
|
|
END;
|
|
} db2
|
|
} {}
|
|
|
|
do_conflict_test 8.1 0 {
|
|
swap t1 a b 1 2
|
|
} {
|
|
}
|
|
|
|
do_execsql_test -db db2 8.2 {
|
|
SELECT * FROM t1;
|
|
SELECT DISTINCT x FROM log ORDER BY 1;
|
|
} {
|
|
1 two 100 2 one 200
|
|
{update 1} {update 2}
|
|
}
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Test cases where setting a column to a random value fails with
|
|
# a constraint. The random value for a text column is a random integer
|
|
# cast to text, so a CHECK constraint on the length of the value is used
|
|
# to cause this. In this case the change should be treated as if the
|
|
# final UPDATE failed with a constraint error - i.e. the savepoint is
|
|
# rolled back and the next UPDATE in the buffer is tried.
|
|
#
|
|
db2 close
|
|
reset_db
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 9.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(a TEXT PRIMARY KEY, b UNIQUE);
|
|
INSERT INTO t1 VALUES('one', 'one');
|
|
INSERT INTO t1 VALUES('two', 'two');
|
|
INSERT INTO t1 VALUES('three', 'three');
|
|
}
|
|
execsql {
|
|
CREATE TABLE t2(a TEXT PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE t3(a TEXT PRIMARY KEY, b UNIQUE);
|
|
CREATE TABLE t4(a TEXT PRIMARY KEY, b UNIQUE);
|
|
INSERT INTO t2 SELECT * FROM t1;
|
|
INSERT INTO t3 SELECT * FROM t1;
|
|
INSERT INTO t4 SELECT * FROM t1;
|
|
}
|
|
execsql {
|
|
CREATE TABLE t2(a TEXT PRIMARY KEY, b UNIQUE CHECK( length(b)<=5 ));
|
|
CREATE TABLE t3(
|
|
a TEXT PRIMARY KEY,
|
|
b UNIQUE CHECK( a!='one' OR length(b)<=5 )
|
|
);
|
|
CREATE TABLE t4(
|
|
a TEXT PRIMARY KEY,
|
|
b UNIQUE CHECK( a!='two' OR length(b)<=5 )
|
|
);
|
|
INSERT INTO t2 SELECT * FROM t1;
|
|
INSERT INTO t3 SELECT * FROM t1;
|
|
INSERT INTO t4 SELECT * FROM t1;
|
|
} db2
|
|
} {}
|
|
|
|
proc xConflict {args} {
|
|
lappend ::conflict_list $args
|
|
return "OMIT"
|
|
}
|
|
|
|
do_conflict_test 9.1 0 {
|
|
swap t2 a b one two
|
|
} {
|
|
{UPDATE t2 CONSTRAINT {t two t two} {{} {} t one}}
|
|
{UPDATE t2 CONSTRAINT {t one t one} {{} {} t two}}
|
|
}
|
|
do_execsql_test -db db2 9.2 {
|
|
SELECT * FROM t2
|
|
} {one one two two three three}
|
|
|
|
do_conflict_test 9.3 0 {
|
|
swap t3 a b one two
|
|
swap t4 a b one two
|
|
} {
|
|
}
|
|
do_execsql_test -db db2 9.4 {
|
|
SELECT * FROM t3;
|
|
SELECT * FROM t4;
|
|
} {one two two one three three one two two one three three}
|
|
|
|
do_conflict_test 9.5 0 {
|
|
swap t3 a b two one
|
|
swap t4 a b two one
|
|
} {
|
|
}
|
|
do_execsql_test -db db2 9.6 {
|
|
SELECT * FROM t3;
|
|
SELECT * FROM t4;
|
|
} {one one two two three three one one two two three three}
|
|
|
|
# With the -noupdateloop option. The conflict handler is invoked for
|
|
# both changes.
|
|
do_conflict_test 9.7 1 {
|
|
swap t3 a b one two
|
|
} {
|
|
{UPDATE t3 CONSTRAINT {t two t two} {{} {} t one}}
|
|
{UPDATE t3 CONSTRAINT {t one t one} {{} {} t two}}
|
|
}
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Test the update loop with:
|
|
#
|
|
# * composite primary keys,
|
|
# * UPDATE changes that modify more than one column,
|
|
# * patchsets,
|
|
# * tables with no PRIMARY KEY (sessions "rowid" mode),
|
|
# * the -invert option.
|
|
#
|
|
db2 close
|
|
reset_db
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 10.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(a, b, c UNIQUE, d UNIQUE, e, PRIMARY KEY(b, a));
|
|
INSERT INTO t1 VALUES(1, 'x', 'c1', 'd1', 'e1');
|
|
INSERT INTO t1 VALUES(2, 'x', 'c2', 'd2', 'e2');
|
|
INSERT INTO t1 VALUES(3, 'y', 'c3', 'd3', 'e3');
|
|
}
|
|
} {}
|
|
|
|
do_test 10.1 {
|
|
do_then_apply_sql {
|
|
UPDATE t1 SET c=NULL, d=NULL;
|
|
UPDATE t1 SET c='c1', d='d1', e='E2' WHERE a=2;
|
|
UPDATE t1 SET c='c2', d='d2' WHERE a=3;
|
|
UPDATE t1 SET c='c3', d='d3' WHERE a=1;
|
|
}
|
|
} {}
|
|
do_execsql_test -db db2 10.2 {
|
|
SELECT * FROM t1 ORDER BY a
|
|
} {1 x c3 d3 e1 2 x c1 d1 E2 3 y c2 d2 e3}
|
|
do_test 10.3 { compare_db db db2 } {}
|
|
|
|
# Procedure do_then_apply_sql, used by earlier tests, overwrites [xConflict].
|
|
proc xConflict {args} {
|
|
lappend ::conflict_list $args
|
|
return "OMIT"
|
|
}
|
|
|
|
proc do_patchset_test {tn script} {
|
|
uplevel [list do_test $tn.1 [subst -nocommands {
|
|
sqlite3session S db "main"
|
|
S attach *
|
|
eval {$script}
|
|
set ::patchset [S patchset]
|
|
S delete
|
|
set ::conflict_list [list]
|
|
sqlite3changeset_apply_v2 db2 \$::patchset xConflict
|
|
set ::conflict_list
|
|
}] {}]
|
|
uplevel [list do_test $tn.2 { compare_db db db2 } {}]
|
|
}
|
|
|
|
do_patchset_test 10.4 {
|
|
swap t1 a c 1 2
|
|
swap t1 a d 2 3
|
|
}
|
|
|
|
do_test 10.5 {
|
|
set ::before [db eval {SELECT * FROM t1 ORDER BY a}]
|
|
sqlite3session S db main
|
|
S attach *
|
|
swap t1 a c 3 1
|
|
swap t1 a d 1 2
|
|
set ::changeset [S changeset]
|
|
S delete
|
|
set ::conflict_list [list]
|
|
sqlite3changeset_apply_v2 db2 $::changeset xConflict
|
|
compare_db db db2
|
|
set ::conflict_list
|
|
} {}
|
|
|
|
# Apply the inverse of the changeset from 10.5 to both databases. This
|
|
# requires the update loop on both.
|
|
do_test 10.6 {
|
|
sqlite3changeset_apply_v2 -invert db $::changeset xConflict
|
|
sqlite3changeset_apply_v2 -invert db2 $::changeset xConflict
|
|
compare_db db db2
|
|
set ::conflict_list
|
|
} {}
|
|
do_test 10.7 {
|
|
db2 eval {SELECT * FROM t1 ORDER BY a}
|
|
} $::before
|
|
|
|
db2 close
|
|
reset_db
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
do_test 11.0 {
|
|
do_common_sql {
|
|
CREATE TABLE t1(x UNIQUE, y);
|
|
INSERT INTO t1 VALUES('a', 1), ('b', 2), ('c', 3);
|
|
}
|
|
} {}
|
|
do_test 11.1 {
|
|
do_then_apply_sql {
|
|
UPDATE t1 SET x=NULL WHERE x IN ('a', 'b');
|
|
UPDATE t1 SET x='a' WHERE y=2;
|
|
UPDATE t1 SET x='b' WHERE y=1;
|
|
}
|
|
} {}
|
|
do_execsql_test -db db2 11.2 {
|
|
SELECT rowid, * FROM t1
|
|
} {1 b 1 2 a 2 3 c 3}
|
|
do_test 11.3 { compare_db db db2 } {}
|
|
|
|
|
|
#-------------------------------------------------------------------------
|
|
# Test with STRICT tables.
|
|
#
|
|
|
|
foreach {tn wr} {1 "" 2 ", WITHOUT ROWID"} {
|
|
db2 close
|
|
reset_db
|
|
forcedelete test.db2
|
|
sqlite3 db2 test.db2
|
|
|
|
proc xConflict {args} {
|
|
lappend ::conflict_list $args
|
|
return "OMIT"
|
|
}
|
|
|
|
do_test 12.$tn.0 {
|
|
do_common_sql "
|
|
CREATE TABLE t1(
|
|
a INTEGER PRIMARY KEY,
|
|
i INTEGER UNIQUE,
|
|
r REAL UNIQUE,
|
|
t TEXT UNIQUE,
|
|
b BLOB UNIQUE,
|
|
x ANY UNIQUE
|
|
) STRICT $wr;
|
|
INSERT INTO t1 VALUES(1, 1, 1.5, 'one', x'01', 1);
|
|
INSERT INTO t1 VALUES(2, 2, 2.5, 'two', x'02', 2.5);
|
|
INSERT INTO t1 VALUES(3, 3, 3.5, 'three', x'03', 'three');
|
|
INSERT INTO t1 VALUES(4, 4, 4.5, 'four', x'04', x'04');
|
|
INSERT INTO t1 VALUES(5, 5, 5.5, 'five', x'05', NULL);
|
|
"
|
|
} {}
|
|
|
|
foreach {tn2 col pk1 pk2} {1 i 1 2 2 r 2 3 3 t 3 4 4 b 4 5} {
|
|
do_conflict_test 12.$tn.1.$tn2 0 [list swap t1 a $col $pk1 $pk2] {
|
|
}
|
|
do_test 12.$tn.2.$tn2 { compare_db db db2 } {}
|
|
}
|
|
|
|
do_conflict_test 12.$tn.3 0 {
|
|
db eval {
|
|
UPDATE t1 SET x=NULL;
|
|
UPDATE t1 SET x=2.5 WHERE a=1;
|
|
UPDATE t1 SET x='three' WHERE a=2;
|
|
UPDATE t1 SET x=x'04' WHERE a=3;
|
|
UPDATE t1 SET x=1 WHERE a=4;
|
|
}
|
|
} {
|
|
}
|
|
do_test 12.$tn.4 { compare_db db db2 } {}
|
|
|
|
do_execsql_test -db db2 12.$tn.5 {
|
|
SELECT a, i, r, t, hex(b), quote(x) FROM t1 ORDER BY a
|
|
} {
|
|
1 2 1.5 one 01 2.5
|
|
2 1 3.5 two 02 'three'
|
|
3 3 2.5 four 03 X'04'
|
|
4 4 4.5 three 05 1
|
|
5 5 5.5 five 04 NULL
|
|
}
|
|
}
|
|
|
|
finish_test
|
|
|