Changeset: 30161c3acd5f for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=30161c3acd5f
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.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.powerpc64.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
sql/test/testdb-upgrade/Tests/upgrade.stable.out.powerpc64.int128
Branch: default
Log Message:
Updated upgrade code so that emptydb/check tests produce fewer diffs.
diffs (truncated from 4736 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
@@ -1749,6 +1749,11 @@ sql_update_default(Client c, mvc *sql)
pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
+ /* 51_sys_schema_extensions.sql */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "ALTER TABLE sys.keywords SET READ WRITE;\n"
+ "INSERT INTO sys.keywords VALUES ('WINDOW');\n"
+ );
/* 99_system.sql */
t = mvc_bind_table(sql, s, "systemfunctions");
t->system = 0;
@@ -1757,7 +1762,6 @@ sql_update_default(Client c, mvc *sql)
"create view sys.systemfunctions as select id as
function_id from sys.functions where system;\n"
"grant select on sys.systemfunctions to public;\n"
"update sys._tables set system = true where name =
'systemfunctions' and schema_id = (select id from sys.schemas where name =
'sys');\n");
-
if (schema)
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
@@ -1765,6 +1769,16 @@ sql_update_default(Client c, mvc *sql)
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
+ if (err == MAL_SUCCEED) {
+ schema = stack_get_string(sql, "current_schema");
+ pos = snprintf(buf, bufsize, "set schema \"sys\";\n"
+ "ALTER TABLE sys.keywords SET READ ONLY;\n");
+ if (schema)
+ pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
+ pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
+ printf("Running database upgrade commands:\n%s\n", buf);
+ err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
+ }
GDKfree(buf);
return err; /* usually MAL_SUCCEED */
@@ -1836,6 +1850,7 @@ sql_upgrade_2019_storagemodel(Client c,
if ((t = mvc_bind_table(sql, s, "tablestoragemodel")) != NULL)
t->system = 0;
+ /* new 75_storagemodel.sql */
pos += snprintf(buf + pos, bufsize - pos,
"set schema sys;\n"
/* drop objects in reverse order of original creation of old
75_storagemodel.sql */
@@ -1853,222 +1868,257 @@ sql_upgrade_2019_storagemodel(Client c,
"drop function if exists sys.\"storage\"(clob, clob) cascade;\n"
"drop function if exists sys.\"storage\"(clob) cascade;\n"
"drop function if exists sys.\"storage\"() cascade;\n"
-/* new 75_storagemodel.sql */
-"create function sys.\"storage\"()\n"
-"returns table (\"schema\" varchar(1024),\"table\" varchar(1024),\"column\"
varchar(1024),\"type\" varchar(1024),\"mode\" varchar(15),location
varchar(1024),\"count\" bigint,\n"
-" typewidth int,columnsize bigint,heapsize bigint,hashes bigint,phash
boolean,\"imprints\" bigint,sorted boolean,revsorted boolean,\"unique\"
boolean,orderidx bigint)\n"
-"external name sql.\"storage\";\n"
-
-"create view sys.\"storage\" as\n"
-"select * from sys.\"storage\"()\n"
-"-- exclude system tables\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 (\"schema\" varchar(1024),\"table\" varchar(1024),\"column\"
varchar(1024),\"type\" varchar(1024),\"mode\" varchar(15),location
varchar(1024),\"count\" bigint,\n"
-" typewidth int,columnsize bigint,heapsize bigint,hashes bigint,phash
boolean,\"imprints\" bigint,sorted boolean,revsorted boolean,\"unique\"
boolean,orderidx bigint)\n"
-"external name sql.\"storage\";\n"
-
-"create function sys.\"storage\"(sname varchar(1024), tname varchar(1024))\n"
-"returns table (\"schema\" varchar(1024),\"table\" varchar(1024),\"column\"
varchar(1024),\"type\" varchar(1024),\"mode\" varchar(15),location
varchar(1024),\"count\" bigint,\n"
-" typewidth int,columnsize bigint,heapsize bigint,hashes bigint,phash
boolean,\"imprints\" bigint,sorted boolean,revsorted boolean,\"unique\"
boolean,orderidx bigint)\n"
-"external name sql.\"storage\";\n"
-
-"create function sys.\"storage\"(sname varchar(1024), tname varchar(1024),
cname varchar(1024))\n"
-"returns table (\"schema\" varchar(1024),\"table\" varchar(1024),\"column\"
varchar(1024),\"type\" varchar(1024),\"mode\" varchar(15),location
varchar(1024),\"count\" bigint,\n"
-" typewidth int,columnsize bigint,heapsize bigint,hashes bigint,phash
boolean,\"imprints\" bigint,sorted boolean,revsorted boolean,\"unique\"
boolean,orderidx bigint)\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, -- name of column or index or
pkey or fkey or unique constraint\n"
-" \"type\" varchar(1024) NOT NULL,\n"
-" typewidth int NOT NULL,\n"
-" \"count\" bigint NOT NULL, -- estimated number of tuples\n"
-" \"distinct\" bigint NOT NULL, -- indication of distinct number of
strings\n"
-" atomwidth int NOT NULL, -- average width of variable size char
or binary strings\n"
-" reference boolean NOT NULL DEFAULT FALSE, -- used as foreign key
reference\n"
-" sorted boolean, -- if set there is no need for an
ordered index\n"
-" \"unique\" boolean, -- are values unique or not\n"
-" isacolumn boolean NOT NULL DEFAULT TRUE\n"
-");\n"
-
-"create procedure sys.storagemodelinit()\n"
-"begin\n"
-" delete from sys.storagemodelinput;\n"
-"\n"
-" insert into sys.storagemodelinput\n"
-" select \"schema\", \"table\", \"column\", \"type\", typewidth,
\"count\",\n"
-" -- assume all variable size types contain distinct values\n"
-" case when (\"unique\" or \"type\" IN ('varchar', 'char',
'clob', 'json', 'url', 'blob', 'geometry', 'geometrya'))\n"
-" then \"count\" else 0 end,\n"
-" case when \"count\" > 0 and heapsize >= 8192 and \"type\" in
('varchar', 'char', 'clob', 'json', 'url')\n"
-" -- string heaps have a header of 8192\n"
-" then cast((heapsize - 8192) / \"count\" as bigint)\n"
-" when \"count\" > 0 and heapsize >= 32 and \"type\" in ('blob',
'geometry', 'geometrya')\n"
-" -- binary data heaps have a header of 32\n"
-" then cast((heapsize - 32) / \"count\" as bigint)\n"
-" else typewidth end,\n"
-" FALSE, case sorted when true then true else false end,
\"unique\", TRUE\n"
-" from sys.\"storage\"; -- view sys.\"storage\" excludes system tables
(as those are not useful to be modeled for storagesize by application users)\n"
-"\n"
-" update sys.storagemodelinput\n"
-" set reference = TRUE\n"
-" where (\"schema\", \"table\", \"column\") in (\n"
-" SELECT fkschema.\"name\", fktable.\"name\", fkkeycol.\"name\"\n"
-" FROM sys.\"keys\" AS fkkey,\n"
-" sys.\"objects\" AS fkkeycol,\n"
-" sys.\"tables\" AS fktable,\n"
-" sys.\"schemas\" AS fkschema\n"
-" WHERE fktable.\"id\" = fkkey.\"table_id\"\n"
-" AND fkkey.\"id\" = fkkeycol.\"id\"\n"
-" AND fkschema.\"id\" = fktable.\"schema_id\"\n"
-" AND fkkey.\"rkey\" > -1 );\n"
-"\n"
-" update sys.storagemodelinput\n"
-" set isacolumn = FALSE\n"
-" where (\"schema\", \"table\", \"column\") NOT in (\n"
-" SELECT sch.\"name\", tbl.\"name\", col.\"name\"\n"
-" FROM sys.\"schemas\" AS sch,\n"
-" sys.\"tables\" AS tbl,\n"
-" sys.\"columns\" AS col\n"
-" WHERE sch.\"id\" = tbl.\"schema_id\"\n"
-" AND tbl.\"id\" = col.\"table_id\");\n"
-"end;\n"
-
-"create function sys.columnsize(tpe varchar(1024), count bigint)\n"
-"returns bigint\n"
-"begin\n"
-" -- for fixed size types: typewidth_inbytes * count\n"
-" if tpe in ('tinyint', 'boolean')\n"
-" then return count;\n"
-" end if;\n"
-" if tpe = 'smallint'\n"
-" then return 2 * count;\n"
-" end if;\n"
-" if tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'month_interval')\n"
-" then return 4 * count;\n"
-" end if;\n"
-" if tpe in ('bigint', 'double', 'timestamp', 'timestamptz', 'inet',
'oid')\n"
-" then return 8 * count;\n"
-" end if;\n"
-" if tpe in ('hugeint', 'decimal', 'uuid', 'mbr')\n"
-" then return 16 * count;\n"
-" end if;\n"
-"\n"
-" -- for variable size types we compute the columnsize as refs (assume 4
bytes each for char strings) to the heap, excluding data in the var heap\n"
-" if tpe in ('varchar', 'char', 'clob', 'json', 'url')\n"
-" then return 4 * count;\n"
-" end if;\n"
-" if tpe in ('blob', 'geometry', 'geometrya')\n"
-" then return 8 * count;\n"
-" end if;\n"
-"\n"
-" return 8 * count;\n"
-"end;\n"
-
-"create function sys.heapsize(tpe varchar(1024), count bigint, distincts
bigint, avgwidth int)\n"
-"returns bigint\n"
-"begin\n"
-" -- only variable size types have a heap\n"
-" if tpe in ('varchar', 'char', 'clob', 'json', 'url')\n"
-" then return 8192 + ((avgwidth + 8) * distincts);\n"
-" end if;\n"
-" if tpe in ('blob', 'geometry', 'geometrya')\n"
-" then return 32 + (avgwidth * count);\n"
-" end if;\n"
-"\n"
-" return 0;\n"
-"end;\n"
-
-"create function sys.hashsize(b boolean, count bigint)\n"
-"returns bigint\n"
-"begin\n"
-" -- assume non-compound keys\n"
-" if b = true\n"
-" then return 8 * count;\n"
-" end if;\n"
-" return 0;\n"
-"end;\n"
-
-"create function sys.imprintsize(tpe varchar(1024), count bigint)\n"
-"returns bigint\n"
-"begin\n"
-" -- for fixed size types: typewidth_inbytes * 0.2 * count\n"
-" if tpe in ('tinyint', 'boolean')\n"
-" then return cast(0.2 * count as bigint);\n"
-" end if;\n"
-" if tpe = 'smallint'\n"
-" then return cast(0.4 * count as bigint);\n"
-" end if;\n"
-" if tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'month_interval')\n"
-" then return cast(0.8 * count as bigint);\n"
-" end if;\n"
-" if tpe in ('bigint', 'double', 'timestamp', 'timestamptz', 'inet',
'oid')\n"
-" then return cast(1.6 * count as bigint);\n"
-" end if;\n"
-" -- a decimal can be mapped to tinyint or smallint or int or bigint or
hugeint depending on precision. For the estimate we assume mapping to
hugeint.\n"
-" if tpe in ('hugeint', 'decimal', 'uuid', 'mbr')\n"
-" then return cast(3.2 * count as bigint);\n"
-" end if;\n"
-"\n"
-" -- imprints are not supported on other types\n"
-" return 0;\n"
-"end;\n"
-
-"create view sys.storagemodel as\n"
-"select \"schema\", \"table\", \"column\", \"type\", \"count\",\n"
-" columnsize(\"type\", \"count\") as columnsize,\n"
-" heapsize(\"type\", \"count\", \"distinct\", \"atomwidth\") as
heapsize,\n"
-" hashsize(\"reference\", \"count\") as hashsize,\n"
-" case when isacolumn then imprintsize(\"type\", \"count\") else 0 end as
imprintsize,\n"
-" case when (isacolumn and not sorted) then cast(8 * \"count\" as bigint)
else 0 end as orderidxsize,\n"
-" sorted, \"unique\", isacolumn\n"
-" from sys.storagemodelinput\n"
-"order by \"schema\", \"table\", \"column\";\n"
-
-"create view sys.tablestoragemodel as\n"
-"select \"schema\", \"table\",\n"
-" max(\"count\") as \"rowcount\",\n"
-" count(*) as \"storages\",\n"
-" sum(columnsize(\"type\", \"count\")) as columnsize,\n"
-" sum(heapsize(\"type\", \"count\", \"distinct\", \"atomwidth\")) as
heapsize,\n"
-" sum(hashsize(\"reference\", \"count\")) as hashsize,\n"
-" sum(case when isacolumn then imprintsize(\"type\", \"count\") else 0
end) as imprintsize,\n"
-" sum(case when (isacolumn and not sorted) then cast(8 * \"count\" as
bigint) else 0 end) as orderidxsize\n"
-" from sys.storagemodelinput\n"
-"group by \"schema\", \"table\"\n"
-"order by \"schema\", \"table\";\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"
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
