Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 433:5964ce6385a5 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
author Alain Mazy <am@osimis.io>
date Wed, 13 Dec 2023 15:48:56 +0100
parents 8b7c1c423367
children 23c7af6f671a
comparison
equal deleted inserted replaced
432:8b7c1c423367 433:5964ce6385a5
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 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) 13 -- (a parent that must not be deleted but whose LastUpdate must be updated)
14 INSERT INTO RemainingAncestor SELECT resourceType, publicId 14 INSERT INTO RemainingAncestor SELECT resourceType, publicId
15 FROM Resources 15 FROM Resources
16 WHERE internalId = old.parentId 16 WHERE internalId = old.parentId
17 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); 17 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
18 -- If this resource is the latest child, delete the parent 18 -- If this resource is the latest child, delete the parent
19 DELETE FROM Resources WHERE internalId = old.parentId 19 DELETE FROM Resources WHERE internalId = old.parentId
24 24
25 CREATE TRIGGER ResourceDeleted 25 CREATE TRIGGER ResourceDeleted
26 AFTER DELETE ON Resources 26 AFTER DELETE ON Resources
27 FOR EACH ROW 27 FOR EACH ROW
28 EXECUTE PROCEDURE ResourceDeletedFunc(); 28 EXECUTE PROCEDURE ResourceDeletedFunc();
29
30 -- we'll now use temporary tables so we need to remove the old tables !
31 DROP TABLE IF EXISTS DeletedFiles;
32 DROP TABLE IF EXISTS RemainingAncestor;
33 DROP TABLE IF EXISTS DeletedResources;