Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade--5.9.1d15-5.9.1d16.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/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/oracle/upgrade/upgrade--5.9.1d15-5.9.1d16.sql 31 Aug 2016 18:57:41 -0000 1.1.2.1 @@ -0,0 +1,344 @@ +-- add extended attribute to rel types +alter table acs_rel_types add column composable_p boolean default 't' not null; +update acs_rel_types set composable_p = 'f' where rel_type = 'admin_rel'; + +create or replace trigger membership_rels_in_tr +after insert on membership_rels +for each row +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 varchar2(4000); +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_application_error(-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 + 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_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'); + + if :new.member_state = 'approved' then + party_approved_member.add(v_object_id_one, v_object_id_two, v_rel_type); + end if; + + if v_composable_p = 't' then + -- 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'); + + if :new.member_state = 'approved' then + party_approved_member.add(map.group_id, v_object_id_two, v_rel_type); + end if; + + end loop; + end if; +end; +/ +show errors + +create or replace trigger composition_rels_in_tr +after insert on composition_rels +for each row +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 varchar2(4000); +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_application_error(-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'); + + for members in (select distinct member_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)) + loop + party_approved_member.add(v_object_id_one, members.member_id, members.rel_type); + end loop; + + -- 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 composable 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 composable elements + + for members in (select distinct member_id, 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)) + loop + party_approved_member.add(map.group_id, members.member_id, members.rel_type); + end loop; + + 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 + 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; + +end; +/ +show errors + +create or replace package acs_rel_type +as + + procedure create_role ( + role in acs_rel_roles.role%TYPE, + pretty_name in acs_rel_roles.pretty_name%TYPE default null, + pretty_plural in acs_rel_roles.pretty_plural%TYPE default null + ); + + procedure drop_role ( + role in acs_rel_roles.role%TYPE + ); + + function role_pretty_name ( + role in acs_rel_roles.role%TYPE + ) return acs_rel_roles.pretty_name%TYPE; + + function role_pretty_plural ( + role in acs_rel_roles.role%TYPE + ) return acs_rel_roles.pretty_plural%TYPE; + + procedure create_type ( + rel_type in acs_rel_types.rel_type%TYPE, + pretty_name in acs_object_types.pretty_name%TYPE, + pretty_plural in acs_object_types.pretty_plural%TYPE, + supertype in acs_object_types.supertype%TYPE + default 'relationship', + table_name in acs_object_types.table_name%TYPE, + id_column in acs_object_types.id_column%TYPE, + package_name in acs_object_types.package_name%TYPE, + abstract_p in acs_object_types.abstract_p%TYPE default 'f', + type_extension_table in acs_object_types.type_extension_table%TYPE + default null, + name_method in acs_object_types.name_method%TYPE default null, + object_type_one in acs_rel_types.object_type_one%TYPE, + role_one in acs_rel_types.role_one%TYPE default null, + min_n_rels_one in acs_rel_types.min_n_rels_one%TYPE, + max_n_rels_one in acs_rel_types.max_n_rels_one%TYPE, + object_type_two in acs_rel_types.object_type_two%TYPE, + role_two in acs_rel_types.role_two%TYPE default null, + min_n_rels_two in acs_rel_types.min_n_rels_two%TYPE, + max_n_rels_two in acs_rel_types.max_n_rels_two%TYPE, + composable_p in acs_rel_types.composable_p%TYPE + ); + + procedure drop_type ( + rel_type in acs_rel_types.rel_type%TYPE, + cascade_p in char default 'f' + ); + +end acs_rel_type; +/ +show errors + +create or replace package body acs_rel_type +as + + procedure create_role ( + role in acs_rel_roles.role%TYPE, + pretty_name in acs_rel_roles.pretty_name%TYPE default null, + pretty_plural in acs_rel_roles.pretty_plural%TYPE default null + ) + is + begin + insert into acs_rel_roles + (role, pretty_name, pretty_plural) + values + (create_role.role, nvl(create_role.pretty_name,create_role.role), nvl(create_role.pretty_plural,create_role.role)); + end; + + procedure drop_role ( + role in acs_rel_roles.role%TYPE + ) + is + begin + delete from acs_rel_roles + where role = drop_role.role; + end; + + function role_pretty_name ( + role in acs_rel_roles.role%TYPE + ) return acs_rel_roles.pretty_name%TYPE + is + v_pretty_name acs_rel_roles.pretty_name%TYPE; + begin + select r.pretty_name into v_pretty_name + from acs_rel_roles r + where r.role = role_pretty_name.role; + + return v_pretty_name; + end role_pretty_name; + + + function role_pretty_plural ( + role in acs_rel_roles.role%TYPE + ) return acs_rel_roles.pretty_plural%TYPE + is + v_pretty_plural acs_rel_roles.pretty_plural%TYPE; + begin + select r.pretty_plural into v_pretty_plural + from acs_rel_roles r + where r.role = role_pretty_plural.role; + + return v_pretty_plural; + end role_pretty_plural; + + procedure create_type ( + rel_type in acs_rel_types.rel_type%TYPE, + pretty_name in acs_object_types.pretty_name%TYPE, + pretty_plural in acs_object_types.pretty_plural%TYPE, + supertype in acs_object_types.supertype%TYPE + default 'relationship', + table_name in acs_object_types.table_name%TYPE, + id_column in acs_object_types.id_column%TYPE, + package_name in acs_object_types.package_name%TYPE, + abstract_p in acs_object_types.abstract_p%TYPE default 'f', + type_extension_table in acs_object_types.type_extension_table%TYPE + default null, + name_method in acs_object_types.name_method%TYPE default null, + object_type_one in acs_rel_types.object_type_one%TYPE, + role_one in acs_rel_types.role_one%TYPE default null, + min_n_rels_one in acs_rel_types.min_n_rels_one%TYPE, + max_n_rels_one in acs_rel_types.max_n_rels_one%TYPE, + object_type_two in acs_rel_types.object_type_two%TYPE, + role_two in acs_rel_types.role_two%TYPE default null, + min_n_rels_two in acs_rel_types.min_n_rels_two%TYPE, + max_n_rels_two in acs_rel_types.max_n_rels_two%TYPE, + composable_p in acs_rel_types.composable_p%TYPE + ) + is + begin + acs_object_type.create_type( + object_type => rel_type, + pretty_name => pretty_name, + pretty_plural => pretty_plural, + supertype => supertype, + table_name => table_name, + id_column => id_column, + package_name => package_name, + abstract_p => abstract_p, + type_extension_table => type_extension_table, + name_method => 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); + end; + + procedure drop_type ( + rel_type in acs_rel_types.rel_type%TYPE, + cascade_p in char default 'f' + ) + is + begin + -- XXX do cascade_p + delete from acs_rel_types + where acs_rel_types.rel_type = acs_rel_type.drop_type.rel_type; + + acs_object_type.drop_type(acs_rel_type.drop_type.rel_type, acs_rel_type.drop_type.cascade_p); + end; + +end acs_rel_type; +/ +show errors