Changeset: 6ea6c0320833 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB?cmd=changeset;node=6ea6c0320833
Modified Files:
sql/backends/monet5/sql_user.c
sql/test/emptydb-upgrade/Tests/upgrade.stable.out
sql/test/emptydb/Tests/check.stable.out
sql/test/emptydb/Tests/check.stable.out.32bit
sql/test/emptydb/Tests/check.stable.out.int128
Branch: default
Log Message:
Fix upgrade by changing how sys.users is created in a clean database.
diffs (93 lines):
diff --git a/sql/backends/monet5/sql_user.c b/sql/backends/monet5/sql_user.c
--- a/sql/backends/monet5/sql_user.c
+++ b/sql/backends/monet5/sql_user.c
@@ -286,11 +286,11 @@ monet5_create_privileges(ptr _mvc, sql_s
mvc_create_func(m, NULL, s, "db_users", ops, res, F_UNION,
FUNC_LANG_SQL, "sql", "db_users", "CREATE FUNCTION db_users () RETURNS TABLE(
name varchar(2048)) EXTERNAL NAME sql.db_users;", FALSE, FALSE, TRUE);
t = mvc_init_create_view(m, s, "users",
- "SELECT u.\"name\" AS \"name\", "
+ "create view sys.users as select u.\"name\" as
\"name\", "
"ui.\"fullname\", ui.\"default_schema\", "
- "ui.\"schema_path\" FROM db_users() AS u "
- "LEFT JOIN \"sys\".\"db_user_info\" AS ui "
- "ON u.\"name\" = ui.\"name\";");
+ "ui.\"schema_path\" from db_users() as u "
+ "left join \"sys\".\"db_user_info\" as ui "
+ "on u.\"name\" = ui.\"name\";");
if (!t) {
TRC_CRITICAL(SQL_TRANS, "Failed to create 'users' view\n");
return ;
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out
@@ -3798,7 +3798,6 @@ CREATE VIEW sys.users AS
SELECT u."name" AS "name", ui."fullname", ui."default_schema", ui."schema_path"
FROM db_users() AS u
LEFT JOIN "sys"."db_user_info" AS ui ON u."name" = ui."name";
-GRANT SELECT ON sys.users TO PUBLIC;
CREATE VIEW sys.dependency_schemas_on_users AS
SELECT s.id AS schema_id, s.name AS schema_name, u.name AS user_name, CAST(6
AS smallint) AS depend_type
FROM sys.users AS u, sys.schemas AS s
diff --git a/sql/test/emptydb/Tests/check.stable.out
b/sql/test/emptydb/Tests/check.stable.out
--- a/sql/test/emptydb/Tests/check.stable.out
+++ b/sql/test/emptydb/Tests/check.stable.out
@@ -959,7 +959,7 @@ SELECT "id", "name", "schema_id", "query
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.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";
create view sys.tracelog as select * from sys.tracelog();
-SELECT u."name" AS "name", ui."fullname", ui."default_schema",
ui."schema_path" FROM db_users() AS u LEFT JOIN "sys"."db_user_info" AS ui ON
u."name" = ui."name";
+create view sys.users as select u."name" as "name", ui."fullname",
ui."default_schema", ui."schema_path" from db_users() as u left join
"sys"."db_user_info" as ui on u."name" = ui."name";
create view sys.var_values (var_name, value) as select 'current_role',
current_role union all select 'current_schema', current_schema union all select
'current_timezone', current_timezone union all select 'current_user',
current_user union all select 'debug', debug union all select 'last_id',
last_id union all select 'optimizer', optimizer union all select 'pi', pi()
union all select 'rowcnt', rowcnt;
create function "abbrev" (p inet) returns clob external name inet."abbrev";
create function alpha(pdec double, pradius double) returns double external
name sql.alpha;
@@ -1550,7 +1550,7 @@ drop function pcre_replace(string, strin
[ "sys._tables", "sys", "triggers", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "types", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "user_role", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
-[ "sys._tables", "sys", "users", "SELECT u.\"name\" AS \"name\",
ui.\"fullname\", ui.\"default_schema\", ui.\"schema_path\" FROM db_users() AS u
LEFT JOIN \"sys\".\"db_user_info\" AS ui ON u.\"name\" = ui.\"name\";", "VIEW",
true, "COMMIT", "WRITABLE" ]
+[ "sys._tables", "sys", "users", "create view sys.users as
select u.\"name\" as \"name\", ui.\"fullname\", ui.\"default_schema\",
ui.\"schema_path\" from db_users() as u left join \"sys\".\"db_user_info\" as
ui on u.\"name\" = ui.\"name\";", "VIEW", true, "COMMIT",
"WRITABLE" ]
[ "sys._tables", "sys", "value_partitions", NULL, "TABLE",
true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "var_values", "create view sys.var_values
(var_name, value) as select 'current_role', current_role union all select
'current_schema', current_schema union all select 'current_timezone',
current_timezone union all select 'current_user', current_user union all select
'debug', debug union all select 'last_id', last_id union all select
'optimizer', optimizer union all select 'pi', pi() union all select 'rowcnt',
rowcnt;", "VIEW", true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "tmp", "_columns", NULL, "TABLE", true,
"PRESERVE", "WRITABLE" ]
diff --git a/sql/test/emptydb/Tests/check.stable.out.32bit
b/sql/test/emptydb/Tests/check.stable.out.32bit
--- a/sql/test/emptydb/Tests/check.stable.out.32bit
+++ b/sql/test/emptydb/Tests/check.stable.out.32bit
@@ -959,7 +959,7 @@ SELECT "id", "name", "schema_id", "query
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.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";
create view sys.tracelog as select * from sys.tracelog();
-SELECT u."name" AS "name", ui."fullname", ui."default_schema",
ui."schema_path" FROM db_users() AS u LEFT JOIN "sys"."db_user_info" AS ui ON
u."name" = ui."name";
+create view sys.users as select u."name" as "name", ui."fullname",
ui."default_schema", ui."schema_path" from db_users() as u left join
"sys"."db_user_info" as ui on u."name" = ui."name";
create view sys.var_values (var_name, value) as select 'current_role',
current_role union all select 'current_schema', current_schema union all select
'current_timezone', current_timezone union all select 'current_user',
current_user union all select 'debug', debug union all select 'last_id',
last_id union all select 'optimizer', optimizer union all select 'pi', pi()
union all select 'rowcnt', rowcnt;
create function "abbrev" (p inet) returns clob external name inet."abbrev";
create function alpha(pdec double, pradius double) returns double external
name sql.alpha;
@@ -1550,7 +1550,7 @@ drop function pcre_replace(string, strin
[ "sys._tables", "sys", "triggers", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "types", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "user_role", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
-[ "sys._tables", "sys", "users", "SELECT u.\"name\" AS \"name\",
ui.\"fullname\", ui.\"default_schema\", ui.\"schema_path\" FROM db_users() AS u
LEFT JOIN \"sys\".\"db_user_info\" AS ui ON u.\"name\" = ui.\"name\";", "VIEW",
true, "COMMIT", "WRITABLE" ]
+[ "sys._tables", "sys", "users", "create view sys.users as
select u.\"name\" as \"name\", ui.\"fullname\", ui.\"default_schema\",
ui.\"schema_path\" from db_users() as u left join \"sys\".\"db_user_info\" as
ui on u.\"name\" = ui.\"name\";", "VIEW", true, "COMMIT",
"WRITABLE" ]
[ "sys._tables", "sys", "value_partitions", NULL, "TABLE",
true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "var_values", "create view sys.var_values
(var_name, value) as select 'current_role', current_role union all select
'current_schema', current_schema union all select 'current_timezone',
current_timezone union all select 'current_user', current_user union all select
'debug', debug union all select 'last_id', last_id union all select
'optimizer', optimizer union all select 'pi', pi() union all select 'rowcnt',
rowcnt;", "VIEW", true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "tmp", "_columns", NULL, "TABLE", true,
"PRESERVE", "WRITABLE" ]
diff --git a/sql/test/emptydb/Tests/check.stable.out.int128
b/sql/test/emptydb/Tests/check.stable.out.int128
--- a/sql/test/emptydb/Tests/check.stable.out.int128
+++ b/sql/test/emptydb/Tests/check.stable.out.int128
@@ -959,7 +959,7 @@ SELECT "id", "name", "schema_id", "query
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.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";
create view sys.tracelog as select * from sys.tracelog();
-SELECT u."name" AS "name", ui."fullname", ui."default_schema",
ui."schema_path" FROM db_users() AS u LEFT JOIN "sys"."db_user_info" AS ui ON
u."name" = ui."name";
+create view sys.users as select u."name" as "name", ui."fullname",
ui."default_schema", ui."schema_path" from db_users() as u left join
"sys"."db_user_info" as ui on u."name" = ui."name";
create view sys.var_values (var_name, value) as select 'current_role',
current_role union all select 'current_schema', current_schema union all select
'current_timezone', current_timezone union all select 'current_user',
current_user union all select 'debug', debug union all select 'last_id',
last_id union all select 'optimizer', optimizer union all select 'pi', pi()
union all select 'rowcnt', rowcnt;
create function "abbrev" (p inet) returns clob external name inet."abbrev";
create function alpha(pdec double, pradius double) returns double external
name sql.alpha;
@@ -1571,7 +1571,7 @@ drop function pcre_replace(string, strin
[ "sys._tables", "sys", "triggers", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "types", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "user_role", NULL, "TABLE", true,
"COMMIT", "WRITABLE" ]
-[ "sys._tables", "sys", "users", "SELECT u.\"name\" AS \"name\",
ui.\"fullname\", ui.\"default_schema\", ui.\"schema_path\" FROM db_users() AS u
LEFT JOIN \"sys\".\"db_user_info\" AS ui ON u.\"name\" = ui.\"name\";", "VIEW",
true, "COMMIT", "WRITABLE" ]
+[ "sys._tables", "sys", "users", "create view sys.users as
select u.\"name\" as \"name\", ui.\"fullname\", ui.\"default_schema\",
ui.\"schema_path\" from db_users() as u left join \"sys\".\"db_user_info\" as
ui on u.\"name\" = ui.\"name\";", "VIEW", true, "COMMIT",
"WRITABLE" ]
[ "sys._tables", "sys", "value_partitions", NULL, "TABLE",
true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "sys", "var_values", "create view sys.var_values
(var_name, value) as select 'current_role', current_role union all select
'current_schema', current_schema union all select 'current_timezone',
current_timezone union all select 'current_user', current_user union all select
'debug', debug union all select 'last_id', last_id union all select
'optimizer', optimizer union all select 'pi', pi() union all select 'rowcnt',
rowcnt;", "VIEW", true, "COMMIT", "WRITABLE" ]
[ "sys._tables", "tmp", "_columns", NULL, "TABLE", true,
"PRESERVE", "WRITABLE" ]
_______________________________________________
checkin-list mailing list
checkin-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/checkin-list
