Mercurial > hg > orthanc
changeset 5774:f96abfe08946 find-refactoring
implementation of specialized SQL commands in SQLiteDatabaseWrapper
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Thu, 12 Sep 2024 08:25:41 +0200 |
| parents | 3b7dce0e43c6 |
| children | 9af45c841f59 |
| files | OrthancServer/Sources/Database/FindResponse.cpp OrthancServer/Sources/Database/FindResponse.h OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h OrthancServer/Sources/ResourceFinder.cpp |
| diffstat | 5 files changed, 168 insertions(+), 47 deletions(-) [+] |
line wrap: on
line diff
--- a/OrthancServer/Sources/Database/FindResponse.cpp Wed Sep 11 21:21:42 2024 +0200 +++ b/OrthancServer/Sources/Database/FindResponse.cpp Thu Sep 12 08:25:41 2024 +0200 @@ -505,6 +505,54 @@ } + void FindResponse::Resource::SetOneInstancePublicId(const std::string& instancePublicId) + { + SetOneInstanceMetadataAndAttachments(instancePublicId, std::map<MetadataType, std::string>(), + std::map<FileContentType, FileInfo>()); + } + + + void FindResponse::Resource::AddOneInstanceMetadata(MetadataType metadata, + const std::string& value) + { + if (hasOneInstanceMetadataAndAttachments_) + { + if (oneInstanceMetadata_.find(metadata) == oneInstanceMetadata_.end()) + { + oneInstanceMetadata_[metadata] = value; + } + else + { + throw OrthancException(ErrorCode_BadSequenceOfCalls, "Metadata already exists"); + } + } + else + { + throw OrthancException(ErrorCode_BadSequenceOfCalls); + } + } + + + void FindResponse::Resource::AddOneInstanceAttachment(const FileInfo& attachment) + { + if (hasOneInstanceMetadataAndAttachments_) + { + if (oneInstanceAttachments_.find(attachment.GetContentType()) == oneInstanceAttachments_.end()) + { + oneInstanceAttachments_[attachment.GetContentType()] = attachment; + } + else + { + throw OrthancException(ErrorCode_BadSequenceOfCalls, "Attachment already exists"); + } + } + else + { + throw OrthancException(ErrorCode_BadSequenceOfCalls); + } + } + + const std::string& FindResponse::Resource::GetOneInstancePublicId() const { if (hasOneInstanceMetadataAndAttachments_) @@ -591,7 +639,7 @@ static void DebugAttachments(Json::Value& target, const std::map<FileContentType, FileInfo>& attachments) { - Json::Value v = Json::objectValue; + target = Json::objectValue; for (std::map<FileContentType, FileInfo>::const_iterator it = attachments.begin(); it != attachments.end(); ++it) { @@ -601,7 +649,7 @@ } else { - DebugAddAttachment(v, it->second); + DebugAddAttachment(target, it->second); } } } @@ -701,7 +749,8 @@ DebugAttachments(target["Attachments"], attachments_); } - if (request.IsRetrieveOneInstanceMetadataAndAttachments()) + if (request.GetLevel() != ResourceType_Instance && + request.IsRetrieveOneInstanceMetadataAndAttachments()) { DebugMetadata(target["OneInstance"]["Metadata"], GetOneInstanceMetadata()); DebugAttachments(target["OneInstance"]["Attachments"], GetOneInstanceAttachments());
--- a/OrthancServer/Sources/Database/FindResponse.h Wed Sep 11 21:21:42 2024 +0200 +++ b/OrthancServer/Sources/Database/FindResponse.h Thu Sep 12 08:25:41 2024 +0200 @@ -277,6 +277,13 @@ const std::map<MetadataType, std::string>& metadata, const std::map<FileContentType, FileInfo>& attachments); + void SetOneInstancePublicId(const std::string& instancePublicId); + + void AddOneInstanceMetadata(MetadataType metadata, + const std::string& value); + + void AddOneInstanceAttachment(const FileInfo& attachment); + bool HasOneInstanceMetadataAndAttachments() const { return hasOneInstanceMetadataAndAttachments_;
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Wed Sep 11 21:21:42 2024 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Thu Sep 12 08:25:41 2024 +0200 @@ -68,12 +68,19 @@ virtual std::string FormatLimits(uint64_t since, uint64_t count) ORTHANC_OVERRIDE { std::string sql; + if (count > 0) { sql += " LIMIT " + boost::lexical_cast<std::string>(count); } + if (since > 0) { + if (count == 0) + { + sql += " LIMIT -1"; // In SQLite, "OFFSET" cannot appear without "LIMIT" + } + sql += " OFFSET " + boost::lexical_cast<std::string>(since); } @@ -588,59 +595,94 @@ } } - if (request.IsRetrieveOneInstanceMetadataAndAttachments()) + if (request.GetLevel() != ResourceType_Instance && + request.IsRetrieveOneInstanceMetadataAndAttachments()) { - throw OrthancException(ErrorCode_NotImplemented); + { + SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS OneInstance"); + s.Run(); + } + + switch (requestLevel) + { + case ResourceType_Patient: + { + SQLite::Statement s( + db_, SQLITE_FROM_HERE, + "CREATE TEMPORARY TABLE OneInstance AS " + "SELECT Lookup.internalId AS parentInternalId, grandGrandChildLevel.publicId AS instancePublicId, grandGrandChildLevel.internalId AS instanceInternalId " + "FROM Resources AS grandGrandChildLevel " + "INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId " + "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " + "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId"); + s.Run(); + break; + } -#if 0 - // need one instance identifier ? TODO: it might be actually more interesting to retrieve directly the attachment ids .... - if (requestLevel == ResourceType_Series) + case ResourceType_Study: + { + SQLite::Statement s( + db_, SQLITE_FROM_HERE, + "CREATE TEMPORARY TABLE OneInstance AS " + "SELECT Lookup.internalId AS parentInternalId, grandChildLevel.publicId AS instancePublicId, grandChildLevel.internalId AS instanceInternalId " + "FROM Resources AS grandChildLevel " + "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " + "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId"); + s.Run(); + break; + } + + case ResourceType_Series: + { + SQLite::Statement s( + db_, SQLITE_FROM_HERE, + "CREATE TEMPORARY TABLE OneInstance AS " + "SELECT Lookup.internalId AS parentInternalId, childLevel.publicId AS instancePublicId, childLevel.internalId AS instanceInternalId " + "FROM Resources AS childLevel " + "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId"); + s.Run(); + break; + } + + default: + throw OrthancException(ErrorCode_InternalError); + } + { - sql = "SELECT Lookup.internalId, childLevel.publicId " - "FROM Resources AS childLevel " - "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; - - SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT parentInternalId, instancePublicId FROM OneInstance"); while (s.Step()) { FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); - res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); + res.SetOneInstancePublicId(s.ColumnString(1)); } } - else if (requestLevel == ResourceType_Study) + { - sql = "SELECT Lookup.internalId, grandChildLevel.publicId " - "FROM Resources AS grandChildLevel " - "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " - "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; - - SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT OneInstance.parentInternalId, Metadata.type, Metadata.value " + "FROM Metadata INNER JOIN OneInstance ON Metadata.id = OneInstance.instanceInternalId"); while (s.Step()) { FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); - res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); + res.AddOneInstanceMetadata(static_cast<MetadataType>(s.ColumnInt(1)), s.ColumnString(2)); } } - else if (requestLevel == ResourceType_Patient) + { - sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId " - "FROM Resources AS grandGrandChildLevel " - "INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId " - "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " - "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; - - SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + SQLite::Statement s(db_, SQLITE_FROM_HERE, + "SELECT OneInstance.parentInternalId, AttachedFiles.fileType, AttachedFiles.uuid, " + "AttachedFiles.uncompressedSize, AttachedFiles.compressedSize, " + "AttachedFiles.compressionType, AttachedFiles.uncompressedMD5, AttachedFiles.compressedMD5 " + "FROM AttachedFiles INNER JOIN OneInstance ON AttachedFiles.id = OneInstance.instanceInternalId"); while (s.Step()) { FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); - res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); + res.AddOneInstanceAttachment( + FileInfo(s.ColumnString(2), static_cast<FileContentType>(s.ColumnInt(1)), + s.ColumnInt64(3), s.ColumnString(6), + static_cast<CompressionType>(s.ColumnInt(5)), + s.ColumnInt64(4), s.ColumnString(7))); } } - else - { - throw OrthancException(ErrorCode_InternalError); - } -#endif } // need children metadata ? @@ -681,7 +723,9 @@ } // need children identifiers ? - if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).IsRetrieveIdentifiers()) + if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Study).IsRetrieveIdentifiers()) || + (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) || + (requestLevel == ResourceType_Series && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers())) { sql = "SELECT Lookup.internalId, childLevel.publicId " "FROM Resources AS currentLevel " @@ -697,7 +741,8 @@ } // need grandchildren identifiers ? - if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).IsRetrieveIdentifiers()) + if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) || + (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers())) { sql = "SELECT Lookup.internalId, grandChildLevel.publicId " "FROM Resources AS currentLevel " @@ -713,6 +758,24 @@ } } + // need grandgrandchildren identifiers ? + if (requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers()) + { + sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId " + "FROM Resources AS currentLevel " + "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " + "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId " + "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId " + "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId "; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); + } + } + // need resource attachments ? if (request.IsRetrieveAttachments()) { @@ -869,17 +932,11 @@ int64_t since, uint32_t limit) ORTHANC_OVERRIDE { - if (limit == 0) - { - target.clear(); - return; - } - SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId FROM Resources WHERE " "resourceType=? LIMIT ? OFFSET ?"); s.BindInt(0, resourceType); - s.BindInt64(1, limit); + s.BindInt64(1, limit == 0 ? -1 : limit); // In SQLite, setting "LIMIT" to "-1" means "no limit" s.BindInt64(2, since); target.clear();
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h Wed Sep 11 21:21:42 2024 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h Thu Sep 12 08:25:41 2024 +0200 @@ -102,8 +102,8 @@ virtual bool HasIntegratedFind() const ORTHANC_OVERRIDE { - //return true; // => This uses optimized SQL commands - return false; // => This uses Compatibility/GenericFind + return true; // => This uses specialized SQL commands + //return false; // => This uses Compatibility/GenericFind } /**
--- a/OrthancServer/Sources/ResourceFinder.cpp Wed Sep 11 21:21:42 2024 +0200 +++ b/OrthancServer/Sources/ResourceFinder.cpp Thu Sep 12 08:25:41 2024 +0200 @@ -969,6 +969,14 @@ { const FindResponse::Resource& resource = response.GetResourceByIndex(i); +#if 0 + { + Json::Value v; + resource.DebugExport(v, request_); + std::cout << v.toStyledString(); + } +#endif + DicomMap requestedTags; if (hasRequestedTags_)