Changeset: 9c78aebbb5b9 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=9c78aebbb5b9
Modified Files:
clients/mapiclient/mclient.c
sql/backends/monet5/sql_upgrades.c
sql/scripts/97_comments.sql
Branch: Mar2018
Log Message:
Inline view describe_all_objects into mclient.c
diffs (216 lines):
diff --git a/clients/mapiclient/mclient.c b/clients/mapiclient/mclient.c
--- a/clients/mapiclient/mclient.c
+++ b/clients/mapiclient/mclient.c
@@ -2192,7 +2192,6 @@ showCommands(void)
mnstr_printf(toConsole, "\\q - terminate session\n");
}
-/* These values must match those used in view sys.describe_all_objects */
#define MD_TABLE 1
#define MD_VIEW 2
#define MD_SEQ 4
@@ -2540,7 +2539,58 @@ doFile(Mapi mid, stream *fp, bool useins
#endif
} else {
/* get all object names in
current schema */
- size_t len = 500 + strlen(line);
+ char *with_clause =
+ "WITH
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\t --
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"
+ " sf.function_id
IS NOT NULL 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 NULL 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"
+ ;
+ size_t len =
strlen(with_clause) + 500 + strlen(line);
char *query = malloc(len);
char *q = query, *endq = query
+ len;
char *name_column = hasSchema ?
"fullname" : "name";
@@ -2558,7 +2608,8 @@ doFile(Mapi mid, stream *fp, bool useins
* | "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,
"%s", with_clause);
+ q += snprintf(q, endq - q,
"SELECT type, fullname, remark FROM 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
@@ -1328,57 +1328,7 @@ sql_update_mar2018(Client c, mvc *sql)
" ELSE 'ROUTINE'\n"
" END;\n"
"END;\n"
- "GRANT EXECUTE ON FUNCTION
sys.function_type_keyword(INT) TO PUBLIC;\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"
- " sf.function_id IS NOT NULL 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 NULL 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"
+ "GRANT EXECUTE ON FUNCTION
sys.function_type_keyword(INT) TO PUBLIC;\n"
"CREATE VIEW sys.commented_function_signatures AS\n"
"SELECT f.id AS fid,\n"
" s.name AS schema,\n"
@@ -1398,7 +1348,7 @@ sql_update_mar2018(Client c, mvc *sql)
"GRANT SELECT ON sys.commented_function_signatures TO
PUBLIC;\n"
"UPDATE sys._tables\n"
"SET system = true\n"
- "WHERE name IN ('comments', 'describe_all_objects',
'commented_function_signatures')\n"
+ "WHERE name IN ('comments',
'commented_function_signatures')\n"
"AND schema_id = (SELECT id FROM sys.schemas WHERE name
= 'sys');\n"
"DELETE FROM sys.systemfunctions WHERE function_id IS
NULL;\n"
"ALTER TABLE sys.systemfunctions ALTER COLUMN
function_id SET NOT NULL;\n"
diff --git a/sql/scripts/97_comments.sql b/sql/scripts/97_comments.sql
--- a/sql/scripts/97_comments.sql
+++ b/sql/scripts/97_comments.sql
@@ -10,60 +10,6 @@ CREATE TABLE sys.systemfunctions (functi
GRANT SELECT ON sys.systemfunctions TO PUBLIC;
--- utility view to list all objects (except columns) which can have a
comment/remark associated
--- it is used in mclient and mdump code
-CREATE VIEW sys.describe_all_objects AS
-SELECT s.name AS sname,
- t.name,
- s.name || '.' || t.name AS fullname,
- CAST(CASE t.type
- WHEN 1 THEN 2 -- ntype for views
- ELSE 1 -- ntype for tables
- END AS SMALLINT) AS ntype,
- (CASE WHEN t.system THEN 'SYSTEM ' ELSE '' END) || tt.table_type_name
AS type,
- t.system,
- c.remark AS remark
- FROM sys._tables t
- LEFT OUTER JOIN sys.comments c ON t.id = c.id
- LEFT OUTER JOIN sys.schemas s ON t.schema_id = s.id
- LEFT OUTER JOIN sys.table_types tt ON t.type = tt.table_type_id
-UNION ALL
-SELECT s.name AS sname,
- sq.name,
- s.name || '.' || sq.name AS fullname,
- CAST(4 AS SMALLINT) AS ntype,
- 'SEQUENCE' AS type,
- false AS system,
- c.remark AS remark
- FROM sys.sequences sq
- LEFT OUTER JOIN sys.comments c ON sq.id = c.id
- LEFT OUTER JOIN sys.schemas s ON sq.schema_id = s.id
-UNION ALL
-SELECT DISTINCT s.name AS sname, -- DISTINCT is needed to filter out
duplicate overloaded function/procedure names
- f.name,
- s.name || '.' || f.name AS fullname,
- CAST(8 AS SMALLINT) AS ntype,
- (CASE WHEN sf.function_id IS NOT NULL THEN 'SYSTEM ' ELSE '' END) ||
sys.function_type_keyword(f.type) AS type,
- sf.function_id IS NOT NULL AS system,
- c.remark AS remark
- FROM sys.functions f
- LEFT OUTER JOIN sys.comments c ON f.id = c.id
- LEFT OUTER JOIN sys.schemas s ON f.schema_id = s.id
- LEFT OUTER JOIN sys.systemfunctions sf ON f.id = sf.function_id
-UNION ALL
-SELECT NULL AS sname,
- s.name,
- s.name AS fullname,
- CAST(16 AS SMALLINT) AS ntype,
- (CASE WHEN s.system THEN 'SYSTEM SCHEMA' ELSE 'SCHEMA' END) AS type,
- s.system,
- c.remark AS remark
- FROM sys.schemas s
- LEFT OUTER JOIN sys.comments c ON s.id = c.id
- ORDER BY system, name, sname, ntype;
-GRANT SELECT ON sys.describe_all_objects TO PUBLIC;
-
-
CREATE VIEW sys.commented_function_signatures AS
SELECT f.id AS fid,
s.name AS schema,
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
