--
-- packages/acs-kernel/sql/groups-body-create.sql
--
-- @author rhs@mit.edu
-- @creation-date 2000-08-22
-- @cvs-id groups-body-create.sql,v 1.1.4.1 2001/01/12 22:58:33 mbryzek Exp
--

--------------
-- TRIGGERS --
--------------
-- a dummy trigger was defined in groups-create.sql
drop trigger membership_rels_in_tr on membership_rels;
drop function membership_rels_in_tr ();
create function membership_rels_in_tr () returns opaque as '
declare
  v_object_id_one acs_rels.object_id_one%TYPE;
  v_object_id_two acs_rels.object_id_two%TYPE;
  v_rel_type      acs_rels.rel_type%TYPE;
  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
      raise EXCEPTION ''-20000: %'', v_error;
  end if;

  select object_id_one, object_id_two, rel_type
  into v_object_id_one, v_object_id_two, v_rel_type
  from acs_rels
  where rel_id = new.rel_id;

  -- Insert a row for me in the group_member_index.
  insert into group_element_index
   (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_rel_type, ''membership_rel'');

  -- For all groups of which I am a component, insert a
  -- row in the group_member_index.
  for map in select distinct group_id
	      from group_component_map
	      where component_id = v_object_id_one 
  LOOP
    insert into group_element_index
     (group_id, element_id, rel_id, container_id,
      rel_type, ancestor_rel_type)
    values
     (map.group_id, v_object_id_two, new.rel_id, v_object_id_one,
      v_rel_type, ''membership_rel'');
  end loop;

  return new;

end;' language 'plpgsql';

create trigger membership_rels_in_tr after insert on membership_rels
for each row execute procedure membership_rels_in_tr ();

-- show errors

-- a dummy trigger was defined in groups-create.sql
drop trigger composition_rels_in_tr on composition_rels;
drop function composition_rels_in_tr();
create function composition_rels_in_tr () returns opaque as '
declare
  v_object_id_one acs_rels.object_id_one%TYPE;
  v_object_id_two acs_rels.object_id_two%TYPE;
  v_rel_type      acs_rels.rel_type%TYPE;
  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
      raise EXCEPTION ''-20000: %'', v_error;
  end if;

  select object_id_one, object_id_two, rel_type
  into v_object_id_one, v_object_id_two, v_rel_type
  from acs_rels
  where rel_id = new.rel_id;

  -- Insert a row for me in group_element_index
  insert into group_element_index
   (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_rel_type, ''composition_rel'');

  -- Make my elements be elements of my new composite group
  insert into group_element_index
   (group_id, element_id, rel_id, container_id,
    rel_type, ancestor_rel_type)
  select distinct
   v_object_id_one, element_id, rel_id, container_id,
   rel_type, ancestor_rel_type
  from group_element_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.element_id
		  and rel_id = m.rel_id);

  -- 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 
  LOOP

    -- Add a row for me
    insert into group_element_index
     (group_id, element_id, rel_id, container_id,
      rel_type, ancestor_rel_type)
    values
     (map.group_id, v_object_id_two, new.rel_id, v_object_id_one,
      v_rel_type, ''composition_rel'');

    -- Add rows for my elements
    insert into group_element_index
     (group_id, element_id, rel_id, container_id,
      rel_type, ancestor_rel_type)
    select distinct
     map.group_id, element_id, rel_id, container_id,
     rel_type, ancestor_rel_type
    from group_element_map m
    where group_id = v_object_id_two
    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);
  end loop;

  return new;

end;' language 'plpgsql';

create trigger composition_rels_in_tr after insert on composition_rels
for each row execute procedure composition_rels_in_tr ();

-- show errors

create function membership_rels_del_tr () returns opaque as '
declare
  v_error text;
begin
  -- First check if removing this relation would violate any relational constraints
  v_error := rel_constraint__violation_if_removed(old.rel_id);
  if v_error is not null then
      raise EXCEPTION ''-20000: %'', v_error;
  end if;

  delete from group_element_index
  where rel_id = old.rel_id;

  return old;

end;' language 'plpgsql';

create trigger membership_rels_del_tr before delete on membership_rels
for each row execute procedure membership_rels_del_tr ();

-- show errors

