Mercurial > hg > orthanc

changeset 1159:8f9d49192815 db-changes

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
speeding up db schema
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 17 Sep 2014 10:25:41 +0200
parents badc14fee61f
children 82cbf1480aac
files OrthancServer/PrepareDatabase.sql OrthancServer/Upgrade4To5.sql UnitTestsSources/ServerIndexTests.cpp
diffstat 3 files changed, 64 insertions(+), 39 deletions(-) [+]
line wrap: on
line diff
--- a/OrthancServer/PrepareDatabase.sql	Tue Sep 16 18:01:42 2014 +0200
+++ b/OrthancServer/PrepareDatabase.sql	Wed Sep 17 10:25:41 2014 +0200
@@ -10,7 +10,7 @@
        parentId INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE
        );
 
-CREATE TABLE MainResourcesTags(
+CREATE TABLE MainDicomTags(
        id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
        tagGroup INTEGER,
        tagElement INTEGER,
@@ -18,7 +18,8 @@
        PRIMARY KEY(id, tagGroup, tagElement)
        );
 
-CREATE TABLE MainInstancesTags(
+-- The following table was added in Orthanc 0.8.4 (database v5)
+CREATE TABLE DicomIdentifiers(
        id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
        tagGroup INTEGER,
        tagElement INTEGER,
@@ -75,10 +76,15 @@
 CREATE INDEX ResourceTypeIndex ON Resources(resourceType);
 CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
 
-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 MainDicomTagsIndex1 ON MainDicomTags(id);
+-- The 2 following indexes were removed in Orthanc 0.8.4 (database v5), to speed up
+-- CREATE INDEX MainDicomTagsIndex2 ON MainDicomTags(tagGroup, tagElement);
+-- CREATE INDEX MainDicomTagsIndexValues ON MainDicomTags(value COLLATE BINARY);
+
+-- The 3 following indexes were added in Orthanc 0.8.4 (database v5)
+CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
+CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
+CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY);
 
 CREATE INDEX ChangesIndex ON Changes(internalId);
 
--- a/OrthancServer/Upgrade4To5.sql	Tue Sep 16 18:01:42 2014 +0200
+++ b/OrthancServer/Upgrade4To5.sql	Wed Sep 17 10:25:41 2014 +0200
@@ -1,15 +1,15 @@
 -- 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)
-       );
+-- Remove 2 indexes to speed up
+
+DROP INDEX MainDicomTagsIndex2;
+DROP INDEX MainDicomTagsIndexValues;
 
-CREATE TABLE MainInstancesTags(
+
+-- Add a new table to index the DICOM identifiers
+
+CREATE TABLE DicomIdentifiers(
        id INTEGER REFERENCES Resources(internalId) ON DELETE CASCADE,
        tagGroup INTEGER,
        tagElement INTEGER,
@@ -17,34 +17,26 @@
        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);
+CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
+CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
+CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value COLLATE BINARY);
 
 
 -- 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));
+INSERT INTO DicomIdentifiers SELECT * FROM MainDicomTags
+       WHERE ((tagGroup = 16 AND tagElement = 32) OR  -- PatientID (0x0010, 0x0020)
+              (tagGroup = 32 AND tagElement = 13) OR  -- StudyInstanceUID (0x0020, 0x000d)
+              (tagGroup = 8  AND tagElement = 80) OR  -- AccessionNumber (0x0008, 0x0050)
+              (tagGroup = 32 AND tagElement = 14) OR  -- SeriesInstanceUID (0x0020, 0x000e)
+              (tagGroup = 8  AND tagElement = 24));   -- SOPInstanceUID (0x0008, 0x0018)
 
--- Remove the MainDicomTags table
-
-DROP INDEX MainDicomTagsIndex1;
-DROP INDEX MainDicomTagsIndex2;
-DROP INDEX MainDicomTagsIndexValues;
-DROP TABLE MainDicomTags;
+DELETE FROM MainDicomTags
+       WHERE ((tagGroup = 16 AND tagElement = 32) OR  -- PatientID (0x0010, 0x0020)
+              (tagGroup = 32 AND tagElement = 13) OR  -- StudyInstanceUID (0x0020, 0x000d)
+              (tagGroup = 8  AND tagElement = 80) OR  -- AccessionNumber (0x0008, 0x0050)
+              (tagGroup = 32 AND tagElement = 14) OR  -- SeriesInstanceUID (0x0020, 0x000e)
+              (tagGroup = 8  AND tagElement = 24));   -- SOPInstanceUID (0x0008, 0x0018)
 
 
 -- Upgrade the "ResourceDeleted" trigger
--- a/UnitTestsSources/ServerIndexTests.cpp	Tue Sep 16 18:01:42 2014 +0200
+++ b/UnitTestsSources/ServerIndexTests.cpp	Wed Sep 17 10:25:41 2014 +0200
@@ -58,6 +58,7 @@
   {
   public:
     std::vector<std::string> deletedFiles_;
+    std::vector<std::string> deletedResources_;
     std::string ancestorId_;
     ResourceType ancestorType_;
 
@@ -79,7 +80,15 @@
       const std::string fileUuid = info.GetUuid();
       deletedFiles_.push_back(fileUuid);
       LOG(INFO) << "A file must be removed: " << fileUuid;
-    }                                
+    }       
+
+    virtual void SignalResourceDeleted(ResourceType type,
+                                       const std::string& publicId)
+    {
+      deletedResources_.push_back(publicId);
+      LOG(INFO) << "A resource was removed: " << publicId;
+    }
+                         
   };
 
 
