diff options
author | Hans-Christoph Steiner <hans@eds.org> | 2012-09-20 18:34:42 -0400 |
---|---|---|
committer | Hans-Christoph Steiner <hans@eds.org> | 2012-09-20 18:34:42 -0400 |
commit | 734b4f890763e4efafe865ba476c43cc8d1a2214 (patch) | |
tree | d561d2fad0788619f4b8e230073f6af1d416934e /test/insert4.test | |
parent | 396b08286e7bb56e0e6440aaf1345c18e72ee22e (diff) | |
parent | 487e15dc239ccdb3344d1c99ce120e872bab4a74 (diff) |
Merge tag 'upstream/2.0.6'
Upstream version 2.0.6
Diffstat (limited to 'test/insert4.test')
-rw-r--r-- | test/insert4.test | 175 |
1 files changed, 175 insertions, 0 deletions
diff --git a/test/insert4.test b/test/insert4.test index cb02b9d..f4a45c1 100644 --- a/test/insert4.test +++ b/test/insert4.test @@ -386,4 +386,179 @@ ifcapable foreignkey { } {1} } +# Ticket [676bc02b87176125635cb174d110b431581912bb] +# Make sure INTEGER PRIMARY KEY ON CONFLICT ... works with the xfer +# optimization. +# +do_test insert4-8.1 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y); + INSERT INTO t1 VALUES(1,2); + INSERT INTO t2 VALUES(1,3); + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; + } +} {1 3} +do_test insert4-8.2 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); + CREATE TABLE t2(x, y); + INSERT INTO t1 VALUES(1,2); + INSERT INTO t2 VALUES(1,3); + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; + } +} {1 3} +do_test insert4-8.3 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y); + INSERT INTO t1 VALUES(1,2); + INSERT INTO t2 VALUES(1,3); + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; + } +} {1 2} +do_test insert4-8.4 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); + CREATE TABLE t2(x, y); + INSERT INTO t1 VALUES(1,2); + INSERT INTO t2 VALUES(1,3); + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; + } +} {1 2} +do_test insert4-8.5 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y); + INSERT INTO t1 VALUES(1,2); + INSERT INTO t2 VALUES(-99,100); + INSERT INTO t2 VALUES(1,3); + SELECT * FROM t1; + } + catchsql { + INSERT INTO t1 SELECT * FROM t2; + } +} {1 {PRIMARY KEY must be unique}} +do_test insert4-8.6 { + execsql { + SELECT * FROM t1; + } +} {-99 100 1 2} +do_test insert4-8.7 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y); + INSERT INTO t1 VALUES(1,2); + INSERT INTO t2 VALUES(-99,100); + INSERT INTO t2 VALUES(1,3); + SELECT * FROM t1; + } + catchsql { + INSERT INTO t1 SELECT * FROM t2; + } +} {1 {PRIMARY KEY must be unique}} +do_test insert4-8.8 { + execsql { + SELECT * FROM t1; + } +} {1 2} +do_test insert4-8.9 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y); + INSERT INTO t1 VALUES(1,2); + INSERT INTO t2 VALUES(-99,100); + INSERT INTO t2 VALUES(1,3); + SELECT * FROM t1; + } + catchsql { + BEGIN; + INSERT INTO t1 VALUES(2,3); + INSERT INTO t1 SELECT * FROM t2; + } +} {1 {PRIMARY KEY must be unique}} +do_test insert4-8.10 { + catchsql {COMMIT} +} {1 {cannot commit - no transaction is active}} +do_test insert4-8.11 { + execsql { + SELECT * FROM t1; + } +} {1 2} + +do_test insert4-8.21 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT REPLACE, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT REPLACE, y); + INSERT INTO t2 VALUES(1,3); + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; + } +} {1 3} +do_test insert4-8.22 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT IGNORE, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT IGNORE, y); + INSERT INTO t2 VALUES(1,3); + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; + } +} {1 3} +do_test insert4-8.23 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ABORT, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ABORT, y); + INSERT INTO t2 VALUES(1,3); + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; + } +} {1 3} +do_test insert4-8.24 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT FAIL, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT FAIL, y); + INSERT INTO t2 VALUES(1,3); + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; + } +} {1 3} +do_test insert4-8.25 { + execsql { + DROP TABLE IF EXISTS t1; + DROP TABLE IF EXISTS t2; + CREATE TABLE t1(a INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, b); + CREATE TABLE t2(x INTEGER PRIMARY KEY ON CONFLICT ROLLBACK, y); + INSERT INTO t2 VALUES(1,3); + INSERT INTO t1 SELECT * FROM t2; + SELECT * FROM t1; + } +} {1 3} + + finish_test |