--
-- TO DO: See if this can be optimized now that the member and component
-- mapping tables have been combined
--
create function composition_rels_del_tr () returns opaque as '
declare
  v_object_id_one acs_rels.object_id_one%TYPE;
  v_object_id_two acs_rels.object_id_two%TYPE;
  n_rows          integer;
  v_error         text;
  map             record;
begin
  -- First check if removing this relation would violate any relational constraints
  v_error := rel_constraint__violation_if_removed(old.rel_id);
  if v_error is not null then
      raise EXCEPTION ''-20000: %'', v_error;
  end if;

  select object_id_one, object_id_two into v_object_id_one, v_object_id_two
  from acs_rels
  where rel_id = old.rel_id;

  for map in  select *
	      from group_component_map
	      where rel_id = old.rel_id 
  LOOP

    delete from group_element_index
    where rel_id = old.rel_id;

    select count(*) into n_rows
    from group_component_map
    where group_id = map.group_id
    and component_id = map.component_id;

    if n_rows = 0 then
      delete from group_element_index
      where group_id = map.group_id
      and container_id = map.component_id
      and ancestor_rel_type = ''membership_rel'';
    end if;

  end loop;


  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)
              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'' 
  LOOP

    delete from group_element_index
    where group_id = map.group_id
    and element_id = map.component_id
    and rel_id = map.rel_id;

    select count(*) into n_rows
    from group_component_map
    where group_id = map.group_id
    and component_id = map.component_id;

    if n_rows = 0 then
      delete from group_element_index
      where group_id = map.group_id
      and container_id = map.component_id
      and ancestor_rel_type = ''membership_rel'';
    end if;

  end loop;

  return old;

end;' language 'plpgsql';

create trigger composition_rels_del_tr before delete on composition_rels
for each row execute procedure composition_rels_del_tr ();

-- show errors


--------------------
-- PACKAGE BODIES --
--------------------

-- create or replace package body composition_rel
-- function new
select define_function_args('composition_rel__new','rel_id,rel_type;composition_rel,object_id_one,object_id_two,creation_user,creation_ip');

create function composition_rel__new (integer,varchar,integer,integer,integer,varchar)
returns integer as '
declare
  new__rel_id            alias for $1;  -- default null  
  rel_type               alias for $2;  -- default ''composition_rel''
  object_id_one          alias for $3;  
  object_id_two          alias for $4;  
  creation_user          alias for $5;  -- default null
  creation_ip            alias for $6;  -- default null
  v_rel_id               integer;       
begin
    v_rel_id := acs_rel__new (
      new__rel_id,
      rel_type,
      object_id_one,
      object_id_two,
      object_id_one,
      creation_user,
      creation_ip
    );

    insert into composition_rels
     (rel_id)
    values
     (v_rel_id);

    return v_rel_id;
   
end;' language 'plpgsql';

-- function new
create function composition_rel__new (integer,integer)
returns integer as '
declare
  object_id_one          alias for $1;  
  object_id_two          alias for $2;  
begin
        return composition_rel__new(null,
                                    ''composition_rel'',
                                    object_id_one,
                                    object_id_two,
                                    null,
                                    null);
end;' language 'plpgsql';

-- procedure delete
create function composition_rel__delete (integer)
returns integer as '
declare
  rel_id                 alias for $1;  
begin
    PERFORM acs_rel__delete(rel_id);

    return 0; 
end;' language 'plpgsql';


-- function check_path_exists_p
create function composition_rel__check_path_exists_p (integer,integer)
returns boolean as '
declare
  component_id           alias for $1;  
  container_id           alias for $2;  
  row                    record;
begin
    if component_id = container_id then
      return ''t'';
    end if;

    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 
    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';


-- function check_index
create function composition_rel__check_index (integer,integer)
returns boolean as '
declare
  check_index__component_id           alias for $1;  
  check_index__container_id           alias for $2;  
  result                              boolean;       
  n_rows                              integer;       
  dc                                  record;
  r1                                  record;
  r2                                  record;
begin
    result := ''t'';

    -- Loop through all the direct containers (DC) of COMPONENT_ID
    -- that are also contained by CONTAINER_ID and verify that the
    -- GROUP_COMPONENT_INDEX contains the (GROUP_ID, DC.REL_ID,
    -- CONTAINER_ID) triple.
    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 
    LOOP

      if composition_rel__check_path_exists_p(dc.container_id,
                             check_index__container_id) = ''t'' then
        select case when count(*) = 0 then 0 else 1 end into n_rows
        from group_component_index
        where group_id = check_index__container_id
        and component_id = check_index__component_id
        and rel_id = dc.rel_id;

        if n_rows = 0 then
          result := ''f'';
          PERFORM acs_log__error(''composition_rel.check_representation'',
                        ''Row missing from group_component_index for ('' ||
                        ''group_id = '' || check_index__container_id || '', '' ||
                        ''component_id = '' || check_index__component_id || '', '' ||
                        ''rel_id = '' || dc.rel_id || '')'');
        end if;

      end if;

    end loop;

    -- Loop through all the containers of CONTAINER_ID.
    for r1 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__container_id
               union
               select check_index__container_id as container_id
               from dual 
    LOOP
      -- Loop through all the components of COMPONENT_ID and make a
      -- recursive call.
      for r2 in  select r.object_id_two as component_id
                 from acs_rels r, composition_rels c
                 where r.rel_id = c.rel_id
                 and r.object_id_one = check_index__component_id
                 union
                 select check_index__component_id as component_id
                 from dual 
      LOOP
        if (r1.container_id != check_index__container_id or
            r2.component_id != check_index__component_id) and
           composition_rel__check_index(r2.component_id, r1.container_id) = ''f'' then
          result := ''f'';
        end if;
      end loop;
    end loop;

    return result;
   
end;' language 'plpgsql';


-- function check_representation
create function composition_rel__check_representation (integer)
returns boolean as '
declare
  check_representation__rel_id                 alias for $1;  
  container_id                                 groups.group_id%TYPE;
  component_id                                 groups.group_id%TYPE;
  result                                       boolean;     
  row                                          record;  
begin
    result := ''t'';

    if acs_object__check_representation(check_representation__rel_id) = ''f'' then
      result := ''f'';
    end if;

    select object_id_one, object_id_two
    into container_id, component_id
    from acs_rels
    where rel_id = check_representation__rel_id;

    -- First let''s check that the index has all the rows it should.
    if composition_rel__check_index(component_id, container_id) = ''f'' then
      result := ''f'';
    end if;

    -- Now let''s check that the index doesn''t have any extraneous rows
    -- relating to this relation.
    for row in  select *
                from group_component_index
                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'';
        PERFORM acs_log__error(''composition_rel.check_representation'',
                      ''Extraneous row in group_component_index: '' ||
                      ''group_id = '' || row.group_id || '', '' ||
                      ''component_id = '' || row.component_id || '', '' ||
                      ''rel_id = '' || row.rel_id || '', '' ||
                      ''container_id = '' || row.container_id || ''.'');
      end if;
    end loop;

    return result;
   
end;' language 'plpgsql';



-- show errors

-- create or replace package body membership_rel
-- function new
select define_function_args('membership_rel__new','rel_id,rel_type;membership_rel,object_id_one,object_id_two,member_state;approved,creation_user,creation_ip');

create function membership_rel__new (integer,varchar,integer,integer,varchar,integer,varchar)
returns integer as '
declare
  new__rel_id            alias for $1;  -- default null  
  rel_type               alias for $2;  -- default ''membership_rel''
  object_id_one          alias for $3;  
  object_id_two          alias for $4;  
  new__member_state      alias for $5;  -- default ''approved''
  creation_user          alias for $6;  -- default null
  creation_ip            alias for $7;  -- default null
  v_rel_id               integer;       
begin
    v_rel_id := acs_rel__new (
      new__rel_id,
      rel_type,
      object_id_one,
      object_id_two,
      object_id_one,
      creation_user,
      creation_ip
    );

    insert into membership_rels
     (rel_id, member_state)
    values
     (v_rel_id, new__member_state);

    return v_rel_id;
   
end;' language 'plpgsql';

-- function new
create function membership_rel__new (integer,integer)
returns integer as '
declare
  object_id_one          alias for $1;  
  object_id_two          alias for $2;  
begin
        return membership_rel__new(null,
                                   ''membership_rel'',
                                   object_id_one,
                                   object_id_two,
                                   ''approved'',
                                   null,
                                   null);
end;' language 'plpgsql';

