Mercurial > hg > orthanc-databases
view PostgreSQL/Plugins/FastTotalSize.sql @ 161:2ccde9c7311b optimized-routes
added new optimized REST routes. this is a temporary work to try to speed up some routes (used by LRO). This way, we avoid another app to access the Orthanc DB and we skip the plugin SDK update for a very specific route
author | Alain Mazy <alain@mazy.be> |
---|---|
date | Fri, 10 Jul 2020 13:26:47 +0200 (2020-07-10) |
parents | 8dd29af7c844 |
children |
line wrap: on
line source
CREATE TABLE GlobalIntegers( key INTEGER PRIMARY KEY, value BIGINT); INSERT INTO GlobalIntegers SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles; INSERT INTO GlobalIntegers SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles; CREATE FUNCTION AttachedFileIncrementSizeFunc() RETURNS TRIGGER AS $body$ BEGIN UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0; UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1; RETURN NULL; END; $body$ LANGUAGE plpgsql; CREATE FUNCTION AttachedFileDecrementSizeFunc() RETURNS TRIGGER AS $body$ BEGIN UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0; UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1; RETURN NULL; END; $body$ LANGUAGE plpgsql; CREATE TRIGGER AttachedFileIncrementSize AFTER INSERT ON AttachedFiles FOR EACH ROW EXECUTE PROCEDURE AttachedFileIncrementSizeFunc(); CREATE TRIGGER AttachedFileDecrementSize AFTER DELETE ON AttachedFiles FOR EACH ROW EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();