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 355
