Changeset: 2a7262929b4c for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2a7262929b4c Modified Files: sql/backends/monet5/sql_upgrades.c Branch: mtest Log Message:
Added upgrade code for describe functions
diffs (193 lines):
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
@@ -2546,7 +2546,7 @@ sql_update_oct2020_sp1(Client c, mvc *sq
static str
sql_update_default(Client c, mvc *sql, const char *prev_schema, bool
*systabfixed)
{
- size_t bufsize = 1024, pos = 0;
+ size_t bufsize = 8192, pos = 0;
char *buf = NULL, *err = NULL;
res_table *output = NULL;
BAT *b = NULL;
@@ -2577,6 +2577,180 @@ sql_update_default(Client c, mvc *sql, c
"ALTER TABLE sys.function_types SET
READ WRITE;\n"
"UPDATE sys.function_types SET
function_type_keyword = 'WINDOW' WHERE function_type_id = 6;\n");
+ /* 52_describe.sql */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "CREATE FUNCTION
sys.describe_type(ctype string, digits integer, tscale integer)\n"
+ "RETURNS string\n"
+ "BEGIN\n"
+ "RETURN\n"
+ " CASE ctype\n"
+ " WHEN 'bigint' THEN 'BIGINT'\n"
+ " WHEN 'blob' THEN\n"
+ " CASE digits\n"
+ " WHEN 0 THEN 'BINARY LARGE
OBJECT'\n"
+ " ELSE 'BINARY LARGE OBJECT(' ||
CAST(digits AS string) || ')'\n"
+ " END\n"
+ " WHEN 'boolean' THEN 'BOOLEAN'\n"
+ " WHEN 'char' THEN\n"
+ " CASE digits\n"
+ " WHEN 1 THEN
'CHARACTER'\n"
+ " ELSE 'CHARACTER(' ||
CAST(digits AS string) || ')'\n"
+ " END\n"
+ " WHEN 'clob' THEN\n"
+ " CASE digits\n"
+ " WHEN 0 THEN 'CHARACTER LARGE
OBJECT'\n"
+ " ELSE 'CHARACTER LARGE OBJECT('
|| CAST(digits AS string) || ')'\n"
+ " END\n"
+ " WHEN 'date' THEN 'DATE'\n"
+ " WHEN 'day_interval' THEN
'INTERVAL DAY'\n"
+ " WHEN 'decimal' THEN 'DECIMAL('
|| CAST(digits AS string) || ',' || CAST(tscale AS string) || ')'\n"
+ " WHEN 'double' THEN\n"
+ " CASE\n"
+ " WHEN digits = 53 and tscale = 0
THEN 'DOUBLE'\n"
+ " WHEN tscale = 0 THEN 'FLOAT('
|| CAST(digits AS string) || ')'\n"
+ " ELSE 'FLOAT(' || CAST(digits AS
string) || ',' || CAST(tscale AS string) || ')'\n"
+ " END\n"
+ " WHEN 'geometry' THEN\n"
+ " CASE digits\n"
+ " WHEN 4 THEN 'GEOMETRY(POINT'
||\n"
+ " CASE tscale\n"
+ " WHEN 0 THEN
''\n"
+ " ELSE ',' ||
CAST(tscale AS string)\n"
+ " END || ')'\n"
+ " WHEN 8 THEN
'GEOMETRY(LINESTRING' ||\n"
+ " CASE tscale\n"
+ " WHEN 0 THEN
''\n"
+ " ELSE ',' ||
CAST(tscale AS string)\n"
+ " END || ')'\n"
+ " WHEN 16 THEN 'GEOMETRY(POLYGON'
||\n"
+ " CASE tscale\n"
+ " WHEN 0 THEN
''\n"
+ " ELSE ',' ||
CAST(tscale AS string)\n"
+ " END || ')'\n"
+ " WHEN 20 THEN
'GEOMETRY(MULTIPOINT' ||\n"
+ " CASE tscale\n"
+ " WHEN 0 THEN
''\n"
+ " ELSE ',' ||
CAST(tscale AS string)\n"
+ " END || ')'\n"
+ " WHEN 24 THEN
'GEOMETRY(MULTILINESTRING' ||\n"
+ " CASE tscale\n"
+ " WHEN 0 THEN
''\n"
+ " ELSE ',' ||
CAST(tscale AS string)\n"
+ " END || ')'\n"
+ " WHEN 28 THEN
'GEOMETRY(MULTIPOLYGON' ||\n"
+ " CASE tscale\n"
+ " WHEN 0 THEN
''\n"
+ " ELSE ',' ||
CAST(tscale AS string)\n"
+ " END || ')'\n"
+ " WHEN 32 THEN
'GEOMETRY(GEOMETRYCOLLECTION' ||\n"
+ " CASE tscale\n"
+ " WHEN 0 THEN
''\n"
+ " ELSE ',' ||
CAST(tscale AS string)\n"
+ " END || ')'\n"
+ " ELSE 'GEOMETRY'\n"
+ " END\n"
+ " WHEN 'hugeint' THEN 'HUGEINT'\n"
+ " WHEN 'int' THEN 'INTEGER'\n"
+ " WHEN 'month_interval' THEN\n"
+ " CASE digits\n"
+ " WHEN 1 THEN 'INTERVAL YEAR'\n"
+ " WHEN 2 THEN 'INTERVAL YEAR TO
MONTH'\n"
+ " WHEN 3 THEN 'INTERVAL MONTH'\n"
+ " END\n"
+ " WHEN 'real' THEN\n"
+ " CASE\n"
+ " WHEN digits = 24 and tscale = 0
THEN 'REAL'\n"
+ " WHEN tscale = 0 THEN 'FLOAT('
|| CAST(digits AS string) || ')'\n"
+ " ELSE 'FLOAT(' || CAST(digits AS
string) || ',' || CAST(tscale AS string) || ')'\n"
+ " END\n"
+ " WHEN 'sec_interval' THEN\n"
+ " CASE digits\n"
+ " WHEN 4 THEN 'INTERVAL DAY'\n"
+ " WHEN 5 THEN 'INTERVAL DAY TO
HOUR'\n"
+ " WHEN 6 THEN 'INTERVAL DAY TO
MINUTE'\n"
+ " WHEN 7 THEN 'INTERVAL DAY TO
SECOND'\n"
+ " WHEN 8 THEN 'INTERVAL HOUR'\n"
+ " WHEN 9 THEN 'INTERVAL HOUR TO
MINUTE'\n"
+ " WHEN 10 THEN 'INTERVAL HOUR TO
SECOND'\n"
+ " WHEN 11 THEN 'INTERVAL
MINUTE'\n"
+ " WHEN 12 THEN 'INTERVAL MINUTE
TO SECOND'\n"
+ " WHEN 13 THEN 'INTERVAL
SECOND'\n"
+ " END\n"
+ " WHEN 'smallint' THEN
'SMALLINT'\n"
+ " WHEN 'time' THEN\n"
+ " CASE digits\n"
+ " WHEN 1 THEN 'TIME'\n"
+ " ELSE 'TIME(' || CAST(digits - 1
AS string) || ')'\n"
+ " END\n"
+ " WHEN 'timestamp' THEN\n"
+ " CASE digits\n"
+ " WHEN 7 THEN 'TIMESTAMP'\n"
+ " ELSE 'TIMESTAMP(' ||
CAST(digits - 1 AS string) || ')'\n"
+ " END\n"
+ " WHEN 'timestamptz' THEN\n"
+ " CASE digits\n"
+ " WHEN 7 THEN 'TIMESTAMP'\n"
+ " ELSE 'TIMESTAMP(' ||
CAST(digits - 1 AS string) || ')'\n"
+ " END || ' WITH TIME ZONE'\n"
+ " WHEN 'timetz' THEN\n"
+ " CASE digits\n"
+ " WHEN 1 THEN 'TIME'\n"
+ " ELSE 'TIME(' || CAST(digits - 1
AS string) || ')'\n"
+ " END || ' WITH TIME ZONE'\n"
+ " WHEN 'tinyint' THEN 'TINYINT'\n"
+ " WHEN 'varchar' THEN 'CHARACTER
VARYING(' || CAST(digits AS string) || ')'\n"
+ " ELSE\n"
+ " CASE\n"
+ " WHEN lower(ctype) =
ctype THEN upper(ctype)\n"
+ " ELSE '\"' || ctype ||
'\"'\n"
+ " END || CASE digits\n"
+ " WHEN 0 THEN ''\n"
+ " ELSE '(' || CAST(digits
AS string) || CASE tscale\n"
+ " WHEN 0 THEN ''\n"
+ " ELSE ',' ||
CAST(tscale AS string)\n"
+ " END || ')'\n"
+ " END\n"
+ " END;\n"
+ " END;\n"
+ "create function
sys.describe_table(schemaName string, tableName string)\n"
+ "returns table(\"name\" string, query
string, \"type\" string, \"id\" integer, remark string)\n"
+ "BEGIN\n"
+ "RETURN SELECT t.name, t.query,
tt.table_type_name, t.id, c.remark\n"
+ " FROM sys.schemas s,
sys.table_types tt, sys._tables t\n"
+ " LEFT OUTER JOIN sys.comments c
ON t.id = c.id\n"
+ " WHERE s.name =
schemaName\n"
+ " AND t.schema_id =
s.id\n"
+ " AND t.name =
tableName\n"
+ " AND t.type =
tt.table_type_id;\n"
+ "END;\n"
+ "create function
sys.describe_columns(schemaName string, tableName string)\n"
+ "returns table(\"name\" string,
\"type\" string, digits integer, scale integer, Nulls boolean, cDefault string,
\"number\" integer, sqltype string, remark string)\n"
+ "BEGIN\n"
+ "return SELECT c.name, c.\"type\",
c.type_digits, c.type_scale, c.\"null\", c.\"default\", c.number,
describe_type(c.\"type\", c.type_digits, c.type_scale), com.remark\n"
+ " FROM sys._tables t, sys.schemas
s, sys._columns c\n"
+ " LEFT OUTER JOIN sys.comments
com ON c.id = com.id\n"
+ " WHERE c.table_id =
t.id\n"
+ " AND t.name =
tableName\n"
+ " AND t.schema_id =
s.id\n"
+ " AND s.name =
schemaName\n"
+ " ORDER BY c.number;\n"
+ "END;\n"
+ "create function
sys.describe_function(schemaName string, functionName string)\n"
+ "returns table(\"id\" integer, \"name\"
string, \"type\" string, \"language\" string, remark string)\n"
+ "BEGIN\n"
+ "return SELECT f.id, f.name,
ft.function_type_keyword, fl.language_keyword, c.remark\n"
+ " FROM sys.functions f\n"
+ " JOIN sys.schemas s ON
f.schema_id = s.id\n"
+ " JOIN sys.function_types ft ON
f.type = ft.function_type_id\n"
+ " LEFT OUTER JOIN
sys.function_languages fl ON f.language = fl.language_id\n"
+ " LEFT OUTER JOIN sys.comments c
ON f.id = c.id\n"
+ " WHERE f.name=functionName AND
s.name = schemaName;\n"
+ "END;\n");
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "update sys.functions set system = true
where system <> true and schema_id = (select id from sys.schemas where name =
'sys') and "
+ "((name = 'describe_type' and type =
%d) or (name in ('describe_table', 'describe_columns', 'describe_function') and
type = %d));\n", (int) F_FUNC, (int) F_UNION);
+
pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", prev_schema);
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
