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 ./genfkey test.db] 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 ./genfkey test.db] 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 ./genfkey test.db] 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 ./genfkey test.db} 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