Mercurial > hg > orthanc

changeset 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 517fc4767ae0
children 8336204d95dc
files CMakeLists.txt OrthancServer/InstallTrackAttachmentsSize.sql OrthancServer/SQLiteDatabaseWrapper.cpp OrthancServer/ServerEnumerations.h OrthancServer/main.cpp UnitTestsSources/ServerIndexTests.cpp
diffstat 6 files changed, 64 insertions(+), 15 deletions(-) [+]
line wrap: on
line diff
--- 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)
--- /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;
--- 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<uint64_t>(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<uint64_t>(s.ColumnInt64(0));
   }
--- 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,
--- 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<std::string>(currentVersion) + " to " +
                            boost::lexical_cast<std::string>(ORTHANC_DATABASE_VERSION) +
                            ": Please run Orthanc with the \"--upgrade\" argument");
--- 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(),