Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/FastTotalStats2.sql @ 436:f16faa1fdc46 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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();