Index: openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql
===================================================================
RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql,v
diff -u -r1.36 -r1.37
--- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql	7 Aug 2017 23:47:56 -0000	1.36
+++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql	3 Sep 2024 15:37:33 -0000	1.37
@@ -41,7 +41,7 @@
   v_error         text;
   map             record;
 BEGIN
-  
+
   -- First check if added this relation violated any relational constraints
   v_error := rel_constraint__violation(new.rel_id);
   if v_error is not null then
@@ -56,10 +56,10 @@
 
   -- Insert a row for me in the group_element_index.
   insert into group_element_index
-   (group_id, element_id, rel_id, container_id, 
+   (group_id, element_id, rel_id, container_id,
     rel_type, ancestor_rel_type)
   values
-   (v_object_id_one, v_object_id_two, new.rel_id, v_object_id_one, 
+   (v_object_id_one, v_object_id_two, new.rel_id, v_object_id_one,
     v_rel_type, 'membership_rel');
 
   if new.member_state = 'approved' then
@@ -72,8 +72,8 @@
      -- For all groups of which I am a component, insert a
      -- row in the group_element_index.
      for map in select distinct group_id
-	      from group_component_map
-	      where component_id = v_object_id_one 
+              from group_component_map
+              where component_id = v_object_id_one
      loop
 
         insert into group_element_index
@@ -186,7 +186,7 @@
   v_error         text;
   map             record;
 BEGIN
-  
+
   -- First check if added this relation violated any relational constraints
   v_error := rel_constraint__violation(new.rel_id);
 
@@ -213,10 +213,10 @@
   from group_approved_member_map m
   where group_id = v_object_id_two
   and not exists (select 1
-		  from group_element_map
-		  where group_id = v_object_id_one
-		  and element_id = m.member_id
-		  and rel_id = m.rel_id);
+                  from group_element_map
+                  where group_id = v_object_id_one
+                  and element_id = m.member_id
+                  and rel_id = m.rel_id);
 
   -- Make my composable elements be elements of my new composite group
   insert into group_element_index
@@ -230,16 +230,16 @@
   where group_id = v_object_id_two
   and t.composable_p = 't'
   and not exists (select 1
-		  from group_element_map
-		  where group_id = v_object_id_one
-		  and element_id = m.element_id
-		  and rel_id = m.rel_id);
+                  from group_element_map
+                  where group_id = v_object_id_one
+                  and element_id = m.element_id
+                  and rel_id = m.rel_id);
 
-  -- For all direct or indirect containers of my new composite group, 
+  -- For all direct or indirect containers of my new composite group,
   -- add me and add my elements
   for map in  select distinct group_id
-	      from group_component_map
-	      where component_id = v_object_id_one 
+              from group_component_map
+              where component_id = v_object_id_one
   LOOP
 
     -- Add a row for me
@@ -259,10 +259,10 @@
     where group_id = v_object_id_two
     and t.composable_p = 't'
     and not exists (select 1
-		    from group_element_map
-		    where group_id = map.group_id
-		    and element_id = m.member_id
-		    and rel_id = m.rel_id);
+                    from group_element_map
+                    where group_id = map.group_id
+                    and element_id = m.member_id
+                    and rel_id = m.rel_id);
 
     -- Add rows for my composable elements
 
@@ -277,16 +277,16 @@
     where group_id = v_object_id_two
     and t.composable_p = 't'
     and not exists (select 1
-		    from group_element_map
-		    where group_id = map.group_id
-		    and element_id = m.element_id
-		    and rel_id = m.rel_id);
+                    from group_element_map
+                    where group_id = map.group_id
+                    and element_id = m.element_id
+                    and rel_id = m.rel_id);
   end loop;
 
   return new;
 
 END;
-$$ LANGUAGE plpgsql;  
+$$ LANGUAGE plpgsql;
 
 create trigger composition_rels_in_tr after insert on composition_rels
 for each row execute procedure composition_rels_in_tr ();
@@ -321,8 +321,8 @@
   where rel_id = old.rel_id;
 
   for map in  select *
-	      from group_component_map
-	      where rel_id = old.rel_id 
+              from group_component_map
+              where rel_id = old.rel_id
   LOOP
 
     delete from group_element_index
@@ -351,19 +351,19 @@
 
   for map in  select *
               from group_component_map
