Mercurial > hg > orthanc
changeset 5796:16ce3c920f71 find-refactoring tip
rewrote SQLite find using CTEs instead of temporary tables
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Thu, 19 Sep 2024 17:50:20 +0200 |
| parents | 9990b4140c1c |
| children | |
| files | OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp OrthancServer/Sources/Search/ISqlLookupFormatter.cpp |
| diffstat | 2 files changed, 587 insertions(+), 330 deletions(-) [+] |
line wrap: on
line diff
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Wed Sep 18 15:45:18 2024 +0200 +++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp Thu Sep 19 17:50:20 2024 +0200 @@ -43,6 +43,39 @@ namespace Orthanc { + static std::string JoinRequestedMetadata(const FindRequest::ChildrenSpecification& childrenSpec) + { + std::set<std::string> metadataTypes; + for (std::set<MetadataType>::const_iterator it = childrenSpec.GetMetadata().begin(); it != childrenSpec.GetMetadata().end(); ++it) + { + metadataTypes.insert(boost::lexical_cast<std::string>(*it)); + } + std::string joinedMetadataTypes; + Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ", "); + + return joinedMetadataTypes; + } + + static std::string JoinRequestedTags(const FindRequest::ChildrenSpecification& childrenSpec) + { + // note: SQLite does not seem to support (tagGroup, tagElement) in ((x, y), (z, w)) in complex subqueries. + // Therefore, since we expect the requested tag list to be short, we write it as + // ((tagGroup = x AND tagElement = y ) OR (tagGroup = z AND tagElement = w)) + + std::string sql = " ("; + std::set<std::string> tags; + for (std::set<DicomTag>::const_iterator it = childrenSpec.GetMainDicomTags().begin(); it != childrenSpec.GetMainDicomTags().end(); ++it) + { + tags.insert("(tagGroup = " + boost::lexical_cast<std::string>(it->GetGroup()) + + " AND tagElement = " + boost::lexical_cast<std::string>(it->GetElement()) + ")"); + } + std::string joinedTags; + Orthanc::Toolbox::JoinStrings(joinedTags, tags, " OR "); + + sql += joinedTags + ") "; + return sql; + } + class SQLiteDatabaseWrapper::LookupFormatter : public ISqlLookupFormatter { private: @@ -390,7 +423,7 @@ std::string sql; LookupFormatter::Apply(sql, formatter, lookup, queryLevel, labels, labelsConstraint, limit); - sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; + sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; // TODO-FIND: use a CTE (or is this method obsolete ?) { SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup"); @@ -420,134 +453,311 @@ } } +#define C0_QUERY_ID 0 +#define C1_INTERNAL_ID 1 +#define C2_ROW_NUMBER 2 +#define C3_STRING_1 3 +#define C4_STRING_2 4 +#define C5_STRING_3 5 +#define C6_INT_1 6 +#define C7_INT_2 7 +#define C8_BIG_INT_1 8 +#define C9_BIG_INT_2 9 + +#define QUERY_LOOKUP 1 +#define QUERY_MAIN_DICOM_TAGS 2 +#define QUERY_ATTACHMENTS 3 +#define QUERY_METADATA 4 +#define QUERY_LABELS 5 +#define QUERY_PARENT_MAIN_DICOM_TAGS 10 +#define QUERY_PARENT_IDENTIFIER 11 +#define QUERY_PARENT_METADATA 12 +#define QUERY_GRAND_PARENT_MAIN_DICOM_TAGS 15 +#define QUERY_GRAND_PARENT_METADATA 16 +#define QUERY_CHILDREN_IDENTIFIERS 20 +#define QUERY_CHILDREN_MAIN_DICOM_TAGS 21 +#define QUERY_CHILDREN_METADATA 22 +#define QUERY_GRAND_CHILDREN_IDENTIFIERS 30 +#define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 31 +#define QUERY_GRAND_CHILDREN_METADATA 32 +#define QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS 40 +#define QUERY_ONE_INSTANCE_IDENTIFIER 50 +#define QUERY_ONE_INSTANCE_METADATA 51 +#define QUERY_ONE_INSTANCE_ATTACHMENTS 52 + +#define STRINGIFY(x) #x +#define TOSTRING(x) STRINGIFY(x) + + virtual void ExecuteFind(FindResponse& response, const FindRequest& request, const Capabilities& capabilities) ORTHANC_OVERRIDE { - const ResourceType requestLevel = request.GetLevel(); + LookupFormatter formatter; std::string sql; + const ResourceType requestLevel = request.GetLevel(); + std::string lookupSql; + LookupFormatter::Apply(lookupSql, formatter, request); + + // base query, retrieve the ordered internalId and publicId of the selected resources + sql = "WITH Lookup AS (" + lookupSql + ") "; + + // in SQLite, all CTEs must be created at the beginning of the query, you can not define local CTE inside subqueries + // need one instance info ? (part 1: create the CTE) + if (request.GetLevel() != ResourceType_Instance && + request.IsRetrieveOneInstanceMetadataAndAttachments()) { - // clean previous lookup table - SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup"); - s.Run(); + // Here, we create a nested CTE 'OneInstance' with one instance ID to join with metadata and main + sql += ", OneInstance AS"; + + switch (requestLevel) + { + case ResourceType_Series: + { + sql+= " (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) "; + break; + } + + case ResourceType_Study: + { + sql+= " (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) "; + break; + } + + case ResourceType_Patient: + { + sql+= " (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) "; + break; + } + + default: + throw OrthancException(ErrorCode_InternalError); + } } - { - // extract the resource id of interest by executing the lookup - LookupFormatter formatter; - LookupFormatter::Apply(sql, formatter, request); - - sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; // TODO-FIND: use a CTE + sql += "SELECT " + " " TOSTRING(QUERY_LOOKUP) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " Lookup.rowNumber AS c2_rowNumber, " + " Lookup.publicId AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " NULL AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + " FROM Lookup "; - SQLite::Statement statement(db_, sql); - formatter.Bind(statement); - statement.Run(); + // need one instance info ? (part 2: execute the queries) + if (request.GetLevel() != ResourceType_Instance && + request.IsRetrieveOneInstanceMetadataAndAttachments()) + { + sql += " UNION SELECT" + " " TOSTRING(QUERY_ONE_INSTANCE_IDENTIFIER) " AS c0_queryId, " + " parentInternalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " instancePublicId AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " NULL AS c6_int1, " + " NULL AS c7_int2, " + " instanceInternalId AS c8_big_int1, " + " NULL AS c9_big_int2 " + " FROM OneInstance "; - 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))); - } + sql += " UNION SELECT" + " " TOSTRING(QUERY_ONE_INSTANCE_METADATA) " AS c0_queryId, " + " parentInternalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " Metadata.value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " Metadata.type AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + " FROM Metadata " + " INNER JOIN OneInstance ON Metadata.id = OneInstance.instanceInternalId "; + + sql += " UNION SELECT" + " " TOSTRING(QUERY_ONE_INSTANCE_ATTACHMENTS) " AS c0_queryId, " + " parentInternalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " uuid AS c3_string1, " + " uncompressedMD5 AS c4_string2, " + " compressedMD5 AS c5_string3, " + " fileType AS c6_int1, " + " compressionType AS c7_int2, " + " compressedSize AS c8_big_int1, " + " uncompressedSize AS c9_big_int2 " + " FROM AttachedFiles " + " INNER JOIN OneInstance ON AttachedFiles.id = OneInstance.instanceInternalId "; + } // need MainDicomTags from resource ? if (request.IsRetrieveMainDicomTags()) { - sql = "SELECT id, tagGroup, tagElement, value " - "FROM MainDicomTags " - "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId"; + sql += "UNION SELECT " + " " TOSTRING(QUERY_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "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 resource metadata ? + if (request.IsRetrieveMetadata()) + { + sql += "UNION SELECT " + " " TOSTRING(QUERY_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " type AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Metadata " + "INNER JOIN Lookup ON Metadata.id = Lookup.internalId "; } - + // need resource attachments ? + if (request.IsRetrieveAttachments()) + { + sql += "UNION SELECT " + " " TOSTRING(QUERY_ATTACHMENTS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " uuid AS c3_string1, " + " uncompressedMD5 AS c4_string2, " + " compressedMD5 AS c5_string3, " + " fileType AS c6_int1, " + " compressionType AS c7_int2, " + " compressedSize AS c8_big_int1, " + " uncompressedSize AS c9_big_int2 " + "FROM AttachedFiles " + "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId "; + } + + + // need resource labels ? + if (request.IsRetrieveLabels()) + { + sql += "UNION SELECT " + " " TOSTRING(QUERY_LABELS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " label AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " NULL AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Labels " + "INNER JOIN Lookup ON Labels.id = Lookup.internalId "; + } + if (requestLevel > ResourceType_Patient) { // need MainDicomTags from parent ? if (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)); - } + sql += "UNION SELECT " + " " TOSTRING(QUERY_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Lookup " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN MainDicomTags ON MainDicomTags.id = currentLevel.parentId "; } // need metadata from parent ? if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMetadata()) { - sql = "SELECT currentLevel.internalId, type, value " - "FROM Metadata " - "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " - "INNER JOIN Lookup ON Metadata.id = currentLevel.parentId"; - - SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); - while (s.Step()) - { - FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); - res.AddMetadata(static_cast<ResourceType>(requestLevel - 1), - static_cast<MetadataType>(s.ColumnInt(1)), - s.ColumnString(2)); - } - } - } - - if (requestLevel > ResourceType_Study) - { - // need MainDicomTags from grandparent ? - if (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)); - } + sql += "UNION SELECT " + " " TOSTRING(QUERY_PARENT_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " type AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Lookup " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN Metadata ON Metadata.id = currentLevel.parentId "; } - // need metadata from grandparent ? - if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMetadata()) + if (requestLevel > ResourceType_Study) { - sql = "SELECT currentLevel.internalId, type, value " - "FROM Metadata " - "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " - "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId " - "INNER JOIN Lookup ON Metadata.id = parentLevel.parentId"; + // need MainDicomTags from grandparent ? + if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMainDicomTags()) + { + sql += "UNION SELECT " + " " TOSTRING(QUERY_GRAND_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Lookup " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId " + "INNER JOIN MainDicomTags ON MainDicomTags.id = parentLevel.parentId "; + } - SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); - while (s.Step()) + // need metadata from grandparent ? + if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMetadata()) { - FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); - res.AddMetadata(static_cast<ResourceType>(requestLevel - 2), - static_cast<MetadataType>(s.ColumnInt(1)), - s.ColumnString(2)); + sql += "UNION SELECT " + " " TOSTRING(QUERY_GRAND_PARENT_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " type AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Lookup " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId " + "INNER JOIN Metadata ON Metadata.id = parentLevel.parentId "; } } } @@ -555,216 +765,98 @@ // 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)); - } + sql += "UNION SELECT " + " " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN MainDicomTags ON MainDicomTags.id = childLevel.internalId AND " + JoinRequestedTags(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1))); } // 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)); - } + sql += "UNION SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN MainDicomTags ON MainDicomTags.id = grandChildLevel.internalId AND " + JoinRequestedTags(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2))); } // 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 info ? - if (request.GetLevel() != ResourceType_Instance && - request.IsRetrieveOneInstanceMetadataAndAttachments()) - { - { - SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS OneInstance"); // TODO-FIND: use a CTE - 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; - } - - 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); - } - - { - SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT parentInternalId, instancePublicId FROM OneInstance"); - while (s.Step()) - { - FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); - res.SetOneInstancePublicId(s.ColumnString(1)); - } - } - - { - 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.AddOneInstanceMetadata(static_cast<MetadataType>(s.ColumnInt(1)), s.ColumnString(2)); - } - } - - { - 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.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))); - } - } + sql += "UNION SELECT " + " " TOSTRING(QUERY_PARENT_IDENTIFIER) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " parentLevel.publicId AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " NULL AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Resources AS currentLevel " + " INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " + " INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "; } // 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)); - } + sql += "UNION SELECT " + " " TOSTRING(QUERY_CHILDREN_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " type AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN Metadata ON Metadata.id = childLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1))) + ") "; } // 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)); - } + sql += "UNION SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " value AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " type AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN Metadata ON Metadata.id = grandChildLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2))) + ") "; } // need children identifiers ? @@ -772,78 +864,240 @@ (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 " - "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)); - } + sql += "UNION SELECT " + " " TOSTRING(QUERY_CHILDREN_IDENTIFIERS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " childLevel.publicId AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " NULL AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " + "FROM Resources AS currentLevel " + " INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " + " INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "; } // need grandchildren identifiers ? if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) || (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers())) { - sql = "SELECT Lookup.internalId, grandChildLevel.publicId " + sql += "UNION SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " grandChildLevel.publicId AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " NULL AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " "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 grandgrandchildren identifiers ? if (requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers()) { - sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId " + sql += "UNION SELECT " + " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " NULL AS c2_rowNumber, " + " grandGrandChildLevel.publicId AS c3_string1, " + " NULL AS c4_string2, " + " NULL AS c5_string3, " + " NULL AS c6_int1, " + " NULL AS c7_int2, " + " NULL AS c8_big_int1, " + " NULL AS c9_big_int2 " "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()) + + sql += " ORDER BY c0_queryId, c2_rowNumber"; // this is really important to make sure that the Lookup query is the first one to provide results since we use it to create the responses element ! + + SQLite::Statement s(db_, SQLITE_FROM_HERE_DYNAMIC(sql), sql); + formatter.Bind(s); + + while (s.Step()) { - sql = "SELECT Lookup.internalId, fileType, uuid, uncompressedSize, compressedSize, compressionType, uncompressedMD5, compressedMD5 " - "FROM AttachedFiles " - "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId"; + int queryId = s.ColumnInt(C0_QUERY_ID); + int64_t internalId = s.ColumnInt64(C1_INTERNAL_ID); + + // LOG(INFO) << queryId << ": " << internalId; + // continue; + + assert(queryId == QUERY_LOOKUP || response.HasResource(internalId)); // the QUERY_LOOKUP must be read first and must create the response before any other query tries to populate the fields + + switch (queryId) + { + case QUERY_LOOKUP: + response.Add(new FindResponse::Resource(requestLevel, internalId, s.ColumnString(C3_STRING_1))); + break; + + case QUERY_LABELS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddLabel(s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_ATTACHMENTS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C6_INT_1)), + s.ColumnInt64(C8_BIG_INT_1), s.ColumnString(C4_STRING_2), + static_cast<CompressionType>(s.ColumnInt(C7_INT_2)), + s.ColumnInt64(C9_BIG_INT_2), s.ColumnString(C5_STRING_3)); + res.AddAttachment(file); + }; break; + + case QUERY_MAIN_DICOM_TAGS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddStringDicomTag(requestLevel, + static_cast<uint16_t>(s.ColumnInt(C6_INT_1)), + static_cast<uint16_t>(s.ColumnInt(C7_INT_2)), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_PARENT_MAIN_DICOM_TAGS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 1), + static_cast<uint16_t>(s.ColumnInt(C6_INT_1)), + static_cast<uint16_t>(s.ColumnInt(C7_INT_2)), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_GRAND_PARENT_MAIN_DICOM_TAGS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 2), + static_cast<uint16_t>(s.ColumnInt(C6_INT_1)), + static_cast<uint16_t>(s.ColumnInt(C7_INT_2)), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_CHILDREN_MAIN_DICOM_TAGS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 1), + DicomTag(static_cast<uint16_t>(s.ColumnInt(C6_INT_1)), static_cast<uint16_t>(s.ColumnInt(C7_INT_2))), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 2), + DicomTag(static_cast<uint16_t>(s.ColumnInt(C6_INT_1)), static_cast<uint16_t>(s.ColumnInt(C7_INT_2))), + s.ColumnString(C3_STRING_1)); + }; break; - 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); + case QUERY_METADATA: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddMetadata(static_cast<ResourceType>(requestLevel), + static_cast<MetadataType>(s.ColumnInt(C6_INT_1)), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_PARENT_METADATA: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddMetadata(static_cast<ResourceType>(requestLevel - 1), + static_cast<MetadataType>(s.ColumnInt(C6_INT_1)), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_GRAND_PARENT_METADATA: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddMetadata(static_cast<ResourceType>(requestLevel - 2), + static_cast<MetadataType>(s.ColumnInt(C6_INT_1)), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_CHILDREN_METADATA: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 1), + static_cast<MetadataType>(s.ColumnInt(C6_INT_1)), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_GRAND_CHILDREN_METADATA: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 2), + static_cast<MetadataType>(s.ColumnInt(C6_INT_1)), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_PARENT_IDENTIFIER: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.SetParentIdentifier(s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_CHILDREN_IDENTIFIERS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_GRAND_CHILDREN_IDENTIFIERS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 3), + s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_ONE_INSTANCE_IDENTIFIER: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.SetOneInstancePublicId(s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_ONE_INSTANCE_METADATA: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + res.AddOneInstanceMetadata(static_cast<MetadataType>(s.ColumnInt(C6_INT_1)), s.ColumnString(C3_STRING_1)); + }; break; + + case QUERY_ONE_INSTANCE_ATTACHMENTS: + { + FindResponse::Resource& res = response.GetResourceByInternalId(internalId); + FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C6_INT_1)), + s.ColumnInt64(C8_BIG_INT_1), s.ColumnString(C4_STRING_2), + static_cast<CompressionType>(s.ColumnInt(C7_INT_2)), + s.ColumnInt64(C9_BIG_INT_2), s.ColumnString(C5_STRING_3)); + res.AddOneInstanceAttachment(file); + }; break; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); } - } } - - // From the "ICreateInstance" interface virtual void AttachChild(int64_t parent, int64_t child) ORTHANC_OVERRIDE
--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Wed Sep 18 15:45:18 2024 +0200 +++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Thu Sep 19 17:50:20 2024 +0200 @@ -619,10 +619,13 @@ assert(upperLevel <= queryLevel && queryLevel <= lowerLevel); + std::string ordering = "row_number() over (order by " + strQueryLevel + ".publicId) as rowNumber"; // we need a default ordering in order to make default queries repeatable when using since&limit + sql = ("SELECT " + strQueryLevel + ".publicId, " + - strQueryLevel + ".internalId" + + strQueryLevel + ".internalId, " + + ordering + " FROM Resources AS " + strQueryLevel);