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.3 -r1.4 --- openacs-4/packages/acs-kernel/sql/oracle/acs-create.sql 7 Jun 2002 04:32:21 -0000 1.3 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-create.sql 18 Feb 2003 20:53:43 -0000 1.4 @@ -193,13 +193,13 @@ root_id integer; begin root_id := acs_object.new ( - object_id => 0 + object_id => -4 ); insert into acs_magic_objects (name, object_id) values - ('security_context_root', 0); + ('security_context_root', -4); end; / show errors @@ -229,33 +229,56 @@ / show errors +declare + foo acs_objects.object_id%TYPE; begin - insert into acs_objects - (object_id, object_type) - values - (-1, 'party'); - insert into parties - (party_id) - values - (-1); + -- Make the "Unregistered Visitor" be object 0, which corresponds + -- with the user_id assigned throughout the toolkit Tcl code - insert into acs_magic_objects - (name, object_id) - values - ('the_public', -1); + insert into acs_objects + (object_id, object_type) + values + (0, 'person'); - commit; -end; -/ -show errors + insert into parties + (party_id) + values + (0); --- Insert the site-wide group. The members of this --- group correspond to all registered users. -declare - group_id integer; -begin - group_id := acs_group.new ( + 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); + + -- Create the public group + foo := acs_group.new ( + group_id => -1, + group_name => 'The Public' + ); + + insert into acs_magic_objects + (name, object_id) + values + ('the_public', -1); + + -- Add our only user, the Unregistered Visitor, to the public group + + 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' + ); + + -- Make the registered users group + + foo := acs_group.new ( group_id => -2, group_name => 'Registered Users' ); @@ -265,10 +288,19 @@ values ('registered_users', -2); - commit; + -- 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') + ); + + commit; end; / -show errors +show errors; -- Create the default context. declare @@ -286,4 +318,5 @@ commit; end; / -show errors +show errors; + Index: openacs-4/packages/acs-kernel/sql/oracle/acs-install.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/acs-install.sql,v diff -u -r1.10 -r1.11 --- openacs-4/packages/acs-kernel/sql/oracle/acs-install.sql 31 Jan 2003 16:17:54 -0000 1.10 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-install.sql 18 Feb 2003 20:53:45 -0000 1.11 @@ -11,6 +11,7 @@ declare node_id site_nodes.node_id%TYPE; main_site_id site_nodes.node_id%TYPE; + segment_id rel_segments.segment_id%TYPE; schema_user varchar2(100); jobnum integer; begin @@ -35,6 +36,21 @@ privilege => 'read' ); + insert into application_groups + (group_id, package_id) + values + (-2, main_site_id); + + update acs_objects + set object_type = 'application_group' + where object_id = -2; + + segment_id := rel_segment.new( + segment_name => 'Main Site Members', + group_id => -2, + rel_type => 'membership_rel' + ); + select user into schema_user from dual; dbms_job.submit ( 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.6 -r1.7 --- openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql 25 May 2002 14:18:49 -0000 1.6 +++ openacs-4/packages/acs-kernel/sql/oracle/acs-objects-create.sql 18 Feb 2003 20:53:45 -0000 1.7 @@ -210,7 +210,15 @@ create or replace trigger acs_objects_context_id_in_tr after insert on acs_objects for each row +declare + security_context_root acs_objects.object_id%TYPE; begin + + -- Hate the hardwiring but magic objects aren't defined yet (PG doesn't + -- mind because function bodies aren't compiled until first called) + + security_context_root := -4; + insert into acs_object_context_index (object_id, ancestor_id, n_generations) values @@ -225,12 +233,13 @@ n_generations + 1 as n_generations from acs_object_context_index where object_id = :new.context_id; - elsif :new.object_id != 0 then - -- 0 is the id of the security context root object - insert into acs_object_context_index - (object_id, ancestor_id, n_generations) - values - (:new.object_id, 0, 1); + else + 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; / @@ -239,13 +248,20 @@ create or replace trigger acs_objects_context_id_up_tr after update on acs_objects for each row +declare + security_context_root acs_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; + -- Hate the hardwiring but magic objects aren't defined yet (PG doesn't + -- mind because function bodies aren't compiled until first called) + + security_context_root := -4; + -- Remove my old ancestors from my descendants. delete from acs_object_context_index where object_id in (select object_id @@ -277,17 +293,20 @@ from acs_object_context_index where object_id = :new.context_id; end loop; - elsif :new.object_id != 0 then - -- We need to make sure that :NEW.OBJECT_ID and all of its - -- children have 0 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, 0, pair.n_generations + 1); - end loop; + else + 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; / 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.3 -r1.4 --- openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql 23 Feb 2002 02:43:45 -0000 1.3 +++ openacs-4/packages/acs-kernel/sql/oracle/groups-body-create.sql 18 Feb 2003 20:53:45 -0000 1.4 @@ -10,6 +10,30 @@ -- TRIGGERS -- -------------- +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 @@ -39,6 +63,10 @@ (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 @@ -50,11 +78,42 @@ 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; + create or replace trigger composition_rels_in_tr after insert on composition_rels for each row @@ -133,24 +192,6 @@ / 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; - - delete from group_element_index - where rel_id = :old.rel_id; -end; -/ -show errors; - -- -- TO DO: See if this can be optimized now that the member and component -- mapping tables have been combined Index: openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql,v diff -u -r1.1 -r1.2 --- openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql 20 Mar 2001 22:51:55 -0000 1.1 +++ openacs-4/packages/acs-kernel/sql/oracle/rel-segments-create.sql 18 Feb 2003 20:53:45 -0000 1.2 @@ -206,46 +206,217 @@ as select distinct segment_id, member_id from rel_seg_approved_member_map; +-- The party_approved_member_map table maps all parties to all their +-- members. It's here rather in a logical place for historical reasons. --- party_member_map can be used to expand any party into its members. --- Every party is considered to be a member of itself. +-- 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) --- By the way, aren't the party_member_map and party_approved_member_map --- views equivalent?? (TO DO: RESOLVE THIS QUESTION) +-- (it is "cnt" rather than "count" because Oracle confuses it with the +-- "count()" aggregate in some contexts) -create or replace view party_member_map -as select segment_id as party_id, member_id - from rel_seg_distinct_member_map - union - select group_id as party_id, member_id - from group_distinct_member_map - union - select party_id, party_id as member_id - from parties; +-- 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 or replace view party_approved_member_map -as select distinct segment_id as party_id, member_id - from rel_seg_approved_member_map - union - select distinct group_id as party_id, member_id - from group_approved_member_map - union - select party_id, party_id as member_id - from parties; +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) +); --- party_element_map tells us all the parties that "belong to" a party, --- whether through somet type of membership, composition, or identity. +-- Need this to speed referential integrity +create index party_member_member_idx on party_approved_member_map(member_id); -create or replace view party_element_map -as select distinct group_id as party_id, element_id - from group_element_map - union - select distinct segment_id as party_id, party_id as element_id - from rel_segment_party_map - union - select party_id, party_id as element_id - from parties; +-- 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 before 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 + v_segment_id rel_segments.segment_id%TYPE; + begin + + add_one(p_party_id, p_member_id); + + -- if the relation type is mapped to a relational segment map that too + + select segment_id into v_segment_id + from rel_segments s + where s.rel_type = p_rel_type + and s.group_id = p_party_id; + + exception when no_data_found then return; + + add_one(v_segment_id, p_member_id); + + 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 + v_segment_id rel_segments.segment_id%TYPE; + begin + + remove_one(p_party_id, p_member_id); + + -- if the relation type is mapped to a relational segment unmap that too + + select segment_id into v_segment_id + from rel_segments s + where s.rel_type = p_rel_type + and s.group_id = p_party_id; + + exception when no_data_found then return; + + remove_one(v_segment_id, p_member_id); + + end remove; + +end party_approved_member; +/ +show errors; + -- View: rel_segment_group_rel_type_map -- -- Result Set: the set of triples (:segment_id, :group_id, :rel_type) such that 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.10 -r1.11 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 17 Feb 2003 15:32:53 -0000 1.10 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-create.sql 18 Feb 2003 20:54:32 -0000 1.11 @@ -20,46 +20,6 @@ objects like the site-wide organization, and the all users party. '; --- create or replace package acs --- as --- --- function add_user ( --- user_id in users.user_id%TYPE default null, --- object_type in acs_objects.object_type%TYPE --- default 'user', --- 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, --- first_names in persons.first_names%TYPE, --- last_name in persons.last_name%TYPE, --- password in users.password%TYPE, --- salt in users.salt%TYPE, --- password_question in users.password_question%TYPE default null, --- password_answer in users.password_answer%TYPE default null, --- screen_name in users.screen_name%TYPE default null, --- email_verified_p in users.email_verified_p%TYPE default 't', --- member_state in membership_rels.member_state%TYPE default 'approved' --- ) --- return users.user_id%TYPE; --- --- procedure remove_user ( --- user_id in users.user_id%TYPE --- ); --- --- function magic_object_id ( --- name in acs_magic_objects.name%TYPE --- ) return acs_objects.object_id%TYPE; --- --- end acs; - --- show errors - --- create or replace package body acs --- function add_user create function acs__add_user (integer,varchar,timestamp with time zone,integer,varchar,varchar,varchar,varchar,varchar,char,char,varchar,varchar,varchar,boolean,varchar) returns integer as ' declare @@ -143,10 +103,6 @@ end;' language 'plpgsql' with(isstrict,iscachable); - - --- show errors - -- ****************************************************************** -- * Community Core API -- ****************************************************************** @@ -329,4 +285,3 @@ ('default_context', -3); --- show errors Index: openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql,v diff -u -r1.19 -r1.20 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql 17 Feb 2003 15:32:53 -0000 1.19 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-install.sql 18 Feb 2003 20:54:32 -0000 1.20 @@ -27,8 +27,31 @@ ); - PERFORM apm_package__enable (main_site_id); + perform apm_package__enable (main_site_id); + insert into application_groups + (group_id, package_id) + values + (-2, main_site_id); + + update acs_objects + set object_type = ''application_group'' + where object_id = -2; + + perform rel_segment__new( + null, + ''rel_segment'', + now(), + null, + null, + null, + null, + ''Main Site Members'', + -2, + ''membership_rel'', + null + ); + node_id := site_node__new ( null, null, @@ -40,7 +63,7 @@ null ); - PERFORM acs_permission__grant_permission ( + perform acs_permission__grant_permission ( main_site_id, acs__magic_object_id(''the_public''), ''read'' @@ -53,5 +76,3 @@ select inline_0 (); drop function inline_0 (); - --- show errors 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.38 -r1.39 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 17 Feb 2003 15:32:53 -0000 1.38 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 18 Feb 2003 20:54:32 -0000 1.39 @@ -381,7 +381,9 @@ from acs_object_context_index where object_id != ancestor_id; -create function acs_objects_context_id_in_tr () returns opaque as ' +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) @@ -396,13 +398,15 @@ n_generations + 1 as n_generations from acs_object_context_index where object_id = new.context_id; - else if new.object_id != 0 then - -- 0 is the id of the security context root object - insert into acs_object_context_index - (object_id, ancestor_id, n_generations) - values - (new.object_id, 0, 1); - end if; end if; + 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; @@ -411,11 +415,10 @@ create trigger acs_objects_context_id_in_tr after insert on acs_objects for each row execute procedure acs_objects_context_id_in_tr (); --- show errors - -create function acs_objects_context_id_up_tr () returns opaque as ' +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 @@ -455,19 +458,22 @@ from acs_object_context_index where object_id = new.context_id; end loop; - else if new.object_id != 0 then + 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 0 as an ancestor. + -- 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, 0, pair.n_generations + 1); - end loop; - end if; end if; + 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; @@ -476,8 +482,6 @@ create trigger acs_objects_context_id_up_tr after update on acs_objects for each row execute procedure acs_objects_context_id_up_tr (); --- show errors - create function acs_objects_context_id_del_tr () returns opaque as ' begin delete from acs_object_context_index @@ -490,8 +494,6 @@ create trigger acs_objects_context_id_del_tr before delete on acs_objects for each row execute procedure acs_objects_context_id_del_tr (); --- show errors - ---------------------- -- ATTRIBUTE VALUES -- ---------------------- 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.19 -r1.20 --- openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 17 Feb 2003 15:32:53 -0000 1.19 +++ openacs-4/packages/acs-kernel/sql/postgresql/groups-body-create.sql 18 Feb 2003 20:54:32 -0000 1.20 @@ -9,9 +9,23 @@ -------------- -- TRIGGERS -- -------------- --- a dummy trigger was defined in groups-create.sql + drop trigger membership_rels_in_tr on membership_rels; drop function membership_rels_in_tr (); + +-- The insert trigger was dummied up in groups-create.sql, so we just need +-- to replace the trigger function, not create the trigger. + +-- However, PG 7.3 introduces a new type "trigger" for the return type +-- needed for functions called by triggers. "create function" transmorgifies +-- the return type "opaque" to "trigger" so PG 7.2 dumps can be restored into +-- PG 7.3. But "create or replace" doesn't do it. We can't use "trigger" +-- because we currently are still supporting PG 7.2. Isn't life a pleasure? + +-- I'm leaving the triggers we aren't overriding as "create or replace" because +-- this will be the right thing to do if the PG folks fix this problem or when +-- we drop support of PG 7.2 and no longer need to declare these as type "opaque" + create function membership_rels_in_tr () returns opaque as ' declare v_object_id_one acs_rels.object_id_one%TYPE; @@ -32,42 +46,102 @@ from acs_rels where rel_id = new.rel_id; - -- Insert a row for me in the group_member_index. + -- 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_member_index. + -- 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 + 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 trigger membership_rels_in_tr after insert on membership_rels +create trigger membership_rels_in_tr after insert on membership_rels for each row execute procedure membership_rels_in_tr (); --- show errors +create or replace function membership_rels_up_tr () returns opaque as ' +declare + map record; +begin --- a dummy trigger was defined in groups-create.sql -drop trigger composition_rels_in_tr on composition_rels; -drop function composition_rels_in_tr(); -create function composition_rels_in_tr () returns opaque as ' + 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'; + +create trigger membership_rels_del_tr before delete on membership_rels +for each row execute procedure membership_rels_del_tr (); + +create or replace function composition_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; @@ -145,38 +219,11 @@ end;' language 'plpgsql'; -create trigger composition_rels_in_tr after insert on composition_rels -for each row execute procedure composition_rels_in_tr (); - --- show errors - -create function membership_rels_del_tr () returns opaque as ' -declare - v_error text; -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; - - delete from group_element_index - where rel_id = old.rel_id; - - return old; - -end;' language 'plpgsql'; - -create trigger membership_rels_del_tr before delete on membership_rels -for each row execute procedure membership_rels_del_tr (); - --- show errors - -- -- TO DO: See if this can be optimized now that the member and component -- mapping tables have been combined -- -create function composition_rels_del_tr () returns opaque as ' +create or replace function composition_rels_del_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; @@ -271,7 +318,7 @@ -- function new select define_function_args('composition_rel__new','rel_id,rel_type;composition_rel,object_id_one,object_id_two,creation_user,creation_ip'); -create function composition_rel__new (integer,varchar,integer,integer,integer,varchar) +create or replace function composition_rel__new (integer,varchar,integer,integer,integer,varchar) returns integer as ' declare new__rel_id alias for $1; -- default null @@ -302,7 +349,7 @@ end;' language 'plpgsql'; -- function new -create function composition_rel__new (integer,integer) +create or replace function composition_rel__new (integer,integer) returns integer as ' declare object_id_one alias for $1; @@ -317,7 +364,7 @@ end;' language 'plpgsql'; -- procedure delete -create function composition_rel__delete (integer) +create or replace function composition_rel__delete (integer) returns integer as ' declare rel_id alias for $1; @@ -329,7 +376,7 @@ -- function check_path_exists_p -create function composition_rel__check_path_exists_p (integer,integer) +create or replace function composition_rel__check_path_exists_p (integer,integer) returns boolean as ' declare component_id alias for $1; @@ -356,7 +403,7 @@ -- function check_index -create function composition_rel__check_index (integer,integer) +create or replace function composition_rel__check_index (integer,integer) returns boolean as ' declare check_index__component_id alias for $1; @@ -433,7 +480,7 @@ -- function check_representation -create function composition_rel__check_representation (integer) +create or replace function composition_rel__check_representation (integer) returns boolean as ' declare check_representation__rel_id alias for $1; @@ -453,12 +500,12 @@ from acs_rels where rel_id = check_representation__rel_id; - -- First let''s check that the index has all the rows it should. + -- First let us check that the index has all the rows it should. if composition_rel__check_index(component_id, container_id) = ''f'' then result := ''f''; end if; - -- Now let''s check that the index doesn''t have any extraneous rows + -- Now let us check that the index doesn''t have any extraneous rows -- relating to this relation. for row in select * from group_component_index @@ -487,7 +534,7 @@ -- function new select define_function_args('membership_rel__new','rel_id,rel_type;membership_rel,object_id_one,object_id_two,member_state;approved,creation_user,creation_ip'); -create function membership_rel__new (integer,varchar,integer,integer,varchar,integer,varchar) +create or replace function membership_rel__new (integer,varchar,integer,integer,varchar,integer,varchar) returns integer as ' declare new__rel_id alias for $1; -- default null @@ -519,7 +566,7 @@ end;' language 'plpgsql'; -- function new -create function membership_rel__new (integer,integer) +create or replace function membership_rel__new (integer,integer) returns integer as ' declare object_id_one alias for $1; @@ -535,7 +582,7 @@ end;' language 'plpgsql'; -- procedure ban -create function membership_rel__ban (integer) +create or replace function membership_rel__ban (integer) returns integer as ' declare ban__rel_id alias for $1; @@ -549,7 +596,7 @@ -- procedure approve -create function membership_rel__approve (integer) +create or replace function membership_rel__approve (integer) returns integer as ' declare approve__rel_id alias for $1; @@ -563,7 +610,7 @@ -- procedure reject -create function membership_rel__reject (integer) +create or replace function membership_rel__reject (integer) returns integer as ' declare reject__rel_id alias for $1; @@ -577,7 +624,7 @@ -- procedure unapprove -create function membership_rel__unapprove (integer) +create or replace function membership_rel__unapprove (integer) returns integer as ' declare unapprove__rel_id alias for $1; @@ -591,7 +638,7 @@ -- procedure deleted -create function membership_rel__deleted (integer) +create or replace function membership_rel__deleted (integer) returns integer as ' declare deleted__rel_id alias for $1; @@ -605,7 +652,7 @@ -- procedure delete -create function membership_rel__delete (integer) +create or replace function membership_rel__delete (integer) returns integer as ' declare rel_id alias for $1; @@ -617,7 +664,7 @@ -- function check_index -create function membership_rel__check_index (integer,integer,integer) +create or replace function membership_rel__check_index (integer,integer,integer) returns boolean as ' declare check_index__group_id alias for $1; @@ -629,15 +676,15 @@ begin select count(*) into n_rows - from group_member_index + from group_element_index where group_id = check_index__group_id and member_id = check_index__member_id and container_id = check_index__container_id; if n_rows = 0 then result := ''f''; PERFORM acs_log__error(''membership_rel.check_representation'', - ''Row missing from group_member_index: '' || + ''Row missing from group_element_index: '' || ''group_id = '' || check_index__group_id || '', '' || ''member_id = '' || check_index__member_id || '', '' || ''container_id = '' || check_index__container_id || ''.''); @@ -659,7 +706,7 @@ -- function check_representation -create function membership_rel__check_representation (integer) +create or replace function membership_rel__check_representation (integer) returns boolean as ' declare check_representation__rel_id alias for $1; @@ -708,7 +755,7 @@ -- function new select define_function_args('acs_group__new','group_id,object_type;group,creation_date;now(),creation_user,creation_ip,email,url,group_name,join_policy,context_id'); -create function acs_group__new (integer,varchar,timestamp with time zone,integer,varchar,varchar,varchar,varchar,varchar,integer) +create or replace function acs_group__new (integer,varchar,timestamp with time zone,integer,varchar,varchar,varchar,varchar,varchar,integer) returns integer as ' declare new__group_id alias for $1; -- default null @@ -773,7 +820,7 @@ end;' language 'plpgsql'; -- function new -create function acs_group__new (varchar) returns integer as ' +create or replace function acs_group__new (varchar) returns integer as ' declare gname alias for $1; begin @@ -790,7 +837,7 @@ end;' language 'plpgsql'; -- procedure delete -create function acs_group__delete (integer) +create or replace function acs_group__delete (integer) returns integer as ' declare delete__group_id alias for $1; @@ -822,7 +869,7 @@ -- function name -create function acs_group__name (integer) +create or replace function acs_group__name (integer) returns varchar as ' declare name__group_id alias for $1; @@ -837,7 +884,7 @@ end;' language 'plpgsql'; -create function acs_group__member_p (integer, integer, boolean) +create or replace function acs_group__member_p (integer, integer, boolean) returns boolean as ' declare p_party_id alias for $1; @@ -862,7 +909,7 @@ -- function check_representation -create function acs_group__check_representation (integer) +create or replace function acs_group__check_representation (integer) returns boolean as ' declare group_id alias for $1; 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.5 -r1.6 --- openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 17 Feb 2003 15:32:53 -0000 1.5 +++ openacs-4/packages/acs-kernel/sql/postgresql/rel-segments-create.sql 18 Feb 2003 20:54:32 -0000 1.6 @@ -92,102 +92,10 @@ -- create pl/sql package rel_segment --- create or replace package rel_segment --- is --- function new ( --- --/** Creates a new relational segment --- -- --- -- @author Oumi Mehrotra (oumi@arsdigita.com) --- -- @creation-date 12/2000 --- -- --- --*/ --- segment_id in rel_segments.segment_id%TYPE default null, --- object_type in acs_objects.object_type%TYPE --- default 'rel_segment', --- 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, --- segment_name in rel_segments.segment_name%TYPE, --- group_id in rel_segments.group_id%TYPE, --- rel_type in rel_segments.rel_type%TYPE, --- context_id in acs_objects.context_id%TYPE default null --- ) return rel_segments.segment_id%TYPE; --- --- procedure delete ( --- --/** Deletes a relational segment --- -- --- -- @author Oumi Mehrotra (oumi@arsdigita.com) --- -- @creation-date 12/2000 --- -- --- --*/ --- segment_id in rel_segments.segment_id%TYPE --- ); --- --- function name ( --- segment_id in rel_segments.segment_id%TYPE --- ) return rel_segments.segment_name%TYPE; --- --- function get ( --- --/** EXPERIMENTAL / UNSTABLE -- use at your own risk --- -- Get the id of a segment given a group_id and rel_type. --- -- This depends on the uniqueness of group_id,rel_type. We --- -- might remove the unique constraint in the future, in which --- -- case we would also probably remove this function. --- -- --- -- @author Oumi Mehrotra (oumi@arsdigita.com) --- -- @creation-date 12/2000 --- -- --- --*/ --- --- group_id in rel_segments.group_id%TYPE, --- rel_type in rel_segments.rel_type%TYPE --- ) return rel_segments.segment_id%TYPE; --- --- function get_or_new ( --- --/** EXPERIMENTAL / UNSTABLE -- use at your own risk --- -- --- -- This function simplifies the use of segments a little by letting --- -- you not have to worry about creating and initializing segments. --- -- If the segment you're interested in exists, this function --- -- returns its segment_id. --- -- If the segment you're interested in doesn't exist, this function --- -- does a pretty minimal amount of initialization for the segment --- -- and returns a new segment_id. --- -- --- -- @author Oumi Mehrotra (oumi@arsdigita.com) --- -- @creation-date 12/2000 --- -- --- --*/ --- group_id in rel_segments.group_id%TYPE, --- rel_type in rel_segments.rel_type%TYPE, --- segment_name in rel_segments.segment_name%TYPE --- default null --- ) return rel_segments.segment_id%TYPE; --- --- end rel_segment; - --- show errors - - ----------- -- Views -- ----------- --- create view rel_segment_party_map --- as select rs.segment_id, gem.element_id as party_id, gem.rel_id, gem.rel_type, --- gem.group_id, gem.container_id, gem.ancestor_rel_type --- from rel_segments rs, --- group_element_map gem --- where gem.group_id = rs.group_id --- and rs.rel_type in (select object_type --- from acs_object_types --- start with object_type = gem.rel_type --- connect by prior supertype = object_type); - create view rel_segment_party_map as select rs.segment_id, gem.element_id as party_id, gem.rel_id, gem.rel_type, gem.group_id, gem.container_id, gem.ancestor_rel_type @@ -207,21 +115,6 @@ from rel_segment_party_map where ancestor_rel_type = 'membership_rel'; - --- Need to find out what this optimizer hint does? DCW, 2001-03-13. --- create view rel_seg_approved_member_map --- as select /*+ ordered */ --- rs.segment_id, gem.element_id as member_id, gem.rel_id, gem.rel_type, --- gem.group_id, gem.container_id --- from membership_rels mr, group_element_map gem, rel_segments rs --- where rs.group_id = gem.group_id --- and rs.rel_type in (select object_type --- from acs_object_types --- start with object_type = gem.rel_type --- connect by prior supertype = object_type) --- and mr.rel_id = gem.rel_id and mr.member_state = 'approved'; - - create view rel_seg_approved_member_map as select rs.segment_id, gem.element_id as member_id, gem.rel_id, gem.rel_type, gem.group_id, gem.container_id @@ -237,71 +130,218 @@ as select distinct segment_id, member_id from rel_seg_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. --- party_member_map can be used to expand any party into its members. --- Every party is considered to be a member of itself. +-- 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. --- By the way, aren't the party_member_map and party_approved_member_map --- views equivalent?? (TO DO: RESOLVE THIS QUESTION) +-- DRB: Unfortunately visibility semantics in PostgreSQL are very different +-- than in Oracle. This makes it impossible to remove the duplicate +-- rows by maintaining a count column as I've done in the Oracle version +-- without requiring application code to issue explicit "lock table in +-- exclusive mode" statements. This would kill abstraction and be very +-- error prone. The PL/pgSQL procs can issue the locks but unfortunately +-- statements within such procs don't generate a new snapshot when executed +-- but rather work within the context of the caller. This means locks within +-- a PL/pgSQL are too late to be of use. Such code works perfectly in Oracle. -create view party_member_map -as select segment_id as party_id, member_id - from rel_seg_distinct_member_map - union - select group_id as party_id, member_id - from group_distinct_member_map - union - select party_id, party_id as member_id - from parties; +-- Maybe people who buy Oracle aren't as dumb as you thought! -create view party_approved_member_map -as select distinct segment_id as party_id, member_id - from rel_seg_approved_member_map - union - select distinct group_id as party_id, member_id - from group_approved_member_map - union - select party_id, party_id as member_id - from parties; +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) +); --- party_element_map tells us all the parties that "belong to" a party, --- whether through somet type of membership, composition, or identity. +-- Need this to speed referential integrity +create index party_member_member_idx on party_approved_member_map(member_id); -create view party_element_map -as select distinct group_id as party_id, element_id - from group_element_map - union - select distinct segment_id as party_id, party_id as element_id - from rel_segment_party_map - union - select party_id, party_id as element_id - from parties; +-- 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_segment_id rel_segments.segment_id%TYPE; +begin + + perform party_approved_member__add_one(p_party_id, p_member_id, p_rel_id); + + select into v_segment_id segment_id + from rel_segments s + where s.rel_type = p_rel_type + and s.group_id = p_party_id; + + if found then + perform party_approved_member__add_one(v_segment_id, p_member_id, p_rel_id); + end if; + + 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_segment_id rel_segments.segment_id%TYPE; +begin + + perform party_approved_member__remove_one(p_party_id, p_member_id, p_rel_id); + + -- if the relation type is mapped to a relational segment unmap that too + + select into v_segment_id segment_id + from rel_segments s + where s.rel_type = p_rel_type + and s.group_id = p_party_id; + + if found then + perform party_approved_member__remove_one(v_segment_id, p_member_id, p_rel_id); + end if; + + return 1; + +end;' language 'plpgsql'; + + +-- Triggers to maintain party_approved_member_map when parties are created or +-- destroyed. These don't call the above helper functions because we're just +-- creating the identity row for the party. + +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 (); + -- View: rel_segment_group_rel_type_map -- -- Result Set: the set of triples (:segment_id, :group_id, :rel_type) such that -- -- IF a party were to be in :group_id -- through a relation of type :rel_type, -- THEN the party would necessarily be in segment :segemnt_id. --- --- --- create view rel_segment_group_rel_type_map as --- select s.segment_id, --- gcm.component_id as group_id, --- acs_rel_types.rel_type as rel_type --- from rel_segments s, --- (select group_id, component_id --- from group_component_map --- UNION ALL --- select group_id, group_id as component_id --- from groups) gcm, --- acs_rel_types --- where s.group_id = gcm.group_id --- and s.rel_type in (select object_type from acs_object_types --- start with object_type = acs_rel_types.rel_type --- connect by prior supertype = object_type); create view rel_segment_group_rel_type_map as select s.segment_id, Index: openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl,v diff -u -r1.5 -r1.6 --- openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl 31 Jan 2003 08:37:39 -0000 1.5 +++ openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.tcl 18 Feb 2003 20:54:59 -0000 1.6 @@ -10,15 +10,7 @@ @return 1 if a user with admin privileges exists, 0 otherwise. } { - return [db_string admin_exists_p { - select 1 as admin_exists_p - from dual - where exists (select 1 - from acs_object_party_privilege_map m, users u - where m.object_id = 0 - and m.party_id = u.user_id - and m.privilege = 'admin') - } -default 0] + return [db_string admin_exists_p {} -default 0] } Index: openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-tcl/tcl/Attic/acs-kernel-procs.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql 28 Nov 2001 18:39:39 -0000 1.2 +++ openacs-4/packages/acs-tcl/tcl/acs-kernel-procs.xql 18 Feb 2003 20:55:00 -0000 1.3 @@ -4,13 +4,14 @@ - select 1 as admin_exists_p - from dual - where exists (select 1 - from all_object_party_privilege_map m, users u - where m.object_id = 0 - and m.party_id = u.user_id - and m.privilege = 'admin') + select 1 as admin_exists_p + from dual + where exists (select 1 + from all_object_party_privilege_map m, users u, acs_magic_objects amo + where m.object_id = amo.object_id + and amo.name = 'security_context_root' + and m.party_id = u.user_id + and m.privilege = 'admin') Index: openacs-4/packages/file-storage/tcl/file-storage-procs.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/tcl/file-storage-procs.tcl,v diff -u -r1.29 -r1.30 --- openacs-4/packages/file-storage/tcl/file-storage-procs.tcl 27 Jan 2003 21:20:25 -0000 1.29 +++ openacs-4/packages/file-storage/tcl/file-storage-procs.tcl 18 Feb 2003 20:55:28 -0000 1.30 @@ -351,6 +351,24 @@ return [db_string select_folder {} -default ""] } + ad_proc -public get_folder_objects { + -folder_id:required + -user_id:required + } { + Return a list the object_ids contained by a file storage folder. + + This would be trivial if it weren't for the fact that we need to UNION ALL + with the gawddamned fs_simple_objects Open Force forced upon us and which + will be removed as soon as I (DRB) find the time to write upgrade scripts. + + @param folder_id The folder for which to retrieve contents + @param user_id The viewer of the contents (to make sure they have + permission) + + } { + return [db_list select_folder_contents {}] + } + ad_proc -public get_folder_contents { {-folder_id ""} {-user_id ""} Index: openacs-4/packages/file-storage/tcl/file-storage-procs.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/tcl/file-storage-procs.xql,v diff -u -r1.10 -r1.11 --- openacs-4/packages/file-storage/tcl/file-storage-procs.xql 17 Sep 2002 21:03:26 -0000 1.10 +++ openacs-4/packages/file-storage/tcl/file-storage-procs.xql 18 Feb 2003 20:55:28 -0000 1.11 @@ -71,6 +71,28 @@ + + + + select * + from (select cr_items.item_id as object_id, + cr_items.name + from cr_items + where cr_items.parent_id = :folder_id + union all + select fs_simple_objects.object_id, + fs_simple_objects.name + from fs_simple_objects + where fs_simple_objects.folder_id = :folder_id) contents + where exists (select 1 + from acs_object_party_privilege_map m + where m.object_id = contents.object_id + and m.party_id = :user_id + and m.privilege = 'read') + + + + select count(*) Index: openacs-4/packages/file-storage/www/folder-chunk-oracle.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/Attic/folder-chunk-oracle.xql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/www/folder-chunk-oracle.xql 18 Nov 2002 18:01:05 -0000 1.2 +++ openacs-4/packages/file-storage/www/folder-chunk-oracle.xql 18 Feb 2003 20:56:09 -0000 1.3 @@ -4,20 +4,40 @@ oracle8.1.6 - select fs_objects.object_id, - fs_objects.name, - fs_objects.live_revision, - fs_objects.type, - to_char(fs_objects.last_modified, 'YYYY-MM-DD HH24:MI:SS') as last_modified_ansi, - fs_objects.content_size, - fs_objects.url, - fs_objects.key, - fs_objects.sort_key, - fs_objects.file_upload_name, - case when fs_objects.last_modified >= (sysdate - :n_past_days) then 1 else 0 end as new_p - from fs_objects - where fs_objects.parent_id = :folder_id - order by sort_key, name + + select fs_objects.object_id, + fs_objects.name, + fs_objects.live_revision, + fs_objects.type, + to_char(fs_objects.last_modified, 'YYYY-MM-DD HH24:MI:SS') as last_modified, + fs_objects.content_size, + fs_objects.url, + fs_objects.sort_key, + fs_objects.file_upload_name, + case when fs_objects.last_modified >= (sysdate - :n_past_days) then 1 else 0 end as new_p, + case + when type = 'url' + then acs_permission.permission_p(fs_objects.object_id, :viewing_user_id, 'delete') + else 't' + end as delete_p, + case + when type = 'url' + then acs_permission.permission_p(fs_objects.object_id, :viewing_user_id, 'write') + else 't' + end as write_p + from fs_objects + where fs_objects.parent_id = :folder_id + and exists (select 1 + from acs_object_party_privilege_map m + where m.object_id = fs_objects.object_id + and m.party_id = :viewing_user_id + and m.privilege = 'read') + order by fs_objects.sort_key, fs_objects.name + - \ No newline at end of file + + + + + Index: openacs-4/packages/file-storage/www/folder-chunk-postgresql.xql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/folder-chunk-postgresql.xql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/file-storage/www/folder-chunk-postgresql.xql 21 Dec 2002 22:28:31 -0000 1.3 +++ openacs-4/packages/file-storage/www/folder-chunk-postgresql.xql 18 Feb 2003 20:56:09 -0000 1.4 @@ -4,20 +4,81 @@ postgresql7.1 - select fs_objects.object_id, - fs_objects.name, - fs_objects.live_revision, - fs_objects.type, - to_char(fs_objects.last_modified, 'YYYY-MM-DD HH24:MI:SS') as last_modified, - fs_objects.content_size, - fs_objects.url, - fs_objects.key, - fs_objects.sort_key, - fs_objects.file_upload_name, - case when fs_objects.last_modified >= (now() - interval '$n_past_days days') then 1 else 0 end as new_p - from fs_objects - where fs_objects.parent_id = :folder_id - order by sort_key, name + + select fs_objects.object_id, + fs_objects.name, + fs_objects.live_revision, + fs_objects.type, + to_char(fs_objects.last_modified, 'YYYY-MM-DD HH24:MI:SS') as last_modified, + fs_objects.content_size, + fs_objects.url, + fs_objects.sort_key, + fs_objects.file_upload_name, + case when fs_objects.last_modified >= (now() - interval '$n_past_days days') then 1 else 0 end as new_p, + delete_p, + write_p + from + (select fs_folders.folder_id as object_id, + 0 as live_revision, + 'folder' as type, + fs_folders.content_size, + fs_folders.name, + '' as file_upload_name, + fs_folders.last_modified, + '' as url, + fs_folders.parent_id, + cast('f' as bool) as write_p, + cast('f' as bool) as delete_p, + 0 as sort_key + from fs_folders + where fs_folders.parent_id = :folder_id + and exists (select 1 + from acs_object_party_privilege_map m + where m.object_id = fs_folders.folder_id + and m.party_id = :viewing_user_id + and m.privilege = 'read') + union all + select fs_files.file_id as object_id, + fs_files.live_revision, + fs_files.type, + fs_files.content_size, + fs_files.name, + fs_files.file_upload_name, + fs_files.last_modified, + '' as url, + fs_files.parent_id, + cast('f' as bool) as write_p, + cast('f' as bool) as delete_p, + 1 as sort_key + from fs_files + where fs_files.parent_id = :folder_id + and exists (select 1 + from acs_object_party_privilege_map m + where m.object_id = fs_files.file_id + and m.party_id = :viewing_user_id + and m.privilege = 'read') + union all + select fs_urls_full.url_id as object_id, + 0 as live_revision, + 'url' as type, + 0 as content_size, + fs_urls_full.name, + fs_urls_full.name as file_upload_name, + fs_urls_full.last_modified, + fs_urls_full.url, + fs_urls_full.folder_id as parent_id, + acs_permission__permission_p(fs_urls.url_id, :viewing_user_id, 'write') as write_p, + acs_permission__permission_p(fs_urls.url_id, :viewing_user_id, 'delete') as delete_p, + 1 as sort_key + from fs_urls_full + where fs_urls_full.folder_id = :folder_id + and exists (select 1 + from acs_object_party_privilege_map m + where m.object_id = fs_urls_full.url_id + and m.party_id = :viewing_user_id + and m.privilege = 'read')) as fs_objects + order by fs_objects.sort_key, fs_objects.name + Index: openacs-4/packages/file-storage/www/folder-chunk.adp =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/folder-chunk.adp,v diff -u -r1.15 -r1.16 --- openacs-4/packages/file-storage/www/folder-chunk.adp 16 Jan 2003 13:47:56 -0000 1.15 +++ openacs-4/packages/file-storage/www/folder-chunk.adp 18 Feb 2003 20:56:09 -0000 1.16 @@ -23,7 +23,7 @@   - @contents.content_size_pretty@ #file-storage.items##file-storage.item# + @contents.content_size@ #file-storage.items##file-storage.item# #file-storage.folder# @contents.last_modified@ @@ -37,18 +37,18 @@ - + [ #file-storage.edit# - + | #file-storage.delete# - + ] @@ -74,7 +74,7 @@ ] - @contents.content_size_pretty@ bytes + @contents.content_size@ bytes @contents.type@ @contents.last_modified@ Index: openacs-4/packages/file-storage/www/folder-chunk.tcl =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/www/folder-chunk.tcl,v diff -u -r1.10 -r1.11 --- openacs-4/packages/file-storage/www/folder-chunk.tcl 18 Nov 2002 18:01:05 -0000 1.10 +++ openacs-4/packages/file-storage/www/folder-chunk.tcl 18 Feb 2003 20:56:09 -0000 1.11 @@ -29,13 +29,12 @@ set fs_url "" } +db_multirow contents select_folder_contents {} { + set file_upload_name [fs::remove_special_file_system_characters -string $file_upload_name] + set last_modified [lc_time_fmt $last_modified "%x %X"] + set content_size [lc_numeric $content_size] +} + set folder_name [fs::get_object_name -object_id $folder_id] -set rows [fs::get_folder_contents \ - -folder_id $folder_id \ - -user_id $viewing_user_id \ - -n_past_days $n_past_days \ -] -util::list_of_ns_sets_to_multirow -rows $rows -var_name "contents" - ad_return_template