Changeset: b2bc06faba77 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b2bc06faba77
Modified Files:
clients/mapiclient/mclient.c
sql/backends/monet5/sql_upgrades.c
sql/scripts/51_sys_schema_extension.sql
sql/scripts/97_comments.sql
sql/test/BugTracker-2017/Tests/crash_correlated_subqueries_in_select.Bug-6254.stable.out
sql/test/Dependencies/Tests/Dependencies.stable.out
sql/test/Dependencies/Tests/Dependencies.stable.out.int128
sql/test/Dependencies/Tests/dependency_loop.stable.out
sql/test/Dependencies/Tests/dependency_loop.stable.out.int128
sql/test/Tests/systemfunctions.stable.out
sql/test/Tests/systemfunctions.stable.out.int128
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: default
Log Message:
Optimize SQL COMMENT views, replace table producing function into a view.
Agreed with Joeri.
diffs (truncated from 2721 to 300 lines):
diff --git a/clients/mapiclient/mclient.c b/clients/mapiclient/mclient.c
--- a/clients/mapiclient/mclient.c
+++ b/clients/mapiclient/mclient.c
@@ -2154,7 +2154,7 @@ showCommands(void)
mnstr_printf(toConsole, "\\q - terminate session\n");
}
-/* These values must match those used in sys.describe_all_objects() */
+/* These values must match those used in view sys.describe_all_objects */
#define MD_TABLE 1
#define MD_VIEW 2
#define MD_SEQ 4
@@ -2500,7 +2500,7 @@ doFile(Mapi mid, stream *fp, int useinse
* | "data.my*" | no
| fullname LIKE 'data.my%' |
* | "*a.my*" | no
| fullname LIKE '%a.my%' |
*/
- q += snprintf(q, endq - q,
"SELECT type, fullname, remark FROM sys.describe_all_objects()\n");
+ q += snprintf(q, endq - q,
"SELECT type, fullname, remark FROM sys.describe_all_objects\n");
q += snprintf(q, endq - q,
"WHERE (ntype & %u) > 0\n", x);
if (!wantsSystem) {
q += snprintf(q, endq -
q, "AND NOT system\n");
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
@@ -1073,166 +1073,110 @@ sql_create_comments_table(Client c)
char *err, *q;
q = ""
- "ALTER TABLE sys.keywords SET READ WRITE;\n"
- "INSERT INTO sys.keywords VALUES ('COMMENT');\n"
- "-- ALTER TABLE sys.keywords SET READ ONLY;\n"
- "\n"
- "CREATE TABLE sys.comments (\n"
- " id INTEGER NOT NULL PRIMARY KEY,\n"
- " remark VARCHAR(65000) NOT NULL\n"
- ");\n"
- "GRANT SELECT ON sys.comments TO PUBLIC;\n"
- "\n"
- "CREATE PROCEDURE sys.comment_on(obj_id INTEGER, obj_remark
VARCHAR(65000))\n"
- "BEGIN\n"
- " IF obj_remark IS NULL OR obj_remark = '' THEN\n"
- " DELETE FROM sys.comments WHERE id = obj_id;\n"
- " ELSEIF EXISTS (SELECT id FROM sys.comments WHERE id =
obj_id) THEN\n"
- " UPDATE sys.comments SET remark = obj_remark
WHERE id = obj_id;\n"
- " ELSE\n"
- " INSERT INTO sys.comments VALUES (obj_id,
obj_remark);\n"
- " END IF;\n"
- "END;\n"
- "-- do not grant to public\n"
- "\n"
- "CREATE FUNCTION sys.function_type_keyword(ftype INT)\n"
- "RETURNS VARCHAR(20)\n"
- "BEGIN\n"
- " RETURN CASE ftype\n"
- " WHEN 1 THEN 'FUNCTION'\n"
- " WHEN 2 THEN 'PROCEDURE'\n"
- " WHEN 3 THEN 'AGGREGATE'\n"
- " WHEN 4 THEN 'FILTER FUNCTION'\n"
- " WHEN 5 THEN 'FUNCTION' -- table returning
function\n"
- " WHEN 7 THEN 'LOADER'\n"
- " ELSE 'ROUTINE'\n"
- " END;\n"
- "END;\n"
- "GRANT EXECUTE ON FUNCTION sys.function_type_keyword(INT) TO
PUBLIC;\n"
- "\n"
- "CREATE FUNCTION sys.describe_all_objects()\n"
- "RETURNS TABLE (\n"
- " sname VARCHAR(1024),\n"
- " name VARCHAR(1024),\n"
- " fullname VARCHAR(1024),\n"
- " ntype INTEGER, -- must match the
MD_TABLE/VIEW/SEQ/FUNC/SCHEMA constants in mclient.c\n"
- " type VARCHAR(30),\n"
- " system BOOLEAN,\n"
- " remark VARCHAR(65000)\n"
- ")\n"
- "BEGIN\n"
- " RETURN TABLE (\n"
- " WITH\n"
- " table_data AS (\n"
- " SELECT schema_id AS sid,\n"
- " id,\n"
- " name,\n"
- " system,\n"
- " (CASE type\n"
- " WHEN 1 THEN 2 -- ntype for
views\n"
- " ELSE 1 -- ntype for tables\n"
- " END) AS ntype,\n"
- " table_type_name AS type\n"
- " FROM sys._tables LEFT OUTER JOIN
sys.table_types ON type = table_type_id\n"
- " WHERE type IN (0, 1, 3, 4, 5, 6)\n"
- " ),\n"
- " sequence_data AS (\n"
- " SELECT schema_id AS sid,\n"
- " id,\n"
- " name,\n"
- " false AS system,\n"
- " 4 AS ntype,\n"
- " 'SEQUENCE' AS type\n"
- " FROM sys.sequences\n"
- " ),\n"
- " function_data AS (\n"
- " SELECT schema_id AS sid,\n"
- " id,\n"
- " name,\n"
- " EXISTS (SELECT function_id FROM
sys.systemfunctions WHERE function_id = id) AS system,\n"
- " 8 AS ntype,\n"
- " sys.function_type_keyword(type) AS
type\n"
- " FROM sys.functions\n"
- " ),\n"
- " schema_data AS (\n"
- " SELECT 0 AS sid,\n"
- " id,\n"
- " name,\n"
- " system,\n"
- " 16 AS ntype,\n"
- " 'SCHEMA' AS type\n"
- " FROM sys.schemas\n"
- " ),\n"
- " all_data AS (\n"
- " SELECT * FROM table_data\n"
- " UNION\n"
- " SELECT * FROM sequence_data\n"
- " UNION\n"
- " SELECT * FROM function_data\n"
- " UNION\n"
- " SELECT * FROM schema_data\n"
- " )\n"
- " --\n"
- " SELECT DISTINCT\n"
- " s.name AS sname,\n"
- " a.name AS name,\n"
- " COALESCE(s.name || '.', '') || a.name AS
fullname,\n"
- " a.ntype AS ntype,\n"
- " (CASE WHEN a.system THEN 'SYSTEM ' ELSE ''
END) || a.type AS type,\n"
- " a.system AS system,\n"
- " c.remark AS remark\n"
- " FROM all_data a\n"
- " LEFT OUTER JOIN sys.schemas s ON a.sid = s.id\n"
- " LEFT OUTER JOIN sys.comments c ON a.id = c.id\n"
- " ORDER BY system, name, fullname, ntype\n"
- " );\n"
- "END;\n"
- "GRANT EXECUTE ON FUNCTION sys.describe_all_objects() TO
PUBLIC;\n"
- "\n"
- "CREATE VIEW commented_function_signatures AS\n"
- "WITH\n"
- "params AS (\n"
- " SELECT * FROM sys.args WHERE inout = 1\n"
- "),\n"
- "commented_function_params AS (\n"
- " SELECT f.id AS fid,\n"
- " f.name AS fname,\n"
- " s.name AS schema,\n"
- " f.type AS ftype,\n"
- " c.remark AS remark,\n"
- " p.number AS n,\n"
- " p.name AS aname,\n"
- " p.type AS type,\n"
- " p.type_digits AS type_digits,\n"
- " p.type_scale AS type_scale,\n"
- " RANK() OVER (PARTITION BY f.id ORDER BY number
ASC) AS asc_rank,\n"
- " RANK() OVER (PARTITION BY f.id ORDER BY number
DESC) AS desc_rank\n"
- " FROM sys.functions f\n"
- " JOIN sys.schemas s ON f.schema_id = s.id\n"
- " JOIN sys.comments c ON f.id = c.id\n"
- " LEFT OUTER JOIN params p ON f.id = p.func_id\n"
- ")\n"
- "SELECT fid,\n"
- " schema,\n"
- " fname,\n"
- " sys.function_type_keyword(ftype) AS category,\n"
- " EXISTS (SELECT function_id FROM sys.systemfunctions
WHERE fid = function_id) AS system,\n"
- " CASE WHEN asc_rank = 1 THEN fname ELSE NULL END AS
name,\n"
- " CASE WHEN desc_rank = 1 THEN remark ELSE NULL END AS
remark,\n"
- " type, type_digits, type_scale,\n"
- " ROW_NUMBER() OVER (ORDER BY fid, n) AS line\n"
- "FROM commented_function_params\n"
- "ORDER BY line;\n"
- "GRANT SELECT ON sys.commented_function_signatures TO
PUBLIC;\n";
- err = SQLstatementIntern(c, &q, "update", 1, 0, NULL);
- if (err)
- return err;
+ "CREATE TABLE sys.comments (\n"
+ " id INTEGER NOT NULL PRIMARY KEY,\n"
+ " remark VARCHAR(65000) NOT NULL\n"
+ ");\n"
+ "GRANT SELECT ON sys.comments TO PUBLIC;\n"
+ "\n"
+ "CREATE PROCEDURE sys.comment_on(obj_id INTEGER, obj_remark
VARCHAR(65000))\n"
+ "BEGIN\n"
+ " IF obj_id IS NOT NULL AND obj_id > 0 THEN\n"
+ " IF obj_remark IS NULL OR obj_remark = '' THEN\n"
+ " DELETE FROM sys.comments WHERE id = obj_id;\n"
+ " ELSEIF EXISTS (SELECT id FROM sys.comments WHERE id = obj_id)
THEN\n"
+ " UPDATE sys.comments SET remark = obj_remark WHERE id =
obj_id;\n"
+ " ELSE\n"
+ " INSERT INTO sys.comments VALUES (obj_id,
obj_remark);\n"
+ " END IF;\n"
+ " END IF;\n"
+ "END;\n"
+ "\n"
+ "CREATE FUNCTION sys.function_type_keyword(ftype INT)\n"
+ "RETURNS VARCHAR(20)\n"
+ "BEGIN\n"
+ " RETURN CASE ftype\n"
+ " WHEN 1 THEN 'FUNCTION'\n"
+ " WHEN 2 THEN 'PROCEDURE'\n"
+ " WHEN 3 THEN 'AGGREGATE'\n"
+ " WHEN 4 THEN 'FILTER FUNCTION'\n"
+ " WHEN 5 THEN 'FUNCTION' -- table returning function\n"
+ " WHEN 6 THEN 'FUNCTION' -- analytic function\n"
+ " WHEN 7 THEN 'LOADER'\n"
+ " ELSE 'ROUTINE'\n"
+ " END;\n"
+ "END;\n"
+ "GRANT EXECUTE ON FUNCTION sys.function_type_keyword(INT) TO PUBLIC;\n"
+ "\n"
+ "CREATE VIEW sys.describe_all_objects AS\n"
+ "SELECT s.name AS sname,\n"
+ " t.name,\n"
+ " s.name || '.' || t.name AS fullname,\n"
+ " CAST(CASE t.type\n"
+ " WHEN 1 THEN 2 -- ntype for views\n"
+ " ELSE 1 -- ntype for tables\n"
+ " END AS SMALLINT) AS ntype,\n"
+ " (CASE WHEN t.system THEN 'SYSTEM ' ELSE '' END) ||
tt.table_type_name AS type,\n"
+ " t.system,\n"
+ " c.remark AS remark\n"
+ " FROM sys._tables t\n"
+ " LEFT OUTER JOIN sys.comments c ON t.id = c.id\n"
+ " LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.id\n"
+ " LEFT OUTER JOIN sys.table_types tt ON t.type = tt.table_type_id\n"
+ "UNION ALL\n"
+ "SELECT s.name AS sname,\n"
+ " sq.name,\n"
+ " s.name || '.' || sq.name AS fullname,\n"
+ " CAST(4 AS SMALLINT) AS ntype,\n"
+ " 'SEQUENCE' AS type,\n"
+ " false AS system,\n"
+ " c.remark AS remark\n"
+ " FROM sys.sequences sq\n"
+ " LEFT OUTER JOIN sys.comments c ON sq.id = c.id\n"
+ " LEFT OUTER JOIN sys.schemas s ON sq.schema_id = s.id\n"
+ "UNION ALL\n"
+ "SELECT DISTINCT s.name AS sname, -- DISTINCT is needed to filter out
duplicate overloaded function/procedure names\n"
+ " f.name,\n"
+ " s.name || '.' || f.name AS fullname,\n"
+ " CAST(8 AS SMALLINT) AS ntype,\n"
+ " (CASE WHEN sf.function_id IS NOT NULL THEN 'SYSTEM ' ELSE ''
END) || sys.function_type_keyword(f.type) AS type,\n"
+ " CASE WHEN sf.function_id IS NULL THEN FALSE ELSE TRUE END AS
system,\n"
+ " c.remark AS remark\n"
+ " FROM sys.functions f\n"
+ " LEFT OUTER JOIN sys.comments c ON f.id = c.id\n"
+ " LEFT OUTER JOIN sys.schemas s ON f.schema_id = s.id\n"
+ " LEFT OUTER JOIN sys.systemfunctions sf ON f.id = sf.function_id\n"
+ "UNION ALL\n"
+ "SELECT s.name AS sname,\n"
+ " s.name,\n"
+ " s.name AS fullname,\n"
+ " CAST(16 AS SMALLINT) AS ntype,\n"
+ " (CASE WHEN s.system THEN 'SYSTEM SCHEMA' ELSE 'SCHEMA' END)
AS type,\n"
+ " s.system,\n"
+ " c.remark AS remark\n"
+ " FROM sys.schemas s\n"
+ " LEFT OUTER JOIN sys.comments c ON s.id = c.id\n"
+ " ORDER BY system, name, sname, ntype;\n"
+ "GRANT SELECT ON sys.describe_all_objects TO PUBLIC;\n"
+ "\n"
+ "CREATE VIEW sys.commented_function_signatures AS\n"
+ "SELECT f.id AS fid,\n"
+ " s.name AS schema,\n"
+ " f.name AS fname,\n"
+ " sys.function_type_keyword(f.type) AS category,\n"
+ " CASE WHEN sf.function_id IS NULL THEN FALSE ELSE TRUE END AS
system,\n"
+ " CASE RANK() OVER (PARTITION BY f.id ORDER BY p.number ASC) WHEN
1 THEN f.name ELSE NULL END AS name,\n"
+ " CASE RANK() OVER (PARTITION BY f.id ORDER BY p.number DESC)
WHEN 1 THEN c.remark ELSE NULL END AS remark,\n"
+ " p.type, p.type_digits, p.type_scale,\n"
+ " ROW_NUMBER() OVER (ORDER BY f.id, p.number) AS line\n"
+ " FROM sys.functions f\n"
+ " JOIN sys.comments c ON f.id = c.id\n"
+ " JOIN sys.schemas s ON f.schema_id = s.id\n"
+ " LEFT OUTER JOIN sys.systemfunctions sf ON f.id = sf.function_id\n"
+ " LEFT OUTER JOIN sys.args p ON f.id = p.func_id AND p.inout = 1\n"
+ " ORDER BY line;\n"
+ "GRANT SELECT ON sys.commented_function_signatures TO PUBLIC;\n";
- q = ""
- "INSERT INTO sys.systemfunctions\n"
- "SELECT id FROM sys.functions\n"
- "WHERE schema_id = (SELECT id FROM sys.schemas WHERE name =
'sys')\n"
- "AND name IN ('comment_on', 'function_type_keyword',
'describe_all_objects');\n";
err = SQLstatementIntern(c, &q, "update", 1, 0, NULL);
if (err)
return err;
@@ -1240,9 +1184,34 @@ sql_create_comments_table(Client c)
q = ""
"UPDATE sys._tables\n"
"SET system = true\n"
- "WHERE name IN ('comments', 'commented_function_signatures')\n"
+ "WHERE name IN ('comments', 'describe_all_objects',
'commented_function_signatures')\n"
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
