Forum OpenACS Development: Hierarchical query in Oracle - prvileges tree

Request notifications

I'm trying to display the tree of all existing acs_privileges. It's easy in postgresql using the helper table acs_privilege_hierarchy_index, but in oracle there are only the two tables: acs_privileges and acs_privilege_hierarchy.

The first one contains a single row for each privilege, and the second one contains one row for each parent-child relationship, e.g. one for admin->read. That means that there is no row for the leaf nodes (those that have no children) in the acs_privilege_hierarchy table, they only have one entry in acs_privileges. I need a query that returns one row for each privilege, including the hierarchy level and in the order of the hierarchy.

Therefor I created a join that produces a resultset with a parent_privilege field which is null in case the row represents a top level node. Trying to do a 'connect by' on this query fails though:

select q.privilege, level from
(select p.privilege, h.privilege as parent_privilege
from acs_privileges p, acs_privilege_hierarchy h
where p.privilege=h.child_privilege(+) ) q
connect by prior q.privilege=q.parent_privilege;

with this error:

ERROR at line 1:
ORA-01437: cannot have join with CONNECT BY

Google says this restriction was removed with 9i, but I couldn't find a suggestion how to circumvent it for this particular case when 8i has to be used.

Any ideas how I can get the privileges tree with the current datamodel using oracle 8i?

Collapse
Posted by Michael Hinds on
Do you know how to get what you want just using acs_privilege_hierarchy? I think you need to give it something to START WITH for it to give you a result set close to what you're looking for.

Can you try this and tell me if it's close:

select 
  tree.tree_level,
  tree.privilege,
  p.privilege as child_privilege
from
  acs_privileges p,
  (select
     level as tree_level,
	 privilege,
	 child_privilege   
   from 
     acs_privilege_hierarchy 
   connect by privilege = prior child_privilege
     start with privilege = 'admin'
  ) tree
where 
  tree.child_privilege(+) = p.privilege
order by tree_level,tree.privilege

Collapse
Posted by Tilmann Singer on
Thanks for the suggestion - this way I don't get the error message anymore, although it doesn't return yet what is needed.

There are possibly more then one root node, so starting with 'admin' is not sufficient. Adding a 'start with privilege in (select ...)' clause that contains all such root nodes is possible.

The problem remains though that the inner query (the one with the CONTAINS) does not return an extra row for the leaf node, so the joined rows from acs_privileges do not appear in the correct order.

This is what your query returns

TREE_LEVEL PRIVILEGE            CHILD_PRIVILEGE
---------- -------------------- --------------------
         1 one_top_level        sub_level
         2 sub_level            sub_sub_level
                                another_top_level
                                one_top_level
This is from a test schema with testing data in it, that contains this:
one_top_level
  |
  +- sub_level
      |
      + sub_sub_level

another_top_level
I modified your query to fit the testing data:
select
  tree.tree_level,
  tree.privilege,
  p.privilege as child_privilege
from
  test_acs_privileges p,
  (select
     level as tree_level,
         privilege,
         child_privilege
   from
     test_acs_privilege_hierarchy
   connect by privilege = prior child_privilege
     start with privilege in ('one_top_level', 'another_top_level')
  ) tree
where
  tree.child_privilege(+) = p.privilege
order by tree_level,tree.privilege

Collapse
Posted by Michael Hinds on
Is it just the order that's bothering you? That's easily fixed:
select
  nvl(tree.tree_level,0) as tree_level,
  tree.privilege,
  p.privilege as child_privilege
from
  test_acs_privileges p,
  (select
     level as tree_level,
         privilege,
         child_privilege
   from
     test_acs_privilege_hierarchy
   connect by privilege = prior child_privilege
     start with privilege in ('one_top_level', 'another_top_level')
  ) tree
where
  tree.child_privilege(+) = p.privilege
order by tree_level,tree.privilege
Collapse
Posted by Tilmann Singer on
Well I'd like to display the tree the way it is, not orderd by levels. This modification returns:

TREE_LEVEL PRIVILEGE            CHILD_PRIVILEGE
---------- -------------------- --------------------
         0                      another_top_level
         0                      one_top_level
         1 one_top_level        sub_level
         2 sub_level            sub_sub_level

but the two first lines are not determined by the hierarchy but by hte alphabetical ordering of their names, e.g. if the parent of 'sub_level' was 'another_top_level', it would still appear on that position which is wrong.
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 ...

Collapse
Posted by Don Baccus on
I believe materialized views are an Enterprise Edition feature?  Can someone check that?

If so you can't put it in the toolkit proper 'cause we're still committed to supporting Standard Edition due to the difference in licensing costs.

Collapse
Posted by Janine Ohmer on
Unfortunately, materialized views are only available in the Enterprise Edition ($$$) and we have always tried to stay compatible with the Standard Edition.

I tried to find some proof of this on the Oracle site but couldn't find any sort of feature comparison; however, this article, from Google, confirms it at the end.

Collapse
Posted by Kevin Crosbie on
I don't have OpenACS installed here so I can't test this, but would replacing the order by on the outer query with 'order siblings by privelege' (you might need to play around with what you are ordering by) on the inner query fix your problem?
Collapse
Posted by Kevin Crosbie on
Oh wait, 8i.  Sorry.  Didn't see that.
Order siblings by isn't available in 8i of course...