Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/ResourceDeletedFunc.sql @ 432:8b7c1c423367 pg-transactions
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
author | Alain Mazy <am@osimis.io> |
---|---|
date | Mon, 11 Dec 2023 14:39:27 +0100 |
parents | |
children | 5964ce6385a5 |
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) |
8b7c1c423367
new 'TransactionMode' config + rewrote ResourceDeletedFunc to avoid IF/THEN/ELSE pattern
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
14 INSERT INTO RemainingAncestor SELECT resourceType, publicId |
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(); |