Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 434:23c7af6f671a pg-transactions
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; |