Mercurial > hg > orthanc

changeset 1158:badc14fee61f db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
speed up db
author Sebastien Jodogne <s.jodogne@gmail.com>
date Tue, 16 Sep 2014 18:01:42 +0200
parents 22ef457b7985
children 8f9d49192815
files CMakeLists.txt OrthancServer/DatabaseWrapper.cpp OrthancServer/IServerIndexListener.h OrthancServer/PrepareDatabase.sql OrthancServer/ServerIndex.cpp OrthancServer/Upgrade4To5.sql
diffstat 6 files changed, 137 insertions(+), 8 deletions(-) [+]
line wrap: on
line diff
--- a/CMakeLists.txt	Tue Sep 16 14:22:22 2014 +0200
+++ b/CMakeLists.txt	Tue Sep 16 18:01:42 2014 +0200
@@ -264,6 +264,7 @@
 set(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
--- a/OrthancServer/DatabaseWrapper.cpp	Tue Sep 16 14:22:22 2014 +0200
+++ b/OrthancServer/DatabaseWrapper.cpp	Tue Sep 16 18:01:42 2014 +0200
@@ -92,6 +92,34 @@
       }
     };
 
+    class SignalResourceDeleted : public SQLite::IScalarFunction
+    {
+    private:
+      IServerIndexListener& listener_;
+
+    public:
+      SignalResourceDeleted(IServerIndexListener& listener) :
+        listener_(listener)
+      {
+      }
+
+      virtual const char* GetName() const
+      {
+        return "SignalResourceDeleted";
+      }
+
+      virtual unsigned int GetCardinality() const
+      {
+        return 2;
+      }
+
+      virtual void Compute(SQLite::FunctionContext& context)
+      {
+        ResourceType type = static_cast<ResourceType>(context.GetIntValue(1));
+        listener_.SignalResourceDeleted(type, context.GetStringValue(0));
+      }
+    };
+
     class SignalRemainingAncestor : public SQLite::IScalarFunction
     {
     private:
@@ -852,11 +880,12 @@
       /**
        * History of the database versions:
        *  - Version 3: from Orthanc 0.3.2 to Orthanc 0.7.2 (inclusive)
-       *  - Version 4: from Orthanc 0.7.3 (inclusive)
+       *  - Version 4: from Orthanc 0.7.3 to Orthanc 0.8.3 (inclusive)
+       *  - Version 5: from Orthanc 0.8.4 (inclusive)
        **/
 
-      // This version of Orthanc is only compatible with versions 3 of 4 of the DB schema
-      ok = (v == 3 || v == 4);
+      // This version of Orthanc is only compatible with versions 3, 4 and 5 of the DB schema
+      ok = (v == 3 || v == 4 || v == 5);
 
       if (v == 3)
       {
@@ -866,6 +895,18 @@
         db_.BeginTransaction();
         db_.Execute(upgrade);
         db_.CommitTransaction();
+        v = 4;
+      }
+
+      if (v == 4)
+      {
+        LOG(WARNING) << "Upgrading database version from 4 to 5";
+        std::string upgrade;
+        EmbeddedResources::GetFileResource(upgrade, EmbeddedResources::UPGRADE_DATABASE_4_TO_5);
+        db_.BeginTransaction();
+        db_.Execute(upgrade);
+        db_.CommitTransaction();
+        v = 5;
       }
     }
     catch (boost::bad_lexical_cast&)
@@ -881,6 +922,7 @@
     signalRemainingAncestor_ = new Internals::SignalRemainingAncestor;
     db_.Register(signalRemainingAncestor_);
     db_.Register(new Internals::SignalFileDeleted(listener_));
+    db_.Register(new Internals::SignalResourceDeleted(listener_));
   }
 
   uint64_t DatabaseWrapper::GetResourceCount(ResourceType resourceType)
--- a/OrthancServer/IServerIndexListener.h	Tue Sep 16 14:22:22 2014 +0200
+++ b/OrthancServer/IServerIndexListener.h	Tue Sep 16 18:01:42 2014 +0200
@@ -47,6 +47,9 @@
     virtual void SignalRemainingAncestor(ResourceType parentType,
                                          const std::string& publicId) = 0;
 
+    virtual void SignalResourceDeleted(ResourceType type,
+                                       const std::string& publicId) = 0;
+
     virtual void SignalFileDeleted(const FileInfo& info) = 0;
   };
 }
--- a/OrthancServer/PrepareDatabase.sql	Tue Sep 16 14:22:22 2014 +0200
+++ b/OrthancServer/PrepareDatabase.sql	Tue Sep 16 18:01:42 2014 +0200
@@ -10,7 +10,15 @@
        parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE
        );
 