-- procedure ban
create function membership_rel__ban (integer)
returns integer as '
declare
  ban__rel_id           alias for $1;  
begin
    update membership_rels
    set member_state = ''banned''
    where rel_id = ban__rel_id;

    return 0; 
end;' language 'plpgsql';


-- procedure approve
create function membership_rel__approve (integer)
returns integer as '
declare
  approve__rel_id               alias for $1;  
begin
    update membership_rels
    set member_state = ''approved''
    where rel_id = approve__rel_id;

    return 0; 
end;' language 'plpgsql';


-- procedure reject
create function membership_rel__reject (integer)
returns integer as '
declare
  reject__rel_id                alias for $1;  
begin
    update membership_rels
    set member_state = ''rejected''
    where rel_id = reject__rel_id;

    return 0; 
end;' language 'plpgsql';


-- procedure unapprove
create function membership_rel__unapprove (integer)
returns integer as '
declare
  unapprove__rel_id             alias for $1;  
begin
    update membership_rels
    set member_state = ''need approval''
    where rel_id = unapprove__rel_id;

    return 0; 
end;' language 'plpgsql';


-- procedure deleted
create function membership_rel__deleted (integer)
returns integer as '
declare
  deleted__rel_id               alias for $1;  
begin
    update membership_rels
    set member_state = ''deleted''
    where rel_id = deleted__rel_id;

    return 0; 
end;' language 'plpgsql';


-- procedure delete
create function membership_rel__delete (integer)
returns integer as '
declare
  rel_id                 alias for $1;  
begin
    PERFORM acs_rel__delete(rel_id);

    return 0; 
end;' language 'plpgsql';


-- function check_index
create function membership_rel__check_index (integer,integer,integer)
returns boolean as '
declare
  check_index__group_id               alias for $1;  
  check_index__member_id              alias for $2;  
  check_index__container_id           alias for $3;  
  result                              boolean;       
  n_rows                              integer;       
  row                                 record;
begin

    select count(*) into n_rows
    from group_member_index
    where group_id = check_index__group_id
    and member_id = check_index__member_id
    and container_id = check_index__container_id;

    if n_rows = 0 then
      result := ''f'';
      PERFORM acs_log__error(''membership_rel.check_representation'',
                    ''Row missing from group_member_index: '' ||
                    ''group_id = '' || check_index__group_id || '', '' ||
                    ''member_id = '' || check_index__member_id || '', '' ||
                    ''container_id = '' || check_index__container_id || ''.'');
    end if;

    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  
    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';


-- function check_representation
create function membership_rel__check_representation (integer)
returns boolean as '
declare
  check_representation__rel_id  alias for $1;  
  group_id                      groups.group_id%TYPE;
  member_id                     parties.party_id%TYPE;
  result                        boolean;  
  row                           record;     
begin
    result := ''t'';

    if acs_object__check_representation(check_representation__rel_id) = ''f'' then
      result := ''f'';
    end if;

    select r.object_id_one, r.object_id_two
    into group_id, member_id
    from acs_rels r, membership_rels m
    where r.rel_id = m.rel_id
    and m.rel_id = check_representation__rel_id;

    if membership_rel__check_index(group_id, member_id, group_id) = ''f'' then
      result := ''f'';
    end if;

    for row in  select *
                from group_member_index
                where rel_id = check_representation__rel_id 
    LOOP
      if composition_rel__check_path_exists_p(row.container_id,
                                             row.group_id) = ''f'' then
        result := ''f'';
        PERFORM acs_log__error(''membership_rel.check_representation'',
                      ''Extra row in group_member_index: '' ||
                      ''group_id = '' || row.group_id || '', '' ||
                      ''member_id = '' || row.member_id || '', '' ||
                      ''container_id = '' || row.container_id || ''.'');
      end if;
    end loop;

    return result;
   
end;' language 'plpgsql';

    
-- create or replace package body acs_group
-- function new
select define_function_args('acs_group__new','group_id,object_type;group,creation_date;now(),creation_user,creation_ip,email,url,group_name,join_policy,context_id');