-	      where group_id in (select group_id
-		               from group_component_map
-		               where component_id = v_object_id_one
-			       union
-			       select v_object_id_one
-			       from dual)
+              where group_id in (select group_id
+                               from group_component_map
+                               where component_id = v_object_id_one
+                               union
+                               select v_object_id_one
+                               from dual)
               and component_id in (select component_id
-			           from group_component_map
-			           where group_id = v_object_id_two
-				   union
-				   select v_object_id_two
-				   from dual)
-              and group_contains_p(group_id, component_id, rel_id) = 'f' 
+                                   from group_component_map
+                                   where group_id = v_object_id_two
+                                   union
+                                   select v_object_id_two
+                                   from dual)
+              and group_contains_p(group_id, component_id, rel_id) = 'f'
   LOOP
 
     delete from group_element_index
@@ -421,9 +421,9 @@
 
 ) RETURNS integer AS $$
 DECLARE
-  v_rel_id               integer;       
+  v_rel_id               integer;
 BEGIN
-    raise NOTICE 'composition_rel__new one % two %', object_id_one, object_id_two;
+    -- raise NOTICE 'composition_rel__new one % two %', object_id_one, object_id_two;
     v_rel_id := acs_rel__new (
       new__rel_id,
       rel_type,
@@ -440,7 +440,7 @@
      (v_rel_id);
 
     return v_rel_id;
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -484,7 +484,7 @@
 BEGIN
     PERFORM acs_rel__delete(rel_id);
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
@@ -512,15 +512,15 @@
     for row in  select r.object_id_one as parent_id
                 from acs_rels r, composition_rels c
                 where r.rel_id = c.rel_id
-                and r.object_id_two = component_id 
+                and r.object_id_two = component_id
     LOOP
       if composition_rel__check_path_exists_p(row.parent_id, container_id) = 't' then
         return 't';
       end if;
     end loop;
 
     return 'f';
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -539,8 +539,8 @@
    check_index__container_id integer
 ) RETURNS boolean AS $$
 DECLARE
-  result                              boolean;       
-  n_rows                              integer;       
+  result                              boolean;
+  n_rows                              integer;
   dc                                  record;
   r1                                  record;
   r2                                  record;
@@ -554,7 +554,7 @@
     for dc in  select r.rel_id, r.object_id_one as container_id
                from acs_rels r, composition_rels c
                where r.rel_id = c.rel_id
-               and r.object_id_two = check_index__component_id 
+               and r.object_id_two = check_index__component_id
     LOOP
 
       if composition_rel__check_path_exists_p(dc.container_id,
@@ -585,7 +585,7 @@
                and r.object_id_two = check_index__container_id
                union
                select check_index__container_id as container_id
-               from dual 
+               from dual
     LOOP
       -- Loop through all the components of COMPONENT_ID and make a
       -- recursive call.
@@ -595,7 +595,7 @@
                  and r.object_id_one = check_index__component_id
                  union
                  select check_index__component_id as component_id
-                 from dual 
+                 from dual
       LOOP
         if (r1.container_id != check_index__container_id or
             r2.component_id != check_index__component_id) and
@@ -606,7 +606,7 @@
     end loop;
 
     return result;
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -626,8 +626,8 @@
 DECLARE
   container_id                                 groups.group_id%TYPE;
   component_id                                 groups.group_id%TYPE;
-  result                                       boolean;     
-  row                                          record;  
+  result                                       boolean;
+  row                                          record;
 BEGIN
     result := 't';
 
@@ -649,7 +649,7 @@
     -- relating to this relation.
     for row in  select *
                 from group_component_index
-                where rel_id = check_representation__rel_id  
+                where rel_id = check_representation__rel_id
     LOOP
       if composition_rel__check_path_exists_p(row.component_id, row.group_id) = 'f' then
         result := 'f';
@@ -663,7 +663,7 @@
     end loop;
 
     return result;
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -695,7 +695,7 @@
 
 ) RETURNS integer AS $$
 DECLARE
