wasCSharpSQLite – Blame information for rev 4

Subversion Repositories:
Rev:
Rev Author Line No. Line
1 office 1 # 2009 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  
19 ifcapable {!foreignkey||!trigger} {
20 finish_test
21 return
22 }
23  
24 set testprefix fkey3
25  
26 # Create a table and some data to work with.
27 #
28 do_test fkey3-1.1 {
29 execsql {
30 PRAGMA foreign_keys=ON;
31 CREATE TABLE t1(x INTEGER PRIMARY KEY);
32 INSERT INTO t1 VALUES(100);
33 INSERT INTO t1 VALUES(101);
34 CREATE TABLE t2(y INTEGER REFERENCES t1 (x));
35 INSERT INTO t2 VALUES(100);
36 INSERT INTO t2 VALUES(101);
37 SELECT 1, x FROM t1;
38 SELECT 2, y FROM t2;
39 }
40 } {1 100 1 101 2 100 2 101}
41  
42 do_test fkey3-1.2 {
43 catchsql {
44 DELETE FROM t1 WHERE x=100;
45 }
46 } {1 {foreign key constraint failed}}
47  
48 do_test fkey3-1.3 {
49 catchsql {
50 DROP TABLE t1;
51 }
52 } {1 {foreign key constraint failed}}
53  
54 do_test fkey3-1.4 {
55 execsql {
56 DROP TABLE t2;
57 }
58 } {}
59  
60 do_test fkey3-1.5 {
61 execsql {
62 DROP TABLE t1;
63 }
64 } {}
65  
66 do_test fkey3-2.1 {
67 execsql {
68 PRAGMA foreign_keys=ON;
69 CREATE TABLE t1(x INTEGER PRIMARY KEY);
70 INSERT INTO t1 VALUES(100);
71 INSERT INTO t1 VALUES(101);
72 CREATE TABLE t2(y INTEGER PRIMARY KEY REFERENCES t1 (x) ON UPDATE SET NULL);
73 }
74 execsql {
75 INSERT INTO t2 VALUES(100);
76 INSERT INTO t2 VALUES(101);
77 SELECT 1, x FROM t1;
78 SELECT 2, y FROM t2;
79 }
80 } {1 100 1 101 2 100 2 101}
81  
82  
83 #-------------------------------------------------------------------------
84 # The following tests - fkey-3.* - test some edge cases to do with
85 # inserting rows into tables that have foreign keys where the parent
86 # table is the same as the child table. Especially cases where the
87 # new row being inserted matches itself.
88 #
89 do_execsql_test 3.1.1 {
90 CREATE TABLE t3(a, b, c, d,
91 UNIQUE(a, b),
92 FOREIGN KEY(c, d) REFERENCES t3(a, b)
93 );
94 INSERT INTO t3 VALUES(1, 2, 1, 2);
95 } {}
96 do_catchsql_test 3.1.2 {
97 INSERT INTO t3 VALUES(NULL, 2, 5, 2);
98 } {1 {foreign key constraint failed}}
99 do_catchsql_test 3.1.3 {
100 INSERT INTO t3 VALUES(NULL, 3, 5, 2);
101 } {1 {foreign key constraint failed}}
102  
103 do_execsql_test 3.2.1 {
104 CREATE TABLE t4(a UNIQUE, b REFERENCES t4(a));
105 }
106 do_catchsql_test 3.2.2 {
107 INSERT INTO t4 VALUES(NULL, 1);
108 } {1 {foreign key constraint failed}}
109  
110 do_execsql_test 3.3.1 {
111 CREATE TABLE t5(a INTEGER PRIMARY KEY, b REFERENCES t5(a));
112 INSERT INTO t5 VALUES(NULL, 1);
113 } {}
114 do_catchsql_test 3.3.2 {
115 INSERT INTO t5 VALUES(NULL, 3);
116 } {1 {foreign key constraint failed}}
117  
118 do_execsql_test 3.4.1 {
119 CREATE TABLE t6(a INTEGER PRIMARY KEY, b, c, d,
120 FOREIGN KEY(c, d) REFERENCES t6(a, b)
121 );
122 CREATE UNIQUE INDEX t6i ON t6(b, a);
123 }
124 do_execsql_test 3.4.2 { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}
125 do_execsql_test 3.4.3 { INSERT INTO t6 VALUES(2, 'a', 2, 'a'); } {}
126 do_execsql_test 3.4.4 { INSERT INTO t6 VALUES(NULL, 'a', 1, 'a'); } {}
127 do_execsql_test 3.4.5 { INSERT INTO t6 VALUES(5, 'a', 2, 'a'); } {}
128 do_catchsql_test 3.4.6 {
129 INSERT INTO t6 VALUES(NULL, 'a', 65, 'a');
130 } {1 {foreign key constraint failed}}
131  
132 do_execsql_test 3.4.7 {
133 INSERT INTO t6 VALUES(100, 'one', 100, 'one');
134 DELETE FROM t6 WHERE a = 100;
135 }
136 do_execsql_test 3.4.8 {
137 INSERT INTO t6 VALUES(100, 'one', 100, 'one');
138 UPDATE t6 SET c = 1, d = 'a' WHERE a = 100;
139 DELETE FROM t6 WHERE a = 100;
140 }
141  
142 do_execsql_test 3.5.1 {
143 CREATE TABLE t7(a, b, c, d INTEGER PRIMARY KEY,
144 FOREIGN KEY(c, d) REFERENCES t7(a, b)
145 );
146 CREATE UNIQUE INDEX t7i ON t7(a, b);
147 }
148 do_execsql_test 3.5.2 { INSERT INTO t7 VALUES('x', 1, 'x', NULL) } {}
149 do_execsql_test 3.5.3 { INSERT INTO t7 VALUES('x', 2, 'x', 2) } {}
150 do_catchsql_test 3.5.4 {
151 INSERT INTO t7 VALUES('x', 450, 'x', NULL);
152 } {1 {foreign key constraint failed}}
153 do_catchsql_test 3.5.5 {
154 INSERT INTO t7 VALUES('x', 450, 'x', 451);
155 } {1 {foreign key constraint failed}}
156  
157  
158 do_execsql_test 3.6.1 {
159 CREATE TABLE t8(a, b, c, d, e, FOREIGN KEY(c, d) REFERENCES t8(a, b));
160 CREATE UNIQUE INDEX t8i1 ON t8(a, b);
161 CREATE UNIQUE INDEX t8i2 ON t8(c);
162 INSERT INTO t8 VALUES(1, 1, 1, 1, 1);
163 }
164 do_catchsql_test 3.6.2 {
165 UPDATE t8 SET d = 2;
166 } {1 {foreign key constraint failed}}
167 do_execsql_test 3.6.3 { UPDATE t8 SET d = 1; }
168 do_execsql_test 3.6.4 { UPDATE t8 SET e = 2; }
169  
170 do_catchsql_test 3.6.5 {
171 CREATE TABLE TestTable (
172 id INTEGER PRIMARY KEY,
173 name text,
174 source_id integer not null,
175 parent_id integer,
176  
177 foreign key(source_id, parent_id) references TestTable(source_id, id)
178 );
179 CREATE UNIQUE INDEX testindex on TestTable(source_id, id);
180 PRAGMA foreign_keys=1;
181 INSERT INTO TestTable VALUES (1, 'parent', 1, null);
182 INSERT INTO TestTable VALUES (2, 'child', 1, 1);
183 UPDATE TestTable SET parent_id=1000 where id=2;
184 } {1 {foreign key constraint failed}}
185  
186 finish_test