Mercurial > hg > orthanc-databases

annotate 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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
432
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
1 -- this script can be used either the first time we create the DB or during an upgrade
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
2 DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
3
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
4 -- The following trigger combines 2 triggers from SQLite:
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
5 -- ResourceDeleted + ResourceDeletedParentCleaning
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
6 CREATE OR REPLACE FUNCTION ResourceDeletedFunc()
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
7 RETURNS TRIGGER AS $body$
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
8 BEGIN
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
9 --RAISE NOTICE 'Delete resource %', old.parentId;
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
10 INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
11
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
12 -- If this resource still has siblings, keep track of the remaining parent
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
13 -- (a parent that must not be deleted but whose LastUpdate must be updated)
433
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
14 INSERT INTO RemainingAncestor SELECT resourceType, publicId
432
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
15 FROM Resources
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
16 WHERE internalId = old.parentId
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
17 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
18 -- If this resource is the latest child, delete the parent
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
19 DELETE FROM Resources WHERE internalId = old.parentId
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
20 AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
21 RETURN NULL;
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
22 END;
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
23 $body$ LANGUAGE plpgsql;
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
24
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
25 CREATE TRIGGER ResourceDeleted
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
26 AFTER DELETE ON Resources
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
27 FOR EACH ROW
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
28 EXECUTE PROCEDURE ResourceDeletedFunc();
433
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
29
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
30 -- we'll now use temporary tables so we need to remove the old tables !
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
31 DROP TABLE IF EXISTS DeletedFiles;
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
32 DROP TABLE IF EXISTS RemainingAncestor;
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
33 DROP TABLE IF EXISTS DeletedResources;