# HG changeset patch # User Alain Mazy # Date 1728403042 -7200 # Node ID e99ca3508757d532df44662b37e6f5fa42382819 # Parent 4c84d908e89123ef5976cd47442b02a0f0d8a433 implemented ordering + metadata constraints in PG diff -r 4c84d908e891 -r e99ca3508757 Framework/Plugins/ISqlLookupFormatter.cpp --- a/Framework/Plugins/ISqlLookupFormatter.cpp Mon Sep 30 18:02:36 2024 +0200 +++ b/Framework/Plugins/ISqlLookupFormatter.cpp Tue Oct 08 17:57:22 2024 +0200 @@ -64,9 +64,33 @@ } + static std::string FormatLevel(const char* prefix, Orthanc::ResourceType level) + { + switch (level) + { + case Orthanc::ResourceType_Patient: + return std::string(prefix) + "patients"; + + case Orthanc::ResourceType_Study: + return std::string(prefix) + "studies"; + + case Orthanc::ResourceType_Series: + return std::string(prefix) + "series"; + + case Orthanc::ResourceType_Instance: + return std::string(prefix) + "instances"; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + } + static bool FormatComparison(std::string& target, ISqlLookupFormatter& formatter, - const DatabaseConstraint& constraint, + OrthancDatabases::ConstraintType constraintType, + const std::vector& values, + bool isCaseSensitive, + bool isMandatory, size_t index, bool escapeBrackets) { @@ -74,14 +98,14 @@ std::string comparison; - switch (constraint.GetConstraintType()) + switch (constraintType) { case ConstraintType_Equal: case ConstraintType_SmallerOrEqual: case ConstraintType_GreaterOrEqual: { std::string op; - switch (constraint.GetConstraintType()) + switch (constraintType) { case ConstraintType_Equal: op = "="; @@ -99,9 +123,9 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); } - std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue()); + std::string parameter = formatter.GenerateParameter(values[0]); - if (constraint.IsCaseSensitive()) + if (isCaseSensitive) { comparison = tag + ".value " + op + " " + parameter; } @@ -115,16 +139,16 @@ case ConstraintType_List: { - for (size_t i = 0; i < constraint.GetValuesCount(); i++) + for (size_t i = 0; i < values.size(); i++) { if (!comparison.empty()) { comparison += ", "; } - std::string parameter = formatter.GenerateParameter(constraint.GetValue(i)); + std::string parameter = formatter.GenerateParameter(values[i]); - if (constraint.IsCaseSensitive()) + if (isCaseSensitive) { comparison += parameter; } @@ -134,7 +158,7 @@ } } - if (constraint.IsCaseSensitive()) + if (isCaseSensitive) { comparison = tag + ".value IN (" + comparison + ")"; } @@ -148,11 +172,11 @@ case ConstraintType_Wildcard: { - const std::string value = constraint.GetSingleValue(); + const std::string value = values[0]; if (value == "*") { - if (!constraint.IsMandatory()) + if (!isMandatory) { // Universal constraint on an optional tag, ignore it return false; @@ -201,7 +225,7 @@ std::string parameter = formatter.GenerateParameter(escaped); - if (constraint.IsCaseSensitive()) + if (isCaseSensitive) { comparison = (tag + ".value LIKE " + parameter + " " + formatter.FormatWildcardEscape()); @@ -220,7 +244,7 @@ return false; } - if (constraint.IsMandatory()) + if (isMandatory) { target = comparison; } @@ -237,6 +261,88 @@ } + static bool FormatComparison(std::string& target, + ISqlLookupFormatter& formatter, + const Orthanc::DatabasePluginMessages::DatabaseMetadataConstraint& constraint, + size_t index, + bool escapeBrackets) + { + std::vector values; + OrthancDatabases::ConstraintType constraintType; + switch (constraint.type()) + { + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_EQUAL: + constraintType = OrthancDatabases::ConstraintType_Equal; + break; + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_GREATER_OR_EQUAL: + constraintType = OrthancDatabases::ConstraintType_GreaterOrEqual; + break; + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_LIST: + constraintType = OrthancDatabases::ConstraintType_List; + break; + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_SMALLER_OR_EQUAL: + constraintType = OrthancDatabases::ConstraintType_SmallerOrEqual; + break; + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_WILDCARD: + constraintType = OrthancDatabases::ConstraintType_Wildcard; + break; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + + if (constraint.type() == Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_LIST) + { + for (int i = 0; i < constraint.values_size(); ++i) + { + values.push_back(constraint.values(i)); + } + } + else + { + assert(constraint.values_size() == 1); + values.push_back(constraint.values(0)); + } + + return FormatComparison(target, + formatter, + constraintType, + values, + constraint.is_case_sensitive(), + constraint.is_mandatory(), + index, + escapeBrackets); + + } + + static bool FormatComparison(std::string& target, + ISqlLookupFormatter& formatter, + const DatabaseConstraint& constraint, + size_t index, + bool escapeBrackets) + { + std::vector values; + if (constraint.GetConstraintType() == OrthancDatabases::ConstraintType_List) + { + for (size_t i = 0; i < constraint.GetValuesCount(); ++i) + { + values.push_back(constraint.GetValue(i)); + } + } + else + { + values.push_back(constraint.GetSingleValue()); + } + + return FormatComparison(target, + formatter, + constraint.GetConstraintType(), + values, + constraint.IsCaseSensitive(), + constraint.IsMandatory(), + index, + escapeBrackets); + } + static void FormatJoin(std::string& target, const DatabaseConstraint& constraint, size_t index) @@ -268,6 +374,95 @@ boost::lexical_cast(constraint.GetTag().GetElement())); } + static void FormatJoin(std::string& target, + const Orthanc::DatabasePluginMessages::DatabaseMetadataConstraint& constraint, + Orthanc::ResourceType level, + size_t index) + { + std::string tag = "t" + boost::lexical_cast(index); + + if (constraint.is_mandatory()) + { + target = " INNER JOIN "; + } + else + { + target = " LEFT JOIN "; + } + + target += "Metadata "; + + target += tag + " ON " + tag + ".id = " + FormatLevel(level) + + ".internalId AND " + tag + ".type = " + + boost::lexical_cast(constraint.metadata()); + } + + static void FormatJoinForOrdering(std::string& target, + uint32_t tagGroup, + uint32_t tagElement, + Orthanc::ResourceType tagLevel, + bool isIdentifierTag, + size_t index, + Orthanc::ResourceType requestLevel) + { + std::string orderArg = "order" + boost::lexical_cast(index); + + target.clear(); + + if (tagLevel == Orthanc::ResourceType_Patient && requestLevel == Orthanc::ResourceType_Study) + { // Patient tags are copied at study level + tagLevel = Orthanc::ResourceType_Study; + } + + std::string tagTable; + if (isIdentifierTag) + { + tagTable = "DicomIdentifiers "; + } + else + { + tagTable = "MainDicomTags "; + } + + std::string tagFilter = orderArg + ".tagGroup = " + boost::lexical_cast(tagGroup) + " AND " + orderArg + ".tagElement = " + boost::lexical_cast(tagElement); + + if (tagLevel == requestLevel) + { + target = " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + FormatLevel(requestLevel) + + ".internalId AND " + tagFilter; + } + else if (static_cast(requestLevel) - static_cast(tagLevel) == 1) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "parent.internalId AND " + tagFilter; + } + else if (static_cast(requestLevel) - static_cast(tagLevel) == 2) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandparent.internalId AND " + tagFilter; + } + else if (static_cast(requestLevel) - static_cast(tagLevel) == 3) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId " + " INNER JOIN Resources " + orderArg + "grandgrandparent ON " + orderArg + "grandgrandparent.internalId = " + orderArg + "grandparent.parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandgrandparent.internalId AND " + tagFilter; + } + } + + static void FormatJoinForOrdering(std::string& target, + int32_t metadata, + size_t index, + Orthanc::ResourceType requestLevel) + { + std::string arg = "order" + boost::lexical_cast(index); + + target = " INNER JOIN Metadata " + arg + " ON " + arg + ".id = " + FormatLevel(requestLevel) + + ".internalId AND " + arg + ".type = " + + boost::lexical_cast(metadata); + } + static std::string Join(const std::list& values, const std::string& prefix, @@ -643,6 +838,24 @@ } } + static const std::string& GetOrthancIdentifier(const Orthanc::DatabasePluginMessages::Find_Request& request, Orthanc::ResourceType level) + { + switch (level) + { + case Orthanc::ResourceType::ResourceType_Patient: + return request.orthanc_id_patient(); + case Orthanc::ResourceType::ResourceType_Study: + return request.orthanc_id_study(); + case Orthanc::ResourceType::ResourceType_Series: + return request.orthanc_id_series(); + case Orthanc::ResourceType::ResourceType_Instance: + return request.orthanc_id_instance(); + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + } + + void ISqlLookupFormatter::Apply(std::string& sql, ISqlLookupFormatter& formatter, const Orthanc::DatabasePluginMessages::Find_Request& request) @@ -664,7 +877,53 @@ 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 + std::string ordering; + std::string orderingJoins; + + if (request.ordering_size() > 0) + { + std::vector orderByFields; + + for (int i = 0; i < request.ordering_size(); ++i) + { + std::string orderingJoin; + const Orthanc::DatabasePluginMessages::Find_Request_Ordering& ordering = request.ordering(i); + + switch (ordering.key_type()) + { + case Orthanc::DatabasePluginMessages::OrderingKeyType::ORDERING_KEY_TYPE_DICOM_TAG: + FormatJoinForOrdering(orderingJoin, ordering.tag_group(), ordering.tag_element(), MessagesToolbox::Convert(ordering.tag_level()), ordering.is_identifier_tag(), i, queryLevel); + break; + case Orthanc::DatabasePluginMessages::OrderingKeyType::ORDERING_KEY_TYPE_METADATA: + FormatJoinForOrdering(orderingJoin, ordering.metadata(), i, queryLevel); + break; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + + orderingJoins += orderingJoin; + + std::string orderByField = "order" + boost::lexical_cast(i) + ".value"; + if (ordering.direction() == Orthanc::DatabasePluginMessages::OrderingDirection::ORDERING_DIRECTION_ASC) + { + orderByField += " ASC"; + } + else + { + orderByField += " DESC"; + } + + orderByFields.push_back(orderByField); + } + + std::string orderByFieldsString; + Orthanc::Toolbox::JoinStrings(orderByFieldsString, orderByFields, ", "); + ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + " NULLS LAST) AS rowNumber"; + } + else + { + 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, " + @@ -680,84 +939,79 @@ !request.orthanc_id_series().empty() || !request.orthanc_id_instance().empty()); - if (isOrthancIdentifiersDefined && - Orthanc::IsResourceLevelAboveOrEqual(DetectLevel(request), queryLevel)) + // handle parent constraints + if (isOrthancIdentifiersDefined && Orthanc::IsResourceLevelAboveOrEqual(DetectLevel(request), queryLevel)) { - // single child resource matching, there should not be other constraints (at least for now) - if (request.dicom_tag_constraints().size() != 0 || - request.labels().size() != 0 || - request.has_limits()) - { - throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); - } + Orthanc::ResourceType topParentLevel = DetectLevel(request); - Orthanc::ResourceType topParentLevel = DetectLevel(request); - const std::string& strTopParentLevel = FormatLevel(topParentLevel); - - std::string publicId; - switch (topParentLevel) + if (topParentLevel == queryLevel) { - case Orthanc::ResourceType_Patient: - publicId = request.orthanc_id_patient(); - break; - - case Orthanc::ResourceType_Study: - publicId = request.orthanc_id_study(); - break; - - case Orthanc::ResourceType_Series: - publicId = request.orthanc_id_series(); - break; - - case Orthanc::ResourceType_Instance: - publicId = request.orthanc_id_instance(); - break; - - default: - throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + comparisons += " AND " + FormatLevel(topParentLevel) + ".publicId = " + formatter.GenerateParameter(GetOrthancIdentifier(request, topParentLevel)); } + else + { + comparisons += " AND " + FormatLevel("parent", topParentLevel) + ".publicId = " + formatter.GenerateParameter(GetOrthancIdentifier(request, topParentLevel)); - if (publicId.empty()) - { - throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); - } - - comparisons = " AND " + strTopParentLevel + ".publicId = " + formatter.GenerateParameter(publicId); - - for (int level = queryLevel; level > topParentLevel; level--) - { - sql += (" INNER JOIN Resources " + - FormatLevel(static_cast(level - 1)) + " ON " + - FormatLevel(static_cast(level - 1)) + ".internalId=" + - FormatLevel(static_cast(level)) + ".parentId"); - } - } - else - { - size_t count = 0; - - for (size_t i = 0; i < constraints.GetSize(); i++) - { - const DatabaseConstraint& constraint = constraints.GetConstraint(i); - - std::string comparison; - - if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets)) + for (int level = queryLevel; level > topParentLevel; level--) { - std::string join; - FormatJoin(join, constraint, count); - joins += join; - - if (!comparison.empty()) + joins += " INNER JOIN Resources " + + FormatLevel("parent", static_cast(level - 1)) + " ON " + + FormatLevel("parent", static_cast(level - 1)) + ".internalId = "; + if (level == queryLevel) { - comparisons += " AND " + comparison; + joins += FormatLevel(static_cast(level)) + ".parentId"; } - - count ++; + else + { + joins += FormatLevel("parent", static_cast(level)) + ".parentId"; + } } } } + size_t count = 0; + + for (size_t i = 0; i < constraints.GetSize(); i++) + { + const DatabaseConstraint& constraint = constraints.GetConstraint(i); + + std::string comparison; + + if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets)) + { + std::string join; + FormatJoin(join, constraint, count); + joins += join; + + if (!comparison.empty()) + { + comparisons += " AND " + comparison; + } + + count ++; + } + } + + for (int i = 0; i < request.metadata_constraints_size(); i++) + { + std::string comparison; + + if (FormatComparison(comparison, formatter, request.metadata_constraints(i), count, escapeBrackets)) + { + std::string join; + FormatJoin(join, request.metadata_constraints(i), queryLevel, count); + joins += join; + + if (!comparison.empty()) + { + comparisons += " AND " + comparison; + } + + count ++; + } + } + + for (int level = queryLevel - 1; level >= upperLevel; level--) { sql += (" INNER JOIN Resources " + @@ -817,7 +1071,7 @@ ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); } - sql += joins + Join(where, " WHERE ", " AND "); + sql += joins + orderingJoins + Join(where, " WHERE ", " AND "); if (request.has_limits()) {