Changeset: d28dc39fb4fc for MonetDB URL: https://dev.monetdb.org/hg/MonetDB/rev/d28dc39fb4fc Added Files: sql/test/sys-schema/Tests/fkeys.test Modified Files: sql/ChangeLog sql/backends/monet5/sql_upgrades.c sql/include/sql_catalog.h sql/scripts/10_sys_schema_extension.sql sql/test/sys-schema/Tests/All Branch: default Log Message:
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. diffs (204 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 <[email protected]> +- 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 <[email protected]> - 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 @@ -573,7 +573,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/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 +---- +0 +NO ACTION +1 +CASCADE +2 +RESTRICT +3 +SET NULL +4 +SET DEFAULT + +statement ok +Create table p (id int unique, name varchar(20)) + +statement ok +Create table c (cid int references p(id), name varchar(20), x int) + +statement ok +Create table d (did int references p(id) ON UPDATE NO ACTION, name varchar(20), x int) + +statement ok +Create table e (eid int references p(id) ON UPDATE CASCADE ON DELETE SET NULL, name varchar(20), x int) + +statement ok +Create table f (fid int references p(id) ON DELETE SET DEFAULT ON UPDATE NO ACTION, name varchar(20), x int) + +statement ok +Create table g (gid int references p(id) ON DELETE CASCADE, name varchar(20), x int) + +query ITITIT nosort +select type, name, update_action_id, update_action, delete_action_id, delete_action from fkeys order by name +---- +2 +c_cid_fkey +2 +RESTRICT +2 +RESTRICT +2 +d_did_fkey +0 +NO ACTION +0 +NO ACTION +2 +e_eid_fkey +1 +CASCADE +3 +SET NULL +2 +f_fid_fkey +0 +NO ACTION +4 +SET DEFAULT +2 +g_gid_fkey +0 +NO ACTION +1 +CASCADE + +statement ok +drop table g + +statement ok +drop table f + +statement ok +drop table e + +statement ok +drop table d + +statement ok +drop table c + +statement ok +drop table p + + _______________________________________________ checkin-list mailing list [email protected] https://www.monetdb.org/mailman/listinfo/checkin-list
