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

Collapse
Posted by Michael Hinds on
Sorry Tilmann, I think it's beat me! In this situation I'd write a proc to do it, using an outer SELECT that goes through the top level privileges and an inner one that STARTs WITH the top level priv.
Collapse
Posted by Tilmann Singer on
The only solution I see currently is to do a hack and build the sorted list in tcl after querying all the rows from both tables - ugh. Given that there will never be many entries in these tables it won't be a performance problem, but it's very ugly.

I wonder why the datamodel was created that way in the first place - would there have been any problem just adding a parent_privilege field to acs_privileges which would have been null in case of a root node?

Collapse
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
as
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 ...