Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 436:f16faa1fdc46 pg-transactions
InsertOrUpdateMetadata function + UpdateAndGetStatistics
author | Alain Mazy <am@osimis.io> |
---|---|
date | Fri, 15 Dec 2023 17:11:26 +0100 |
parents | 326f8304daa1 |
children |
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; |