Changeset: b4827e72171c for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=b4827e72171c
Modified Files:
sql/backends/monet5/generator/90_generator.sql
sql/backends/monet5/sql_upgrades.c
sql/scripts/75_storagemodel.sql
sql/test/sys-schema/Tests/systemfunctions.stable.out
sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
Branch: Oct2020
Log Message:
Added day_interval type to the list of column and imprint sizes, as well as
generate series function. Updated upgrade code.
diffs (241 lines):
diff --git a/sql/backends/monet5/generator/90_generator.sql
b/sql/backends/monet5/generator/90_generator.sql
--- a/sql/backends/monet5/generator/90_generator.sql
+++ b/sql/backends/monet5/generator/90_generator.sql
@@ -53,3 +53,7 @@ external name generator.series;
create function sys.generate_series(first timestamp, "limit" timestamp,
stepsize interval second)
returns table (value timestamp)
external name generator.series;
+
+create function sys.generate_series(first timestamp, "limit" timestamp,
stepsize interval day)
+returns table (value timestamp)
+external name generator.series;
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
@@ -604,12 +604,13 @@ sql_update_apr2019(Client c, mvc *sql, c
}
static str
-sql_update_storagemodel(Client c, mvc *sql, const char *prev_schema)
+sql_update_storagemodel(Client c, mvc *sql, const char *prev_schema, 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);
@@ -630,17 +631,48 @@ sql_update_storagemodel(Client c, mvc *s
/* 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 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();\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 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"
@@ -815,7 +847,7 @@ sql_update_storagemodel(Client c, mvc *s
" 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"
+ " if tpe in ('int', 'real', 'date', 'time', 'timetz',
'sec_interval',%s 'month_interval')\n"
" then return 4 * count;\n"
" end if;\n"
" if tpe in ('bigint', 'double', 'timestamp',
'timestamptz', 'inet', 'oid')\n"
@@ -860,7 +892,7 @@ sql_update_storagemodel(Client c, mvc *s
" 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"
+ " if tpe in ('int', 'real', 'date', 'time', 'timetz',
'sec_interval',%s '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"
@@ -892,8 +924,7 @@ sql_update_storagemodel(Client c, mvc *s
" 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"
- );
+ "order by \"schema\", \"table\";\n", day_interval_str,
day_interval_str);
assert(pos < bufsize);
pos += snprintf(buf + pos, bufsize - pos,
@@ -2438,11 +2469,24 @@ sql_update_oct2020(Client c, mvc *sql, c
"GRANT EXECUTE ON AGGREGATE
quantile(INTERVAL DAY, DOUBLE) TO PUBLIC;\n"
"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 = %d;\n", (int) F_AGGR);
+ /* 90_generator.sql */
+ pos += snprintf(buf + pos, bufsize - pos,
+ "create function
sys.generate_series(first timestamp, \"limit\" timestamp, stepsize interval
day) returns table (value timestamp)\n"
+ " external name
\"generator\".\"series\";\n"
+ "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", (int) F_UNION);
+
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", prev_schema);
assert(pos < bufsize);
printf("Running database upgrade commands:\n%s\n", buf);
err = SQLstatementIntern(c, buf, "update", true, false,
NULL);
+ if (err) {
+ BBPunfix(b->batCacheid);
+ res_table_destroy(output);
+ GDKfree(buf);
+ return err;
+ }
+ err = sql_update_storagemodel(c, sql, prev_schema,
true); /* because of day interval addition, we have to recreate the storagmodel
views */
}
BBPunfix(b->batCacheid);
}
@@ -2580,7 +2624,7 @@ SQLupgrades(Client c, mvc *m)
if (sql_bind_func(m->sa, s, "storagemodel", NULL, NULL, F_UNION)
&& (t = mvc_bind_table(m, s, "tablestorage")) == NULL
&& (t = mvc_bind_table(m, s, "schemastorage")) == NULL ) {
- if ((err = sql_update_storagemodel(c, m, prev_schema)) != NULL)
{
+ if ((err = sql_update_storagemodel(c, m, prev_schema, false))
!= NULL) {
TRC_CRITICAL(SQL_PARSER, "%s\n", err);
freeException(err);
GDKfree(prev_schema);
diff --git a/sql/scripts/75_storagemodel.sql b/sql/scripts/75_storagemodel.sql
--- a/sql/scripts/75_storagemodel.sql
+++ b/sql/scripts/75_storagemodel.sql
@@ -220,7 +220,7 @@ begin
if tpe = 'smallint'
then return 2 * count;
end if;
- if tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'month_interval')
+ 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')
@@ -275,7 +275,7 @@ begin
if tpe = 'smallint'
then return cast(0.4 * count as bigint);
end if;
- if tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'month_interval')
+ 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')
diff --git a/sql/test/sys-schema/Tests/systemfunctions.stable.out
b/sql/test/sys-schema/Tests/systemfunctions.stable.out
--- a/sql/test/sys-schema/Tests/systemfunctions.stable.out
+++ b/sql/test/sys-schema/Tests/systemfunctions.stable.out
@@ -28,7 +28,7 @@ stdout of test 'systemfunctions` in dire
% sys., sys., sys., sys., . # table_name
% schema, function, argno, argtype, definition # name
% varchar, varchar, int, varchar, varchar # type
-% 8, 24, 2, 14, 679 # length
+% 8, 24, 2, 14, 695 # length
[ "json", "filter", 0, "json", "create function json.filter(js
json, pathexpr string)\nreturns json external name json.filter;" ]
[ "json", "filter", 1, "json", "" ]
[ "json", "filter", 2, "clob", "" ]
@@ -412,7 +412,7 @@ stdout of test 'systemfunctions` in dire
[ "sys", "code", 1, "int", "" ]
[ "sys", "code", 0, "clob", "unicode" ]
[ "sys", "code", 1, "int", "" ]
-[ "sys", "columnsize", 0, "bigint", "create function
sys.columnsize(tpe varchar(1024), count bigint)\nreturns bigint\nbegin\nif tpe
in ('tinyint', 'boolean')\nthen return count;\nend if;\nif tpe =
'smallint'\nthen return 2 * count;\nend if;\nif tpe in ('int', 'real', 'date',
'time', 'timetz', 'sec_interval', 'month_interval')\nthen return 4 *
count;\nend if;\nif tpe in ('bigint', 'double', 'timestamp', 'timestamptz',
'inet', 'oid')\nthen return 8 * count;\nend if;\nif tpe in ('hugeint',
'decimal', 'uuid', 'mbr')\nthen return 16 * count;\nend if;\nif tpe in
('varchar', 'char', 'clob', 'json', 'url')\nthen return 4 * count;\nend if;\nif
tpe in ('blob', 'geometry', 'geometrya')\nthen return 8 * count;\nend
if;\nreturn 8 * count;\nend;" ]
+[ "sys", "columnsize", 0, "bigint", "create function
sys.columnsize(tpe varchar(1024), count bigint)\nreturns bigint\nbegin\nif tpe
in ('tinyint', 'boolean')\nthen return count;\nend if;\nif tpe =
'smallint'\nthen return 2 * count;\nend if;\nif tpe in ('int', 'real', 'date',
'time', 'timetz', 'sec_interval', 'day_interval', 'month_interval')\nthen
return 4 * count;\nend if;\nif tpe in ('bigint', 'double', 'timestamp',
'timestamptz', 'inet', 'oid')\nthen return 8 * count;\nend if;\nif tpe in
('hugeint', 'decimal', 'uuid', 'mbr')\nthen return 16 * count;\nend if;\nif tpe
in ('varchar', 'char', 'clob', 'json', 'url')\nthen return 4 * count;\nend
if;\nif tpe in ('blob', 'geometry', 'geometrya')\nthen return 8 * count;\nend
if;\nreturn 8 * count;\nend;" ]
[ "sys", "columnsize", 1, "varchar", "" ]
[ "sys", "columnsize", 2, "bigint", "" ]
[ "sys", "concat", 0, "char", "+" ]
@@ -886,7 +886,7 @@ stdout of test 'systemfunctions` in dire
[ "sys", "ilike", 1, "clob", "" ]
[ "sys", "ilike", 2, "clob", "" ]
[ "sys", "ilike", 3, "clob", "" ]
-[ "sys", "imprintsize", 0, "bigint", "create function
sys.imprintsize(tpe varchar(1024), count bigint)\nreturns bigint\nbegin\nif tpe
in ('tinyint', 'boolean')\nthen return cast(0.2 * count as bigint);\nend
if;\nif tpe = 'smallint'\nthen return cast(0.4 * count as bigint);\nend if;\nif
tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'month_interval')\nthen return cast(0.8 * count as bigint);\nend if;\nif tpe in
('bigint', 'double', 'timestamp', 'timestamptz', 'inet', 'oid')\nthen return
cast(1.6 * count as bigint);\nend if;\nif tpe in ('hugeint', 'decimal', 'uuid',
'mbr')\nthen return cast(3.2 * count as bigint);\nend if;\nreturn 0;\nend;"
]
+[ "sys", "imprintsize", 0, "bigint", "create function
sys.imprintsize(tpe varchar(1024), count bigint)\nreturns bigint\nbegin\nif tpe
in ('tinyint', 'boolean')\nthen return cast(0.2 * count as bigint);\nend
if;\nif tpe = 'smallint'\nthen return cast(0.4 * count as bigint);\nend if;\nif
tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'day_interval', 'month_interval')\nthen return cast(0.8 * count as
bigint);\nend if;\nif tpe in ('bigint', 'double', 'timestamp', 'timestamptz',
'inet', 'oid')\nthen return cast(1.6 * count as bigint);\nend if;\nif tpe in
('hugeint', 'decimal', 'uuid', 'mbr')\nthen return cast(3.2 * count as
bigint);\nend if;\nreturn 0;\nend;" ]
[ "sys", "imprintsize", 1, "varchar", "" ]
[ "sys", "imprintsize", 2, "bigint", "" ]
[ "sys", "index", 0, "tinyint", "index" ]
diff --git a/sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
b/sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
--- a/sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
+++ b/sql/test/sys-schema/Tests/systemfunctions.stable.out.int128
@@ -28,7 +28,7 @@ stdout of test 'systemfunctions` in dire
% sys., sys., sys., sys., . # table_name
% schema, function, argno, argtype, definition # name
% varchar, varchar, int, varchar, varchar # type
-% 8, 24, 2, 14, 679 # length
+% 8, 24, 2, 14, 695 # length
[ "json", "filter", 0, "json", "create function json.filter(js
json, pathexpr string)\nreturns json external name json.filter;" ]
[ "json", "filter", 1, "json", "" ]
[ "json", "filter", 2, "clob", "" ]
@@ -449,7 +449,7 @@ stdout of test 'systemfunctions` in dire
[ "sys", "code", 1, "int", "" ]
[ "sys", "code", 0, "clob", "unicode" ]
[ "sys", "code", 1, "int", "" ]
-[ "sys", "columnsize", 0, "bigint", "create function
sys.columnsize(tpe varchar(1024), count bigint)\nreturns bigint\nbegin\nif tpe
in ('tinyint', 'boolean')\nthen return count;\nend if;\nif tpe =
'smallint'\nthen return 2 * count;\nend if;\nif tpe in ('int', 'real', 'date',
'time', 'timetz', 'sec_interval', 'month_interval')\nthen return 4 *
count;\nend if;\nif tpe in ('bigint', 'double', 'timestamp', 'timestamptz',
'inet', 'oid')\nthen return 8 * count;\nend if;\nif tpe in ('hugeint',
'decimal', 'uuid', 'mbr')\nthen return 16 * count;\nend if;\nif tpe in
('varchar', 'char', 'clob', 'json', 'url')\nthen return 4 * count;\nend if;\nif
tpe in ('blob', 'geometry', 'geometrya')\nthen return 8 * count;\nend
if;\nreturn 8 * count;\nend;" ]
+[ "sys", "columnsize", 0, "bigint", "create function
sys.columnsize(tpe varchar(1024), count bigint)\nreturns bigint\nbegin\nif tpe
in ('tinyint', 'boolean')\nthen return count;\nend if;\nif tpe =
'smallint'\nthen return 2 * count;\nend if;\nif tpe in ('int', 'real', 'date',
'time', 'timetz', 'sec_interval', 'day_interval', 'month_interval')\nthen
return 4 * count;\nend if;\nif tpe in ('bigint', 'double', 'timestamp',
'timestamptz', 'inet', 'oid')\nthen return 8 * count;\nend if;\nif tpe in
('hugeint', 'decimal', 'uuid', 'mbr')\nthen return 16 * count;\nend if;\nif tpe
in ('varchar', 'char', 'clob', 'json', 'url')\nthen return 4 * count;\nend
if;\nif tpe in ('blob', 'geometry', 'geometrya')\nthen return 8 * count;\nend
if;\nreturn 8 * count;\nend;" ]
[ "sys", "columnsize", 1, "varchar", "" ]
[ "sys", "columnsize", 2, "bigint", "" ]
[ "sys", "concat", 0, "char", "+" ]
@@ -825,6 +825,10 @@ stdout of test 'systemfunctions` in dire
[ "sys", "generate_series", 1, "timestamp", "" ]
[ "sys", "generate_series", 2, "timestamp", "" ]
[ "sys", "generate_series", 3, "sec_interval", "" ]
+[ "sys", "generate_series", 0, "timestamp", "create
function sys.generate_series(first timestamp, \"limit\" timestamp, stepsize
interval day)\nreturns table (value timestamp)\nexternal name
generator.series;" ]
+[ "sys", "generate_series", 1, "timestamp", "" ]
+[ "sys", "generate_series", 2, "timestamp", "" ]
+[ "sys", "generate_series", 3, "day_interval", "" ]
[ "sys", "generate_series", 0, "hugeint", "create
function sys.generate_series(first hugeint, \"limit\" hugeint)\nreturns table
(value hugeint)\nexternal name generator.series;" ]
[ "sys", "generate_series", 1, "hugeint", "" ]
[ "sys", "generate_series", 2, "hugeint", "" ]
@@ -953,7 +957,7 @@ stdout of test 'systemfunctions` in dire
[ "sys", "ilike", 1, "clob", "" ]
[ "sys", "ilike", 2, "clob", "" ]
[ "sys", "ilike", 3, "clob", "" ]
-[ "sys", "imprintsize", 0, "bigint", "create function
sys.imprintsize(tpe varchar(1024), count bigint)\nreturns bigint\nbegin\nif tpe
in ('tinyint', 'boolean')\nthen return cast(0.2 * count as bigint);\nend
if;\nif tpe = 'smallint'\nthen return cast(0.4 * count as bigint);\nend if;\nif
tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'month_interval')\nthen return cast(0.8 * count as bigint);\nend if;\nif tpe in
('bigint', 'double', 'timestamp', 'timestamptz', 'inet', 'oid')\nthen return
cast(1.6 * count as bigint);\nend if;\nif tpe in ('hugeint', 'decimal', 'uuid',
'mbr')\nthen return cast(3.2 * count as bigint);\nend if;\nreturn 0;\nend;"
]
+[ "sys", "imprintsize", 0, "bigint", "create function
sys.imprintsize(tpe varchar(1024), count bigint)\nreturns bigint\nbegin\nif tpe
in ('tinyint', 'boolean')\nthen return cast(0.2 * count as bigint);\nend
if;\nif tpe = 'smallint'\nthen return cast(0.4 * count as bigint);\nend if;\nif
tpe in ('int', 'real', 'date', 'time', 'timetz', 'sec_interval',
'day_interval', 'month_interval')\nthen return cast(0.8 * count as
bigint);\nend if;\nif tpe in ('bigint', 'double', 'timestamp', 'timestamptz',
'inet', 'oid')\nthen return cast(1.6 * count as bigint);\nend if;\nif tpe in
('hugeint', 'decimal', 'uuid', 'mbr')\nthen return cast(3.2 * count as
bigint);\nend if;\nreturn 0;\nend;" ]
[ "sys", "imprintsize", 1, "varchar", "" ]
[ "sys", "imprintsize", 2, "bigint", "" ]
[ "sys", "index", 0, "tinyint", "index" ]
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
