tool/genfkey.test

   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