1 2package require sqlite3 3 4proc 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 17proc execsql {sql} { 18 uplevel [list db eval $sql] 19} 20 21proc catchsql {sql} { 22 set rc [catch {uplevel [list db eval $sql]} msg] 23 list $rc $msg 24} 25 26file delete -force test.db test.db.journal 27sqlite3 db test.db 28 29# The following tests - genfkey-1.* - test RESTRICT foreign keys. 30# 31do_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} {} 38do_test genfkey-1.2 { 39 execsql [exec ./sqlite3 test.db .genfkey] 40} {} 41do_test genfkey-1.3 { 42 catchsql { INSERT INTO t2 VALUES(1, 2) } 43} {1 {constraint failed}} 44do_test genfkey-1.4 { 45 execsql { 46 INSERT INTO t1 VALUES(1, 2, 3); 47 INSERT INTO t2 VALUES(1, 2); 48 } 49} {} 50do_test genfkey-1.5 { 51 execsql { INSERT INTO t2 VALUES(NULL, 3) } 52} {} 53do_test genfkey-1.6 { 54 catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL } 55} {1 {constraint failed}} 56do_test genfkey-1.7 { 57 execsql { UPDATE t2 SET e = 1 WHERE e IS NULL } 58} {} 59do_test genfkey-1.8 { 60 execsql { UPDATE t2 SET e = NULL WHERE f = 3 } 61} {} 62do_test genfkey-1.9 { 63 catchsql { UPDATE t1 SET a = 10 } 64} {1 {constraint failed}} 65do_test genfkey-1.9a { 66 catchsql { UPDATE t1 SET a = NULL } 67} {1 {datatype mismatch}} 68do_test genfkey-1.10 { 69 catchsql { DELETE FROM t1 } 70} {1 {constraint failed}} 71do_test genfkey-1.11 { 72 execsql { UPDATE t2 SET e = NULL } 73} {} 74do_test genfkey-1.12 { 75 execsql { 76 UPDATE t1 SET a = 10 ; 77 DELETE FROM t1; 78 DELETE FROM t2; 79 } 80} {} 81 82do_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} {} 89do_test genfkey-1.14 { 90 catchsql { INSERT INTO t3 VALUES(3, 1, 4) } 91} {1 {constraint failed}} 92do_test genfkey-1.15 { 93 execsql { 94 INSERT INTO t1 VALUES(1, 1, 4); 95 INSERT INTO t3 VALUES(3, 1, 4); 96 } 97} {} 98do_test genfkey-1.16 { 99 catchsql { DELETE FROM t1 } 100} {1 {constraint failed}} 101do_test genfkey-1.17 { 102 catchsql { UPDATE t1 SET b = 10} 103} {1 {constraint failed}} 104do_test genfkey-1.18 { 105 execsql { UPDATE t1 SET a = 10} 106} {} 107do_test genfkey-1.19 { 108 catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3} 109} {1 {constraint failed}} 110 111do_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# 121do_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} {} 131do_test genfkey-2.2 { 132 execsql [exec ./sqlite3 test.db .genfkey] 133} {} 134do_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} {} 142do_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} 148do_test genfkey-2.5 { 149 execsql { 150 DELETE FROM t1 WHERE a = 4; 151 SELECT * FROM t2; 152 } 153} {2 one} 154do_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} 161do_test genfkey-2.7 { 162 execsql { 163 DELETE FROM t1; 164 SELECT * FROM t3; 165 } 166} {} 167do_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# 178do_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} {} 188do_test genfkey-3.2 { 189 execsql [exec ./sqlite3 test.db .genfkey] 190} {} 191do_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} {} 199do_test genfkey-3.4 { 200 execsql { 201 UPDATE t1 SET a = 2 WHERE a = 1; 202 SELECT * FROM t2; 203 } 204} {{} one 4 four} 205do_test genfkey-3.5 { 206 execsql { 207 DELETE FROM t1 WHERE a = 4; 208 SELECT * FROM t2; 209 } 210} {{} one {} four} 211do_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 {} {}} 218do_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 {} {}} 225do_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# 236do_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 251do_test genfkey-4.X { 252 set rc [catch {exec ./sqlite3 test.db .genfkey} msg] 253 list $rc $msg 254} "1 {[string trim { 255Error in table t5: foreign key columns do not exist 256Error in table t8: foreign key columns do not exist 257Error in table t4: implicit mapping to composite primary key 258Error in table t1: implicit mapping to non-existant primary key 259Error in table t2: implicit mapping to non-existant primary key 260Error in table t6: foreign key is not unique 261Error in table t7: foreign key is not unique 262}]}" 263 264# Test that ticket #3800 has been resolved. 265# 266do_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} {} 273do_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} {} 279do_test genfkey-5.3 { 280 set rc [catch {exec ./sqlite3 test.db .genfkey} msg] 281} {0} 282do_test genfkey-5.4 { 283 db eval $msg 284} {} 285do_test genfkey-5.5 { 286 catchsql { INSERT INTO t13 VALUES(1) } 287} {1 {constraint failed}} 288do_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. 296do_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} {} 312do_test genfkey-6.2 { 313 set rc [catch {exec ./sqlite3 test.db .genfkey} msg] 314} {0} 315do_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 328do_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} 337do_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}} 345do_test genfkey-6.6 { 346 catchsql { UPDATE child SET "b.2"=7 } 347} {1 {constraint failed}} 348do_test genfkey-6.7 { 349 execsql { 350 SELECT * FROM parent; 351 SELECT * FROM child; 352 } 353} {1 1} 354