Debian Codegraph @

test/fkey1.test

raw
1 # 2001 September 15
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 implements tests for foreign keys.
14 #
15
16 set testdir [file dirname $argv0]
17 source $testdir/tester.tcl
18 set testprefix fkey1
19
20 ifcapable {!foreignkey} {
21 finish_test
22 return
23 }
24
25 # Create a table and some data to work with.
26 #
27 do_test fkey1-1.0 {
28 execsql {
29 CREATE TABLE t1(
30 a INTEGER PRIMARY KEY,
31 b INTEGER
32 REFERENCES t1 ON DELETE CASCADE
33 REFERENCES t2,
34 c TEXT,
35 FOREIGN KEY (b,c) REFERENCES t2(x,y) ON UPDATE CASCADE
36 );
37 }
38 } {}
39 do_test fkey1-1.1 {
40 execsql {
41 CREATE TABLE t2(
42 x INTEGER PRIMARY KEY,
43 y TEXT
44 );
45 }
46 } {}
47 do_test fkey1-1.2 {
48 execsql {
49 CREATE TABLE t3(
50 a INTEGER REFERENCES t2,
51 b INTEGER REFERENCES t1,
52 FOREIGN KEY (a,b) REFERENCES t2(x,y)
53 );
54 }
55 } {}
56
57 do_test fkey1-2.1 {
58 execsql {
59 CREATE TABLE t4(a integer primary key);
60 CREATE TABLE t5(x references t4);
61 CREATE TABLE t6(x references t4);
62 CREATE TABLE t7(x references t4);
63 CREATE TABLE t8(x references t4);
64 CREATE TABLE t9(x references t4);
65 CREATE TABLE t10(x references t4);
66 DROP TABLE t7;
67 DROP TABLE t9;
68 DROP TABLE t5;
69 DROP TABLE t8;
70 DROP TABLE t6;
71 DROP TABLE t10;
72 }
73 } {}
74
75 do_test fkey1-3.1 {
76 execsql {
77 CREATE TABLE t5(a PRIMARY KEY, b, c);
78 CREATE TABLE t6(
79 d REFERENCES t5,
80 e REFERENCES t5(c)
81 );
82 PRAGMA foreign_key_list(t6);
83 }
84 } [concat \
85 {0 0 t5 e c {NO ACTION} {NO ACTION} NONE} \
86 {1 0 t5 d {} {NO ACTION} {NO ACTION} NONE} \
87 ]
88 do_test fkey1-3.2 {
89 execsql {
90 CREATE TABLE t7(d, e, f,
91 FOREIGN KEY (d, e) REFERENCES t5(a, b)
92 );
93 PRAGMA foreign_key_list(t7);
94 }
95 } [concat \
96 {0 0 t5 d a {NO ACTION} {NO ACTION} NONE} \
97 {0 1 t5 e b {NO ACTION} {NO ACTION} NONE} \
98 ]
99 do_test fkey1-3.3 {
100 execsql {
101 CREATE TABLE t8(d, e, f,
102 FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET NULL
103 );
104 PRAGMA foreign_key_list(t8);
105 }
106 } [concat \
107 {0 0 t5 d {} {SET NULL} CASCADE NONE} \
108 {0 1 t5 e {} {SET NULL} CASCADE NONE} \
109 ]
110 do_test fkey1-3.4 {
111 execsql {
112 CREATE TABLE t9(d, e, f,
113 FOREIGN KEY (d, e) REFERENCES t5 ON DELETE CASCADE ON UPDATE SET DEFAULT
114 );
115 PRAGMA foreign_key_list(t9);
116 }
117 } [concat \
118 {0 0 t5 d {} {SET DEFAULT} CASCADE NONE} \
119 {0 1 t5 e {} {SET DEFAULT} CASCADE NONE} \
120 ]
121 do_test fkey1-3.5 {
122 sqlite3_db_status db DBSTATUS_DEFERRED_FKS 0
123 } {0 0 0}
124
125 # Stress the dequoting logic. The first test is not so bad.
126 do_execsql_test fkey1-4.0 {
127 PRAGMA foreign_keys=ON;
128 CREATE TABLE "xx1"("xx2" TEXT PRIMARY KEY, "xx3" TEXT);
129 INSERT INTO "xx1"("xx2","xx3") VALUES('abc','def');
130 CREATE TABLE "xx4"("xx5" TEXT REFERENCES "xx1" ON DELETE CASCADE);
131 INSERT INTO "xx4"("xx5") VALUES('abc');
132 INSERT INTO "xx1"("xx2","xx3") VALUES('uvw','xyz');
133 SELECT 1, "xx5" FROM "xx4";
134 DELETE FROM "xx1";
135 SELECT 2, "xx5" FROM "xx4";
136 } {1 abc}
137
138 # This case is identical to the previous except the "xx" in each name
139 # is changed to a single escaped double-quote character.
140 do_execsql_test fkey1-4.1 {
141 PRAGMA foreign_keys=ON;
142 CREATE TABLE """1"("""2" TEXT PRIMARY KEY, """3" TEXT);
143 INSERT INTO """1"("""2","""3") VALUES('abc','def');
144 CREATE TABLE """4"("""5" TEXT REFERENCES """1" ON DELETE CASCADE);
145 INSERT INTO """4"("""5") VALUES('abc');
146 INSERT INTO """1"("""2","""3") VALUES('uvw','xyz');
147 SELECT 1, """5" FROM """4";
148 DELETE FROM """1";
149 SELECT 2, """5" FROM """4";
150 } {1 abc}
151 do_execsql_test fkey1-4.2 {
152 PRAGMA table_info="""1";
153 } {0 {"2} TEXT 0 {} 1 1 {"3} TEXT 0 {} 0}
154
155 #-------------------------------------------------------------------------
156 #
157 do_execsql_test fkey1-5.1 {
158 CREATE TABLE t11(
159 x INTEGER PRIMARY KEY,
160 parent REFERENCES t11 ON DELETE CASCADE
161 );
162 INSERT INTO t11 VALUES (1, NULL), (2, 1), (3, 2);
163 } {}
164
165 # The REPLACE part of this statement deletes the row (2, 1). Then the
166 # DELETE CASCADE caused by deleting that row removes the (3, 2) row. Which
167 # would have been the parent of the new row being inserted. Causing an
168 # FK violation.
169 #
170 do_catchsql_test fkey1-5.2 {
171 INSERT OR REPLACE INTO t11 VALUES (2, 3);
172 } {1 {FOREIGN KEY constraint failed}}
173
174 # Make sure sqlite3_trace() output works with triggers used to implement
175 # FK constraints
176 #
177 ifcapable trace {
178 proc sqltrace {txt} {
179 global traceoutput
180 lappend traceoutput $txt
181 }
182 do_test fkey1-5.2.1 {
183 unset -nocomplain traceoutput
184 db trace sqltrace
185 catch {db eval {INSERT OR REPLACE INTO t11 VALUES(2,3);}}
186 set traceoutput
187 } {{INSERT OR REPLACE INTO t11 VALUES(2,3);} {INSERT OR REPLACE INTO t11 VALUES(2,3);} {INSERT OR REPLACE INTO t11 VALUES(2,3);}}
188 }
189
190 # A similar test to the above.
191 do_execsql_test fkey1-5.3 {
192 CREATE TABLE Foo (
193 Id INTEGER PRIMARY KEY,
194 ParentId INTEGER REFERENCES Foo(Id) ON DELETE CASCADE, C1
195 );
196 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (1, null, 'A');
197 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 1, 'A-2-1');
198 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (3, 2, 'A-3-2');
199 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (4, 3, 'A-4-3');
200 }
201 do_catchsql_test fkey1-5.4 {
202 INSERT OR REPLACE INTO Foo(Id, ParentId, C1) VALUES (2, 3, 'A-2-3');
203 } {1 {FOREIGN KEY constraint failed}}
204
205 #-------------------------------------------------------------------------
206 # Check that foreign key processing is not fooled by partial indexes
207 # on the parent table.
208 #
209 do_execsql_test 6.0 {
210 CREATE TABLE p1(x, y);
211 CREATE UNIQUE INDEX p1x ON p1(x) WHERE y<2;
212 INSERT INTO p1 VALUES(1, 1);
213 CREATE TABLE c1(a REFERENCES p1(x));
214 }
215
216 do_catchsql_test 6.1 {
217 INSERT INTO c1 VALUES(1);
218 } {1 {foreign key mismatch - "c1" referencing "p1"}}
219
220 do_execsql_test 6.2 {
221 CREATE UNIQUE INDEX p1x2 ON p1(x);
222 INSERT INTO c1 VALUES(1);
223 } {}
224
225 # 2021-07-03 https://sqlite.org/forum/forumpost/a6b0c05277
226 # 2021-07-07 https://sqlite.org/forum/forumpost/79c9e4797d
227 # Failure to allocate enough registers in the VDBE for a
228 # PRAGMA foreign_key_check when the foreign key has more
229 # columns than the table.
230 #
231 reset_db
232 do_execsql_test 7.1 {
233 PRAGMA foreign_keys=OFF;
234 CREATE TABLE t1(a,b,c,FOREIGN KEY(a,a,a,a,a,a,a,a,a,a,a,a,a,a) REFERENCES t0);
235 INSERT INTO t1 VALUES(1,2,3);
236 PRAGMA foreign_key_check;
237 } {t1 1 t0 0}
238 do_execsql_test 7.2 {
239 DROP TABLE t1;
240 CREATE TABLE t1(a,b,c AS(1),d, FOREIGN KEY(c,d,b,a,b,d,b,c) REFERENCES t0);
241 PRAGMA foreign_key_check;
242 } {}
243
244 # 2021-12-31 forum https://sqlite.org/forum/forumpost/24bd1fef7e9323ef
245 # Memory leak caused by sqlite3NestedParse() running on a corrupt system
246 # table. Discovered by Jingzhou Fu.
247 #
248 reset_db
249 do_execsql_test 8.1 {
250 PRAGMA writable_schema=ON;
251 PRAGMA foreign_keys = ON;
252 CREATE TABLE sqlite_stat1 (tbl INTEGER PRIMARY KEY DESC, idx UNIQUE DEFAULT NULL) WITHOUT ROWID;
253 PRAGMA writable_schema=OFF;
254 CREATE TABLE sqlsim4(stat PRIMARY KEY);;
255 CREATE TABLE t1(sqlsim7 REFERENCES sqlite_stat1 ON DELETE CASCADE);
256 DROP table "sqlsim4";
257 } {}
258 # 2022-01-01 dbsqlfuzz 1c57440219f6f0aedf5e8f72a8ddd75f15aea381
259 # Follow-up case to the above. Assertion is not true if the schema
260 # is corrupt.
261 reset_db
262 database_may_be_corrupt
263 do_execsql_test 8.2 {
264 CREATE TABLE t1(a REFERENCES sqlite_stat1 ON DELETE CASCADE);
265 CREATE TABLE t2(a TEXT PRIMARY KEY);
266 PRAGMA writable_schema=ON;
267 CREATE TABLE sqlite_stat1(tbl INTEGER PRIMARY KEY DESC, idx UNIQUE DEFAULT NULL) WITHOUT ROWID;
268 UPDATE sqlite_schema SET name='sqlite_autoindex_sqlite_stat1_1' WHERE name='sqlite_autoindex_sqlite_stat1_2';
269 PRAGMA writable_schema=RESET;
270 } {}
271 do_catchsql_test 8.3 {
272 REINDEX;
273 } {1 {database disk image is malformed}}
274
275 # 2023-04-13 https://bugs.chromium.org/p/chromium/issues/detail?id=1405220
276 # Avoid double-de-quoting of table names when processing foreign keys.
277 #
278 reset_db
279 do_execsql_test 9.1 {
280 PRAGMA foreign_keys = ON;
281 CREATE TABLE """1"("""2", """3" PRIMARY KEY);
282 CREATE TABLE """4"("""5" REFERENCES """1" ON DELETE RESTRICT);
283 DELETE FROM """1";
284 }
285
286 finish_test