Forum OpenACS Development: Re: Hierarchical query in Oracle - prvileges tree

Posted by Tilmann Singer on
I just found out a way to work around it: create a materialized view.

create materialized view acs_privilege_helper
refresh on commit
select p.privilege, h.privilege as parent_privilege
from acs_privileges p, acs_privilege_hierarchy h
where p.privilege=h.child_privilege(+)

The resulting object acs_privilege_helper can be used like a table and the connect by works just fine on it.

Any objections on adding this view to acs-kernel? Drawbacks:

1) slight performance hit when inserting new privileges - since this happens almost never it's not a problem.

2) A maintainability nuisance: I had to grant the oracle user the right to create materialized view like this before I could create it:

grant create materialized view to myuser;
I wonder how this is set by default in normal oracle installations and if it would bother everyone upgrading.

Benefit: a beautiful permission grant page ...