Changeset: 2c9dd7b4b348 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/2c9dd7b4b348
Modified Files:
clients/mapiclient/dump.c
Branch: default
Log Message:
Legibility of embedded SQL queries.
diffs (truncated from 470 to 300 lines):
diff --git a/clients/mapiclient/dump.c b/clients/mapiclient/dump.c
--- a/clients/mapiclient/dump.c
+++ b/clients/mapiclient/dump.c
@@ -308,7 +308,14 @@ has_schema_max_memory(Mapi mid)
if (answer >= 0)
return answer;
- if ((hdl = mapi_query(mid, "select id from sys._columns where table_id
= (select id from sys._tables where name = 'db_user_info' and schema_id = 2000)
and name = 'max_memory'")) == NULL ||
+ if ((hdl = mapi_query(mid,
+ "SELECT id "
+ "FROM sys._columns "
+ "WHERE table_id = (SELECT id"
+ " FROM sys._tables"
+ " WHERE name = 'db_user_info'"
+ " AND schema_id = 2000)"
+ " AND name = 'max_memory'")) == NULL ||
mapi_error(mid))
goto bailout;
ret = mapi_get_row_count(hdl) == 1;
@@ -346,9 +353,9 @@ has_remote_user_info_table(Mapi mid)
return answer;
if ((hdl = mapi_query(mid,
- "select id from sys._tables"
- " where name = 'remote_user_info'"
- " and schema_id = 2000")) == NULL ||
+ "SELECT id FROM sys._tables"
+ " WHERE name = 'remote_user_info'"
+ " AND schema_id = 2000")) == NULL ||
mapi_error(mid))
goto bailout;
ret = mapi_get_row_count(hdl) == 1;
@@ -385,9 +392,9 @@ has_check_constraint(Mapi mid)
return answer;
if ((hdl = mapi_query(mid,
- "select id from sys.functions"
- " where schema_id = 2000"
- " and name = 'check_constraint'")) == NULL ||
+ "SELECT id FROM sys.functions"
+ " WHERE schema_id = 2000"
+ " AND name = 'check_constraint'")) == NULL ||
mapi_error(mid))
goto bailout;
ret = mapi_get_row_count(hdl) == 1;
@@ -526,7 +533,7 @@ dump_foreign_keys(Mapi mid, const char *
" AND fkt.schema_id = fs.id "
" AND fkt.system = FALSE "
"ORDER BY fs.name, fkt.name, "
- "fkk.name, fkkc.nr";
+ " fkk.name, fkkc.nr";
}
hdl = mapi_query(mid, query);
if (query != NULL && maxquerylen != 0)
@@ -923,7 +930,8 @@ dump_column_definition(Mapi mid, stream
" us.name, " /* 6 */
" uo.name " /* 7 */
"FROM sys._columns c "
- " LEFT OUTER JOIN sys.dependencies d ON c.id =
d.depend_id "
+ " LEFT OUTER JOIN sys.dependencies d"
+ " ON c.id = d.depend_id "
" LEFT OUTER JOIN sys.objects uo ON d.id = uo.id "
" LEFT OUTER JOIN sys.schemas us ON uo.nr = us.id "
"WHERE c.table_id = %s "
@@ -939,9 +947,10 @@ dump_column_definition(Mapi mid, stream
" us.name, " /* 6 */
" uo.name " /* 7 */
"FROM sys._columns c "
- " LEFT OUTER JOIN sys.dependencies d ON c.id =
d.depend_id "
+ " LEFT OUTER JOIN sys.dependencies d"
+ " ON c.id = d.depend_id "
" LEFT OUTER JOIN sys.objects uo ON d.id = uo.id "
- " LEFT OUTER JOIN sys.schemas us ON uo.nr = us.id,
"
+ " LEFT OUTER JOIN sys.schemas us ON uo.nr = us.id,"
" sys._tables t, "
" sys.schemas s "
"WHERE c.table_id = t.id "
@@ -1112,7 +1121,7 @@ dump_column_definition(Mapi mid, stream
" k.name, " /* 2 */
" kc.id, " /* 3 */
" k.type, " /* 4 */
- " %s " /* 5 */
+ " %s " /* 5 */
"FROM sys.objects kc, "
" sys.keys k "
"WHERE kc.id = k.id "
@@ -1126,7 +1135,7 @@ dump_column_definition(Mapi mid, stream
" k.name, " /* 2 */
" kc.id, " /* 3 */
" k.type, " /* 4 */
- " %s " /* 5 */
+ " %s " /* 5 */
"FROM sys.objects kc, "
" sys.keys k, "
" sys.schemas s, "
@@ -1311,7 +1320,9 @@ describe_table(Mapi mid, const char *sch
"FROM sys.schemas s, "
" sys.functions f "
"WHERE s.id = f.schema_id "
- " AND f.id IN (SELECT id FROM
sys.dependencies WHERE depend_id = '%d')",
+ " AND f.id IN (SELECT id"
+ " FROM sys.dependencies"
+ " WHERE depend_id = '%d')",
table_id);
if ((hdl = mapi_query(mid, query)) == NULL ||
mapi_error(mid))
goto bailout;
@@ -1360,9 +1371,18 @@ describe_table(Mapi mid, const char *sch
char *rt_hash = NULL;
if (has_remote_user_info_table(mid)) {
snprintf(query, maxquerylen,
- "SELECT username,
sys.decypher(password) "
- "FROM sys.remote_user_info where
table_id = (select t.id from sys._tables t, sys.schemas s where "
- " t.schema_id = s.id and s.name =
'%s' and t.name = '%s')", schema, tname);
+ "SELECT username,"
+ " sys.decypher(password) "
+ "FROM sys.remote_user_info "
+ "WHERE table_id = (SELECT t.id"
+ " FROM sys._tables t,"
+ " sys.schemas s"
+ " WHERE "
+ " t.schema_id = s.id"
+ " AND s.name = '%s'"
+ " AND t.name = '%s'"
+ " )",
+ schema, tname);
} else {
snprintf(query, maxquerylen,
"SELECT username, hash "
@@ -1387,7 +1407,10 @@ describe_table(Mapi mid, const char *sch
} else if (type == 3) { /* A merge table might be partitioned */
int properties = 0;
- snprintf(query, maxquerylen, "SELECT tp.type FROM
sys.table_partitions tp WHERE tp.table_id = '%d'", table_id);
+ snprintf(query, maxquerylen,
+ "SELECT tp.type "
+ "FROM sys.table_partitions tp "
+ "WHERE tp.table_id = '%d'", table_id);
if ((hdl = mapi_query(mid, query)) == NULL ||
mapi_error(mid))
goto bailout;
while (mapi_fetch_row(hdl) != 0)
@@ -1402,13 +1425,27 @@ describe_table(Mapi mid, const char *sch
if (column) { /* by column */
snprintf(query, maxquerylen,
- "SELECT c.name FROM
sys.schemas s, sys._tables t, sys._columns c, sys.table_partitions tp "
- "WHERE s.name = '%s' AND
t.name = '%s' AND s.id = t.schema_id AND t.id = c.table_id "
- " AND c.id = tp.column_id",
s, t);
+ "SELECT c.name "
+ "FROM sys.schemas s,"
+ " sys._tables t,"
+ " sys._columns c,"
+ " sys.table_partitions tp "
+ "WHERE s.name = '%s'"
+ " AND t.name = '%s'"
+ " AND s.id = t.schema_id"
+ " AND t.id = c.table_id "
+ " AND c.id = tp.column_id",
+ s, t);
} else { /* by expression */
snprintf(query, maxquerylen,
- "SELECT tp.expression FROM
sys.schemas s, sys._tables t, sys.table_partitions tp "
- "WHERE s.name = '%s' AND
t.name = '%s' AND s.id = t.schema_id AND t.id = tp.table_id",
+ "SELECT tp.expression "
+ "FROM sys.schemas s,"
+ " sys._tables t,"
+ " sys.table_partitions tp "
+ "WHERE s.name = '%s'"
+ " AND t.name = '%s'"
+ " AND s.id = t.schema_id"
+ " AND t.id = tp.table_id",
s, t);
}
if ((hdl = mapi_query(mid, query)) == NULL ||
mapi_error(mid))
@@ -1439,8 +1476,8 @@ describe_table(Mapi mid, const char *sch
" sys.schemas s, "
" sys._tables AS t, "
" (VALUES (0, 'INDEX'), "
- " (4, 'IMPRINTS INDEX'), "
- " (5, 'ORDERED INDEX')) AS it (id, idx) "
+ " (4, 'IMPRINTS INDEX'), "
+ " (5, 'ORDERED INDEX')) AS it (id, idx) "
"WHERE i.table_id = t.id "
" AND i.id = kc.id "
" AND t.id = c.table_id "
@@ -1491,7 +1528,12 @@ describe_table(Mapi mid, const char *sch
"SELECT i.name, c.remark "
"FROM sys.idxs i, sys.comments c "
"WHERE i.id = c.id "
- " AND i.table_id = (SELECT id FROM sys._tables WHERE
schema_id = (select id FROM sys.schemas WHERE name = '%s') AND name = '%s') "
+ " AND i.table_id = (SELECT id"
+ " FROM sys._tables"
+ " WHERE schema_id = (SELECT id"
+ " FROM sys.schemas"
+ " WHERE name = '%s')"
+ " AND name = '%s') "
"ORDER BY i.name",
s, t);
if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid))
@@ -1509,7 +1551,12 @@ describe_table(Mapi mid, const char *sch
"SELECT col.name, com.remark "
"FROM sys._columns col, sys.comments com "
"WHERE col.id = com.id "
- " AND col.table_id = (SELECT id FROM sys._tables WHERE
schema_id = (SELECT id FROM sys.schemas WHERE name = '%s') AND name = '%s') "
+ " AND col.table_id = (SELECT id"
+ " FROM sys._tables"
+ " WHERE schema_id = (SELECT id"
+ " FROM sys.schemas"
+ " WHERE name = '%s')"
+ " AND name = '%s') "
"ORDER BY col.number",
s, t);
if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid))
@@ -1592,7 +1639,8 @@ describe_sequence(Mapi mid, const char *
snprintf(query, maxquerylen,
"SELECT c.remark, q.* "
- "FROM sys.sequences seq LEFT OUTER JOIN sys.comments c ON
seq.id = c.id, "
+ "FROM sys.sequences seq LEFT OUTER JOIN sys.comments c"
+ " ON seq.id = c.id, "
" sys.schemas s, "
" sys.describe_sequences q "
"WHERE s.id = seq.schema_id "
@@ -1690,7 +1738,8 @@ describe_schema(Mapi mid, const char *sn
snprintf(schemas, sizeof(schemas),
"SELECT s.name, a.name, c.remark "
"FROM sys.auths a, "
- " sys.schemas s LEFT OUTER JOIN sys.comments c ON s.id =
c.id "
+ " sys.schemas s LEFT OUTER JOIN sys.comments c"
+ " ON s.id = c.id "
"WHERE s.\"authorization\" = a.id "
" AND s.name = '%s' "
"ORDER BY s.name",
@@ -1983,8 +2032,12 @@ dump_table_storage(Mapi mid, const char
snprintf(query, maxquerylen,
"SELECT name, storage FROM sys._columns "
"WHERE storage NOT LIKE 'USTR%%' "
- " AND table_id = (SELECT id FROM sys._tables WHERE name =
'%s' "
- " AND schema_id = (SELECT id FROM sys.schemas WHERE name =
'%s'))",
+ " AND table_id = (SELECT id"
+ " FROM sys._tables"
+ " WHERE name = '%s' "
+ " AND schema_id = (SELECT id"
+ " FROM sys.schemas"
+ " WHERE name = '%s'))",
t, s);
if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid))
goto bailout;
@@ -2030,7 +2083,10 @@ dump_table_access(Mapi mid, const char *
snprintf(query, maxquerylen,
"SELECT t.access FROM sys._tables t, sys.schemas s "
- "WHERE s.name = '%s' AND t.schema_id = s.id AND t.name = '%s'
AND t.access in (1, 2)",
+ "WHERE s.name = '%s'"
+ " AND t.schema_id = s.id"
+ " AND t.name = '%s'"
+ " AND t.access in (1, 2)",
s, t);
if ((hdl = mapi_query(mid, query)) == NULL || mapi_error(mid))
goto bailout;
@@ -2166,7 +2222,13 @@ dump_table(Mapi mid, const char *schema,
fprintf(stderr, "malloc failure\n");
goto doreturn;
}
- snprintf(query, qlen, "SELECT s.name, t.name FROM
sys._tables t, sys.schemas s WHERE t.schema_id = s.id AND s.name LIKE '%s' AND
t.name LIKE '%s' ORDER BY t.id", s, t);
+ snprintf(query, qlen,
+ "SELECT s.name, t.name "
+ "FROM sys._tables t, sys.schemas s "
+ "WHERE t.schema_id = s.id"
+ " AND s.name LIKE '%s'"
+ " AND t.name LIKE '%s' "
+ "ORDER BY t.id", s, t);
free(s);
free(t);
MapiHdl hdl = mapi_query(mid, query);
@@ -2278,8 +2340,10 @@ dump_function(Mapi mid, stream *sqlf, co
" c.remark "
"FROM sys.functions f "
" JOIN sys.schemas s ON f.schema_id = s.id "
- " JOIN sys.function_types ft ON f.type =
ft.function_type_id "
- " LEFT OUTER JOIN sys.comments c ON f.id =
c.id "
+ " JOIN sys.function_types ft"
+ " ON f.type = ft.function_type_id "
+ " LEFT OUTER JOIN sys.comments c"
+ " ON f.id = c.id "
"WHERE f.id = %s",
fid);
assert(query_len < (int) query_size);
@@ -2606,7 +2670,8 @@ dump_functions(Mapi mid, stream *sqlf, c
query_len += snprintf(query + query_len, query_size -
query_len, "AND NOT f.system ");
}
}
- query_len += snprintf(query + query_len, query_size - query_len, "ORDER
BY f.func, f.id");
+ query_len += snprintf(query + query_len, query_size - query_len,
+ "ORDER BY f.func, f.id");
assert(query_len < (int) query_size);
if (query_len >= (int) query_size) {
free(query);
@@ -2670,11 +2735,13 @@ dump_database(Mapi mid, stream *sqlf, co
"SELECT s.name, "
" t.systemname, "
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