@@ -281,12 +290,14 @@
   ASSERT_EQ(CompressionType_None, att.GetCompressionType());
 
   ASSERT_EQ(0u, listener_->deletedFiles_.size());
+  ASSERT_EQ(0u, listener_->deletedResources_.size());
   ASSERT_EQ(7u, index_->GetTableRecordCount("Resources")); 
   ASSERT_EQ(3u, index_->GetTableRecordCount("AttachedFiles"));
   ASSERT_EQ(1u, index_->GetTableRecordCount("Metadata"));
   ASSERT_EQ(1u, index_->GetTableRecordCount("MainDicomTags"));
+
   index_->DeleteResource(a[0]);
-
+  ASSERT_EQ(5u, listener_->deletedResources_.size());
   ASSERT_EQ(2u, listener_->deletedFiles_.size());
   ASSERT_FALSE(std::find(listener_->deletedFiles_.begin(), 
                          listener_->deletedFiles_.end(),
@@ -300,6 +311,7 @@
   ASSERT_EQ(1u, index_->GetTableRecordCount("AttachedFiles"));
   ASSERT_EQ(0u, index_->GetTableRecordCount("MainDicomTags"));
   index_->DeleteResource(a[5]);
+  ASSERT_EQ(7u, listener_->deletedResources_.size());
   ASSERT_EQ(0u, index_->GetTableRecordCount("Resources"));
   ASSERT_EQ(0u, index_->GetTableRecordCount("AttachedFiles"));
   ASSERT_EQ(2u, index_->GetTableRecordCount("GlobalProperties"));
@@ -395,9 +407,11 @@
   ASSERT_EQ(10u, index_->GetTableRecordCount("PatientRecyclingOrder")); 
 
   listener_->Reset();
+  ASSERT_EQ(0u, listener_->deletedResources_.size());
 
   index_->DeleteResource(patients[5]);
   index_->DeleteResource(patients[0]);
+  ASSERT_EQ(2u, listener_->deletedResources_.size());
   ASSERT_EQ(8u, index_->GetTableRecordCount("Resources")); 
   ASSERT_EQ(8u, index_->GetTableRecordCount("PatientRecyclingOrder"));
 
@@ -408,20 +422,27 @@
   int64_t p;
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[1]);
   index_->DeleteResource(p);
+  ASSERT_EQ(3u, listener_->deletedResources_.size());
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[2]);
   index_->DeleteResource(p);
+  ASSERT_EQ(4u, listener_->deletedResources_.size());
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[3]);
   index_->DeleteResource(p);
+  ASSERT_EQ(5u, listener_->deletedResources_.size());
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[4]);
   index_->DeleteResource(p);
+  ASSERT_EQ(6u, listener_->deletedResources_.size());
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[6]);
   index_->DeleteResource(p);
   index_->DeleteResource(patients[8]);
+  ASSERT_EQ(8u, listener_->deletedResources_.size());
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[7]);
   index_->DeleteResource(p);
+  ASSERT_EQ(9u, listener_->deletedResources_.size());
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[9]);
   index_->DeleteResource(p);
   ASSERT_FALSE(index_->SelectPatientToRecycle(p));
+  ASSERT_EQ(10u, listener_->deletedResources_.size());
 
   ASSERT_EQ(10u, listener_->deletedFiles_.size());
   ASSERT_EQ(0u, index_->GetTableRecordCount("Resources")); 
@@ -477,16 +498,21 @@
 
   // Unprotecting a patient puts it at the last position in the recycling queue
   int64_t p;
+  ASSERT_EQ(0u, listener_->deletedResources_.size());
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[0]);
   index_->DeleteResource(p);
+  ASSERT_EQ(1u, listener_->deletedResources_.size());
   ASSERT_TRUE(index_->SelectPatientToRecycle(p, patients[1])); ASSERT_EQ(p, patients[4]);
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[1]);
   index_->DeleteResource(p);
+  ASSERT_EQ(2u, listener_->deletedResources_.size());
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[4]);
   index_->DeleteResource(p);
+  ASSERT_EQ(3u, listener_->deletedResources_.size());
   ASSERT_FALSE(index_->SelectPatientToRecycle(p, patients[2]));
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[2]);
   index_->DeleteResource(p);
+  ASSERT_EQ(4u, listener_->deletedResources_.size());
   // "patients[3]" is still protected
   ASSERT_FALSE(index_->SelectPatientToRecycle(p));
 
@@ -500,6 +526,7 @@
   ASSERT_TRUE(index_->SelectPatientToRecycle(p, patients[2]));
   ASSERT_TRUE(index_->SelectPatientToRecycle(p)); ASSERT_EQ(p, patients[3]);
   index_->DeleteResource(p);
+  ASSERT_EQ(5u, listener_->deletedResources_.size());
 
   ASSERT_EQ(5u, listener_->deletedFiles_.size());
   ASSERT_EQ(0u, index_->GetTableRecordCount("Resources"));