Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql,v diff -u -N -r1.63.2.2 -r1.63.2.3 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 10 Feb 2016 09:14:19 -0000 1.63.2.2 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 11 Feb 2016 08:07:03 -0000 1.63.2.3 @@ -390,10 +390,14 @@ references acs_objects, parent_id integer constraint cr_child_rels_parent_id_nn - not null, + not null + CONSTRAINT cr_child_rels_parent_id_fk + FOREIGN KEY (parent_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE, child_id integer constraint cr_child_rels_child_id_nn - not null, + not null + CONSTRAINT cr_child_rels_child_id_fk + FOREIGN KEY (child_id) REFERENCES cr_items(item_id) ON DELETE CASCADE, relation_tag varchar(100), order_n integer ); @@ -417,7 +421,8 @@ references acs_objects, item_id integer constraint cr_item_rels_item_id_fk - references cr_items, + CONSTRAINT cr_item_rels_item_id_fk + FOREIGN KEY (item_id) REFERENCES cr_items(item_id) ON DELETE CASCADE, related_object_id integer constraint cr_item_rels_rel_obj_fk references acs_objects, @@ -950,8 +955,8 @@ constraint cr_symlinks_symlink_id_pk primary key, target_id integer - constraint cr_symlinks_target_id_fk - references cr_items + CONSTRAINT cr_symlinks_target_id_fk + FOREIGN KEY (target_id) REFERENCES cr_items(item_id) ON DELETE CASCADE constraint cr_symlinks_target_id_nn not null, label varchar(1000) Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql,v diff -u -N -r1.72.2.4 -r1.72.2.5 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 10 Feb 2016 09:14:19 -0000 1.72.2.4 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 11 Feb 2016 08:07:03 -0000 1.72.2.5 @@ -1037,30 +1037,19 @@ -- 6) delete keyword associations -- 7) delete all associated comments - select define_function_args('content_item__del','item_id'); + -- -- procedure content_item__del/1 -- CREATE OR REPLACE FUNCTION content_item__del( delete__item_id integer ) RETURNS integer AS $$ DECLARE - v_symlink_val record; - v_revision_val record; - v_rel_val record; + v_revision_val record; + v_child_val record; BEGIN -- - -- Delete all symlinks to this item - -- - for v_symlink_val in select symlink_id - from cr_symlinks - where target_id = delete__item_id - LOOP - PERFORM content_symlink__delete(v_symlink_val.symlink_id); - end loop; - - -- -- Delete all revisions of this item -- -- The following loop could be dropped / replaced by a cascade @@ -1075,35 +1064,19 @@ end loop; -- - -- Delete all relations on this item + -- Delete all children of this item via a recursive call. -- - for v_rel_val in select rel_id - from cr_item_rels - where item_id = delete__item_id - or related_object_id = delete__item_id - LOOP - PERFORM acs_rel__delete(v_rel_val.rel_id); - end loop; - - for v_rel_val in select rel_id - from cr_child_rels - where child_id = delete__item_id - LOOP - PERFORM acs_rel__delete(v_rel_val.rel_id); - end loop; - - for v_rel_val in select rel_id, child_id - from cr_child_rels - where parent_id = delete__item_id - LOOP - PERFORM acs_rel__delete(v_rel_val.rel_id); - PERFORM content_item__delete(v_rel_val.child_id); - end loop; - + -- The following loop is just needed to delete the revisions of + -- child items. It could be removed, when proper foreign keys are + -- used along the inheritence path of cr_content_revisions (which is + -- not enforced and not always the case). -- - -- Delete associated comments - -- - PERFORM journal_entry__delete_for_object(delete__item_id); + for v_child_val in select item_id + from cr_items + where parent_id = delete__item_id + LOOP + PERFORM content_item__delete(v_child_val.item_id); + end loop; -- -- Finally, delete the acs_object of the item. Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-search.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-search.sql,v diff -u -N -r1.10.2.1 -r1.10.2.2 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-search.sql 2 Feb 2016 13:27:52 -0000 1.10.2.1 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-search.sql 11 Feb 2016 08:07:03 -0000 1.10.2.2 @@ -81,8 +81,10 @@ -- DaveB: We only want to index live_revisions 2002-09-26 +-- +-- procedure content_search__itrg/0 +-- - CREATE OR REPLACE FUNCTION content_search__itrg () RETURNS trigger AS $$ BEGIN if (select live_revision from cr_items where item_id=new.item_id) = new.revision_id and new.publish_date >= current_timestamp then @@ -92,15 +94,7 @@ END; $$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION content_search__dtrg () RETURNS trigger AS $$ -BEGIN - perform search_observer__enqueue(old.revision_id,'DELETE'); - return old; -END; -$$ LANGUAGE plpgsql; - - -- -- procedure content_search__utrg/0 -- @@ -151,9 +145,6 @@ create trigger content_search__itrg after insert on cr_revisions for each row execute procedure content_search__itrg (); -create trigger content_search__dtrg after delete on cr_revisions -for each row execute procedure content_search__dtrg (); - create trigger content_search__utrg after update on cr_revisions for each row execute procedure content_search__utrg (); Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.1d8-5.9.1d9.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.1d8-5.9.1d9.sql,v diff -u -N --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.1d8-5.9.1d9.sql 11 Feb 2016 08:07:03 -0000 1.1.2.1 @@ -0,0 +1,126 @@ +-- +-- Note: some of the update operations might take on large sites a +-- couple of minutes, since these operate on the largest tables of +-- OpenACS. You might consider to run this on production offline or +-- with a proxy turned off. +-- +-- Make sure there are no stray entries in cr_child_rels +-- +delete from cr_child_rels where parent_id in (select parent_id from cr_child_rels except select object_id from acs_objects); +delete from cr_child_rels where child_id in (select child_id from cr_child_rels except select item_id from cr_items); + +-- +-- Add FK constraints for cr_child_rels with cascade operations +-- +ALTER TABLE cr_child_rels DROP CONSTRAINT IF EXISTS cr_child_rels_parent_id_fk; +ALTER TABLE cr_child_rels ADD CONSTRAINT cr_child_rels_parent_id_fk +FOREIGN KEY (parent_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE; + +ALTER TABLE cr_child_rels DROP CONSTRAINT IF EXISTS cr_child_rels_child_id_fk; +ALTER TABLE cr_child_rels ADD CONSTRAINT cr_child_rels_child_id_fk +FOREIGN KEY (child_id) REFERENCES cr_items(item_id) ON DELETE CASCADE; + +-- +-- Add FK constraints for cr_item_rels with cascade operations +-- +ALTER TABLE cr_item_rels DROP CONSTRAINT IF EXISTS cr_item_rels_item_id_fk; +ALTER TABLE cr_item_rels ADD CONSTRAINT cr_item_rels_item_id_fk +FOREIGN KEY (item_id) REFERENCES cr_items(item_id) ON DELETE CASCADE; + +-- +-- alter FK constraints to of context index and of acs_objects.context_id to cascade operations +-- +ALTER TABLE acs_object_context_index DROP CONSTRAINT IF EXISTS acs_obj_context_idx_anc_id_fk; +ALTER TABLE acs_object_context_index ADD CONSTRAINT acs_obj_context_idx_anc_id_fk +FOREIGN KEY (ancestor_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE; + +ALTER TABLE acs_object_context_index DROP CONSTRAINT IF EXISTS acs_obj_context_idx_obj_id_fk; +ALTER TABLE acs_object_context_index ADD CONSTRAINT acs_obj_context_idx_obj_id_fk +FOREIGN KEY (object_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE; + +ALTER TABLE acs_objects DROP CONSTRAINT IF EXISTS acs_objects_context_id_fk; +ALTER TABLE acs_objects ADD CONSTRAINT acs_objects_context_id_fk +FOREIGN KEY (context_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE; + +-- +-- Since acs_objects_context_id_fk cascades, there is no need for an +-- extra trigger +-- +DROP TRIGGER IF EXISTS acs_objects_context_id_del_tr ON acs_objects; +DROP FUNCTION IF EXISTS acs_objects_context_id_del_tr(); + +-- +-- alter FK constraints of symlinks to cascade +-- +ALTER TABLE cr_symlinks DROP CONSTRAINT IF EXISTS cr_symlinks_target_id_fk; +ALTER TABLE cr_symlinks ADD CONSTRAINT cr_symlinks_target_id_fk +FOREIGN KEY (target_id) REFERENCES cr_items(item_id) ON DELETE CASCADE; + +-- +-- procedure content_item__del/1 +-- +CREATE OR REPLACE FUNCTION content_item__del( + delete__item_id integer +) RETURNS integer AS $$ +DECLARE + v_revision_val record; + v_child_val record; +BEGIN + -- + -- Delete all revisions of this item + -- + -- The following loop could be dropped / replaced by a cascade + -- operation, when proper foreign keys are used along the + -- inheritence path. + -- + for v_revision_val in select revision_id + from cr_revisions + where item_id = delete__item_id + LOOP + PERFORM acs_object__delete(v_revision_val.revision_id); + end loop; + + -- + -- Delete all children of this item via a recursive call. + -- + -- The following loop is just needed to delete the revisions of + -- child items. It could be removed, when proper foreign keys are + -- used along the inheritence path of cr_content_revisions (which is + -- not enforced and not always the case). + -- + for v_child_val in select item_id + from cr_items + where parent_id = delete__item_id + LOOP + PERFORM content_item__delete(v_child_val.item_id); + end loop; + + -- + -- Finally, delete the acs_object of the item. + -- + PERFORM acs_object__delete(delete__item_id); + + return 0; +END; +$$ LANGUAGE plpgsql; + + +-- +-- The content_search__dtrg tries to add entries on deletion the the +-- search queue via +-- +-- SELECT search_observer__enqueue(old.revision_id,'DELETE') +-- +-- However, we do not need to queue the revision_id for deletion, +-- since the content-repository uses always the revision_id as +-- object_id. It might be a problem, if there would be a way to use +-- the item_id as object_id for search and to remove content based on +-- the revision_id, but this does not seem to be possible. If i am +-- wrong, one has to revive content_search__dtrg. +-- +-- Since the search_observer_queue and txt have FK with cascades, +-- there is no need for an extra trigger +-- +DROP TRIGGER IF EXISTS content_search__dtrg ON cr_revisions; +DROP FUNCTION IF EXISTS content_search__dtrg(); +