Mercurial > hg > orthanc-databases
comparison PostgreSQL/Plugins/FastTotalSize.sql @ 72:8dd29af7c844 db-changes
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(); |