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
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
