| OLD | NEW |
|---|
| (Empty) |
| 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 | |
| OLD | NEW |
|---|
Chromium Code Reviews
Issue 2363173002:
[sqlite] Remove obsolete reference version 3.8.7.4. (Closed)
