# 2013-07-11
#
# The author disclaims copyright to this source code. In place of
# a legal notice, here is a blessing:
#
# May you do good and not evil.
# May you find forgiveness for yourself and forgive others.
# May you share freely, never taking more than you give.
#
#***********************************************************************
# This file implements regression tests for SQLite library.
#
# This file tests the PRAGMA defer_foreign_keys and
# SQLITE_DBSTATUS_DEFERRED_FKS
#
# EVIDENCE-OF: R-18981-16292 When the defer_foreign_keys PRAGMA is on,
# enforcement of all foreign key constraints is delayed until the
# outermost transaction is committed.
#
# EVIDENCE-OF: R-28911-57501 The defer_foreign_keys pragma defaults to
# OFF so that foreign key constraints are only deferred if they are
# created as "DEFERRABLE INITIALLY DEFERRED".
set testdir [file dirname $argv0]
source $testdir/tester.tcl
ifcapable {!foreignkey} {
finish_test
return
}
do_execsql_test fkey6-1.0 {
PRAGMA defer_foreign_keys;
} {0}
do_execsql_test fkey6-1.1 {
PRAGMA foreign_keys=ON;
CREATE TABLE t1(x INTEGER PRIMARY KEY);
CREATE TABLE t2(y INTEGER PRIMARY KEY,
z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED);
CREATE INDEX t2z ON t2(z);
CREATE TABLE t3(u INTEGER PRIMARY KEY, v INTEGER REFERENCES t1(x));
CREATE INDEX t3v ON t3(v);
INSERT INTO t1 VALUES(1),(2),(3),(4),(5);
INSERT INTO t2 VALUES(1,1),(2,2);
INSERT INTO t3 VALUES(3,3),(4,4);
} {}
do_test fkey6-1.2 {
catchsql {DELETE FROM t1 WHERE x=2;}
} {1 {FOREIGN KEY constraint failed}}
do_test fkey6-1.3 {
sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
} {0 0 0}
do_test fkey6-1.4 {
execsql {
BEGIN;
DELETE FROM t1 WHERE x=1;
}
} {}
do_test fkey6-1.5.1 {
sqlite3_db_status db DBSTATUS_DEFERRED_FKS 1
} {0 1 0}
do_test fkey6-1.5.2 {
sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
} {0 1 0}
do_test fkey6-1.6 {
execsql {
ROLLBACK;
}
} {}
do_test fkey6-1.7 {
sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
} {0 0 0}
do_test fkey6-1.8 {
execsql {
PRAGMA defer_foreign_keys=ON;
BEGIN;
DELETE FROM t1 WHERE x=3;
}
} {}
do_test fkey6-1.9 {
sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
} {0 1 0}
# EVIDENCE-OF: R-21752-26913 The defer_foreign_keys pragma is
# automatically switched off at each COMMIT or ROLLBACK. Hence, the
# defer_foreign_keys pragma must be separately enabled for each
# transaction.
do_execsql_test fkey6-1.10.1 {
PRAGMA defer_foreign_keys;
ROLLBACK;
PRAGMA defer_foreign_keys;
BEGIN;
PRAGMA defer_foreign_keys=ON;
PRAGMA defer_foreign_keys;
COMMIT;
PRAGMA defer_foreign_keys;
BEGIN;
} {1 0 1 0}
do_test fkey6-1.10.2 {
catchsql {DELETE FROM t1 WHERE x=3}
} {1 {FOREIGN KEY constraint failed}}
db eval {ROLLBACK}
do_test fkey6-1.20 {
execsql {
BEGIN;
DELETE FROM t1 WHERE x=1;
}
sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
} {0 1 0}
do_test fkey6-1.21 {
execsql {
DELETE FROM t2 WHERE y=1;
}
sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
} {0 0 0}
do_test fkey6-1.22 {
execsql {
COMMIT;
}
} {}
do_execsql_test fkey6-2.1 {
CREATE TABLE p1(a PRIMARY KEY);
INSERT INTO p1 VALUES('one'), ('two');
CREATE TABLE c1(x REFERENCES p1);
INSERT INTO c1 VALUES('two'), ('one');
}
do_execsql_test fkey6-2.2 {
BEGIN;
PRAGMA defer_foreign_keys = 1;
DELETE FROM p1;
ROLLBACK;
PRAGMA defer_foreign_keys;
} {0}
do_execsql_test fkey6-2.3 {
BEGIN;
PRAGMA defer_foreign_keys = 1;
DROP TABLE p1;
PRAGMA vdbe_trace = 0;
ROLLBACK;
PRAGMA defer_foreign_keys;
} {0}
do_execsql_test fkey6-2.4 {
BEGIN;
PRAGMA defer_foreign_keys = 1;
DELETE FROM p1;
DROP TABLE c1;
COMMIT;
PRAGMA defer_foreign_keys;
} {0}
do_execsql_test fkey6-2.5 {
DROP TABLE p1;
CREATE TABLE p1(a PRIMARY KEY);
INSERT INTO p1 VALUES('one'), ('two');
CREATE TABLE c1(x REFERENCES p1);
INSERT INTO c1 VALUES('two'), ('one');
}
do_execsql_test fkey6-2.6 {
BEGIN;
PRAGMA defer_foreign_keys = 1;
INSERT INTO c1 VALUES('three');
DROP TABLE c1;
COMMIT;
PRAGMA defer_foreign_keys;
} {0}
finish_test