Fisheye: Tag 1.4 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6-4.6.1.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6-4.6.1d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6-4.6.1d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6-4.6.1d1.sql 26 Feb 2005 19:48:54 -0000 1.1 @@ -0,0 +1,1475 @@ +-- Add two new datatypes (supported by templating already). +-- + insert into acs_datatypes + (datatype, max_n_values) + values + ('url', null); + + insert into acs_datatypes + (datatype, max_n_values) + values + ('email', null); + +-- This giant package body is here since we are adding +-- two lines to acs_object.delete() to delete direct permissions +-- granted on the object which we are deleting +-- + +create or replace package body acs_object +as + + procedure initialize_attributes ( + object_id in acs_objects.object_id%TYPE + ) + is + v_object_type acs_objects.object_type%TYPE; + begin + -- XXX This should be fixed to initialize supertypes properly. + + -- Initialize dynamic attributes + insert into acs_attribute_values + (object_id, attribute_id, attr_value) + select + initialize_attributes.object_id, a.attribute_id, a.default_value + from acs_attributes a, acs_objects o + where a.object_type = o.object_type + and o.object_id = initialize_attributes.object_id + and a.storage = 'generic' + and a.static_p = 'f'; + + -- Retreive type for static attributes + select object_type into v_object_type from acs_objects + where object_id = initialize_attributes.object_id; + + -- Initialize static attributes + begin + insert into acs_static_attr_values + (object_type, attribute_id, attr_value) + select + v_object_type, a.attribute_id, a.default_value + from acs_attributes a, acs_objects o + where a.object_type = o.object_type + and o.object_id = initialize_attributes.object_id + and a.storage = 'generic' + and a.static_p = 't' + and not exists (select 1 from acs_static_attr_values + where object_type = a.object_type); + exception when no_data_found then null; + end; + + end initialize_attributes; + + function new ( + object_id in acs_objects.object_id%TYPE default null, + object_type in acs_objects.object_type%TYPE + default 'acs_object', + creation_date in acs_objects.creation_date%TYPE + default sysdate, + creation_user in acs_objects.creation_user%TYPE + default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + context_id in acs_objects.context_id%TYPE default null + ) + return acs_objects.object_id%TYPE + is + v_object_id acs_objects.object_id%TYPE; + begin + if object_id is null then + select acs_object_id_seq.nextval + into v_object_id + from dual; + else + v_object_id := object_id; + end if; + + insert into acs_objects + (object_id, object_type, context_id, + creation_date, creation_user, creation_ip) + values + (v_object_id, object_type, context_id, + creation_date, creation_user, creation_ip); + + acs_object.initialize_attributes(v_object_id); + + return v_object_id; + end new; + + procedure delete ( + object_id in acs_objects.object_id%TYPE + ) + is + v_exists_p char; + begin + + -- Delete dynamic/generic attributes + delete from acs_attribute_values where object_id = acs_object.delete.object_id; + + -- Delete directly assigned permissions + -- + -- JCD: We do this as an execute rather than just a direct query since + -- the acs_permissions table is not created when this file is + -- sourced. We need to clean up the creates and once that is done + -- we can turn this into a simple delete statement. + -- + execute immediate 'delete from acs_permissions where object_id = :object_id' + using in object_id; + + for object_type + in (select table_name, id_column + from acs_object_types + start with object_type = (select object_type + from acs_objects o + where o.object_id = acs_object.delete.object_id) + connect by object_type = prior supertype) + loop + -- Delete from the table if it exists. + select decode(count(*),0,'f','t') into v_exists_p + from user_tables + where table_name = upper(object_type.table_name); + + if v_exists_p = 't' then + execute immediate 'delete from ' || object_type.table_name || + ' where ' || object_type.id_column || ' = :object_id' + using in object_id; + end if; + + end loop; + + end delete; + + function name ( + object_id in acs_objects.object_id%TYPE + ) + return varchar2 + is + object_name varchar2(500); + v_object_id integer := object_id; + begin + -- Find the name function for this object, which is stored in the + -- name_method column of acs_object_types. Starting with this + -- object's actual type, traverse the type hierarchy upwards until + -- a non-null name_method value is found. + -- + for object_type + in (select name_method + from acs_object_types + start with object_type = (select object_type + from acs_objects o + where o.object_id = name.object_id) + connect by object_type = prior supertype) + loop + if object_type.name_method is not null then + + -- Execute the first name_method we find (since we're traversing + -- up the type hierarchy from the object's exact type) using + -- Native Dynamic SQL, to ascertain the name of this object. + -- + --execute immediate 'select ' || object_type.name_method || '(:1) from dual' + execute immediate 'begin :1 := ' || object_type.name_method || '(:2); end;' + using out object_name, in object_id; + --into object_name + + exit; + end if; + end loop; + + return object_name; + end name; + + function default_name ( + object_id in acs_objects.object_id%TYPE + ) return varchar2 + is + object_type_pretty_name acs_object_types.pretty_name%TYPE; + begin + select ot.pretty_name + into object_type_pretty_name + from acs_objects o, acs_object_types ot + where o.object_id = default_name.object_id + and o.object_type = ot.object_type; + + return object_type_pretty_name || ' ' || object_id; + end default_name; + + procedure get_attribute_storage ( + object_id_in in acs_objects.object_id%TYPE, + attribute_name_in in acs_attributes.attribute_name%TYPE, + v_column out varchar2, + v_table_name out varchar2, + v_key_sql out varchar2 + ) + is + v_object_type acs_attributes.object_type%TYPE; + v_static acs_attributes.static_p%TYPE := null; + v_attr_id acs_attributes.attribute_id%TYPE := null; + v_storage acs_attributes.storage%TYPE := null; + v_attr_name acs_attributes.attribute_name%TYPE := null; + v_id_column varchar2(200) := null; + v_sql varchar2(4000) := null; + v_return varchar2(4000) := null; + + -- Fetch the most inherited attribute + cursor c_attribute is + select + a.attribute_id, a.static_p, a.storage, a.table_name, a.attribute_name, + a.object_type, a.column_name, t.id_column + from + acs_attributes a, + (select + object_type, id_column + from + acs_object_types + connect by + object_type = prior supertype + start with + object_type = (select object_type from acs_objects + where object_id = object_id_in) + ) t + where + a.attribute_name = attribute_name_in + and + a.object_type = t.object_type; + + begin + + -- Determine the attribute parameters + open c_attribute; + fetch c_attribute into + v_attr_id, v_static, v_storage, v_table_name, v_attr_name, + v_object_type, v_column, v_id_column; + if c_attribute%NOTFOUND then + close c_attribute; + raise_application_error (-20000, + 'No such attribute ' || v_object_type || '::' || attribute_name_in || + ' in acs_object.get_attribute_storage.'); + end if; + close c_attribute; + + -- This should really be done in a trigger on acs_attributes, + -- instead of generating it each time in this function + + -- If there is no specific table name for this attribute, + -- figure it out based on the object type + if v_table_name is null then + + -- Determine the appropriate table name + if v_storage = 'generic' then + -- Generic attribute: table name/column are hardcoded + + v_column := 'attr_value'; + + if v_static = 'f' then + v_table_name := 'acs_attribute_values'; + v_key_sql := '(object_id = ' || object_id_in || ' and ' || + 'attribute_id = ' || v_attr_id || ')'; + else + v_table_name := 'acs_static_attr_values'; + v_key_sql := '(object_type = ''' || v_object_type || ''' and ' || + 'attribute_id = ' || v_attr_id || ')'; + end if; + + else + -- Specific attribute: table name/column need to be retreived + + if v_static = 'f' then + select + table_name, id_column + into + v_table_name, v_id_column + from + acs_object_types + where + object_type = v_object_type; + else + raise_application_error(-20000, + 'No table name specified for storage specific static attribute ' || + v_object_type || '::' || attribute_name_in || + ' in acs_object.get_attribute_storage.'); + end if; + + end if; + else + -- There is a custom table name for this attribute. + -- Get the id column out of the acs_object_tables + -- Raise an error if not found + select id_column into v_id_column from acs_object_type_tables + where object_type = v_object_type + and table_name = v_table_name; + + end if; + + if v_column is null then + + if v_storage = 'generic' then + v_column := 'attr_value'; + else + v_column := v_attr_name; + end if; + + end if; + + if v_key_sql is null then + if v_static = 'f' then + v_key_sql := v_id_column || ' = ' || object_id_in ; + else + v_key_sql := v_id_column || ' = ''' || v_object_type || ''''; + end if; + end if; + + exception when no_data_found then + if c_attribute%ISOPEN then + close c_attribute; + end if; + raise_application_error(-20000, 'No data found for attribute ' || + v_object_type || '::' || attribute_name_in || + ' in acs_object.get_attribute_storage'); + + end get_attribute_storage; + + -- Get/set the value of an object attribute, as long as + -- the type can be cast to varchar2 + function get_attribute ( + object_id_in in acs_objects.object_id%TYPE, + attribute_name_in in acs_attributes.attribute_name%TYPE + ) return varchar2 + is + v_table_name varchar2(200); + v_column varchar2(200); + v_key_sql varchar2(4000); + v_return varchar2(4000); + begin + + get_attribute_storage(object_id_in, attribute_name_in, + v_column, v_table_name, v_key_sql); + + begin + execute immediate 'select ' + || v_column || ' from ' || v_table_name || ' where ' || v_key_sql + into + v_return; + exception when no_data_found then + return null; + end; + + return v_return; + end get_attribute; + + procedure set_attribute ( + object_id_in in acs_objects.object_id%TYPE, + attribute_name_in in acs_attributes.attribute_name%TYPE, + value_in in varchar2 + ) + is + v_table_name varchar2(200); + v_column varchar2(200); + v_key_sql varchar2(4000); + v_return varchar2(4000); + v_dummy integer; + begin + + get_attribute_storage(object_id_in, attribute_name_in, + v_column, v_table_name, v_key_sql); + + execute immediate 'update ' + || v_table_name || ' set ' || v_column || ' = :value where ' || v_key_sql + using value_in; + + end set_attribute; + + function check_context_index ( + object_id in acs_objects.object_id%TYPE, + ancestor_id in acs_objects.object_id%TYPE, + n_generations in integer + ) return char + is + n_rows integer; + n_gens integer; + begin + -- Verify that this row exists in the index. + select decode(count(*),0,0,1) into n_rows + from acs_object_context_index + where object_id = check_context_index.object_id + and ancestor_id = check_context_index.ancestor_id; + + if n_rows = 1 then + -- Verify that the count is correct. + select n_generations into n_gens + from acs_object_context_index + where object_id = check_context_index.object_id + and ancestor_id = check_context_index.ancestor_id; + + if n_gens != n_generations then + acs_log.error('acs_object.check_representation', 'Ancestor ' || + ancestor_id || ' of object ' || object_id || + ' reports being generation ' || n_gens || + ' when it is actually generation ' || n_generations || + '.'); + return 'f'; + else + return 't'; + end if; + else + acs_log.error('acs_object.check_representation', 'Ancestor ' || + ancestor_id || ' of object ' || object_id || + ' is missing an entry in acs_object_context_index.'); + return 'f'; + end if; + end; + + function check_object_ancestors ( + object_id in acs_objects.object_id%TYPE, + ancestor_id in acs_objects.object_id%TYPE, + n_generations in integer + ) return char + is + context_id acs_objects.context_id%TYPE; + security_inherit_p acs_objects.security_inherit_p%TYPE; + n_rows integer; + n_gens integer; + result char(1); + begin + -- OBJECT_ID is the object we are verifying + -- ANCESTOR_ID is the current ancestor we are tracking + -- N_GENERATIONS is how far ancestor_id is from object_id + + -- Note that this function is only supposed to verify that the + -- index contains each ancestor for OBJECT_ID. It doesn''t + -- guarantee that there aren''t extraneous rows or that + -- OBJECT_ID''s children are contained in the index. That is + -- verified by seperate functions. + + result := 't'; + + -- Grab the context and security_inherit_p flag of the current + -- ancestor''s parent. + select context_id, security_inherit_p into context_id, security_inherit_p + from acs_objects + where object_id = check_object_ancestors.ancestor_id; + + if ancestor_id = 0 then + if context_id is null then + result := 't'; + else + -- This can be a constraint, can''t it? + acs_log.error('acs_object.check_representation', + 'Object 0 doesn''t have a null context_id'); + result := 'f'; + end if; + else + if context_id is null or security_inherit_p = 'f' then + context_id := 0; + end if; + + if check_context_index(object_id, ancestor_id, n_generations) = 'f' then + result := 'f'; + end if; + + if check_object_ancestors(object_id, context_id, + n_generations + 1) = 'f' then + result := 'f'; + end if; + end if; + + return result; + end; + + function check_object_descendants ( + object_id in acs_objects.object_id%TYPE, + descendant_id in acs_objects.object_id%TYPE, + n_generations in integer + ) return char + is + result char(1); + begin + -- OBJECT_ID is the object we are verifying. + -- DESCENDANT_ID is the current descendant we are tracking. + -- N_GENERATIONS is how far the current DESCENDANT_ID is from + -- OBJECT_ID. + + -- This function will verfy that each actualy descendant of + -- OBJECT_ID has a row in the index table. It does not check that + -- there aren't extraneous rows or that the ancestors of OBJECT_ID + -- are maintained correctly. + + result := 't'; + + -- First verify that OBJECT_ID and DESCENDANT_ID are actually in + -- the index. + if check_context_index(descendant_id, object_id, n_generations) = 'f' then + result := 'f'; + end if; + + -- For every child that reports inheriting from OBJECT_ID we need to call + -- ourselves recursively. + for obj in (select * + from acs_objects + where context_id = descendant_id + and security_inherit_p = 't') loop + if check_object_descendants(object_id, obj.object_id, + n_generations + 1) = 'f' then + result := 'f'; + end if; + end loop; + + return result; + end; + + function check_path ( + object_id in acs_objects.object_id%TYPE, + ancestor_id in acs_objects.object_id%TYPE + ) return char + is + context_id acs_objects.context_id%TYPE; + security_inherit_p acs_objects.security_inherit_p%TYPE; + begin + if object_id = ancestor_id then + return 't'; + end if; + + select context_id, security_inherit_p into context_id, security_inherit_p + from acs_objects + where object_id = check_path.object_id; + + if context_id is null or security_inherit_p = 'f' then + context_id := 0; + end if; + + return check_path(context_id, ancestor_id); + end; + + function check_representation ( + object_id in acs_objects.object_id%TYPE + ) return char + is + result char(1); + object_type acs_objects.object_type%TYPE; + n_rows integer; + begin + result := 't'; + acs_log.notice('acs_object.check_representation', + 'Running acs_object.check_representation on object_id = ' || + object_id || '.'); + + -- If this fails then there isn''t even an object associated with + -- this id. I'm going to let that error propogate as an exception. + select object_type into object_type + from acs_objects + where object_id = check_representation.object_id; + + acs_log.notice('acs_object.check_representation', + 'OBJECT STORAGE INTEGRITY TEST'); + + -- Let's look through every primary storage table associated with + -- this object type and all of its supertypes and make sure there + -- is a row with OBJECT_ID as theh primary key. + for t in (select t.object_type, t.table_name, t.id_column + from acs_object_type_supertype_map m, acs_object_types t + where m.ancestor_type = t.object_type + and m.object_type = check_representation.object_type + union + select object_type, table_name, id_column + from acs_object_types + where object_type = check_representation.object_type) loop + execute immediate 'select decode(count(*),0,0,1) from ' || t.table_name || + ' where ' || t.id_column || ' = ' || object_id + into n_rows; + + if n_rows = 0 then + result := 'f'; + acs_log.error('acs_object.check_representation', + 'Table ' || t.table_name || ' (primary storage for ' || + t.object_type || ') doesn''t have a row for object ' || + object_id || ' of type ' || object_type || '.'); + end if; + end loop; + + acs_log.notice('acs_object.check_representation', + 'OBJECT CONTEXT INTEGRITY TEST'); + + -- Do a bunch of dirt simple sanity checks. + + -- First let's check that all of our ancestors appear in + -- acs_object_context_index with the correct generation listed. + if check_object_ancestors(object_id, object_id, 0) = 'f' then + result := 'f'; + end if; + + -- Now let's check that all of our descendants appear in + -- acs_object_context_index with the correct generation listed. + if check_object_descendants(object_id, object_id, 0) = 'f' then + result := 'f'; + end if; + + -- Ok, we know that the index contains every entry that it is + -- supposed to have. Now let's make sure it doesn't contain any + -- extraneous entries. + for row in (select * + from acs_object_context_index + where object_id = check_representation.object_id + or ancestor_id = check_representation.object_id) loop + if check_path(row.object_id, row.ancestor_id) = 'f' then + acs_log.error('acs_object.check_representation', + 'acs_object_context_index contains an extraneous row: ' || + 'object_id = ' || row.object_id || ', ancestor_id = ' || + row.ancestor_id || ', n_generations = ' || + row.n_generations || '.'); + result := 'f'; + end if; + end loop; + + acs_log.notice('acs_object.check_representation', + 'Done running acs_object.check_representation ' || + 'on object_id = ' || object_id || '.'); + return result; + end check_representation; + + procedure update_last_modified ( + object_id in acs_objects.object_id%TYPE, + last_modified in acs_objects.last_modified%TYPE default sysdate + ) + is + v_parent_id acs_objects.context_id%TYPE; + begin + update acs_objects + set acs_objects.last_modified = acs_object.update_last_modified.last_modified + where acs_objects.object_id in (select ao.object_id + from acs_objects ao + connect by prior ao.context_id = ao.object_id + start with ao.object_id = acs_object.update_last_modified.object_id) + and acs_objects.context_id is not null + and acs_objects.object_id != 0; + end update_last_modified; + +end acs_object; +/ +show errors + +-- DRB: Change security context to object -4 + +drop trigger acs_objects_context_id_in_tr; +drop trigger acs_objects_context_id_up_tr; + +delete from acs_magic_objects +where name = 'security_context_root'; + +declare + foo acs_objects.object_id%TYPE; +begin + foo := acs_object.new ( + object_id => -4, + object_type => 'acs_object' + ); +end; +/ +show errors; + +insert into acs_magic_objects + (name, object_id) +values + ('security_context_root', -4); + +update acs_object_context_index +set ancestor_id = -4 +where ancestor_id = 0; + +update acs_object_context_index +set object_id = -4 +where object_id = 0; + +update acs_permissions +set object_id = -4 +where object_id = 0; + +update acs_objects +set context_id = -4 +where context_id = 0; + +-- Content Repository sets parent_id to security_context_root +-- for content modules + +update cr_items +set parent_id = -4 +where parent_id = 0; + +begin + acs_object.delete(0); +end; +/ +show errors; + +create or replace trigger acs_objects_context_id_in_tr +after insert on acs_objects +for each row +declare + security_context_root acs_magic_objects.object_id%TYPE; +begin + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (:new.object_id, :new.object_id, 0); + + if :new.context_id is not null and :new.security_inherit_p = 't' then + insert into acs_object_context_index + (object_id, ancestor_id, + n_generations) + select + :new.object_id as object_id, ancestor_id, + n_generations + 1 as n_generations + from acs_object_context_index + where object_id = :new.context_id; + else + + select object_id into security_context_root + from acs_magic_objects + where name = 'security_context_root'; + + if :new.object_id != security_context_root then + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (:new.object_id, security_context_root, 1); + end if; + + end if; +end; +/ +show errors + +create or replace trigger acs_objects_context_id_up_tr +after update on acs_objects +for each row +declare + security_context_root acs_magic_objects.object_id%TYPE; +begin + if :new.object_id = :old.object_id and + :new.context_id = :old.context_id and + :new.security_inherit_p = :old.security_inherit_p then + return; + end if; + + -- Remove my old ancestors from my descendants. + delete from acs_object_context_index + where object_id in (select object_id + from acs_object_contexts + where ancestor_id = :old.object_id) + and ancestor_id in (select ancestor_id + from acs_object_contexts + where object_id = :old.object_id); + + -- Kill all my old ancestors. + delete from acs_object_context_index + where object_id = :old.object_id; + + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (:new.object_id, :new.object_id, 0); + + if :new.context_id is not null and :new.security_inherit_p = 't' then + -- Now insert my new ancestors for my descendants. + for pair in (select * + from acs_object_context_index + where ancestor_id = :new.object_id) loop + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + select + pair.object_id, ancestor_id, + n_generations + pair.n_generations + 1 as n_generations + from acs_object_context_index + where object_id = :new.context_id; + end loop; + else + + select object_id into security_context_root + from acs_magic_objects + where name = 'security_context_root'; + + if :new.object_id != 0 then + -- We need to make sure that :NEW.OBJECT_ID and all of its + -- children have security_context_root as an ancestor. + for pair in (select * + from acs_object_context_index + where ancestor_id = :new.object_id) + loop + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (pair.object_id, security_context_root, pair.n_generations + 1); + end loop; + end if; + + end if; +end; +/ +show errors + +---------------------------------------------------------------------------- + +-- DRB: We now will turn the magic -1 party into a group that contains +-- all registered users and a new unregistered visitor. This will allow +-- us to do all permission checking on a materialized version of the +-- party_member_map. + +-- Make our new "Unregistered Visitor" be object 0, which corresponds +-- with the user_id assigned throughout the toolkit Tcl code + +insert into acs_objects + (object_id, object_type) +values + (0, 'person'); + +insert into parties + (party_id) +values + (0); + +insert into persons + (person_id, first_names, last_name) +values + (0, 'Unregistered', 'Visitor'); + +insert into acs_magic_objects + (name, object_id) +values + ('unregistered_visitor', 0); + +-- Now transform the old special -1 party into a legitimate group with +-- one user, our Unregistered Visitor + +update acs_objects +set object_type = 'group' +where object_id = -1; + +insert into groups + (group_id, group_name, join_policy) +values + (-1, 'The Public', 'closed'); + +declare + foo acs_objects.object_id%TYPE; +begin + + -- Add our only user, the Unregistered Visitor + + foo := membership_rel.new ( + rel_type => 'membership_rel', + object_id_one => acs.magic_object_id('the_public'), + object_id_two => acs.magic_object_id('unregistered_visitor'), + member_state => 'approved' + ); + + -- Now declare "The Public" to be composed of itself and the "Registered + -- Users" group + + foo := composition_rel.new ( + rel_type => 'composition_rel', + object_id_one => acs.magic_object_id('the_public'), + object_id_two => acs.magic_object_id('registered_users') + ); +end; +/ +show errors; + +------------------------------------------------------------------------------- + +-- DRB: Replace the old party_emmber_map and party_approved_member_map views +-- (they were both the same and very slow) with a table containing the same +-- information. This can be used to greatly speed permissions checking. + +drop view party_member_map; +drop view party_approved_member_map; + +-- The count column is needed because composition_rels lead to a lot of +-- redundant data in the group element map (i.e. you can belong to the +-- registered users group an infinite number of times, strange concept) + +-- (it is "cnt" rather than "count" because Oracle confuses it with the +-- "count()" aggregate in some contexts) + +-- Though for permission checking we only really need to map parties to +-- member users, the old view included identity entries for all parties +-- in the system. It doesn't cost all that much to maintain the extra +-- rows so we will, just in case some overly clever programmer out there +-- depends on it. + +create table party_approved_member_map ( + party_id integer + constraint party_member_party_fk + references parties, + member_id integer + constraint party_member_member_fk + references parties, + cnt integer, + constraint party_approved_member_map_pk + primary key (party_id, member_id) +); + +-- Need this to speed referential integrity +create index party_member_member_idx on party_approved_member_map(member_id); + +-- Every person is a member of itself + +insert into party_approved_member_map + (party_id, member_id, cnt) +select party_id, party_id, 1 +from parties; + +-- Every party is a member if it is an approved member of +-- some sort of membership_rel + +insert into party_approved_member_map + (party_id, member_id, cnt) +select group_id, member_id, count(*) +from group_approved_member_map +group by group_id, member_id; + +-- Every party is a member if it is an approved member of +-- some sort of relation segment + +insert into party_approved_member_map + (party_id, member_id, cnt) +select segment_id, member_id, count(*) +from rel_seg_approved_member_map +group by segment_id, member_id; + +-- Triggers to maintain party_approved_member_map when parties are create or replaced or +-- destroyed. + +create or replace trigger parties_in_tr after insert on parties +for each row +begin + insert into party_approved_member_map + (party_id, member_id, cnt) + values + (:new.party_id, :new.party_id, 1); +end parties_in_tr; +/ +show errors; + +create or replace trigger parties_del_tr before delete on parties +for each row +begin + delete from party_approved_member_map + where party_id = :old.party_id + and member_id = :old.party_id; +end parties_del_tr; +/ +show errors; + +-- Triggers to maintain party_approved_member_map when relational segments are +-- create or replaced or destroyed. We only remove the (segment_id, member_id) rows as +-- removing the relational segment itself does not remove members from the +-- group with that rel_type. This was intentional on the part of the aD folks +-- who added relational segments to ACS 4.2. + +create or replace trigger rel_segments_in_tr after insert on rel_segments +for each row +begin + insert into party_approved_member_map + (party_id, member_id, cnt) + select :new.segment_id, element_id, 1 + from group_element_index + where group_id = :new.group_id + and rel_type = :new.rel_type; +end rel_segments_in_tr; +/ +show errors; + +create or replace trigger rel_segments_del_tr before delete on rel_segments +for each row +begin + delete from party_approved_member_map + where party_id = :old.segment_id + and member_id in (select element_id + from group_element_index + where group_id = :old.group_id + and rel_type = :old.rel_type); +end parties_del_tr; +/ +show errors; + +-- DRB: Helper functions to maintain the materialized party_approved_member_map. The counting crap +-- has to do with the fact that composition rels create duplicate rows in groups. + +create or replace package party_approved_member is + + procedure add_one( + p_party_id in parties.party_id%TYPE, + p_member_id in parties.party_id%TYPE + ); + + procedure add( + p_party_id in parties.party_id%TYPE, + p_member_id in parties.party_id%TYPE, + p_rel_type in acs_rels.rel_type%TYPE + ); + + procedure remove_one ( + p_party_id in parties.party_id%TYPE, + p_member_id in parties.party_id%TYPE + ); + + procedure remove ( + p_party_id in parties.party_id%TYPE, + p_member_id in parties.party_id%TYPE, + p_rel_type in acs_rels.rel_type%TYPE + ); + +end party_approved_member; +/ +show errors; + +create or replace package body party_approved_member is + + procedure add_one( + p_party_id in parties.party_id%TYPE, + p_member_id in parties.party_id%TYPE + ) + is + begin + + insert into party_approved_member_map + (party_id, member_id, cnt) + values + (p_party_id, p_member_id, 1); + + exception when dup_val_on_index then + update party_approved_member_map + set cnt = cnt + 1 + where party_id = p_party_id + and member_id = p_member_id; + + end add_one; + + procedure add( + p_party_id in parties.party_id%TYPE, + p_member_id in parties.party_id%TYPE, + p_rel_type in acs_rels.rel_type%TYPE + ) + is + begin + + add_one(p_party_id, p_member_id); + + -- if the relation type is mapped to a relational segment map that too + + for v_segments in (select segment_id + from rel_segments + where group_id = p_party_id + and rel_type in (select object_type + from acs_object_types + start with object_type = p_rel_type + connect by prior supertype = object_type)) + loop + add_one(v_segments.segment_id, p_member_id); + end loop; + + end add; + + procedure remove_one ( + p_party_id in parties.party_id%TYPE, + p_member_id in parties.party_id%TYPE + ) + is + begin + + update party_approved_member_map + set cnt = cnt - 1 + where party_id = p_party_id + and member_id = p_member_id; + + delete from party_approved_member_map + where party_id = p_party_id + and member_id = p_member_id + and cnt = 0; + + end remove_one; + + procedure remove ( + p_party_id in parties.party_id%TYPE, + p_member_id in parties.party_id%TYPE, + p_rel_type in acs_rels.rel_type%TYPE + ) + is + begin + + remove_one(p_party_id, p_member_id); + + -- if the relation type is mapped to a relational segment unmap that too + + for v_segments in (select segment_id + from rel_segments + where group_id = p_party_id + and rel_type in (select object_type + from acs_object_types + start with object_type = p_rel_type + connect by prior supertype = object_type)) + loop + remove_one(v_segments.segment_id, p_member_id); + end loop; + + end remove; + +end party_approved_member; +/ +show errors; + +create or replace trigger membership_rels_up_tr +before update on membership_rels +for each row +begin + + if :new.member_state = :old.member_state then + return; + 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 + party_approved_member.add(map.group_id, map.element_id, map.rel_type); + else + party_approved_member.remove(map.group_id, map.element_id, map.rel_type); + end if; + end loop; + +end; +/ +show errors + +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, 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'); + + if :new.member_state = 'approved' then + party_approved_member.add(v_object_id_one, v_object_id_two, v_rel_type); + end if; + + -- 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; +/ +show errors + +create or replace trigger membership_rels_del_tr +before delete on membership_rels +for each row +declare + v_error varchar2(4000); +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_application_error(-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 + party_approved_member.remove(map.group_id, map.element_id, map.rel_type); + end loop; + + delete from group_element_index + where rel_id = :old.rel_id; +end; +/ +show errors; + +-- New fast version of acs_object_party_privilege_map + +create or replace view acs_object_party_privilege_map as +select c.object_id, pdm.descendant as privilege, pamm.member_id as party_id +from acs_object_context_index c, acs_permissions p, acs_privilege_descendant_map pdm, + party_approved_member_map pamm +where c.ancestor_id = p.object_id + and pdm.privilege = p.privilege + and pamm.party_id = p.grantee_id; + +-- Kept to avoid breaking existing code, should eventually go away. + +create or replace view all_object_party_privilege_map as +select * from acs_object_party_privilege_map; + +create or replace package body acs_permission +as + procedure grant_permission ( + object_id acs_permissions.object_id%TYPE, + grantee_id acs_permissions.grantee_id%TYPE, + privilege acs_permissions.privilege%TYPE + ) + as + begin + insert into acs_permissions + (object_id, grantee_id, privilege) + values + (object_id, grantee_id, privilege); + exception + when dup_val_on_index then + return; + end grant_permission; + -- + procedure revoke_permission ( + object_id acs_permissions.object_id%TYPE, + grantee_id acs_permissions.grantee_id%TYPE, + privilege acs_permissions.privilege%TYPE + ) + as + begin + delete from acs_permissions + where object_id = revoke_permission.object_id + and grantee_id = revoke_permission.grantee_id + and privilege = revoke_permission.privilege; + end revoke_permission; + + function permission_p ( + object_id acs_objects.object_id%TYPE, + party_id parties.party_id%TYPE, + privilege acs_privileges.privilege%TYPE + ) return char + as + exists_p char(1); + begin + + select decode(count(*),0,'f','t') into exists_p + from dual where exists + (select 1 + from acs_permissions p, party_approved_member_map m, + acs_object_context_index c, acs_privilege_descendant_map h + where p.object_id = c.ancestor_id + and h.descendant = permission_p.privilege + and c.object_id = permission_p.object_id + and m.member_id = permission_p.party_id + and p.privilege = h.privilege + and p.grantee_id = m.party_id); + + return exists_p; + + end permission_p; + +end acs_permission; +/ +show errors + +create or replace package body acs_group +is + function new ( + group_id in groups.group_id%TYPE default null, + object_type in acs_objects.object_type%TYPE + default 'group', + creation_date in acs_objects.creation_date%TYPE + default sysdate, + creation_user in acs_objects.creation_user%TYPE + default null, + creation_ip in acs_objects.creation_ip%TYPE default null, + email in parties.email%TYPE default null, + url in parties.url%TYPE default null, + group_name in groups.group_name%TYPE, + join_policy in groups.join_policy%TYPE default null, + context_id in acs_objects.context_id%TYPE default null + ) + return groups.group_id%TYPE + is + 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(group_id, object_type, creation_date, creation_user, + creation_ip, email, url, context_id); + + v_join_policy := join_policy; + + -- if join policy wasn't specified, select the default based on group type + if v_join_policy is null then + select count(*) into v_group_type_exists_p + from group_types + where group_type = object_type; + + if v_group_type_exists_p = 1 then + select default_join_policy into v_join_policy + from group_types + where group_type = object_type; + else + v_join_policy := 'open'; + end if; + end if; + + insert into groups + (group_id, group_name, join_policy) + values + (v_group_id, group_name, v_join_policy); + + + -- setup the permissible relationship types for this group + insert into group_rels + (group_rel_id, group_id, rel_type) + select acs_object_id_seq.nextval, v_group_id, g.rel_type + from group_type_rels g + where g.group_type = new.object_type; + + return v_group_id; + end new; + + + procedure delete ( + group_id in groups.group_id%TYPE + ) + is + begin + + -- Delete all segments defined for this group + for row in (select segment_id + from rel_segments + where group_id = acs_group.delete.group_id) loop + + 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 = acs_group.delete.group_id + or r.object_id_two = acs_group.delete.group_id)) loop + execute immediate 'begin ' || row.package_name || '.delete(' || row.rel_id || '); end;'; + end loop; + + party.delete(group_id); + end delete; + + function name ( + group_id in groups.group_id%TYPE + ) + return varchar2 + is + group_name varchar2(200); + begin + select group_name + into group_name + from groups + where group_id = name.group_id; + + return group_name; + end name; + + function member_p ( + party_id in parties.party_id%TYPE, + group_id in groups.group_id%TYPE, + cascade_membership char + ) + return char + is + m_result integer; + begin + + if cascade_membership = 't' then + select count(*) + into m_result + from group_member_map + where group_id = member_p.group_id and + member_id = member_p.party_id; + + if m_result > 0 then + return 't'; + end if; + else + select count(*) + into m_result + 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 = member_p.group_id + and rels.object_id_two = member_p.party_id; + + if m_result > 0 then + return 't'; + end if; + end if; + + return 'f'; + end member_p; + + function check_representation ( + group_id in groups.group_id%TYPE + ) return char + is + result char(1); + begin + result := 't'; + acs_log.notice('acs_group.check_representation', + 'Running check_representation on group ' || group_id); + + if acs_object.check_representation(group_id) = 'f' then + result := 'f'; + end if; + + for c 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(c.rel_id) = 'f' then + result := 'f'; + end if; + end loop; + + for m 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(m.rel_id) = 'f' then + result := 'f'; + end if; + end loop; + + acs_log.notice('acs_group.check_representation', + 'Done running check_representation on group ' || group_id); + return result; + end; + +end acs_group; +/ +show errors Fisheye: Tag 1.4 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1d1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1d1.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1d1.sql 26 Feb 2005 19:48:54 -0000 1.1 @@ -0,0 +1,804 @@ +-- acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql +-- +-- @author Jeff Davis (davis@xarg.net) +-- @creation-date 2002-12-17 +-- @cvs-id $Id: upgrade-4.6-4.6.1d1.sql,v 1.1 2005/02/26 19:48:54 jeffd Exp $ + +-- Add two new datatypes (supported by templating already). +-- + +insert into acs_datatypes + (datatype, max_n_values) +values + ('url', null); + +insert into acs_datatypes + (datatype, max_n_values) +values + ('email', null); + +-- declaring this function isstrict,iscachable can make a significant +-- performance difference since this is used in some potentially +-- expensive queries + +create or replace function acs__magic_object_id (varchar) +returns integer as ' +declare + magic_object_id__name alias for $1; + magic_object_id__object_id acs_objects.object_id%TYPE; +begin + select object_id + into magic_object_id__object_id + from acs_magic_objects + where name = magic_object_id__name; + + return magic_object_id__object_id; + +end;' language 'plpgsql' with(isstrict,iscachable); + +-------------------------------------------------------------------------------- +-- +-- Tilmann Singer - delete direct permissions when deleting an object. +-- +create or replace function acs_object__delete (integer) +returns integer as ' +declare + delete__object_id alias for $1; + obj_type record; +begin + + -- Delete dynamic/generic attributes + delete from acs_attribute_values where object_id = delete__object_id; + + -- Delete direct permissions records. + delete from acs_permissions where object_id = delete__object_id; + + -- select table_name, id_column + -- from acs_object_types + -- start with object_type = (select object_type + -- from acs_objects o + -- where o.object_id = delete__object_id) + -- connect by object_type = prior supertype + + -- There was a gratuitous join against the objects table here, + -- probably a leftover from when this was a join, and not a subquery. + -- Functionally, this was working, but time taken was O(n) where n is the + -- number of objects. OUCH. Fixed. (ben) + for obj_type + in select o2.table_name, o2.id_column + from acs_object_types o1, acs_object_types o2 + where o1.object_type = (select object_type + from acs_objects o + where o.object_id = delete__object_id) + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) + order by o2.tree_sortkey desc + loop + -- Delete from the table. + + -- DRB: I removed the quote_ident calls that DanW originally included + -- because the table names appear to be stored in upper case. Quoting + -- causes them to not match the actual lower or potentially mixed-case + -- table names. We will just forbid squirrely names that include quotes. +-- daveB +-- ETP is creating a new object, but not a table, although it does specify a +-- table name, so we need to check if the table exists. Wp-slim does this too + + if table_exists(obj_type.table_name) then + execute ''delete from '' || obj_type.table_name || + '' where '' || obj_type.id_column || '' = '' || delete__object_id; + end if; + end loop; + + return 0; +end;' language 'plpgsql'; + + +-------------------------------------------------------------------------------- + +-- DRB: Change security context to object -4 + +create or replace function acs_objects_context_id_in_tr () returns opaque as ' +declare + security_context_root integer; +begin + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (new.object_id, new.object_id, 0); + + if new.context_id is not null and new.security_inherit_p = ''t'' then + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + select + new.object_id as object_id, ancestor_id, + n_generations + 1 as n_generations + from acs_object_context_index + where object_id = new.context_id; + else + security_context_root = acs__magic_object_id(''security_context_root''); + if new.object_id != security_context_root then + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (new.object_id, security_context_root, 1); + end if; + end if; + + return new; + +end;' language 'plpgsql'; + +create or replace function acs_objects_context_id_up_tr () returns opaque as ' +declare + pair record; + security_context_root integer; +begin + if new.object_id = old.object_id and + new.context_id = old.context_id and + new.security_inherit_p = old.security_inherit_p then + return new; + end if; + + -- Remove my old ancestors from my descendants. + delete from acs_object_context_index + where object_id in (select object_id + from acs_object_contexts + where ancestor_id = old.object_id) + and ancestor_id in (select ancestor_id + from acs_object_contexts + where object_id = old.object_id); + + -- Kill all my old ancestors. + delete from acs_object_context_index + where object_id = old.object_id; + + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (new.object_id, new.object_id, 0); + + if new.context_id is not null and new.security_inherit_p = ''t'' then + -- Now insert my new ancestors for my descendants. + for pair in select * + from acs_object_context_index + where ancestor_id = new.object_id + LOOP + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + select + pair.object_id, ancestor_id, + n_generations + pair.n_generations + 1 as n_generations + from acs_object_context_index + where object_id = new.context_id; + end loop; + else + security_context_root = acs__magic_object_id(''security_context_root''); + if new.object_id != security_context_root then + -- We need to make sure that new.OBJECT_ID and all of its + -- children have security_context_root as an ancestor. + for pair in select * + from acs_object_context_index + where ancestor_id = new.object_id + LOOP + insert into acs_object_context_index + (object_id, ancestor_id, n_generations) + values + (pair.object_id, security_context_root, pair.n_generations + 1); + end loop; + end if; + end if; + + return new; + +end;' language 'plpgsql'; + +-- DRB: This is the function that actually changes security_context_root +-- to -4 rather than 0 + +drop trigger acs_objects_context_id_in_tr on acs_objects; +drop trigger acs_objects_context_id_up_tr on acs_objects; + +delete from acs_magic_objects +where name = 'security_context_root'; + +select acs_object__new ( + -4, + 'acs_object', + now(), + null, + null, + null + ); + +insert into acs_magic_objects + (name, object_id) +values + ('security_context_root', -4); + +update acs_object_context_index +set ancestor_id = -4 +where ancestor_id = 0; + +update acs_object_context_index +set object_id = -4 +where object_id = 0; + +update acs_permissions +set object_id = -4 +where object_id = 0; + +update acs_objects +set context_id = -4 +where context_id = 0; + +-- Content Repository sets parent_id to security_context_root +-- for content modules + +update cr_items +set parent_id = -4 +where parent_id = 0; + +select acs_object__delete(0); + +create trigger acs_objects_context_id_in_tr after insert on acs_objects +for each row execute procedure acs_objects_context_id_in_tr (); + +create trigger acs_objects_context_id_up_tr after update on acs_objects +for each row execute procedure acs_objects_context_id_up_tr (); + +------------------------------------------------------------------------- + +-- DRB: We now will turn the magic -1 party into a group that contains +-- all registered users and a new unregistered visitor. This will allow +-- us to do all permission checking on a materialized version of the +-- party_member_map. + +-- Make our new "Unregistered Visitor" be object 0, which corresponds +-- with the user_id assigned throughout the toolkit Tcl code + +insert into acs_objects + (object_id, object_type) +values + (0, 'person'); + +insert into parties + (party_id) +values + (0); + +insert into persons + (person_id, first_names, last_name) +values + (0, 'Unregistered', 'Visitor'); + +insert into acs_magic_objects + (name, object_id) +values + ('unregistered_visitor', 0); + +-- Now transform the old special -1 party into a legitimate group with +-- one user, our Unregistered Visitor + +update acs_objects +set object_type = 'group' +where object_id = -1; + +insert into groups + (group_id, group_name, join_policy) +values + (-1, 'The Public', 'closed'); + +-- Add our only user, the Unregistered Visitor + +select membership_rel__new ( + null, + 'membership_rel', + acs__magic_object_id('the_public'), + 0, + 'approved', + null, + null); + +-- Now declare "The Public" to be composed of itself and the "Registered +-- Users" group + +select composition_rel__new ( + null, + 'composition_rel', + acs__magic_object_id('the_public'), + acs__magic_object_id('registered_users'), + null, + null); + +------------------------------------------------------------------------------- + +-- DRB: Replace the old party_emmber_map and party_approved_member_map views +-- (they were both the same and very slow) with a table containing the same +-- information. This can be used to greatly speed permissions checking. + +drop view party_member_map; +drop view party_approved_member_map; + +-- Though for permission checking we only really need to map parties to +-- member users, the old view included identity entries for all parties +-- in the system. It doesn't cost all that much to maintain the extra +-- rows so we will, just in case some overly clever programmer out there +-- depends on it. + +-- This represents a large amount of redundant data which is separately +-- stored in the group_element_index table. We might want to clean this +-- up in the future but time constraints on 4.6.1 require I keep this +-- relatively simple. Implementing a real "subgroup_rel" would help a +-- lot by in itself reducing the number of redundant rows in the two +-- tables. + +create table party_approved_member_map ( + party_id integer + constraint party_member_party_nn + not null + constraint party_member_party_fk + references parties, + member_id integer + constraint party_member_member_nn + not null + constraint party_member_member_fk + references parties, + tag integer + constraint party_member_tag_nn + not null, + constraint party_approved_member_map_pk + primary key (party_id, member_id, tag) +); + +-- Need this to speed referential integrity +create index party_member_member_idx on party_approved_member_map(member_id); + +-- Every person is a member of itself + +insert into party_approved_member_map + (party_id, member_id, tag) +select party_id, party_id, 0 +from parties; + +-- Every party is a member if it is an approved member of +-- some sort of membership_rel + +insert into party_approved_member_map + (party_id, member_id, tag) +select group_id, member_id, rel_id +from group_approved_member_map; + +-- Every party is a member if it is an approved member of +-- some sort of relation segment + +insert into party_approved_member_map + (party_id, member_id, tag) +select segment_id, member_id, rel_id +from rel_seg_approved_member_map; + +analyze party_approved_member_map; + +-- Helper functions to maintain the materialized party_approved_member_map. + +create or replace function party_approved_member__add_one(integer, integer, integer) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; +begin + + insert into party_approved_member_map + (party_id, member_id, tag) + values + (p_party_id, p_member_id, p_rel_id); + + return 1; + +end;' language 'plpgsql'; + +create or replace function party_approved_member__add(integer, integer, integer, varchar) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; + p_rel_type alias for $4; + v_segments record; +begin + + perform party_approved_member__add_one(p_party_id, p_member_id, p_rel_id); + + -- if the relation type is mapped to relational segments unmap them too + + for v_segments in select segment_id + from rel_segments s, acs_object_types o1, acs_object_types o2 + where + o1.object_type = p_rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) + and s.rel_type = o2.object_type + and s.group_id = p_party_id + loop + perform party_approved_member__add_one(v_segments.segment_id, p_member_id, p_rel_id); + end loop; + + return 1; + +end;' language 'plpgsql'; + +create or replace function party_approved_member__remove_one(integer, integer, integer) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; +begin + + delete from party_approved_member_map + where party_id = p_party_id + and member_id = p_member_id + and tag = p_rel_id; + + return 1; + +end;' language 'plpgsql'; + +create or replace function party_approved_member__remove(integer, integer, integer, varchar) returns integer as ' +declare + p_party_id alias for $1; + p_member_id alias for $2; + p_rel_id alias for $3; + p_rel_type alias for $4; + v_segments record; +begin + + perform party_approved_member__remove_one(p_party_id, p_member_id, p_rel_id); + + -- if the relation type is mapped to relational segments unmap them too + + for v_segments in select segment_id + from rel_segments s, acs_object_types o1, acs_object_types o2 + where + o1.object_type = p_rel_type + and o1.tree_sortkey between o2.tree_sortkey and tree_right(o2.tree_sortkey) + and s.rel_type = o2.object_type + and s.group_id = p_party_id + loop + perform party_approved_member__remove_one(v_segments.segment_id, p_member_id, p_rel_id); + end loop; + + return 1; + +end;' language 'plpgsql'; + +-- Triggers to maintain party_approved_member_map when parties are created or +-- destroyed. + +create or replace function parties_in_tr () returns opaque as ' +begin + + insert into party_approved_member_map + (party_id, member_id, tag) + values + (new.party_id, new.party_id, 0); + + return new; + +end;' language 'plpgsql'; + +create trigger parties_in_tr after insert on parties +for each row execute procedure parties_in_tr (); + +create or replace function parties_del_tr () returns opaque as ' +begin + + delete from party_approved_member_map + where party_id = old.party_id + and member_id = old.party_id; + + return old; + +end;' language 'plpgsql'; + +create trigger parties_del_tr before delete on parties +for each row execute procedure parties_del_tr (); + +-- Triggers to maintain party_approved_member_map when relational segments are +-- created or destroyed. We only remove the (segment_id, member_id) rows as +-- removing the relational segment itself does not remove members from the +-- group with that rel_type. This was intentional on the part of the aD folks +-- who added relational segments to ACS 4.2. + +create or replace function rel_segments_in_tr () returns opaque as ' +begin + + insert into party_approved_member_map + (party_id, member_id, tag) + select new.segment_id, element_id, rel_id + from group_element_index + where group_id = new.group_id + and rel_type = new.rel_type; + + return new; + +end;' language 'plpgsql'; + +create trigger rel_segments_in_tr before insert on rel_segments +for each row execute procedure rel_segments_in_tr (); + +create or replace function rel_segments_del_tr () returns opaque as ' +begin + + delete from party_approved_member_map + where party_id = old.segment_id + and member_id in (select element_id + from group_element_index + where group_id = old.group_id + and rel_type = old.rel_type); + + return old; + +end;' language 'plpgsql'; + +create trigger rel_segments_del_tr before delete on rel_segments +for each row execute procedure rel_segments_del_tr (); + +-- The insert trigger was dummied up in groups-create.sql, so we just need +-- to replace the trigger function, not create the trigger + +create or replace 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_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; + + -- 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; + + return new; + +end;' language 'plpgsql'; + +create or replace function membership_rels_up_tr () returns opaque 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 (); + +create or replace function membership_rels_del_tr () returns opaque 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'; + +------------------------------------------------------------------------------------ + +-- DRB: upgrade to Dan Wickstrom's version of acs-permissions which materializes the +-- acs_privilege_descendant_map view. + +drop view acs_privilege_descendant_map; +create table acs_privilege_descendant_map ( + privilege varchar(100) not null + constraint acs_priv_hier_priv_fk + references acs_privileges (privilege), + descendant varchar(100) not null + constraint acs_priv_hier_child_priv_fk + references acs_privileges (privilege) + +); + +-- DRB: Empirical testing showed that even with just 61 entries in the new table +-- this index sped things up by roughly 15% + +create index acs_priv_desc_map_idx on acs_privilege_descendant_map(descendant); + +create view acs_privilege_descendant_map_view +as select p1.privilege, p2.privilege as descendant + from acs_privileges p1, acs_privileges p2 + where exists (select h2.child_privilege + from + acs_privilege_hierarchy_index h1, + acs_privilege_hierarchy_index h2 + where + h1.privilege = p1.privilege + and h2.privilege = p2.privilege + and h2.tree_sortkey between h1.tree_sortkey and tree_right(h1.tree_sortkey)) or + p1.privilege = p2.privilege; + +insert into acs_privilege_descendant_map (privilege, descendant) +select privilege, descendant from acs_privilege_descendant_map_view; + +drop view acs_object_grantee_priv_map; +create view acs_object_grantee_priv_map as +select a.object_id, a.grantee_id, m.descendant as privilege + from acs_permissions_all a, acs_privilege_descendant_map m + where a.privilege = m.privilege; + +create or replace function acs_priv_hier_ins_del_tr () returns opaque as ' +declare + new_value integer; + new_key varbit default null; + v_rec record; + deleted_p boolean; +begin + -- if more than one node was deleted the second trigger call + -- will error out. This check avoids that problem. + + if TG_OP = ''DELETE'' then + select count(*) = 0 into deleted_p + from acs_privilege_hierarchy_index + where old.privilege = privilege + and old.child_privilege = child_privilege; + + if deleted_p then + + return new; + + end if; + end if; + + -- recalculate the table from scratch. + + delete from acs_privilege_hierarchy_index; + + -- first find the top nodes of the tree + + for v_rec in select privilege, child_privilege + from acs_privilege_hierarchy + where privilege + NOT in (select distinct child_privilege + from acs_privilege_hierarchy) + + LOOP + + -- top level node, so find the next key at this level. + + select max(tree_leaf_key_to_int(tree_sortkey)) into new_value + from acs_privilege_hierarchy_index + where tree_level(tree_sortkey) = 1; + + -- insert the new node + + insert into acs_privilege_hierarchy_index + (privilege, child_privilege, tree_sortkey) + values + (v_rec.privilege, v_rec.child_privilege, tree_next_key(null, new_value)); + + -- now recurse down from this node + + PERFORM priv_recurse_subtree(tree_next_key(null, new_value), v_rec.child_privilege); + + end LOOP; + + -- materialize the map view to speed up queries + -- DanW (dcwickstrom@earthlink.net) 30 Jan, 2003 + delete from acs_privilege_descendant_map; + + insert into acs_privilege_descendant_map (privilege, descendant) + select privilege, descendant from acs_privilege_descendant_map_view; + + return new; + +end;' language 'plpgsql'; + + +-- New fast version of acs_object_party_privilege_map + +drop view acs_object_party_privilege_map; +create view acs_object_party_privilege_map as +select c.object_id, pdm.descendant as privilege, pamm.member_id as party_id +from acs_object_context_index c, acs_permissions p, acs_privilege_descendant_map pdm, + party_approved_member_map pamm +where c.ancestor_id = p.object_id + and pdm.privilege = p.privilege + and pamm.party_id = p.grantee_id; + +drop view all_object_party_privilege_map; +create view all_object_party_privilege_map as +select * from acs_object_party_privilege_map; + +-- Really speedy version of permission_p written by Don Baccus + +create or replace function acs_permission__permission_p (integer,integer,varchar) +returns boolean as ' +declare + permission_p__object_id alias for $1; + permission_p__party_id alias for $2; + permission_p__privilege alias for $3; + exists_p boolean; +begin + return exists (select 1 + from acs_permissions p, party_approved_member_map m, + acs_object_context_index c, acs_privilege_descendant_map h + where p.object_id = c.ancestor_id + and h.descendant = permission_p__privilege + and c.object_id = permission_p__object_id + and m.member_id = permission_p__party_id + and p.privilege = h.privilege + and p.grantee_id = m.party_id); +end;' language 'plpgsql'; + +-- No longer needed with fast acs_object_party_privilege_map +drop function acs_permission__user_with_perm_exists_p (integer,varchar); + +