Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 435:326f8304daa1 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new creating temporary tables inside functions
author Alain Mazy <am@osimis.io>
date Thu, 14 Dec 2023 09:25:45 +0100
parents 23c7af6f671a
children
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 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
13 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
14 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
15 RETURN NULL;
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
16 END;
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
17 $body$ LANGUAGE plpgsql;
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
18
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
19 CREATE TRIGGER ResourceDeleted
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
20 AFTER DELETE ON Resources
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
21 FOR EACH ROW
8b7c1c423367 new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff changeset
22 EXECUTE PROCEDURE ResourceDeletedFunc();
433
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
23
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
24 -- 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
25 DROP TABLE IF EXISTS DeletedFiles;
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
26 DROP TABLE IF EXISTS RemainingAncestor;
5964ce6385a5 use temporary tables for DeletedFiles, RemainingAncestor and DeletedResources
Alain Mazy <am@osimis.io>
parents: 432
diff changeset
27 DROP TABLE IF EXISTS DeletedResources;
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
28
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
29
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
30 CREATE OR REPLACE FUNCTION DeleteResource(
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
31 IN id BIGINT,
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
32 OUT remainingAncestorResourceType INTEGER,
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
33 OUT remainingAncestorPublicId TEXT) AS $body$
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
34
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
35 DECLARE
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
36 deleted_row RECORD;
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
37
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
38 BEGIN
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
39
435
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
40 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
41
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
42 -- note: temporary tables are created at session (connection) level -> they are likely to exist
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
43 -- these tables are used by the triggers
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
44 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
45 resourceType INTEGER NOT NULL,
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
46 publicId VARCHAR(64) NOT NULL
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
47 );
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
48
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
49 RESET client_min_messages;
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
50
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
51 -- clear the temporary table in case it has been created earlier in the session
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
52 DELETE FROM DeletedResources;
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
53
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
54 -- create/clear the DeletedFiles temporary table
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
55 PERFORM CreateDeletedFilesTemporaryTable();
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
56
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
57
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
58 -- delete the resource itself
434
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
59 DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row;
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
60 -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children +
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
61
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
62 -- If this resource still has siblings, keep track of the remaining parent
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
63 -- (a parent that must not be deleted but whose LastUpdate must be updated)
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
64 SELECT resourceType, publicId INTO remainingAncestorResourceType, remainingAncestorPublicId
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
65 FROM Resources
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
66 WHERE internalId = deleted_row.parentId
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
67 AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId);
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
68
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
69 END;
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
70
23c7af6f671a DeleteResource does not need the RemainingAncestor table anymore
Alain Mazy <am@osimis.io>
parents: 433
diff changeset
71 $body$ LANGUAGE plpgsql;
435
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
72
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
73
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
74 CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable(
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
75 ) RETURNS VOID AS $body$
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
76
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
77 BEGIN
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
78
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
79 SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
80
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
81 -- note: temporary tables are created at session (connection) level -> they are likely to exist
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
82 CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles(
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
83 uuid VARCHAR(64) NOT NULL,
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
84 fileType INTEGER,
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
85 compressedSize BIGINT,
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
86 uncompressedSize BIGINT,
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
87 compressionType INTEGER,
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
88 uncompressedHash VARCHAR(40),
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
89 compressedHash VARCHAR(40)
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
90 );
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
91
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
92 RESET client_min_messages;
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
93
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
94 -- clear the temporary table in case it has been created earlier in the session
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
95 DELETE FROM DeletedFiles;
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
96 END;
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
97
326f8304daa1 new creating temporary tables inside functions
Alain Mazy <am@osimis.io>
parents: 434
diff changeset
98 $body$ LANGUAGE plpgsql;