# HG changeset patch # User Sebastien Jodogne # Date 1410942341 -7200 # Node ID 8f9d49192815faa3d70dc709c43393daea16bb35 # Parent badc14fee61f4d8508f34cbe15bd621a067b0e7b speeding up db schema diff -r badc14fee61f -r 8f9d49192815 OrthancServer/PrepareDatabase.sql --- 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); diff -r badc14fee61f -r 8f9d49192815 OrthancServer/Upgrade4To5.sql --- 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 diff -r badc14fee61f -r 8f9d49192815 UnitTestsSources/ServerIndexTests.cpp --- 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 deletedFiles_; + std::vector 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"));