Mercurial > hg > orthanc

comparison OrthancServer/InstallTrackAttachmentsSize.sql @ 3018:e3b5c07146a3 db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
speeding up the computation of the size of the attachments in SQLite
author Sebastien Jodogne <s.jodogne@gmail.com>
date Fri, 14 Dec 2018 16:04:17 +0100
parents
children 8336204d95dc
comparison
equal deleted inserted replaced
3017:517fc4767ae0 3018:e3b5c07146a3
1 CREATE TABLE GlobalIntegers(
2 key INTEGER PRIMARY KEY,
3 value INTEGER);
4
5 INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_DatabaseTracksSizeOfAttachments
6
7 INSERT INTO GlobalIntegers SELECT 0, IFNULL(SUM(compressedSize), 0) FROM AttachedFiles;
8 INSERT INTO GlobalIntegers SELECT 1, IFNULL(SUM(uncompressedSize), 0) FROM AttachedFiles;
9
10 CREATE TRIGGER AttachedFileIncrementSize
11 AFTER INSERT ON AttachedFiles
12 BEGIN
13 UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0;
14 UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1;
15 END;
16
17 CREATE TRIGGER AttachedFileDecrementSize
18 AFTER DELETE ON AttachedFiles
19 BEGIN
20 UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0;
21 UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1;
22 END;