create function acs_group__new (integer,varchar,timestamp,integer,varchar,varchar,varchar,varchar,varchar,integer)
returns integer as '
declare
  new__group_id              alias for $1;  -- default null  
  new__object_type           alias for $2;  -- default ''group''
  new__creation_date         alias for $3;  -- default now()
  new__creation_user         alias for $4;  -- default null
  new__creation_ip           alias for $5;  -- default null
  new__email                 alias for $6;  -- default null
  new__url                   alias for $7;  -- default null
  new__group_name            alias for $8;  
  new__join_policy           alias for $9;  -- default null
  new__context_id            alias for $10; -- default null
  v_group_id                 groups.group_id%TYPE;
  v_group_type_exists_p      integer;
  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, 
              new__url, new__context_id);

  v_join_policy := new__join_policy;

  -- if join policy was not specified, select the default based on group type
  if v_join_policy is null or v_join_policy = '''' then
      select count(*) into v_group_type_exists_p
      from group_types
      where group_type = new__object_type;

      if v_group_type_exists_p = 1 then
          select default_join_policy into v_join_policy
          from group_types
          where group_type = new__object_type;
      else
          v_join_policy := ''open'';
      end if;
  end if;

  insert into groups
   (group_id, group_name, join_policy)
  values
   (v_group_id, new__group_name, v_join_policy);

  -- setup the permissible relationship types for this group

  -- DRB: we have to call nextval() directly because the select may
  -- return more than one row.  The sequence hack will only compute
  -- one nextval value causing the insert to fail ("may" in PG, which
  -- is actually broken.  It should ALWAYS return exactly one value for
  -- the view.  In PG it may or may not depending on the optimizer''s
  -- mood.  PG group seems uninterested in acknowledging the fact that
  -- this is a bug)

  insert into group_rels
  (group_rel_id, group_id, rel_type)
  select nextval(''t_acs_object_id_seq''), v_group_id, g.rel_type
    from group_type_rels g
   where g.group_type = new__object_type;

  return v_group_id;
  
end;' language 'plpgsql';

-- function new
create function acs_group__new (varchar) returns integer as '
declare
        gname   alias for $1;
begin
        return acs_group__new(null,
                              ''group'',
                              now(),
                              null,
                              null,
                              null,
                              null,
                              gname,
                              null,
                              null);
end;' language 'plpgsql';

-- procedure delete
create function acs_group__delete (integer)
returns integer as '
declare
  delete__group_id              alias for $1;  
  row                           record;
begin
 
   -- Delete all segments defined for this group
   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;

   -- 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) 
   LOOP
      execute ''select '' ||  row.package_name || ''__delete('' || row.rel_id || '')'';
   end loop;
 
   PERFORM party__delete(delete__group_id);

   return 0; 
end;' language 'plpgsql';


-- function name
create function acs_group__name (integer)
returns varchar as '
declare
  name__group_id         alias for $1;  
  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';

create function acs_group__member_p (integer, integer, boolean)
returns boolean as '
declare
  p_party_id               alias for $1;  
  p_group_id               alias for $2;
  p_cascade_membership     alias for $3;
begin
  if p_cascade_membership  then
    return count(*) > 0
      from group_member_map
      where group_id = p_group_id and
            member_id = p_party_id;
  else
    return count(*) > 0
      from acs_rels rels, all_object_party_privilege_map perm
    where perm.object_id = rels.rel_id
           and perm.privilege = ''read''
           and rels.rel_type = ''membership_rel''
	   and rels.object_id_one = p_group_id
           and rels.object_id_two = p_party_id;
  end if;
end;' language 'plpgsql';


-- function check_representation
create function acs_group__check_representation (integer)
returns boolean as '
declare
  group_id               alias for $1;  
  res                    boolean; 
  comp                   record;
  memb                   record;      
begin
   res := ''t'';
   PERFORM acs_log__notice(''acs_group.check_representation'',
                  ''Running check_representation on group '' || group_id);

   if acs_object__check_representation(group_id) = ''f'' then
     res := ''f'';
   end if;

   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 
   LOOP
     if composition_rel__check_representation(comp.rel_id) = ''f'' then
       res := ''f'';
     end if;
   end loop;

   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 
   LOOP
     if membership_rel__check_representation(memb.rel_id) = ''f'' then
       res := ''f'';
     end if;
   end loop;

   PERFORM acs_log__notice(''acs_group.check_representation'',
                  ''Done running check_representation on group '' || group_id);

   return res;
  
end;' language 'plpgsql';



-- show errors