1:
2: package require sqlite3
3:
4: proc do_test {name cmd expected} {
5: puts -nonewline "$name ..."
6: set res [uplevel $cmd]
7: if {$res eq $expected} {
8: puts Ok
9: } else {
10: puts Error
11: puts " Got: $res"
12: puts " Expected: $expected"
13: exit
14: }
15: }
16:
17: proc execsql {sql} {
18: uplevel [list db eval $sql]
19: }
20:
21: proc catchsql {sql} {
22: set rc [catch {uplevel [list db eval $sql]} msg]
23: list $rc $msg
24: }
25:
26: file delete -force test.db test.db.journal
27: sqlite3 db test.db
28:
29: # The following tests - genfkey-1.* - test RESTRICT foreign keys.
30: #
31: do_test genfkey-1.1 {
32: execsql {
33: CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
34: CREATE TABLE t2(e REFERENCES t1, f);
35: CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
36: }
37: } {}
38: do_test genfkey-1.2 {
39: execsql [exec ./sqlite3 test.db .genfkey]
40: } {}
41: do_test genfkey-1.3 {
42: catchsql { INSERT INTO t2 VALUES(1, 2) }
43: } {1 {constraint failed}}
44: do_test genfkey-1.4 {
45: execsql {
46: INSERT INTO t1 VALUES(1, 2, 3);
47: INSERT INTO t2 VALUES(1, 2);
48: }
49: } {}
50: do_test genfkey-1.5 {
51: execsql { INSERT INTO t2 VALUES(NULL, 3) }
52: } {}
53: do_test genfkey-1.6 {
54: catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
55: } {1 {constraint failed}}
56: do_test genfkey-1.7 {
57: execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
58: } {}
59: do_test genfkey-1.8 {
60: execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
61: } {}
62: do_test genfkey-1.9 {
63: catchsql { UPDATE t1 SET a = 10 }
64: } {1 {constraint failed}}
65: do_test genfkey-1.9a {
66: catchsql { UPDATE t1 SET a = NULL }
67: } {1 {datatype mismatch}}
68: do_test genfkey-1.10 {
69: catchsql { DELETE FROM t1 }
70: } {1 {constraint failed}}
71: do_test genfkey-1.11 {
72: execsql { UPDATE t2 SET e = NULL }
73: } {}
74: do_test genfkey-1.12 {
75: execsql {
76: UPDATE t1 SET a = 10 ;
77: DELETE FROM t1;
78: DELETE FROM t2;
79: }
80: } {}
81:
82: do_test genfkey-1.13 {
83: execsql {
84: INSERT INTO t3 VALUES(1, NULL, NULL);
85: INSERT INTO t3 VALUES(1, 2, NULL);
86: INSERT INTO t3 VALUES(1, NULL, 3);
87: }
88: } {}
89: do_test genfkey-1.14 {
90: catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
91: } {1 {constraint failed}}
92: do_test genfkey-1.15 {
93: execsql {
94: INSERT INTO t1 VALUES(1, 1, 4);
95: INSERT INTO t3 VALUES(3, 1, 4);
96: }
97: } {}
98: do_test genfkey-1.16 {
99: catchsql { DELETE FROM t1 }
100: } {1 {constraint failed}}
101: do_test genfkey-1.17 {
102: catchsql { UPDATE t1 SET b = 10}
103: } {1 {constraint failed}}
104: do_test genfkey-1.18 {
105: execsql { UPDATE t1 SET a = 10}
106: } {}
107: do_test genfkey-1.19 {
108: catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
109: } {1 {constraint failed}}
110:
111: do_test genfkey-1.X {
112: execsql {
113: DROP TABLE t1;
114: DROP TABLE t2;
115: DROP TABLE t3;
116: }
117: } {}
118:
119: # The following tests - genfkey-2.* - test CASCADE foreign keys.
120: #
121: do_test genfkey-2.1 {
122: execsql {
123: CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
124: CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
125: CREATE TABLE t3(g, h, i,
126: FOREIGN KEY (h, i)
127: REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
128: );
129: }
130: } {}
131: do_test genfkey-2.2 {
132: execsql [exec ./sqlite3 test.db .genfkey]
133: } {}
134: do_test genfkey-2.3 {
135: execsql {
136: INSERT INTO t1 VALUES(1, 2, 3);
137: INSERT INTO t1 VALUES(4, 5, 6);
138: INSERT INTO t2 VALUES(1, 'one');
139: INSERT INTO t2 VALUES(4, 'four');
140: }
141: } {}
142: do_test genfkey-2.4 {
143: execsql {
144: UPDATE t1 SET a = 2 WHERE a = 1;
145: SELECT * FROM t2;
146: }
147: } {2 one 4 four}
148: do_test genfkey-2.5 {
149: execsql {
150: DELETE FROM t1 WHERE a = 4;
151: SELECT * FROM t2;
152: }
153: } {2 one}
154: do_test genfkey-2.6 {
155: execsql {
156: INSERT INTO t3 VALUES('hello', 2, 3);
157: UPDATE t1 SET c = 2;
158: SELECT * FROM t3;
159: }
160: } {hello 2 2}
161: do_test genfkey-2.7 {
162: execsql {
163: DELETE FROM t1;
164: SELECT * FROM t3;
165: }
166: } {}
167: do_test genfkey-2.X {
168: execsql {
169: DROP TABLE t1;
170: DROP TABLE t2;
171: DROP TABLE t3;
172: }
173: } {}
174:
175:
176: # The following tests - genfkey-3.* - test SET NULL foreign keys.
177: #
178: do_test genfkey-3.1 {
179: execsql {
180: CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
181: CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
182: CREATE TABLE t3(g, h, i,
183: FOREIGN KEY (h, i)
184: REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
185: );
186: }
187: } {}
188: do_test genfkey-3.2 {
189: execsql [exec ./sqlite3 test.db .genfkey]
190: } {}
191: do_test genfkey-3.3 {
192: execsql {
193: INSERT INTO t1 VALUES(1, 2, 3);
194: INSERT INTO t1 VALUES(4, 5, 6);
195: INSERT INTO t2 VALUES(1, 'one');
196: INSERT INTO t2 VALUES(4, 'four');
197: }
198: } {}
199: do_test genfkey-3.4 {
200: execsql {
201: UPDATE t1 SET a = 2 WHERE a = 1;
202: SELECT * FROM t2;
203: }
204: } {{} one 4 four}
205: do_test genfkey-3.5 {
206: execsql {
207: DELETE FROM t1 WHERE a = 4;
208: SELECT * FROM t2;
209: }
210: } {{} one {} four}
211: do_test genfkey-3.6 {
212: execsql {
213: INSERT INTO t3 VALUES('hello', 2, 3);
214: UPDATE t1 SET c = 2;
215: SELECT * FROM t3;
216: }
217: } {hello {} {}}
218: do_test genfkey-2.7 {
219: execsql {
220: UPDATE t3 SET h = 2, i = 2;
221: DELETE FROM t1;
222: SELECT * FROM t3;
223: }
224: } {hello {} {}}
225: do_test genfkey-3.X {
226: execsql {
227: DROP TABLE t1;
228: DROP TABLE t2;
229: DROP TABLE t3;
230: }
231: } {}
232:
233: # The following tests - genfkey-4.* - test that errors in the schema
234: # are detected correctly.
235: #
236: do_test genfkey-4.1 {
237: execsql {
238: CREATE TABLE t1(a REFERENCES nosuchtable, b);
239: CREATE TABLE t2(a REFERENCES t1, b);
240:
241: CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b));
242: CREATE TABLE t4(a, b, c, FOREIGN KEY(c, b) REFERENCES t3);
243:
244: CREATE TABLE t5(a REFERENCES t4(d), b, c);
245: CREATE TABLE t6(a REFERENCES t4(a), b, c);
246: CREATE TABLE t7(a REFERENCES t3(a), b, c);
247: CREATE TABLE t8(a REFERENCES nosuchtable(a), b, c);
248: }
249: } {}
250:
251: do_test genfkey-4.X {
252: set rc [catch {exec ./sqlite3 test.db .genfkey} msg]
253: list $rc $msg
254: } "1 {[string trim {
255: Error in table t5: foreign key columns do not exist
256: Error in table t8: foreign key columns do not exist
257: Error in table t4: implicit mapping to composite primary key
258: Error in table t1: implicit mapping to non-existant primary key
259: Error in table t2: implicit mapping to non-existant primary key
260: Error in table t6: foreign key is not unique
261: Error in table t7: foreign key is not unique
262: }]}"
263:
264: # Test that ticket #3800 has been resolved.
265: #
266: do_test genfkey-5.1 {
267: execsql {
268: DROP TABLE t1; DROP TABLE t2; DROP TABLE t3;
269: DROP TABLE t4; DROP TABLE t5; DROP TABLE t6;
270: DROP TABLE t7; DROP TABLE t8;
271: }
272: } {}
273: do_test genfkey-5.2 {
274: execsql {
275: CREATE TABLE "t.3" (c1 PRIMARY KEY);
276: CREATE TABLE t13 (c1, foreign key(c1) references "t.3"(c1));
277: }
278: } {}
279: do_test genfkey-5.3 {
280: set rc [catch {exec ./sqlite3 test.db .genfkey} msg]
281: } {0}
282: do_test genfkey-5.4 {
283: db eval $msg
284: } {}
285: do_test genfkey-5.5 {
286: catchsql { INSERT INTO t13 VALUES(1) }
287: } {1 {constraint failed}}
288: do_test genfkey-5.5 {
289: catchsql {
290: INSERT INTO "t.3" VALUES(1);
291: INSERT INTO t13 VALUES(1);
292: }
293: } {0 {}}
294:
295: # Test also column names that require quoting.
296: do_test genfkey-6.1 {
297: execsql {
298: DROP TABLE "t.3";
299: DROP TABLE t13;
300: CREATE TABLE p(
301: "a.1 first", "b.2 second",
302: UNIQUE("a.1 first", "b.2 second")
303: );
304: CREATE TABLE c(
305: "c.1 I", "d.2 II",
306: FOREIGN KEY("c.1 I", "d.2 II")
307: REFERENCES p("a.1 first", "b.2 second")
308: ON UPDATE CASCADE ON DELETE CASCADE
309: );
310: }
311: } {}
312: do_test genfkey-6.2 {
313: set rc [catch {exec ./sqlite3 test.db .genfkey} msg]
314: } {0}
315: do_test genfkey-6.3 {
316: execsql $msg
317: execsql {
318: INSERT INTO p VALUES('A', 'B');
319: INSERT INTO p VALUES('C', 'D');
320: INSERT INTO c VALUES('A', 'B');
321: INSERT INTO c VALUES('C', 'D');
322: UPDATE p SET "a.1 first" = 'X' WHERE rowid = 1;
323: DELETE FROM p WHERE rowid = 2;
324: }
325: execsql { SELECT * FROM c }
326: } {X B}
327:
328: do_test genfkey-6.4 {
329: execsql {
330: DROP TABLE p;
331: DROP TABLE c;
332: CREATE TABLE parent("a.1", PRIMARY KEY("a.1"));
333: CREATE TABLE child("b.2", FOREIGN KEY("b.2") REFERENCES parent("a.1"));
334: }
335: set rc [catch {exec ./sqlite3 test.db .genfkey} msg]
336: } {0}
337: do_test genfkey-6.5 {
338: execsql $msg
339: execsql {
340: INSERT INTO parent VALUES(1);
341: INSERT INTO child VALUES(1);
342: }
343: catchsql { UPDATE parent SET "a.1"=0 }
344: } {1 {constraint failed}}
345: do_test genfkey-6.6 {
346: catchsql { UPDATE child SET "b.2"=7 }
347: } {1 {constraint failed}}
348: do_test genfkey-6.7 {
349: execsql {
350: SELECT * FROM parent;
351: SELECT * FROM child;
352: }
353: } {1 1}
354:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to genfkey.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool