# HG changeset patch # User Sebastien Jodogne # Date 1544799857 -3600 # Node ID e3b5c07146a300fa69234eb3eba84b3d89385044 # Parent 517fc4767ae001d10c16a478b13f8941931e11e0 speeding up the computation of the size of the attachments in SQLite diff -r 517fc4767ae0 -r e3b5c07146a3 CMakeLists.txt --- a/CMakeLists.txt Fri Dec 14 14:55:49 2018 +0100 +++ b/CMakeLists.txt Fri Dec 14 16:04:17 2018 +0100 @@ -168,13 +168,14 @@ ##################################################################### set(ORTHANC_EMBEDDED_FILES - PREPARE_DATABASE ${CMAKE_CURRENT_SOURCE_DIR}/OrthancServer/PrepareDatabase.sql - UPGRADE_DATABASE_3_TO_4 ${CMAKE_CURRENT_SOURCE_DIR}/OrthancServer/Upgrade3To4.sql - UPGRADE_DATABASE_4_TO_5 ${CMAKE_CURRENT_SOURCE_DIR}/OrthancServer/Upgrade4To5.sql - CONFIGURATION_SAMPLE ${CMAKE_CURRENT_SOURCE_DIR}/Resources/Configuration.json - DICOM_CONFORMANCE_STATEMENT ${CMAKE_CURRENT_SOURCE_DIR}/Resources/DicomConformanceStatement.txt - LUA_TOOLBOX ${CMAKE_CURRENT_SOURCE_DIR}/Resources/Toolbox.lua - FONT_UBUNTU_MONO_BOLD_16 ${CMAKE_CURRENT_SOURCE_DIR}/Resources/Fonts/UbuntuMonoBold-16.json + PREPARE_DATABASE ${CMAKE_CURRENT_SOURCE_DIR}/OrthancServer/PrepareDatabase.sql + UPGRADE_DATABASE_3_TO_4 ${CMAKE_CURRENT_SOURCE_DIR}/OrthancServer/Upgrade3To4.sql + UPGRADE_DATABASE_4_TO_5 ${CMAKE_CURRENT_SOURCE_DIR}/OrthancServer/Upgrade4To5.sql + CONFIGURATION_SAMPLE ${CMAKE_CURRENT_SOURCE_DIR}/Resources/Configuration.json + DICOM_CONFORMANCE_STATEMENT ${CMAKE_CURRENT_SOURCE_DIR}/Resources/DicomConformanceStatement.txt + LUA_TOOLBOX ${CMAKE_CURRENT_SOURCE_DIR}/Resources/Toolbox.lua + FONT_UBUNTU_MONO_BOLD_16 ${CMAKE_CURRENT_SOURCE_DIR}/Resources/Fonts/UbuntuMonoBold-16.json + INSTALL_TRACK_ATTACHMENTS_SIZE ${CMAKE_CURRENT_SOURCE_DIR}/OrthancServer/InstallTrackAttachmentsSize.sql ) if (STANDALONE_BUILD) diff -r 517fc4767ae0 -r e3b5c07146a3 OrthancServer/InstallTrackAttachmentsSize.sql --- /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; diff -r 517fc4767ae0 -r e3b5c07146a3 OrthancServer/SQLiteDatabaseWrapper.cpp --- a/OrthancServer/SQLiteDatabaseWrapper.cpp Fri Dec 14 14:55:49 2018 +0100 +++ b/OrthancServer/SQLiteDatabaseWrapper.cpp Fri Dec 14 16:04:17 2018 +0100 @@ -370,6 +370,19 @@ "Incompatible version of the Orthanc database: " + tmp); } + // New in Orthanc 1.5.1 + if (version_ == 6) + { + if (!LookupGlobalProperty(tmp, GlobalProperty_DatabaseTracksSizeOfAttachments) || + tmp != "1") + { + LOG(INFO) << "Installing the SQLite triggers to track the size of the attachments"; + std::string query; + EmbeddedResources::GetFileResource(query, EmbeddedResources::INSTALL_TRACK_ATTACHMENTS_SIZE); + db_.Execute(query); + } + } + signalRemainingAncestor_ = new Internals::SignalRemainingAncestor; db_.Register(signalRemainingAncestor_); } @@ -890,7 +903,10 @@ uint64_t SQLiteDatabaseWrapper::GetTotalCompressedSize() { - SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(compressedSize) FROM AttachedFiles"); + // Old SQL query that was used in Orthanc <= 1.5.0: + // SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(compressedSize) FROM AttachedFiles"); + + SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT value FROM GlobalIntegers WHERE key=0"); s.Run(); return static_cast(s.ColumnInt64(0)); } @@ -898,7 +914,10 @@ uint64_t SQLiteDatabaseWrapper::GetTotalUncompressedSize() { - SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(uncompressedSize) FROM AttachedFiles"); + // Old SQL query that was used in Orthanc <= 1.5.0: + // SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT SUM(uncompressedSize) FROM AttachedFiles"); + + SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT value FROM GlobalIntegers WHERE key=1"); s.Run(); return static_cast(s.ColumnInt64(0)); } diff -r 517fc4767ae0 -r e3b5c07146a3 OrthancServer/ServerEnumerations.h --- a/OrthancServer/ServerEnumerations.h Fri Dec 14 14:55:49 2018 +0100 +++ b/OrthancServer/ServerEnumerations.h Fri Dec 14 16:04:17 2018 +0100 @@ -93,10 +93,9 @@ GlobalProperty_FlushSleep = 2, GlobalProperty_AnonymizationSequence = 3, GlobalProperty_JobsRegistry = 5, - GlobalProperty_TotalCompressedSize = 6, // Reserved for Orthanc > 1.5.0 - GlobalProperty_TotalUncompressedSize = 7, // Reserved for Orthanc > 1.5.0 - GlobalProperty_Modalities = 20, // New in Orthanc 1.5.0 - GlobalProperty_Peers = 21, // New in Orthanc 1.5.0 + GlobalProperty_DatabaseTracksSizeOfAttachments = 6, // New in Orthanc 1.5.1 + GlobalProperty_Modalities = 20, // New in Orthanc 1.5.0 + GlobalProperty_Peers = 21, // New in Orthanc 1.5.0 // Reserved values for internal use by the database plugins GlobalProperty_DatabasePatchLevel = 4, diff -r 517fc4767ae0 -r e3b5c07146a3 OrthancServer/main.cpp --- a/OrthancServer/main.cpp Fri Dec 14 14:55:49 2018 +0100 +++ b/OrthancServer/main.cpp Fri Dec 14 16:04:17 2018 +0100 @@ -1174,7 +1174,7 @@ else if (currentVersion != ORTHANC_DATABASE_VERSION) { throw OrthancException(ErrorCode_IncompatibleDatabaseVersion, - "The database schema must be changed from version " + + "The database schema must be upgraded from version " + boost::lexical_cast(currentVersion) + " to " + boost::lexical_cast(ORTHANC_DATABASE_VERSION) + ": Please run Orthanc with the \"--upgrade\" argument"); diff -r 517fc4767ae0 -r e3b5c07146a3 UnitTestsSources/ServerIndexTests.cpp --- a/UnitTestsSources/ServerIndexTests.cpp Fri Dec 14 14:55:49 2018 +0100 +++ b/UnitTestsSources/ServerIndexTests.cpp Fri Dec 14 16:04:17 2018 +0100 @@ -464,7 +464,15 @@ CheckTableRecordCount(0, "Resources"); CheckTableRecordCount(0, "AttachedFiles"); - CheckTableRecordCount(2, "GlobalProperties"); + CheckTableRecordCount(3, "GlobalProperties"); + + std::string tmp; + ASSERT_TRUE(index_->LookupGlobalProperty(tmp, GlobalProperty_DatabaseSchemaVersion)); + ASSERT_EQ("6", tmp); + ASSERT_TRUE(index_->LookupGlobalProperty(tmp, GlobalProperty_FlushSleep)); + ASSERT_EQ("World", tmp); + ASSERT_TRUE(index_->LookupGlobalProperty(tmp, GlobalProperty_DatabaseTracksSizeOfAttachments)); + ASSERT_EQ("1", tmp); ASSERT_EQ(3u, listener_->deletedFiles_.size()); ASSERT_FALSE(std::find(listener_->deletedFiles_.begin(),