Mercurial > hg > orthanc
diff OrthancServer/InstallTrackAttachmentsSize.sql @ 3018:e3b5c07146a3 db-changes
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 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/OrthancServer/InstallTrackAttachmentsSize.sql Fri Dec 14 16:04:17 2018 +0100 @@ -0,0 +1,22 @@ +CREATE TABLE GlobalIntegers( + key INTEGER PRIMARY KEY, + value INTEGER); + +INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_DatabaseTracksSizeOfAttachments + +INSERT INTO GlobalIntegers SELECT 0, IFNULL(SUM(compressedSize), 0) FROM AttachedFiles; +INSERT INTO GlobalIntegers SELECT 1, IFNULL(SUM(uncompressedSize), 0) FROM AttachedFiles; + +CREATE TRIGGER AttachedFileIncrementSize +AFTER INSERT ON AttachedFiles +BEGIN + UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0; + UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1; +END; + +CREATE TRIGGER AttachedFileDecrementSize +AFTER DELETE ON AttachedFiles +BEGIN + UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0; + UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1; +END;