Mercurial > hg > orthanc-databases
annotate PostgreSQL/Plugins/FastTotalStats2.sql @ 436:f16faa1fdc46 pg-transactions
InsertOrUpdateMetadata function + UpdateAndGetStatistics
author | Alain Mazy <am@osimis.io> |
---|---|
date | Fri, 15 Dec 2023 17:11:26 +0100 |
parents | |
children |
rev | line source |
---|---|
436
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
1 -- uninstall FastTotalSize & FastCountResources |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
2 DROP TRIGGER IF EXISTS AttachedFileIncrementSize ON AttachedFiles; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
3 DROP TRIGGER IF EXISTS AttachedFileDecrementSize ON AttachedFiles; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
4 DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
5 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
6 -- this table stores all changes that needs to be performed to the GlobalIntegers table |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
7 -- This way, each transaction can add row independently in this table without having to lock |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
8 -- any row (which was the case with previous FastTotalSize). |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
9 -- These changes will be applied at regular interval by an external thread or when someone |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
10 -- requests the statistics |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
11 CREATE TABLE IF NOT EXISTS GlobalIntegersChanges( |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
12 key INTEGER, |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
13 value BIGINT); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
14 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
15 CREATE OR REPLACE FUNCTION UpdateSingleStatistic( |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
16 IN statisticsKey INTEGER, |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
17 OUT newValue BIGINT |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
18 ) AS $body$ |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
19 BEGIN |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
20 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
21 -- Delete the current changes, sum them and update the GlobalIntegers row. |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
22 -- New rows can be added in the meantime, they won't be deleted or summed. |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
23 WITH deleted_rows AS ( |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
24 DELETE FROM GlobalIntegersChanges |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
25 WHERE GlobalIntegersChanges.key = statisticsKey |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
26 RETURNING value |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
27 ) |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
28 UPDATE GlobalIntegers |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
29 SET value = value + ( |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
30 SELECT COALESCE(SUM(value), 0) |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
31 FROM deleted_rows |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
32 ) |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
33 WHERE GlobalIntegers.key = statisticsKey |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
34 RETURNING value INTO newValue; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
35 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
36 END; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
37 $body$ LANGUAGE plpgsql; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
38 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
39 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
40 CREATE OR REPLACE FUNCTION UpdateStatistics( |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
41 OUT patientsCount BIGINT, |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
42 OUT studiesCount BIGINT, |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
43 OUT seriesCount BIGINT, |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
44 OUT instancesCount BIGINT, |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
45 OUT totalCompressedSize BIGINT, |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
46 OUT totalUncompressedSize BIGINT |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
47 ) AS $body$ |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
48 BEGIN |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
49 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
50 SELECT UpdateSingleStatistic(0) INTO totalCompressedSize; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
51 SELECT UpdateSingleStatistic(1) INTO totalUncompressedSize; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
52 SELECT UpdateSingleStatistic(2) INTO patientsCount; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
53 SELECT UpdateSingleStatistic(3) INTO studiesCount; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
54 SELECT UpdateSingleStatistic(4) INTO seriesCount; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
55 SELECT UpdateSingleStatistic(5) INTO instancesCount; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
56 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
57 END; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
58 $body$ LANGUAGE plpgsql; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
59 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
60 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
61 CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc() |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
62 RETURNS TRIGGER AS $$ |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
63 BEGIN |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
64 INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
65 RETURN NULL; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
66 END; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
67 $$ LANGUAGE plpgsql; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
68 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
69 CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc() |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
70 RETURNS TRIGGER AS $$ |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
71 BEGIN |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
72 INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
73 RETURN NULL; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
74 END; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
75 $$ LANGUAGE plpgsql; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
76 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
77 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
78 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc() |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
79 RETURNS TRIGGER AS $body$ |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
80 BEGIN |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
81 INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
82 INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
83 RETURN NULL; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
84 END; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
85 $body$ LANGUAGE plpgsql; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
86 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
87 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
88 RETURNS TRIGGER AS $body$ |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
89 BEGIN |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
90 INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
91 INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
92 RETURN NULL; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
93 END; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
94 $body$ LANGUAGE plpgsql; |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
95 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
96 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
97 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
98 CREATE TRIGGER AttachedFileIncrementSize |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
99 AFTER INSERT ON AttachedFiles |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
100 FOR EACH ROW |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
101 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc(); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
102 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
103 CREATE TRIGGER AttachedFileDecrementSize |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
104 AFTER DELETE ON AttachedFiles |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
105 FOR EACH ROW |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
106 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc(); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
107 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
108 CREATE TRIGGER IncrementResourcesTracker |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
109 AFTER INSERT ON Resources |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
110 FOR EACH ROW |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
111 EXECUTE PROCEDURE IncrementResourcesTrackerFunc(); |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
112 |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
113 CREATE TRIGGER DecrementResourcesTracker |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
114 AFTER DELETE ON Resources |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
115 FOR EACH ROW |
f16faa1fdc46
InsertOrUpdateMetadata function + UpdateAndGetStatistics
Alain Mazy <am@osimis.io>
parents:
diff
changeset
|
116 EXECUTE PROCEDURE DecrementResourcesTrackerFunc(); |