Index: openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql,v diff -u -r1.2 -r1.3 --- openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql 28 Aug 2001 23:05:52 -0000 1.2 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-create.sql 30 Aug 2001 12:43:31 -0000 1.3 @@ -11,7 +11,6 @@ -- each instance of file storage -- - create table fs_root_folders ( -- ID for this package instance package_id integer @@ -20,44 +19,7 @@ constraint fs_root_folder_package_id_pk primary key, -- the ID of the root folder - -- JS: I removed the on delete cascade constraint on folder_id!!!!!!!!!!!!!!!!!!!!! - -- JS: - -- JS: 1) folder_id points to the root folder id in CR, which cannot (and should not) be deleted - -- JS: unless the package instance is deleted (which is done from APM). Thus there is - -- JS: no conceivable scenario when the "on delete cascade" will be triggered to delete - -- JS: the entry in fs_root_folders through the deletion of the root - -- JS: folder id. This constraint is superfluous. - -- JS: - -- JS: 2) There is a nasty bug in the implementation of File Storage (FS), where if a package - -- JS: instance is deleted, the "on delete cascade" on package_id will cause the deletion of - -- JS: the row containing package_id and folder_id when the instance is deleted. Since the - -- JS: CR items inserted by this FS instance is accessible only through the root folder_id, - -- JS: the CR items are orphaned! - -- JS: - -- JS: To clean up CR, we need to impose a trigger on deletion of the row containing the root - -- JS: folder_id (i.e., when the package instance is deleted). With "on delete cascade" - -- JS: constaint imposed on folder_id, any trigger on the deletion of a package instance - -- JS: to clean up CR creates a circular reference. To see this, note cleaning up CR - -- JS: requires an inverted tree of the items belonging to the package be created, so - -- JS: that we can clean up from the leaves of the tree up to the root node. - -- JS: We then call content_item.delete() or content_folder.delete() to remove the file or - -- JS: folder, as the case may be. However, content_item.delete() (cotent_folder.delete - -- JS: calls content_item.delete) calls on acs_object.delete, relying on the "on delete - -- JS: cascade" constraint imposed on item_id of cr_items to do the actual deletion. Now - -- JS: with "on delete cascade" also imposed on folder_id of fs_root_folders, the "on delete - -- JS: cascade" on item_id of cr_items will trigger deletion of the entry in fs_root_folders - -- JS: AS IT IS BEING DELETED BY APM. Thus, we will get the famous "mutating tables" error - -- JS: in oracle. - -- JS: - -- JS: So we simplify our life and drop the "on delete cascade". We still requie the foreign - -- JS: key constraint. The subtle problem this will cause is that when we clean up CR, we - -- JS: cannot prune the tree up to the root folder, since it will cause a foreign key - -- JS: constraint. We could also drop the foreign key constraint, but we will expose our - -- JS: database to integrity errors. The solution is to make two triggers: a "before delete" - -- JS: trigger on fs_root_folders that cleans up the CR entries of the package except the - -- JS: root folder, and an "after delete" trigger that cleans up the root folder entry - -- JS: (since the folder id will have been deleted, so no foreign key reference will be - -- JS: violated by the deletion of the root folder) + -- JS: I removed the on delete cascade constraint on folder_id folder_id integer constraint fs_root_folder_folder_id_fk references cr_folders Index: openacs-4/packages/file-storage/sql/oracle/file-storage-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/oracle/file-storage-drop.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/file-storage/sql/oracle/file-storage-drop.sql 30 Aug 2001 01:10:47 -0000 1.3 +++ openacs-4/packages/file-storage/sql/oracle/file-storage-drop.sql 30 Aug 2001 12:45:25 -0000 1.4 @@ -12,28 +12,15 @@ -- content repository is set up to cascade, so we should just have to -- delete the root folders -- --- JS: The above is wrong. You cannot, for example, delete a folder with contents on it so --- JS: trying to delete the root folder will raise an exception. Also, acs_object.delete --- JS: is the wrong way to delete the items in CR. To delete a folder, for example, we must --- JS: call content_folder.delete, which in turn calls acs_object.delete only through --- JS: content_item.delete and only after cleaning up stuff). So if the package is deleted but --- JS: there are still items in it, this drop script will fail. Perhaps it should, since it makes --- JS: sense to delete the package only when the are no more stuff in it (perhaps moved elsewhere). --- JS: Thus, an empty file storage is the only way this drop script will succeed. But then again, --- JS: the package should not barf if indeed the admin wants to nuke the package and --- JS: everything in it! (This is why deleting a package has a warning, I guess.) --- JS: --- JS: To delete properly, note: how did entries in fs_root_folders come about? Through APM! --- JS: So we instead delete the *package instances*, and the triggers that we imposed on fs_root_folders --- JS: will take care of cleaning up whatever items are in CR that are related to the package. declare cursor c_root_folders is select package_id from fs_root_folders; begin for v_root_folder in c_root_folders loop - -- acs_object.delete(v_root_folder.folder_id); + -- JS: The RI constraints will cause acs_objects.delete to fail + -- JS: So I changed this to apm_package.delete apm_package.delete(v_root_folder.package_id); end loop; end; Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql 28 Aug 2001 23:07:55 -0000 1.3 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-create.sql 30 Aug 2001 12:40:38 -0000 1.4 @@ -10,8 +10,6 @@ -- We need to create a root folder in the content repository for -- each instance of file storage -- - - create table fs_root_folders ( -- ID for this package instance package_id integer @@ -20,43 +18,8 @@ constraint fs_root_folder_package_id_pk primary key, -- the ID of the root folder - -- JS: I removed the on delete cascade constraint on folder_id!!!!!!!!!!!!!!!!!!!!! - -- JS: - -- JS: 1) folder_id points to the root folder id in CR, which cannot (and should not) be deleted - -- JS: unless the package instance is deleted (which is done from APM). Thus there is - -- JS: no conceivable scenario when the "on delete cascade" will be triggered to delete - -- JS: the entry in fs_root_folders through the deletion of the root - -- JS: folder id. This constraint is superfluous. - -- JS: - -- JS: 2) There is a nasty bug in the implementation of File Storage (FS), where if a package - -- JS: instance is deleted, the "on delete cascade" on package_id will cause the deletion of - -- JS: the row containing package_id and folder_id when the instance is deleted. Since the - -- JS: CR items inserted by this FS instance is accessible only through the root folder_id, - -- JS: the CR items are orphaned! - -- JS: - -- JS: To clean up CR, we need to impose a trigger on deletion of the row containing the root - -- JS: folder_id (i.e., when the package instance is deleted). With "on delete cascade" - -- JS: constaint imposed on folder_id, any trigger on the deletion of a package instance - -- JS: to clean up CR creates a circular reference. To see this, note cleaning up CR - -- JS: requires an inverted tree of the items belonging to the package be created, so - -- JS: that we can clean up from the leaves of the tree up to the root node. - -- JS: We then call content_item.delete() or content_folder.delete() to remove the file or - -- JS: folder, as the case may be. However, content_item.delete() (cotent_folder.delete - -- JS: calls content_item.delete) calls on acs_object.delete, relying on the "on delete - -- JS: cascade" constraint imposed on item_id of cr_items to do the actual deletion. Now - -- JS: with "on delete cascade" also imposed on folder_id of fs_root_folders, the "on delete - -- JS: cascade" on item_id of cr_items will trigger deletion of the entry in fs_root_folders - -- JS: AS IT IS BEING DELETED BY APM. In oracle, this will trigger the "mutating tables" error. - -- JS: - -- JS: So we simplify our life and drop the "on delete cascade". We still requie the foreign - -- JS: key constraint. The subtle problem this will cause is that when we clean up CR, we - -- JS: cannot prune the tree up to the root folder, since it will cause a foreign key - -- JS: constraint violation. We could also drop the foreign key constraint, but we will expose our - -- JS: database to carelessness errors. The solution is to make two triggers: a "before delete" - -- JS: trigger on fs_root_folders that cleans up the CR entries of the package except the - -- JS: root folder, and an "after delete" trigger that cleans up the root folder entry - -- JS: (since the folder id will have been deleted, so no foreign key reference will be - -- JS: violated by the deletion of the root folder) + -- JS: I removed the on delete cascade constraint on folder_id + -- JS: It is superfluous, and causes a lot of RI headaches folder_id integer constraint fs_root_folder_folder_id_fk references cr_folders Index: openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql =================================================================== RCS file: /usr/local/cvsroot/openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql,v diff -u -r1.3 -r1.4 --- openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql 30 Aug 2001 01:11:13 -0000 1.3 +++ openacs-4/packages/file-storage/sql/postgresql/file-storage-drop.sql 30 Aug 2001 12:42:38 -0000 1.4 @@ -12,21 +12,6 @@ -- content repository is set up to cascade, so we should just have to -- delete the root folders -- --- JS: The above is wrong. You cannot, for example, delete a folder with contents on it so --- JS: trying to delete the root folder will raise an exception. Also, acs_object__delete --- JS: is the wrong way to delete the items in CR. To delete a folder, for example, we must --- JS: call content_folder__delete, which in turn calls acs_object__delete only through --- JS: content_item__delete and only after cleaning up stuff). So if the package is deleted but --- JS: there are still items in it, this drop script will fail. Perhaps it should, since it makes --- JS: sense to delete the package only when the are no more stuff in it (perhaps moved elsewhere). --- JS: Thus, an empty file storage is the only way this drop script will succeed. But then again, --- JS: the package should not barf if indeed the admin wants to nuke the package and --- JS: everything in it! (This is why deleting a package has a warning, I guess.) --- JS: --- JS: To delete properly, note: how did entries in fs_root_folders come about? Through APM! --- JS: So we instead delete the *package instances*, and the triggers that we imposed on fs_root_folders --- JS: will take care of cleaning up whatever items are in CR that are related to the package. - create function inline_0() returns integer as ' declare @@ -37,7 +22,8 @@ select package_id from fs_root_folders loop - -- PERFORM acs_object__delete(rec_root_folder.folder_id); + -- JS: The RI constraints will cause acs_objects__delete to fail + -- JS: So I changed this to apm_package__delete PERFORM apm_package__delete(rec_root_folder.package_id); end loop;