Forum OpenACS Development: Deleting WF case log entries and notification requests ...

I've been debugging why you no longer can delete curriculums and I traced the problem back to the fact that when a workflow case associated with a curriculum object gets deleted the log entries for the case and the notifications and notification requests are left linguering. Therefore you get a foreign key constraint violation when you delete the curriculum object ...

I've managed to make the problem go away by letting workflow_case_pkg__delete do a couple of more things, and among them is looping over the cr items that are the case log entries, and deleting them. Also, to be able to delete these items we should add an "on delete cascade" statement for workflow_case_log.entry_id, I think. I can't see how that whould hurt.

The notifications and the notification requests that remain to be deleted before you can get rid of the curriculum object should probably be taken care of by adding a call to "notification::request::delete_all". I've currently added this to the package specific "curriculum::delete" but I believe it belongs in "workflow::case::delete" instead.

What do you think about this? Personally, I think this type of "clean-up" operations should be performed centrally by the "thing" that created these tuples to begin with. In this case, each package that implements workflow shouldn't need to worry about these details ...

If this sounds right, I will prepare the corresponding changes for oracle (although I can't test those myself at the moment) and write the upgrade scripts for 5.0.

create table workflow_case_log (
  entry_id                integer
                          constraint wf_case_log_pk
                          primary key
                          constraint wf_case_log_cr_items_fk
                          references cr_items(item_id)
                          on delete cascade,
  case_id                 integer
                          constraint wf_case_log_case_id_fk
                          references workflow_cases(case_id)
                          on delete cascade,
  action_id               integer
                          constraint wf_case_log_acn_id_fk
                          references workflow_actions(action_id)
                          on delete cascade
);





create or replace function workflow_case_pkg__delete (integer)
returns integer as '
declare
  delete_case_id                alias for $1;
  rec                           record;
begin                                                                                                                                                                                 

 -- Delete the items corresponding to case log entries for this case.
    -- The entries in workflow_case_log will cascade.
    for rec in select ci.item_id
               from cr_items ci, workflow_case_log wcl
               where ci.item_id = wcl.entry_id
               and wcl.case_id = delete_case_id loop
                                                                                                                                                            
        perform content_item__delete (rec.item_id);
    end loop;
                                                                                                                                                            
    -- All workflow data cascades from the case id
    delete from workflow_cases
      where case_id = delete_case_id;
                                                                                                                                                            
  return 0;
end;' language 'plpgsql';

/Ola