Changeset: 5104fbfac107 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=5104fbfac107
Modified Files:
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: Oct2020
Log Message:
Approvals.
diffs (truncated from 5599 to 300 lines):
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
@@ -4415,6 +4415,282 @@ create aggregate quantile(val INTERVAL D
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(INTERVAL DAY, DOUBLE) TO PUBLIC;
update sys.functions set system = true where system <> true and name in
('median', 'quantile') and schema_id = (select id from sys.schemas where name =
'sys') and type = 3;
+create function sys.generate_series(first timestamp, "limit" timestamp,
stepsize interval day) returns table (value timestamp)
+ external name "generator"."series";
+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 = 5;
+set schema "sys";
+
+Running database upgrade commands:
+set schema sys;
+drop view if exists sys.tablestoragemodel;
+drop view if exists sys.storagemodel cascade;
+drop function if exists sys.storagemodel() cascade;
+drop function if exists sys.imprintsize(varchar(1024), bigint) cascade;
+drop function if exists sys.hashsize(boolean, bigint) cascade;
+drop function if exists sys.columnsize(varchar(1024), bigint) cascade;
+drop function if exists sys.heapsize(varchar(1024), bigint, bigint, int)
cascade;
+drop procedure if exists sys.storagemodelinit();
+drop table if exists sys.storagemodelinput cascade;
+drop view if exists sys."storage" cascade;
+drop function if exists sys."storage"(varchar(1024), varchar(1024),
varchar(1024)) cascade;
+drop function if exists sys."storage"(varchar(1024), varchar(1024)) cascade;
+drop function if exists sys."storage"(varchar(1024)) cascade;
+drop function if exists sys."storage"() cascade;
+create function sys."storage"()
+returns table (
+ "schema" varchar(1024),
+ "table" varchar(1024),
+ "column" varchar(1024),
+ "type" varchar(1024),
+ "mode" varchar(15),
+ location varchar(1024),
+ "count" bigint,
+ typewidth int,
+ columnsize bigint,
+ heapsize bigint,
+ hashes bigint,
+ phash boolean,
+ "imprints" bigint,
+ sorted boolean,
+ revsorted boolean,
+ "unique" boolean,
+ orderidx bigint
+)
+external name sql."storage";
+create view sys."storage" as
+select * from sys."storage"()
+ where ("schema", "table") in (
+ SELECT sch."name", tbl."name"
+ FROM sys."tables" AS tbl JOIN sys."schemas" AS sch ON tbl.schema_id =
sch.id
+ WHERE tbl."system" = FALSE)
+order by "schema", "table", "column";
+create view sys."tablestorage" as
+select "schema", "table",
+ max("count") as "rowcount",
+ count(*) as "storages",
+ sum(columnsize) as columnsize,
+ sum(heapsize) as heapsize,
+ sum(hashes) as hashsize,
+ sum("imprints") as imprintsize,
+ sum(orderidx) as orderidxsize
+ from sys."storage"
+group by "schema", "table"
+order by "schema", "table";
+create view sys."schemastorage" as
+select "schema",
+ count(*) as "storages",
+ sum(columnsize) as columnsize,
+ sum(heapsize) as heapsize,
+ sum(hashes) as hashsize,
+ sum("imprints") as imprintsize,
+ sum(orderidx) as orderidxsize
+ from sys."storage"
+group by "schema"
+order by "schema";
+create function sys."storage"(sname varchar(1024))
+returns table (
+ "schema" varchar(1024),
+ "table" varchar(1024),
+ "column" varchar(1024),
+ "type" varchar(1024),
+ "mode" varchar(15),
+ location varchar(1024),
+ "count" bigint,
+ typewidth int,
+ columnsize bigint,
+ heapsize bigint,
+ hashes bigint,
+ phash boolean,
+ "imprints" bigint,
+ sorted boolean,
+ revsorted boolean,
+ "unique" boolean,
+ orderidx bigint
+)
+external name sql."storage";
+create function sys."storage"(sname varchar(1024), tname varchar(1024))
+returns table (
+ "schema" varchar(1024),
+ "table" varchar(1024),
+ "column" varchar(1024),
+ "type" varchar(1024),
+ "mode" varchar(15),
+ location varchar(1024),
+ "count" bigint,
+ typewidth int,
+ columnsize bigint,
+ heapsize bigint,
+ hashes bigint,
+ phash boolean,
+ "imprints" bigint,
+ sorted boolean,
+ revsorted boolean,
+ "unique" boolean,
+ orderidx bigint
+)
+external name sql."storage";
+create function sys."storage"(sname varchar(1024), tname varchar(1024), cname
varchar(1024))
+returns table (
+ "schema" varchar(1024),
+ "table" varchar(1024),
+ "column" varchar(1024),
+ "type" varchar(1024),
+ "mode" varchar(15),
+ location varchar(1024),
+ "count" bigint,
+ typewidth int,
+ columnsize bigint,
+ heapsize bigint,
+ hashes bigint,
+ phash boolean,
+ "imprints" bigint,
+ sorted boolean,
+ revsorted boolean,
+ "unique" boolean,
+ orderidx bigint
+)
+external name sql."storage";
+create table sys.storagemodelinput(
+ "schema" varchar(1024) NOT NULL,
+ "table" varchar(1024) NOT NULL,
+ "column" varchar(1024) NOT NULL,
+ "type" varchar(1024) NOT NULL,
+ typewidth int NOT NULL,
+ "count" bigint NOT NULL,
+ "distinct" bigint NOT NULL,
+ atomwidth int NOT NULL,
+ reference boolean NOT NULL DEFAULT FALSE,
+ sorted boolean,
+ "unique" boolean,
+ isacolumn boolean NOT NULL DEFAULT TRUE
+);
+create procedure sys.storagemodelinit()
+begin
+ delete from sys.storagemodelinput;
+ insert into sys.storagemodelinput
+ select "schema", "table", "column", "type", typewidth, "count",
+ case when ("unique" or "type" IN ('varchar', 'char', 'clob',
'json', 'url', 'blob', 'geometry', 'geometrya'))
+ then "count" else 0 end,
+ case when "count" > 0 and heapsize >= 8192 and "type" in
('varchar', 'char', 'clob', 'json', 'url')
+ then cast((heapsize - 8192) / "count" as bigint)
+ when "count" > 0 and heapsize >= 32 and "type" in ('blob',
'geometry', 'geometrya')
+ then cast((heapsize - 32) / "count" as bigint)
+ else typewidth end,
+ FALSE, case sorted when true then true else false end,
"unique", TRUE
+ from sys."storage";
+ update sys.storagemodelinput
+ set reference = TRUE
+ where ("schema", "table", "column") in (
+ SELECT fkschema."name", fktable."name", fkkeycol."name"
+ FROM sys."keys" AS fkkey,
+ sys."objects" AS fkkeycol,
+ sys."tables" AS fktable,
+ sys."schemas" AS fkschema
+ WHERE fktable."id" = fkkey."table_id"
+ AND fkkey."id" = fkkeycol."id"
+ AND fkschema."id" = fktable."schema_id"
+ AND fkkey."rkey" > -1 );
+ update sys.storagemodelinput
+ set isacolumn = FALSE
+ where ("schema", "table", "column") NOT in (
+ SELECT sch."name", tbl."name", col."name"
+ FROM sys."schemas" AS sch,
+ sys."tables" AS tbl,
+ sys."columns" AS col
+ WHERE sch."id" = tbl."schema_id"
+ AND tbl."id" = col."table_id");
+end;
+create function sys.columnsize(tpe varchar(1024), count bigint)
+returns bigint
+begin
+ if tpe in ('tinyint', 'boolean')
+ then return count;
+ end if;
+ if tpe = 'smallint'
+ then return 2 * count;
+ end if;
+ if tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'day_interval', 'month_interval')
+ then return 4 * count;
+ end if;
+ if tpe in ('bigint', 'double', 'timestamp', 'timestamptz', 'inet',
'oid')
+ then return 8 * count;
+ end if;
+ if tpe in ('hugeint', 'decimal', 'uuid', 'mbr')
+ then return 16 * count;
+ end if;
+ if tpe in ('varchar', 'char', 'clob', 'json', 'url')
+ then return 4 * count;
+ end if;
+ if tpe in ('blob', 'geometry', 'geometrya')
+ then return 8 * count;
+ end if;
+ return 8 * count;
+end;
+create function sys.heapsize(tpe varchar(1024), count bigint, distincts
bigint, avgwidth int)
+returns bigint
+begin
+ if tpe in ('varchar', 'char', 'clob', 'json', 'url')
+ then return 8192 + ((avgwidth + 8) * distincts);
+ end if;
+ if tpe in ('blob', 'geometry', 'geometrya')
+ then return 32 + (avgwidth * count);
+ end if;
+ return 0;
+end;
+create function sys.hashsize(b boolean, count bigint)
+returns bigint
+begin
+ if b = true
+ then return 8 * count;
+ end if;
+ return 0;
+end;
+create function sys.imprintsize(tpe varchar(1024), count bigint)
+returns bigint
+begin
+ if tpe in ('tinyint', 'boolean')
+ then return cast(0.2 * count as bigint);
+ end if;
+ if tpe = 'smallint'
+ then return cast(0.4 * count as bigint);
+ end if;
+ if tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'day_interval', 'month_interval')
+ then return cast(0.8 * count as bigint);
+ end if;
+ if tpe in ('bigint', 'double', 'timestamp', 'timestamptz', 'inet',
'oid')
+ then return cast(1.6 * count as bigint);
+ end if;
+ if tpe in ('hugeint', 'decimal', 'uuid', 'mbr')
+ then return cast(3.2 * count as bigint);
+ end if;
+ return 0;
+end;
+create view sys.storagemodel as
+select "schema", "table", "column", "type", "count",
+ sys.columnsize("type", "count") as columnsize,
+ sys.heapsize("type", "count", "distinct", "atomwidth") as heapsize,
+ sys.hashsize("reference", "count") as hashsize,
+ case when isacolumn then sys.imprintsize("type", "count") else 0 end as
imprintsize,
+ case when (isacolumn and not sorted) then cast(8 * "count" as bigint)
else 0 end as orderidxsize,
+ sorted, "unique", isacolumn
+ from sys.storagemodelinput
+order by "schema", "table", "column";
+create view sys.tablestoragemodel as
+select "schema", "table",
+ max("count") as "rowcount",
+ count(*) as "storages",
+ sum(sys.columnsize("type", "count")) as columnsize,
+ sum(sys.heapsize("type", "count", "distinct", "atomwidth")) as heapsize,
+ sum(sys.hashsize("reference", "count")) as hashsize,
+ sum(case when isacolumn then sys.imprintsize("type", "count") else 0
end) as imprintsize,
+ sum(case when (isacolumn and not sorted) then cast(8 * "count" as
bigint) else 0 end) as orderidxsize
+ from sys.storagemodelinput
+group by "schema", "table"
+order by "schema", "table";
+update sys._tables set system = true where schema_id = (select id from
sys.schemas where name = 'sys') and name in ('storage', 'tablestorage',
'schemastorage', 'storagemodelinput', 'storagemodel', 'tablestoragemodel');
+update sys.functions set system = true where system <> true and schema_id =
(select id from sys.schemas where name = 'sys') and name in ('storage') and
type = 5;
+update sys.functions set system = true where system <> true and schema_id =
(select id from sys.schemas where name = 'sys') and name in
('storagemodelinit') and type = 2;
+update sys.functions set system = true where system <> true and schema_id =
(select id from sys.schemas where name = 'sys') and name in ('columnsize',
'heapsize', 'hashsize', 'imprintsize') and type = 1;
set schema "sys";
# MonetDB/SQL module loaded
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
@@ -4415,6 +4415,282 @@ create aggregate quantile(val INTERVAL D
external name "aggr"."quantile";
GRANT EXECUTE ON AGGREGATE quantile(INTERVAL DAY, DOUBLE) TO PUBLIC;
update sys.functions set system = true where system <> true and name in
('median', 'quantile') and schema_id = (select id from sys.schemas where name =
'sys') and type = 3;
+create function sys.generate_series(first timestamp, "limit" timestamp,
stepsize interval day) returns table (value timestamp)
+ external name "generator"."series";
+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 = 5;
+set schema "sys";
+
+Running database upgrade commands:
+set schema sys;
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
