Mercurial > hg > orthanc-databases

comparison PostgreSQL/Plugins/FastTotalSize.sql @ 72:8dd29af7c844 db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
new extension: FastTotalSize
author Sebastien Jodogne <s.jodogne@gmail.com>
date Fri, 04 Jan 2019 14:43:35 +0100
parents
children
comparison
equal deleted inserted replaced
71:d40c5fecd160 72:8dd29af7c844
1 CREATE TABLE GlobalIntegers(
2 key INTEGER PRIMARY KEY,
3 value BIGINT);
4
5 INSERT INTO GlobalIntegers
6 SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles;
7
8 INSERT INTO GlobalIntegers
9 SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles;
10
11
12
13 CREATE FUNCTION AttachedFileIncrementSizeFunc()
14 RETURNS TRIGGER AS $body$
15 BEGIN
16 UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0;
17 UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1;
18 RETURN NULL;
19 END;
20 $body$ LANGUAGE plpgsql;
21
22 CREATE FUNCTION AttachedFileDecrementSizeFunc()
23 RETURNS TRIGGER AS $body$
24 BEGIN
25 UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0;
26 UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1;
27 RETURN NULL;
28 END;
29 $body$ LANGUAGE plpgsql;
30
31
32
33 CREATE TRIGGER AttachedFileIncrementSize
34 AFTER INSERT ON AttachedFiles
35 FOR EACH ROW
36 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
37
38 CREATE TRIGGER AttachedFileDecrementSize
39 AFTER DELETE ON AttachedFiles
40 FOR EACH ROW
41 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();