Mercurial > hg > orthanc-databases

annotate 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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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();