Index: openacs-4/packages/acs-kernel/sql/postgresql/rel-constraints-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/rel-constraints-create.sql,v
diff -u -r1.3 -r1.4
--- openacs-4/packages/acs-kernel/sql/postgresql/rel-constraints-create.sql	14 Mar 2001 14:07:31 -0000	1.3
+++ openacs-4/packages/acs-kernel/sql/postgresql/rel-constraints-create.sql	15 Mar 2001 01:37:48 -0000	1.4
@@ -200,20 +200,21 @@
 --   and rspm.party_id(+) = constrained_rels.container_id
 --   and rspm.party_id is null;
 
-
+create view constrained_rels1 as 
+select rel.constraint_id, rel.constraint_name, 
+       r.rel_id, r.container_id, r.party_id, r.rel_type, 
+       rel.rel_segment,
+       rel.rel_side, 
+       rel.required_rel_segment
+  from rel_constraints rel, rel_segment_party_map r
+ where rel.rel_side = 'one'
+   and rel.rel_segment = r.segment_id;
+     
 create view rel_constraints_violated_one as
-select constrained_rels.*
-from (select rel_constraints.constraint_id, rel_constraints.constraint_name, 
-             r.rel_id, r.container_id, r.party_id, r.rel_type, 
-             rel_constraints.rel_segment,
-             rel_constraints.rel_side, 
-             rel_constraints.required_rel_segment
-      from rel_constraints, rel_segment_party_map r
-      where rel_constraints.rel_side = 'one'
-        and rel_constraints.rel_segment = r.segment_id
-     ) constrained_rels left outer join rel_segment_party_map rspm 
-       on (rspm.segment_id = constrained_rels.required_rel_segment and 
-           rspm.party_id = constrained_rels.container_id)
+select c.*
+from   constrained_rels1 c left outer join rel_segment_party_map rspm 
+       on (rspm.segment_id = c.required_rel_segment and 
+           rspm.party_id = c.container_id)
 where rspm.party_id is null;
 
 
@@ -263,19 +264,22 @@
 --   and rspm.party_id(+) = constrained_rels.party_id
 --   and rspm.party_id is null;
 
+create view constrained_rels2 as 
+select rel.constraint_id, rel.constraint_name, 
+       r.rel_id, r.container_id, r.party_id, r.rel_type, 
+       rel.rel_segment,
+       rel.rel_side, 
+       rel.required_rel_segment
+  from rel_constraints rel, rel_segment_party_map r
+ where rel.rel_side = 'two'
+   and rel.rel_segment = r.segment_id;
+     
+
 create view rel_constraints_violated_two as
-select constrained_rels.*
-from (select rel_constraints.constraint_id, rel_constraints.constraint_name, 
-             r.rel_id, r.container_id, r.party_id, r.rel_type, 
-             rel_constraints.rel_segment,
-             rel_constraints.rel_side, 
-             rel_constraints.required_rel_segment
-      from rel_constraints, rel_segment_party_map r
-      where rel_constraints.rel_side = 'two'
-        and rel_constraints.rel_segment = r.segment_id
-     ) constrained_rels left outer join rel_segment_party_map rspm 
-       on (rspm.segment_id = constrained_rels.required_rel_segment and
-           rspm.party_id = constrained_rels.party_id)
+select c.*
+from  constrained_rels2 c left outer join rel_segment_party_map rspm 
+       on (rspm.segment_id = c.required_rel_segment and
+           rspm.party_id = c.party_id)
 where rspm.party_id is null;
 
 -- Originally, we tried this view.  It was slow.  The one above is much
@@ -424,15 +428,23 @@
 --   and rc_required_rel_segments.rel_type(+) = group_rel_type_combos.rel_type
 --   and rc_required_rel_segments.group_id is null;
 
+create view comp_or_member_rel_types as 
+select object_type as rel_type 
+  from acs_object_types
+ where tree_sortkey like (select o.tree_sortkey || '%' 
+                            from acs_object_types o
+                           where o.object_type = 'composition_rel')
+    or tree_sortkey like (select o.tree_sortkey || '%' 
+                            from acs_object_types o
+                           where o.object_type = 'membership_rel');
 
+create view group_rel_type_combos as 
+select groups.group_id, comp_or_member_rel_types.rel_type
+       from groups, comp_or_member_rel_types;
 
 
-create view rc_parties_in_required_segs as
-select parties_in_required_segs.group_id,
-       parties_in_required_segs.rel_type,
-       parties_in_required_segs.party_id
-from
-   (select required_segs.group_id, 
+create view parties_in_required_segs as 
+select required_segs.group_id, 
            required_segs.rel_type, 
            seg_parties.party_id,
            count(*) as num_matching_segs
@@ -441,10 +453,20 @@
     where required_segs.required_rel_segment = seg_parties.segment_id
     group by required_segs.group_id, 
              required_segs.rel_type, 
-             seg_parties.party_id) parties_in_required_segs,
-   (select group_id, rel_type, count(*) as total
-    from rc_required_rel_segments
-    group by group_id, rel_type) total_num_required_segs
+             seg_parties.party_id;
+
+create view total_num_required_segs as 
+select group_id, rel_type, count(*) as total
+  from rc_required_rel_segments
+ group by group_id, rel_type;
+
+create view rc_parties_in_required_segs as
+select parties_in_required_segs.group_id,
+       parties_in_required_segs.rel_type,
+       parties_in_required_segs.party_id
+from
+    parties_in_required_segs,
+    total_num_required_segs
 where
       parties_in_required_segs.group_id = total_num_required_segs.group_id
   and parties_in_required_segs.rel_type = total_num_required_segs.rel_type
@@ -453,18 +475,9 @@
 select group_rel_type_combos.group_id,
        group_rel_type_combos.rel_type,
        parties.party_id
-from (rc_required_rel_segments right outer join  
-      (select groups.group_id, comp_or_member_rel_types.rel_type
-       from groups,
-            (select object_type as rel_type 
-               from acs_object_types
-              where tree_sortkey like (select o.tree_sortkey || '%' 
-                                         from acs_object_types o
-                                        where o.object_type = 'composition_rel')
-                 or tree_sortkey like (select o.tree_sortkey || '%' 
-                                         from acs_object_types o
-                                        where o.object_type = 'membership_rel')) comp_or_member_rel_types
-      ) group_rel_type_combos on (rc_required_rel_segments.group_id = group_rel_type_combos.group_id and rc_required_rel_segments.rel_type = group_rel_type_combos.rel_type)), parties
+from (rc_required_rel_segments right outer join group_rel_type_combos 
+      on (rc_required_rel_segments.group_id = group_rel_type_combos.group_id and 
+          rc_required_rel_segments.rel_type = group_rel_type_combos.rel_type)), parties
 where rc_required_rel_segments.group_id is null;
 
 
@@ -517,12 +530,8 @@
 --   and rc_all_constraints.rel_type(+) = group_rel_type_combos.rel_type
 --   and rc_all_constraints.group_id is null;
 
-
-
-create view rc_valid_rel_types as
-select side_one_constraints.group_id, 
-       side_one_constraints.rel_type
-  from (select required_segs.group_id, 
+create view side_one_constraints as 
+select required_segs.group_id, 
                required_segs.rel_type, 
                count(*) as num_satisfied
           from rc_all_constraints required_segs,
@@ -531,34 +540,35 @@
            and required_segs.required_rel_segment = map.segment_id
            and required_segs.group_id = map.party_id
         group by required_segs.group_id, 
-                 required_segs.rel_type) side_one_constraints,
-       (select group_id, rel_type, count(*) as total
-          from rc_all_constraints
-         where rel_side = 'one'
-        group by group_id, rel_type) total_side_one_constraints
+                 required_segs.rel_type;
+
+create view total_side_one_constraints as 
+select group_id, rel_type, count(*) as total
+  from rc_all_constraints
+ where rel_side = 'one'
+ group by group_id, rel_type;
+
+create view rc_all_constraints_view as 
+select * from rc_all_constraints where rel_side='one';
+
+create view rc_valid_rel_types as
+select side_one_constraints.group_id, 
+       side_one_constraints.rel_type
+  from side_one_constraints,
+       total_side_one_constraints
  where side_one_constraints.group_id = total_side_one_constraints.group_id
    and side_one_constraints.rel_type = total_side_one_constraints.rel_type
    and side_one_constraints.num_satisfied = total_side_one_constraints.total
 UNION ALL
 select group_rel_type_combos.group_id,
        group_rel_type_combos.rel_type
-from (select * from rc_all_constraints where rel_side='one') rc_all_constraints
-     right outer join 
-     (select groups.group_id, comp_or_member_rel_types.rel_type
-      from groups, 
-           (select object_type as rel_type 
-               from acs_object_types
-              where tree_sortkey like (select o.tree_sortkey || '%' 
-                                         from acs_object_types o
-                                        where o.object_type = 'composition_rel')
-                 or tree_sortkey like (select o.tree_sortkey || '%' 
-                                         from acs_object_types o
-                                        where o.object_type = 'membership_rel')) comp_or_member_rel_types
-     ) group_rel_type_combos on 
-       (rc_all_constraints.group_id = group_rel_type_combos.group_id and 
-        rc_all_constraints.rel_type = group_rel_type_combos.rel_type)
-where rc_all_constraints.group_id is null;
+from rc_all_constraints_view right outer join group_rel_type_combos
+      on 
+     (rc_all_constraints_view.group_id = group_rel_type_combos.group_id and 
+      rc_all_constraints_view.rel_type = group_rel_type_combos.rel_type)
+where rc_all_constraints_view.group_id is null;
 
+
 -- View: rc_violations_by_removing_rel
 --
 -- Question: Given relation :rel_id
@@ -626,14 +636,17 @@
 select rc.rel_segment, rc.rel_side, rc_required.required_rel_segment
 from rel_constraints rc, rel_constraints rc_required 
 where rc.rel_segment in (
-          select rel_segment
-            from rel_constraints
-           where tree_sortkey 
+          select c.rel_segment
+            from rel_constraints c
+           where ('two' = (select rel_side 
+                             from rel_constraints 
+                            where rel_segment = c.required_rel_segment)
+                  or c.rel_segment = rc_required.rel_segment)         
+             and c.tree_sortkey 
                  like 
                  (select tree_sortkey || '%'
                     from rel_constraints
-                   where rel_segment = rc_required.rel_segment 
-                     and rel_side = 'two'));
+                   where rel_segment = rc_required.rel_segment));
 
 -- View: rc_segment_dependency_levels
 --
@@ -678,13 +691,15 @@
 -- DCW 2001-03-14.
 
 create view rc_segment_dependency_levels as
-      select rc.rel_segment as segment_id,
-             max(rc.tree_level) as dependency_level
-       from (select rc1.rel_segment, tree_level(rc1.tree_sortkey) as tree_level
+             select rc1.rel_segment as segment_id, 
+                    max(tree_level(rc1.tree_sortkey)) as dependency_level
                from rel_constraints rc1, rel_constraints rc2
-              where rc1.tree_sortkey like rc2.tree_sortkey || '%'
-                and rc2.rel_side = 'two') rc
-   group by segment_id;
+              where ('two' = (select rel_side 
+                                from rel_constraints 
+                               where rel_segment = rc1.required_rel_segment)
+                     or rc1.rel_segment = rc2.rel_segment)     
+                and rc1.tree_sortkey like rc2.tree_sortkey || '%'
+           group by segment_id;
 
 --------------
 -- PACKAGES --
