Annotation of embedaddon/sqlite3/tool/genfkey.test, revision 1.1
1.1 ! misho 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:
FreeBSD-CVSweb <freebsd-cvsweb@FreeBSD.org>
![[BACK]](/icons/cvsweb/back.gif){kind=icon}
Return to genfkey.test CVS log ![[TXT]](/icons/cvsweb/text.gif){kind=icon}
![[DIR]](/icons/cvsweb/dir.gif){kind=icon}
Up to [ELWIX - Embedded LightWeight unIX -] / embedaddon / sqlite3 / tool