-- -- packages/acs-kernel/sql/groups-body-create.sql -- -- @author rhs@mit.edu -- @creation-date 2000-08-22 -- @cvs-id $Id: groups-body-create.sql,v 1.37 2024/09/03 15:37:33 gustafn Exp $ -- -------------- -- TRIGGERS -- -------------- -- The insert trigger was dummied up in groups-create.sql, so we just need -- to replace the trigger function, not create the trigger. -- However, PG 7.3 introduces a new type "trigger" for the return type -- needed for functions called by triggers. "create function" transmorgifies -- the return type "trigger" to "trigger" so PG 7.2 dumps can be restored into -- PG 7.3. But "create or replace" doesn't do it. We can't use "trigger" -- because we currently are still supporting PG 7.2. Isn't life a pleasure? -- I'm leaving the triggers we aren't overriding as "create or replace" because -- this will be the right thing to do if the PG folks fix this problem or when -- we drop support of PG 7.2 and no longer need to declare these as type "trigger" drop trigger membership_rels_in_tr on membership_rels; drop function membership_rels_in_tr (); -- -- procedure membership_rels_in_tr/0 -- CREATE OR REPLACE FUNCTION membership_rels_in_tr( ) RETURNS trigger 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_composable_p acs_rel_types.composable_p%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, r.rel_type, composable_p into v_object_id_one, v_object_id_two, v_rel_type, v_composable_p from acs_rels r join acs_rel_types t on (r.rel_type = t.rel_type) where rel_id = new.rel_id; -- Insert a row for me in the 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, 'membership_rel'); if new.member_state = 'approved' then perform party_approved_member__add(v_object_id_one, v_object_id_two, new.rel_id, v_rel_type); end if; -- If this rel_type composable... if v_composable_p = 't' then -- 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 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'); if new.member_state = 'approved' then perform party_approved_member__add(map.group_id, v_object_id_two, new.rel_id, v_rel_type); end if; end loop; end if; 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 (); -- -- procedure membership_rels_up_tr/0 -- CREATE OR REPLACE FUNCTION membership_rels_up_tr( ) RETURNS trigger AS $$ DECLARE map record; BEGIN if new.member_state = old.member_state then return new; end if; for map in select group_id, element_id, rel_type from group_element_index where rel_id = new.rel_id loop if new.member_state = 'approved' then perform party_approved_member__add(map.group_id, map.element_id, new.rel_id, map.rel_type); else perform party_approved_member__remove(map.group_id, map.element_id, new.rel_id, map.rel_type); end if; end loop; return new; END; $$ LANGUAGE plpgsql; create trigger membership_rels_up_tr before update on membership_rels for each row execute procedure membership_rels_up_tr (); -- -- procedure membership_rels_del_tr/0 -- CREATE OR REPLACE FUNCTION membership_rels_del_tr( ) RETURNS trigger AS $$ DECLARE 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; for map in select group_id, element_id, rel_type from group_element_index where rel_id = old.rel_id loop perform party_approved_member__remove(map.group_id, map.element_id, old.rel_id, map.rel_type); end loop; 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 (); drop trigger composition_rels_in_tr on composition_rels; drop function composition_rels_in_tr (); -- -- procedure composition_rels_in_tr/0 -- CREATE OR REPLACE FUNCTION composition_rels_in_tr( ) RETURNS trigger 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'); -- Add to the denormalized party_approved_member_map perform party_approved_member__add(v_object_id_one, member_id, rel_id, rel_type) 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); -- Make my composable 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, m.rel_type, ancestor_rel_type from group_element_map m join acs_rel_types t on (m.rel_type = t.rel_type) 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); -- 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 to party_approved_member_map perform party_approved_member__add(map.group_id, member_id, rel_id, m.rel_type) from group_approved_member_map m join acs_rel_types t on (m.rel_type = t.rel_type) 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); -- Add rows for my composable 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, m.rel_type, ancestor_rel_type from group_element_map m join acs_rel_types t on (m.rel_type = t.rel_type) 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); 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 (); -- -- TO DO: See if this can be optimized now that the member and component -- mapping tables have been combined -- -- -- procedure composition_rels_del_tr/0 -- CREATE OR REPLACE FUNCTION composition_rels_del_tr( ) RETURNS trigger 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 perform party_approved_member__remove(map.group_id, member_id, rel_id, rel_type) from group_approved_member_map where group_id = map.group_id and container_id = map.component_id; 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 end if; perform party_approved_member__remove(map.group_id, member_id, rel_id, rel_type) from group_approved_member_map where group_id = map.group_id and container_id = map.component_id; delete from group_element_index where group_id = map.group_id and container_id = map.component_id and ancestor_rel_type = 'membership_rel'; 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 (); -------------------- -- PACKAGE BODIES -- -------------------- -- create or replace package body composition_rel -- function new select define_function_args('composition_rel__new','rel_id;null,rel_type;composition_rel,object_id_one,object_id_two,creation_user;null,creation_ip;null'); -- -- procedure composition_rel__new/6 -- CREATE OR REPLACE FUNCTION composition_rel__new( new__rel_id integer, -- default null rel_type varchar, -- default 'composition_rel' object_id_one integer, object_id_two integer, creation_user integer, -- default null creation_ip varchar -- default null ) RETURNS integer AS $$ DECLARE v_rel_id integer; BEGIN -- raise NOTICE 'composition_rel__new one % two %', object_id_one, object_id_two; 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 -- -- procedure composition_rel__new/2 -- CREATE OR REPLACE FUNCTION composition_rel__new( object_id_one integer, object_id_two integer ) RETURNS integer AS $$ -- -- composition_rel__new/2 maybe obsolete, when we define proper defaults for /6 -- DECLARE BEGIN return composition_rel__new(null, 'composition_rel', object_id_one, object_id_two, null, null); END; $$ LANGUAGE plpgsql; -- procedure delete -- added select define_function_args('composition_rel__delete','rel_id'); -- -- procedure composition_rel__delete/1 -- CREATE OR REPLACE FUNCTION composition_rel__delete( rel_id integer ) RETURNS integer AS $$ DECLARE BEGIN PERFORM acs_rel__delete(rel_id); return 0; END; $$ LANGUAGE plpgsql; -- function check_path_exists_p -- added select define_function_args('composition_rel__check_path_exists_p','component_id,container_id'); -- -- procedure composition_rel__check_path_exists_p/2 -- CREATE OR REPLACE FUNCTION composition_rel__check_path_exists_p( component_id integer, container_id integer ) RETURNS boolean AS $$ DECLARE 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 -- added select define_function_args('composition_rel__check_index','component_id,container_id'); -- -- procedure composition_rel__check_index/2 -- CREATE OR REPLACE FUNCTION composition_rel__check_index( check_index__component_id integer, check_index__container_id integer ) RETURNS boolean AS $$ DECLARE 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 -- added select define_function_args('composition_rel__check_representation','rel_id'); -- -- procedure composition_rel__check_representation/1 -- CREATE OR REPLACE FUNCTION composition_rel__check_representation( check_representation__rel_id integer ) RETURNS boolean AS $$ DECLARE 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 us 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 us 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 -- old 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') -- new select define_function_args('membership_rel__new','rel_id;null,rel_type;membership_rel,object_id_one,object_id_two,member_state;approved,creation_user;null,creation_ip;null'); -- -- procedure membership_rel__new/7 -- CREATE OR REPLACE FUNCTION membership_rel__new( new__rel_id integer, -- default null rel_type varchar, -- default 'membership_rel' object_id_one integer, object_id_two integer, new__member_state varchar, -- default 'approved' creation_user integer, -- default null creation_ip varchar -- default null ) RETURNS integer AS $$ DECLARE 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 -- -- procedure membership_rel__new/2 -- CREATE OR REPLACE FUNCTION membership_rel__new( object_id_one integer, object_id_two integer ) RETURNS integer AS $$ -- -- membership_rel__new/2 maybe obsolete, when we define proper defaults for /7 -- DECLARE BEGIN return membership_rel__new(null, 'membership_rel', object_id_one, object_id_two, 'approved', null, null); END; $$ LANGUAGE plpgsql; -- procedure ban -- added select define_function_args('membership_rel__ban','rel_id'); -- -- procedure membership_rel__ban/1 -- CREATE OR REPLACE FUNCTION membership_rel__ban( ban__rel_id integer ) RETURNS integer AS $$ DECLARE BEGIN update membership_rels set member_state = 'banned' where rel_id = ban__rel_id; return 0; END; $$ LANGUAGE plpgsql; -- procedure approve -- added select define_function_args('membership_rel__approve','rel_id'); -- -- procedure membership_rel__approve/1 -- CREATE OR REPLACE FUNCTION membership_rel__approve( approve__rel_id integer ) RETURNS integer AS $$ DECLARE BEGIN update membership_rels set member_state = 'approved' where rel_id = approve__rel_id; return 0; END; $$ LANGUAGE plpgsql; -- procedure reject -- added select define_function_args('membership_rel__reject','rel_id'); -- -- procedure membership_rel__reject/1 -- CREATE OR REPLACE FUNCTION membership_rel__reject( reject__rel_id integer ) RETURNS integer AS $$ DECLARE BEGIN update membership_rels set member_state = 'rejected' where rel_id = reject__rel_id; return 0; END; $$ LANGUAGE plpgsql; -- procedure unapprove -- added select define_function_args('membership_rel__unapprove','rel_id'); -- -- procedure membership_rel__unapprove/1 -- CREATE OR REPLACE FUNCTION membership_rel__unapprove( unapprove__rel_id integer ) RETURNS integer AS $$ DECLARE BEGIN update membership_rels set member_state = 'needs approval' where rel_id = unapprove__rel_id; 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 -- added select define_function_args('membership_rel__deleted','rel_id'); -- -- procedure membership_rel__deleted/1 -- CREATE OR REPLACE FUNCTION membership_rel__deleted( deleted__rel_id integer ) RETURNS integer AS $$ DECLARE BEGIN update membership_rels set member_state = 'deleted' where rel_id = deleted__rel_id; return 0; END; $$ LANGUAGE plpgsql; -- procedure delete -- added select define_function_args('membership_rel__delete','rel_id'); -- -- procedure membership_rel__delete/1 -- CREATE OR REPLACE FUNCTION membership_rel__delete( rel_id integer ) RETURNS integer AS $$ DECLARE BEGIN PERFORM acs_rel__delete(rel_id); return 0; END; $$ LANGUAGE plpgsql; -- procedure merge -- added select define_function_args('membership_rel__merge','rel_id'); -- -- procedure membership_rel__merge/1 -- CREATE OR REPLACE FUNCTION membership_rel__merge( merge__rel_id integer ) RETURNS integer AS $$ DECLARE BEGIN update membership_rels set member_state = 'merged' where rel_id = merge__rel_id; return 0; END; $$ LANGUAGE plpgsql; -- function check_index -- added select define_function_args('membership_rel__check_index','group_id,member_id,container_id'); -- -- procedure membership_rel__check_index/3 -- CREATE OR REPLACE FUNCTION membership_rel__check_index( check_index__group_id integer, check_index__member_id integer, check_index__container_id integer ) RETURNS boolean AS $$ DECLARE result boolean; n_rows integer; row record; BEGIN select count(*) into n_rows from group_element_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_element_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 -- added select define_function_args('membership_rel__check_representation','rel_id'); -- -- procedure membership_rel__check_representation/1 -- CREATE OR REPLACE FUNCTION membership_rel__check_representation( check_representation__rel_id integer ) RETURNS boolean AS $$ DECLARE 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 -- old 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') -- new select define_function_args('acs_group__new','group_id;null,object_type;group,creation_date;now(),creation_user;null,creation_ip;null,email;null,url;null,group_name,join_policy;null,context_id;null'); -- -- procedure acs_group__new/10 -- CREATE OR REPLACE FUNCTION acs_group__new( new__group_id integer, -- default null new__object_type varchar, -- default 'group' new__creation_date timestamptz, -- default now() -- default 'now()' new__creation_user integer, -- default null new__creation_ip varchar, -- default null new__email varchar, -- default null new__url varchar, -- default null new__group_name varchar, new__join_policy varchar, -- default null new__context_id integer -- default null ) RETURNS integer AS $$ DECLARE 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; update acs_objects set title = new__group_name where object_id = v_group_id; 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, rels.rel_type from ( select distinct g.rel_type from group_type_rels g, ( select parent.object_type as parent_type from acs_object_types child, acs_object_types parent where child.object_type <> parent.object_type and child.tree_sortkey between parent.tree_sortkey and tree_right(parent.tree_sortkey) and child.object_type = new__object_type order by parent.tree_sortkey desc) types where g.group_type = types.parent_type and not exists ( select 1 from group_rels where group_rels.group_id = v_group_id and group_rels.rel_type = g.rel_type) ) rels; return v_group_id; END; $$ LANGUAGE plpgsql; -- function new -- -- procedure acs_group__new/1 -- CREATE OR REPLACE FUNCTION acs_group__new( gname varchar ) RETURNS integer AS $$ -- -- acs_group__new/1 maybe obsolete, when we define proper defaults for /10 -- DECLARE BEGIN return acs_group__new(null, 'group', now(), null, null, null, null, gname, null, null); END; $$ LANGUAGE plpgsql; -- procedure delete -- added select define_function_args('acs_group__delete','group_id'); -- -- procedure acs_group__delete/1 -- CREATE OR REPLACE FUNCTION acs_group__delete( delete__group_id integer ) RETURNS integer AS $$ 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) 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 LOOP PERFORM rel_segment__delete(row.segment_id); end loop; PERFORM party__delete(delete__group_id); return 0; END; $$ LANGUAGE plpgsql; -- function name -- added select define_function_args('acs_group__name','group_id'); -- -- procedure acs_group__name/1 -- CREATE OR REPLACE FUNCTION acs_group__name( name__group_id integer ) RETURNS varchar AS $$ DECLARE 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'); -- -- 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 $$ SELECT CASE WHEN p_cascade_membership = true then -- -- Direct and indirect memberships -- EXISTS ( select 1 from group_member_map where group_id = p_group_id and member_id = p_party_id ) ELSE -- -- Only direct memberships -- 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 -- added select define_function_args('acs_group__check_representation','group_id'); -- -- procedure acs_group__check_representation/1 -- CREATE OR REPLACE FUNCTION acs_group__check_representation( group_id integer ) RETURNS boolean AS $$ DECLARE res boolean; comp record; memb record; BEGIN if group_id is null then --maybe we should just return 'f' instead? raise exception 'acs_group__check_representation called with null group_id'; end if; 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; -- create or replace package body admin_rel select define_function_args('admin_rel__new','rel_id;null,rel_type;admin_rel,object_id_one,object_id_two,member_state;approved,creation_user;null,creation_ip;null'); -- -- procedure admin_rel__new/7 -- CREATE OR REPLACE FUNCTION admin_rel__new( p_rel_id integer, -- default null p_rel_type varchar, -- default 'admin_rel' p_object_id_one integer, p_object_id_two integer, p_member_state varchar, -- default 'approved' p_creation_user integer, -- default null p_creation_ip varchar -- default null ) RETURNS integer AS $$ DECLARE v_rel_id integer; BEGIN v_rel_id := membership_rel__new ( p_rel_id, -- rel_id p_rel_type, -- rel_type p_object_id_one, -- object_id_one p_object_id_two, -- object_id_two p_member_state, -- member_state p_creation_user, -- creation_usre p_creation_ip -- creation_ip ); insert into admin_rels (rel_id) values (v_rel_id); return v_rel_id; END; $$ LANGUAGE plpgsql; -- -- procedure admin_rel__new/2 -- CREATE OR REPLACE FUNCTION admin_rel__new( object_id_one integer, object_id_two integer ) RETURNS integer AS $$ -- -- admin_rel__new/2 maybe obsolete, when we define proper defaults for /7 -- DECLARE BEGIN return membership_rel__new( null, -- rel_id 'admin_rel', -- rel_type object_id_one, -- object_id_one object_id_two, -- object_id_two 'approved', -- member_state null, -- creation_user null -- creation_ip ); END; $$ LANGUAGE plpgsql; select define_function_args('admin_rel__delete','rel_id'); -- -- procedure admin_rel__delete/1 -- CREATE OR REPLACE FUNCTION admin_rel__delete( rel_id integer ) RETURNS integer AS $$ DECLARE BEGIN PERFORM membership_rel__delete(rel_id); return 0; END; $$ LANGUAGE plpgsql; -- -- Local variables: -- mode: sql -- indent-tabs-mode: nil -- End: