Index: openacs-4/packages/acs-kernel/acs-kernel.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/acs-kernel.info,v diff -u -r1.24 -r1.25 --- openacs-4/packages/acs-kernel/acs-kernel.info 17 Feb 2003 23:55:57 -0000 1.24 +++ openacs-4/packages/acs-kernel/acs-kernel.info 17 May 2003 01:46:25 -0000 1.25 @@ -19,157 +19,19 @@ - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - + + + + + + + + @@ -208,7 +70,13 @@ - + + + + + + + Index: openacs-4/packages/acs-kernel/sql/oracle/acs-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/acs-kernel/sql/oracle/acs-create.sql 18 Feb 2003 20:53:43 -0000 1.4 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-create.sql 17 May 2003 01:46:41 -0000 1.5 @@ -161,26 +161,28 @@ create or replace view registered_users as select p.email, p.url, pe.first_names, pe.last_name, u.*, mr.member_state - from parties p, persons pe, users u, group_member_map m, membership_rels mr + from parties p, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo where party_id = person_id and person_id = user_id and u.user_id = m.member_id and m.rel_id = mr.rel_id - and m.group_id = (select acs.magic_object_id('registered_users') from dual) + and amo.name = 'registered_users' + and m.group_id = amo.object_id and mr.member_state = 'approved' and u.email_verified_p = 't'; create or replace view cc_users as select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id -from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr +from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo where o.object_id = pa.party_id -and pa.party_id = pe.person_id -and pe.person_id = u.user_id -and u.user_id = m.member_id -and m.group_id = acs.magic_object_id('registered_users') -and m.rel_id = mr.rel_id -and m.container_id = m.group_id; + and pa.party_id = pe.person_id + and pe.person_id = u.user_id + and u.user_id = m.member_id + and amo.name = 'registered_users' + and m.group_id = amo.object_id + and m.rel_id = mr.rel_id + and m.container_id = m.group_id; ----------------------------------- -- Community Core Initialization -- Index: openacs-4/packages/acs-kernel/sql/oracle/acs-metadata-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-metadata-create.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/acs-kernel/sql/oracle/acs-metadata-create.sql 16 Sep 2002 21:52:41 -0000 1.5 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-metadata-create.sql 17 May 2003 01:46:41 -0000 1.6 @@ -246,6 +246,16 @@ values ('enumeration', null); + insert into acs_datatypes + (datatype, max_n_values) + values + ('url', null); + + insert into acs_datatypes + (datatype, max_n_values) + values + ('email', null); + commit; end; / Index: openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql,v diff -u -r1.7 -r1.8 --- openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql 18 Feb 2003 20:53:45 -0000 1.7 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql 17 May 2003 01:46:41 -0000 1.8 @@ -526,6 +526,16 @@ -- 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 Index: openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql,v diff -u -r1.6 -r1.7 --- openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql 21 Mar 2003 14:57:44 -0000 1.6 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-permissions-create.sql 17 May 2003 01:46:41 -0000 1.7 @@ -2,8 +2,7 @@ -- acs-kernel/sql/acs-permissions-create.sql -- -- The ACS core permissioning system. The knowledge level of system --- allows you to define a hierarchichal system of privilages, and --- associate them with low level operations on object types. The +-- allows you to define a hierarchichal system of privilages. The -- operational level allows you to grant to any party a privilege on -- any object. -- @@ -16,26 +15,9 @@ --------------------------------------------- --- KNOWLEDGE LEVEL: PRIVILEGES AND ACTIONS -- +-- KNOWLEDGE LEVEL: PRIVILEGES -- --------------------------------------------- --- suggestion: acs_methods, acs_operations, acs_transactions? --- what about cross-type actions? new-stuff? site-wide search? - ---create table acs_methods ( --- object_type not null constraint acs_methods_object_type_fk --- references acs_object_types (object_type), --- method varchar2(100) not null, --- constraint acs_methods_pk --- primary key (object_type, method) ---); - ---comment on table acs_methods is ' --- Each row in the acs_methods table directly corresponds to a --- transaction on an object. For example an sql statement that updates a --- bboard message would require an entry in this table. ---' - create table acs_privileges ( privilege varchar2(100) not null constraint acs_privileges_pk primary key, @@ -55,20 +37,8 @@ -- create bitmap index acs_priv_hier_child_priv_idx on acs_privilege_hierarchy (child_privilege); create index acs_priv_hier_child_priv_idx on acs_privilege_hierarchy (child_privilege); ---create table acs_privilege_method_rules ( --- privilege not null constraint acs_priv_method_rules_priv_fk --- references acs_privileges (privilege), --- object_type varchar2(100) not null, --- method varchar2(100) not null, --- constraint acs_privilege_method_rules_pk --- primary key (privilege, object_type, method), --- constraint acs_priv_meth_rul_type_meth_fk --- foreign key (object_type, method) references acs_methods ---); - comment on table acs_privileges is ' - The rows in this table correspond to aggregations of specific - methods. Privileges share a global namespace. This is to avoid a + Privileges share a global namespace. This is to avoid a situation where granting the foo privilege on one type of object can have an entirely different meaning than granting the foo privilege on another type of object. @@ -79,32 +49,6 @@ privilege is a superset of the bar privilege. '; ---comment on table acs_privilege_method_rules is ' --- The privilege method map allows us to create rules that specify which --- methods a certain privilege is allowed to invoke in the context of a --- particular object_type. Note that the same privilege can have --- different methods for different object_types. This is because each --- method corresponds to a piece of code, and the code that displays an --- instance of foo will be different than the code that displays an --- instance of bar. If there are no methods defined for a particular --- (privilege, object_type) pair, then that privilege is not relavent to --- that object type, for example there is no way to moderate a user, so --- there would be no additional methods that you could invoke if you --- were granted moderate on a user. ---' - ---create or replace view acs_privilege_method_map ---as select r1.privilege, pmr.object_type, pmr.method --- from acs_privileges r1, acs_privileges r2, acs_privilege_method_rules pmr --- where r2.privilege in (select distinct rh.child_privilege --- from acs_privilege_hierarchy rh --- start with privilege = r1.privilege --- connect by prior child_privilege = privilege --- union --- select r1.privilege --- from dual) --- and r2.privilege = pmr.privilege; - create or replace package acs_privilege as @@ -244,12 +188,6 @@ create or replace view all_object_party_privilege_map as select * from acs_object_party_privilege_map; - ---create or replace view acs_object_party_method_map ---as select opp.object_id, opp.party_id, pm.object_type, pm.method --- from acs_object_party_privilege_map opp, acs_privilege_method_map pm --- where opp.privilege = pm.privilege; - create or replace package acs_permission as Index: openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql,v diff -u -r1.6 -r1.7 --- openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql 21 Mar 2003 14:57:44 -0000 1.6 +++ openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql 17 May 2003 01:46:41 -0000 1.7 @@ -143,6 +143,18 @@ (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 elements be elements of my new composite group insert into group_element_index (group_id, element_id, rel_id, container_id, @@ -173,6 +185,19 @@ v_rel_type, 'composition_rel'); -- Add rows for my elements + + 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 = 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) @@ -228,6 +253,15 @@ and component_id = map.component_id; if n_rows = 0 then + + for members in (select member_id, rel_type + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id) + loop + party_approved_member.remove(map.group_id, members.member_id, members.rel_type); + end loop; + delete from group_element_index where group_id = map.group_id and container_id = map.component_id @@ -236,7 +270,6 @@ end loop; - for map in (select * from group_component_map where group_id in (select group_id @@ -264,18 +297,27 @@ and component_id = map.component_id; if n_rows = 0 then + + for members in (select member_id, rel_type + from group_approved_member_map + where group_id = map.group_id + and container_id = map.component_id) + loop + party_approved_member.remove(map.group_id, members.member_id, members.rel_type); + end loop; + 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; end; / show errors - -------------------- -- PACKAGE BODIES -- -------------------- Index: openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6-4.6.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/upgrade/Attic/upgrade-4.6-4.6.1.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6-4.6.1.sql 16 Jan 2003 13:33:41 -0000 1.2 +++ openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6-4.6.1.sql 17 May 2003 01:46:55 -0000 1.3 @@ -1,2 +1,1475 @@ --- the 4.6 to 4.6.1 change only affected postgresql --- file here to prevent confusion. +-- 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.1 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6.1-4.6.2.sql'. Fisheye: No comparison available. Pass `N' to diff? Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/oracle/upgrade/upgrade-4.6.2-4.6.3.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql,v diff -u -r1.13 -r1.14 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 17 Mar 2003 21:58:36 -0000 1.13 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 17 May 2003 01:47:10 -0000 1.14 @@ -106,27 +106,30 @@ create view registered_users as select p.email, p.url, pe.first_names, pe.last_name, u.*, mr.member_state - from parties p, persons pe, users u, group_member_map m, membership_rels mr + from parties p, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo where party_id = person_id and person_id = user_id and u.user_id = m.member_id and m.rel_id = mr.rel_id - and m.group_id = acs__magic_object_id('registered_users') + and amo.name = 'registered_users' + and m.group_id = amo.object_id and mr.member_state = 'approved' and u.email_verified_p = 't'; create view cc_users as select o.*, pa.*, pe.*, u.*, mr.member_state, mr.rel_id -from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr +from acs_objects o, parties pa, persons pe, users u, group_member_map m, membership_rels mr, acs_magic_objects amo where o.object_id = pa.party_id -and pa.party_id = pe.person_id -and pe.person_id = u.user_id -and u.user_id = m.member_id -and m.group_id = acs__magic_object_id('registered_users') -and m.rel_id = mr.rel_id -and m.container_id = m.group_id; + and pa.party_id = pe.person_id + and pe.person_id = u.user_id + and u.user_id = m.member_id + and amo.name = 'registered_users' + and m.group_id = amo.object_id + and m.rel_id = mr.rel_id + and m.container_id = m.group_id; + ----------------------------------- -- Community Core Initialization -- ----------------------------------- Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-logs-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-logs-create.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-logs-create.sql 28 Apr 2001 17:35:30 -0000 1.3 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-logs-create.sql 17 May 2003 01:47:10 -0000 1.4 @@ -14,7 +14,7 @@ log_id integer constraint acs_logs_pk primary key, - log_date timestamp default now() not null, + log_date timestamptz default current_timestamp not null, log_level varchar(20) not null constraint acs_logs_log_level_ck check (log_level in ('notice', 'warn', 'error', Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql,v diff -u -r1.17 -r1.18 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql 27 Dec 2002 00:48:58 -0000 1.17 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-metadata-create.sql 17 May 2003 01:47:10 -0000 1.18 @@ -338,7 +338,17 @@ values (''enumeration'', null); + insert into acs_datatypes + (datatype, max_n_values) + values + (''url'', null); + insert into acs_datatypes + (datatype, max_n_values) + values + (''email'', null); + + return 0; end;' language 'plpgsql'; Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql,v diff -u -r1.39 -r1.40 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 18 Feb 2003 20:54:32 -0000 1.39 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 17 May 2003 01:47:10 -0000 1.40 @@ -182,9 +182,9 @@ references acs_objects(object_id), security_inherit_p boolean default 't' not null, creation_user integer, - creation_date timestamp default now() not null, + creation_date timestamptz default current_timestamp not null, creation_ip varchar(50), - last_modified timestamp default now() not null, + last_modified timestamptz default current_timestamp not null, modifying_user integer, modifying_ip varchar(50), tree_sortkey varbit, @@ -588,7 +588,7 @@ -- function new -create function acs_object__new (integer,varchar,timestamp with time zone,integer,varchar,integer,boolean) +create function acs_object__new (integer,varchar,timestamptz,integer,varchar,integer,boolean) returns integer as ' declare new__object_id alias for $1; -- default null @@ -599,7 +599,7 @@ new__context_id alias for $6; -- default null new__security_inherit_p alias for $7; -- default ''t'' v_object_id acs_objects.object_id%TYPE; - v_creation_date timestamp; + v_creation_date timestamptz; begin if new__object_id is null then select acs_object_id_seq.nextval @@ -629,7 +629,7 @@ end;' language 'plpgsql'; -- function new -create function acs_object__new (integer,varchar,timestamp with time zone,integer,varchar,integer) +create function acs_object__new (integer,varchar,timestamptz,integer,varchar,integer) returns integer as ' declare new__object_id alias for $1; -- default null @@ -639,7 +639,7 @@ new__creation_ip alias for $5; -- default null new__context_id alias for $6; -- default null v_object_id acs_objects.object_id%TYPE; - v_creation_date timestamp; + v_creation_date timestamptz; begin if new__object_id is null then select acs_object_id_seq.nextval @@ -687,6 +687,9 @@ -- 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 @@ -1341,13 +1344,13 @@ return acs_object__update_last_modified(acs_object__update_last_modified__object_id, now()); end;' language 'plpgsql'; -create function acs_object__update_last_modified (integer, timestamp with time zone) +create function acs_object__update_last_modified (integer, timestamptz) returns integer as ' declare acs_object__update_last_modified__object_id alias for $1; acs_object__update_last_modified__last_modified alias for $2; -- default now() v_parent_id integer; - v_last_modified timestamp; + v_last_modified timestamptz; begin if acs_object__update_last_modified__last_modified is null then v_last_modified := now(); Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql,v diff -u -r1.25 -r1.26 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 17 Mar 2003 21:58:36 -0000 1.25 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql 17 May 2003 01:47:10 -0000 1.26 @@ -2,8 +2,7 @@ -- acs-kernel/sql/acs-permissions-create.sql -- -- The ACS core permissioning system. The knowledge level of system --- allows you to define a hierarchichal system of privilages, and --- associate them with low level operations on object types. The +-- allows you to define a hierarchichal system of privilages. The -- operational level allows you to grant to any party a privilege on -- any object. -- @@ -19,23 +18,6 @@ -- KNOWLEDGE LEVEL: PRIVILEGES AND ACTIONS -- --------------------------------------------- --- suggestion: acs_methods, acs_operations, acs_transactions? --- what about cross-type actions? new-stuff? site-wide search? - ---create table acs_methods ( --- object_type not null constraint acs_methods_object_type_fk --- references acs_object_types (object_type), --- method varchar2(100) not null, --- constraint acs_methods_pk --- primary key (object_type, method) ---); - ---comment on table acs_methods is ' --- Each row in the acs_methods table directly corresponds to a --- transaction on an object. For example an sql statement that updates a --- bboard message would require an entry in this table. ---' - create table acs_privileges ( privilege varchar(100) not null constraint acs_privileges_pk primary key, @@ -201,6 +183,20 @@ on acs_privilege_hierarchy for each row execute procedure acs_priv_hier_ins_del_tr (); +create or replace function acs_priv_del_tr () returns opaque as ' +begin + + delete from acs_privilege_descendant_map + where privilege = old.privilege; + + return old; + +end;' language 'plpgsql'; + +create trigger acs_priv_del_tr before delete +on acs_privileges for each row +execute procedure acs_priv_del_tr (); + create function priv_recurse_subtree(varbit, varchar) returns integer as ' declare @@ -256,20 +252,8 @@ end;' language 'plpgsql'; ---create table acs_privilege_method_rules ( --- privilege not null constraint acs_priv_method_rules_priv_fk --- references acs_privileges (privilege), --- object_type varchar2(100) not null, --- method varchar2(100) not null, --- constraint acs_privilege_method_rules_pk --- primary key (privilege, object_type, method), --- constraint acs_priv_meth_rul_type_meth_fk --- foreign key (object_type, method) references acs_methods ---); - comment on table acs_privileges is ' - The rows in this table correspond to aggregations of specific - methods. Privileges share a global namespace. This is to avoid a + Privileges share a global namespace. This is to avoid a situation where granting the foo privilege on one type of object can have an entirely different meaning than granting the foo privilege on another type of object. @@ -280,20 +264,6 @@ privilege is a superset of the bar privilege. '; ---comment on table acs_privilege_method_rules is ' --- The privilege method map allows us to create rules that specify which --- methods a certain privilege is allowed to invoke in the context of a --- particular object_type. Note that the same privilege can have --- different methods for different object_types. This is because each --- method corresponds to a piece of code, and the code that displays an --- instance of foo will be different than the code that displays an --- instance of bar. If there are no methods defined for a particular --- (privilege, object_type) pair, then that privilege is not relavent to --- that object type, for example there is no way to moderate a user, so --- there would be no additional methods that you could invoke if you --- were granted moderate on a user. ---' - create function acs_privilege__create_privilege (varchar,varchar,varchar) returns integer as ' declare Index: openacs-4/packages/acs-kernel/sql/postgresql/apm-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/apm-create.sql,v diff -u -r1.36 -r1.37 --- openacs-4/packages/acs-kernel/sql/postgresql/apm-create.sql 31 Jan 2003 14:02:24 -0000 1.36 +++ openacs-4/packages/acs-kernel/sql/postgresql/apm-create.sql 17 May 2003 01:47:10 -0000 1.37 @@ -267,7 +267,7 @@ constraint apm_package_vers_desc_for_ck check (description_format in ('text/html', 'text/plain')), description text, - release_date timestamp, + release_date timestamptz, vendor varchar(500), vendor_uri varchar(1500), enabled_p boolean default 'f' @@ -281,8 +281,8 @@ data_model_loaded_p boolean default 'f' constraint apm_package_vers_dml_p_nn not null, cvs_import_results text, - activation_date timestamp, - deactivation_date timestamp, + activation_date timestamptz, + deactivation_date timestamptz, -- FIXME: store the tarball in the content-repository -- distribution_tarball blob, item_id integer, @@ -292,8 +292,8 @@ -- references cr_items(item_id), content_length integer, distribution_uri varchar(1500), - distribution_date timestamp, - auto_mount varchar(50), + distribution_date timestamptz, + auto_mount varchar(50) default null, constraint apm_package_vers_id_name_un unique(package_key, version_name) ); @@ -1669,7 +1669,7 @@ -- function new -create function apm_package__new (integer,varchar,varchar,varchar,timestamp with time zone,integer,varchar,integer) +create function apm_package__new (integer,varchar,varchar,varchar,timestamptz,integer,varchar,integer) returns integer as ' declare new__package_id alias for $1; -- default null @@ -1897,7 +1897,7 @@ end;' language 'plpgsql'; -- create or replace package body apm_package_version -create function apm_package_version__new (integer,varchar,varchar,varchar,varchar,varchar,varchar,timestamp with time zone,varchar,varchar,varchar,boolean,boolean) returns integer as ' +create function apm_package_version__new (integer,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,varchar,varchar,boolean,boolean) returns integer as ' declare apm_pkg_ver__version_id alias for $1; -- default null apm_pkg_ver__package_key alias for $2; @@ -2054,7 +2054,7 @@ -- function edit -create function apm_package_version__edit (integer,integer,varchar,varchar,varchar,varchar,varchar,timestamp with time zone,varchar,varchar,varchar,boolean,boolean) +create function apm_package_version__edit (integer,integer,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,varchar,varchar,boolean,boolean) returns integer as ' declare edit__new_version_id alias for $1; -- default null @@ -2600,7 +2600,7 @@ -- function new -create function apm_application__new (integer,varchar,varchar,varchar,timestamp with time zone,integer,varchar,integer) +create function apm_application__new (integer,varchar,varchar,varchar,timestamptz,integer,varchar,integer) returns integer as ' declare application_id alias for $1; -- default null @@ -2650,7 +2650,7 @@ -- create or replace package body apm_service -- function new -create function apm_service__new (integer,varchar,varchar,varchar,timestamp with time zone,integer,varchar,integer) +create function apm_service__new (integer,varchar,varchar,varchar,timestamptz,integer,varchar,integer) returns integer as ' declare service_id alias for $1; -- default null Index: openacs-4/packages/acs-kernel/sql/postgresql/apm-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/apm-drop.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-kernel/sql/postgresql/apm-drop.sql 9 May 2001 22:32:25 -0000 1.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/apm-drop.sql 17 May 2003 01:47:10 -0000 1.3 @@ -80,7 +80,7 @@ drop function apm__set_value (integer,integer,varchar); drop function apm__set_value (integer,varchar,varchar); drop function apm_package__initialize_parameters (integer,varchar); -drop function apm_package__new (integer,varchar,varchar,varchar,timestamp,integer,varchar,integer); +drop function apm_package__new (integer,varchar,varchar,varchar,timestamptz,integer,varchar,integer); drop function apm_package__delete (integer); drop function apm_package__initial_install_p (varchar); drop function apm_package__singleton_p (varchar); @@ -89,12 +89,12 @@ drop function apm_package__enable (integer); drop function apm_package__disable (integer); drop function apm_package__highest_version (varchar); -drop function apm_package_version__new (integer,varchar,varchar,varchar,varchar,varchar,varchar,timestamp,varchar,varchar,boolean,boolean); +drop function apm_package_version__new (integer,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,varchar,boolean,boolean); drop function apm_package_version__delete (integer); drop function apm_package_version__enable (integer); drop function apm_package_version__disable (integer); drop function apm_package_version__copy (integer,integer,varchar,varchar); -drop function apm_package_version__edit (integer,integer,varchar,varchar,varchar,varchar,varchar,timestamp,varchar,varchar,boolean,boolean); +drop function apm_package_version__edit (integer,integer,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,varchar,boolean,boolean); drop function apm_package_version__add_file (integer,integer,varchar,varchar, varchar); drop function apm_package_version__remove_file (integer,varchar); drop function apm_package_version__add_interface (integer,integer,varchar,varchar); @@ -113,9 +113,9 @@ drop function apm_package_type__num_parameters (varchar); drop function apm_parameter_value__new (integer,integer,integer,varchar); drop function apm_parameter_value__delete (integer); -drop function apm_application__new (integer,varchar,varchar,varchar,timestamp,integer,varchar,integer); +drop function apm_application__new (integer,varchar,varchar,varchar,timestamptz,integer,varchar,integer); drop function apm_application__delete (integer); -drop function apm_service__new (integer,varchar,varchar,varchar,timestamp,integer,varchar,integer); +drop function apm_service__new (integer,varchar,varchar,varchar,timestamptz,integer,varchar,integer); drop function apm_service__delete (integer); \t Index: openacs-4/packages/acs-kernel/sql/postgresql/community-core-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/community-core-create.sql,v diff -u -r1.10 -r1.11 --- openacs-4/packages/acs-kernel/sql/postgresql/community-core-create.sql 21 Dec 2002 22:31:17 -0000 1.10 +++ openacs-4/packages/acs-kernel/sql/postgresql/community-core-create.sql 17 May 2003 01:47:10 -0000 1.11 @@ -269,39 +269,7 @@ -- PARTY PACKAGE -- ------------------- --- create or replace package party --- as --- --- function new ( --- party_id in parties.party_id%TYPE default null, --- object_type in acs_objects.object_type%TYPE --- default 'party', --- 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, --- url in parties.url%TYPE default null, --- context_id in acs_objects.context_id%TYPE default null --- ) return parties.party_id%TYPE; --- --- procedure delete ( --- party_id in parties.party_id%TYPE --- ); --- --- function name ( --- party_id in parties.party_id%TYPE --- ) return varchar2; --- --- end party; - --- show errors - - --- create or replace package body party --- function new -create function party__new (integer,varchar,timestamp with time zone,integer,varchar,varchar,varchar,integer) +create function party__new (integer,varchar,timestamptz,integer,varchar,varchar,varchar,integer) returns integer as ' declare new__party_id alias for $1; -- default null @@ -327,8 +295,6 @@ end;' language 'plpgsql'; - --- procedure delete create function party__delete (integer) returns integer as ' declare @@ -339,8 +305,6 @@ return 0; end;' language 'plpgsql'; - --- function name create function party__name (integer) returns varchar as ' declare @@ -354,8 +318,6 @@ end;' language 'plpgsql'; - --- function email create function party__email (integer) returns varchar as ' declare @@ -371,7 +333,6 @@ end;' language 'plpgsql'; - -- show errors ------------- @@ -430,7 +391,7 @@ -- create or replace package body person -- function new select define_function_args('person__new','person_id,object_type;person,creation_date;now(),creation_user,creation_ip,email,url,first_names,last_name,context_id'); -create function person__new (integer,varchar,timestamp with time zone,integer,varchar,varchar,varchar,varchar,varchar,integer) +create function person__new (integer,varchar,timestamptz,integer,varchar,varchar,varchar,varchar,varchar,integer) returns integer as ' declare new__person_id alias for $1; -- default null @@ -542,9 +503,9 @@ priv_email integer default 5 not null, email_verified_p boolean default 't', email_bouncing_p boolean default 'f' not null, - no_alerts_until timestamp, - last_visit timestamp, - second_to_last_visit timestamp, + no_alerts_until timestamptz, + last_visit timestamptz, + second_to_last_visit timestamptz, n_sessions integer default 1 not null, password_question varchar(1000), password_answer varchar(1000) @@ -667,7 +628,7 @@ select define_function_args('user__new','user_id,object_type;user,creation_date;now(),creation_user,creation_ip,email,url,first_names,last_name,password,salt,password_question,password_answer,screen_name,email_verified_p;t,context_id'); -create function acs_user__new (integer,varchar,timestamp with time zone,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,integer) +create function acs_user__new (integer,varchar,timestamptz,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,integer) returns integer as ' declare new__user_id alias for $1; -- default null Index: openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql,v diff -u -r1.22 -r1.23 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 17 Mar 2003 21:58:36 -0000 1.22 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 17 May 2003 01:47:10 -0000 1.23 @@ -172,6 +172,17 @@ (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 elements be elements of my new composite group insert into group_element_index (group_id, element_id, rel_id, container_id, @@ -195,14 +206,27 @@ 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, 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 = map.group_id + and element_id = m.member_id + and rel_id = m.rel_id); + -- Add rows for my elements + insert into group_element_index (group_id, element_id, rel_id, container_id, rel_type, ancestor_rel_type) @@ -261,6 +285,12 @@ 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 @@ -298,11 +328,17 @@ 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 if; end loop; @@ -313,9 +349,6 @@ create trigger composition_rels_del_tr before delete on composition_rels for each row execute procedure composition_rels_del_tr (); --- show errors - - -------------------- -- PACKAGE BODIES -- -------------------- Index: openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql,v diff -u -r1.14 -r1.15 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql 17 Feb 2003 15:32:53 -0000 1.14 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-create.sql 17 May 2003 01:47:10 -0000 1.15 @@ -130,7 +130,7 @@ -- show errors create table group_types ( - group_type varchar(100) not null + group_type varchar(400) not null constraint group_types_pk primary key constraint group_types_obj_type_fk references acs_object_types (object_type), @@ -386,7 +386,7 @@ create function composition_rels_in_tr () returns opaque as ' declare begin - raise EXCEPTION ''-20000: Insert to membership rels not yet supported''; + raise EXCEPTION ''-20000: Insert to composition rels not yet supported''; return new; Index: openacs-4/packages/acs-kernel/sql/postgresql/journal-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/journal-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/acs-kernel/sql/postgresql/journal-create.sql 21 Dec 2002 22:31:16 -0000 1.4 +++ openacs-4/packages/acs-kernel/sql/postgresql/journal-create.sql 17 May 2003 01:47:10 -0000 1.5 @@ -88,7 +88,7 @@ -- create or replace package body journal_entry -- function new -create function journal_entry__new (integer,integer,varchar,varchar,timestamp with time zone,integer,varchar,varchar) +create function journal_entry__new (integer,integer,varchar,varchar,timestamptz,integer,varchar,varchar) returns integer as ' declare new__journal_id alias for $1; -- default null Index: openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql,v diff -u -r1.31 -r1.32 --- openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 21 Dec 2002 22:31:16 -0000 1.31 +++ openacs-4/packages/acs-kernel/sql/postgresql/postgresql.sql 17 May 2003 01:47:10 -0000 1.32 @@ -629,79 +629,30 @@ return position(p_potential_ancestor in p_potential_child) = 1; end;' language 'plpgsql' with(iscachable); -create function create_tree_ancestor_keys() returns boolean as ' +-- PG does not allow recursive SQL functions during CREATE, but you can fool it easily +-- with CREATE OR REPLACE, a feature added in 7.2. --- PG 7.1 does not allow recursive SQL functions, but David Walker figured out how to --- get around this with a truly inspired hack he posted to the OpenACS 4 Design Forum. - --- His solution involves a general "create and replace function" function written in --- Tcl. - --- Rather than use the general solution I have just hacked up a PL/pgSQL function to --- create the one recursive function we need: tree_ancestor_keys(varbit, integer). - --- PG 7.2 still does not allow recursive SQL functions during CREATE, but you can --- fool it easily with CREATE OR REPLACE, a new feature in this version. Perhaps --- someday the PG development group will see the light and just let us CREATE such --- functions. - -- tree_ancestor_keys(varbit, integer) returns the set of ancestor keys starting at -- the level passed in as the second parameter down to the key passed in as the first -- This function should probably only be called from its overloaded cousin -- tree_ancestor_keys(varbit), which returns the set of tree_sortkeys for all of the -- ancestors of the given tree_sortkey... -begin +create function tree_ancestor_keys(varbit, integer) returns setof varbit as ' + select $1 +' language 'sql'; - -- create tree_ancestor_keys with a dummy body +-- The bootstrap installer has made certain that we are running a version >= 7.2 so it is safe +-- at this point to assume create or replace is supported. - execute ''create function tree_ancestor_keys(varbit, integer) returns setof varbit as '''' - select $1 - '''' language ''''sql'''' ''; +create or replace function tree_ancestor_keys(varbit, integer) returns setof varbit as ' + select tree_ancestor_key($1, $2) + union + select tree_ancestor_keys($1, $2 + 1) + where $2 < tree_level($1) +' language 'sql' with (isstrict); - if version() like ''%7.1%'' then - - -- create another function with the body we want - - execute ''create function __tree_ancestor_keys(varbit, integer) returns setof varbit as '''' - select tree_ancestor_key($1, $2) - union - select tree_ancestor_keys($1, $2 + 1) - where $2 < tree_level($1) - '''' language ''''sql'''' with (isstrict) ''; - - -- replace the body for tree_ancestor_keys with the body we want. Slick, eh? - - update pg_proc - set prosrc = hack.prosrc, probin = hack.probin - from (select prosrc, probin - from pg_proc - where proname = ''__tree_ancestor_keys'') hack - where proname = ''tree_ancestor_keys''; - - execute ''drop function __tree_ancestor_keys(varbit, integer)''; - - else - - -- The bootstrap installer has made certain that we are running a version >= 7.1 so it is safe - -- at this point to assume create or replace is supported. - - execute ''create or replace function tree_ancestor_keys(varbit, integer) returns setof varbit as '''' - select tree_ancestor_key($1, $2) - union - select tree_ancestor_keys($1, $2 + 1) - where $2 < tree_level($1) - '''' language ''''sql'''' with (isstrict) ''; - end if; - - return true; -end;' language 'plpgsql'; - -select create_tree_ancestor_keys(); - -drop function create_tree_ancestor_keys(); - create function tree_ancestor_keys(varbit) returns setof varbit as ' -- Return the set of tree_sortkeys for all of the ancestors of the given @@ -766,77 +717,26 @@ -- PG substitute for Oracle user_col_comments view -create function create_user_col_comments() returns boolean as ' -begin - -- in version 7.1 col_description was missing but is present in 7.2 - -- does it exist in 7.0? - if version() like ''%7.1%'' then - execute '' - create view user_col_comments as - select upper(c.relname) as table_name, - upper(a.attname) as column_name, - d.description as comments - from pg_class c, - pg_attribute a - left outer join pg_description d on (a.oid = d.objoid) - where c.oid = a.attrelid - and a.attnum > 0''; - else - execute '' - create view user_col_comments as - select upper(c.relname) as table_name, - upper(a.attname) as column_name, - col_description(a.attrelid, a.attnum) as comments - from pg_class c - left join pg_attribute a - on a.attrelid = c.oid - where a.attnum > 0''; - end if; - return ''t''; -end;' language 'plpgsql'; +create view user_col_comments as + select upper(c.relname) as table_name, + upper(a.attname) as column_name, + col_description(a.attrelid, a.attnum) as comments + from pg_class c left join pg_attribute a on a.attrelid = c.oid + where a.attnum > 0; -select create_user_col_comments(); - -drop function create_user_col_comments(); - -- PG substitute for Oracle user_col_comments view -create function create_user_tab_comments() returns boolean as ' -begin - if version() like ''%7.2%'' then - execute '' - create view user_tab_comments as - select upper(c.relname) as table_name, - case - when c.relkind = ''''r'''' then ''''TABLE'''' - when c.relkind = ''''v'''' then ''''VIEW'''' - else c.relkind::text - end as table_type, - d.description as comments - from pg_class c - left outer join pg_description d on (c.oid = d.objoid) - where d.objsubid = 0''; - else - execute '' - create view user_tab_comments as - select upper(c.relname) as table_name, - case - when c.relkind = ''''r'''' then ''''TABLE'''' - when c.relkind = ''''v'''' then ''''VIEW'''' - else c.relkind::text - end as table_type, - d.description as comments - from pg_class c - left outer join pg_description d on (c.oid = d.objoid)''; - end if; - return ''t''; -end;' language 'plpgsql'; +create view user_tab_comments as + select upper(c.relname) as table_name, + case + when c.relkind = 'r' then 'TABLE' + when c.relkind = 'v' then 'VIEW' + else c.relkind::text + end as table_type, + d.description as comments + from pg_class c left outer join pg_description d on (c.oid = d.objoid) + where d.objsubid = 0; -select create_user_tab_comments(); - -drop function create_user_tab_comments(); - - -- Table for storing PL/PGSQL function arguments create table acs_function_args ( Index: openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql,v diff -u -r1.7 -r1.8 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 13 Mar 2003 20:23:24 -0000 1.7 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 17 May 2003 01:47:10 -0000 1.8 @@ -205,7 +205,7 @@ 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 + -- if the relation type is mapped to relational segments map them too for v_segments in select segment_id from rel_segments s, acs_object_types o1, acs_object_types o2 Index: openacs-4/packages/acs-kernel/sql/postgresql/security-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/security-create.sql,v diff -u -r1.4 -r1.5 --- openacs-4/packages/acs-kernel/sql/postgresql/security-create.sql 19 Dec 2001 18:33:22 -0000 1.4 +++ openacs-4/packages/acs-kernel/sql/postgresql/security-create.sql 17 May 2003 01:47:10 -0000 1.5 @@ -35,7 +35,7 @@ token_id integer constraint secret_tokens_token_id_pk primary key, token char(40), - token_timestamp timestamp + token_timestamp timestamptz ); create sequence t_sec_security_token_id_seq cache 100; Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.5-4.5.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.5-4.5.1.sql,v diff -u -r1.5 -r1.6 --- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.5-4.5.1.sql 21 Dec 2002 22:31:06 -0000 1.5 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.5-4.5.1.sql 17 May 2003 01:47:26 -0000 1.6 @@ -14,7 +14,7 @@ return acs_object__update_last_modified(acs_object__update_last_modified__object_id, now()); end;' language 'plpgsql'; - create function acs_object__update_last_modified (integer, timestamp with time zone) + create function acs_object__update_last_modified (integer, timestamptz) returns integer as ' declare acs_object__update_last_modified__object_id alias for $1; Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/Attic/upgrade-4.6-4.6.1.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql 16 Jan 2003 13:34:42 -0000 1.2 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6-4.6.1.sql 17 May 2003 01:47:26 -0000 1.3 @@ -4,7 +4,19 @@ -- @creation-date 2002-12-17 -- @cvs-id $Id$ +-- 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 @@ -23,3 +35,770 @@ 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); + + Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.6.2.sql'. Fisheye: No comparison available. Pass `N' to diff? Index: openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.7d.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/upgrade/Attic/upgrade-4.6.1-4.7d.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.7d.sql 31 Jan 2003 14:02:48 -0000 1.3 +++ openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.1-4.7d.sql 17 May 2003 01:47:26 -0000 1.4 @@ -52,7 +52,7 @@ where enabled_p = 't'; -- Recreate functions for auto-mount -create or replace function apm_package_version__new (integer,varchar,varchar,varchar,varchar,varchar,varchar,timestamp with time zone,varchar,varchar,varchar,boolean,boolean) returns integer as ' +create or replace function apm_package_version__new (integer,varchar,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,varchar,varchar,boolean,boolean) returns integer as ' declare apm_pkg_ver__version_id alias for $1; -- default null apm_pkg_ver__package_key alias for $2; @@ -100,7 +100,7 @@ end;' language 'plpgsql'; -create or replace function apm_package_version__edit (integer,integer,varchar,varchar,varchar,varchar,varchar,timestamp with time zone,varchar,varchar,varchar,boolean,boolean) +create or replace function apm_package_version__edit (integer,integer,varchar,varchar,varchar,varchar,varchar,timestamptz,varchar,varchar,varchar,boolean,boolean) returns integer as ' declare edit__new_version_id alias for $1; -- default null Fisheye: Tag 1.1 refers to a dead (removed) revision in file `openacs-4/packages/acs-kernel/sql/postgresql/upgrade/upgrade-4.6.2-4.6.3.sql'. Fisheye: No comparison available. Pass `N' to diff?