# 2012 December 17 # # 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 # set testdir [file dirname $argv0] source $testdir/tester.tcl ifcapable {!foreignkey} { finish_test return } 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} do_test fkey6-1.10 { execsql { ROLLBACK; PRAGMA defer_foreign_keys=OFF; BEGIN; } 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; } } {} finish_test