Changeset: 99812bed8715 for MonetDB
URL: https://dev.monetdb.org/hg/MonetDB/rev/99812bed8715
Modified Files:
sql/backends/monet5/sql_upgrades.c
sql/test/Dependencies/Tests/dependency_owner_schema_3.test
sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
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-hge/Tests/upgrade.stable.out.int128
sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
sql/test/emptydb/Tests/check.stable.out.int128
sql/test/testdb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-previous-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.int128
sql/test/testdb-upgrade-hge/Tests/upgrade.stable.out.int128
sql/test/testdb-upgrade/Tests/upgrade.stable.out.int128
sql/test/transactions/Tests/transaction_isolation2.test
Branch: Dec2025
Log Message:
Fix and approve upgrade code for changes sys.roles.
diffs (truncated from 552 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
@@ -5277,13 +5277,31 @@ sql_update_dec2025_sp1(Client c, mvc *sq
err = SQLstatementIntern(c, query1, "update", true, false, &output);
if (err == MAL_SUCCEED && (b = BBPquickdesc(output->cols[0].b)) &&
BATcount(b) == 1) {
static const char stmt1[] =
+ "DROP VIEW sys.describe_accessible_tables CASCADE;\n"
"DROP VIEW sys.roles CASCADE;\n"
"CREATE VIEW sys.roles AS SELECT id, name, grantor FROM
sys.auths;\n"
"GRANT SELECT ON sys.roles TO PUBLIC;\n"
- "UPDATE sys._tables SET system = true WHERE not system
and schema_id = 2000 and name = 'roles';\n";
+ "CREATE VIEW sys.describe_accessible_tables AS\n"
+ " SELECT\n"
+ " schemas.name AS schema,\n"
+ " tables.name AS table,\n"
+ " tt.table_type_name AS table_type,\n"
+ " pc.privilege_code_name AS privs,\n"
+ " p.privileges AS privs_code\n"
+ " FROM privileges p\n"
+ " JOIN sys.roles ON p.auth_id = roles.id\n"
+ " JOIN sys.tables ON p.obj_id = tables.id\n"
+ " JOIN sys.table_types tt ON tables.type =
tt.table_type_id\n"
+ " JOIN sys.schemas ON tables.schema_id =
schemas.id\n"
+ " JOIN sys.privilege_codes pc ON p.privileges =
pc.privilege_code_id\n"
+ " WHERE roles.name = current_role;\n"
+ "GRANT SELECT ON sys.describe_accessible_tables TO
PUBLIC;\n"
+ "UPDATE sys._tables SET system = true WHERE not system
and schema_id = 2000 and name in ('roles', 'describe_accessible_tables');\n";
sql_table *t;
if ((t = mvc_bind_table(sql, s, "roles")) != NULL)
t->system = 0; /* make it non-system else the drop view
will fail */
+ if ((t = mvc_bind_table(sql, s, "describe_accessible_tables"))
!= NULL)
+ t->system = 0; /* make it non-system else the drop view
will fail */
printf("Running database upgrade commands:\n%s\n", stmt1);
fflush(stdout);
err = SQLstatementIntern(c, stmt1, "update", true, false, NULL);
diff --git a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
--- a/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
+++ b/sql/test/Dependencies/Tests/dependency_owner_schema_3.test
@@ -213,9 +213,6 @@ db_user_info
dump_create_users
DEP_VIEW
db_user_info
-roles
-DEP_VIEW
-db_user_info
users
DEP_VIEW
dependencies
diff --git
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
---
a/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
+++
b/sql/test/emptydb-previous-upgrade-chain-hge/Tests/upgrade.stable.out.int128
@@ -256,3 +256,25 @@ update sys.functions set system = true w
Running database upgrade commands:
delete from sys.dependencies where id in (2004, 2005, 2006) and depend_id in
(select id from sys._tables where name in ('check_constraints',
'table_constraints') and schema_id = (select id from sys.schemas where name =
'information_schema'));
+Running database upgrade commands:
+DROP VIEW sys.describe_accessible_tables CASCADE;
+DROP VIEW sys.roles CASCADE;
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths;
+GRANT SELECT ON sys.roles TO PUBLIC;
+CREATE VIEW sys.describe_accessible_tables AS
+ SELECT
+ schemas.name AS schema,
+ tables.name AS table,
+ tt.table_type_name AS table_type,
+ pc.privilege_code_name AS privs,
+ p.privileges AS privs_code
+ FROM privileges p
+ JOIN sys.roles ON p.auth_id = roles.id
+ JOIN sys.tables ON p.obj_id = tables.id
+ JOIN sys.table_types tt ON tables.type = tt.table_type_id
+ JOIN sys.schemas ON tables.schema_id = schemas.id
+ JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
+ WHERE roles.name = current_role;
+GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE not system and schema_id = 2000 and
name in ('roles', 'describe_accessible_tables');
+
diff --git
a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-chain/Tests/upgrade.stable.out.int128
@@ -341,3 +341,25 @@ update sys.functions set system = true w
Running database upgrade commands:
delete from sys.dependencies where id in (2004, 2005, 2006) and depend_id in
(select id from sys._tables where name in ('check_constraints',
'table_constraints') and schema_id = (select id from sys.schemas where name =
'information_schema'));
+Running database upgrade commands:
+DROP VIEW sys.describe_accessible_tables CASCADE;
+DROP VIEW sys.roles CASCADE;
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths;
+GRANT SELECT ON sys.roles TO PUBLIC;
+CREATE VIEW sys.describe_accessible_tables AS
+ SELECT
+ schemas.name AS schema,
+ tables.name AS table,
+ tt.table_type_name AS table_type,
+ pc.privilege_code_name AS privs,
+ p.privileges AS privs_code
+ FROM privileges p
+ JOIN sys.roles ON p.auth_id = roles.id
+ JOIN sys.tables ON p.obj_id = tables.id
+ JOIN sys.table_types tt ON tables.type = tt.table_type_id
+ JOIN sys.schemas ON tables.schema_id = schemas.id
+ JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
+ WHERE roles.name = current_role;
+GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE not system and schema_id = 2000 and
name in ('roles', 'describe_accessible_tables');
+
diff --git
a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -256,3 +256,25 @@ update sys.functions set system = true w
Running database upgrade commands:
delete from sys.dependencies where id in (2004, 2005, 2006) and depend_id in
(select id from sys._tables where name in ('check_constraints',
'table_constraints') and schema_id = (select id from sys.schemas where name =
'information_schema'));
+Running database upgrade commands:
+DROP VIEW sys.describe_accessible_tables CASCADE;
+DROP VIEW sys.roles CASCADE;
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths;
+GRANT SELECT ON sys.roles TO PUBLIC;
+CREATE VIEW sys.describe_accessible_tables AS
+ SELECT
+ schemas.name AS schema,
+ tables.name AS table,
+ tt.table_type_name AS table_type,
+ pc.privilege_code_name AS privs,
+ p.privileges AS privs_code
+ FROM privileges p
+ JOIN sys.roles ON p.auth_id = roles.id
+ JOIN sys.tables ON p.obj_id = tables.id
+ JOIN sys.table_types tt ON tables.type = tt.table_type_id
+ JOIN sys.schemas ON tables.schema_id = schemas.id
+ JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
+ WHERE roles.name = current_role;
+GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE not system and schema_id = 2000 and
name in ('roles', 'describe_accessible_tables');
+
diff --git a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-previous-upgrade/Tests/upgrade.stable.out.int128
@@ -341,3 +341,25 @@ update sys.functions set system = true w
Running database upgrade commands:
delete from sys.dependencies where id in (2004, 2005, 2006) and depend_id in
(select id from sys._tables where name in ('check_constraints',
'table_constraints') and schema_id = (select id from sys.schemas where name =
'information_schema'));
+Running database upgrade commands:
+DROP VIEW sys.describe_accessible_tables CASCADE;
+DROP VIEW sys.roles CASCADE;
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths;
+GRANT SELECT ON sys.roles TO PUBLIC;
+CREATE VIEW sys.describe_accessible_tables AS
+ SELECT
+ schemas.name AS schema,
+ tables.name AS table,
+ tt.table_type_name AS table_type,
+ pc.privilege_code_name AS privs,
+ p.privileges AS privs_code
+ FROM privileges p
+ JOIN sys.roles ON p.auth_id = roles.id
+ JOIN sys.tables ON p.obj_id = tables.id
+ JOIN sys.table_types tt ON tables.type = tt.table_type_id
+ JOIN sys.schemas ON tables.schema_id = schemas.id
+ JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
+ WHERE roles.name = current_role;
+GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE not system and schema_id = 2000 and
name in ('roles', 'describe_accessible_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
@@ -0,0 +1,22 @@
+Running database upgrade commands:
+DROP VIEW sys.describe_accessible_tables CASCADE;
+DROP VIEW sys.roles CASCADE;
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths;
+GRANT SELECT ON sys.roles TO PUBLIC;
+CREATE VIEW sys.describe_accessible_tables AS
+ SELECT
+ schemas.name AS schema,
+ tables.name AS table,
+ tt.table_type_name AS table_type,
+ pc.privilege_code_name AS privs,
+ p.privileges AS privs_code
+ FROM privileges p
+ JOIN sys.roles ON p.auth_id = roles.id
+ JOIN sys.tables ON p.obj_id = tables.id
+ JOIN sys.table_types tt ON tables.type = tt.table_type_id
+ JOIN sys.schemas ON tables.schema_id = schemas.id
+ JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
+ WHERE roles.name = current_role;
+GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE not system and schema_id = 2000 and
name in ('roles', 'describe_accessible_tables');
+
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
@@ -83,3 +83,25 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name in
('stddev_samp', 'stddev_pop', 'var_samp', 'covar_samp', 'var_pop', 'covar_pop',
'corr') and schema_id = 2000 and type = 6;
update sys.functions set system = true where system <> true and name =
'filter' and schema_id = (select id from sys.schemas where name = 'json') and
type = 1;
+Running database upgrade commands:
+DROP VIEW sys.describe_accessible_tables CASCADE;
+DROP VIEW sys.roles CASCADE;
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths;
+GRANT SELECT ON sys.roles TO PUBLIC;
+CREATE VIEW sys.describe_accessible_tables AS
+ SELECT
+ schemas.name AS schema,
+ tables.name AS table,
+ tt.table_type_name AS table_type,
+ pc.privilege_code_name AS privs,
+ p.privileges AS privs_code
+ FROM privileges p
+ JOIN sys.roles ON p.auth_id = roles.id
+ JOIN sys.tables ON p.obj_id = tables.id
+ JOIN sys.table_types tt ON tables.type = tt.table_type_id
+ JOIN sys.schemas ON tables.schema_id = schemas.id
+ JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
+ WHERE roles.name = current_role;
+GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE not system and schema_id = 2000 and
name in ('roles', 'describe_accessible_tables');
+
diff --git a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade-hge/Tests/upgrade.stable.out.int128
@@ -0,0 +1,22 @@
+Running database upgrade commands:
+DROP VIEW sys.describe_accessible_tables CASCADE;
+DROP VIEW sys.roles CASCADE;
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths;
+GRANT SELECT ON sys.roles TO PUBLIC;
+CREATE VIEW sys.describe_accessible_tables AS
+ SELECT
+ schemas.name AS schema,
+ tables.name AS table,
+ tt.table_type_name AS table_type,
+ pc.privilege_code_name AS privs,
+ p.privileges AS privs_code
+ FROM privileges p
+ JOIN sys.roles ON p.auth_id = roles.id
+ JOIN sys.tables ON p.obj_id = tables.id
+ JOIN sys.table_types tt ON tables.type = tt.table_type_id
+ JOIN sys.schemas ON tables.schema_id = schemas.id
+ JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
+ WHERE roles.name = current_role;
+GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE not system and schema_id = 2000 and
name in ('roles', 'describe_accessible_tables');
+
diff --git a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
--- a/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
+++ b/sql/test/emptydb-upgrade/Tests/upgrade.stable.out.int128
@@ -83,3 +83,25 @@ update sys.functions set system = true w
update sys.functions set system = true where system <> true and name in
('stddev_samp', 'stddev_pop', 'var_samp', 'covar_samp', 'var_pop', 'covar_pop',
'corr') and schema_id = 2000 and type = 6;
update sys.functions set system = true where system <> true and name =
'filter' and schema_id = (select id from sys.schemas where name = 'json') and
type = 1;
+Running database upgrade commands:
+DROP VIEW sys.describe_accessible_tables CASCADE;
+DROP VIEW sys.roles CASCADE;
+CREATE VIEW sys.roles AS SELECT id, name, grantor FROM sys.auths;
+GRANT SELECT ON sys.roles TO PUBLIC;
+CREATE VIEW sys.describe_accessible_tables AS
+ SELECT
+ schemas.name AS schema,
+ tables.name AS table,
+ tt.table_type_name AS table_type,
+ pc.privilege_code_name AS privs,
+ p.privileges AS privs_code
+ FROM privileges p
+ JOIN sys.roles ON p.auth_id = roles.id
+ JOIN sys.tables ON p.obj_id = tables.id
+ JOIN sys.table_types tt ON tables.type = tt.table_type_id
+ JOIN sys.schemas ON tables.schema_id = schemas.id
+ JOIN sys.privilege_codes pc ON p.privileges = pc.privilege_code_id
+ WHERE roles.name = current_role;
+GRANT SELECT ON sys.describe_accessible_tables TO PUBLIC;
+UPDATE sys._tables SET system = true WHERE not system and schema_id = 2000 and
name in ('roles', 'describe_accessible_tables');
+
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
@@ -560,7 +560,7 @@ select 'null in fkeys.delete_action', de
[ "sys._tables", "sys", "range_partitions", NULL, "TABLE",
true, "COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "rejects", "create view sys.rejects as
select * from sys.rejects();", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
[ "sys._tables", "sys", "remote_user_info", NULL, "TABLE",
true, "COMMIT", "WRITABLE", NULL ]
-[ "sys._tables", "sys", "roles", "create view sys.roles as
select id, name, grantor from sys.auths a where a.name not in (select u.name
from sys.db_user_info u);", "VIEW", true, "COMMIT", "WRITABLE",
NULL ]
+[ "sys._tables", "sys", "roles", "create view sys.roles as
select id, name, grantor from sys.auths;", "VIEW", true, "COMMIT",
"WRITABLE", NULL ]
[ "sys._tables", "sys", "schemas", NULL, "TABLE", true,
"COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "schemastorage", "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\";", "VIEW", true,
"COMMIT", "WRITABLE", NULL ]
[ "sys._tables", "sys", "sequences", NULL, "TABLE", true,
"COMMIT", "WRITABLE", NULL ]
@@ -3572,7 +3572,6 @@ select 'null in fkeys.delete_action', de
[ "table used by view", "tmp", "keys", "sys", "ids", "VIEW" ]
[ "table used by view", "tmp", "triggers", "sys", "ids", "VIEW"
]
[ "table used by view", "sys", "auths", "sys", "roles",
"VIEW" ]
-[ "table used by view", "sys", "db_user_info", "sys", "roles",
"VIEW" ]
[ "table used by view", "sys", "storage", "sys",
"schemastorage", "VIEW" ]
[ "table used by view", "sys", "schemas", "sys", "statistics",
"VIEW" ]
[ "table used by view", "sys", "tables", "sys", "statistics",
"VIEW" ]
@@ -4439,7 +4438,6 @@ select 'null in fkeys.delete_action', de
[ "column used by view", "sys", "auths", "grantor", "sys",
"roles", "VIEW" ]
[ "column used by view", "sys", "auths", "id", "sys",
"roles", "VIEW" ]
[ "column used by view", "sys", "auths", "name", "sys",
"roles", "VIEW" ]
-[ "column used by view", "sys", "db_user_info", "name", "sys",
"roles", "VIEW" ]
_______________________________________________
checkin-list mailing list -- [email protected]
To unsubscribe send an email to [email protected]
