Index: openacs-4/packages/acs-content-repository/acs-content-repository.info =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/acs-content-repository.info,v diff -u -r1.106 -r1.107 --- openacs-4/packages/acs-content-repository/acs-content-repository.info 2 May 2015 17:23:21 -0000 1.106 +++ openacs-4/packages/acs-content-repository/acs-content-repository.info 3 May 2015 12:32:28 -0000 1.107 @@ -7,7 +7,7 @@ t t - + OpenACS The canonical repository for OpenACS content. 2013-09-08 @@ -19,7 +19,7 @@ GPL 3 - + 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 -r1.62 -r1.63 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 2 May 2015 17:23:21 -0000 1.62 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-create.sql 3 May 2015 12:32:28 -0000 1.63 @@ -400,8 +400,9 @@ create index cr_child_rels_by_parent on cr_child_rels(parent_id); create unique index cr_child_rels_unq_id on cr_child_rels(parent_id, child_id); -CREATE UNIQUE INDEX CR_CHILD_RELS_kids_IDx ON CR_CHILD_RELS(CHILD_ID); +create unique index cr_child_rels_child_id_idx on cr_child_rels(child_id); + comment on table cr_child_rels is ' Provides for richer parent-child relationships than the simple link encapsulated in the primary table. May be subclassed to provide @@ -507,10 +508,10 @@ '; alter table cr_items add constraint cr_items_live_fk - foreign key (live_revision) references cr_revisions(revision_id); + foreign key (live_revision) references cr_revisions(revision_id) on delete set null; alter table cr_items add constraint cr_items_latest_fk - foreign key (latest_revision) references cr_revisions(revision_id); + foreign key (latest_revision) references cr_revisions(revision_id) on delete set null; @@ -740,11 +741,11 @@ create table cr_item_publish_audit ( item_id integer - constraint cr_item_publish_audit_item_fk references cr_items, + constraint cr_item_publish_audit_item_fk references cr_items (item_id) on delete cascade, old_revision integer - constraint cr_item_publish_audit_orev_fk references cr_revisions (revision_id), + constraint cr_item_publish_audit_orev_fk references cr_revisions (revision_id) on delete cascade, new_revision integer - constraint cr_item_publish_audit_nrev_fk references cr_revisions (revision_id), + constraint cr_item_publish_audit_nrev_fk references cr_revisions (revision_id) on delete cascade, old_status varchar(40), new_status varchar(40), publish_date timestamptz @@ -764,7 +765,7 @@ create table cr_release_periods ( item_id integer constraint cr_release_periods_item_id_fk - references cr_items + references cr_items on delete cascade constraint cr_release_periods_item_id_pk primary key, start_when timestamptz default current_timestamp, @@ -913,7 +914,7 @@ create table cr_item_template_map ( item_id integer constraint cr_item_template_map_item_fk - references cr_items + references cr_items (item_id) on delete cascade constraint cr_item_template_map_item_nn not null, template_id integer @@ -1151,7 +1152,7 @@ create table cr_item_keyword_map ( item_id integer constraint cr_item_keyword_map_item_id_fk - references cr_items + references cr_items (item_id) on delete cascade constraint cr_item_keyword_map_item_id_nn not null, keyword_id integer 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 -r1.71 -r1.72 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 27 Oct 2014 16:39:07 -0000 1.71 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-item.sql 3 May 2015 12:32:28 -0000 1.72 @@ -1337,139 +1337,100 @@ $$ LANGUAGE plpgsql stable; -/* delete a content item - 1) delete all associated workflows - 2) delete all symlinks associated with this object - 3) delete any revisions for this item - 4) unregister template relations - 5) delete all permissions associated with this item - 6) delete keyword associations - 7) delete all associated comments */ +-- +-- Delete a content item +-- +-- Technically, the following steps are necessary, some of these are +-- achieved via cascading operations: +-- +-- 1) delete all associated workflows +-- 2) delete all symlinks associated with this object +-- 3) delete any revisions for this item +-- 4) unregister template relations +-- 5) delete all permissions associated with this item +-- 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_wf_cases_val record; v_symlink_val record; v_revision_val record; v_rel_val record; BEGIN - - -- Removed this as having workflow stuff in the CR is just plain wrong. - -- DanW, Aug 25th, 2001. - - -- raise NOTICE 'Deleting associated workflows...'; - -- 1) delete all workflow cases associated with this item - -- for v_wf_cases_val in select - -- case_id - -- from - -- wf_cases - -- where - -- object_id = delete__item_id - -- LOOP - -- PERFORM workflow_case__delete(v_wf_cases_val.case_id); - -- end loop; - - -- 2) delete all symlinks to this item - for v_symlink_val in select - symlink_id - from - cr_symlinks - where - target_id = delete__item_id + -- + -- 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 from cr_release_periods - where item_id = delete__item_id; - - update cr_items set live_revision = null, latest_revision = null where item_id = delete__item_id; - - -- 3) delete all revisions of this item - delete from cr_item_publish_audit - where item_id = delete__item_id; - - for v_revision_val in select - revision_id - from - cr_revisions - where - item_id = delete__item_id + -- + -- 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; - - -- 4) unregister all templates to this item - delete from cr_item_template_map - where item_id = delete__item_id; + -- -- Delete all relations on this item - 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 + -- + 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 + 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 + 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; - -- 5) delete associated permissions - delete from acs_permissions - where object_id = delete__item_id; - - -- 6) delete keyword associations - delete from cr_item_keyword_map - where item_id = delete__item_id; - - -- 7) delete associated comments + -- + -- Delete associated comments + -- PERFORM journal_entry__delete_for_object(delete__item_id); - -- context_id debugging loop - --for v_error_val in c_error_cur loop - -- || v_error_val.object_type); - --end loop; - + -- + -- Finally, delete the acs_object of the item. + -- PERFORM acs_object__delete(delete__item_id); return 0; END; $$ LANGUAGE plpgsql; -select define_function_args('content_item__delete','item_id'); +select define_function_args('content_item__delete','item_id'); -- -- procedure content_item__delete/1 -- @@ -1478,14 +1439,14 @@ ) RETURNS integer AS $$ DECLARE BEGIN - PERFORM content_item__del (delete__item_id); - return 0; + PERFORM content_item__del (delete__item_id); + return 0; END; $$ LANGUAGE plpgsql; -select define_function_args('content_item__edit_name','item_id,name'); +select define_function_args('content_item__edit_name','item_id,name'); -- -- procedure content_item__edit_name/2 -- Index: openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql,v diff -u -r1.47 -r1.48 --- openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 7 Jul 2011 10:46:02 -0000 1.47 +++ openacs-4/packages/acs-content-repository/sql/postgresql/content-revision.sql 3 May 2015 12:32:28 -0000 1.48 @@ -405,14 +405,10 @@ $$ LANGUAGE plpgsql; --- function copy --- old define_function_args('content_revision__copy','revision_id,copy_id,target_item_id,creation_user,creation_ip') --- new +-- function copy select define_function_args('content_revision__copy','revision_id,copy_id;null,target_item_id;null,creation_user;null,creation_ip;null'); - - -- -- procedure content_revision__copy/5 -- @@ -516,80 +512,67 @@ $$ LANGUAGE plpgsql; --- procedure delete -select define_function_args('content_revision__del','revision_id'); - - -- +-- Delete a content revisions +-- -- procedure content_revision__del/1 -- +select define_function_args('content_revision__del','revision_id'); + CREATE OR REPLACE FUNCTION content_revision__del( delete__revision_id integer ) RETURNS integer AS $$ DECLARE v_item_id cr_items.item_id%TYPE; v_latest_revision cr_revisions.revision_id%TYPE; - v_live_revision cr_revisions.revision_id%TYPE; - v_rec record; BEGIN + -- + -- Get item_id and the latest revision + -- + select item_id + into v_item_id + from cr_revisions + where revision_id = delete__revision_id; - -- Get item id and latest/live revisions - select item_id into v_item_id from cr_revisions - where revision_id = delete__revision_id; + select latest_revision + into v_latest_revision + from cr_items + where item_id = v_item_id; - select - latest_revision, live_revision - into - v_latest_revision, v_live_revision - from - cr_items - where - item_id = v_item_id; - - -- Recalculate latest revision + -- + -- Recalculate latest revision in case it was deleted + -- if v_latest_revision = delete__revision_id then - for v_rec in - select r.revision_id - from cr_revisions r, acs_objects o - where o.object_id = r.revision_id - and r.item_id = v_item_id - and r.revision_id <> delete__revision_id - order by o.creation_date desc - LOOP - v_latest_revision := v_rec.revision_id; - exit; - end LOOP; + select r.revision_id + into v_latest_revision + from cr_revisions r, acs_objects o + where o.object_id = r.revision_id + and r.item_id = v_item_id + and r.revision_id <> delete__revision_id + order by o.creation_date desc limit 1; + if NOT FOUND then - v_latest_revision := null; + v_latest_revision := null; end if; - + update cr_items set latest_revision = v_latest_revision - where item_id = v_item_id; + where item_id = v_item_id; + end if; - - -- Clear live revision - if v_live_revision = delete__revision_id then - update cr_items set live_revision = null - where item_id = v_item_id; - end if; - -- Clear the audit - delete from cr_item_publish_audit - where old_revision = delete__revision_id - or new_revision = delete__revision_id; - + -- -- Delete the revision + -- PERFORM acs_object__delete(delete__revision_id); return 0; END; $$ LANGUAGE plpgsql; -select define_function_args('content_revision__delete','revision_id'); - +select define_function_args('content_revision__delete','revision_id'); -- -- procedure content_revision__delete/1 -- @@ -598,15 +581,14 @@ ) RETURNS integer AS $$ DECLARE BEGIN - PERFORM content_revision__del(delete__revision_id); + PERFORM content_revision__del(delete__revision_id); return 0; END; $$ LANGUAGE plpgsql; + -- function get_number select define_function_args('content_revision__get_number','revision_id'); - - -- -- procedure content_revision__get_number/1 -- @@ -660,10 +642,10 @@ END; $$ LANGUAGE plpgsql stable strict; --- procedure to_html -select define_function_args('content_revision__to_html','revision_id'); +-- procedure to_html +select define_function_args('content_revision__to_html','revision_id'); -- -- procedure content_revision__to_html/1 -- Index: openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.0d2-5.9.0d3.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.0d2-5.9.0d3.sql,v diff -u --- /dev/null 1 Jan 1970 00:00:00 -0000 +++ openacs-4/packages/acs-content-repository/sql/postgresql/upgrade/upgrade-5.9.0d2-5.9.0d3.sql 3 May 2015 12:32:28 -0000 1.1 @@ -0,0 +1,198 @@ +-- +-- Scalability reform part 3 (content-repository): +-- +-- - content_revision__del: +-- * Removed manual nulling of live_revision and latest_revision +-- by using appropriate ond delete actions on foreign keys +-- * Removed manual deletion of old_revision and new_revision in +-- cr_item_publish_audit by using "on delete cascade" +-- +-- - content_item__del: +-- * Removed manual deletion of item_id in cr_item_publish_audit +-- by using "on delete cascade" +-- * Removed manual deletion of item_id in cr_release_periods +-- by using "on delete cascade" +-- * Removed manual deletion of item_id in cr_item_template_map +-- by using "on delete cascade" +-- * Removed manual deletion of item_id in cr_item_keyword_map +-- by using "on delete cascade" +-- * Removed manual deletion of direct permissions (was already +-- cascading) +-- +-- - Added missing index for child_id to cr_child_rels. +-- This index was in the create scripts (with a non-conformant name), +-- but not in the upgrade scripts + + +-- constraints from acs-content-repository/sql/postgresql/content-revision.sql + +ALTER TABLE cr_item_publish_audit DROP CONSTRAINT cr_item_publish_audit_orev_fk; +ALTER TABLE cr_item_publish_audit ADD CONSTRAINT cr_item_publish_audit_orev_fk +FOREIGN KEY (old_revision) REFERENCES cr_revisions(revision_id) ON DELETE CASCADE; + +ALTER TABLE cr_item_publish_audit DROP CONSTRAINT cr_item_publish_audit_nrev_fk; +ALTER TABLE cr_item_publish_audit ADD CONSTRAINT cr_item_publish_audit_nrev_fk +FOREIGN KEY (new_revision) REFERENCES cr_revisions(revision_id) ON DELETE CASCADE; + + +-- constraints from acs-content-repository/sql/postgresql/content-item.sql + +ALTER TABLE cr_release_periods DROP CONSTRAINT cr_release_periods_item_id_fk; +ALTER TABLE cr_release_periods ADD CONSTRAINT cr_release_periods_item_id_fk +FOREIGN KEY (item_id) REFERENCES cr_items(item_id) ON DELETE CASCADE; + +ALTER TABLE cr_item_publish_audit DROP CONSTRAINT cr_item_publish_audit_item_fk; +ALTER TABLE cr_item_publish_audit ADD CONSTRAINT cr_item_publish_audit_item_fk +FOREIGN KEY (item_id) REFERENCES cr_items(item_id) ON DELETE CASCADE; + +ALTER TABLE cr_item_template_map DROP CONSTRAINT cr_item_template_map_item_fk; +ALTER TABLE cr_item_template_map ADD CONSTRAINT cr_item_template_map_item_fk +FOREIGN KEY (item_id) REFERENCES cr_items(item_id) ON DELETE CASCADE; + +ALTER TABLE cr_item_keyword_map DROP CONSTRAINT cr_item_keyword_map_item_id_fk; +ALTER TABLE cr_item_keyword_map ADD CONSTRAINT cr_item_keyword_map_item_id_fk +FOREIGN KEY (item_id) REFERENCES cr_items(item_id) ON DELETE CASCADE; + +ALTER TABLE cr_items DROP CONSTRAINT cr_items_latest_fk; +ALTER TABLE cr_items ADD CONSTRAINT cr_items_latest_fk +FOREIGN KEY (latest_revision) REFERENCES cr_revisions(revision_id) on delete set null; + +ALTER TABLE cr_items DROP CONSTRAINT cr_items_live_fk +ALTER TABLE cr_items ADD CONSTRAINT cr_items_live_fk +FOREIGN KEY (live_revision) REFERENCES cr_revisions(revision_id) on delete set null; + + +DROP INDEX if exists CR_CHILD_RELS_kids_IDx; +CREATE INDEX cr_child_rels_child_id_idx on cr_child_rels(child_id); + +-- +-- updated functions +-- + +CREATE OR REPLACE FUNCTION content_revision__del( + delete__revision_id integer +) RETURNS integer AS $$ +DECLARE + v_item_id cr_items.item_id%TYPE; + v_latest_revision cr_revisions.revision_id%TYPE; +BEGIN + -- + -- Get item_id and the latest revision + -- + select item_id + into v_item_id + from cr_revisions + where revision_id = delete__revision_id; + + select latest_revision + into v_latest_revision + from cr_items + where item_id = v_item_id; + + -- + -- Recalculate latest revision in case it was deleted + -- + if v_latest_revision = delete__revision_id then + + select r.revision_id + into v_latest_revision + from cr_revisions r, acs_objects o + where o.object_id = r.revision_id + and r.item_id = v_item_id + and r.revision_id <> delete__revision_id + order by o.creation_date desc limit 1; + + if NOT FOUND then + v_latest_revision := null; + end if; + + update cr_items set latest_revision = v_latest_revision + where item_id = v_item_id; + + end if; + + -- + -- Delete the revision + -- + PERFORM acs_object__delete(delete__revision_id); + + return 0; +END; +$$ LANGUAGE plpgsql; + + + +-- +-- 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; +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 + -- 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 relations on this item + -- + 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; + + -- + -- Delete associated comments + -- + PERFORM journal_entry__delete_for_object(delete__item_id); + + -- + -- Finally, delete the acs_object of the item. + -- + PERFORM acs_object__delete(delete__item_id); + + return 0; +END; +$$ LANGUAGE plpgsql;