AryaWu/sqlite

1# 2012 December 172#3# The author disclaims copyright to this source code.  In place of4# a legal notice, here is a blessing:5#6#    May you do good and not evil.7#    May you find forgiveness for yourself and forgive others.8#    May you share freely, never taking more than you give.9#10#***********************************************************************11# This file implements regression tests for SQLite library.12#13# This file tests the PRAGMA defer_foreign_keys and 14# SQLITE_DBSTATUS_DEFERRED_FKS15#16# EVIDENCE-OF: R-18981-16292 When the defer_foreign_keys PRAGMA is on,17# enforcement of all foreign key constraints is delayed until the18# outermost transaction is committed.19#20# EVIDENCE-OF: R-28911-57501 The defer_foreign_keys pragma defaults to21# OFF so that foreign key constraints are only deferred if they are22# created as "DEFERRABLE INITIALLY DEFERRED".23 24set testdir [file dirname $argv0]25source $testdir/tester.tcl26set testprefix fkey627 28ifcapable {!foreignkey} {29  finish_test30  return31}32 33do_execsql_test fkey6-1.0 {34  PRAGMA defer_foreign_keys;35} {0}36 37do_execsql_test fkey6-1.1 {38  PRAGMA foreign_keys=ON;39  CREATE TABLE t1(x INTEGER PRIMARY KEY);40  CREATE TABLE t2(y INTEGER PRIMARY KEY,41          z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED);42  CREATE INDEX t2z ON t2(z);43  CREATE TABLE t3(u INTEGER PRIMARY KEY, v INTEGER REFERENCES t1(x));44  CREATE INDEX t3v ON t3(v);45  INSERT INTO t1 VALUES(1),(2),(3),(4),(5);46  INSERT INTO t2 VALUES(1,1),(2,2);47  INSERT INTO t3 VALUES(3,3),(4,4);48} {}49do_test fkey6-1.2 {50  catchsql {DELETE FROM t1 WHERE x=2;}51} {1 {FOREIGN KEY constraint failed}}52do_test fkey6-1.3 {53  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 054} {0 0 0}55do_test fkey6-1.4 {56  execsql {57    BEGIN;58    DELETE FROM t1 WHERE x=1;59  }60} {}61do_test fkey6-1.5.1 {62  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 163} {0 1 0}64do_test fkey6-1.5.2 {65  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 066} {0 1 0}67do_test fkey6-1.6 {68  execsql {69    ROLLBACK;70  }71} {}72do_test fkey6-1.7 {73  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 074} {0 0 0}75do_test fkey6-1.8 {76  execsql {77    PRAGMA defer_foreign_keys=ON;78    BEGIN;79    DELETE FROM t1 WHERE x=3;80  }81} {}82do_test fkey6-1.9 {83  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 084} {0 1 0}85 86# EVIDENCE-OF: R-21752-26913 The defer_foreign_keys pragma is87# automatically switched off at each COMMIT or ROLLBACK. Hence, the88# defer_foreign_keys pragma must be separately enabled for each89# transaction.90do_execsql_test fkey6-1.10.1 {91  PRAGMA defer_foreign_keys;92  ROLLBACK;93  PRAGMA defer_foreign_keys;94  BEGIN;95  PRAGMA defer_foreign_keys=ON;96  PRAGMA defer_foreign_keys;97  COMMIT;98  PRAGMA defer_foreign_keys;99  BEGIN;100} {1 0 1 0}101do_test fkey6-1.10.2 {102  catchsql {DELETE FROM t1 WHERE x=3}103} {1 {FOREIGN KEY constraint failed}}104db eval {ROLLBACK}105 106do_test fkey6-1.20 {107  execsql {108    BEGIN;109    DELETE FROM t1 WHERE x=1;110  }111  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0112} {0 1 0}113do_test fkey6-1.21 {114  execsql {115    DELETE FROM t2 WHERE y=1;116  }117  sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0118} {0 0 0}119do_test fkey6-1.22 {120  execsql {121    COMMIT;122  }123} {}124 125do_execsql_test fkey6-2.1 {126  CREATE TABLE p1(a PRIMARY KEY);127  INSERT INTO p1 VALUES('one'), ('two');128  CREATE TABLE c1(x REFERENCES p1);129  INSERT INTO c1 VALUES('two'), ('one');130}131 132do_execsql_test fkey6-2.2 {133  BEGIN;134    PRAGMA defer_foreign_keys = 1;135    DELETE FROM p1;136  ROLLBACK;137  PRAGMA defer_foreign_keys;138} {0}139 140do_execsql_test fkey6-2.3 {141  BEGIN;142    PRAGMA defer_foreign_keys = 1;143    DROP TABLE p1;144    PRAGMA vdbe_trace = 0;145  ROLLBACK;146  PRAGMA defer_foreign_keys;147} {0}148 149do_execsql_test fkey6-2.4 {150  BEGIN;151    PRAGMA defer_foreign_keys = 1;152    DELETE FROM p1;153    DROP TABLE c1;154  COMMIT;155  PRAGMA defer_foreign_keys;156} {0}157 158do_execsql_test fkey6-2.5 {159  DROP TABLE p1;160  CREATE TABLE p1(a PRIMARY KEY);161  INSERT INTO p1 VALUES('one'), ('two');162  CREATE TABLE c1(x REFERENCES p1);163  INSERT INTO c1 VALUES('two'), ('one');164}165 166do_execsql_test fkey6-2.6 {167  BEGIN;168    PRAGMA defer_foreign_keys = 1;169    INSERT INTO c1 VALUES('three');170    DROP TABLE c1;171  COMMIT;172  PRAGMA defer_foreign_keys;173} {0}174 175#--------------------------------------------------------------------------176# Test that defer_foreign_keys disables RESTRICT.177#178do_execsql_test 3.1 {179  CREATE TABLE p2(a PRIMARY KEY, b);180  CREATE TABLE c2(x, y REFERENCES p2 ON DELETE RESTRICT ON UPDATE RESTRICT);181  INSERT INTO p2 VALUES(1, 'one');182  INSERT INTO p2 VALUES(2, 'two');183  INSERT INTO c2 VALUES('i', 1);184}185 186do_catchsql_test 3.2.1 {187  BEGIN;188    UPDATE p2 SET a=a-1;189} {1 {FOREIGN KEY constraint failed}}190do_execsql_test 3.2.2 { COMMIT }191 192do_execsql_test 3.2.3 {193  BEGIN;194    PRAGMA defer_foreign_keys = 1;195    UPDATE p2 SET a=a-1;196  COMMIT;197}198 199do_execsql_test 3.2.4 {200  BEGIN;201    PRAGMA defer_foreign_keys = 1;202    UPDATE p2 SET a=a-1;203}204do_catchsql_test 3.2.5 {205  COMMIT;206} {1 {FOREIGN KEY constraint failed}}207do_execsql_test 3.2.6 { ROLLBACK }208 209do_execsql_test 3.3.1 {210  CREATE TRIGGER p2t AFTER DELETE ON p2 BEGIN211    INSERT INTO p2 VALUES(old.a, 'deleted!');212  END;213}214do_catchsql_test 3.3.2 {215  BEGIN;216    DELETE FROM p2 WHERE a=1;217} {1 {FOREIGN KEY constraint failed}}218do_execsql_test 3.3.3 { COMMIT }219 220do_execsql_test 3.3.4 {221  BEGIN;222    PRAGMA defer_foreign_keys = 1;223    DELETE FROM p2 WHERE a=1;224  COMMIT;225  SELECT * FROM p2;226} {0 one 1 deleted!}227 228#-------------------------------------------------------------------------229# Verify that, even with "PRAGMA defer_foreign_keys", a transaction cannot230# be committed if there are outstanding foreign key violations.231#232reset_db233do_execsql_test 4.0 {234  CREATE TABLE p1(a INTEGER PRIMARY KEY, b UNIQUE);235  CREATE TABLE c1(x REFERENCES p1(b));236 237  INSERT INTO p1 VALUES(1, 'one'), (2, 'two'), (3, 'three');238  INSERT INTO c1 VALUES('two');239 240  PRAGMA foreign_keys = 1;241  PRAGMA defer_foreign_keys = 1;242}243 244do_execsql_test 4.1 {245  BEGIN;246    DELETE FROM p1 WHERE a=2;247}248 249do_catchsql_test 4.2 {250  COMMIT;251} {1 {FOREIGN KEY constraint failed}}252 253#-------------------------------------------------------------------------254#255reset_db256do_execsql_test 5.0 {257  PRAGMA foreign_keys = 1;258  CREATE TABLE p1(a INTEGER PRIMARY KEY, b);259  CREATE TABLE c1(x REFERENCES p1 DEFERRABLE INITIALLY DEFERRED);260}261 262do_execsql_test 5.1 {263  BEGIN;264    INSERT INTO c1 VALUES(123);265    PRAGMA defer_foreign_keys = 1;266    INSERT INTO p1 VALUES(123, 'one two three');267  COMMIT;268}269 270#-------------------------------------------------------------------------271#272reset_db273 274ifcapable fts5 {275if {[permutation]!="inmemory_journal"} {276  do_execsql_test 6.1 {277    PRAGMA auto_vacuum = 0;278    PRAGMA writable_schema = 1;279    INSERT INTO sqlite_schema 280      VALUES('table', 't1', 't1', 2, 'CREATE TABLE t1(x INTEGER PRIMARY KEY)');281  }282  db close283  sqlite3 db test.db284  do_execsql_test 6.1 {285    PRAGMA foreign_keys = 1;286    PRAGMA writable_schema = 1;287  }288  do_execsql_test 6.2 {289    CREATE TABLE t2(290        y INTEGER PRIMARY KEY,291        z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED292    );293  }294  do_execsql_test 6.3 {295    BEGIN;296      INSERT INTO t2 VALUES(1,0),(2,1);297      CREATE VIRTUAL TABLE t3 USING fts5(a, b, content='', tokendata=1);298      INSERT INTO t3 VALUES(3,3);299      PRAGMA defer_foreign_keys=ON;300      DELETE FROM t2;301    COMMIT;302  }303}304}305 306finish_test307