OLD | NEW |
| (Empty) |
1 # 2013-07-11 | |
2 # | |
3 # The author disclaims copyright to this source code. In place of | |
4 # 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_FKS | |
15 # | |
16 # EVIDENCE-OF: R-18981-16292 When the defer_foreign_keys PRAGMA is on, | |
17 # enforcement of all foreign key constraints is delayed until the | |
18 # outermost transaction is committed. | |
19 # | |
20 # EVIDENCE-OF: R-28911-57501 The defer_foreign_keys pragma defaults to | |
21 # OFF so that foreign key constraints are only deferred if they are | |
22 # created as "DEFERRABLE INITIALLY DEFERRED". | |
23 | |
24 set testdir [file dirname $argv0] | |
25 source $testdir/tester.tcl | |
26 | |
27 ifcapable {!foreignkey} { | |
28 finish_test | |
29 return | |
30 } | |
31 | |
32 do_execsql_test fkey6-1.0 { | |
33 PRAGMA defer_foreign_keys; | |
34 } {0} | |
35 | |
36 do_execsql_test fkey6-1.1 { | |
37 PRAGMA foreign_keys=ON; | |
38 CREATE TABLE t1(x INTEGER PRIMARY KEY); | |
39 CREATE TABLE t2(y INTEGER PRIMARY KEY, | |
40 z INTEGER REFERENCES t1(x) DEFERRABLE INITIALLY DEFERRED); | |
41 CREATE INDEX t2z ON t2(z); | |
42 CREATE TABLE t3(u INTEGER PRIMARY KEY, v INTEGER REFERENCES t1(x)); | |
43 CREATE INDEX t3v ON t3(v); | |
44 INSERT INTO t1 VALUES(1),(2),(3),(4),(5); | |
45 INSERT INTO t2 VALUES(1,1),(2,2); | |
46 INSERT INTO t3 VALUES(3,3),(4,4); | |
47 } {} | |
48 do_test fkey6-1.2 { | |
49 catchsql {DELETE FROM t1 WHERE x=2;} | |
50 } {1 {FOREIGN KEY constraint failed}} | |
51 do_test fkey6-1.3 { | |
52 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 | |
53 } {0 0 0} | |
54 do_test fkey6-1.4 { | |
55 execsql { | |
56 BEGIN; | |
57 DELETE FROM t1 WHERE x=1; | |
58 } | |
59 } {} | |
60 do_test fkey6-1.5.1 { | |
61 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 1 | |
62 } {0 1 0} | |
63 do_test fkey6-1.5.2 { | |
64 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 | |
65 } {0 1 0} | |
66 do_test fkey6-1.6 { | |
67 execsql { | |
68 ROLLBACK; | |
69 } | |
70 } {} | |
71 do_test fkey6-1.7 { | |
72 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 | |
73 } {0 0 0} | |
74 do_test fkey6-1.8 { | |
75 execsql { | |
76 PRAGMA defer_foreign_keys=ON; | |
77 BEGIN; | |
78 DELETE FROM t1 WHERE x=3; | |
79 } | |
80 } {} | |
81 do_test fkey6-1.9 { | |
82 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 | |
83 } {0 1 0} | |
84 | |
85 # EVIDENCE-OF: R-21752-26913 The defer_foreign_keys pragma is | |
86 # automatically switched off at each COMMIT or ROLLBACK. Hence, the | |
87 # defer_foreign_keys pragma must be separately enabled for each | |
88 # transaction. | |
89 do_execsql_test fkey6-1.10.1 { | |
90 PRAGMA defer_foreign_keys; | |
91 ROLLBACK; | |
92 PRAGMA defer_foreign_keys; | |
93 BEGIN; | |
94 PRAGMA defer_foreign_keys=ON; | |
95 PRAGMA defer_foreign_keys; | |
96 COMMIT; | |
97 PRAGMA defer_foreign_keys; | |
98 BEGIN; | |
99 } {1 0 1 0} | |
100 do_test fkey6-1.10.2 { | |
101 catchsql {DELETE FROM t1 WHERE x=3} | |
102 } {1 {FOREIGN KEY constraint failed}} | |
103 db eval {ROLLBACK} | |
104 | |
105 do_test fkey6-1.20 { | |
106 execsql { | |
107 BEGIN; | |
108 DELETE FROM t1 WHERE x=1; | |
109 } | |
110 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 | |
111 } {0 1 0} | |
112 do_test fkey6-1.21 { | |
113 execsql { | |
114 DELETE FROM t2 WHERE y=1; | |
115 } | |
116 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0 | |
117 } {0 0 0} | |
118 do_test fkey6-1.22 { | |
119 execsql { | |
120 COMMIT; | |
121 } | |
122 } {} | |
123 | |
124 do_execsql_test fkey6-2.1 { | |
125 CREATE TABLE p1(a PRIMARY KEY); | |
126 INSERT INTO p1 VALUES('one'), ('two'); | |
127 CREATE TABLE c1(x REFERENCES p1); | |
128 INSERT INTO c1 VALUES('two'), ('one'); | |
129 } | |
130 | |
131 do_execsql_test fkey6-2.2 { | |
132 BEGIN; | |
133 PRAGMA defer_foreign_keys = 1; | |
134 DELETE FROM p1; | |
135 ROLLBACK; | |
136 PRAGMA defer_foreign_keys; | |
137 } {0} | |
138 | |
139 do_execsql_test fkey6-2.3 { | |
140 BEGIN; | |
141 PRAGMA defer_foreign_keys = 1; | |
142 DROP TABLE p1; | |
143 PRAGMA vdbe_trace = 0; | |
144 ROLLBACK; | |
145 PRAGMA defer_foreign_keys; | |
146 } {0} | |
147 | |
148 do_execsql_test fkey6-2.4 { | |
149 BEGIN; | |
150 PRAGMA defer_foreign_keys = 1; | |
151 DELETE FROM p1; | |
152 DROP TABLE c1; | |
153 COMMIT; | |
154 PRAGMA defer_foreign_keys; | |
155 } {0} | |
156 | |
157 do_execsql_test fkey6-2.5 { | |
158 DROP TABLE p1; | |
159 CREATE TABLE p1(a PRIMARY KEY); | |
160 INSERT INTO p1 VALUES('one'), ('two'); | |
161 CREATE TABLE c1(x REFERENCES p1); | |
162 INSERT INTO c1 VALUES('two'), ('one'); | |
163 } | |
164 | |
165 do_execsql_test fkey6-2.6 { | |
166 BEGIN; | |
167 PRAGMA defer_foreign_keys = 1; | |
168 INSERT INTO c1 VALUES('three'); | |
169 DROP TABLE c1; | |
170 COMMIT; | |
171 PRAGMA defer_foreign_keys; | |
172 } {0} | |
173 | |
174 | |
175 finish_test | |
OLD | NEW |