Changeset: be895ad56040 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=be895ad56040
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.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:
Extended SQL upgrade program for SQL changes made in script 75_storagemodel.sql
This will automatically migrate an Aug2018 database to new (2019) database.
diffs (truncated from 4054 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
@@ -1811,6 +1811,291 @@ sql_drop_functions_dependencies_Xs_on_Ys
return err; /* usually MAL_SUCCEED */
}
+static str
+sql_upgrade_2019_storagemodel(Client c, mvc *sql)
+{
+ size_t bufsize = 20000, pos = 0;
+ char *buf, *err;
+ char *schema;
+ sql_schema *s;
+ sql_table *t;
+
+ if ((buf = GDKmalloc(bufsize)) == NULL)
+ throw(SQL, "sql_upgrade_2019_storagemodel", SQLSTATE(HY001)
MAL_MALLOC_FAIL);
+
+ schema = stack_get_string(sql, "current_schema");
+
+ s = mvc_bind_schema(sql, "sys");
+ /* 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;
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "set schema sys;\n"
+ /* drop objects in reverse order of original creation of old
75_storagemodel.sql */
+ "drop view if exists sys.tablestoragemodel;\n"
+ "drop view if exists sys.storagemodel cascade;\n"
+ "drop function if exists sys.storagemodel() cascade;\n"
+ "drop function if exists sys.imprintsize(bigint, clob)
cascade;\n"
+ "drop function if exists sys.hashsize(boolean, bigint)
cascade;\n"
+ "drop function if exists sys.heapsize(clob, bigint, int)
cascade;\n"
+ "drop function if exists sys.columnsize(clob, bigint, bigint)
cascade;\n"
+ "drop procedure if exists sys.storagemodelinit();\n"
+ "drop table if exists sys.storagemodelinput cascade;\n"
+ "drop view if exists sys.\"storage\" cascade;\n"
+ "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"
+ "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"
+ );
+ assert(pos < bufsize);
+
+ pos += snprintf(buf + pos, bufsize - pos,
+ "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');\n");
+ pos += snprintf(buf + pos, bufsize - pos,
+ "update sys.functions set system = true where schema_id =
(select id from sys.schemas where name = 'sys')"
+ " and name in ('storage') and type = %d;\n", F_UNION);
+ pos += snprintf(buf + pos, bufsize - pos,
+ "update sys.functions set system = true where schema_id =
(select id from sys.schemas where name = 'sys')"
+ " and name in ('storagemodelinit') and type = %d;\n", F_PROC);
+ pos += snprintf(buf + pos, bufsize - pos,
+ "update sys.functions set system = true where schema_id =
(select id from sys.schemas where name = 'sys')"
+ " and name in ('columnsize', 'heapsize', 'hashsize',
'imprintsize') and type = %d;\n", F_FUNC);
+
+ if (schema)
+ pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
+ pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
+ assert(pos < bufsize);
+
+ printf("Running database upgrade commands:\n%s\n", buf);
+ err = SQLstatementIntern(c, &buf, "update", 1, 0, NULL);
+ GDKfree(buf);
+ return err; /* usually MAL_SUCCEED */
+}
+
void
SQLupgrades(Client c, mvc *m)
{
@@ -2019,4 +2304,14 @@ SQLupgrades(Client c, mvc *m)
freeException(err);
}
}
+
_______________________________________________
checkin-list mailing list
[email protected]
https://www.monetdb.org/mailman/listinfo/checkin-list
