Forum OpenACS Development: ]project-open[ new Filestorage - acs_object__delete throws acs_permissions_on_what_id_fk

Hi!

We are working on a new file-storage system for ]po[ based on the Content Repository and file-storage. We create special folders per project, and then use a "DropBox" like Windows/Linux client to sync files from the local machine with the server.

This architecture came after warning about issues with WebDAV, thanks!

A first MVP works, although without conflict handling yet.

However, deleting files the file-storage gives errors, just by using the OpenACS GUI of the file-storage (see below).

I've seen similar issues with other object types already: Wouldn't it be correct to delete acs_permission dependencies right in acs_object__delete?

Cheers
Frank

ERROR:  update or delete on table "acs_objects" violates foreign key constraint "acs_permissions_on_what_id_fk" on table "acs_permissions"
DETAIL:  Key (object_id)=(52646) is still referenced from table "acs_permissions".
CONTEXT:  SQL statement "delete from acs_objects where object_id =  52646"
PL/pgSQL function acs_object__delete(integer) line 37 at EXECUTE statement
SQL statement "SELECT acs_object__delete(delete__item_id)"
PL/pgSQL function content_item__del(integer) line 65 at PERFORM
SQL statement "SELECT content_item__del (delete__item_id)"
PL/pgSQL function content_item__delete(integer) line 5 at PERFORM
PL/pgSQL function file_storage__delete_file(integer) line 5 at RETURN
SQL:

	select file_storage__delete_file(52646);

Dear Frank, I cannot comment about Project Open, but the "vanilla" OpenACS already defines the ON DELETE CASCADE behavior you suggest (sorry for the italian shell):

oacs-5-10=# \d acs_permissions
          Tabella "public.acs_permissions"
  Colonna   |          Tipo          | Modificatori 
------------+------------------------+--------------
 object_id  | integer                | non null
 grantee_id | integer                | non null
 privilege  | character varying(100) | non null
Indici:
    "acs_permissions_pk" PRIMARY KEY, btree (object_id, grantee_id, privilege)
    "acs_permissions_grantee_idx" btree (grantee_id)
    "acs_permissions_object_id_idx" btree (object_id)
    "acs_permissions_privilege_idx" btree (privilege)
Vincoli di integrità referenziale
    "acs_permissions_grantee_id_fk" FOREIGN KEY (grantee_id) REFERENCES parties(party_id) ON DELETE CASCADE
    "acs_permissions_object_id_fk" FOREIGN KEY (object_id) REFERENCES acs_objects(object_id) ON DELETE CASCADE
    "acs_permissions_privilege_fk" FOREIGN KEY (privilege) REFERENCES acs_privileges(privilege) ON DELETE CASCADE

Furthermore, "acs_permissions_on_what_id_fk" constraint does not exist in a default installation.

All the best

Using "on delete cascade" on the permissions table is not a recent change, but was introduced to OpenACS 14 years ago (2005, [1]).

-gn

[1] https://github.com/openacs/openacs-core/blame/2153b958443b2b1f296fa5bf434c338146a38e8f/packages/acs-kernel/sql/postgresql/acs-permissions-create.sql#L404

If you upgrade acs-core to 5.9.1, these constraints are added.