Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d15-5.9.1d16.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d15-5.9.1d16.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-5.9.1d15-5.9.1d16.sql 31 Aug 2016 18:57:41 -0000 1.1.2.1 @@ -0,0 +1,376 @@ +-- +-- add extended attribute to rel types +-- +-- make the upgrade script loadable multiple times +DO $$ +DECLARE + v_found boolean; +BEGIN + SELECT exists( + SELECT 1 FROM information_schema.columns WHERE table_name='acs_rel_types' and column_name='composable_p' + ) INTO v_found; + if v_found IS FALSE then + + ALTER TABLE acs_rel_types ADD COLUMN composable_p boolean DEFAULT true NOT NULL; + UPDATE acs_rel_types SET composable_p = false WHERE rel_type = 'admin_rel'; + + end if; +END$$; + + +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 (); + +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 (); + +select define_function_args('acs_rel_type__create_type','rel_type,pretty_name,pretty_plural,supertype;relationship,table_name,id_column,package_name,object_type_one,role_one;null,min_n_rels_one,max_n_rels_one,object_type_two,role_two;null,min_n_rels_two,max_n_rels_two,composable_p;t'); + +drop function if exists acs_rel_type__create_type( + varchar, + varchar, + varchar, + varchar, -- default 'relationship' + varchar, + varchar, + varchar, + varchar, + varchar, -- default null + integer, + integer, + varchar, + varchar, -- default null + integer, + integer + ); +-- +-- procedure acs_rel_type__create_type/16 +-- +CREATE OR REPLACE FUNCTION acs_rel_type__create_type( + create_type__rel_type varchar, + create_type__pretty_name varchar, + create_type__pretty_plural varchar, + create_type__supertype varchar, -- default 'relationship' + create_type__table_name varchar, + create_type__id_column varchar, + create_type__package_name varchar, + create_type__object_type_one varchar, + create_type__role_one varchar, -- default null + create_type__min_n_rels_one integer, + create_type__max_n_rels_one integer, + create_type__object_type_two varchar, + create_type__role_two varchar, -- default null + create_type__min_n_rels_two integer, + create_type__max_n_rels_two integer, + create_type__composable_p boolean default true + +) RETURNS integer AS $$ +DECLARE + + type_extension_table acs_object_types.type_extension_table%TYPE default null; + abstract_p acs_object_types.abstract_p%TYPE default 'f'; + name_method acs_object_types.name_method%TYPE default null; +BEGIN + PERFORM acs_object_type__create_type( + create_type__rel_type, + create_type__pretty_name, + create_type__pretty_plural, + create_type__supertype, + create_type__table_name, + create_type__id_column, + create_type__package_name, + abstract_p, + type_extension_table, + name_method + ); + + insert into acs_rel_types + (rel_type, + object_type_one, role_one, + min_n_rels_one, max_n_rels_one, + object_type_two, role_two, + min_n_rels_two, max_n_rels_two, + composable_p) + values + (create_type__rel_type, + create_type__object_type_one, create_type__role_one, + create_type__min_n_rels_one, create_type__max_n_rels_one, + create_type__object_type_two, create_type__role_two, + create_type__min_n_rels_two, create_type__max_n_rels_two, + create_type__composable_p); + + return 0; +END; +$$ LANGUAGE plpgsql; + + + +-- procedure create_type + +DROP FUNCTION IF EXISTS acs_rel_type__create_type( + varchar, + varchar, + varchar, + varchar, -- default 'relationship' + varchar, + varchar, + varchar, + varchar, -- default null + varchar, + integer, + integer, + varchar, + integer, + integer +); +-- +-- procedure acs_rel_type__create_type/15 +-- +CREATE OR REPLACE FUNCTION acs_rel_type__create_type( + create_type__rel_type varchar, + create_type__pretty_name varchar, + create_type__pretty_plural varchar, + create_type__supertype varchar, -- default 'relationship' + create_type__table_name varchar, + create_type__id_column varchar, + create_type__package_name varchar, + create_type__type_extension_table varchar, -- default null + create_type__object_type_one varchar, + create_type__min_n_rels_one integer, + create_type__max_n_rels_one integer, + create_type__object_type_two varchar, + create_type__min_n_rels_two integer, + create_type__max_n_rels_two integer, + create_type__composable_p boolean default true + +) RETURNS integer AS $$ +DECLARE + + abstract_p acs_object_types.abstract_p%TYPE default 'f'; + name_method acs_object_types.name_method%TYPE default null; + create_type__role_one acs_rel_types.role_one%TYPE default null; + create_type__role_two acs_rel_types.role_two%TYPE default null; +BEGIN + + PERFORM acs_object_type__create_type( + create_type__rel_type, + create_type__pretty_name, + create_type__pretty_plural, + create_type__supertype, + create_type__table_name, + create_type__id_column, + create_type__package_name, + abstract_p, + create_type__type_extension_table, + name_method + ); + + insert into acs_rel_types + (rel_type, + object_type_one, role_one, + min_n_rels_one, max_n_rels_one, + object_type_two, role_two, + min_n_rels_two, max_n_rels_two, + composable_p) + values + (create_type__rel_type, + create_type__object_type_one, create_type__role_one, + create_type__min_n_rels_one, create_type__max_n_rels_one, + create_type__object_type_two, create_type__role_two, + create_type__min_n_rels_two, create_type__max_n_rels_two, + create_type__composable_p); + + return 0; +END; +$$ LANGUAGE plpgsql;