Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 434:23c7af6f671a pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
DeleteResource does not need the RemainingAncestor table anymore
author Alain Mazy <am@osimis.io>
date Wed, 13 Dec 2023 16:52:06 +0100
parents 5964ce6385a5
children 326f8304daa1
comparison
equal deleted inserted replaced
433:5964ce6385a5 434:23c7af6f671a
7 RETURNS TRIGGER AS $body$ 7 RETURNS TRIGGER AS $body$
8 BEGIN 8 BEGIN
9 --RAISE NOTICE 'Delete resource %', old.parentId; 9 --RAISE NOTICE 'Delete resource %', old.parentId;
10 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); 10 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
11 11
12 -- If this resource still has siblings, keep track of the remaining parent
13 -- (a parent that must not be deleted but whose LastUpdate must be updated)
14 INSERT INTO RemainingAncestor SELECT resourceType, publicId
15 FROM Resources
16 WHERE internalId = old.parentId
17 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
18 -- If this resource is the latest child, delete the parent 12 -- If this resource is the latest child, delete the parent
19 DELETE FROM Resources WHERE internalId = old.parentId 13 DELETE FROM Resources WHERE internalId = old.parentId
20 AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); 14 AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
21 RETURN NULL; 15 RETURN NULL;
22 END; 16 END;
29 23
30 -- we'll now use temporary tables so we need to remove the old tables ! 24 -- we'll now use temporary tables so we need to remove the old tables !
31 DROP TABLE IF EXISTS DeletedFiles; 25 DROP TABLE IF EXISTS DeletedFiles;
32 DROP TABLE IF EXISTS RemainingAncestor; 26 DROP TABLE IF EXISTS RemainingAncestor;
33 DROP TABLE IF EXISTS DeletedResources; 27 DROP TABLE IF EXISTS DeletedResources;
28
29
30 CREATE OR REPLACE FUNCTION DeleteResource(
31 IN id BIGINT,
32 OUT remainingAncestorResourceType INTEGER,
33 OUT remainingAncestorPublicId TEXT) AS $body$
34
35 DECLARE
36 deleted_row RECORD;
37
38 BEGIN
39
40 DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row;
41 -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children +
42
43 -- If this resource still has siblings, keep track of the remaining parent
44 -- (a parent that must not be deleted but whose LastUpdate must be updated)
45 SELECT resourceType, publicId INTO remainingAncestorResourceType, remainingAncestorPublicId
46 FROM Resources
47 WHERE internalId = deleted_row.parentId
48 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId);
49
50 END;
51
52 $body$ LANGUAGE plpgsql;