Mercurial > hg > orthanc-databases

annotate PostgreSQL/Plugins/FastTotalSize.sql @ 434:23c7af6f671a pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
DeleteResource does not need the RemainingAncestor table anymore
author Alain Mazy <am@osimis.io>
date Wed, 13 Dec 2023 16:52:06 +0100
parents 8dd29af7c844
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
72
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
1 CREATE TABLE GlobalIntegers(
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
2 key INTEGER PRIMARY KEY,
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
3 value BIGINT);
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
4
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
5 INSERT INTO GlobalIntegers
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
6 SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
7
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
8 INSERT INTO GlobalIntegers
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
9 SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
10
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
11
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
12
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
13 CREATE FUNCTION AttachedFileIncrementSizeFunc()
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
14 RETURNS TRIGGER AS $body$
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
15 BEGIN
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
16 UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
17 UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
18 RETURN NULL;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
19 END;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
20 $body$ LANGUAGE plpgsql;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
21
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
22 CREATE FUNCTION AttachedFileDecrementSizeFunc()
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
23 RETURNS TRIGGER AS $body$
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
24 BEGIN
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
25 UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
26 UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
27 RETURN NULL;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
28 END;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
29 $body$ LANGUAGE plpgsql;
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
30
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
31
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
32
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
33 CREATE TRIGGER AttachedFileIncrementSize
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
34 AFTER INSERT ON AttachedFiles
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
35 FOR EACH ROW
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
36 EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
37
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
38 CREATE TRIGGER AttachedFileDecrementSize
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
39 AFTER DELETE ON AttachedFiles
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
40 FOR EACH ROW
8dd29af7c844 new extension: FastTotalSize
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
diff changeset
41 EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();