summaryrefslogtreecommitdiffstats
path: root/third_party/sqlite/tool/genfkey.test
diff options
context:
space:
mode:
Diffstat (limited to 'third_party/sqlite/tool/genfkey.test')
-rw-r--r--third_party/sqlite/tool/genfkey.test294
1 files changed, 294 insertions, 0 deletions
diff --git a/third_party/sqlite/tool/genfkey.test b/third_party/sqlite/tool/genfkey.test
new file mode 100644
index 0000000..e1011d6
--- /dev/null
+++ b/third_party/sqlite/tool/genfkey.test
@@ -0,0 +1,294 @@
+
+package require sqlite3
+
+proc do_test {name cmd expected} {
+ puts -nonewline "$name ..."
+ set res [uplevel $cmd]
+ if {$res eq $expected} {
+ puts Ok
+ } else {
+ puts Error
+ puts " Got: $res"
+ puts " Expected: $expected"
+ exit
+ }
+}
+
+proc execsql {sql} {
+ uplevel [list db eval $sql]
+}
+
+proc catchsql {sql} {
+ set rc [catch {uplevel [list db eval $sql]} msg]
+ list $rc $msg
+}
+
+file delete -force test.db test.db.journal
+sqlite3 db test.db
+
+# The following tests - genfkey-1.* - test RESTRICT foreign keys.
+#
+do_test genfkey-1.1 {
+ execsql {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
+ CREATE TABLE t2(e REFERENCES t1, f);
+ CREATE TABLE t3(g, h, i, FOREIGN KEY (h, i) REFERENCES t1(b, c));
+ }
+} {}
+do_test genfkey-1.2 {
+ execsql [exec ./sqlite3 test.db .genfkey]
+} {}
+do_test genfkey-1.3 {
+ catchsql { INSERT INTO t2 VALUES(1, 2) }
+} {1 {constraint failed}}
+do_test genfkey-1.4 {
+ execsql {
+ INSERT INTO t1 VALUES(1, 2, 3);
+ INSERT INTO t2 VALUES(1, 2);
+ }
+} {}
+do_test genfkey-1.5 {
+ execsql { INSERT INTO t2 VALUES(NULL, 3) }
+} {}
+do_test genfkey-1.6 {
+ catchsql { UPDATE t2 SET e = 5 WHERE e IS NULL }
+} {1 {constraint failed}}
+do_test genfkey-1.7 {
+ execsql { UPDATE t2 SET e = 1 WHERE e IS NULL }
+} {}
+do_test genfkey-1.8 {
+ execsql { UPDATE t2 SET e = NULL WHERE f = 3 }
+} {}
+do_test genfkey-1.9 {
+ catchsql { UPDATE t1 SET a = 10 }
+} {1 {constraint failed}}
+do_test genfkey-1.9a {
+ catchsql { UPDATE t1 SET a = NULL }
+} {1 {datatype mismatch}}
+do_test genfkey-1.10 {
+ catchsql { DELETE FROM t1 }
+} {1 {constraint failed}}
+do_test genfkey-1.11 {
+ execsql { UPDATE t2 SET e = NULL }
+} {}
+do_test genfkey-1.12 {
+ execsql {
+ UPDATE t1 SET a = 10 ;
+ DELETE FROM t1;
+ DELETE FROM t2;
+ }
+} {}
+
+do_test genfkey-1.13 {
+ execsql {
+ INSERT INTO t3 VALUES(1, NULL, NULL);
+ INSERT INTO t3 VALUES(1, 2, NULL);
+ INSERT INTO t3 VALUES(1, NULL, 3);
+ }
+} {}
+do_test genfkey-1.14 {
+ catchsql { INSERT INTO t3 VALUES(3, 1, 4) }
+} {1 {constraint failed}}
+do_test genfkey-1.15 {
+ execsql {
+ INSERT INTO t1 VALUES(1, 1, 4);
+ INSERT INTO t3 VALUES(3, 1, 4);
+ }
+} {}
+do_test genfkey-1.16 {
+ catchsql { DELETE FROM t1 }
+} {1 {constraint failed}}
+do_test genfkey-1.17 {
+ catchsql { UPDATE t1 SET b = 10}
+} {1 {constraint failed}}
+do_test genfkey-1.18 {
+ execsql { UPDATE t1 SET a = 10}
+} {}
+do_test genfkey-1.19 {
+ catchsql { UPDATE t3 SET h = 'hello' WHERE i = 3}
+} {1 {constraint failed}}
+
+do_test genfkey-1.X {
+ execsql {
+ DROP TABLE t1;
+ DROP TABLE t2;
+ DROP TABLE t3;
+ }
+} {}
+
+# The following tests - genfkey-2.* - test CASCADE foreign keys.
+#
+do_test genfkey-2.1 {
+ execsql {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(b, c));
+ CREATE TABLE t2(e REFERENCES t1 ON UPDATE CASCADE ON DELETE CASCADE, f);
+ CREATE TABLE t3(g, h, i,
+ FOREIGN KEY (h, i)
+ REFERENCES t1(b, c) ON UPDATE CASCADE ON DELETE CASCADE
+ );
+ }
+} {}
+do_test genfkey-2.2 {
+ execsql [exec ./sqlite3 test.db .genfkey]
+} {}
+do_test genfkey-2.3 {
+ execsql {
+ INSERT INTO t1 VALUES(1, 2, 3);
+ INSERT INTO t1 VALUES(4, 5, 6);
+ INSERT INTO t2 VALUES(1, 'one');
+ INSERT INTO t2 VALUES(4, 'four');
+ }
+} {}
+do_test genfkey-2.4 {
+ execsql {
+ UPDATE t1 SET a = 2 WHERE a = 1;
+ SELECT * FROM t2;
+ }
+} {2 one 4 four}
+do_test genfkey-2.5 {
+ execsql {
+ DELETE FROM t1 WHERE a = 4;
+ SELECT * FROM t2;
+ }
+} {2 one}
+do_test genfkey-2.6 {
+ execsql {
+ INSERT INTO t3 VALUES('hello', 2, 3);
+ UPDATE t1 SET c = 2;
+ SELECT * FROM t3;
+ }
+} {hello 2 2}
+do_test genfkey-2.7 {
+ execsql {
+ DELETE FROM t1;
+ SELECT * FROM t3;
+ }
+} {}
+do_test genfkey-2.X {
+ execsql {
+ DROP TABLE t1;
+ DROP TABLE t2;
+ DROP TABLE t3;
+ }
+} {}
+
+
+# The following tests - genfkey-3.* - test SET NULL foreign keys.
+#
+do_test genfkey-3.1 {
+ execsql {
+ CREATE TABLE t1(a INTEGER PRIMARY KEY, b, c, UNIQUE(c, b));
+ CREATE TABLE t2(e REFERENCES t1 ON UPDATE SET NULL ON DELETE SET NULL, f);
+ CREATE TABLE t3(g, h, i,
+ FOREIGN KEY (h, i)
+ REFERENCES t1(b, c) ON UPDATE SET NULL ON DELETE SET NULL
+ );
+ }
+} {}
+do_test genfkey-3.2 {
+ execsql [exec ./sqlite3 test.db .genfkey]
+} {}
+do_test genfkey-3.3 {
+ execsql {
+ INSERT INTO t1 VALUES(1, 2, 3);
+ INSERT INTO t1 VALUES(4, 5, 6);
+ INSERT INTO t2 VALUES(1, 'one');
+ INSERT INTO t2 VALUES(4, 'four');
+ }
+} {}
+do_test genfkey-3.4 {
+ execsql {
+ UPDATE t1 SET a = 2 WHERE a = 1;
+ SELECT * FROM t2;
+ }
+} {{} one 4 four}
+do_test genfkey-3.5 {
+ execsql {
+ DELETE FROM t1 WHERE a = 4;
+ SELECT * FROM t2;
+ }
+} {{} one {} four}
+do_test genfkey-3.6 {
+ execsql {
+ INSERT INTO t3 VALUES('hello', 2, 3);
+ UPDATE t1 SET c = 2;
+ SELECT * FROM t3;
+ }
+} {hello {} {}}
+do_test genfkey-2.7 {
+ execsql {
+ UPDATE t3 SET h = 2, i = 2;
+ DELETE FROM t1;
+ SELECT * FROM t3;
+ }
+} {hello {} {}}
+do_test genfkey-3.X {
+ execsql {
+ DROP TABLE t1;
+ DROP TABLE t2;
+ DROP TABLE t3;
+ }
+} {}
+
+# The following tests - genfkey-4.* - test that errors in the schema
+# are detected correctly.
+#
+do_test genfkey-4.1 {
+ execsql {
+ CREATE TABLE t1(a REFERENCES nosuchtable, b);
+ CREATE TABLE t2(a REFERENCES t1, b);
+
+ CREATE TABLE t3(a, b, c, PRIMARY KEY(a, b));
+ CREATE TABLE t4(a, b, c, FOREIGN KEY(c, b) REFERENCES t3);
+
+ CREATE TABLE t5(a REFERENCES t4(d), b, c);
+ CREATE TABLE t6(a REFERENCES t4(a), b, c);
+ CREATE TABLE t7(a REFERENCES t3(a), b, c);
+ CREATE TABLE t8(a REFERENCES nosuchtable(a), b, c);
+ }
+} {}
+
+do_test genfkey-4.X {
+ set rc [catch {exec ./sqlite3 test.db .genfkey} msg]
+ list $rc $msg
+} "1 {[string trim {
+Error in table t5: foreign key columns do not exist
+Error in table t8: foreign key columns do not exist
+Error in table t4: implicit mapping to composite primary key
+Error in table t1: implicit mapping to non-existant primary key
+Error in table t2: implicit mapping to non-existant primary key
+Error in table t6: foreign key is not unique
+Error in table t7: foreign key is not unique
+}]}"
+
+# Test that ticket #3800 has been resolved.
+#
+do_test genfkey-5.1 {
+ execsql {
+ DROP TABLE t1; DROP TABLE t2; DROP TABLE t3;
+ DROP TABLE t4; DROP TABLE t5; DROP TABLE t6;
+ DROP TABLE t7; DROP TABLE t8;
+ }
+} {}
+do_test genfkey-5.2 {
+ execsql {
+ CREATE TABLE "t.3" (c1 PRIMARY KEY);
+ CREATE TABLE t13 (c1, foreign key(c1) references "t.3"(c1));
+ }
+} {}
+do_test genfkey-5.3 {
+ set rc [catch {exec ./sqlite3 test.db .genfkey} msg]
+} {0}
+do_test genfkey-5.4 {
+ db eval $msg
+} {}
+do_test genfkey-5.5 {
+ catchsql { INSERT INTO t13 VALUES(1) }
+} {1 {constraint failed}}
+do_test genfkey-5.5 {
+ catchsql {
+ INSERT INTO "t.3" VALUES(1);
+ INSERT INTO t13 VALUES(1);
+ }
+} {0 {}}
+
generated by cgit v1.1 at 2025-01-30 03:40:29 +0000