Changeset: 367645ad4deb for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=367645ad4deb Modified Files: clients/mapiclient/dump.c clients/mapiclient/mclient.c Branch: system-functions Log Message:
We don't need to send multi-line queries to the server. This makes the strings ever so slightly more readable. diffs (178 lines): diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c --- a/clients/mapiclient/dump.c +++ b/clients/mapiclient/dump.c @@ -1365,22 +1365,22 @@ dump_function_comment(Mapi mid, stream * return 1; snprintf(query, len, - "SELECT \n" - " coalesce(function_type_keyword, '') AS category,\n" - " s.name AS schema,\n" - " CASE RANK() OVER (PARTITION BY f.id ORDER BY p.number ASC) WHEN 1 THEN f.name ELSE NULL END AS name,\n" - " p.type, \n" - " p.type_digits, \n" - " p.type_scale,\n" - " CASE RANK() OVER (PARTITION BY f.id ORDER BY p.number DESC) WHEN 1 THEN c.remark ELSE NULL END AS remark,\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.function_types ft ON f.type = ft.function_type_id\n" - "LEFT OUTER JOIN sys.args p ON f.id = p.func_id AND p.inout = 1\n" - "WHERE f.id = %s\n" - "ORDER BY line;", id); + "SELECT" + " coalesce(function_type_keyword, '') AS category," + " s.name AS schema," + " CASE RANK() OVER (PARTITION BY f.id ORDER BY p.number ASC) WHEN 1 THEN f.name ELSE NULL END AS name," + " p.type," + " p.type_digits," + " p.type_scale," + " CASE RANK() OVER (PARTITION BY f.id ORDER BY p.number DESC) WHEN 1 THEN c.remark ELSE NULL END AS remark," + " ROW_NUMBER() OVER (ORDER BY f.id, p.number) AS line" + " FROM sys.functions f" + " JOIN sys.comments c ON f.id = c.id" + " JOIN sys.schemas s ON f.schema_id = s.id" + " LEFT OUTER JOIN sys.function_types ft ON f.type = ft.function_type_id" + " LEFT OUTER JOIN sys.args p ON f.id = p.func_id AND p.inout = 1" + " WHERE f.id = %s" + " ORDER BY line", id); hashge = has_hugeint(mid); hdl = mapi_query(mid, query); diff --git a/clients/mapiclient/mclient.c b/clients/mapiclient/mclient.c --- a/clients/mapiclient/mclient.c +++ b/clients/mapiclient/mclient.c @@ -2540,56 +2540,56 @@ doFile(Mapi mid, stream *fp, bool useins } else { /* get all object names in current schema */ 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) || function_type_keyword 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.function_types ft ON f.type = ft.function_type_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" + "WITH 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) || function_type_keyword 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.function_types ft ON f.type = ft.function_type_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)" ; size_t len = strlen(with_clause) + 500 + strlen(line); char *query = malloc(len); @@ -2610,19 +2610,18 @@ doFile(Mapi mid, stream *fp, bool useins * | "*a.my*" | no | fullname LIKE '%a.my%' | */ 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); + q += snprintf(q, endq - q, " SELECT type, fullname, remark FROM describe_all_objects"); + q += snprintf(q, endq - q, " WHERE (ntype & %u) > 0", x); if (!wantsSystem) { - q += snprintf(q, endq - q, "AND NOT system\n"); + q += snprintf(q, endq - q, " AND NOT system"); } if (!hasSchema) { - q += snprintf(q, endq - q, "AND (sname IS NULL OR sname = current_schema)\n"); + q += snprintf(q, endq - q, " AND (sname IS NULL OR sname = current_schema)"); } if (*line) { - q += snprintf(q, endq - q, "AND (%s LIKE '%s')\n", name_column, line); + q += snprintf(q, endq - q, " AND (%s LIKE '%s')", name_column, line); } - q += snprintf(q, endq - q, "ORDER BY fullname, type, remark\n"); - q += snprintf(q, endq - q, ";\n"); + q += snprintf(q, endq - q, " ORDER BY fullname, type, remark"); hdl = mapi_query(mid, query); free(query); _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list