Changeset: 90be490cc6cb for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=90be490cc6cb Modified Files: clients/mapiclient/mclient.c sql/scripts/97_comments.sql sql/scripts/99_system.sql Branch: comment-on Log Message:
Implement mclient \d <pattern> with a sql function
Currently defined in 97_comments.sql, will have to move
to another file later on.
Does not include the comment yet.
diffs (273 lines):
diff --git a/clients/mapiclient/mclient.c b/clients/mapiclient/mclient.c
--- a/clients/mapiclient/mclient.c
+++ b/clients/mapiclient/mclient.c
@@ -2199,6 +2199,7 @@ showCommands(void)
mnstr_printf(toConsole, "\\q - terminate session\n");
}
+/* These values must match those used in sys.describe_all_objects() */
#define MD_TABLE 1
#define MD_VIEW 2
#define MD_SEQ 4
@@ -2530,126 +2531,42 @@ doFile(Mapi mid, stream *fp, int useinse
#endif
} else {
/* get all object names in
current schema */
- char *type, *name, *schema;
- char q[4096];
- char nameq[128];
- char funcq[512];
+ char query[4096], *q = query,
*endq = query + sizeof(query);
+ char *name_column = hasSchema ?
"fullname" : "name";
+ char *type, *name; /* output
columns */
- if (!*line) {
- line = "%";
- hasSchema = 0;
- }
- if (hasSchema) {
- snprintf(nameq,
sizeof(nameq),
- "s.name
|| '.' || o.name LIKE '%s'",
- line);
- } else {
- snprintf(nameq,
sizeof(nameq),
- "s.name
= current_schema AND "
- "o.name
LIKE '%s'",
- line);
+ /*
+ * | LINE | SCHEMA
FILTER | NAME FILTER |
+ *
|-----------------+---------------+-------------------------------|
+ * | "" | yes
| - |
+ * | "my_table" | yes
| name LIKE 'my_table' |
+ * | "my*" | yes
| name LIKE 'my%' |
+ * | "data.my_table" | no
| fullname LIKE 'data.my_table' |
+ * | "data.my*" | no
| fullname LIKE 'data.my%' |
+ * | "*a.my*" | no
| fullname LIKE '%a.my%' |
+ */
+ q += snprintf(q, endq - q,
"SELECT type, fullname, ntype FROM sys.describe_all_objects()\n");
+ q += snprintf(q, endq - q,
"WHERE (ntype & %d > 0)\n", x);
+ if (!wantsSystem) {
+ q += snprintf(q, endq -
q, "AND NOT system\n");
}
- snprintf(funcq, sizeof(funcq),
- "SELECT o.name, "
- "(CASE WHEN
sf.function_id IS NOT NULL "
- "THEN
'SYSTEM ' "
- "ELSE '' "
- "END ||
'FUNCTION') AS type, "
- "CASE WHEN
sf.function_id IS NULL "
- "THEN
false "
- "ELSE
true "
- "END AS
system, "
- "s.name AS
sname, "
- "%d AS ntype "
- "FROM sys.functions o "
- "LEFT JOIN
sys.systemfunctions sf "
- "ON o.id =
sf.function_id, "
- "sys.schemas s "
- "WHERE o.schema_id =
s.id AND "
- "%s ",
- MD_FUNC,
- nameq);
- snprintf(q, sizeof(q),
- "SELECT name, "
- "CAST(type AS
VARCHAR(30)) AS type, "
- "system, "
- "sname, "
- "ntype "
- "FROM (SELECT o.name, "
- "(CASE
o.system "
-
"WHEN true THEN 'SYSTEM ' "
-
"ELSE '' "
- "END || "
- "CASE
o.type "
-
"WHEN 0 THEN 'TABLE' "
-
"WHEN 1 THEN 'VIEW' "
-
"WHEN 3 THEN 'MERGE TABLE' "
-
"WHEN 4 THEN 'STREAM TABLE' "
-
"WHEN 5 THEN 'REMOTE TABLE' "
-
"WHEN 6 THEN 'REPLICA TABLE' "
-
"ELSE '' "
- "END) AS
type, "
-
"o.system, "
- "s.name
AS sname, "
- "CASE
o.type "
-
"WHEN 0 THEN %d "
-
"WHEN 1 THEN %d "
-
"WHEN 3 THEN %d "
-
"WHEN 4 THEN %d "
-
"WHEN 5 THEN %d "
-
"WHEN 6 THEN %d "
-
"ELSE 0 "
- "END AS
ntype "
- "FROM
sys._tables o, "
-
"sys.schemas s "
- "WHERE
o.schema_id = s.id AND "
- "%s AND "
- "o.type IN
(0, 1, 3, 4, 5, 6) "
- "UNION "
- "SELECT o.name, "
-
"'SEQUENCE' AS type, "
- "false AS
system, "
- "s.name
AS sname, "
- "%d AS
ntype "
- "FROM
sys.sequences o, "
-
"sys.schemas s "
- "WHERE
o.schema_id = s.id AND "
- "%s "
- "UNION "
- "%s "
- "UNION "
- "SELECT NULL AS
name, "
- "(CASE
WHEN o.system THEN 'SYSTEM ' ELSE '' END || 'SCHEMA') AS type, "
- "o.system
AS system, "
- "o.name
AS sname, "
- "%d AS
ntype "
- "FROM
sys.schemas o "
- "WHERE o.name
LIKE '%s'"
- ") AS \"all\" "
- "WHERE ntype & %u > 0 "
- "%s "
- "ORDER BY system,
name, sname",
- MD_TABLE, MD_VIEW,
MD_TABLE, MD_TABLE, MD_TABLE, MD_TABLE,
- nameq,
- MD_SEQ,
- nameq, funcq,
- MD_SCHEMA,
- line, x,
- (wantsSystem ?
- "" :
- "AND system =
false"));
- hdl = mapi_query(mid, q);
+ if (!hasSchema) {
+ q += snprintf(q, endq -
q, "AND (sname IS NULL OR sname = current_schema)\n");
+ }
+ if (*line) {
+ q += snprintf(q, endq -
q, "AND (%s LIKE '%s')\n", name_column, line);
+ }
+ q += snprintf(q, endq - q,
";\n");
+
+ hdl = mapi_query(mid, query);
CHECK_RESULT(mid, hdl,
continue, buf, fp);
- while (fetch_row(hdl) == 5) {
- name =
mapi_fetch_field(hdl, 0);
- type =
mapi_fetch_field(hdl, 1);
- schema =
mapi_fetch_field(hdl, 3);
+ while (fetch_row(hdl) == 3) {
+ type =
mapi_fetch_field(hdl, 0);
+ name =
mapi_fetch_field(hdl, 1);
mnstr_printf(toConsole,
- "%-*s
%s%s%s\n",
-
mapi_get_len(hdl, 1),
- type,
schema,
- name
!= NULL ? "." : "",
- name
!= NULL ? name : "");
+ "%-*s
%s\n",
+
mapi_get_len(hdl, 0), type,
+ name);
}
mapi_close_handle(hdl);
hdl = NULL;
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
@@ -19,3 +19,86 @@ BEGIN
INSERT INTO sys.comments VALUES (obj_id, obj_remark);
END IF;
END;
+
+
+
+-- Temporary home for this function.
+-- We have to create table systemfunctions first, because describe_all_objects
uses it
+-- to recognize system functions. For some reason, the functions table does
not have a
+-- 'system' column.
+
+CREATE TABLE systemfunctions (function_id INTEGER NOT NULL);
+
+CREATE FUNCTION sys.describe_all_objects()
+RETURNS TABLE (
+ sname VARCHAR(1024),
+ name VARCHAR(1024),
+ fullname VARCHAR(1024),
+ ntype INTEGER, -- must match the MD_TABLE/VIEW/SEQ/FUNC/SCHEMA
constants in mclient.c
+ type VARCHAR(30),
+ system BOOLEAN
+)
+BEGIN
+ RETURN TABLE (
+ WITH
+ table_data AS (
+ SELECT schema_id AS sid,
+ id,
+ name,
+ system,
+ (CASE type
+ WHEN 1 THEN 2 -- ntype for views
+ ELSE 1 -- ntype for tables
+ END) AS ntype,
+ table_type_name AS type
+ FROM sys._tables LEFT OUTER JOIN sys.table_types ON type =
table_type_id
+ WHERE type IN (0, 1, 3, 4, 5, 6)
+ ),
+ sequence_data AS (
+ SELECT schema_id AS sid,
+ id,
+ name,
+ false AS system,
+ 4 AS ntype,
+ 'SEQUENCE' AS type
+ FROM sys.sequences
+ ),
+ function_data AS (
+ SELECT schema_id AS sid,
+ id,
+ name,
+ (id IN (SELECT function_id FROM
sys.systemfunctions)) AS system,
+ 8 AS ntype,
+ 'FUNCTION' AS type
+ FROM sys.functions
+ ),
+ schema_data AS (
+ SELECT 0 AS sid,
+ id,
+ name,
+ system,
+ 16 AS ntype,
+ 'SCHEMA' AS type
+ FROM sys.schemas
+ ),
+ all_data AS (
+ SELECT * FROM table_data
+ UNION
+ SELECT * FROM sequence_data
+ UNION
+ SELECT * FROM function_data
+ UNION
+ SELECT * FROM schema_data
+ )
+ SELECT DISTINCT
+ s.name AS sname,
+ a.name AS name,
+ COALESCE(s.name || '.', '') || a.name AS fullname,
+ a.ntype AS ntype,
+ (CASE WHEN a.system THEN 'SYSTEM ' ELSE '' END) || a.type
AS type,
+ a.system AS system
+ FROM all_data a
+ LEFT OUTER JOIN sys.schemas s ON a.sid = s.id
+ ORDER BY system, name, fullname, ntype
+ );
+END;
diff --git a/sql/scripts/99_system.sql b/sql/scripts/99_system.sql
--- a/sql/scripts/99_system.sql
+++ b/sql/scripts/99_system.sql
@@ -5,8 +5,7 @@
-- Copyright 1997 - July 2008 CWI, August 2008 - 2017 MonetDB B.V.
-- only system functions until now
-create table systemfunctions (function_id)
- as (select id from functions) with data;
+insert into systemfunctions select id from functions;
grant select on systemfunctions to public;
-- only system tables until now
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