-CREATE TABLE MainDicomTags(
+CREATE TABLE MainResourcesTags(
+       id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
+       tagGroup INTEGER,
+       tagElement INTEGER,
+       value TEXT,
+       PRIMARY KEY(id, tagGroup, tagElement)
+       );
+
+CREATE TABLE MainInstancesTags(
        id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
        tagGroup INTEGER,
        tagElement INTEGER,
@@ -67,9 +75,10 @@
 CREATE INDEX ResourceTypeIndex ON Resources(resourceType);
 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
 
-CREATE INDEX MainDicomTagsIndex1 ON MainDicomTags(id);
-CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement);
-CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY);
+CREATE INDEX MainResourcesTagsIndex1 ON MainResourcesTags(id);
+CREATE INDEX MainResourcesTagsIndex2 ON MainResourcesTags(tagGroup, tagElement);
+CREATE INDEX MainResourcesTagsIndexValues ON MainResourcesTags(value COLLATE BINARY);
+CREATE INDEX MainInstancesTagsIndex ON MainInstancesTags(id);
 
 CREATE INDEX ChangesIndex ON Changes(internalId);
 
@@ -85,6 +94,7 @@
 CREATE TRIGGER ResourceDeleted
 AFTER DELETE ON Resources
 BEGIN
+  SELECT SignalResourceDeleted(old.publicId, old.resourceType);  -- New in Orthanc 0.8.4 (db v5)
   SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) 
     FROM Resources AS parent WHERE internalId = old.parentId;
 END;
@@ -107,4 +117,4 @@
 
 -- Set the version of the database schema
 -- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration
-INSERT INTO GlobalProperties VALUES (1, "4");
+INSERT INTO GlobalProperties VALUES (1, "5");
--- a/OrthancServer/ServerIndex.cpp	Tue Sep 16 14:22:22 2014 +0200
+++ b/OrthancServer/ServerIndex.cpp	Tue Sep 16 18:01:42 2014 +0200
@@ -137,6 +137,13 @@
         sizeOfFilesToRemove_ += info.GetCompressedSize();
       }
 
+      virtual void SignalResourceDeleted(ResourceType type,
+                                         const std::string& publicId)
+      {
+        LOG(INFO) << "Resource " << publicId << " of type " << EnumerationToString(type) << " is deleted";
+      }
+
+
       bool HasRemainingLevel() const
       {
         return hasRemainingLevel_;
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/OrthancServer/Upgrade4To5.sql	Tue Sep 16 18:01:42 2014 +0200
@@ -0,0 +1,66 @@
+-- This SQLite script updates the version of the Orthanc database from 4 to 5.
+
+
+CREATE TABLE MainResourcesTags(
+       id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
+       tagGroup INTEGER,
+       tagElement INTEGER,
+       value TEXT,
+       PRIMARY KEY(id, tagGroup, tagElement)
+       );
+
+CREATE TABLE MainInstancesTags(
+       id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
+       tagGroup INTEGER,
+       tagElement INTEGER,
+       value TEXT,
+       PRIMARY KEY(id, tagGroup, tagElement)
+       );
+
+CREATE INDEX MainResourcesTagsIndex1 ON MainResourcesTags(id);
+CREATE INDEX MainResourcesTagsIndex2 ON MainResourcesTags(tagGroup, tagElement);
+CREATE INDEX MainResourcesTagsIndexValues ON MainResourcesTags(value COLLATE BINARY);
+CREATE INDEX MainInstancesTagsIndex ON MainInstancesTags(id);
+
+
+-- Migrate data from MainDicomTags to MainResourcesTags and MainInstancesTags
+-- Below, the value "4" corresponds to "ResourceType_Instance".
+-- The "8" and "24" correspond to SOPInstanceUID (0x0008, 0x0018)
+
+INSERT INTO MainResourcesTags SELECT MainDicomTags.* FROM MainDicomTags
+       INNER JOIN Resources ON Resources.internalId = MainDicomTags.id
+       WHERE (Resources.resourceType != 4 OR
+              (MainDicomTags.tagGroup = 8 AND
+               MainDicomTags.tagElement = 24));
+
+INSERT INTO MainInstancesTags SELECT MainDicomTags.* FROM MainDicomTags
+       INNER JOIN Resources ON Resources.internalId = MainDicomTags.id
+       WHERE (Resources.resourceType = 4 AND
+              (MainDicomTags.tagGroup != 8 OR
+               MainDicomTags.tagElement != 24));
+
+-- Remove the MainDicomTags table
+
+DROP INDEX MainDicomTagsIndex1;
+DROP INDEX MainDicomTagsIndex2;
+DROP INDEX MainDicomTagsIndexValues;
+DROP TABLE MainDicomTags;
+
+
+-- Upgrade the "ResourceDeleted" trigger
+
+DROP TRIGGER ResourceDeleted;
+
+CREATE TRIGGER ResourceDeleted
+AFTER DELETE ON Resources
+BEGIN
+  SELECT SignalResourceDeleted(old.publicId, old.resourceType);
+  SELECT SignalRemainingAncestor(parent.publicId, parent.resourceType) 
+    FROM Resources AS parent WHERE internalId = old.parentId;
+END;
+
+
+-- Change the database version
+-- The "1" corresponds to the "GlobalProperty_DatabaseSchemaVersion" enumeration
+
+UPDATE GlobalProperties SET value="5" WHERE property=1;