Forum OpenACS Development: Response to Can't delete unmounted ETP instance

Ok, I think I understand now. The table definition for cr_folders changed. In the most current version (1.35) of acs-content-repository/sql/postgres/content-create.sql, cr_folders is defined as

create table cr_folders (
  folder_id	    integer
  -- removed due to postgresql RI bug which causes deletion failures.
  -- replace with user triggers
  -- DanW (dcwickstrom@earthlink.net)

  --		    constraint cr_folder_id_fk references
  --		    cr_items on delete cascade
		    constraint cr_folders_pk 
                    primary key,
  label		    varchar(1000),
  description	    text,
  has_child_folders boolean default 'f',
  has_child_symlinks boolean default 'f',
  package_id integer 
  constraint cr_fldr_pkg_id_fk
  references apm_packages
);  

In previous versions (at least in v1.26 that I still have a copy of), there is no column "package_id" referencing apm_packages. Thus, I suspect that the add-on contraint found at the end of the file

alter table cr_folders
add constraint cr_flder_pkg_id_fk foreign key (package_id) references apm_packages (package_id);

was mistakenly left behind when cr_folders changed. Given the new table definition of cr_folders, this last contraint is redundant and can be taken out.

Don, I still think that there may be specialized situations when "on delete cascade" is useful, particularly on this one. We have acs-content-repository and APM (in acs-kernel) as two separate packages, but they must somehow "communicate" with each other when packages are installed and deleted. Installation is a separate problem, but let's see what happens when an instance of a package is deleted, say ETP. APM will delete the entry with package_id in the table apm_packages corresponding to the ETP instance. Without "on delete cacade", there is no mechanism in ACS that allows APM to tell acs-content-repository, "Hey, this instance of ETP is being deleted, clean up the contents of this ETP instance in the database." They are distinct packages, APM was built without any prior knowledge of what it needs to tell other packages like acs-content-repository what to do when APM does something.

I view "on delete cascade" as a way for a package to receive communication from another package about actions that affect it, without altering the datamodel of the other package (in this case, APM in acs-kernel). File-storage solved (I should say, hacked) the problem by constructing a table that maps package_id and folder_id. Note that this was necessary because previous versions of cr_folders didn't have the package_id attribute. This file-storage table specifies "on delete cascade" on package_id, so that when the package instance is deleted by APM, file-storage is informed about it. File-storage has "on delete" triggers that cleans up the contents of the file-storage instance when APM tries to delete it (otherwise, the contents are orphaned, which is an even nastier bug that simply refusing the deletion). I won't go over the code here -- it's on the current CVS.

Note that "on delete" triggers are defined by the file-storage package, so it does not alter in any way the acs-content-repository datamodel. I think the triggers actually belong to acs-content-repository because the mechanism can also be used by ETP. The nice thing though about having package-specific triggers is that it allows the package writers flexibility on what to do when cleaning up. File-storage is actually a bit moronic in this regard, because it simply deletes all content when the package instance is deleted. I would prefer archiving it somehow in acs-content-repository, while still allowing the instance to be deleted. Or do what ETP does: put them in the "Trash" folder. Of course, we still need to clean up the "Trash" folder if the package (and not just a package instance) is totally deleted.

Now about the problem on installation: when a package_id is inserted in apm_packages coresponding to the installation of a package instance, packages that are built on top of acs-content-repository must also do other automatic stuff, like create folders (e.g., the "root folder" in file-storage). Again, there is no way for APM to tell acs-content-repository, "Hey, I just created an instance, go ahead and create the folders you need". File-storage again has a hack to solve this (see the file-storage-proc.tcl file), but it would be preferable to have in APM a way for a package to register an "initialization" function (a pgsql function) that gets called when a package instance is created.