Changeset: 7ea1cab8361e for MonetDB URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=7ea1cab8361e Modified Files: sql/backends/monet5/sql_upgrades.c 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/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:
Add upgrade code. diffs (truncated from 880 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 @@ -2718,6 +2718,40 @@ sql_update_default(Client c, mvc *sql, c " 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, diff --git a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128 b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128 --- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128 +++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128 @@ -4350,6 +4350,40 @@ BEGIN END END; END; +CREATE FUNCTION sys.describe_table(schemaName string, tableName string) + RETURNS TABLE(name string, query string, type string, id integer, remark string) +BEGIN + RETURN SELECT t.name, t.query, tt.table_type_name, t.id, c.remark + FROM sys.schemas s, sys.table_types tt, sys._tables t + LEFT OUTER JOIN sys.comments c ON t.id = c.id + WHERE s.name = schemaName + AND t.schema_id = s.id + AND t.name = tableName + AND t.type = tt.table_type_id; +END; +CREATE FUNCTION sys.describe_columns(schemaName string, tableName string) + RETURNS TABLE(name string, type string, digits integer, scale integer, Nulls boolean, cDefault string, number integer, sqltype string, remark string) +BEGIN + 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 + FROM sys._tables t, sys.schemas s, sys._columns c + LEFT OUTER JOIN sys.comments com ON c.id = com.id + WHERE c.table_id = t.id + AND t.name = tableName + AND t.schema_id = s.id + AND s.name = schemaName + ORDER BY c.number; +END; +CREATE FUNCTION sys.describe_function(schemaName string, functionName string) + RETURNS TABLE(id integer, name string, type string, language string, remark string) +BEGIN + RETURN SELECT f.id, f.name, ft.function_type_keyword, fl.language_keyword, 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.function_languages fl ON f.language = fl.language_id + LEFT OUTER JOIN sys.comments c ON f.id = c.id + WHERE f.name=functionName AND s.name = schemaName; +END; 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 = 1) or (name in ('describe_table', 'describe_columns', 'describe_function') and type = 5)); commit; set schema "sys"; diff --git a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128 b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128 --- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128 +++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.ppc64.int128 @@ -4350,6 +4350,40 @@ BEGIN END END; END; +CREATE FUNCTION sys.describe_table(schemaName string, tableName string) + RETURNS TABLE(name string, query string, type string, id integer, remark string) +BEGIN + RETURN SELECT t.name, t.query, tt.table_type_name, t.id, c.remark + FROM sys.schemas s, sys.table_types tt, sys._tables t + LEFT OUTER JOIN sys.comments c ON t.id = c.id + WHERE s.name = schemaName + AND t.schema_id = s.id + AND t.name = tableName + AND t.type = tt.table_type_id; +END; +CREATE FUNCTION sys.describe_columns(schemaName string, tableName string) + RETURNS TABLE(name string, type string, digits integer, scale integer, Nulls boolean, cDefault string, number integer, sqltype string, remark string) +BEGIN + 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 + FROM sys._tables t, sys.schemas s, sys._columns c + LEFT OUTER JOIN sys.comments com ON c.id = com.id + WHERE c.table_id = t.id + AND t.name = tableName + AND t.schema_id = s.id + AND s.name = schemaName + ORDER BY c.number; +END; +CREATE FUNCTION sys.describe_function(schemaName string, functionName string) + RETURNS TABLE(id integer, name string, type string, language string, remark string) +BEGIN + RETURN SELECT f.id, f.name, ft.function_type_keyword, fl.language_keyword, 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.function_languages fl ON f.language = fl.language_id + LEFT OUTER JOIN sys.comments c ON f.id = c.id + WHERE f.name=functionName AND s.name = schemaName; +END; 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 = 1) or (name in ('describe_table', 'describe_columns', 'describe_function') and type = 5)); commit; set schema "sys"; diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out --- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out +++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out @@ -3755,6 +3755,40 @@ BEGIN END END; END; +CREATE FUNCTION sys.describe_table(schemaName string, tableName string) + RETURNS TABLE(name string, query string, type string, id integer, remark string) +BEGIN + RETURN SELECT t.name, t.query, tt.table_type_name, t.id, c.remark + FROM sys.schemas s, sys.table_types tt, sys._tables t + LEFT OUTER JOIN sys.comments c ON t.id = c.id + WHERE s.name = schemaName + AND t.schema_id = s.id + AND t.name = tableName + AND t.type = tt.table_type_id; +END; +CREATE FUNCTION sys.describe_columns(schemaName string, tableName string) + RETURNS TABLE(name string, type string, digits integer, scale integer, Nulls boolean, cDefault string, number integer, sqltype string, remark string) +BEGIN + 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 + FROM sys._tables t, sys.schemas s, sys._columns c + LEFT OUTER JOIN sys.comments com ON c.id = com.id + WHERE c.table_id = t.id + AND t.name = tableName + AND t.schema_id = s.id + AND s.name = schemaName + ORDER BY c.number; +END; +CREATE FUNCTION sys.describe_function(schemaName string, functionName string) + RETURNS TABLE(id integer, name string, type string, language string, remark string) +BEGIN + RETURN SELECT f.id, f.name, ft.function_type_keyword, fl.language_keyword, 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.function_languages fl ON f.language = fl.language_id + LEFT OUTER JOIN sys.comments c ON f.id = c.id + WHERE f.name=functionName AND s.name = schemaName; +END; 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 = 1) or (name in ('describe_table', 'describe_columns', 'describe_function') and type = 5)); commit; set schema "sys"; diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit --- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit +++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit @@ -3755,6 +3755,40 @@ BEGIN END END; END; +CREATE FUNCTION sys.describe_table(schemaName string, tableName string) + RETURNS TABLE(name string, query string, type string, id integer, remark string) +BEGIN + RETURN SELECT t.name, t.query, tt.table_type_name, t.id, c.remark + FROM sys.schemas s, sys.table_types tt, sys._tables t + LEFT OUTER JOIN sys.comments c ON t.id = c.id + WHERE s.name = schemaName + AND t.schema_id = s.id + AND t.name = tableName + AND t.type = tt.table_type_id; +END; +CREATE FUNCTION sys.describe_columns(schemaName string, tableName string) + RETURNS TABLE(name string, type string, digits integer, scale integer, Nulls boolean, cDefault string, number integer, sqltype string, remark string) +BEGIN + 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 + FROM sys._tables t, sys.schemas s, sys._columns c + LEFT OUTER JOIN sys.comments com ON c.id = com.id + WHERE c.table_id = t.id + AND t.name = tableName + AND t.schema_id = s.id + AND s.name = schemaName + ORDER BY c.number; +END; +CREATE FUNCTION sys.describe_function(schemaName string, functionName string) + RETURNS TABLE(id integer, name string, type string, language string, remark string) +BEGIN + RETURN SELECT f.id, f.name, ft.function_type_keyword, fl.language_keyword, 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.function_languages fl ON f.language = fl.language_id + LEFT OUTER JOIN sys.comments c ON f.id = c.id + WHERE f.name=functionName AND s.name = schemaName; +END; 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 = 1) or (name in ('describe_table', 'describe_columns', 'describe_function') and type = 5)); commit; set schema "sys"; diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128 b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128 --- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128 +++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128 @@ -4424,6 +4424,40 @@ BEGIN END END; END; +CREATE FUNCTION sys.describe_table(schemaName string, tableName string) + RETURNS TABLE(name string, query string, type string, id integer, remark string) +BEGIN + RETURN SELECT t.name, t.query, tt.table_type_name, t.id, c.remark + FROM sys.schemas s, sys.table_types tt, sys._tables t + LEFT OUTER JOIN sys.comments c ON t.id = c.id + WHERE s.name = schemaName + AND t.schema_id = s.id + AND t.name = tableName + AND t.type = tt.table_type_id; +END; +CREATE FUNCTION sys.describe_columns(schemaName string, tableName string) + RETURNS TABLE(name string, type string, digits integer, scale integer, Nulls boolean, cDefault string, number integer, sqltype string, remark string) +BEGIN + 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 + FROM sys._tables t, sys.schemas s, sys._columns c + LEFT OUTER JOIN sys.comments com ON c.id = com.id + WHERE c.table_id = t.id + AND t.name = tableName + AND t.schema_id = s.id + AND s.name = schemaName + ORDER BY c.number; +END; +CREATE FUNCTION sys.describe_function(schemaName string, functionName string) + RETURNS TABLE(id integer, name string, type string, language string, remark string) +BEGIN + RETURN SELECT f.id, f.name, ft.function_type_keyword, fl.language_keyword, 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.function_languages fl ON f.language = fl.language_id + LEFT OUTER JOIN sys.comments c ON f.id = c.id + WHERE f.name=functionName AND s.name = schemaName; +END; 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 = 1) or (name in ('describe_table', 'describe_columns', 'describe_function') and type = 5)); commit; set schema "sys"; diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64 b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64 --- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64 +++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.ppc64 @@ -3755,6 +3755,40 @@ BEGIN END END; END; +CREATE FUNCTION sys.describe_table(schemaName string, tableName string) + RETURNS TABLE(name string, query string, type string, id integer, remark string) +BEGIN + RETURN SELECT t.name, t.query, tt.table_type_name, t.id, c.remark + FROM sys.schemas s, sys.table_types tt, sys._tables t + LEFT OUTER JOIN sys.comments c ON t.id = c.id + WHERE s.name = schemaName + AND t.schema_id = s.id + AND t.name = tableName + AND t.type = tt.table_type_id; +END; +CREATE FUNCTION sys.describe_columns(schemaName string, tableName string) + RETURNS TABLE(name string, type string, digits integer, scale integer, Nulls boolean, cDefault string, number integer, sqltype string, remark string) +BEGIN + 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 + FROM sys._tables t, sys.schemas s, sys._columns c + LEFT OUTER JOIN sys.comments com ON c.id = com.id + WHERE c.table_id = t.id + AND t.name = tableName + AND t.schema_id = s.id + AND s.name = schemaName + ORDER BY c.number; +END; +CREATE FUNCTION sys.describe_function(schemaName string, functionName string) + RETURNS TABLE(id integer, name string, type string, language string, remark string) +BEGIN + RETURN SELECT f.id, f.name, ft.function_type_keyword, fl.language_keyword, c.remark + FROM sys.functions f + JOIN sys.schemas s ON f.schema_id = s.id _______________________________________________ checkin-list mailing list checkin-list@monetdb.org https://www.monetdb.org/mailman/listinfo/checkin-list