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.71.2.5 -r1.71.2.6 --- openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 24 Mar 2021 09:51:12 -0000 1.71.2.5 +++ openacs-4/packages/acs-kernel/sql/postgresql/acs-objects-create.sql 8 Oct 2022 20:07:17 -0000 1.71.2.6 @@ -337,158 +337,6 @@ titles or object_names of package specific tables. '; ------------------------ --- CONTEXT HIERARCHY -- ------------------------ - -create table acs_object_context_index ( - object_id integer not null - CONSTRAINT acs_obj_context_idx_obj_id_fk - REFERENCES acs_objects(object_id) ON DELETE CASCADE, - ancestor_id integer not null - CONSTRAINT acs_obj_context_idx_anc_id_fk - REFERENCES acs_objects(object_id) ON DELETE CASCADE, - n_generations integer not null - constraint acs_obj_context_idx_n_gen_ck - check (n_generations >= 0), - constraint acs_object_context_index_pk - primary key (object_id, ancestor_id) -); - -create index acs_obj_ctx_idx_ancestor_idx on acs_object_context_index (ancestor_id); -create index acs_obj_ctx_idx_object_id_idx on acs_object_context_index (object_id); - -create view acs_object_paths -as select object_id, ancestor_id, n_generations - from acs_object_context_index; - -create view acs_object_contexts -as select object_id, ancestor_id, n_generations - from acs_object_context_index - where object_id != ancestor_id; - - - --- --- procedure acs_objects_context_id_in_tr/0 --- -CREATE OR REPLACE FUNCTION acs_objects_context_id_in_tr( - -) RETURNS trigger 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 trigger acs_objects_context_id_in_tr after insert on acs_objects -for each row execute procedure acs_objects_context_id_in_tr (); - - - --- --- procedure acs_objects_context_id_up_tr/0 --- -CREATE OR REPLACE FUNCTION acs_objects_context_id_up_tr( - -) RETURNS trigger AS $$ -DECLARE - pair record; - outer_record record; - inner_record record; - security_context_root integer; -BEGIN - if new.object_id = old.object_id - and ((new.context_id = old.context_id) - or (new.context_id is null and old.context_id is null)) - and new.security_inherit_p = old.security_inherit_p then - return new; - end if; - - -- Remove my old ancestors from my descendants. - for outer_record in select object_id from acs_object_context_index where - ancestor_id = old.object_id and object_id <> old.object_id loop - for inner_record in select ancestor_id from acs_object_context_index where - object_id = old.object_id and ancestor_id <> old.object_id loop - delete from acs_object_context_index - where object_id = outer_record.object_id - and ancestor_id = inner_record.ancestor_id; - end loop; - end loop; - - -- 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; - -create trigger acs_objects_context_id_up_tr after update on acs_objects -for each row execute procedure acs_objects_context_id_up_tr (); - ---------------------- -- ATTRIBUTE VALUES -- ---------------------- @@ -1337,60 +1185,7 @@ --- --- procedure acs_object__check_context_index/3 --- -CREATE OR REPLACE FUNCTION acs_object__check_context_index( - check_context_index__object_id integer, - check_context_index__ancestor_id integer, - check_context_index__n_generations integer -) RETURNS boolean AS $$ -DECLARE - n_rows integer; - n_gens integer; -BEGIN - -- Verify that this row exists in the index. - if check_context_index__object_id is null or check_context_index__ancestor_id is null then - raise exception 'object_id or ancestor_id is null in acs_object__check_context_index'; - end if; - select case when count(*) = 0 then 0 else 1 end 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 != check_context_index__n_generations then - PERFORM acs_log__error('acs_object.check_representation', - 'Ancestor ' || - check_context_index__ancestor_id || ' of object ' || - check_context_index__object_id || - ' reports being generation ' || n_gens || - ' when it is actually generation ' || - check_context_index__n_generations || - '.'); - return 'f'; - else - return 't'; - end if; - else - PERFORM acs_log__error('acs_object.check_representation', - 'Ancestor ' || - check_context_index__ancestor_id || - ' of object ' || check_context_index__object_id - || ' is missing an entry in acs_object_context_index.'); - return 'f'; - end if; - -END; -$$ LANGUAGE plpgsql; - - -- function check_object_ancestors @@ -1569,109 +1364,7 @@ $$ LANGUAGE plpgsql stable; --- function check_representation - -select define_function_args('acs_object__check_representation','object_id'); - - - -- --- procedure acs_object__check_representation/1 --- -CREATE OR REPLACE FUNCTION acs_object__check_representation( - check_representation__object_id integer -) RETURNS boolean AS $$ -DECLARE - result boolean; - check_representation__object_type acs_objects.object_type%TYPE; - n_rows integer; - v_rec record; - row record; -BEGIN - if check_representation__object_id is null then - raise exception 'acs_object__check_representation called for null object_id'; - end if; - - result := 't'; - PERFORM acs_log__notice('acs_object.check_representation', - 'Running acs_object.check_representation on object_id = ' - || check_representation__object_id || '.'); - - select object_type into check_representation__object_type - from acs_objects - where object_id = check_representation__object_id; - - PERFORM acs_log__notice('acs_object.check_representation', - 'OBJECT STORAGE INTEGRITY TEST'); - - for v_rec 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 - - for row in execute 'select case when count(*) = 0 then 0 else 1 end as n_rows from ' || quote_ident(v_rec.table_name) || ' where ' || quote_ident(v_rec.id_column) || ' = ' || check_representation__object_id - LOOP - n_rows := row.n_rows; - exit; - end LOOP; - - if n_rows = 0 then - result := 'f'; - PERFORM acs_log__error('acs_object.check_representation', - 'Table ' || v_rec.table_name || - ' (primary storage for ' || - v_rec.object_type || - ') doesn''t have a row for object ' || - check_representation__object_id || ' of type ' || - check_representation__object_type || '.'); - end if; - - end loop; - - PERFORM acs_log__notice('acs_object.check_representation', - 'OBJECT CONTEXT INTEGRITY TEST'); - - if acs_object__check_object_ancestors(check_representation__object_id, - check_representation__object_id, 0) = 'f' then - result := 'f'; - end if; - - if acs_object__check_object_descendants(check_representation__object_id, - check_representation__object_id, 0) = 'f' then - result := 'f'; - end if; - for row in select object_id, ancestor_id, n_generations - from acs_object_context_index - where object_id = check_representation__object_id - or ancestor_id = check_representation__object_id - LOOP - if acs_object__check_path(row.object_id, row.ancestor_id) = 'f' then - PERFORM 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; - - PERFORM acs_log__notice('acs_object.check_representation', - 'Done running acs_object.check_representation ' || - 'on object_id = ' || check_representation__object_id || '.'); - - return result; - -END; -$$ LANGUAGE plpgsql; - - - --- -- procedure acs_object__update_last_modified/3 -- CREATE OR REPLACE FUNCTION acs_object__update_last_modified(