Changeset: 2306be044f3d for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=2306be044f3d
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/scripts/99_system.sql
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
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/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: system-functions
Log Message:
Replace table sys.systemfunctions with a view.
diffs (truncated from 811 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
@@ -170,10 +170,6 @@ sql_fix_system_tables(Client c, mvc *sql
arg->inout);
}
}
- pos += snprintf(buf + pos, bufsize - pos,
- "delete from sys.systemfunctions where function_id <
2000;\n"
- "insert into sys.systemfunctions"
- " (select id from sys.functions where id < 2000);\n");
if (schema)
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
@@ -256,8 +252,6 @@ sql_update_hugeint(Client c, mvc *sql)
"from sys.storagemodel() group by
\"schema\",\"table\";\n");
pos += snprintf(buf + pos, bufsize - pos,
- "insert into sys.systemfunctions (select id from
sys.functions where name in ('fuse', 'generate_series', 'stddev_samp',
'stddev_pop', 'var_samp', 'var_pop', 'median', 'quantile', 'corr') and
schema_id = (select id from sys.schemas where name = 'sys') and id not in
(select function_id from sys.systemfunctions));\n"
- "insert into sys.systemfunctions (select id from
sys.functions where name = 'filter' and schema_id = (select id from sys.schemas
where name = 'json') and id not in (select function_id from
sys.systemfunctions));\n"
"update sys._tables set system = true where name =
'tablestoragemodel' and schema_id = (select id from sys.schemas where name =
'sys');\n");
if (s != NULL) {
@@ -536,15 +530,6 @@ sql_update_dec2016(Client c, mvc *sql)
pos += snprintf(buf + pos, bufsize - pos,
"alter table sys.statistics add column \"revsorted\"
boolean;\n");
- pos += snprintf(buf + pos, bufsize - pos,
- "insert into sys.systemfunctions (select f.id from
sys.functions f, sys.schemas s where f.name in ('storage', 'storagemodel') and
f.type = %d and f.schema_id = s.id and s.name = 'sys');\n",
- F_UNION);
- pos += snprintf(buf + pos, bufsize - pos,
- "insert into sys.systemfunctions (select f.id from
sys.functions f, sys.schemas s where f.name in ('createorderindex',
'droporderindex', 'storagemodelinit') and f.type = %d and f.schema_id = s.id
and s.name = 'sys');\n",
- F_PROC);
- pos += snprintf(buf + pos, bufsize - pos,
- "delete from systemfunctions where function_id not in
(select id from functions);\n");
-
if (schema)
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
@@ -625,8 +610,7 @@ sql_update_dec2016_sp3(Client c, mvc *sq
"drop procedure sys.setsession(bigint);\n"
"create system procedure sys.settimeout(\"query\"
bigint) external name clients.settimeout;\n"
"create system procedure sys.settimeout(\"query\"
bigint, \"session\" bigint) external name clients.settimeout;\n"
- "create system procedure sys.setsession(\"timeout\"
bigint) external name clients.setsession;\n"
- "delete from systemfunctions where function_id not in
(select id from functions);\n");
+ "create system procedure sys.setsession(\"timeout\"
bigint) external name clients.setsession;\n");
if (schema)
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
@@ -667,16 +651,14 @@ sql_update_jul2017(Client c, mvc *sql)
"drop function sys.optimizer_stats();\n"
"create system function sys.optimizer_stats() "
"returns table (optname string, count int, timing
bigint) "
- "external name inspect.optimizer_stats;\n"
- "insert into sys.systemfunctions (select id from
sys.functions where name in ('malfunctions', 'optimizer_stats') and schema_id =
(select id from sys.schemas where name = 'sys') and id not in (select
function_id from sys.systemfunctions));\n");
+ "external name inspect.optimizer_stats;\n");
/* 46_profiler.sql */
pos += snprintf(buf + pos, bufsize - pos,
"create system function profiler.getlimit() returns
integer external name profiler.getlimit;\n"
"create system procedure profiler.setlimit(lim integer)
external name profiler.setlimit;\n"
"drop procedure profiler.setpoolsize;\n"
- "drop procedure profiler.setstream;\n"
- "insert into sys.systemfunctions (select id from
sys.functions where name in ('getlimit', 'setlimit') and schema_id = (select id
from sys.schemas where name = 'profiler') and id not in (select function_id
from sys.systemfunctions));\n");
+ "drop procedure profiler.setstream;\n");
/* 51_sys_schema_extensions.sql */
pos += snprintf(buf + pos, bufsize - pos,
@@ -736,16 +718,12 @@ sql_update_jul2017(Client c, mvc *sql)
if (BATcount(b) > 0) {
pos += snprintf(buf + pos, bufsize - pos,
"drop procedure SHPload(integer);\n"
- "create system procedure SHPload(fid
integer) external name shp.import;\n"
- "insert into sys.systemfunctions
(select id from sys.functions where name = 'shpload' and schema_id = (select id
from sys.schemas where name = 'sys') and id not in (select function_id from
sys.systemfunctions));\n");
+ "create system procedure SHPload(fid
integer) external name shp.import;\n");
}
BBPunfix(b->batCacheid);
}
res_tables_destroy(output);
- pos += snprintf(buf + pos, bufsize - pos,
- "delete from sys.systemfunctions where function_id not
in (select id from sys.functions);\n");
-
if (schema)
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
@@ -883,9 +861,6 @@ sql_update_mar2018_geom(Client c, mvc *s
"GRANT SELECT ON sys.geometry_columns TO PUBLIC;\n"
"update sys._tables set system = true where name =
'geometry_columns' and schema_id in (select id from schemas where name =
'sys');\n");
- pos += snprintf(buf + pos, bufsize - pos,
- "delete from sys.systemfunctions where function_id not
in (select id from sys.functions);\n");
-
if (schema)
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
@@ -1212,9 +1187,6 @@ sql_update_mar2018(Client c, mvc *sql)
"create system aggregate corr(e1 HUGEINT, e2
HUGEINT) returns DOUBLE\n\texternal name \"aggr\".\"corr\";\n"
"grant execute on aggregate sys.corr(hugeint, hugeint)
to public;\n");
#endif
- pos += snprintf(buf + pos, bufsize - pos,
- "insert into sys.systemfunctions (select id from
sys.functions where name = 'corr' and schema_id = (select id from sys.schemas
where name = 'sys') and id not in (select function_id from
sys.systemfunctions));\n");
-
/* 51_sys_schema_extensions.sql */
t = mvc_bind_table(sql, s, "privilege_codes");
t->system = 0;
@@ -1347,7 +1319,6 @@ sql_update_mar2018(Client c, mvc *sql)
"external name wlr.\"getreplicaclock\";\n"
"create system function replicaTick() returns bigint\n"
"external name wlr.\"getreplicatick\";\n"
- "insert into sys.systemfunctions (select id from
sys.functions where name in ('master', 'stopmaster', 'masterbeat',
'masterclock', 'mastertick', 'replicate', 'replicabeat', 'replicaclock',
'replicatick') and schema_id = (select id from sys.schemas where name = 'sys')
and id not in (select function_id from sys.systemfunctions));\n"
);
/* comments */
@@ -1356,12 +1327,7 @@ sql_update_mar2018(Client c, mvc *sql)
"SET system = true\n"
"WHERE name = 'comments'\n"
"AND schema_id = (SELECT id FROM sys.schemas WHERE name
= 'sys');\n"
- "DELETE FROM sys.systemfunctions WHERE function_id IS
NULL;\n"
- "ALTER TABLE sys.systemfunctions ALTER COLUMN
function_id SET NOT NULL;\n"
);
- pos += snprintf(buf + pos, bufsize - pos,
- "delete from sys.systemfunctions where function_id not
in (select id from sys.functions);\n");
-
if (schema)
pos += snprintf(buf + pos, bufsize - pos, "set schema
\"%s\";\n", schema);
pos += snprintf(buf + pos, bufsize - pos, "commit;\n");
@@ -1453,23 +1419,20 @@ sql_update_mar2018_samtools(Client c, mv
if (sql_bind_func_(sql->sa, s, "seq_char", l, F_FUNC) == NULL) {
pos += snprintf(buf + pos, bufsize - pos,
"CREATE SYSTEM FUNCTION bam.seq_char(ref_pos
INT, alg_seq STRING, alg_pos INT, alg_cigar STRING)\n"
- "RETURNS CHAR(1) EXTERNAL NAME bam.seq_char;\n"
- "insert into sys.systemfunctions (select id from
sys.functions where name in ('seq_char') and schema_id = (select id from
sys.schemas where name = 'bam') and id not in (select function_id from
sys.systemfunctions));\n");
+ "RETURNS CHAR(1) EXTERNAL NAME
bam.seq_char;\n");
}
sql_find_subtype(&tpi, "smallint", 0, 0);
if (sql_bind_func3(sql->sa, s, "bam_loader_repos", &tps, &tpi, &tpi,
F_PROC) != NULL) {
pos += snprintf(buf + pos, bufsize - pos,
"drop procedure bam.bam_loader_repos(string,
smallint, smallint);\n"
- "drop procedure bam.bam_loader_files(string,
smallint, smallint);\n"
- "delete from systemfunctions where function_id
not in (select id from functions);\n");
+ "drop procedure bam.bam_loader_files(string,
smallint, smallint);\n");
}
if (sql_bind_func(sql->sa, s, "bam_loader_repos", &tps, &tpi, F_PROC)
== NULL) {
pos += snprintf(buf + pos, bufsize - pos,
"CREATE SYSTEM PROCEDURE
bam.bam_loader_repos(bam_repos STRING, dbschema SMALLINT)\n"
"EXTERNAL NAME bam.bam_loader_repos;\n"
"CREATE SYSTEM PROCEDURE
bam.bam_loader_files(bam_files STRING, dbschema SMALLINT)\n"
- "EXTERNAL NAME bam.bam_loader_files;\n"
- "insert into sys.systemfunctions (select id from
sys.functions where name in ('bam_loader_repos', 'bam_loader_files') and
schema_id = (select id from sys.schemas where name = 'bam') and id not in
(select function_id from sys.systemfunctions));\n");
+ "EXTERNAL NAME bam.bam_loader_files;\n");
}
pos += snprintf(buf + pos, bufsize - pos,
@@ -1508,18 +1471,29 @@ sql_update_default(Client c, mvc *sql)
size_t bufsize = 1000, pos = 0;
char *buf, *err;
char *schema;
+ sql_schema *s;
+ sql_table *t;
schema = stack_get_string(sql, "current_schema");
if ((buf = GDKmalloc(bufsize)) == NULL)
throw(SQL, "sql_update_default", SQLSTATE(HY001)
MAL_MALLOC_FAIL);
+ s = mvc_bind_schema(sql, "sys");
+
pos += snprintf(buf + pos, bufsize - pos, "set schema sys;\n");
pos += snprintf(buf + pos, bufsize - pos,
"create system aggregate sys.group_concat(str string)
returns string external name \"aggr\".\"str_group_concat\";\n"
"grant execute on aggregate sys.group_concat(string) to
public;\n"
"create system aggregate sys.group_concat(str string,
sep string) returns string external name \"aggr\".\"str_group_concat\";\n"
- "grant execute on aggregate sys.group_concat(string,
string) to public;\n"
- "insert into sys.systemfunctions (select id from
sys.functions where name in ('group_concat') and schema_id = (select id from
sys.schemas where name = 'sys') and id not in (select function_id from
sys.systemfunctions));\n");
+ "grant execute on aggregate sys.group_concat(string,
string) to public;\n");
+
+ t = mvc_bind_table(sql, s, "systemfunctions");
+ t->system = 0;
+ pos += snprintf(buf + pos, bufsize - pos,
+ "drop table sys.systemfunctions;\n"
+ "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);
diff --git a/sql/scripts/99_system.sql b/sql/scripts/99_system.sql
--- a/sql/scripts/99_system.sql
+++ b/sql/scripts/99_system.sql
@@ -4,10 +4,9 @@
--
-- Copyright 1997 - July 2008 CWI, August 2008 - 2018 MonetDB B.V.
--- only system functions until now
-create table sys.systemfunctions (function_id integer not null);
+-- sys.systemfunctions may be removed in the future
+create view sys.systemfunctions as select id as function_id from sys.functions
where system;
grant select on sys.systemfunctions to public;
-insert into systemfunctions select id from functions;
create trigger system_update_schemas after update on sys.schemas for each
statement call sys_update_schemas();
create trigger system_update_tables after update on sys._tables for each
statement call sys_update_tables();
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
@@ -30,7 +30,10 @@ create system aggregate sys.group_concat
grant execute on aggregate sys.group_concat(string) to public;
create system aggregate sys.group_concat(str string, sep string) returns
string external name "aggr"."str_group_concat";
grant execute on aggregate sys.group_concat(string, string) to public;
-insert into sys.systemfunctions (select id from sys.functions where name in
('group_concat') and schema_id = (select id from sys.schemas where name =
'sys') and id not in (select function_id from sys.systemfunctions));
+drop table sys.systemfunctions;
+create view sys.systemfunctions as select id as function_id from sys.functions
where system;
+grant select on sys.systemfunctions to public;
+update sys._tables set system = true where name = 'systemfunctions' and
schema_id = (select id from sys.schemas where name = 'sys');
set schema "sys";
commit;
diff --git
a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
--- a/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
+++ b/sql/test/emptydb-upgrade-chain-hge/Tests/upgrade.stable.out.powerpc64
@@ -25,7 +25,10 @@ create system aggregate sys.group_concat
grant execute on aggregate sys.group_concat(string) to public;
create system aggregate sys.group_concat(str string, sep string) returns
string external name "aggr"."str_group_concat";
grant execute on aggregate sys.group_concat(string, string) to public;
-insert into sys.systemfunctions (select id from sys.functions where name in
('group_concat') and schema_id = (select id from sys.schemas where name =
'sys') and id not in (select function_id from sys.systemfunctions));
+drop table sys.systemfunctions;
+create view sys.systemfunctions as select id as function_id from sys.functions
where system;
+grant select on sys.systemfunctions to public;
+update sys._tables set system = true where name = 'systemfunctions' and
schema_id = (select id from sys.schemas where name = 'sys');
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out
@@ -30,7 +30,10 @@ create system aggregate sys.group_concat
grant execute on aggregate sys.group_concat(string) to public;
create system aggregate sys.group_concat(str string, sep string) returns
string external name "aggr"."str_group_concat";
grant execute on aggregate sys.group_concat(string, string) to public;
-insert into sys.systemfunctions (select id from sys.functions where name in
('group_concat') and schema_id = (select id from sys.schemas where name =
'sys') and id not in (select function_id from sys.systemfunctions));
+drop table sys.systemfunctions;
+create view sys.systemfunctions as select id as function_id from sys.functions
where system;
+grant select on sys.systemfunctions to public;
+update sys._tables set system = true where name = 'systemfunctions' and
schema_id = (select id from sys.schemas where name = 'sys');
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.32bit
@@ -30,7 +30,10 @@ create system aggregate sys.group_concat
grant execute on aggregate sys.group_concat(string) to public;
create system aggregate sys.group_concat(str string, sep string) returns
string external name "aggr"."str_group_concat";
grant execute on aggregate sys.group_concat(string, string) to public;
-insert into sys.systemfunctions (select id from sys.functions where name in
('group_concat') and schema_id = (select id from sys.schemas where name =
'sys') and id not in (select function_id from sys.systemfunctions));
+drop table sys.systemfunctions;
+create view sys.systemfunctions as select id as function_id from sys.functions
where system;
+grant select on sys.systemfunctions to public;
+update sys._tables set system = true where name = 'systemfunctions' and
schema_id = (select id from sys.schemas where name = 'sys');
set schema "sys";
commit;
diff --git a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -5297,8 +5297,6 @@ insert into sys.functions values (134, '
insert into sys.args values (22390, 134, 'res', 'bigint', 64, 0, 0, 0);
insert into sys.functions values (135, 'count', 'count', 'aggr', 0, 3, false,
false, false, 2000, true);
insert into sys.args values (22391, 135, 'res', 'bigint', 64, 0, 0, 0);
-delete from sys.systemfunctions where function_id < 2000;
-insert into sys.systemfunctions (select id from sys.functions where id < 2000);
set schema "sys";
Running database upgrade commands:
@@ -5338,8 +5336,6 @@ as select "schema","table",max(count) as
sum("imprints") as "imprints",
sum(case when sorted = false then 8 * count else 0 end) as auxiliary
from sys.storagemodel() group by "schema","table";
-insert into sys.systemfunctions (select id from sys.functions where name in
('fuse', 'generate_series', 'stddev_samp', 'stddev_pop', 'var_samp', 'var_pop',
'median', 'quantile', 'corr') and schema_id = (select id from sys.schemas where
name = 'sys') and id not in (select function_id from sys.systemfunctions));
-insert into sys.systemfunctions (select id from sys.functions where name =
'filter' and schema_id = (select id from sys.schemas where name = 'json') and
id not in (select function_id from sys.systemfunctions));
update sys._tables set system = true where name = 'tablestoragemodel' and
schema_id = (select id from sys.schemas where name = 'sys');
grant execute on aggregate sys.stddev_samp(hugeint) to public;
grant execute on aggregate sys.stddev_pop(hugeint) to public;
@@ -5358,7 +5354,10 @@ create system aggregate sys.group_concat
grant execute on aggregate sys.group_concat(string) to public;
create system aggregate sys.group_concat(str string, sep string) returns
string external name "aggr"."str_group_concat";
grant execute on aggregate sys.group_concat(string, string) to public;
-insert into sys.systemfunctions (select id from sys.functions where name in
('group_concat') and schema_id = (select id from sys.schemas where name =
'sys') and id not in (select function_id from sys.systemfunctions));
+drop table sys.systemfunctions;
+create view sys.systemfunctions as select id as function_id from sys.functions
where system;
+grant select on sys.systemfunctions to public;
+update sys._tables set system = true where name = 'systemfunctions' and
schema_id = (select id from sys.schemas where name = 'sys');
set schema "sys";
commit;
diff --git
a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
--- a/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
+++ b/sql/test/emptydb-upgrade-chain/Tests/upgrade.stable.out.powerpc64.int128
@@ -5338,8 +5338,6 @@ as select "schema","table",max(count) as
sum("imprints") as "imprints",
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
