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?

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

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.

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?

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.

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?