Changeset: 2c11dcbc4179 for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2c11dcbc4179 Modified Files: sql/backends/monet5/sql_upgrades.c sql/scripts/52_describe.sql sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128 sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128 sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128 sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64 sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64.int128 sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128 sql/test/emptydb-upgrade/Tests/upgrade.stable.out sql/test/emptydb-upgrade/Tests/upgrade.stable.out.32bit sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128 sql/test/emptydb/Tests/check.stable.out sql/test/emptydb/Tests/check.stable.out.32bit sql/test/emptydb/Tests/check.stable.out.int128 sql/test/testdb-upgrade-chain-hge/Tests/upgrade.stable.out.int128 sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.32bit sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128 sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128 sql/test/testdb-upgrade/Tests/upgrade.stable.out sql/test/testdb-upgrade/Tests/upgrade.stable.out.32bit sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128 Branch: default Log Message:
Fix and approve upgrade code.
diffs (truncated from 4673 to 300 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
@@ -2580,171 +2580,171 @@ sql_update_default(Client c, mvc *sql, c
/* 52_describe.sql */
pos += snprintf(buf + pos, bufsize - pos,
"CREATE FUNCTION
sys.describe_type(ctype string, digits integer, tscale integer)\n"
- "RETURNS string\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"
+ " 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"
+ " 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"
+ "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"
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
