Index: openacs-4/packages/dotlrn/www/communities-chunk-oracle.xql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/dotlrn/www/communities-chunk-oracle.xql,v
diff -u -r1.10 -r1.11
--- openacs-4/packages/dotlrn/www/communities-chunk-oracle.xql	1 May 2002 01:19:47 -0000	1.10
+++ openacs-4/packages/dotlrn/www/communities-chunk-oracle.xql	31 May 2002 06:23:49 -0000	1.11
@@ -19,12 +19,10 @@
                    dotlrn_communities.package_id,
                    dotlrn_community.url(dotlrn_communities.community_id) as url,
                    1 as member_p,
-                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'),'f',0,1) as admin_p,
-                   (select community_type
+                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'), 'f', 0, 1) as admin_p,
+                   (select dotlrn_community_types.community_type
                     from dotlrn_community_types
-                    where supertype = 'dotlrn_community'
-                    start with community_type = dotlrn_communities.community_type
-                    connect by community_type = prior supertype) as root_community_type
+                    where dotlrn_community_types.tree_sortkey = tree.ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type
             from dotlrn_active_communities dotlrn_communities,
                  dotlrn_member_rels_approved
             where dotlrn_member_rels_approved.user_id = :user_id
@@ -44,12 +42,10 @@
                    dotlrn_communities.package_id,
                    dotlrn_community.url(dotlrn_communities.community_id) as url,
                    1 as member_p,
-                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'),'f',0,1) as admin_p,
-                   (select community_type
+                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'), 'f', 0, 1) as admin_p,
+                   (select dotlrn_community_types.community_type
                     from dotlrn_community_types
-                    where supertype = 'dotlrn_community'
-                    start with community_type = dotlrn_communities.community_type
-                    connect by community_type = prior supertype) as root_community_type
+                    where dotlrn_community_types.tree_sortkey = tree.ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type
             from dotlrn_communities dotlrn_communities,
                  dotlrn_member_rels_approved
             where dotlrn_member_rels_approved.user_id = :user_id
@@ -69,12 +65,10 @@
                    dotlrn_communities.package_id,
                    dotlrn_community.url(dotlrn_communities.community_id) as url,
                    0 as member_p,
-                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'),'f',0,1) as admin_p,
-                   (select community_type
+                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'), 'f', 0, 1) as admin_p,
+                   (select dotlrn_community_types.community_type
                     from dotlrn_community_types
-                    where supertype = 'dotlrn_community'
-                    start with community_type = dotlrn_communities.community_type
-                    connect by community_type = prior supertype) as root_community_type
+                    where dotlrn_community_types.tree_sortkey = tree.ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type
             from dotlrn_active_comms_not_closed dotlrn_communities
             where not exists (select 1
                               from dotlrn_member_rels_full
@@ -95,12 +89,10 @@
                    dotlrn_communities.package_id,
                    dotlrn_community.url(dotlrn_communities.community_id) as url,
                    0 as member_p,
-                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'),'f',0,1) as admin_p,
-                   (select community_type
+                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'), 'f', 0, 1) as admin_p,
+                   (select dotlrn_community_types.community_type
                     from dotlrn_community_types
-                    where supertype = 'dotlrn_community'
-                    start with community_type = dotlrn_communities.community_type
-                    connect by community_type = prior supertype) as root_community_type
+                    where dotlrn_community_types.tree_sortkey = tree.ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type
             from dotlrn_communities_not_closed dotlrn_communities
             where not exists (select 1
                               from dotlrn_member_rels_full
@@ -130,12 +122,10 @@
                    dotlrn_communities.package_id,
                    dotlrn_community.url(dotlrn_communities.community_id) as url,
                    1 as member_p,
-                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'),'f',0,1) as admin_p,
-                   (select community_type
+                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'), 'f', 0, 1) as admin_p,
+                   (select dotlrn_community_types.community_type
                     from dotlrn_community_types
-                    where supertype = 'dotlrn_community'
-                    start with community_type = dotlrn_communities.community_type
-                    connect by community_type = prior supertype) as root_community_type
+                    where dotlrn_community_types.tree_sortkey = tree.ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type
             from dotlrn_active_communities dotlrn_communities,
                  dotlrn_member_rels_approved
             where dotlrn_member_rels_approved.user_id = :user_id
@@ -156,12 +146,10 @@
                    dotlrn_communities.package_id,
                    dotlrn_community.url(dotlrn_communities.community_id) as url,
                    1 as member_p,
-                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'),'f',0,1) as admin_p,
-                   (select community_type
+                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'), 'f', 0, 1) as admin_p,
+                   (select dotlrn_community_types.community_type
                     from dotlrn_community_types
-                    where supertype = 'dotlrn_community'
-                    start with community_type = dotlrn_communities.community_type
-                    connect by community_type = prior supertype) as root_community_type
+                    where dotlrn_community_types.tree_sortkey = tree.ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type
             from dotlrn_communities dotlrn_communities,
                  dotlrn_member_rels_approved
             where dotlrn_member_rels_approved.user_id = :user_id
@@ -182,12 +170,10 @@
                    dotlrn_communities.package_id,
                    dotlrn_community.url(dotlrn_communities.community_id) as url,
                    0 as member_p,
-                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'),'f',0,1) as admin_p,
-                   (select community_type
+                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'), 'f', 0, 1) as admin_p,
+                   (select dotlrn_community_types.community_type
                     from dotlrn_community_types
-                    where supertype = 'dotlrn_community'
-                    start with community_type = dotlrn_communities.community_type
-                    connect by community_type = prior supertype) as root_community_type
+                    where dotlrn_community_types.tree_sortkey = tree.ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type
             from dotlrn_active_comms_not_closed dotlrn_communities
             where not exists (select 1
                               from dotlrn_member_rels_full
@@ -209,12 +195,10 @@
                    dotlrn_communities.package_id,
                    dotlrn_community.url(dotlrn_communities.community_id) as url,
                    0 as member_p,
-                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'),'f',0,1) as admin_p,
-                   (select community_type
+                   decode(acs_permission.permission_p(:user_id, dotlrn_communities.community_id, 'admin'), 'f', 0, 1) as admin_p,
+                   (select dotlrn_community_types.community_type
                     from dotlrn_community_types
-                    where supertype = 'dotlrn_community'
-                    start with community_type = dotlrn_communities.community_type
-                    connect by community_type = prior supertype) as root_community_type
+                    where dotlrn_community_types.tree_sortkey = tree.ancestor_key(dotlrn_communities.tree_sortkey, 1)) as root_community_type
             from dotlrn_communities_not_closed dotlrn_communities
             where not exists (select 1
                               from dotlrn_member_rels_full