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