Mercurial > hg > orthanc
diff OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp @ 5758:ca06dde85358 large-queries
merged find-refactoring -> large-queries
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Thu, 05 Sep 2024 18:52:27 +0200 |
parents | 5463c3ae3235 717acb0ea546 |
children | d52b3f394f69 |
line wrap: on
line diff
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Thu Sep 05 18:21:56 2024 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Thu Sep 05 18:52:27 2024 +0200 @@ -29,6 +29,7 @@ #include "../../../OrthancFramework/Sources/SQLite/Transaction.h" #include "../Search/ISqlLookupFormatter.h" #include "../ServerToolbox.h" +#include "Compatibility/GenericFind.h" #include "Compatibility/ICreateInstance.h" #include "Compatibility/IGetChildrenMetadata.h" #include "Compatibility/ILookupResourceAndParent.h" @@ -356,7 +357,7 @@ virtual void ApplyLookupResources(std::list<std::string>& resourcesId, std::list<std::string>* instancesId, - const std::vector<DatabaseConstraint>& lookup, + const DatabaseConstraints& lookup, ResourceType queryLevel, const std::set<std::string>& labels, LabelsConstraint labelsConstraint, @@ -397,6 +398,324 @@ } } + virtual void ExecuteFind(FindResponse& response, + const FindRequest& request, + const Capabilities& capabilities) ORTHANC_OVERRIDE + { + const ResourceType requestLevel = request.GetLevel(); + std::string sql; + + { + // clean previous lookup table + SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup"); + s.Run(); + } + + { + // extract the resource id of interest by executing the lookup + LookupFormatter formatter; + LookupFormatter::Apply(sql, formatter, request); + + sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; + + SQLite::Statement statement(db_, sql); + formatter.Bind(statement); + statement.Run(); + + SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId, internalId FROM Lookup"); + while (s.Step()) + { + response.Add(new FindResponse::Resource(requestLevel, s.ColumnInt64(1), s.ColumnString(0))); + } + } + + // need MainDicomTags from resource ? + if (request.IsRetrieveMainDicomTags()) + { + sql = "SELECT id, tagGroup, tagElement, value " + "FROM MainDicomTags " + "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddStringDicomTag(requestLevel, + static_cast<uint16_t>(s.ColumnInt(1)), + static_cast<uint16_t>(s.ColumnInt(2)), + s.ColumnString(3)); + } + } + + // need MainDicomTags from parent ? + if (requestLevel > ResourceType_Patient && request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMainDicomTags()) + { + sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value " + "FROM MainDicomTags " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN Lookup ON MainDicomTags.id = currentLevel.parentId"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 1), + static_cast<uint16_t>(s.ColumnInt(1)), + static_cast<uint16_t>(s.ColumnInt(2)), + s.ColumnString(3)); + } + } + + // need MainDicomTags from grandparent ? + if (requestLevel > ResourceType_Study && request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMainDicomTags()) + { + sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value " + "FROM MainDicomTags " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId " + "INNER JOIN Lookup ON MainDicomTags.id = parentLevel.parentId"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 2), + static_cast<uint16_t>(s.ColumnInt(1)), + static_cast<uint16_t>(s.ColumnInt(2)), + s.ColumnString(3)); + } + } + + // need MainDicomTags from children ? + if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMainDicomTags().size() > 0) + { + sql = "SELECT Lookup.internalId, tagGroup, tagElement, value " + "FROM MainDicomTags " + "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + "INNER JOIN Lookup ON MainDicomTags.id = childLevel.internalId "; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 1), + DicomTag(static_cast<uint16_t>(s.ColumnInt(1)), + static_cast<uint16_t>(s.ColumnInt(2))), + s.ColumnString(3)); + } + } + + // need MainDicomTags from grandchildren ? + if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMainDicomTags().size() > 0) + { + sql = "SELECT Lookup.internalId, tagGroup, tagElement, value " + "FROM MainDicomTags " + "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + "INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId " + "INNER JOIN Lookup ON MainDicomTags.id = grandChildLevel.internalId "; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 2), + DicomTag(static_cast<uint16_t>(s.ColumnInt(1)), + static_cast<uint16_t>(s.ColumnInt(2))), + s.ColumnString(3)); + } + } + + // need parent identifier ? + if (request.IsRetrieveParentIdentifier()) + { + sql = "SELECT currentLevel.internalId, parentLevel.publicId " + "FROM Resources AS currentLevel " + "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " + "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.SetParentIdentifier(s.ColumnString(1)); + } + } + + // need resource metadata ? + if (request.IsRetrieveMetadata()) + { + sql = "SELECT id, type, value " + "FROM Metadata " + "INNER JOIN Lookup ON Metadata.id = Lookup.internalId"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddMetadata(requestLevel, + static_cast<MetadataType>(s.ColumnInt(1)), + s.ColumnString(2)); + } + } + + // need resource labels ? + if (request.IsRetrieveLabels()) + { + sql = "SELECT Lookup.internalId, label " + "FROM Labels " + "INNER JOIN Lookup ON Labels.id = Lookup.internalId"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddLabel(s.ColumnString(1)); + } + } + + // need one instance identifier ? TODO: it might be actually more interesting to retrieve directly the attachment ids .... + if (request.IsRetrieveOneInstanceIdentifier()) + { + if (requestLevel == ResourceType_Series) + { + 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); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildIdentifier(ResourceType_Instance, 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); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); + } + } + 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); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); + } + } + else + { + throw OrthancException(ErrorCode_InternalError); + } + } + + // need children metadata ? + if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMetadata().size() > 0) + { + sql = "SELECT Lookup.internalId, type, value " + "FROM Metadata " + "INNER JOIN Lookup ON Lookup.internalId = childLevel.parentId " + "INNER JOIN Resources childLevel ON childLevel.internalId = Metadata.id"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 1), + static_cast<MetadataType>(s.ColumnInt(1)), + s.ColumnString(2)); + } + } + + // need grandchildren metadata ? + if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMetadata().size() > 0) + { + sql = "SELECT Lookup.internalId, type, value " + "FROM Metadata " + "INNER JOIN Lookup ON Lookup.internalId = childLevel.parentId " + "INNER JOIN Resources childLevel ON childLevel.internalId = grandChildLevel.parentId " + "INNER JOIN Resources grandChildLevel ON grandChildLevel.internalId = Metadata.id"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 2), + static_cast<MetadataType>(s.ColumnInt(1)), + s.ColumnString(2)); + } + } + + // need children identifiers ? + if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).IsRetrieveIdentifiers()) + { + sql = "SELECT Lookup.internalId, childLevel.publicId " + "FROM Resources AS currentLevel " + "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " + "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1), s.ColumnString(1)); + } + } + + // need grandchildren identifiers ? + if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).IsRetrieveIdentifiers()) + { + sql = "SELECT Lookup.internalId, grandChildLevel.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 "; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2), s.ColumnString(1)); + } + } + + // need resource attachments ? + if (request.IsRetrieveAttachments()) + { + sql = "SELECT Lookup.internalId, fileType, uuid, uncompressedSize, compressedSize, compressionType, uncompressedMD5, compressedMD5 " + "FROM AttachedFiles " + "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId"; + + SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); + while (s.Step()) + { + FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); + FileInfo file(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)); + res.AddAttachment(file); + } + + } + } + + // From the "ICreateInstance" interface virtual void AttachChild(int64_t parent,