Changeset: 692339075527 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/692339075527
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
Branch: default
Log Message:
Merge with Aug2024 branch.
diffs (truncated from 2982 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
@@ -240,7 +240,9 @@ sql_update_hugeint(Client c, mvc *sql)
"external name generator.series;\n"
"create function sys.generate_series(first hugeint,
\"limit\" hugeint, stepsize hugeint)\n"
"returns table (value hugeint)\n"
- "external name generator.series;\n");
+ "external name generator.series;\n"
+ "grant execute on function sys.generate_series(hugeint,
hugeint) to public;\n"
+ "grant execute on function sys.generate_series(hugeint,
hugeint, hugeint) to public;\n");
/* 39_analytics_hge.sql */
pos += snprintf(buf + pos, bufsize - pos,
@@ -297,7 +299,19 @@ sql_update_hugeint(Client c, mvc *sql)
"GRANT EXECUTE ON AGGREGATE corr(HUGEINT, HUGEINT) TO
PUBLIC;\n"
"create window corr(e1 HUGEINT, e2 HUGEINT) returns
DOUBLE\n"
" external name \"sql\".\"corr\";\n"
- "GRANT EXECUTE ON WINDOW corr(HUGEINT, HUGEINT) TO
PUBLIC;\n");
+ "GRANT EXECUTE ON WINDOW corr(HUGEINT, HUGEINT) TO
PUBLIC;\n"
+ "create aggregate median(val DECIMAL(38)) returns
DECIMAL(38)\n"
+ " external name \"aggr\".\"median\";\n"
+ "GRANT EXECUTE ON AGGREGATE median(DECIMAL(38)) TO
PUBLIC;\n"
+ "create aggregate median_avg(val DECIMAL(38)) returns
DOUBLE\n"
+ " external name \"aggr\".\"median_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE median_avg(DECIMAL(38)) TO
PUBLIC;\n"
+ "create aggregate quantile(val DECIMAL(38), q DOUBLE)
returns DECIMAL(38)\n"
+ " external name \"aggr\".\"quantile\";\n"
+ "GRANT EXECUTE ON AGGREGATE quantile(DECIMAL(38),
DOUBLE) TO PUBLIC;\n"
+ "create aggregate quantile_avg(val DECIMAL(38), q
DOUBLE) returns DOUBLE\n"
+ " external name \"aggr\".\"quantile_avg\";\n"
+ "GRANT EXECUTE ON AGGREGATE quantile_avg(DECIMAL(38),
DOUBLE) TO PUBLIC;\n");
/* 40_json_hge.sql */
pos += snprintf(buf + pos, bufsize - pos,
@@ -306,9 +320,9 @@ sql_update_hugeint(Client c, mvc *sql)
"GRANT EXECUTE ON FUNCTION json.filter(json, hugeint)
TO PUBLIC;\n");
pos += snprintf(buf + pos, bufsize - pos,
- "update sys.functions set system = true where system <>
true and name in ('generate_series') and schema_id = (select id from
sys.schemas where name = 'sys') and type = %d;\n"
- "update sys.functions set system = true where system <>
true and name in ('stddev_samp', 'stddev_pop', 'var_samp', 'covar_samp',
'var_pop', 'covar_pop', 'median', 'median_avg', 'quantile', 'quantile_avg',
'corr') and schema_id = (select id from sys.schemas where name = 'sys') and
type = %d;\n"
- "update sys.functions set system = true where system <>
true and name in ('stddev_samp', 'stddev_pop', 'var_samp', 'covar_samp',
'var_pop', 'covar_pop', 'corr') and schema_id = (select id from sys.schemas
where name = 'sys') and type = %d;\n"
+ "update sys.functions set system = true where system <>
true and name in ('generate_series') and schema_id = 2000 and type = %d;\n"
+ "update sys.functions set system = true where system <>
true and name in ('stddev_samp', 'stddev_pop', 'var_samp', 'covar_samp',
'var_pop', 'covar_pop', 'median', 'median_avg', 'quantile', 'quantile_avg',
'corr') and schema_id = 2000 and type = %d;\n"
+ "update sys.functions set system = true where system <>
true and name in ('stddev_samp', 'stddev_pop', 'var_samp', 'covar_samp',
'var_pop', 'covar_pop', 'corr') and schema_id = 2000 and type = %d;\n"
"update sys.functions set system = true where system <>
true and name = 'filter' and schema_id = (select id from sys.schemas where name
= 'json') and type = %d;\n",
(int) F_UNION, (int) F_AGGR, (int) F_ANALYTIC, (int)
F_FUNC);
@@ -349,1803 +363,6 @@ sql_drop_shp(Client c)
}
static str
-sql_update_generator(Client c)
-{
- const char query[] = "update sys.args set name = 'limit' where name =
'last' and func_id in (select id from sys.functions where schema_id = 2000 and
name = 'generate_series' and func like '% last %');\n"
- "update sys.functions set func = replace(func, ' last ', '
\"limit\" ') where schema_id = 2000 and name = 'generate_series' and func like
'% last %';\n";
- return SQLstatementIntern(c, query, "update", true, false, NULL);
-}
-
-static str
-sql_drop_functions_dependencies_Xs_on_Ys(Client c)
-{
- size_t bufsize = 1600, pos = 0;
- char *err = NULL, *buf = GDKmalloc(bufsize);
-
- if (buf == NULL)
- throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
-
- /* remove functions which were created in
sql/scripts/21_dependency_functions.sql */
- pos += snprintf(buf + pos, bufsize - pos,
- "DROP FUNCTION dependencies_schemas_on_users()
CASCADE;\n"
- "DROP FUNCTION dependencies_owners_on_schemas()
CASCADE;\n"
- "DROP FUNCTION dependencies_tables_on_views()
CASCADE;\n"
- "DROP FUNCTION dependencies_tables_on_indexes()
CASCADE;\n"
- "DROP FUNCTION dependencies_tables_on_triggers()
CASCADE;\n"
- "DROP FUNCTION dependencies_tables_on_foreignKeys()
CASCADE;\n"
- "DROP FUNCTION dependencies_tables_on_functions()
CASCADE;\n"
- "DROP FUNCTION dependencies_columns_on_views()
CASCADE;\n"
- "DROP FUNCTION dependencies_columns_on_keys()
CASCADE;\n"
- "DROP FUNCTION dependencies_columns_on_indexes()
CASCADE;\n"
- "DROP FUNCTION dependencies_columns_on_functions()
CASCADE;\n"
- "DROP FUNCTION dependencies_columns_on_triggers()
CASCADE;\n"
- "DROP FUNCTION dependencies_views_on_functions()
CASCADE;\n"
- "DROP FUNCTION dependencies_views_on_triggers()
CASCADE;\n"
- "DROP FUNCTION dependencies_functions_on_functions()
CASCADE;\n"
- "DROP FUNCTION dependencies_functions_on_triggers()
CASCADE;\n"
- "DROP FUNCTION dependencies_keys_on_foreignKeys()
CASCADE;\n");
-
- assert(pos < bufsize);
-
- printf("Running database upgrade commands:\n%s\n", buf);
- fflush(stdout);
- err = SQLstatementIntern(c, buf, "update", true, false, NULL);
- GDKfree(buf);
- return err; /* usually MAL_SUCCEED */
-}
-
-static str
-sql_update_storagemodel(Client c, mvc *sql, bool oct2020_upgrade)
-{
- size_t bufsize = 20000, pos = 0;
- char *buf, *err;
- sql_schema *s = mvc_bind_schema(sql, "sys");
- sql_table *t;
- char *day_interval_str = oct2020_upgrade ? " 'day_interval'," : "";
-
- if ((buf = GDKmalloc(bufsize)) == NULL)
- throw(SQL, __func__, SQLSTATE(HY013) MAL_MALLOC_FAIL);
-
- /* set views and tables internally to non-system to allow drop commands
to succeed without error */
- if ((t = mvc_bind_table(sql, s, "storage")) != NULL)
- t->system = 0;
- if ((t = mvc_bind_table(sql, s, "storagemodel")) != NULL)
- t->system = 0;
- if ((t = mvc_bind_table(sql, s, "storagemodelinput")) != NULL)
- t->system = 0;
- if ((t = mvc_bind_table(sql, s, "tablestoragemodel")) != NULL)
- t->system = 0;
-
- /* new 75_storagemodel.sql */
- pos += snprintf(buf + pos, bufsize - pos,
- /* drop objects in reverse order of original creation of old
75_storagemodel.sql */
- "drop view if exists sys.tablestoragemodel cascade;\n"
- "drop view if exists sys.storagemodel cascade;\n"
- "drop function if exists sys.storagemodel() cascade;\n");
-
- if (oct2020_upgrade) {
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function if exists sys.imprintsize(varchar(1024),
bigint) cascade;\n");
- } else {
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function if exists sys.imprintsize(bigint, clob)
cascade;\n");
- }
-
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function if exists sys.hashsize(boolean, bigint)
cascade;\n");
-
- if (oct2020_upgrade) {
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function if exists sys.columnsize(varchar(1024),
bigint) cascade;\n"
- "drop function if exists sys.heapsize(varchar(1024),
bigint, bigint, int) cascade;\n");
- } else {
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function if exists sys.columnsize(clob, bigint,
bigint) cascade;\n"
- "drop function if exists sys.heapsize(clob, bigint,
int) cascade;\n");
- }
-
- pos += snprintf(buf + pos, bufsize - pos,
- "drop procedure if exists sys.storagemodelinit() cascade;\n"
- "drop table if exists sys.storagemodelinput cascade;\n"
- "drop view if exists sys.\"storage\" cascade;\n");
-
- if (oct2020_upgrade) {
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function if exists sys.\"storage\"(varchar(1024),
varchar(1024), varchar(1024)) cascade;\n"
- "drop function if exists sys.\"storage\"(varchar(1024),
varchar(1024)) cascade;\n"
- "drop function if exists sys.\"storage\"(varchar(1024))
cascade;\n");
- } else {
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function if exists sys.\"storage\"(clob, clob,
clob) cascade;\n"
- "drop function if exists sys.\"storage\"(clob, clob)
cascade;\n"
- "drop function if exists sys.\"storage\"(clob)
cascade;\n");
- }
-
- /* new 75_storagemodel.sql */
- pos += snprintf(buf + pos, bufsize - pos,
- "drop function if exists sys.\"storage\"() cascade;\n"
- "create function sys.\"storage\"()\n"
- "returns table (\n"
- " \"schema\" varchar(1024),\n"
- " \"table\" varchar(1024),\n"
- " \"column\" varchar(1024),\n"
- " \"type\" varchar(1024),\n"
- " \"mode\" varchar(15),\n"
- " location varchar(1024),\n"
- " \"count\" bigint,\n"
- " typewidth int,\n"
- " columnsize bigint,\n"
- " heapsize bigint,\n"
- " hashes bigint,\n"
- " phash boolean,\n"
- " \"imprints\" bigint,\n"
- " sorted boolean,\n"
- " revsorted boolean,\n"
- " \"unique\" boolean,\n"
- " orderidx bigint\n"
- ")\n"
- "external name sql.\"storage\";\n"
- "create view sys.\"storage\" as\n"
- "select * from sys.\"storage\"()\n"
- " where (\"schema\", \"table\") in (\n"
- " SELECT sch.\"name\", tbl.\"name\"\n"
- " FROM sys.\"tables\" AS tbl JOIN sys.\"schemas\" AS
sch ON tbl.schema_id = sch.id\n"
- " WHERE tbl.\"system\" = FALSE)\n"
- "order by \"schema\", \"table\", \"column\";\n"
- "create view sys.\"tablestorage\" as\n"
- "select \"schema\", \"table\",\n"
- " max(\"count\") as \"rowcount\",\n"
- " count(*) as \"storages\",\n"
- " sum(columnsize) as columnsize,\n"
- " sum(heapsize) as heapsize,\n"
- " sum(hashes) as hashsize,\n"
- " sum(\"imprints\") as imprintsize,\n"
- " sum(orderidx) as orderidxsize\n"
- " from sys.\"storage\"\n"
- "group by \"schema\", \"table\"\n"
- "order by \"schema\", \"table\";\n"
- "create view sys.\"schemastorage\" as\n"
- "select \"schema\",\n"
- " count(*) as \"storages\",\n"
- " sum(columnsize) as columnsize,\n"
- " sum(heapsize) as heapsize,\n"
- " sum(hashes) as hashsize,\n"
- " sum(\"imprints\") as imprintsize,\n"
- " sum(orderidx) as orderidxsize\n"
- " from sys.\"storage\"\n"
- "group by \"schema\"\n"
- "order by \"schema\";\n"
- "create function sys.\"storage\"(sname varchar(1024))\n"
- "returns table (\n"
- " \"schema\" varchar(1024),\n"
- " \"table\" varchar(1024),\n"
- " \"column\" varchar(1024),\n"
- " \"type\" varchar(1024),\n"
- " \"mode\" varchar(15),\n"
- " location varchar(1024),\n"
- " \"count\" bigint,\n"
- " typewidth int,\n"
- " columnsize bigint,\n"
- " heapsize bigint,\n"
- " hashes bigint,\n"
- " phash boolean,\n"
- " \"imprints\" bigint,\n"
- " sorted boolean,\n"
- " revsorted boolean,\n"
- " \"unique\" boolean,\n"
- " orderidx bigint\n"
- ")\n"
- "external name sql.\"storage\";\n"
- "create function sys.\"storage\"(sname varchar(1024), tname
varchar(1024))\n"
- "returns table (\n"
- " \"schema\" varchar(1024),\n"
- " \"table\" varchar(1024),\n"
- " \"column\" varchar(1024),\n"
- " \"type\" varchar(1024),\n"
- " \"mode\" varchar(15),\n"
- " location varchar(1024),\n"
- " \"count\" bigint,\n"
- " typewidth int,\n"
- " columnsize bigint,\n"
- " heapsize bigint,\n"
- " hashes bigint,\n"
- " phash boolean,\n"
- " \"imprints\" bigint,\n"
- " sorted boolean,\n"
- " revsorted boolean,\n"
- " \"unique\" boolean,\n"
- " orderidx bigint\n"
- ")\n"
- "external name sql.\"storage\";\n"
- "create function sys.\"storage\"(sname varchar(1024), tname
varchar(1024), cname varchar(1024))\n"
- "returns table (\n"
- " \"schema\" varchar(1024),\n"
- " \"table\" varchar(1024),\n"
- " \"column\" varchar(1024),\n"
- " \"type\" varchar(1024),\n"
- " \"mode\" varchar(15),\n"
- " location varchar(1024),\n"
- " \"count\" bigint,\n"
- " typewidth int,\n"
- " columnsize bigint,\n"
- " heapsize bigint,\n"
- " hashes bigint,\n"
- " phash boolean,\n"
- " \"imprints\" bigint,\n"
- " sorted boolean,\n"
- " revsorted boolean,\n"
- " \"unique\" boolean,\n"
- " orderidx bigint\n"
- ")\n"
- "external name sql.\"storage\";\n"
- "create table sys.storagemodelinput(\n"
- " \"schema\" varchar(1024) NOT NULL,\n"
- " \"table\" varchar(1024) NOT NULL,\n"
- " \"column\" varchar(1024) NOT NULL,\n"
- " \"type\" varchar(1024) NOT NULL,\n"
- " typewidth int NOT NULL,\n"
- " \"count\" bigint NOT NULL,\n"
- " \"distinct\" bigint NOT NULL,\n"
- " atomwidth int NOT NULL,\n"
- " reference boolean NOT NULL DEFAULT FALSE,\n"
- " sorted boolean,\n"
- " \"unique\" boolean,\n"
- " isacolumn boolean NOT NULL DEFAULT TRUE\n"
- ");\n"
- "create procedure sys.storagemodelinit()\n"
- "begin\n"
- " delete from sys.storagemodelinput;\n"
- " insert into sys.storagemodelinput\n"
- " select \"schema\", \"table\", \"column\", \"type\",
typewidth, \"count\",\n"
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
