Changeset: ef988c694472 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/ef988c694472
Modified Files:
sql/include/sql_catalog.h
Branch: dict
Log Message:
merged with default
diffs (truncated from 383 to 300 lines):
diff --git a/sql/ChangeLog b/sql/ChangeLog
--- a/sql/ChangeLog
+++ b/sql/ChangeLog
@@ -1,6 +1,11 @@
# ChangeLog file for sql
# This file is updated with Maddlog
+* Thu Oct 21 2021 Martin van Dinther <martin.van.dint...@monetdbsolutions.com>
+- Extended SQL system catalog with lookup table sys.fkey_actions and
+ view sys.fkeys to provide user friendly querying of existing foreign
+ keys and their ON UPDATE and ON DELETE referential action specifications.
+
* Mon Oct 18 2021 Pedro Ferreira <pedro.ferre...@monetdbsolutions.com>
- The COPY INTO command would use the double quote as the default string
delimiter to produce output, while COPY FROM would use the empty string.
diff --git a/sql/backends/monet5/sql_upgrades.c
b/sql/backends/monet5/sql_upgrades.c
--- a/sql/backends/monet5/sql_upgrades.c
+++ b/sql/backends/monet5/sql_upgrades.c
@@ -4227,6 +4227,32 @@ sql_update_default(Client c, mvc *sql, c
pos += snprintf(buf + pos, bufsize - pos,
"update sys.functions set system = true
where system <> true and name in ('vacuum', 'stop_vacuum') and schema_id = 2000
and type = %d;\n", F_PROC);
+ /* 10_sys_schema_extension.sql */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "CREATE TABLE sys.fkey_actions (\n"
+ " action_id SMALLINT NOT NULL
PRIMARY KEY,\n"
+ " action_name VARCHAR(15) NOT
NULL);\n"
+ "INSERT INTO sys.fkey_actions
(action_id, action_name) VALUES\n"
+ " (0, 'NO ACTION'),\n"
+ " (1, 'CASCADE'),\n"
+ " (2, 'RESTRICT'),\n"
+ " (3, 'SET NULL'),\n"
+ " (4, 'SET DEFAULT');\n"
+ "ALTER TABLE sys.fkey_actions SET READ
ONLY;\n"
+ "GRANT SELECT ON sys.fkey_actions TO
PUBLIC;\n"
+ "CREATE VIEW sys.fkeys AS\n"
+ "SELECT id, table_id, type, name, rkey,
update_action_id, upd.action_name as update_action, delete_action_id,
del.action_name as delete_action FROM (\n"
+ " SELECT id, table_id, type, name,
rkey, cast(((\"action\" >> 8) & 255) as smallint) as update_action_id,
cast((\"action\" & 255) as smallint) AS delete_action_id FROM sys.keys WHERE
type = 2\n"
+ " UNION ALL\n"
+ " SELECT id, table_id, type, name,
rkey, cast(((\"action\" >> 8) & 255) as smallint) as update_action_id,
cast((\"action\" & 255) as smallint) AS delete_action_id FROM tmp.keys WHERE
type = 2\n"
+ ") AS fks\n"
+ "JOIN sys.fkey_actions upd ON
fks.update_action_id = upd.action_id\n"
+ "JOIN sys.fkey_actions del ON
fks.delete_action_id = del.action_id;\n"
+ "GRANT SELECT ON sys.fkeys TO PUBLIC;\n"
+ );
+ pos += snprintf(buf + pos, bufsize - pos,
+ "update sys._tables set system = true
where name in ('fkey_actions', 'fkeys') AND schema_id = 2000;\n");
+
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
err = SQLstatementIntern(c, buf, "update", true, false, NULL);
diff --git a/sql/include/sql_catalog.h b/sql/include/sql_catalog.h
--- a/sql/include/sql_catalog.h
+++ b/sql/include/sql_catalog.h
@@ -574,7 +574,7 @@ typedef struct sql_ukey { /* pkey, ukey
typedef struct sql_fkey { /* fkey */
sql_key k;
- /* no action, restrict (default), cascade, set null, set default */
+ /* 0=no action, 1=cascade, 2=restrict (default setting), 3=set null,
4=set default */
int on_delete;
int on_update;
sqlid rkey;
diff --git a/sql/scripts/10_sys_schema_extension.sql
b/sql/scripts/10_sys_schema_extension.sql
--- a/sql/scripts/10_sys_schema_extension.sql
+++ b/sql/scripts/10_sys_schema_extension.sql
@@ -409,6 +409,35 @@ ALTER TABLE sys.key_types SET READ ONLY;
GRANT SELECT ON sys.key_types TO PUBLIC;
+CREATE TABLE sys.fkey_actions (
+ action_id SMALLINT NOT NULL PRIMARY KEY,
+ action_name VARCHAR(15) NOT NULL);
+
+-- Values taken from sql/include/sql_catalog.h see sql_fkey
+-- and sql/server/sql_parser.y search for: ref_action:
+INSERT INTO sys.fkey_actions (action_id, action_name) VALUES
+ (0, 'NO ACTION'),
+ (1, 'CASCADE'),
+ (2, 'RESTRICT'),
+ (3, 'SET NULL'),
+ (4, 'SET DEFAULT');
+
+ALTER TABLE sys.fkey_actions SET READ ONLY;
+GRANT SELECT ON sys.fkey_actions TO PUBLIC;
+
+
+CREATE VIEW sys.fkeys AS
+SELECT id, table_id, type, name, rkey, update_action_id, upd.action_name as
update_action, delete_action_id, del.action_name as delete_action FROM (
+ SELECT id, table_id, type, name, rkey, cast((("action" >> 8) & 255) as
smallint) as update_action_id, cast(("action" & 255) as smallint) AS
delete_action_id FROM sys.keys WHERE type = 2
+ UNION ALL
+ SELECT id, table_id, type, name, rkey, cast((("action" >> 8) & 255) as
smallint) as update_action_id, cast(("action" & 255) as smallint) AS
delete_action_id FROM tmp.keys WHERE type = 2
+) AS fks
+JOIN sys.fkey_actions upd ON fks.update_action_id = upd.action_id
+JOIN sys.fkey_actions del ON fks.delete_action_id = del.action_id;
+
+GRANT SELECT ON sys.fkeys TO PUBLIC;
+
+
CREATE TABLE sys.index_types (
index_type_id SMALLINT NOT NULL PRIMARY KEY,
index_type_name VARCHAR(25) NOT NULL UNIQUE);
diff --git a/sql/server/rel_dump.c b/sql/server/rel_dump.c
--- a/sql/server/rel_dump.c
+++ b/sql/server/rel_dump.c
@@ -986,6 +986,17 @@ function_error_string(mvc *sql, const ch
schema ? "'.":"", fname, arg_list ?
arg_list : "");
}
+static void /* keep updating the label count */
+try_update_label_count(mvc *sql, const char *label)
+{
+ if (label && label[0] == '%' && isdigit(label[1])) {
+ char *eptr = NULL;
+ unsigned int value = (unsigned int) strtol(label + 1, &eptr,
10);
+ if (eptr && eptr[0] == '\0')
+ sql->label = MAX(sql->label, value);
+ }
+}
+
static sql_exp*
exp_read(mvc *sql, sql_rel *lrel, sql_rel *rrel, list *top_exps, char *r, int
*pos, int grp)
{
@@ -1036,6 +1047,10 @@ exp_read(mvc *sql, sql_rel *lrel, sql_re
} else if (!exp) {
exp = exp_column(sql->sa, tname, cname, NULL,
CARD_ATOM, 1, 0, cname[0] == '%');
}
+ if (exp) {
+ try_update_label_count(sql, tname);
+ try_update_label_count(sql, cname);
+ }
}
break;
/* atom */
@@ -1436,8 +1451,7 @@ exp_read(mvc *sql, sql_rel *lrel, sql_re
} else {
res->digits = 0;
}
- } else if (!f->func->vararg &&
!(exps = check_arguments_and_find_largest_any_type(sql, lrel, exps, f, 0)))
- return NULL;
+ }
} else if (list_length(exps) > 2) {
if (!f->func->vararg && !(exps
= check_arguments_and_find_largest_any_type(sql, lrel, exps, f, 0)))
return NULL;
@@ -1486,6 +1500,8 @@ exp_read(mvc *sql, sql_rel *lrel, sql_re
if (!exp && rrel)
exp = rel_bind_column(sql, rrel, var_cname, 0, 1);
*e = old;
+ if (exp)
+ try_update_label_count(sql, var_cname);
skipWS(r,pos);
}
@@ -1553,6 +1569,7 @@ exp_read(mvc *sql, sql_rel *lrel, sql_re
(*pos)++;
if (r[*pos] != '.') {
cname = tname;
+ tname = NULL;
exp_setname(sql->sa, exp, NULL, cname);
skipWS(r, pos);
} else {
@@ -1564,6 +1581,8 @@ exp_read(mvc *sql, sql_rel *lrel, sql_re
skipWS(r, pos);
exp_setname(sql->sa, exp, tname, cname);
}
+ try_update_label_count(sql, tname);
+ try_update_label_count(sql, cname);
}
return exp;
}
@@ -1892,6 +1911,8 @@ rel_read(mvc *sql, char *r, int *pos, li
set_basecol(next);
append(outputs, next);
m = m->next;
+ try_update_label_count(sql, nrname);
+ try_update_label_count(sql, ncname);
skipWS(r, pos);
}
if (r[*pos] != ']')
diff --git a/sql/test/Dependencies/Tests/dependency_DBobjects.test
b/sql/test/Dependencies/Tests/dependency_DBobjects.test
--- a/sql/test/Dependencies/Tests/dependency_DBobjects.test
+++ b/sql/test/Dependencies/Tests/dependency_DBobjects.test
@@ -233,6 +233,9 @@ DEP_VIEW
query TTT rowsort
SELECT c.name, k.name, 'DEP_KEY' from sys.columns as c, sys.objects as kc,
sys.keys as k where kc."name" = c.name AND kc.id = k.id AND k.table_id =
c.table_id AND k.rkey = -1 order by c.name, k.name
----
+action_id
+fkey_actions_action_id_pkey
+DEP_KEY
dependency_type_id
dependency_types_dependency_type_id_pkey
DEP_KEY
diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -48,7 +48,7 @@ DEP_SCHEMA
query TTT rowsort
SELECT t.name, v.name, 'DEP_VIEW' from sys.tables as t, sys.tables as v,
sys.dependencies as dep where t.id = dep.id AND v.id = dep.depend_id AND
dep.depend_type = 5 AND v.type in (1, 11, 21, 31) order by t.name, v.name
----
-636 values hashing to 90e2ee5bb64e32ae6e9d2ac0ab8dae7b
+645 values hashing to 6a76e9478e88821358bdbee0f171f520
query TTT rowsort
SELECT t.name, i.name, 'DEP_INDEX' from sys.tables as t, sys.idxs as i where
i.table_id = t.id and i.name not in (select name from sys.keys) and t.type in
(0, 10, 20, 30) order by t.name, i.name
@@ -126,11 +126,14 @@ DEP_FUNC
query TTT rowsort
SELECT c.name, v.name, 'DEP_VIEW' from sys.columns as c, sys.tables as v,
sys.dependencies as dep where c.id = dep.id AND v.id = dep.depend_id AND
dep.depend_type = 5 AND v.type in (1, 11, 21, 31) order by c.name, v.name
----
-2217 values hashing to 419c161985866d250f6e16deb56ab344
+2259 values hashing to fbfaada25cafb60a05201d2a699ce3e3
query TTT rowsort
SELECT c.name, k.name, 'DEP_KEY' from sys.columns as c, sys.objects as kc,
sys.keys as k where kc."name" = c.name AND kc.id = k.id AND k.table_id =
c.table_id AND k.rkey = -1 order by c.name, k.name
----
+action_id
+fkey_actions_action_id_pkey
+DEP_KEY
dependency_type_id
dependency_types_dependency_type_id_pkey
DEP_KEY
diff --git a/sql/test/DropCascade/Tests/drop_columns_cascade.test
b/sql/test/DropCascade/Tests/drop_columns_cascade.test
--- a/sql/test/DropCascade/Tests/drop_columns_cascade.test
+++ b/sql/test/DropCascade/Tests/drop_columns_cascade.test
@@ -80,6 +80,7 @@ select name from sys.keys where name not
----
dependency_types_dependency_type_id_pkey
dependency_types_dependency_type_name_unique
+fkey_actions_action_id_pkey
function_languages_language_id_pkey
function_languages_language_name_unique
function_types_function_type_id_pkey
diff --git a/sql/test/DropCascade/Tests/drop_pkey_cascade.test
b/sql/test/DropCascade/Tests/drop_pkey_cascade.test
--- a/sql/test/DropCascade/Tests/drop_pkey_cascade.test
+++ b/sql/test/DropCascade/Tests/drop_pkey_cascade.test
@@ -22,6 +22,7 @@ select name from sys.keys where name not
----
dependency_types_dependency_type_id_pkey
dependency_types_dependency_type_name_unique
+fkey_actions_action_id_pkey
function_languages_language_id_pkey
function_languages_language_name_unique
function_types_function_type_id_pkey
diff --git a/sql/test/SQLancer/Tests/sqlancer19.SQL.py
b/sql/test/SQLancer/Tests/sqlancer19.SQL.py
--- a/sql/test/SQLancer/Tests/sqlancer19.SQL.py
+++ b/sql/test/SQLancer/Tests/sqlancer19.SQL.py
@@ -292,6 +292,10 @@ with SQLTestCase() as cli:
.assertSucceeded().assertDataResultMatch([(True,),(True,),(True,),(True,),(True,),(True,),(True,),(None,),(None,),(None,),(None,)])
cli.execute("SELECT CAST(2 AS REAL) BETWEEN 2 AND (rt5.c0 / rt5.c0)^5 AS X
FROM rt5 ORDER BY x NULLS LAST;") \
.assertSucceeded().assertDataResultMatch([(True,),(True,),(True,),(True,),(True,),(True,),(True,),(None,),(None,),(None,),(None,)])
+ cli.execute("SELECT count(*) FROM t3 GROUP BY 1 + least(2, round(0.68,
t3.c0));") \
+ .assertSucceeded().assertDataResultMatch([(1,), (5,)])
+ cli.execute("SELECT count(*) FROM rt3 GROUP BY 1 + least(2, round(0.68,
rt3.c0));") \
+ .assertSucceeded().assertDataResultMatch([(1,), (5,)])
cli.execute("ROLLBACK;")
cli.execute("CREATE FUNCTION mybooludf(a bool) RETURNS BOOL RETURN a;")
diff --git a/sql/test/Tests/keys.test b/sql/test/Tests/keys.test
--- a/sql/test/Tests/keys.test
+++ b/sql/test/Tests/keys.test
@@ -44,6 +44,10 @@ dependency_types_dependency_type_name_un
0
dependency_types_dependency_type_name_unique
1
+fkey_actions_action_id_pkey
+0
+fkey_actions_action_id_pkey
+0
function_languages_language_id_pkey
0
function_languages_language_id_pkey
@@ -122,6 +126,10 @@ dependency_types_dependency_type_name_un
0
dependency_types_dependency_type_name_unique
1
+fkey_actions_action_id_pkey
+0
+fkey_actions_action_id_pkey
+0
function_languages_language_id_pkey
0
function_languages_language_id_pkey
diff --git a/sql/test/sys-schema/Tests/All b/sql/test/sys-schema/Tests/All
--- a/sql/test/sys-schema/Tests/All
+++ b/sql/test/sys-schema/Tests/All
@@ -9,6 +9,8 @@ check_MaxStrLength_violations
HAVE_GEOM?geom_tables_checks
HAVE_NETCDF?netcdf_tables_checks
+fkeys
+
webExamplesLogicalFunctionsOperators
webExamplesComparisonFunctionsOperators
webExamplesCastFunctionsOperators
diff --git a/sql/test/sys-schema/Tests/fkeys.test
b/sql/test/sys-schema/Tests/fkeys.test
new file mode 100644
--- /dev/null
+++ b/sql/test/sys-schema/Tests/fkeys.test
@@ -0,0 +1,85 @@
+query IT nosort
+select * from sys.fkey_actions order by 1
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
