| Index: third_party/sqlite/sqlite-src-3080704/test/without_rowid3.test
|
| diff --git a/third_party/sqlite/src/test/fkey2.test b/third_party/sqlite/sqlite-src-3080704/test/without_rowid3.test
|
| similarity index 56%
|
| copy from third_party/sqlite/src/test/fkey2.test
|
| copy to third_party/sqlite/sqlite-src-3080704/test/without_rowid3.test
|
| index f0cc4d244cba17e61a1378d4d2d7ba5e62cfcaf1..a0dc76d3f99221f075fd043fb12694d9b3b09ff0 100644
|
| --- a/third_party/sqlite/src/test/fkey2.test
|
| +++ b/third_party/sqlite/sqlite-src-3080704/test/without_rowid3.test
|
| @@ -1,4 +1,4 @@
|
| -# 2009 September 15
|
| +# 2013-11-02
|
| #
|
| # The author disclaims copyright to this source code. In place of
|
| # a legal notice, here is a blessing:
|
| @@ -10,7 +10,8 @@
|
| #***********************************************************************
|
| # This file implements regression tests for SQLite library.
|
| #
|
| -# This file implements tests for foreign keys.
|
| +# This file implements tests for foreign keys on WITHOUT ROWID
|
| +# tables.
|
| #
|
|
|
| set testdir [file dirname $argv0]
|
| @@ -24,63 +25,63 @@ ifcapable {!foreignkey||!trigger} {
|
| #-------------------------------------------------------------------------
|
| # Test structure:
|
| #
|
| -# fkey2-1.*: Simple tests to check that immediate and deferred foreign key
|
| +# without_rowid3-1.*: Simple tests to check that immediate and deferred foreign key
|
| # constraints work when not inside a transaction.
|
| #
|
| -# fkey2-2.*: Tests to verify that deferred foreign keys work inside
|
| +# without_rowid3-2.*: Tests to verify that deferred foreign keys work inside
|
| # explicit transactions (i.e that processing really is deferred).
|
| #
|
| -# fkey2-3.*: Tests that a statement transaction is rolled back if an
|
| +# without_rowid3-3.*: Tests that a statement transaction is rolled back if an
|
| # immediate foreign key constraint is violated.
|
| #
|
| -# fkey2-4.*: Test that FK actions may recurse even when recursive triggers
|
| +# without_rowid3-4.*: Test that FK actions may recurse even when recursive triggers
|
| # are disabled.
|
| #
|
| -# fkey2-5.*: Check that if foreign-keys are enabled, it is not possible
|
| +# without_rowid3-5.*: Check that if foreign-keys are enabled, it is not possible
|
| # to write to an FK column using the incremental blob API.
|
| #
|
| -# fkey2-6.*: Test that FK processing is automatically disabled when
|
| +# without_rowid3-6.*: Test that FK processing is automatically disabled when
|
| # running VACUUM.
|
| #
|
| -# fkey2-7.*: Test using an IPK as the key in the child (referencing) table.
|
| +# without_rowid3-7.*: Test using an IPK as the key in the child (referencing) table.
|
| #
|
| -# fkey2-8.*: Test that enabling/disabling foreign key support while a
|
| +# without_rowid3-8.*: Test that enabling/disabling foreign key support while a
|
| # transaction is active is not possible.
|
| #
|
| -# fkey2-9.*: Test SET DEFAULT actions.
|
| +# without_rowid3-9.*: Test SET DEFAULT actions.
|
| #
|
| -# fkey2-10.*: Test errors.
|
| +# without_rowid3-10.*: Test errors.
|
| #
|
| -# fkey2-11.*: Test CASCADE actions.
|
| +# without_rowid3-11.*: Test CASCADE actions.
|
| #
|
| -# fkey2-12.*: Test RESTRICT actions.
|
| +# without_rowid3-12.*: Test RESTRICT actions.
|
| #
|
| -# fkey2-13.*: Test that FK processing is performed when a row is REPLACED by
|
| +# without_rowid3-13.*: Test that FK processing is performed when a row is REPLACED by
|
| # an UPDATE or INSERT statement.
|
| #
|
| -# fkey2-14.*: Test the ALTER TABLE and DROP TABLE commands.
|
| +# without_rowid3-14.*: Test the ALTER TABLE and DROP TABLE commands.
|
| #
|
| -# fkey2-15.*: Test that if there are no (known) outstanding foreign key
|
| +# without_rowid3-15.*: Test that if there are no (known) outstanding foreign key
|
| # constraint violations in the database, inserting into a parent
|
| # table or deleting from a child table does not cause SQLite
|
| # to check if this has repaired an outstanding violation.
|
| #
|
| -# fkey2-16.*: Test that rows that refer to themselves may be inserted,
|
| +# without_rowid3-16.*: Test that rows that refer to themselves may be inserted,
|
| # updated and deleted.
|
| #
|
| -# fkey2-17.*: Test that the "count_changes" pragma does not interfere with
|
| +# without_rowid3-17.*: Test that the "count_changes" pragma does not interfere with
|
| # FK constraint processing.
|
| #
|
| -# fkey2-18.*: Test that the authorization callback is invoked when processing
|
| +# without_rowid3-18.*: Test that the authorization callback is invoked when processing
|
| # FK constraints.
|
| #
|
| -# fkey2-20.*: Test that ON CONFLICT clauses specified as part of statements
|
| +# without_rowid3-20.*: Test that ON CONFLICT clauses specified as part of statements
|
| # do not affect the operation of FK constraints.
|
| #
|
| -# fkey2-genfkey.*: Tests that were used with the shell tool .genfkey
|
| +# without_rowid3-genfkey.*: Tests that were used with the shell tool .genfkey
|
| # command. Recycled to test the built-in implementation.
|
| #
|
| -# fkey2-dd08e5.*: Tests to verify that ticket dd08e5a988d00decc4a543daa8d
|
| +# without_rowid3-dd08e5.*: Tests to verify that ticket dd08e5a988d00decc4a543daa8d
|
| # has been fixed.
|
| #
|
|
|
| @@ -89,13 +90,13 @@ execsql { PRAGMA foreign_keys = on }
|
|
|
| set FkeySimpleSchema {
|
| PRAGMA foreign_keys = on;
|
| - CREATE TABLE t1(a PRIMARY KEY, b);
|
| + CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE t2(c REFERENCES t1(a) /D/ , d);
|
|
|
| - CREATE TABLE t3(a PRIMARY KEY, b);
|
| + CREATE TABLE t3(a PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE t4(c REFERENCES t3 /D/, d);
|
|
|
| - CREATE TABLE t7(a, b INTEGER PRIMARY KEY);
|
| + CREATE TABLE t7(a, b INT PRIMARY KEY) WITHOUT rowid;
|
| CREATE TABLE t8(c REFERENCES t7 /D/, d);
|
|
|
| CREATE TABLE t9(a REFERENCES nosuchtable, b);
|
| @@ -104,79 +105,97 @@ set FkeySimpleSchema {
|
|
|
|
|
| set FkeySimpleTests {
|
| - 1.1 "INSERT INTO t2 VALUES(1, 3)" {1 {foreign key constraint failed}}
|
| + 1.1 "INSERT INTO t2 VALUES(1, 3)" {1 {FOREIGN KEY constraint failed}}
|
| 1.2 "INSERT INTO t1 VALUES(1, 2)" {0 {}}
|
| 1.3 "INSERT INTO t2 VALUES(1, 3)" {0 {}}
|
| - 1.4 "INSERT INTO t2 VALUES(2, 4)" {1 {foreign key constraint failed}}
|
| + 1.4 "INSERT INTO t2 VALUES(2, 4)" {1 {FOREIGN KEY constraint failed}}
|
| 1.5 "INSERT INTO t2 VALUES(NULL, 4)" {0 {}}
|
| - 1.6 "UPDATE t2 SET c=2 WHERE d=4" {1 {foreign key constraint failed}}
|
| + 1.6 "UPDATE t2 SET c=2 WHERE d=4" {1 {FOREIGN KEY constraint failed}}
|
| 1.7 "UPDATE t2 SET c=1 WHERE d=4" {0 {}}
|
| 1.9 "UPDATE t2 SET c=1 WHERE d=4" {0 {}}
|
| 1.10 "UPDATE t2 SET c=NULL WHERE d=4" {0 {}}
|
| - 1.11 "DELETE FROM t1 WHERE a=1" {1 {foreign key constraint failed}}
|
| - 1.12 "UPDATE t1 SET a = 2" {1 {foreign key constraint failed}}
|
| + 1.11 "DELETE FROM t1 WHERE a=1" {1 {FOREIGN KEY constraint failed}}
|
| + 1.12 "UPDATE t1 SET a = 2" {1 {FOREIGN KEY constraint failed}}
|
| 1.13 "UPDATE t1 SET a = 1" {0 {}}
|
|
|
| - 2.1 "INSERT INTO t4 VALUES(1, 3)" {1 {foreign key constraint failed}}
|
| + 2.1 "INSERT INTO t4 VALUES(1, 3)" {1 {FOREIGN KEY constraint failed}}
|
| 2.2 "INSERT INTO t3 VALUES(1, 2)" {0 {}}
|
| 2.3 "INSERT INTO t4 VALUES(1, 3)" {0 {}}
|
|
|
| - 4.1 "INSERT INTO t8 VALUES(1, 3)" {1 {foreign key constraint failed}}
|
| + 4.1 "INSERT INTO t8 VALUES(1, 3)" {1 {FOREIGN KEY constraint failed}}
|
| 4.2 "INSERT INTO t7 VALUES(2, 1)" {0 {}}
|
| 4.3 "INSERT INTO t8 VALUES(1, 3)" {0 {}}
|
| - 4.4 "INSERT INTO t8 VALUES(2, 4)" {1 {foreign key constraint failed}}
|
| + 4.4 "INSERT INTO t8 VALUES(2, 4)" {1 {FOREIGN KEY constraint failed}}
|
| 4.5 "INSERT INTO t8 VALUES(NULL, 4)" {0 {}}
|
| - 4.6 "UPDATE t8 SET c=2 WHERE d=4" {1 {foreign key constraint failed}}
|
| + 4.6 "UPDATE t8 SET c=2 WHERE d=4" {1 {FOREIGN KEY constraint failed}}
|
| 4.7 "UPDATE t8 SET c=1 WHERE d=4" {0 {}}
|
| 4.9 "UPDATE t8 SET c=1 WHERE d=4" {0 {}}
|
| 4.10 "UPDATE t8 SET c=NULL WHERE d=4" {0 {}}
|
| - 4.11 "DELETE FROM t7 WHERE b=1" {1 {foreign key constraint failed}}
|
| - 4.12 "UPDATE t7 SET b = 2" {1 {foreign key constraint failed}}
|
| + 4.11 "DELETE FROM t7 WHERE b=1" {1 {FOREIGN KEY constraint failed}}
|
| + 4.12 "UPDATE t7 SET b = 2" {1 {FOREIGN KEY constraint failed}}
|
| 4.13 "UPDATE t7 SET b = 1" {0 {}}
|
| - 4.14 "INSERT INTO t8 VALUES('a', 'b')" {1 {foreign key constraint failed}}
|
| - 4.15 "UPDATE t7 SET b = 5" {1 {foreign key constraint failed}}
|
| - 4.16 "UPDATE t7 SET rowid = 5" {1 {foreign key constraint failed}}
|
| + 4.14 "INSERT INTO t8 VALUES('a', 'b')" {1 {FOREIGN KEY constraint failed}}
|
| + 4.15 "UPDATE t7 SET b = 5" {1 {FOREIGN KEY constraint failed}}
|
| 4.17 "UPDATE t7 SET a = 10" {0 {}}
|
|
|
| 5.1 "INSERT INTO t9 VALUES(1, 3)" {1 {no such table: main.nosuchtable}}
|
| - 5.2 "INSERT INTO t10 VALUES(1, 3)" {1 {foreign key mismatch}}
|
| + 5.2 "INSERT INTO t10 VALUES(1, 3)"
|
| + {1 {foreign key mismatch - "t10" referencing "t9"}}
|
| }
|
|
|
| -do_test fkey2-1.1.0 {
|
| +do_test without_rowid3-1.1.0 {
|
| execsql [string map {/D/ {}} $FkeySimpleSchema]
|
| } {}
|
| foreach {tn zSql res} $FkeySimpleTests {
|
| - do_test fkey2-1.1.$tn { catchsql $zSql } $res
|
| + do_test without_rowid3-1.1.$tn.1 { catchsql $zSql } $res
|
| + do_test without_rowid3-1.1.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
|
| + do_test without_rowid3-1.1.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
|
| + do_test without_rowid3-1.1.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
|
| + do_test without_rowid3-1.1.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
|
| + do_test without_rowid3-1.1.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
|
| + do_test without_rowid3-1.1.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
|
| }
|
| drop_all_tables
|
|
|
| -do_test fkey2-1.2.0 {
|
| +do_test without_rowid3-1.2.0 {
|
| execsql [string map {/D/ {DEFERRABLE INITIALLY DEFERRED}} $FkeySimpleSchema]
|
| } {}
|
| foreach {tn zSql res} $FkeySimpleTests {
|
| - do_test fkey2-1.2.$tn { catchsql $zSql } $res
|
| + do_test without_rowid3-1.2.$tn { catchsql $zSql } $res
|
| + do_test without_rowid3-1.2.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
|
| + do_test without_rowid3-1.2.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
|
| + do_test without_rowid3-1.2.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
|
| + do_test without_rowid3-1.2.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
|
| + do_test without_rowid3-1.2.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
|
| + do_test without_rowid3-1.2.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
|
| }
|
| drop_all_tables
|
|
|
| -do_test fkey2-1.3.0 {
|
| +do_test without_rowid3-1.3.0 {
|
| execsql [string map {/D/ {}} $FkeySimpleSchema]
|
| execsql { PRAGMA count_changes = 1 }
|
| } {}
|
| foreach {tn zSql res} $FkeySimpleTests {
|
| if {$res == "0 {}"} { set res {0 1} }
|
| - do_test fkey2-1.3.$tn { catchsql $zSql } $res
|
| + do_test without_rowid3-1.3.$tn { catchsql $zSql } $res
|
| + do_test without_rowid3-1.3.$tn.2 { execsql {PRAGMA foreign_key_check(t1)} } {}
|
| + do_test without_rowid3-1.3.$tn.3 { execsql {PRAGMA foreign_key_check(t2)} } {}
|
| + do_test without_rowid3-1.3.$tn.4 { execsql {PRAGMA foreign_key_check(t3)} } {}
|
| + do_test without_rowid3-1.3.$tn.5 { execsql {PRAGMA foreign_key_check(t4)} } {}
|
| + do_test without_rowid3-1.3.$tn.6 { execsql {PRAGMA foreign_key_check(t7)} } {}
|
| + do_test without_rowid3-1.3.$tn.7 { execsql {PRAGMA foreign_key_check(t8)} } {}
|
| }
|
| execsql { PRAGMA count_changes = 0 }
|
| drop_all_tables
|
|
|
| -do_test fkey2-1.4.0 {
|
| +do_test without_rowid3-1.4.0 {
|
| execsql [string map {/D/ {}} $FkeySimpleSchema]
|
| execsql { PRAGMA count_changes = 1 }
|
| } {}
|
| foreach {tn zSql res} $FkeySimpleTests {
|
| if {$res == "0 {}"} { set res {0 1} }
|
| execsql BEGIN
|
| - do_test fkey2-1.4.$tn { catchsql $zSql } $res
|
| + do_test without_rowid3-1.4.$tn { catchsql $zSql } $res
|
| execsql COMMIT
|
| }
|
| execsql { PRAGMA count_changes = 0 }
|
| @@ -185,22 +204,22 @@ drop_all_tables
|
| # Special test: When the parent key is an IPK, make sure the affinity of
|
| # the IPK is not applied to the child key value before it is inserted
|
| # into the child table.
|
| -do_test fkey2-1.5.1 {
|
| +do_test without_rowid3-1.5.1 {
|
| execsql {
|
| - CREATE TABLE i(i INTEGER PRIMARY KEY);
|
| + CREATE TABLE i(i INT PRIMARY KEY) WITHOUT rowid;
|
| CREATE TABLE j(j REFERENCES i);
|
| INSERT INTO i VALUES(35);
|
| INSERT INTO j VALUES('35.0');
|
| SELECT j, typeof(j) FROM j;
|
| }
|
| } {35.0 text}
|
| -do_test fkey2-1.5.2 {
|
| +do_test without_rowid3-1.5.2 {
|
| catchsql { DELETE FROM i }
|
| -} {1 {foreign key constraint failed}}
|
| +} {1 {FOREIGN KEY constraint failed}}
|
|
|
| # Same test using a regular primary key with integer affinity.
|
| drop_all_tables
|
| -do_test fkey2-1.6.1 {
|
| +do_test without_rowid3-1.6.1 {
|
| execsql {
|
| CREATE TABLE i(i INT UNIQUE);
|
| CREATE TABLE j(j REFERENCES i(i));
|
| @@ -210,137 +229,137 @@ do_test fkey2-1.6.1 {
|
| SELECT i, typeof(i) FROM i;
|
| }
|
| } {35.0 text 35 integer}
|
| -do_test fkey2-1.6.2 {
|
| +do_test without_rowid3-1.6.2 {
|
| catchsql { DELETE FROM i }
|
| -} {1 {foreign key constraint failed}}
|
| +} {1 {FOREIGN KEY constraint failed}}
|
|
|
| # Use a collation sequence on the parent key.
|
| drop_all_tables
|
| -do_test fkey2-1.7.1 {
|
| +do_test without_rowid3-1.7.1 {
|
| execsql {
|
| - CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY);
|
| + CREATE TABLE i(i TEXT COLLATE nocase PRIMARY KEY) WITHOUT rowid;
|
| CREATE TABLE j(j TEXT COLLATE binary REFERENCES i(i));
|
| INSERT INTO i VALUES('SQLite');
|
| INSERT INTO j VALUES('sqlite');
|
| }
|
| catchsql { DELETE FROM i }
|
| -} {1 {foreign key constraint failed}}
|
| +} {1 {FOREIGN KEY constraint failed}}
|
|
|
| # Use the parent key collation even if it is default and the child key
|
| # has an explicit value.
|
| drop_all_tables
|
| -do_test fkey2-1.7.2 {
|
| +do_test without_rowid3-1.7.2 {
|
| execsql {
|
| - CREATE TABLE i(i TEXT PRIMARY KEY); -- Colseq is "BINARY"
|
| + CREATE TABLE i(i TEXT PRIMARY KEY) WITHOUT rowid; -- Colseq is "BINARY"
|
| CREATE TABLE j(j TEXT COLLATE nocase REFERENCES i(i));
|
| INSERT INTO i VALUES('SQLite');
|
| }
|
| catchsql { INSERT INTO j VALUES('sqlite') }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-1.7.3 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-1.7.3 {
|
| execsql {
|
| INSERT INTO i VALUES('sqlite');
|
| INSERT INTO j VALUES('sqlite');
|
| DELETE FROM i WHERE i = 'SQLite';
|
| }
|
| catchsql { DELETE FROM i WHERE i = 'sqlite' }
|
| -} {1 {foreign key constraint failed}}
|
| +} {1 {FOREIGN KEY constraint failed}}
|
|
|
| #-------------------------------------------------------------------------
|
| -# This section (test cases fkey2-2.*) contains tests to check that the
|
| +# This section (test cases without_rowid3-2.*) contains tests to check that the
|
| # deferred foreign key constraint logic works.
|
| #
|
| -proc fkey2-2-test {tn nocommit sql {res {}}} {
|
| +proc without_rowid3-2-test {tn nocommit sql {res {}}} {
|
| if {$res eq "FKV"} {
|
| - set expected {1 {foreign key constraint failed}}
|
| + set expected {1 {FOREIGN KEY constraint failed}}
|
| } else {
|
| set expected [list 0 $res]
|
| }
|
| - do_test fkey2-2.$tn [list catchsql $sql] $expected
|
| + do_test without_rowid3-2.$tn [list catchsql $sql] $expected
|
| if {$nocommit} {
|
| - do_test fkey2-2.${tn}c {
|
| + do_test without_rowid3-2.${tn}c {
|
| catchsql COMMIT
|
| - } {1 {foreign key constraint failed}}
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| }
|
| }
|
|
|
| -fkey2-2-test 1 0 {
|
| +without_rowid3-2-test 1 0 {
|
| CREATE TABLE node(
|
| nodeid PRIMARY KEY,
|
| parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
|
| - );
|
| + ) WITHOUT rowid;
|
| CREATE TABLE leaf(
|
| cellid PRIMARY KEY,
|
| parent REFERENCES node DEFERRABLE INITIALLY DEFERRED
|
| - );
|
| + ) WITHOUT rowid;
|
| }
|
|
|
| -fkey2-2-test 1 0 "INSERT INTO node VALUES(1, 0)" FKV
|
| -fkey2-2-test 2 0 "BEGIN"
|
| -fkey2-2-test 3 1 "INSERT INTO node VALUES(1, 0)"
|
| -fkey2-2-test 4 0 "UPDATE node SET parent = NULL"
|
| -fkey2-2-test 5 0 "COMMIT"
|
| -fkey2-2-test 6 0 "SELECT * FROM node" {1 {}}
|
| -
|
| -fkey2-2-test 7 0 "BEGIN"
|
| -fkey2-2-test 8 1 "INSERT INTO leaf VALUES('a', 2)"
|
| -fkey2-2-test 9 1 "INSERT INTO node VALUES(2, 0)"
|
| -fkey2-2-test 10 0 "UPDATE node SET parent = 1 WHERE nodeid = 2"
|
| -fkey2-2-test 11 0 "COMMIT"
|
| -fkey2-2-test 12 0 "SELECT * FROM node" {1 {} 2 1}
|
| -fkey2-2-test 13 0 "SELECT * FROM leaf" {a 2}
|
| -
|
| -fkey2-2-test 14 0 "BEGIN"
|
| -fkey2-2-test 15 1 "DELETE FROM node WHERE nodeid = 2"
|
| -fkey2-2-test 16 0 "INSERT INTO node VALUES(2, NULL)"
|
| -fkey2-2-test 17 0 "COMMIT"
|
| -fkey2-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}}
|
| -fkey2-2-test 19 0 "SELECT * FROM leaf" {a 2}
|
| -
|
| -fkey2-2-test 20 0 "BEGIN"
|
| -fkey2-2-test 21 0 "INSERT INTO leaf VALUES('b', 1)"
|
| -fkey2-2-test 22 0 "SAVEPOINT save"
|
| -fkey2-2-test 23 0 "DELETE FROM node WHERE nodeid = 1"
|
| -fkey2-2-test 24 0 "ROLLBACK TO save"
|
| -fkey2-2-test 25 0 "COMMIT"
|
| -fkey2-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}}
|
| -fkey2-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1}
|
| -
|
| -fkey2-2-test 28 0 "BEGIN"
|
| -fkey2-2-test 29 0 "INSERT INTO leaf VALUES('c', 1)"
|
| -fkey2-2-test 30 0 "SAVEPOINT save"
|
| -fkey2-2-test 31 0 "DELETE FROM node WHERE nodeid = 1"
|
| -fkey2-2-test 32 1 "RELEASE save"
|
| -fkey2-2-test 33 1 "DELETE FROM leaf WHERE cellid = 'b'"
|
| -fkey2-2-test 34 0 "DELETE FROM leaf WHERE cellid = 'c'"
|
| -fkey2-2-test 35 0 "COMMIT"
|
| -fkey2-2-test 36 0 "SELECT * FROM node" {2 {}}
|
| -fkey2-2-test 37 0 "SELECT * FROM leaf" {a 2}
|
| -
|
| -fkey2-2-test 38 0 "SAVEPOINT outer"
|
| -fkey2-2-test 39 1 "INSERT INTO leaf VALUES('d', 3)"
|
| -fkey2-2-test 40 1 "RELEASE outer" FKV
|
| -fkey2-2-test 41 1 "INSERT INTO leaf VALUES('e', 3)"
|
| -fkey2-2-test 42 0 "INSERT INTO node VALUES(3, 2)"
|
| -fkey2-2-test 43 0 "RELEASE outer"
|
| -
|
| -fkey2-2-test 44 0 "SAVEPOINT outer"
|
| -fkey2-2-test 45 1 "DELETE FROM node WHERE nodeid=3"
|
| -fkey2-2-test 47 0 "INSERT INTO node VALUES(3, 2)"
|
| -fkey2-2-test 48 0 "ROLLBACK TO outer"
|
| -fkey2-2-test 49 0 "RELEASE outer"
|
| -
|
| -fkey2-2-test 50 0 "SAVEPOINT outer"
|
| -fkey2-2-test 51 1 "INSERT INTO leaf VALUES('f', 4)"
|
| -fkey2-2-test 52 1 "SAVEPOINT inner"
|
| -fkey2-2-test 53 1 "INSERT INTO leaf VALUES('g', 4)"
|
| -fkey2-2-test 54 1 "RELEASE outer" FKV
|
| -fkey2-2-test 55 1 "ROLLBACK TO inner"
|
| -fkey2-2-test 56 0 "COMMIT" FKV
|
| -fkey2-2-test 57 0 "INSERT INTO node VALUES(4, NULL)"
|
| -fkey2-2-test 58 0 "RELEASE outer"
|
| -fkey2-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}}
|
| -fkey2-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4}
|
| +without_rowid3-2-test 1 0 "INSERT INTO node VALUES(1, 0)" FKV
|
| +without_rowid3-2-test 2 0 "BEGIN"
|
| +without_rowid3-2-test 3 1 "INSERT INTO node VALUES(1, 0)"
|
| +without_rowid3-2-test 4 0 "UPDATE node SET parent = NULL"
|
| +without_rowid3-2-test 5 0 "COMMIT"
|
| +without_rowid3-2-test 6 0 "SELECT * FROM node" {1 {}}
|
| +
|
| +without_rowid3-2-test 7 0 "BEGIN"
|
| +without_rowid3-2-test 8 1 "INSERT INTO leaf VALUES('a', 2)"
|
| +without_rowid3-2-test 9 1 "INSERT INTO node VALUES(2, 0)"
|
| +without_rowid3-2-test 10 0 "UPDATE node SET parent = 1 WHERE nodeid = 2"
|
| +without_rowid3-2-test 11 0 "COMMIT"
|
| +without_rowid3-2-test 12 0 "SELECT * FROM node" {1 {} 2 1}
|
| +without_rowid3-2-test 13 0 "SELECT * FROM leaf" {a 2}
|
| +
|
| +without_rowid3-2-test 14 0 "BEGIN"
|
| +without_rowid3-2-test 15 1 "DELETE FROM node WHERE nodeid = 2"
|
| +without_rowid3-2-test 16 0 "INSERT INTO node VALUES(2, NULL)"
|
| +without_rowid3-2-test 17 0 "COMMIT"
|
| +without_rowid3-2-test 18 0 "SELECT * FROM node" {1 {} 2 {}}
|
| +without_rowid3-2-test 19 0 "SELECT * FROM leaf" {a 2}
|
| +
|
| +without_rowid3-2-test 20 0 "BEGIN"
|
| +without_rowid3-2-test 21 0 "INSERT INTO leaf VALUES('b', 1)"
|
| +without_rowid3-2-test 22 0 "SAVEPOINT save"
|
| +without_rowid3-2-test 23 0 "DELETE FROM node WHERE nodeid = 1"
|
| +without_rowid3-2-test 24 0 "ROLLBACK TO save"
|
| +without_rowid3-2-test 25 0 "COMMIT"
|
| +without_rowid3-2-test 26 0 "SELECT * FROM node" {1 {} 2 {}}
|
| +without_rowid3-2-test 27 0 "SELECT * FROM leaf" {a 2 b 1}
|
| +
|
| +without_rowid3-2-test 28 0 "BEGIN"
|
| +without_rowid3-2-test 29 0 "INSERT INTO leaf VALUES('c', 1)"
|
| +without_rowid3-2-test 30 0 "SAVEPOINT save"
|
| +without_rowid3-2-test 31 0 "DELETE FROM node WHERE nodeid = 1"
|
| +without_rowid3-2-test 32 1 "RELEASE save"
|
| +without_rowid3-2-test 33 1 "DELETE FROM leaf WHERE cellid = 'b'"
|
| +without_rowid3-2-test 34 0 "DELETE FROM leaf WHERE cellid = 'c'"
|
| +without_rowid3-2-test 35 0 "COMMIT"
|
| +without_rowid3-2-test 36 0 "SELECT * FROM node" {2 {}}
|
| +without_rowid3-2-test 37 0 "SELECT * FROM leaf" {a 2}
|
| +
|
| +without_rowid3-2-test 38 0 "SAVEPOINT outer"
|
| +without_rowid3-2-test 39 1 "INSERT INTO leaf VALUES('d', 3)"
|
| +without_rowid3-2-test 40 1 "RELEASE outer" FKV
|
| +without_rowid3-2-test 41 1 "INSERT INTO leaf VALUES('e', 3)"
|
| +without_rowid3-2-test 42 0 "INSERT INTO node VALUES(3, 2)"
|
| +without_rowid3-2-test 43 0 "RELEASE outer"
|
| +
|
| +without_rowid3-2-test 44 0 "SAVEPOINT outer"
|
| +without_rowid3-2-test 45 1 "DELETE FROM node WHERE nodeid=3"
|
| +without_rowid3-2-test 47 0 "INSERT INTO node VALUES(3, 2)"
|
| +without_rowid3-2-test 48 0 "ROLLBACK TO outer"
|
| +without_rowid3-2-test 49 0 "RELEASE outer"
|
| +
|
| +without_rowid3-2-test 50 0 "SAVEPOINT outer"
|
| +without_rowid3-2-test 51 1 "INSERT INTO leaf VALUES('f', 4)"
|
| +without_rowid3-2-test 52 1 "SAVEPOINT inner"
|
| +without_rowid3-2-test 53 1 "INSERT INTO leaf VALUES('g', 4)"
|
| +without_rowid3-2-test 54 1 "RELEASE outer" FKV
|
| +without_rowid3-2-test 55 1 "ROLLBACK TO inner"
|
| +without_rowid3-2-test 56 0 "COMMIT" FKV
|
| +without_rowid3-2-test 57 0 "INSERT INTO node VALUES(4, NULL)"
|
| +without_rowid3-2-test 58 0 "RELEASE outer"
|
| +without_rowid3-2-test 59 0 "SELECT * FROM node" {2 {} 3 2 4 {}}
|
| +without_rowid3-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4}
|
|
|
| # The following set of tests check that if a statement that affects
|
| # multiple rows violates some foreign key constraints, then strikes a
|
| @@ -348,90 +367,90 @@ fkey2-2-test 60 0 "SELECT * FROM leaf" {a 2 d 3 e 3 f 4}
|
| # the deferred constraint counter is correctly reset to the value it
|
| # had before the statement-transaction was opened.
|
| #
|
| -fkey2-2-test 61 0 "BEGIN"
|
| -fkey2-2-test 62 0 "DELETE FROM leaf"
|
| -fkey2-2-test 63 0 "DELETE FROM node"
|
| -fkey2-2-test 64 1 "INSERT INTO leaf VALUES('a', 1)"
|
| -fkey2-2-test 65 1 "INSERT INTO leaf VALUES('b', 2)"
|
| -fkey2-2-test 66 1 "INSERT INTO leaf VALUES('c', 1)"
|
| -do_test fkey2-2-test-67 {
|
| +without_rowid3-2-test 61 0 "BEGIN"
|
| +without_rowid3-2-test 62 0 "DELETE FROM leaf"
|
| +without_rowid3-2-test 63 0 "DELETE FROM node"
|
| +without_rowid3-2-test 64 1 "INSERT INTO leaf VALUES('a', 1)"
|
| +without_rowid3-2-test 65 1 "INSERT INTO leaf VALUES('b', 2)"
|
| +without_rowid3-2-test 66 1 "INSERT INTO leaf VALUES('c', 1)"
|
| +do_test without_rowid3-2-test-67 {
|
| catchsql "INSERT INTO node SELECT parent, 3 FROM leaf"
|
| -} {1 {column nodeid is not unique}}
|
| -fkey2-2-test 68 0 "COMMIT" FKV
|
| -fkey2-2-test 69 1 "INSERT INTO node VALUES(1, NULL)"
|
| -fkey2-2-test 70 0 "INSERT INTO node VALUES(2, NULL)"
|
| -fkey2-2-test 71 0 "COMMIT"
|
| +} {1 {UNIQUE constraint failed: node.nodeid}}
|
| +without_rowid3-2-test 68 0 "COMMIT" FKV
|
| +without_rowid3-2-test 69 1 "INSERT INTO node VALUES(1, NULL)"
|
| +without_rowid3-2-test 70 0 "INSERT INTO node VALUES(2, NULL)"
|
| +without_rowid3-2-test 71 0 "COMMIT"
|
|
|
| -fkey2-2-test 72 0 "BEGIN"
|
| -fkey2-2-test 73 1 "DELETE FROM node"
|
| -fkey2-2-test 74 0 "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf"
|
| -fkey2-2-test 75 0 "COMMIT"
|
| +without_rowid3-2-test 72 0 "BEGIN"
|
| +without_rowid3-2-test 73 1 "DELETE FROM node"
|
| +without_rowid3-2-test 74 0 "INSERT INTO node(nodeid) SELECT DISTINCT parent FROM leaf"
|
| +without_rowid3-2-test 75 0 "COMMIT"
|
|
|
| #-------------------------------------------------------------------------
|
| -# Test cases fkey2-3.* test that a program that executes foreign key
|
| +# Test cases without_rowid3-3.* test that a program that executes foreign key
|
| # actions (CASCADE, SET DEFAULT, SET NULL etc.) or tests FK constraints
|
| # opens a statement transaction if required.
|
| #
|
| -# fkey2-3.1.*: Test UPDATE statements.
|
| -# fkey2-3.2.*: Test DELETE statements.
|
| +# without_rowid3-3.1.*: Test UPDATE statements.
|
| +# without_rowid3-3.2.*: Test DELETE statements.
|
| #
|
| drop_all_tables
|
| -do_test fkey2-3.1.1 {
|
| +do_test without_rowid3-3.1.1 {
|
| execsql {
|
| - CREATE TABLE ab(a PRIMARY KEY, b);
|
| + CREATE TABLE ab(a PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE cd(
|
| c PRIMARY KEY REFERENCES ab ON UPDATE CASCADE ON DELETE CASCADE,
|
| d
|
| - );
|
| + ) WITHOUT rowid;
|
| CREATE TABLE ef(
|
| e REFERENCES cd ON UPDATE CASCADE,
|
| f, CHECK (e!=5)
|
| );
|
| }
|
| } {}
|
| -do_test fkey2-3.1.2 {
|
| +do_test without_rowid3-3.1.2 {
|
| execsql {
|
| INSERT INTO ab VALUES(1, 'b');
|
| INSERT INTO cd VALUES(1, 'd');
|
| INSERT INTO ef VALUES(1, 'e');
|
| }
|
| } {}
|
| -do_test fkey2-3.1.3 {
|
| +do_test without_rowid3-3.1.3 {
|
| catchsql { UPDATE ab SET a = 5 }
|
| -} {1 {constraint failed}}
|
| -do_test fkey2-3.1.4 {
|
| +} {1 {CHECK constraint failed: ef}}
|
| +do_test without_rowid3-3.1.4 {
|
| execsql { SELECT * FROM ab }
|
| } {1 b}
|
| -do_test fkey2-3.1.4 {
|
| +do_test without_rowid3-3.1.4 {
|
| execsql BEGIN;
|
| catchsql { UPDATE ab SET a = 5 }
|
| -} {1 {constraint failed}}
|
| -do_test fkey2-3.1.5 {
|
| +} {1 {CHECK constraint failed: ef}}
|
| +do_test without_rowid3-3.1.5 {
|
| execsql COMMIT;
|
| execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
|
| } {1 b 1 d 1 e}
|
|
|
| -do_test fkey2-3.2.1 {
|
| +do_test without_rowid3-3.2.1 {
|
| execsql BEGIN;
|
| catchsql { DELETE FROM ab }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-3.2.2 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-3.2.2 {
|
| execsql COMMIT
|
| execsql { SELECT * FROM ab; SELECT * FROM cd; SELECT * FROM ef }
|
| } {1 b 1 d 1 e}
|
|
|
| #-------------------------------------------------------------------------
|
| -# Test cases fkey2-4.* test that recursive foreign key actions
|
| +# Test cases without_rowid3-4.* test that recursive foreign key actions
|
| # (i.e. CASCADE) are allowed even if recursive triggers are disabled.
|
| #
|
| drop_all_tables
|
| -do_test fkey2-4.1 {
|
| +do_test without_rowid3-4.1 {
|
| execsql {
|
| CREATE TABLE t1(
|
| node PRIMARY KEY,
|
| parent REFERENCES t1 ON DELETE CASCADE
|
| - );
|
| - CREATE TABLE t2(node PRIMARY KEY, parent);
|
| + ) WITHOUT rowid;
|
| + CREATE TABLE t2(node PRIMARY KEY, parent) WITHOUT rowid;
|
| CREATE TRIGGER t2t AFTER DELETE ON t2 BEGIN
|
| DELETE FROM t2 WHERE parent = old.node;
|
| END;
|
| @@ -445,7 +464,7 @@ do_test fkey2-4.1 {
|
| INSERT INTO t2 SELECT * FROM t1;
|
| }
|
| } {}
|
| -do_test fkey2-4.2 {
|
| +do_test without_rowid3-4.2 {
|
| execsql { PRAGMA recursive_triggers = off }
|
| execsql {
|
| BEGIN;
|
| @@ -453,14 +472,14 @@ do_test fkey2-4.2 {
|
| SELECT node FROM t1;
|
| }
|
| } {}
|
| -do_test fkey2-4.3 {
|
| +do_test without_rowid3-4.3 {
|
| execsql {
|
| DELETE FROM t2 WHERE node = 1;
|
| SELECT node FROM t2;
|
| ROLLBACK;
|
| }
|
| } {4 5 6 7}
|
| -do_test fkey2-4.4 {
|
| +do_test without_rowid3-4.4 {
|
| execsql { PRAGMA recursive_triggers = on }
|
| execsql {
|
| BEGIN;
|
| @@ -468,7 +487,7 @@ do_test fkey2-4.4 {
|
| SELECT node FROM t1;
|
| }
|
| } {}
|
| -do_test fkey2-4.3 {
|
| +do_test without_rowid3-4.3 {
|
| execsql {
|
| DELETE FROM t2 WHERE node = 1;
|
| SELECT node FROM t2;
|
| @@ -477,42 +496,31 @@ do_test fkey2-4.3 {
|
| } {}
|
|
|
| #-------------------------------------------------------------------------
|
| -# Test cases fkey2-5.* verify that the incremental blob API may not
|
| +# Test cases without_rowid3-5.* verify that the incremental blob API may not
|
| # write to a foreign key column while foreign-keys are enabled.
|
| #
|
| drop_all_tables
|
| ifcapable incrblob {
|
| - do_test fkey2-5.1 {
|
| + do_test without_rowid3-5.1 {
|
| execsql {
|
| - CREATE TABLE t1(a PRIMARY KEY, b);
|
| - CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a));
|
| + CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
|
| + CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1(a)) WITHOUT rowid;
|
| INSERT INTO t1 VALUES('hello', 'world');
|
| INSERT INTO t2 VALUES('key', 'hello');
|
| }
|
| } {}
|
| - do_test fkey2-5.2 {
|
| + do_test without_rowid3-5.2 {
|
| set rc [catch { set fd [db incrblob t2 b 1] } msg]
|
| list $rc $msg
|
| - } {1 {cannot open foreign key column for writing}}
|
| - do_test fkey2-5.3 {
|
| - set rc [catch { set fd [db incrblob -readonly t2 b 1] } msg]
|
| - close $fd
|
| - set rc
|
| - } {0}
|
| - do_test fkey2-5.4 {
|
| - execsql { PRAGMA foreign_keys = off }
|
| - set rc [catch { set fd [db incrblob t2 b 1] } msg]
|
| - close $fd
|
| - set rc
|
| - } {0}
|
| - do_test fkey2-5.5 {
|
| + } {1 {cannot open table without rowid: t2}}
|
| + do_test without_rowid3-5.5 {
|
| execsql { PRAGMA foreign_keys = on }
|
| } {}
|
| }
|
|
|
| drop_all_tables
|
| ifcapable vacuum {
|
| - do_test fkey2-6.1 {
|
| + do_test without_rowid3-6.1 {
|
| execsql {
|
| CREATE TABLE t1(a REFERENCES t2(c), b);
|
| CREATE TABLE t2(c UNIQUE, b);
|
| @@ -524,86 +532,83 @@ ifcapable vacuum {
|
| }
|
|
|
| #-------------------------------------------------------------------------
|
| -# Test that it is possible to use an INTEGER PRIMARY KEY as the child key
|
| +# Test that it is possible to use an INT PRIMARY KEY as the child key
|
| # of a foreign constraint.
|
| #
|
| drop_all_tables
|
| -do_test fkey2-7.1 {
|
| +do_test without_rowid3-7.1 {
|
| execsql {
|
| - CREATE TABLE t1(a PRIMARY KEY, b);
|
| - CREATE TABLE t2(c INTEGER PRIMARY KEY REFERENCES t1, b);
|
| + CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
|
| + CREATE TABLE t2(c INT PRIMARY KEY REFERENCES t1, b) WITHOUT rowid;
|
| }
|
| } {}
|
| -do_test fkey2-7.2 {
|
| +do_test without_rowid3-7.2 {
|
| catchsql { INSERT INTO t2 VALUES(1, 'A'); }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-7.3 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-7.3 {
|
| execsql {
|
| INSERT INTO t1 VALUES(1, 2);
|
| INSERT INTO t1 VALUES(2, 3);
|
| INSERT INTO t2 VALUES(1, 'A');
|
| }
|
| } {}
|
| -do_test fkey2-7.4 {
|
| +do_test without_rowid3-7.4 {
|
| execsql { UPDATE t2 SET c = 2 }
|
| } {}
|
| -do_test fkey2-7.5 {
|
| +do_test without_rowid3-7.5 {
|
| catchsql { UPDATE t2 SET c = 3 }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-7.6 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-7.6 {
|
| catchsql { DELETE FROM t1 WHERE a = 2 }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-7.7 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-7.7 {
|
| execsql { DELETE FROM t1 WHERE a = 1 }
|
| } {}
|
| -do_test fkey2-7.8 {
|
| +do_test without_rowid3-7.8 {
|
| catchsql { UPDATE t1 SET a = 3 }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-7.9 {
|
| - catchsql { UPDATE t2 SET rowid = 3 }
|
| -} {1 {foreign key constraint failed}}
|
| +} {1 {FOREIGN KEY constraint failed}}
|
|
|
| #-------------------------------------------------------------------------
|
| # Test that it is not possible to enable/disable FK support while a
|
| # transaction is open.
|
| #
|
| drop_all_tables
|
| -proc fkey2-8-test {tn zSql value} {
|
| - do_test fkey-2.8.$tn.1 [list execsql $zSql] {}
|
| - do_test fkey-2.8.$tn.2 { execsql "PRAGMA foreign_keys" } $value
|
| +proc without_rowid3-8-test {tn zSql value} {
|
| + do_test without_rowid3-2.8.$tn.1 [list execsql $zSql] {}
|
| + do_test without_rowid3-2.8.$tn.2 { execsql "PRAGMA foreign_keys" } $value
|
| }
|
| -fkey2-8-test 1 { PRAGMA foreign_keys = 0 } 0
|
| -fkey2-8-test 2 { PRAGMA foreign_keys = 1 } 1
|
| -fkey2-8-test 3 { BEGIN } 1
|
| -fkey2-8-test 4 { PRAGMA foreign_keys = 0 } 1
|
| -fkey2-8-test 5 { COMMIT } 1
|
| -fkey2-8-test 6 { PRAGMA foreign_keys = 0 } 0
|
| -fkey2-8-test 7 { BEGIN } 0
|
| -fkey2-8-test 8 { PRAGMA foreign_keys = 1 } 0
|
| -fkey2-8-test 9 { COMMIT } 0
|
| -fkey2-8-test 10 { PRAGMA foreign_keys = 1 } 1
|
| -fkey2-8-test 11 { PRAGMA foreign_keys = off } 0
|
| -fkey2-8-test 12 { PRAGMA foreign_keys = on } 1
|
| -fkey2-8-test 13 { PRAGMA foreign_keys = no } 0
|
| -fkey2-8-test 14 { PRAGMA foreign_keys = yes } 1
|
| -fkey2-8-test 15 { PRAGMA foreign_keys = false } 0
|
| -fkey2-8-test 16 { PRAGMA foreign_keys = true } 1
|
| +without_rowid3-8-test 1 { PRAGMA foreign_keys = 0 } 0
|
| +without_rowid3-8-test 2 { PRAGMA foreign_keys = 1 } 1
|
| +without_rowid3-8-test 3 { BEGIN } 1
|
| +without_rowid3-8-test 4 { PRAGMA foreign_keys = 0 } 1
|
| +without_rowid3-8-test 5 { COMMIT } 1
|
| +without_rowid3-8-test 6 { PRAGMA foreign_keys = 0 } 0
|
| +without_rowid3-8-test 7 { BEGIN } 0
|
| +without_rowid3-8-test 8 { PRAGMA foreign_keys = 1 } 0
|
| +without_rowid3-8-test 9 { COMMIT } 0
|
| +without_rowid3-8-test 10 { PRAGMA foreign_keys = 1 } 1
|
| +without_rowid3-8-test 11 { PRAGMA foreign_keys = off } 0
|
| +without_rowid3-8-test 12 { PRAGMA foreign_keys = on } 1
|
| +without_rowid3-8-test 13 { PRAGMA foreign_keys = no } 0
|
| +without_rowid3-8-test 14 { PRAGMA foreign_keys = yes } 1
|
| +without_rowid3-8-test 15 { PRAGMA foreign_keys = false } 0
|
| +without_rowid3-8-test 16 { PRAGMA foreign_keys = true } 1
|
|
|
| #-------------------------------------------------------------------------
|
| -# The following tests, fkey2-9.*, test SET DEFAULT actions.
|
| +# The following tests, without_rowid3-9.*, test SET DEFAULT actions.
|
| #
|
| drop_all_tables
|
| -do_test fkey2-9.1.1 {
|
| +do_test without_rowid3-9.1.1 {
|
| execsql {
|
| - CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
|
| + CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE t2(
|
| - c INTEGER PRIMARY KEY,
|
| + c INT PRIMARY KEY,
|
| d INTEGER DEFAULT 1 REFERENCES t1 ON DELETE SET DEFAULT
|
| - );
|
| + ) WITHOUT rowid;
|
| DELETE FROM t1;
|
| }
|
| } {}
|
| -do_test fkey2-9.1.2 {
|
| +do_test without_rowid3-9.1.2 {
|
| execsql {
|
| INSERT INTO t1 VALUES(1, 'one');
|
| INSERT INTO t1 VALUES(2, 'two');
|
| @@ -613,7 +618,7 @@ do_test fkey2-9.1.2 {
|
| SELECT * FROM t2;
|
| }
|
| } {1 2 1 1}
|
| -do_test fkey2-9.1.3 {
|
| +do_test without_rowid3-9.1.3 {
|
| execsql {
|
| INSERT INTO t1 VALUES(2, 'two');
|
| UPDATE t2 SET d = 2;
|
| @@ -621,16 +626,16 @@ do_test fkey2-9.1.3 {
|
| SELECT * FROM t2;
|
| }
|
| } {1 2}
|
| -do_test fkey2-9.1.4 {
|
| +do_test without_rowid3-9.1.4 {
|
| execsql { SELECT * FROM t1 }
|
| } {2 two}
|
| -do_test fkey2-9.1.5 {
|
| +do_test without_rowid3-9.1.5 {
|
| catchsql { DELETE FROM t1 }
|
| -} {1 {foreign key constraint failed}}
|
| +} {1 {FOREIGN KEY constraint failed}}
|
|
|
| -do_test fkey2-9.2.1 {
|
| +do_test without_rowid3-9.2.1 {
|
| execsql {
|
| - CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
|
| + CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)) WITHOUT rowid;
|
| CREATE TABLE cc(d DEFAULT 3, e DEFAULT 1, f DEFAULT 2,
|
| FOREIGN KEY(f, d) REFERENCES pp
|
| ON UPDATE SET DEFAULT
|
| @@ -641,7 +646,7 @@ do_test fkey2-9.2.1 {
|
| INSERT INTO pp VALUES(7, 8, 9);
|
| }
|
| } {}
|
| -do_test fkey2-9.2.2 {
|
| +do_test without_rowid3-9.2.2 {
|
| execsql {
|
| INSERT INTO cc VALUES(6, 'A', 5);
|
| INSERT INTO cc VALUES(6, 'B', 5);
|
| @@ -651,7 +656,7 @@ do_test fkey2-9.2.2 {
|
| SELECT * FROM cc;
|
| }
|
| } {6 A 5 6 B 5 3 A 2 3 B 2}
|
| -do_test fkey2-9.2.3 {
|
| +do_test without_rowid3-9.2.3 {
|
| execsql {
|
| DELETE FROM pp WHERE a = 4;
|
| SELECT * FROM cc;
|
| @@ -659,18 +664,18 @@ do_test fkey2-9.2.3 {
|
| } {{} A {} {} B {} 3 A 2 3 B 2}
|
|
|
| #-------------------------------------------------------------------------
|
| -# The following tests, fkey2-10.*, test "foreign key mismatch" and
|
| +# The following tests, without_rowid3-10.*, test "foreign key mismatch" and
|
| # other errors.
|
| #
|
| set tn 0
|
| foreach zSql [list {
|
| - CREATE TABLE p(a PRIMARY KEY, b);
|
| + CREATE TABLE p(a PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE c(x REFERENCES p(c));
|
| } {
|
| CREATE TABLE c(x REFERENCES v(y));
|
| CREATE VIEW v AS SELECT x AS y FROM c;
|
| } {
|
| - CREATE TABLE p(a, b, PRIMARY KEY(a, b));
|
| + CREATE TABLE p(a, b, PRIMARY KEY(a, b)) WITHOUT rowid;
|
| CREATE TABLE c(x REFERENCES p);
|
| } {
|
| CREATE TABLE p(a COLLATE binary, b);
|
| @@ -678,30 +683,30 @@ foreach zSql [list {
|
| CREATE TABLE c(x REFERENCES p(a));
|
| }] {
|
| drop_all_tables
|
| - do_test fkey2-10.1.[incr tn] {
|
| + do_test without_rowid3-10.1.[incr tn] {
|
| execsql $zSql
|
| catchsql { INSERT INTO c DEFAULT VALUES }
|
| - } {1 {foreign key mismatch}}
|
| + } {/1 {foreign key mismatch - "c" referencing "."}/}
|
| }
|
|
|
| # "rowid" cannot be used as part of a child or parent key definition
|
| # unless it happens to be the name of an explicitly declared column.
|
| #
|
| -do_test fkey2-10.2.1 {
|
| +do_test without_rowid3-10.2.1 {
|
| drop_all_tables
|
| catchsql {
|
| - CREATE TABLE t1(a PRIMARY KEY, b);
|
| + CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE t2(c, d, FOREIGN KEY(rowid) REFERENCES t1(a));
|
| }
|
| } {1 {unknown column "rowid" in foreign key definition}}
|
| -do_test fkey2-10.2.2 {
|
| +do_test without_rowid3-10.2.2 {
|
| drop_all_tables
|
| catchsql {
|
| - CREATE TABLE t1(a PRIMARY KEY, b);
|
| + CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE t2(rowid, d, FOREIGN KEY(rowid) REFERENCES t1(a));
|
| }
|
| } {0 {}}
|
| -do_test fkey2-10.2.1 {
|
| +do_test without_rowid3-10.2.1 {
|
| drop_all_tables
|
| catchsql {
|
| CREATE TABLE t1(a, b);
|
| @@ -709,11 +714,11 @@ do_test fkey2-10.2.1 {
|
| INSERT INTO t1(rowid, a, b) VALUES(1, 1, 1);
|
| INSERT INTO t2 VALUES(1, 1);
|
| }
|
| -} {1 {foreign key mismatch}}
|
| -do_test fkey2-10.2.2 {
|
| +} {1 {foreign key mismatch - "t2" referencing "t1"}}
|
| +do_test without_rowid3-10.2.2 {
|
| drop_all_tables
|
| catchsql {
|
| - CREATE TABLE t1(rowid PRIMARY KEY, b);
|
| + CREATE TABLE t1(rowid PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(rowid));
|
| INSERT INTO t1(rowid, b) VALUES(1, 1);
|
| INSERT INTO t2 VALUES(1, 1);
|
| @@ -722,12 +727,12 @@ do_test fkey2-10.2.2 {
|
|
|
|
|
| #-------------------------------------------------------------------------
|
| -# The following tests, fkey2-11.*, test CASCADE actions.
|
| +# The following tests, without_rowid3-11.*, test CASCADE actions.
|
| #
|
| drop_all_tables
|
| -do_test fkey2-11.1.1 {
|
| +do_test without_rowid3-11.1.1 {
|
| execsql {
|
| - CREATE TABLE t1(a INTEGER PRIMARY KEY, b);
|
| + CREATE TABLE t1(a INT PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE t2(c, d, FOREIGN KEY(c) REFERENCES t1(a) ON UPDATE CASCADE);
|
|
|
| INSERT INTO t1 VALUES(10, 100);
|
| @@ -738,12 +743,12 @@ do_test fkey2-11.1.1 {
|
| } {15 100}
|
|
|
| #-------------------------------------------------------------------------
|
| -# The following tests, fkey2-12.*, test RESTRICT actions.
|
| +# The following tests, without_rowid3-12.*, test RESTRICT actions.
|
| #
|
| drop_all_tables
|
| -do_test fkey2-12.1.1 {
|
| +do_test without_rowid3-12.1.1 {
|
| execsql {
|
| - CREATE TABLE t1(a, b PRIMARY KEY);
|
| + CREATE TABLE t1(a, b PRIMARY KEY) WITHOUT rowid;
|
| CREATE TABLE t2(
|
| x REFERENCES t1 ON UPDATE RESTRICT DEFERRABLE INITIALLY DEFERRED
|
| );
|
| @@ -752,23 +757,23 @@ do_test fkey2-12.1.1 {
|
| INSERT INTO t1 VALUES(3, 'three');
|
| }
|
| } {}
|
| -do_test fkey2-12.1.2 {
|
| +do_test without_rowid3-12.1.2 {
|
| execsql "BEGIN"
|
| execsql "INSERT INTO t2 VALUES('two')"
|
| } {}
|
| -do_test fkey2-12.1.3 {
|
| +do_test without_rowid3-12.1.3 {
|
| execsql "UPDATE t1 SET b = 'four' WHERE b = 'one'"
|
| } {}
|
| -do_test fkey2-12.1.4 {
|
| +do_test without_rowid3-12.1.4 {
|
| catchsql "UPDATE t1 SET b = 'five' WHERE b = 'two'"
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-12.1.5 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-12.1.5 {
|
| execsql "DELETE FROM t1 WHERE b = 'two'"
|
| } {}
|
| -do_test fkey2-12.1.6 {
|
| +do_test without_rowid3-12.1.6 {
|
| catchsql "COMMIT"
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-12.1.7 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-12.1.7 {
|
| execsql {
|
| INSERT INTO t1 VALUES(2, 'two');
|
| COMMIT;
|
| @@ -776,9 +781,9 @@ do_test fkey2-12.1.7 {
|
| } {}
|
|
|
| drop_all_tables
|
| -do_test fkey2-12.2.1 {
|
| +do_test without_rowid3-12.2.1 {
|
| execsql {
|
| - CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY);
|
| + CREATE TABLE t1(x COLLATE NOCASE PRIMARY KEY) WITHOUT rowid;
|
| CREATE TRIGGER tt1 AFTER DELETE ON t1
|
| WHEN EXISTS ( SELECT 1 FROM t2 WHERE old.x = y )
|
| BEGIN
|
| @@ -794,14 +799,14 @@ do_test fkey2-12.2.1 {
|
| SELECT * FROM t2;
|
| }
|
| } {A B a b}
|
| -do_test fkey2-12.2.2 {
|
| +do_test without_rowid3-12.2.2 {
|
| execsql { DELETE FROM t1 }
|
| execsql {
|
| SELECT * FROM t1;
|
| SELECT * FROM t2;
|
| }
|
| } {A B a b}
|
| -do_test fkey2-12.2.3 {
|
| +do_test without_rowid3-12.2.3 {
|
| execsql {
|
| DROP TABLE t2;
|
| CREATE TABLE t2(y REFERENCES t1 ON DELETE RESTRICT);
|
| @@ -809,8 +814,8 @@ do_test fkey2-12.2.3 {
|
| INSERT INTO t2 VALUES('b');
|
| }
|
| catchsql { DELETE FROM t1 }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-12.2.4 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-12.2.4 {
|
| execsql {
|
| SELECT * FROM t1;
|
| SELECT * FROM t2;
|
| @@ -818,7 +823,7 @@ do_test fkey2-12.2.4 {
|
| } {A B a b}
|
|
|
| drop_all_tables
|
| -do_test fkey2-12.3.1 {
|
| +do_test without_rowid3-12.3.1 {
|
| execsql {
|
| CREATE TABLE up(
|
| c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
|
| @@ -826,7 +831,7 @@ do_test fkey2-12.3.1 {
|
| c20, c21, c22, c23, c24, c25, c26, c27, c28, c29,
|
| c30, c31, c32, c33, c34, c35, c36, c37, c38, c39,
|
| PRIMARY KEY(c34, c35)
|
| - );
|
| + ) WITHOUT rowid;
|
| CREATE TABLE down(
|
| c00, c01, c02, c03, c04, c05, c06, c07, c08, c09,
|
| c10, c11, c12, c13, c14, c15, c16, c17, c18, c19,
|
| @@ -836,7 +841,7 @@ do_test fkey2-12.3.1 {
|
| );
|
| }
|
| } {}
|
| -do_test fkey2-12.3.2 {
|
| +do_test without_rowid3-12.3.2 {
|
| execsql {
|
| INSERT INTO up(c34, c35) VALUES('yes', 'no');
|
| INSERT INTO down(c39, c38) VALUES('yes', 'no');
|
| @@ -845,17 +850,17 @@ do_test fkey2-12.3.2 {
|
| DELETE FROM down;
|
| }
|
| } {no possibly}
|
| -do_test fkey2-12.3.3 {
|
| +do_test without_rowid3-12.3.3 {
|
| catchsql { INSERT INTO down(c39, c38) VALUES('yes', 'no') }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-12.3.4 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-12.3.4 {
|
| execsql {
|
| INSERT INTO up(c34, c35) VALUES('yes', 'no');
|
| INSERT INTO down(c39, c38) VALUES('yes', 'no');
|
| }
|
| catchsql { DELETE FROM up WHERE c34 = 'yes' }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-12.3.5 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-12.3.5 {
|
| execsql {
|
| DELETE FROM up WHERE c34 = 'possibly';
|
| SELECT c34, c35 FROM up;
|
| @@ -864,13 +869,13 @@ do_test fkey2-12.3.5 {
|
| } {yes no yes no}
|
|
|
| #-------------------------------------------------------------------------
|
| -# The following tests, fkey2-13.*, test that FK processing is performed
|
| +# The following tests, without_rowid3-13.*, test that FK processing is performed
|
| # when rows are REPLACEd.
|
| #
|
| drop_all_tables
|
| -do_test fkey2-13.1.1 {
|
| +do_test without_rowid3-13.1.1 {
|
| execsql {
|
| - CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c));
|
| + CREATE TABLE pp(a UNIQUE, b, c, PRIMARY KEY(b, c)) WITHOUT rowid;
|
| CREATE TABLE cc(d, e, f UNIQUE, FOREIGN KEY(d, e) REFERENCES pp);
|
| INSERT INTO pp VALUES(1, 2, 3);
|
| INSERT INTO cc VALUES(2, 3, 1);
|
| @@ -878,22 +883,21 @@ do_test fkey2-13.1.1 {
|
| } {}
|
| foreach {tn stmt} {
|
| 1 "REPLACE INTO pp VALUES(1, 4, 5)"
|
| - 2 "REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 3, 4)"
|
| } {
|
| - do_test fkey2-13.1.$tn.1 {
|
| + do_test without_rowid3-13.1.$tn.1 {
|
| catchsql $stmt
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-13.1.$tn.2 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-13.1.$tn.2 {
|
| execsql {
|
| SELECT * FROM pp;
|
| SELECT * FROM cc;
|
| }
|
| } {1 2 3 2 3 1}
|
| - do_test fkey2-13.1.$tn.3 {
|
| + do_test without_rowid3-13.1.$tn.3 {
|
| execsql BEGIN;
|
| catchsql $stmt
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-13.1.$tn.4 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-13.1.$tn.4 {
|
| execsql {
|
| COMMIT;
|
| SELECT * FROM pp;
|
| @@ -901,52 +905,38 @@ foreach {tn stmt} {
|
| }
|
| } {1 2 3 2 3 1}
|
| }
|
| -do_test fkey2-13.1.3 {
|
| - execsql {
|
| - REPLACE INTO pp(rowid, a, b, c) VALUES(1, 2, 2, 3);
|
| - SELECT rowid, * FROM pp;
|
| - SELECT * FROM cc;
|
| - }
|
| -} {1 2 2 3 2 3 1}
|
| -do_test fkey2-13.1.4 {
|
| - execsql {
|
| - REPLACE INTO pp(rowid, a, b, c) VALUES(2, 2, 2, 3);
|
| - SELECT rowid, * FROM pp;
|
| - SELECT * FROM cc;
|
| - }
|
| -} {2 2 2 3 2 3 1}
|
|
|
| #-------------------------------------------------------------------------
|
| -# The following tests, fkey2-14.*, test that the "DROP TABLE" and "ALTER
|
| +# The following tests, without_rowid3-14.*, test that the "DROP TABLE" and "ALTER
|
| # TABLE" commands work as expected wrt foreign key constraints.
|
| #
|
| -# fkey2-14.1*: ALTER TABLE ADD COLUMN
|
| -# fkey2-14.2*: ALTER TABLE RENAME TABLE
|
| -# fkey2-14.3*: DROP TABLE
|
| +# without_rowid3-14.1*: ALTER TABLE ADD COLUMN
|
| +# without_rowid3-14.2*: ALTER TABLE RENAME TABLE
|
| +# without_rowid3-14.3*: DROP TABLE
|
| #
|
| drop_all_tables
|
| ifcapable altertable {
|
| - do_test fkey2-14.1.1 {
|
| + do_test without_rowid3-14.1.1 {
|
| # Adding a column with a REFERENCES clause is not supported.
|
| execsql {
|
| - CREATE TABLE t1(a PRIMARY KEY);
|
| + CREATE TABLE t1(a PRIMARY KEY) WITHOUT rowid;
|
| CREATE TABLE t2(a, b);
|
| }
|
| catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
|
| } {0 {}}
|
| - do_test fkey2-14.1.2 {
|
| + do_test without_rowid3-14.1.2 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
|
| } {0 {}}
|
| - do_test fkey2-14.1.3 {
|
| + do_test without_rowid3-14.1.3 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
|
| } {0 {}}
|
| - do_test fkey2-14.1.4 {
|
| + do_test without_rowid3-14.1.4 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
|
| } {1 {Cannot add a REFERENCES column with non-NULL default value}}
|
| - do_test fkey2-14.1.5 {
|
| + do_test without_rowid3-14.1.5 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
|
| } {1 {Cannot add a REFERENCES column with non-NULL default value}}
|
| - do_test fkey2-14.1.6 {
|
| + do_test without_rowid3-14.1.6 {
|
| execsql {
|
| PRAGMA foreign_keys = off;
|
| ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
|
| @@ -961,79 +951,82 @@ ifcapable altertable {
|
| proc test_rename_parent {zCreate zOld zNew} {
|
| db eval {SELECT sqlite_rename_parent($zCreate, $zOld, $zNew)}
|
| }
|
| - do_test fkey2-14.2.1.1 {
|
| + do_test without_rowid3-14.2.1.1 {
|
| test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
|
| } {{CREATE TABLE t1(a REFERENCES "t3")}}
|
| - do_test fkey2-14.2.1.2 {
|
| + do_test without_rowid3-14.2.1.2 {
|
| test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
|
| } {{CREATE TABLE t1(a REFERENCES t2)}}
|
| - do_test fkey2-14.2.1.3 {
|
| + do_test without_rowid3-14.2.1.3 {
|
| test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
|
| } {{CREATE TABLE t1(a REFERENCES "t3")}}
|
|
|
| # Test ALTER TABLE RENAME TABLE a bit.
|
| #
|
| - do_test fkey2-14.2.2.1 {
|
| + do_test without_rowid3-14.2.2.1 {
|
| drop_all_tables
|
| execsql {
|
| - CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1);
|
| - CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
|
| + CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid;
|
| + CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
|
| + WITHOUT rowid;
|
| CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
|
| }
|
| execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
|
| } [list \
|
| - {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \
|
| - {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \
|
| + {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid} \
|
| + {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
|
| + WITHOUT rowid} \
|
| {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \
|
| ]
|
| - do_test fkey2-14.2.2.2 {
|
| + do_test without_rowid3-14.2.2.2 {
|
| execsql { ALTER TABLE t1 RENAME TO t4 }
|
| execsql { SELECT sql FROM sqlite_master WHERE type = 'table'}
|
| } [list \
|
| - {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \
|
| - {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \
|
| + {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid} \
|
| + {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)
|
| + WITHOUT rowid} \
|
| {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
|
| ]
|
| - do_test fkey2-14.2.2.3 {
|
| + do_test without_rowid3-14.2.2.3 {
|
| catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-14.2.2.4 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-14.2.2.4 {
|
| execsql { INSERT INTO t4 VALUES(1, NULL) }
|
| } {}
|
| - do_test fkey2-14.2.2.5 {
|
| + do_test without_rowid3-14.2.2.5 {
|
| catchsql { UPDATE t4 SET b = 5 }
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-14.2.2.6 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-14.2.2.6 {
|
| catchsql { UPDATE t4 SET b = 1 }
|
| } {0 {}}
|
| - do_test fkey2-14.2.2.7 {
|
| + do_test without_rowid3-14.2.2.7 {
|
| execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
|
| } {}
|
|
|
| # Repeat for TEMP tables
|
| #
|
| drop_all_tables
|
| - do_test fkey2-14.1tmp.1 {
|
| + do_test without_rowid3-14.1tmp.1 {
|
| # Adding a column with a REFERENCES clause is not supported.
|
| execsql {
|
| - CREATE TEMP TABLE t1(a PRIMARY KEY);
|
| + CREATE TEMP TABLE t1(a PRIMARY KEY) WITHOUT rowid;
|
| CREATE TEMP TABLE t2(a, b);
|
| }
|
| catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
|
| } {0 {}}
|
| - do_test fkey2-14.1tmp.2 {
|
| + do_test without_rowid3-14.1tmp.2 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
|
| } {0 {}}
|
| - do_test fkey2-14.1tmp.3 {
|
| + do_test without_rowid3-14.1tmp.3 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
|
| } {0 {}}
|
| - do_test fkey2-14.1tmp.4 {
|
| + do_test without_rowid3-14.1tmp.4 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
|
| } {1 {Cannot add a REFERENCES column with non-NULL default value}}
|
| - do_test fkey2-14.1tmp.5 {
|
| + do_test without_rowid3-14.1tmp.5 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
|
| } {1 {Cannot add a REFERENCES column with non-NULL default value}}
|
| - do_test fkey2-14.1tmp.6 {
|
| + do_test without_rowid3-14.1tmp.6 {
|
| execsql {
|
| PRAGMA foreign_keys = off;
|
| ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
|
| @@ -1042,80 +1035,83 @@ ifcapable altertable {
|
| }
|
| } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
|
|
|
| - do_test fkey2-14.2tmp.1.1 {
|
| + do_test without_rowid3-14.2tmp.1.1 {
|
| test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
|
| } {{CREATE TABLE t1(a REFERENCES "t3")}}
|
| - do_test fkey2-14.2tmp.1.2 {
|
| + do_test without_rowid3-14.2tmp.1.2 {
|
| test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
|
| } {{CREATE TABLE t1(a REFERENCES t2)}}
|
| - do_test fkey2-14.2tmp.1.3 {
|
| + do_test without_rowid3-14.2tmp.1.3 {
|
| test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
|
| } {{CREATE TABLE t1(a REFERENCES "t3")}}
|
|
|
| # Test ALTER TABLE RENAME TABLE a bit.
|
| #
|
| - do_test fkey2-14.2tmp.2.1 {
|
| + do_test without_rowid3-14.2tmp.2.1 {
|
| drop_all_tables
|
| execsql {
|
| - CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1);
|
| - CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
|
| + CREATE TEMP TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid;
|
| + CREATE TEMP TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
|
| + WITHOUT rowid;
|
| CREATE TEMP TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
|
| }
|
| execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
|
| } [list \
|
| - {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \
|
| - {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \
|
| + {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid} \
|
| + {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
|
| + WITHOUT rowid} \
|
| {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \
|
| ]
|
| - do_test fkey2-14.2tmp.2.2 {
|
| + do_test without_rowid3-14.2tmp.2.2 {
|
| execsql { ALTER TABLE t1 RENAME TO t4 }
|
| execsql { SELECT sql FROM sqlite_temp_master WHERE type = 'table'}
|
| } [list \
|
| - {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \
|
| - {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \
|
| + {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid} \
|
| + {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)
|
| + WITHOUT rowid} \
|
| {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
|
| ]
|
| - do_test fkey2-14.2tmp.2.3 {
|
| + do_test without_rowid3-14.2tmp.2.3 {
|
| catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-14.2tmp.2.4 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-14.2tmp.2.4 {
|
| execsql { INSERT INTO t4 VALUES(1, NULL) }
|
| } {}
|
| - do_test fkey2-14.2tmp.2.5 {
|
| + do_test without_rowid3-14.2tmp.2.5 {
|
| catchsql { UPDATE t4 SET b = 5 }
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-14.2tmp.2.6 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-14.2tmp.2.6 {
|
| catchsql { UPDATE t4 SET b = 1 }
|
| } {0 {}}
|
| - do_test fkey2-14.2tmp.2.7 {
|
| + do_test without_rowid3-14.2tmp.2.7 {
|
| execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
|
| } {}
|
|
|
| # Repeat for ATTACH-ed tables
|
| #
|
| drop_all_tables
|
| - do_test fkey2-14.1aux.1 {
|
| + do_test without_rowid3-14.1aux.1 {
|
| # Adding a column with a REFERENCES clause is not supported.
|
| execsql {
|
| ATTACH ':memory:' AS aux;
|
| - CREATE TABLE aux.t1(a PRIMARY KEY);
|
| + CREATE TABLE aux.t1(a PRIMARY KEY) WITHOUT rowid;
|
| CREATE TABLE aux.t2(a, b);
|
| }
|
| catchsql { ALTER TABLE t2 ADD COLUMN c REFERENCES t1 }
|
| } {0 {}}
|
| - do_test fkey2-14.1aux.2 {
|
| + do_test without_rowid3-14.1aux.2 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN d DEFAULT NULL REFERENCES t1 }
|
| } {0 {}}
|
| - do_test fkey2-14.1aux.3 {
|
| + do_test without_rowid3-14.1aux.3 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN e REFERENCES t1 DEFAULT NULL}
|
| } {0 {}}
|
| - do_test fkey2-14.1aux.4 {
|
| + do_test without_rowid3-14.1aux.4 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN f REFERENCES t1 DEFAULT 'text'}
|
| } {1 {Cannot add a REFERENCES column with non-NULL default value}}
|
| - do_test fkey2-14.1aux.5 {
|
| + do_test without_rowid3-14.1aux.5 {
|
| catchsql { ALTER TABLE t2 ADD COLUMN g DEFAULT CURRENT_TIME REFERENCES t1 }
|
| } {1 {Cannot add a REFERENCES column with non-NULL default value}}
|
| - do_test fkey2-14.1aux.6 {
|
| + do_test without_rowid3-14.1aux.6 {
|
| execsql {
|
| PRAGMA foreign_keys = off;
|
| ALTER TABLE t2 ADD COLUMN h DEFAULT 'text' REFERENCES t1;
|
| @@ -1124,110 +1120,113 @@ ifcapable altertable {
|
| }
|
| } {{CREATE TABLE t2(a, b, c REFERENCES t1, d DEFAULT NULL REFERENCES t1, e REFERENCES t1 DEFAULT NULL, h DEFAULT 'text' REFERENCES t1)}}
|
|
|
| - do_test fkey2-14.2aux.1.1 {
|
| + do_test without_rowid3-14.2aux.1.1 {
|
| test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t2 t3
|
| } {{CREATE TABLE t1(a REFERENCES "t3")}}
|
| - do_test fkey2-14.2aux.1.2 {
|
| + do_test without_rowid3-14.2aux.1.2 {
|
| test_rename_parent {CREATE TABLE t1(a REFERENCES t2)} t4 t3
|
| } {{CREATE TABLE t1(a REFERENCES t2)}}
|
| - do_test fkey2-14.2aux.1.3 {
|
| + do_test without_rowid3-14.2aux.1.3 {
|
| test_rename_parent {CREATE TABLE t1(a REFERENCES "t2")} t2 t3
|
| } {{CREATE TABLE t1(a REFERENCES "t3")}}
|
|
|
| # Test ALTER TABLE RENAME TABLE a bit.
|
| #
|
| - do_test fkey2-14.2aux.2.1 {
|
| + do_test without_rowid3-14.2aux.2.1 {
|
| drop_all_tables
|
| execsql {
|
| - CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1);
|
| - CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2);
|
| + CREATE TABLE aux.t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid;
|
| + CREATE TABLE aux.t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
|
| + WITHOUT rowid;
|
| CREATE TABLE aux.t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1);
|
| }
|
| execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
|
| } [list \
|
| - {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1)} \
|
| - {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)} \
|
| + {CREATE TABLE t1(a PRIMARY KEY, b REFERENCES t1) WITHOUT rowid} \
|
| + {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES t1, c REFERENCES t2)
|
| + WITHOUT rowid} \
|
| {CREATE TABLE t3(a REFERENCES t1, b REFERENCES t2, c REFERENCES t1)} \
|
| ]
|
| - do_test fkey2-14.2aux.2.2 {
|
| + do_test without_rowid3-14.2aux.2.2 {
|
| execsql { ALTER TABLE t1 RENAME TO t4 }
|
| execsql { SELECT sql FROM aux.sqlite_master WHERE type = 'table'}
|
| } [list \
|
| - {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4")} \
|
| - {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)} \
|
| + {CREATE TABLE "t4"(a PRIMARY KEY, b REFERENCES "t4") WITHOUT rowid} \
|
| + {CREATE TABLE t2(a PRIMARY KEY, b REFERENCES "t4", c REFERENCES t2)
|
| + WITHOUT rowid} \
|
| {CREATE TABLE t3(a REFERENCES "t4", b REFERENCES t2, c REFERENCES "t4")} \
|
| ]
|
| - do_test fkey2-14.2aux.2.3 {
|
| + do_test without_rowid3-14.2aux.2.3 {
|
| catchsql { INSERT INTO t3 VALUES(1, 2, 3) }
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-14.2aux.2.4 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-14.2aux.2.4 {
|
| execsql { INSERT INTO t4 VALUES(1, NULL) }
|
| } {}
|
| - do_test fkey2-14.2aux.2.5 {
|
| + do_test without_rowid3-14.2aux.2.5 {
|
| catchsql { UPDATE t4 SET b = 5 }
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-14.2aux.2.6 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-14.2aux.2.6 {
|
| catchsql { UPDATE t4 SET b = 1 }
|
| } {0 {}}
|
| - do_test fkey2-14.2aux.2.7 {
|
| + do_test without_rowid3-14.2aux.2.7 {
|
| execsql { INSERT INTO t3 VALUES(1, NULL, 1) }
|
| } {}
|
| }
|
|
|
| -do_test fkey-2.14.3.1 {
|
| +do_test without_rowid3-2.14.3.1 {
|
| drop_all_tables
|
| execsql {
|
| CREATE TABLE t1(a, b REFERENCES nosuchtable);
|
| DROP TABLE t1;
|
| }
|
| } {}
|
| -do_test fkey-2.14.3.2 {
|
| +do_test without_rowid3-2.14.3.2 {
|
| execsql {
|
| - CREATE TABLE t1(a PRIMARY KEY, b);
|
| + CREATE TABLE t1(a PRIMARY KEY, b) WITHOUT rowid;
|
| INSERT INTO t1 VALUES('a', 1);
|
| CREATE TABLE t2(x REFERENCES t1);
|
| INSERT INTO t2 VALUES('a');
|
| }
|
| } {}
|
| -do_test fkey-2.14.3.3 {
|
| +do_test without_rowid3-2.14.3.3 {
|
| catchsql { DROP TABLE t1 }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey-2.14.3.4 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-2.14.3.4 {
|
| execsql {
|
| DELETE FROM t2;
|
| DROP TABLE t1;
|
| }
|
| } {}
|
| -do_test fkey-2.14.3.4 {
|
| +do_test without_rowid3-2.14.3.4 {
|
| catchsql { INSERT INTO t2 VALUES('x') }
|
| } {1 {no such table: main.t1}}
|
| -do_test fkey-2.14.3.5 {
|
| +do_test without_rowid3-2.14.3.5 {
|
| execsql {
|
| - CREATE TABLE t1(x PRIMARY KEY);
|
| + CREATE TABLE t1(x PRIMARY KEY) WITHOUT rowid;
|
| INSERT INTO t1 VALUES('x');
|
| }
|
| execsql { INSERT INTO t2 VALUES('x') }
|
| } {}
|
| -do_test fkey-2.14.3.6 {
|
| +do_test without_rowid3-2.14.3.6 {
|
| catchsql { DROP TABLE t1 }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey-2.14.3.7 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-2.14.3.7 {
|
| execsql {
|
| DROP TABLE t2;
|
| DROP TABLE t1;
|
| }
|
| } {}
|
| -do_test fkey-2.14.3.8 {
|
| +do_test without_rowid3-2.14.3.8 {
|
| execsql {
|
| - CREATE TABLE pp(x, y, PRIMARY KEY(x, y));
|
| + CREATE TABLE pp(x, y, PRIMARY KEY(x, y)) WITHOUT ROWID;
|
| CREATE TABLE cc(a, b, FOREIGN KEY(a, b) REFERENCES pp(x, z));
|
| }
|
| catchsql { INSERT INTO cc VALUES(1, 2) }
|
| -} {1 {foreign key mismatch}}
|
| -do_test fkey-2.14.3.9 {
|
| +} {1 {foreign key mismatch - "cc" referencing "pp"}}
|
| +do_test without_rowid3-2.14.3.9 {
|
| execsql { DROP TABLE cc }
|
| } {}
|
| -do_test fkey-2.14.3.10 {
|
| +do_test without_rowid3-2.14.3.10 {
|
| execsql {
|
| CREATE TABLE cc(a, b,
|
| FOREIGN KEY(a, b) REFERENCES pp DEFERRABLE INITIALLY DEFERRED
|
| @@ -1238,12 +1237,12 @@ do_test fkey-2.14.3.10 {
|
| INSERT INTO cc VALUES('a', 'b');
|
| BEGIN;
|
| DROP TABLE pp;
|
| - CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c));
|
| + CREATE TABLE pp(a, b, c, PRIMARY KEY(b, c)) WITHOUT rowid;
|
| INSERT INTO pp VALUES(1, 'a', 'b');
|
| COMMIT;
|
| }
|
| } {}
|
| -do_test fkey-2.14.3.11 {
|
| +do_test without_rowid3-2.14.3.11 {
|
| execsql {
|
| BEGIN;
|
| DROP TABLE cc;
|
| @@ -1251,14 +1250,14 @@ do_test fkey-2.14.3.11 {
|
| COMMIT;
|
| }
|
| } {}
|
| -do_test fkey-2.14.3.12 {
|
| +do_test without_rowid3-2.14.3.12 {
|
| execsql {
|
| CREATE TABLE b1(a, b);
|
| CREATE TABLE b2(a, b REFERENCES b1);
|
| DROP TABLE b1;
|
| }
|
| } {}
|
| -do_test fkey-2.14.3.13 {
|
| +do_test without_rowid3-2.14.3.13 {
|
| execsql {
|
| CREATE TABLE b3(a, b REFERENCES b2 DEFERRABLE INITIALLY DEFERRED);
|
| DROP TABLE b2;
|
| @@ -1269,23 +1268,23 @@ do_test fkey-2.14.3.13 {
|
| # Or dropping a view that an existing FK (incorrectly) refers to. Or either
|
| # of the above scenarios with a virtual table.
|
| drop_all_tables
|
| -do_test fkey-2.14.4.1 {
|
| +do_test without_rowid3-2.14.4.1 {
|
| execsql {
|
| CREATE TABLE t1(x REFERENCES v);
|
| CREATE VIEW v AS SELECT * FROM t1;
|
| }
|
| } {}
|
| -do_test fkey-2.14.4.2 {
|
| +do_test without_rowid3-2.14.4.2 {
|
| execsql {
|
| DROP VIEW v;
|
| }
|
| } {}
|
| ifcapable vtab {
|
| register_echo_module db
|
| - do_test fkey-2.14.4.3 {
|
| + do_test without_rowid3-2.14.4.3 {
|
| execsql { CREATE VIRTUAL TABLE v USING echo(t1) }
|
| } {}
|
| - do_test fkey-2.14.4.2 {
|
| + do_test without_rowid3-2.14.4.2 {
|
| execsql {
|
| DROP TABLE v;
|
| }
|
| @@ -1293,7 +1292,7 @@ ifcapable vtab {
|
| }
|
|
|
| #-------------------------------------------------------------------------
|
| -# The following tests, fkey2-15.*, test that unnecessary FK related scans
|
| +# The following tests, without_rowid3-15.*, test that unnecessary FK related scans
|
| # and lookups are avoided when the constraint counters are zero.
|
| #
|
| drop_all_tables
|
| @@ -1303,9 +1302,9 @@ proc execsqlS {zSql} {
|
| set res [uplevel [list execsql $zSql]]
|
| concat [expr $::sqlite_found_count + $::sqlite_search_count] $res
|
| }
|
| -do_test fkey2-15.1.1 {
|
| +do_test without_rowid3-15.1.1 {
|
| execsql {
|
| - CREATE TABLE pp(a PRIMARY KEY, b);
|
| + CREATE TABLE pp(a PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE cc(x, y REFERENCES pp DEFERRABLE INITIALLY DEFERRED);
|
| INSERT INTO pp VALUES(1, 'one');
|
| INSERT INTO pp VALUES(2, 'two');
|
| @@ -1313,31 +1312,31 @@ do_test fkey2-15.1.1 {
|
| INSERT INTO cc VALUES('song', 2);
|
| }
|
| } {}
|
| -do_test fkey2-15.1.2 {
|
| +do_test without_rowid3-15.1.2 {
|
| execsqlS { INSERT INTO pp VALUES(3, 'three') }
|
| } {0}
|
| -do_test fkey2-15.1.3 {
|
| +do_test without_rowid3-15.1.3 {
|
| execsql {
|
| BEGIN;
|
| INSERT INTO cc VALUES('see', 4); -- Violates deferred constraint
|
| }
|
| execsqlS { INSERT INTO pp VALUES(5, 'five') }
|
| } {2}
|
| -do_test fkey2-15.1.4 {
|
| +do_test without_rowid3-15.1.4 {
|
| execsql { DELETE FROM cc WHERE x = 'see' }
|
| execsqlS { INSERT INTO pp VALUES(6, 'six') }
|
| } {0}
|
| -do_test fkey2-15.1.5 {
|
| +do_test without_rowid3-15.1.5 {
|
| execsql COMMIT
|
| } {}
|
| -do_test fkey2-15.1.6 {
|
| +do_test without_rowid3-15.1.6 {
|
| execsql BEGIN
|
| execsqlS {
|
| DELETE FROM cc WHERE x = 'neung';
|
| ROLLBACK;
|
| }
|
| } {1}
|
| -do_test fkey2-15.1.7 {
|
| +do_test without_rowid3-15.1.7 {
|
| execsql {
|
| BEGIN;
|
| DELETE FROM pp WHERE a = 2;
|
| @@ -1349,49 +1348,136 @@ do_test fkey2-15.1.7 {
|
| } {2}
|
|
|
| #-------------------------------------------------------------------------
|
| -# This next block of tests, fkey2-16.*, test that rows that refer to
|
| +# This next block of tests, without_rowid3-16.*, test that rows that refer to
|
| # themselves may be inserted and deleted.
|
| #
|
| foreach {tn zSchema} {
|
| - 1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a)) }
|
| - 2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) }
|
| - 3 { CREATE TABLE self(a UNIQUE, b INTEGER PRIMARY KEY REFERENCES self(a)) }
|
| + 1 { CREATE TABLE self(a INTEGER PRIMARY KEY, b REFERENCES self(a))
|
| + WITHOUT rowid }
|
| + 2 { CREATE TABLE self(a PRIMARY KEY, b REFERENCES self(a)) WITHOUT rowid }
|
| + 3 { CREATE TABLE self(a UNIQUE, b INT PRIMARY KEY REFERENCES self(a))
|
| + WITHOUT rowid }
|
| } {
|
| drop_all_tables
|
| - do_test fkey2-16.1.$tn.1 {
|
| + do_test without_rowid3-16.1.$tn.1 {
|
| execsql $zSchema
|
| execsql { INSERT INTO self VALUES(13, 13) }
|
| } {}
|
| - do_test fkey2-16.1.$tn.2 {
|
| + do_test without_rowid3-16.1.$tn.2 {
|
| execsql { UPDATE self SET a = 14, b = 14 }
|
| } {}
|
|
|
| - do_test fkey2-16.1.$tn.3 {
|
| + do_test without_rowid3-16.1.$tn.3 {
|
| catchsql { UPDATE self SET b = 15 }
|
| - } {1 {foreign key constraint failed}}
|
| + } {1 {FOREIGN KEY constraint failed}}
|
|
|
| - do_test fkey2-16.1.$tn.4 {
|
| + do_test without_rowid3-16.1.$tn.4 {
|
| catchsql { UPDATE self SET a = 15 }
|
| - } {1 {foreign key constraint failed}}
|
| + } {1 {FOREIGN KEY constraint failed}}
|
|
|
| - do_test fkey2-16.1.$tn.5 {
|
| + do_test without_rowid3-16.1.$tn.5 {
|
| catchsql { UPDATE self SET a = 15, b = 16 }
|
| - } {1 {foreign key constraint failed}}
|
| + } {1 {FOREIGN KEY constraint failed}}
|
|
|
| - do_test fkey2-16.1.$tn.6 {
|
| + do_test without_rowid3-16.1.$tn.6 {
|
| catchsql { UPDATE self SET a = 17, b = 17 }
|
| } {0 {}}
|
|
|
| - do_test fkey2-16.1.$tn.7 {
|
| + do_test without_rowid3-16.1.$tn.7 {
|
| execsql { DELETE FROM self }
|
| } {}
|
| - do_test fkey2-16.1.$tn.8 {
|
| + do_test without_rowid3-16.1.$tn.8 {
|
| catchsql { INSERT INTO self VALUES(20, 21) }
|
| - } {1 {foreign key constraint failed}}
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| }
|
|
|
| +# Additional tests cases using multi-column self-referential
|
| +# FOREIGN KEY constraints.
|
| +#
|
| +drop_all_tables
|
| +do_execsql_test without_rowid3-16.4.1.1 {
|
| + PRAGMA foreign_keys=ON;
|
| + CREATE TABLE t1(a,b,c,d,e,f,
|
| + UNIQUE (a,b),
|
| + PRIMARY KEY (e,c),
|
| + FOREIGN KEY (d,f) REFERENCES t1(e,c)
|
| + ) WITHOUT rowid;
|
| + INSERT INTO t1 VALUES(1,2,3,5,5,3);
|
| + INSERT INTO t1 VALUES(2,3,4,6,6,4);
|
| + INSERT INTO t1 VALUES('x','y',1.5,'fizzle','fizzle',1.5);
|
| + SELECT *, '|' FROM t1 ORDER BY a, b;
|
| +} {1 2 3 5 5 3 | 2 3 4 6 6 4 | x y 1.5 fizzle fizzle 1.5 |}
|
| +
|
| +do_execsql_test without_rowid3-16.4.1.2 {
|
| + UPDATE t1 SET c=99, f=99 WHERE a=1;
|
| + SELECT *, '|' FROM t1 ORDER BY a, b;
|
| +} {1 2 99 5 5 99 | 2 3 4 6 6 4 | x y 1.5 fizzle fizzle 1.5 |}
|
| +
|
| +do_execsql_test without_rowid3-16.4.1.3 {
|
| + UPDATE t1 SET e=876, d=876 WHERE a=2;
|
| + SELECT *, '|' FROM t1 ORDER BY a, b;
|
| +} {1 2 99 5 5 99 | 2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}
|
| +
|
| +do_test without_rowid3-16.4.1.4 {
|
| + catchsql {
|
| + UPDATE t1 SET c=11, e=22 WHERE a=1;
|
| + }
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +
|
| +do_test without_rowid3-16.4.1.5 {
|
| + catchsql {
|
| + UPDATE t1 SET d=11, f=22 WHERE a=1;
|
| + }
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +
|
| +do_execsql_test without_rowid3-16.4.1.6 {
|
| + DELETE FROM t1 WHERE a=1;
|
| + SELECT *, '|' FROM t1 ORDER BY a, b;
|
| +} {2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}
|
| +
|
| +do_execsql_test without_rowid3-16.4.2.1 {
|
| + DROP TABLE t1;
|
| + CREATE TABLE t1(a,b,c,d,e,f,
|
| + PRIMARY KEY (a,b),
|
| + UNIQUE (e,c),
|
| + FOREIGN KEY (d,f) REFERENCES t1(e,c)
|
| + ) WITHOUT rowid;
|
| + INSERT INTO t1 VALUES(1,2,3,5,5,3);
|
| + INSERT INTO t1 VALUES(2,3,4,6,6,4);
|
| + INSERT INTO t1 VALUES('x','y',1.5,'fizzle','fizzle',1.5);
|
| + SELECT *, '|' FROM t1 ORDER BY a, b;
|
| +} {1 2 3 5 5 3 | 2 3 4 6 6 4 | x y 1.5 fizzle fizzle 1.5 |}
|
| +
|
| +do_execsql_test without_rowid3-16.4.2.2 {
|
| + UPDATE t1 SET c=99, f=99 WHERE a=1;
|
| + SELECT *, '|' FROM t1 ORDER BY a, b;
|
| +} {1 2 99 5 5 99 | 2 3 4 6 6 4 | x y 1.5 fizzle fizzle 1.5 |}
|
| +
|
| +do_execsql_test without_rowid3-16.4.2.3 {
|
| + UPDATE t1 SET e=876, d=876 WHERE a=2;
|
| + SELECT *, '|' FROM t1 ORDER BY a, b;
|
| +} {1 2 99 5 5 99 | 2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}
|
| +
|
| +do_test without_rowid3-16.4.2.4 {
|
| + catchsql {
|
| + UPDATE t1 SET c=11, e=22 WHERE a=1;
|
| + }
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +
|
| +do_test without_rowid3-16.4.2.5 {
|
| + catchsql {
|
| + UPDATE t1 SET d=11, f=22 WHERE a=1;
|
| + }
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +
|
| +do_execsql_test without_rowid3-16.4.2.6 {
|
| + DELETE FROM t1 WHERE a=1;
|
| + SELECT *, '|' FROM t1 ORDER BY a, b;
|
| +} {2 3 4 876 876 4 | x y 1.5 fizzle fizzle 1.5 |}
|
| +
|
| +
|
| #-------------------------------------------------------------------------
|
| -# This next block of tests, fkey2-17.*, tests that if "PRAGMA count_changes"
|
| +# This next block of tests, without_rowid3-17.*, tests that if "PRAGMA count_changes"
|
| # is turned on statements that violate immediate FK constraints return
|
| # SQLITE_CONSTRAINT immediately, not after returning a number of rows.
|
| # Whereas statements that violate deferred FK constraints return the number
|
| @@ -1402,7 +1488,7 @@ foreach {tn zSchema} {
|
| # trigger related changes, they are included in sqlite3_total_changes() though.
|
| #
|
| drop_all_tables
|
| -do_test fkey2-17.1.1 {
|
| +do_test without_rowid3-17.1.1 {
|
| execsql { PRAGMA count_changes = 1 }
|
| execsql {
|
| CREATE TABLE one(a, b, c, UNIQUE(b, c));
|
| @@ -1410,23 +1496,26 @@ do_test fkey2-17.1.1 {
|
| INSERT INTO one VALUES(1, 2, 3);
|
| }
|
| } {1}
|
| -do_test fkey2-17.1.2 {
|
| +do_test without_rowid3-17.1.2 {
|
| set STMT [sqlite3_prepare_v2 db "INSERT INTO two VALUES(4, 5, 6)" -1 dummy]
|
| sqlite3_step $STMT
|
| } {SQLITE_CONSTRAINT}
|
| +verify_ex_errcode without_rowid3-17.1.2b SQLITE_CONSTRAINT_FOREIGNKEY
|
| ifcapable autoreset {
|
| - do_test fkey2-17.1.3 {
|
| + do_test without_rowid3-17.1.3 {
|
| sqlite3_step $STMT
|
| } {SQLITE_CONSTRAINT}
|
| + verify_ex_errcode without_rowid3-17.1.3b SQLITE_CONSTRAINT_FOREIGNKEY
|
| } else {
|
| - do_test fkey2-17.1.3 {
|
| + do_test without_rowid3-17.1.3 {
|
| sqlite3_step $STMT
|
| } {SQLITE_MISUSE}
|
| }
|
| -do_test fkey2-17.1.4 {
|
| +do_test without_rowid3-17.1.4 {
|
| sqlite3_finalize $STMT
|
| } {SQLITE_CONSTRAINT}
|
| -do_test fkey2-17.1.5 {
|
| +verify_ex_errcode without_rowid3-17.1.4b SQLITE_CONSTRAINT_FOREIGNKEY
|
| +do_test without_rowid3-17.1.5 {
|
| execsql {
|
| INSERT INTO one VALUES(2, 3, 4);
|
| INSERT INTO one VALUES(3, 4, 5);
|
| @@ -1435,23 +1524,23 @@ do_test fkey2-17.1.5 {
|
| INSERT INTO two VALUES(3, 4, 5);
|
| }
|
| } {1 1 1 1 1}
|
| -do_test fkey2-17.1.6 {
|
| +do_test without_rowid3-17.1.6 {
|
| catchsql {
|
| BEGIN;
|
| INSERT INTO one VALUES(0, 0, 0);
|
| UPDATE two SET e=e+1, f=f+1;
|
| }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-17.1.7 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-17.1.7 {
|
| execsql { SELECT * FROM one }
|
| } {1 2 3 2 3 4 3 4 5 0 0 0}
|
| -do_test fkey2-17.1.8 {
|
| +do_test without_rowid3-17.1.8 {
|
| execsql { SELECT * FROM two }
|
| } {1 2 3 2 3 4 3 4 5}
|
| -do_test fkey2-17.1.9 {
|
| +do_test without_rowid3-17.1.9 {
|
| execsql COMMIT
|
| } {}
|
| -do_test fkey2-17.1.10 {
|
| +do_test without_rowid3-17.1.10 {
|
| execsql {
|
| CREATE TABLE three(
|
| g, h, i,
|
| @@ -1459,31 +1548,33 @@ do_test fkey2-17.1.10 {
|
| );
|
| }
|
| } {}
|
| -do_test fkey2-17.1.11 {
|
| +do_test without_rowid3-17.1.11 {
|
| set STMT [sqlite3_prepare_v2 db "INSERT INTO three VALUES(7, 8, 9)" -1 dummy]
|
| sqlite3_step $STMT
|
| } {SQLITE_ROW}
|
| -do_test fkey2-17.1.12 {
|
| +do_test without_rowid3-17.1.12 {
|
| sqlite3_column_text $STMT 0
|
| } {1}
|
| -do_test fkey2-17.1.13 {
|
| +do_test without_rowid3-17.1.13 {
|
| sqlite3_step $STMT
|
| } {SQLITE_CONSTRAINT}
|
| -do_test fkey2-17.1.14 {
|
| +verify_ex_errcode without_rowid3-17.1.13b SQLITE_CONSTRAINT_FOREIGNKEY
|
| +do_test without_rowid3-17.1.14 {
|
| sqlite3_finalize $STMT
|
| } {SQLITE_CONSTRAINT}
|
| +verify_ex_errcode without_rowid3-17.1.14b SQLITE_CONSTRAINT_FOREIGNKEY
|
|
|
| drop_all_tables
|
| -do_test fkey2-17.2.1 {
|
| +do_test without_rowid3-17.2.1 {
|
| execsql {
|
| - CREATE TABLE high("a'b!" PRIMARY KEY, b);
|
| + CREATE TABLE high("a'b!" PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE low(
|
| c,
|
| "d&6" REFERENCES high ON UPDATE CASCADE ON DELETE CASCADE
|
| );
|
| }
|
| } {}
|
| -do_test fkey2-17.2.2 {
|
| +do_test without_rowid3-17.2.2 {
|
| execsql {
|
| INSERT INTO high VALUES('a', 'b');
|
| INSERT INTO low VALUES('b', 'a');
|
| @@ -1491,28 +1582,28 @@ do_test fkey2-17.2.2 {
|
| db changes
|
| } {1}
|
| set nTotal [db total_changes]
|
| -do_test fkey2-17.2.3 {
|
| +do_test without_rowid3-17.2.3 {
|
| execsql { UPDATE high SET "a'b!" = 'c' }
|
| } {1}
|
| -do_test fkey2-17.2.4 {
|
| +do_test without_rowid3-17.2.4 {
|
| db changes
|
| } {1}
|
| -do_test fkey2-17.2.5 {
|
| +do_test without_rowid3-17.2.5 {
|
| expr [db total_changes] - $nTotal
|
| } {2}
|
| -do_test fkey2-17.2.6 {
|
| +do_test without_rowid3-17.2.6 {
|
| execsql { SELECT * FROM high ; SELECT * FROM low }
|
| } {c b b c}
|
| -do_test fkey2-17.2.7 {
|
| +do_test without_rowid3-17.2.7 {
|
| execsql { DELETE FROM high }
|
| } {1}
|
| -do_test fkey2-17.2.8 {
|
| +do_test without_rowid3-17.2.8 {
|
| db changes
|
| } {1}
|
| -do_test fkey2-17.2.9 {
|
| +do_test without_rowid3-17.2.9 {
|
| expr [db total_changes] - $nTotal
|
| } {4}
|
| -do_test fkey2-17.2.10 {
|
| +do_test without_rowid3-17.2.10 {
|
| execsql { SELECT * FROM high ; SELECT * FROM low }
|
| } {}
|
| execsql { PRAGMA count_changes = 0 }
|
| @@ -1522,21 +1613,21 @@ execsql { PRAGMA count_changes = 0 }
|
| #
|
|
|
| ifcapable auth {
|
| - do_test fkey2-18.1 {
|
| + do_test without_rowid3-18.1 {
|
| execsql {
|
| - CREATE TABLE long(a, b PRIMARY KEY, c);
|
| + CREATE TABLE long(a, b PRIMARY KEY, c) WITHOUT rowid;
|
| CREATE TABLE short(d, e, f REFERENCES long);
|
| CREATE TABLE mid(g, h, i REFERENCES long DEFERRABLE INITIALLY DEFERRED);
|
| }
|
| } {}
|
|
|
| - proc auth {args} {eval lappend ::authargs $args ; return SQLITE_OK}
|
| + proc auth {args} {eval lappend ::authargs [lrange $args 0 4]; return SQLITE_OK}
|
| db auth auth
|
|
|
| # An insert on the parent table must read the child key of any deferred
|
| # foreign key constraints. But not the child key of immediate constraints.
|
| set authargs {}
|
| - do_test fkey2-18.2 {
|
| + do_test without_rowid3-18.2 {
|
| execsql { INSERT INTO long VALUES(1, 2, 3) }
|
| set authargs
|
| } {SQLITE_INSERT long {} main {} SQLITE_READ mid i main {}}
|
| @@ -1544,21 +1635,21 @@ ifcapable auth {
|
| # An insert on the child table of an immediate constraint must read the
|
| # parent key columns (to see if it is a violation or not).
|
| set authargs {}
|
| - do_test fkey2-18.3 {
|
| + do_test without_rowid3-18.3 {
|
| execsql { INSERT INTO short VALUES(1, 3, 2) }
|
| set authargs
|
| } {SQLITE_INSERT short {} main {} SQLITE_READ long b main {}}
|
|
|
| # As must an insert on the child table of a deferred constraint.
|
| set authargs {}
|
| - do_test fkey2-18.4 {
|
| + do_test without_rowid3-18.4 {
|
| execsql { INSERT INTO mid VALUES(1, 3, 2) }
|
| set authargs
|
| } {SQLITE_INSERT mid {} main {} SQLITE_READ long b main {}}
|
|
|
| - do_test fkey2-18.5 {
|
| + do_test without_rowid3-18.5 {
|
| execsql {
|
| - CREATE TABLE nought(a, b PRIMARY KEY, c);
|
| + CREATE TABLE nought(a, b PRIMARY KEY, c) WITHOUT rowid;
|
| CREATE TABLE cross(d, e, f,
|
| FOREIGN KEY(e) REFERENCES nought(b) ON UPDATE CASCADE
|
| );
|
| @@ -1570,13 +1661,13 @@ ifcapable auth {
|
| set authargs
|
| } {SQLITE_UPDATE nought b main {} SQLITE_READ cross e main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {} SQLITE_UPDATE cross e main {} SQLITE_READ nought b main {} SQLITE_READ cross e main {} SQLITE_READ nought b main {} SQLITE_READ nought b main {}}
|
|
|
| - do_test fkey2-18.6 {
|
| + do_test without_rowid3-18.6 {
|
| execsql {SELECT * FROM cross}
|
| } {0 5 0}
|
|
|
| - do_test fkey2-18.7 {
|
| + do_test without_rowid3-18.7 {
|
| execsql {
|
| - CREATE TABLE one(a INTEGER PRIMARY KEY, b);
|
| + CREATE TABLE one(a INT PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE TABLE two(b, c REFERENCES one);
|
| INSERT INTO one VALUES(101, 102);
|
| }
|
| @@ -1593,54 +1684,56 @@ ifcapable auth {
|
| if {[lindex $args 1] == "long"} {return SQLITE_IGNORE}
|
| return SQLITE_OK
|
| }
|
| - do_test fkey2-18.8 {
|
| + do_test without_rowid3-18.8 {
|
| catchsql { INSERT INTO short VALUES(1, 3, 2) }
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-18.9 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-18.9 {
|
| execsql { INSERT INTO short VALUES(1, 3, NULL) }
|
| } {}
|
| - do_test fkey2-18.10 {
|
| + do_test without_rowid3-18.10 {
|
| execsql { SELECT * FROM short }
|
| } {1 3 2 1 3 {}}
|
| - do_test fkey2-18.11 {
|
| + do_test without_rowid3-18.11 {
|
| catchsql { UPDATE short SET f = 2 WHERE f IS NULL }
|
| - } {1 {foreign key constraint failed}}
|
| + } {1 {FOREIGN KEY constraint failed}}
|
|
|
| db auth {}
|
| unset authargs
|
| }
|
|
|
|
|
| -do_test fkey2-19.1 {
|
| +do_test without_rowid3-19.1 {
|
| execsql {
|
| - CREATE TABLE main(id INTEGER PRIMARY KEY);
|
| + CREATE TABLE main(id INT PRIMARY KEY) WITHOUT rowid;
|
| CREATE TABLE sub(id INT REFERENCES main(id));
|
| INSERT INTO main VALUES(1);
|
| INSERT INTO main VALUES(2);
|
| INSERT INTO sub VALUES(2);
|
| }
|
| } {}
|
| -do_test fkey2-19.2 {
|
| +do_test without_rowid3-19.2 {
|
| set S [sqlite3_prepare_v2 db "DELETE FROM main WHERE id = ?" -1 dummy]
|
| sqlite3_bind_int $S 1 2
|
| sqlite3_step $S
|
| } {SQLITE_CONSTRAINT}
|
| -do_test fkey2-19.3 {
|
| +verify_ex_errcode without_rowid3-19.2b SQLITE_CONSTRAINT_FOREIGNKEY
|
| +do_test without_rowid3-19.3 {
|
| sqlite3_reset $S
|
| } {SQLITE_CONSTRAINT}
|
| -do_test fkey2-19.4 {
|
| +verify_ex_errcode without_rowid3-19.3b SQLITE_CONSTRAINT_FOREIGNKEY
|
| +do_test without_rowid3-19.4 {
|
| sqlite3_bind_int $S 1 1
|
| sqlite3_step $S
|
| } {SQLITE_DONE}
|
| -do_test fkey2-19.4 {
|
| +do_test without_rowid3-19.4 {
|
| sqlite3_finalize $S
|
| } {SQLITE_OK}
|
|
|
| drop_all_tables
|
| -do_test fkey2-20.1 {
|
| +do_test without_rowid3-20.1 {
|
| execsql {
|
| - CREATE TABLE pp(a PRIMARY KEY, b);
|
| - CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp);
|
| + CREATE TABLE pp(a PRIMARY KEY, b) WITHOUT rowid;
|
| + CREATE TABLE cc(c PRIMARY KEY, d REFERENCES pp) WITHOUT rowid;
|
| }
|
| } {}
|
|
|
| @@ -1652,24 +1745,24 @@ foreach {tn insert} {
|
| 5 "INSERT OR REPLACE"
|
| 6 "INSERT OR FAIL"
|
| } {
|
| - do_test fkey2-20.2.$tn.1 {
|
| + do_test without_rowid3-20.2.$tn.1 {
|
| catchsql "$insert INTO cc VALUES(1, 2)"
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-20.2.$tn.2 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-20.2.$tn.2 {
|
| execsql { SELECT * FROM cc }
|
| } {}
|
| - do_test fkey2-20.2.$tn.3 {
|
| + do_test without_rowid3-20.2.$tn.3 {
|
| execsql {
|
| BEGIN;
|
| INSERT INTO pp VALUES(2, 'two');
|
| INSERT INTO cc VALUES(1, 2);
|
| }
|
| catchsql "$insert INTO cc VALUES(3, 4)"
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-20.2.$tn.4 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-20.2.$tn.4 {
|
| execsql { COMMIT ; SELECT * FROM cc }
|
| } {1 2}
|
| - do_test fkey2-20.2.$tn.5 {
|
| + do_test without_rowid3-20.2.$tn.5 {
|
| execsql { DELETE FROM cc ; DELETE FROM pp }
|
| } {}
|
| }
|
| @@ -1682,137 +1775,137 @@ foreach {tn update} {
|
| 5 "UPDATE OR REPLACE"
|
| 6 "UPDATE OR FAIL"
|
| } {
|
| - do_test fkey2-20.3.$tn.1 {
|
| + do_test without_rowid3-20.3.$tn.1 {
|
| execsql {
|
| INSERT INTO pp VALUES(2, 'two');
|
| INSERT INTO cc VALUES(1, 2);
|
| }
|
| } {}
|
| - do_test fkey2-20.3.$tn.2 {
|
| + do_test without_rowid3-20.3.$tn.2 {
|
| catchsql "$update pp SET a = 1"
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-20.3.$tn.3 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-20.3.$tn.3 {
|
| execsql { SELECT * FROM pp }
|
| } {2 two}
|
| - do_test fkey2-20.3.$tn.4 {
|
| + do_test without_rowid3-20.3.$tn.4 {
|
| catchsql "$update cc SET d = 1"
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-20.3.$tn.5 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-20.3.$tn.5 {
|
| execsql { SELECT * FROM cc }
|
| } {1 2}
|
| - do_test fkey2-20.3.$tn.6 {
|
| + do_test without_rowid3-20.3.$tn.6 {
|
| execsql {
|
| BEGIN;
|
| INSERT INTO pp VALUES(3, 'three');
|
| }
|
| catchsql "$update pp SET a = 1 WHERE a = 2"
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-20.3.$tn.7 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-20.3.$tn.7 {
|
| execsql { COMMIT ; SELECT * FROM pp }
|
| } {2 two 3 three}
|
| - do_test fkey2-20.3.$tn.8 {
|
| + do_test without_rowid3-20.3.$tn.8 {
|
| execsql {
|
| BEGIN;
|
| INSERT INTO cc VALUES(2, 2);
|
| }
|
| catchsql "$update cc SET d = 1 WHERE c = 1"
|
| - } {1 {foreign key constraint failed}}
|
| - do_test fkey2-20.3.$tn.9 {
|
| + } {1 {FOREIGN KEY constraint failed}}
|
| + do_test without_rowid3-20.3.$tn.9 {
|
| execsql { COMMIT ; SELECT * FROM cc }
|
| } {1 2 2 2}
|
| - do_test fkey2-20.3.$tn.10 {
|
| + do_test without_rowid3-20.3.$tn.10 {
|
| execsql { DELETE FROM cc ; DELETE FROM pp }
|
| } {}
|
| }
|
|
|
| #-------------------------------------------------------------------------
|
| -# The following block of tests, those prefixed with "fkey2-genfkey.", are
|
| -# the same tests that were used to test the ".genfkey" command provided
|
| +# The following block of tests, those prefixed with "without_rowid3-genfkey.",
|
| +# are the same tests that were used to test the ".genfkey" command provided
|
| # by the shell tool. So these tests show that the built-in foreign key
|
| # implementation is more or less compatible with the triggers generated
|
| # by genfkey.
|
| #
|
| drop_all_tables
|
| -do_test fkey2-genfkey.1.1 {
|
| +do_test without_rowid3-genfkey.1.1 {
|
| execsql {
|
| - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
|
| + CREATE TABLE t1(a INT PRIMARY KEY, b, c, UNIQUE(b, c)) WITHOUT rowid;
|
| CREATE TABLE t2(e REFERENCES t1, f);
|
| CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
|
| }
|
| } {}
|
| -do_test fkey2-genfkey.1.2 {
|
| +do_test without_rowid3-genfkey.1.2 {
|
| catchsql { INSERT INTO t2 VALUES(1, 2) }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-genfkey.1.3 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-genfkey.1.3 {
|
| execsql {
|
| INSERT INTO t1 VALUES(1, 2, 3);
|
| INSERT INTO t2 VALUES(1, 2);
|
| }
|
| } {}
|
| -do_test fkey2-genfkey.1.4 {
|
| +do_test without_rowid3-genfkey.1.4 {
|
| execsql { INSERT INTO t2 VALUES(NULL, 3) }
|
| } {}
|
| -do_test fkey2-genfkey.1.5 {
|
| +do_test without_rowid3-genfkey.1.5 {
|
| catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-genfkey.1.6 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-genfkey.1.6 {
|
| execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
|
| } {}
|
| -do_test fkey2-genfkey.1.7 {
|
| +do_test without_rowid3-genfkey.1.7 {
|
| execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
|
| } {}
|
| -do_test fkey2-genfkey.1.8 {
|
| +do_test without_rowid3-genfkey.1.8 {
|
| catchsql { UPDATE t1 SET a = 10 }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-genfkey.1.9 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-genfkey.1.9 {
|
| catchsql { UPDATE t1 SET a = NULL }
|
| -} {1 {datatype mismatch}}
|
| -do_test fkey2-genfkey.1.10 {
|
| +} {1 {NOT NULL constraint failed: t1.a}}
|
| +do_test without_rowid3-genfkey.1.10 {
|
| catchsql { DELETE FROM t1 }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-genfkey.1.11 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-genfkey.1.11 {
|
| execsql { UPDATE t2 SET e = NULL }
|
| } {}
|
| -do_test fkey2-genfkey.1.12 {
|
| +do_test without_rowid3-genfkey.1.12 {
|
| execsql {
|
| UPDATE t1 SET a = 10;
|
| DELETE FROM t1;
|
| DELETE FROM t2;
|
| }
|
| } {}
|
| -do_test fkey2-genfkey.1.13 {
|
| +do_test without_rowid3-genfkey.1.13 {
|
| execsql {
|
| INSERT INTO t3 VALUES(1, NULL, NULL);
|
| INSERT INTO t3 VALUES(1, 2, NULL);
|
| INSERT INTO t3 VALUES(1, NULL, 3);
|
| }
|
| } {}
|
| -do_test fkey2-genfkey.1.14 {
|
| +do_test without_rowid3-genfkey.1.14 {
|
| catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-genfkey.1.15 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-genfkey.1.15 {
|
| execsql {
|
| INSERT INTO t1 VALUES(1, 1, 4);
|
| INSERT INTO t3 VALUES(3, 1, 4);
|
| }
|
| } {}
|
| -do_test fkey2-genfkey.1.16 {
|
| +do_test without_rowid3-genfkey.1.16 {
|
| catchsql { DELETE FROM t1 }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-genfkey.1.17 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-genfkey.1.17 {
|
| catchsql { UPDATE t1 SET b = 10}
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-genfkey.1.18 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-genfkey.1.18 {
|
| execsql { UPDATE t1 SET a = 10}
|
| } {}
|
| -do_test fkey2-genfkey.1.19 {
|
| +do_test without_rowid3-genfkey.1.19 {
|
| catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
|
| -} {1 {foreign key constraint failed}}
|
| +} {1 {FOREIGN KEY constraint failed}}
|
|
|
| drop_all_tables
|
| -do_test fkey2-genfkey.2.1 {
|
| +do_test without_rowid3-genfkey.2.1 {
|
| execsql {
|
| - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
|
| + CREATE TABLE t1(a INT PRIMARY KEY, b, c, UNIQUE(b, c)) WITHOUT rowid;
|
| CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
|
| CREATE TABLE t3(g, h, i,
|
| FOREIGN KEY (h, i)
|
| @@ -1820,7 +1913,7 @@ do_test fkey2-genfkey.2.1 {
|
| );
|
| }
|
| } {}
|
| -do_test fkey2-genfkey.2.2 {
|
| +do_test without_rowid3-genfkey.2.2 {
|
| execsql {
|
| INSERT INTO t1 VALUES(1, 2, 3);
|
| INSERT INTO t1 VALUES(4, 5, 6);
|
| @@ -1828,27 +1921,27 @@ do_test fkey2-genfkey.2.2 {
|
| INSERT INTO t2 VALUES(4, 'four');
|
| }
|
| } {}
|
| -do_test fkey2-genfkey.2.3 {
|
| +do_test without_rowid3-genfkey.2.3 {
|
| execsql {
|
| UPDATE t1 SET a = 2 WHERE a = 1;
|
| SELECT * FROM t2;
|
| }
|
| } {2 one 4 four}
|
| -do_test fkey2-genfkey.2.4 {
|
| +do_test without_rowid3-genfkey.2.4 {
|
| execsql {
|
| DELETE FROM t1 WHERE a = 4;
|
| SELECT * FROM t2;
|
| }
|
| } {2 one}
|
|
|
| -do_test fkey2-genfkey.2.5 {
|
| +do_test without_rowid3-genfkey.2.5 {
|
| execsql {
|
| INSERT INTO t3 VALUES('hello', 2, 3);
|
| UPDATE t1 SET c = 2;
|
| SELECT * FROM t3;
|
| }
|
| } {hello 2 2}
|
| -do_test fkey2-genfkey.2.6 {
|
| +do_test without_rowid3-genfkey.2.6 {
|
| execsql {
|
| DELETE FROM t1;
|
| SELECT * FROM t3;
|
| @@ -1856,9 +1949,9 @@ do_test fkey2-genfkey.2.6 {
|
| } {}
|
|
|
| drop_all_tables
|
| -do_test fkey2-genfkey.3.1 {
|
| +do_test without_rowid3-genfkey.3.1 {
|
| execsql {
|
| - CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
|
| + CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b)) WITHOUT rowid;
|
| CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
|
| CREATE TABLE t3(g, h, i,
|
| FOREIGN KEY (h, i)
|
| @@ -1866,7 +1959,7 @@ do_test fkey2-genfkey.3.1 {
|
| );
|
| }
|
| } {}
|
| -do_test fkey2-genfkey.3.2 {
|
| +do_test without_rowid3-genfkey.3.2 {
|
| execsql {
|
| INSERT INTO t1 VALUES(1, 2, 3);
|
| INSERT INTO t1 VALUES(4, 5, 6);
|
| @@ -1874,26 +1967,26 @@ do_test fkey2-genfkey.3.2 {
|
| INSERT INTO t2 VALUES(4, 'four');
|
| }
|
| } {}
|
| -do_test fkey2-genfkey.3.3 {
|
| +do_test without_rowid3-genfkey.3.3 {
|
| execsql {
|
| UPDATE t1 SET a = 2 WHERE a = 1;
|
| SELECT * FROM t2;
|
| }
|
| } {{} one 4 four}
|
| -do_test fkey2-genfkey.3.4 {
|
| +do_test without_rowid3-genfkey.3.4 {
|
| execsql {
|
| DELETE FROM t1 WHERE a = 4;
|
| SELECT * FROM t2;
|
| }
|
| } {{} one {} four}
|
| -do_test fkey2-genfkey.3.5 {
|
| +do_test without_rowid3-genfkey.3.5 {
|
| execsql {
|
| INSERT INTO t3 VALUES('hello', 2, 3);
|
| UPDATE t1 SET c = 2;
|
| SELECT * FROM t3;
|
| }
|
| } {hello {} {}}
|
| -do_test fkey2-genfkey.3.6 {
|
| +do_test without_rowid3-genfkey.3.6 {
|
| execsql {
|
| UPDATE t3 SET h = 2, i = 2;
|
| DELETE FROM t1;
|
| @@ -1905,10 +1998,10 @@ do_test fkey2-genfkey.3.6 {
|
| # Verify that ticket dd08e5a988d00decc4a543daa8dbbfab9c577ad8 has been
|
| # fixed.
|
| #
|
| -do_test fkey2-dd08e5.1.1 {
|
| +do_test without_rowid3-dd08e5.1.1 {
|
| execsql {
|
| PRAGMA foreign_keys=ON;
|
| - CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b);
|
| + CREATE TABLE tdd08(a INTEGER PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE UNIQUE INDEX idd08 ON tdd08(a,b);
|
| INSERT INTO tdd08 VALUES(200,300);
|
|
|
| @@ -1916,39 +2009,39 @@ do_test fkey2-dd08e5.1.1 {
|
| INSERT INTO tdd08_b VALUES(100,200,300);
|
| }
|
| } {}
|
| -do_test fkey2-dd08e5.1.2 {
|
| +do_test without_rowid3-dd08e5.1.2 {
|
| catchsql {
|
| DELETE FROM tdd08;
|
| }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-dd08e5.1.3 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-dd08e5.1.3 {
|
| execsql {
|
| SELECT * FROM tdd08;
|
| }
|
| } {200 300}
|
| -do_test fkey2-dd08e5.1.4 {
|
| +do_test without_rowid3-dd08e5.1.4 {
|
| catchsql {
|
| INSERT INTO tdd08_b VALUES(400,500,300);
|
| }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-dd08e5.1.5 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-dd08e5.1.5 {
|
| catchsql {
|
| UPDATE tdd08_b SET x=x+1;
|
| }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-dd08e5.1.6 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-dd08e5.1.6 {
|
| catchsql {
|
| UPDATE tdd08 SET a=a+1;
|
| }
|
| -} {1 {foreign key constraint failed}}
|
| +} {1 {FOREIGN KEY constraint failed}}
|
|
|
| #-------------------------------------------------------------------------
|
| # Verify that ticket ce7c133ea6cc9ccdc1a60d80441f80b6180f5eba
|
| # fixed.
|
| #
|
| -do_test fkey2-ce7c13.1.1 {
|
| +do_test without_rowid3-ce7c13.1.1 {
|
| execsql {
|
| - CREATE TABLE tce71(a INTEGER PRIMARY KEY, b);
|
| + CREATE TABLE tce71(a INTEGER PRIMARY KEY, b) WITHOUT rowid;
|
| CREATE UNIQUE INDEX ice71 ON tce71(a,b);
|
| INSERT INTO tce71 VALUES(100,200);
|
| CREATE TABLE tce72(w, x, y, FOREIGN KEY(x,y) REFERENCES tce71(a,b));
|
| @@ -1957,19 +2050,19 @@ do_test fkey2-ce7c13.1.1 {
|
| SELECT * FROM tce71, tce72;
|
| }
|
| } {100 200 300 100 200}
|
| -do_test fkey2-ce7c13.1.2 {
|
| +do_test without_rowid3-ce7c13.1.2 {
|
| catchsql {
|
| UPDATE tce71 set b = 201 where a = 100;
|
| }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-ce7c13.1.3 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-ce7c13.1.3 {
|
| catchsql {
|
| UPDATE tce71 set a = 101 where a = 100;
|
| }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-ce7c13.1.4 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-ce7c13.1.4 {
|
| execsql {
|
| - CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b));
|
| + CREATE TABLE tce73(a INTEGER PRIMARY KEY, b, UNIQUE(a,b)) WITHOUT rowid;
|
| INSERT INTO tce73 VALUES(100,200);
|
| CREATE TABLE tce74(w, x, y, FOREIGN KEY(x,y) REFERENCES tce73(a,b));
|
| INSERT INTO tce74 VALUES(300,100,200);
|
| @@ -1977,15 +2070,15 @@ do_test fkey2-ce7c13.1.4 {
|
| SELECT * FROM tce73, tce74;
|
| }
|
| } {100 200 300 100 200}
|
| -do_test fkey2-ce7c13.1.5 {
|
| +do_test without_rowid3-ce7c13.1.5 {
|
| catchsql {
|
| UPDATE tce73 set b = 201 where a = 100;
|
| }
|
| -} {1 {foreign key constraint failed}}
|
| -do_test fkey2-ce7c13.1.6 {
|
| +} {1 {FOREIGN KEY constraint failed}}
|
| +do_test without_rowid3-ce7c13.1.6 {
|
| catchsql {
|
| UPDATE tce73 set a = 101 where a = 100;
|
| }
|
| -} {1 {foreign key constraint failed}}
|
| +} {1 {FOREIGN KEY constraint failed}}
|
|
|
| finish_test
|
|
|
Chromium Code Reviews
Issue 883353008:
[sql] Import reference version of SQLite 3.8.7.4. (Closed)
Base URL: http://chromium.googlesource.com/chromium/src.git@master