-  v_rel_id               integer;       
+  v_rel_id               integer;
 BEGIN
     v_rel_id := acs_rel__new (
       new__rel_id,
@@ -713,7 +713,7 @@
      (v_rel_id, new__member_state);
 
     return v_rel_id;
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -760,7 +760,7 @@
     set member_state = 'banned'
     where rel_id = ban__rel_id;
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
@@ -783,7 +783,7 @@
     set member_state = 'approved'
     where rel_id = approve__rel_id;
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
@@ -806,7 +806,7 @@
     set member_state = 'rejected'
     where rel_id = reject__rel_id;
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
@@ -829,11 +829,35 @@
     set member_state = 'needs approval'
     where rel_id = unapprove__rel_id;
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
 
+-- procedure expire
+
+
+-- added
+select define_function_args('membership_rel__expire','rel_id');
+
+--
+-- procedure membership_rel__expire/1
+--
+CREATE OR REPLACE FUNCTION membership_rel__expire(
+   expire__rel_id integer
+) RETURNS integer AS $$
+DECLARE
+BEGIN
+    update membership_rels
+    set member_state = 'expired'
+    where rel_id = expire__rel_id;
+
+    return 0;
+END;
+$$ LANGUAGE plpgsql;
+
+
+
 -- procedure deleted
 
 
@@ -852,7 +876,7 @@
     set member_state = 'deleted'
     where rel_id = deleted__rel_id;
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
@@ -873,7 +897,7 @@
 BEGIN
     PERFORM acs_rel__delete(rel_id);
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
@@ -895,7 +919,7 @@
     set member_state = 'merged'
     where rel_id = merge__rel_id;
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
@@ -914,8 +938,8 @@
    check_index__container_id integer
 ) RETURNS boolean AS $$
 DECLARE
-  result                              boolean;       
-  n_rows                              integer;       
+  result                              boolean;
+  n_rows                              integer;
   row                                 record;
 BEGIN
 
@@ -937,15 +961,15 @@
     for row in  select r.object_id_one as container_id
                 from acs_rels r, composition_rels c
                 where r.rel_id = c.rel_id
-                and r.object_id_two = check_index__group_id  
+                and r.object_id_two = check_index__group_id
     LOOP
       if membership_rel__check_index(row.container_id, check_index__member_id, check_index__container_id) = 'f' then
         result := 'f';
       end if;
     end loop;
 
     return result;
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -965,8 +989,8 @@
 DECLARE
   group_id                      groups.group_id%TYPE;
   member_id                     parties.party_id%TYPE;
-  result                        boolean;  
-  row                           record;     
+  result                        boolean;
+  row                           record;
 BEGIN
     result := 't';
 
@@ -986,7 +1010,7 @@
 
     for row in  select *
                 from group_member_index
-                where rel_id = check_representation__rel_id 
+                where rel_id = check_representation__rel_id
     LOOP
       if composition_rel__check_path_exists_p(row.container_id,
                                              row.group_id) = 'f' then
@@ -1000,11 +1024,11 @@
     end loop;
 
     return result;
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
-    
+
 -- create or replace package body acs_group
 -- function new
 
@@ -1037,8 +1061,8 @@
   v_join_policy              groups.join_policy%TYPE;
 BEGIN
   v_group_id :=
-   party__new(new__group_id, new__object_type, new__creation_date, 
-              new__creation_user, new__creation_ip, new__email, 
+   party__new(new__group_id, new__object_type, new__creation_date,
+              new__creation_user, new__creation_ip, new__email,
               new__url, new__context_id);
 
   v_join_policy := new__join_policy;
@@ -1096,9 +1120,9 @@
        where group_rels.group_id = v_group_id
        and group_rels.rel_type = g.rel_type)
   ) rels;
-  
+
   return v_group_id;
-  
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -1144,28 +1168,28 @@
 DECLARE
   row                           record;
 BEGIN
- 
+
    -- Delete all the relations of any type to this group
    for row in select r.rel_id, t.package_name
                  from acs_rels r, acs_object_types t
                 where r.rel_type = t.object_type
                   and (r.object_id_one = delete__group_id
-                       or r.object_id_two = delete__group_id) 
+                       or r.object_id_two = delete__group_id)
    LOOP
       execute 'select ' ||  row.package_name || '__delete(' || row.rel_id || ')';
    end loop;
- 
+
    -- Delete all segments defined for this group
-   for row in  select segment_id 
-                 from rel_segments 
-                where group_id = delete__group_id 
+   for row in  select segment_id
+                 from rel_segments
+                where group_id = delete__group_id
    LOOP
        PERFORM rel_segment__delete(row.segment_id);
    end loop;
 
    PERFORM party__delete(delete__group_id);
 
-   return 0; 
+   return 0;
 END;
 $$ LANGUAGE plpgsql;
 
@@ -1183,56 +1207,56 @@
    name__group_id integer
 ) RETURNS varchar AS $$
 DECLARE
-  name__group_name       varchar(200);  
+  name__group_name       varchar(200);
 BEGIN
   select group_name
   into name__group_name
   from groups
   where group_id = name__group_id;
 
   return name__group_name;
-  
+
 END;
 $$ LANGUAGE plpgsql stable strict;
 
 
-
 select define_function_args('acs_group__member_p','party_id,group_id,cascade_membership');
 
 --
--- procedure acs_group__member_p/3
+-- function acs_group__member_p/3
 --
 CREATE OR REPLACE FUNCTION acs_group__member_p(
    p_party_id integer,
    p_group_id integer,
    p_cascade_membership boolean
 ) RETURNS boolean AS $$
-DECLARE
-BEGIN
-  if p_cascade_membership then
+
+  SELECT CASE
+  WHEN p_cascade_membership = true then
     --
     -- Direct and indirect memberships
     --
-    return count(*) > 0
-      from group_member_map
+    EXISTS (
+      select 1 from group_member_map
       where group_id = p_group_id
-        and member_id = p_party_id;
-  else
+        and member_id = p_party_id
+    )
+  ELSE
     --
     -- Only direct memberships
     --
-    return count(*) > 0
-      from acs_rels rels
-    where rels.rel_type = 'membership_rel'
-      and rels.object_id_one = p_group_id
-      and rels.object_id_two = p_party_id
-      and acs_permission.permission_p(rels.rel_id, p_party_id, 'read');
-  end if;
-END;
-$$ LANGUAGE plpgsql stable;
+    EXISTS (
+      select 1 from acs_rels rels
+      where rels.rel_type = 'membership_rel'
+        and rels.object_id_one = p_group_id
+        and rels.object_id_two = p_party_id
+    )
+  END;
 
+$$ LANGUAGE sql strict stable;
 
 
+
 -- function check_representation
 
 
@@ -1246,13 +1270,13 @@
    group_id integer
 ) RETURNS boolean AS $$
 DECLARE
-  res                    boolean; 
+  res                    boolean;
   comp                   record;
-  memb                   record;      
+  memb                   record;
 BEGIN
-   if group_id is null then 
+   if group_id is null then
         --maybe we should just return 'f' instead?
-	raise exception 'acs_group__check_representation called with null group_id';
+        raise exception 'acs_group__check_representation called with null group_id';
    end if;
 
    res := 't';
@@ -1266,7 +1290,7 @@
    for comp in select c.rel_id
              from acs_rels r, composition_rels c
              where r.rel_id = c.rel_id
-             and r.object_id_one = group_id 
+             and r.object_id_one = group_id
    LOOP
      if composition_rel__check_representation(comp.rel_id) = 'f' then
        res := 'f';
@@ -1276,7 +1300,7 @@
    for memb in  select m.rel_id
              from acs_rels r, membership_rels m
              where r.rel_id = m.rel_id
-             and r.object_id_one = group_id 
+             and r.object_id_one = group_id
    LOOP
      if membership_rel__check_representation(memb.rel_id) = 'f' then
        res := 'f';
@@ -1287,7 +1311,7 @@
                   'Done running check_representation on group ' || group_id);
 
    return res;
-  
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -1311,7 +1335,7 @@
 
 ) RETURNS integer AS $$
 DECLARE
-  v_rel_id               integer;       
+  v_rel_id               integer;
 BEGIN
     v_rel_id := membership_rel__new (
       p_rel_id,           -- rel_id
@@ -1329,7 +1353,7 @@
      (v_rel_id);
 
     return v_rel_id;
-   
+
 END;
 $$ LANGUAGE plpgsql;
 
@@ -1346,13 +1370,13 @@
 DECLARE
 BEGIN
     return membership_rel__new(
-        null,                  -- rel_id
+        null,                -- rel_id
         'admin_rel',         -- rel_type
-        object_id_one,         -- object_id_one
-        object_id_two,         -- object_id_two
+        object_id_one,       -- object_id_one
+        object_id_two,       -- object_id_two
         'approved',          -- member_state
-        null,                  -- creation_user
-        null                   -- creation_ip
+        null,                -- creation_user
+        null                 -- creation_ip
     );
 END;
 $$ LANGUAGE plpgsql;
@@ -1371,8 +1395,13 @@
 BEGIN
     PERFORM membership_rel__delete(rel_id);
 
-    return 0; 
+    return 0;
 END;
 $$ LANGUAGE plpgsql;
 
 
+--
+-- Local variables:
+--   mode: sql
+--   indent-tabs-mode: nil
+-- End: