# HG changeset patch # User Alain Mazy # Date 1699904144 -3600 # Node ID 7f37233359aa93f92f2c500e657124a142778096 # Parent ac4e9fb8761550ae1531d8eedfb0149bb6abf650 sync code from DB plugins diff -r ac4e9fb87615 -r 7f37233359aa OrthancServer/Sources/Search/ISqlLookupFormatter.cpp --- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Thu Nov 09 08:51:01 2023 +0100 +++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Mon Nov 13 20:35:44 2023 +0100 @@ -32,8 +32,10 @@ #if ORTHANC_BUILDING_SERVER_LIBRARY == 1 # include "../../../OrthancFramework/Sources/OrthancException.h" +# include "../../../OrthancFramework/Sources/Toolbox.h" #else # include +# include #endif #include "DatabaseConstraint.h" @@ -301,22 +303,184 @@ return s; } } - + + static bool FormatComparison2(std::string& target, + ISqlLookupFormatter& formatter, + const DatabaseConstraint& constraint, + bool escapeBrackets) + { + std::string comparison; + std::string tagFilter = ("tagGroup = " + boost::lexical_cast(constraint.GetTag().GetGroup()) + + " AND tagElement = " + boost::lexical_cast(constraint.GetTag().GetElement())); + + switch (constraint.GetConstraintType()) + { + case ConstraintType_Equal: + case ConstraintType_SmallerOrEqual: + case ConstraintType_GreaterOrEqual: + { + std::string op; + switch (constraint.GetConstraintType()) + { + case ConstraintType_Equal: + op = "="; + break; + + case ConstraintType_SmallerOrEqual: + op = "<="; + break; + + case ConstraintType_GreaterOrEqual: + op = ">="; + break; + + default: + throw OrthancException(ErrorCode_InternalError); + } + + std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue()); + + if (constraint.IsCaseSensitive()) + { + comparison = " AND value " + op + " " + parameter; + } + else + { + comparison = " AND lower(value) " + op + " lower(" + parameter + ")"; + } + + break; + } + + case ConstraintType_List: + { + std::vector comparisonValues; + for (size_t i = 0; i < constraint.GetValuesCount(); i++) + { + std::string parameter = formatter.GenerateParameter(constraint.GetValue(i)); + + if (constraint.IsCaseSensitive()) + { + comparisonValues.push_back(parameter); + } + else + { + comparisonValues.push_back("lower(" + parameter + ")"); + } + } + + std::string values; + Toolbox::JoinStrings(values, comparisonValues, ", "); + + if (constraint.IsCaseSensitive()) + { + comparison = " AND value IN (" + values + ")"; + } + else + { + comparison = " AND lower(value) IN (" + values + ")"; + } + + break; + } + + case ConstraintType_Wildcard: + { + const std::string value = constraint.GetSingleValue(); - void ISqlLookupFormatter::Apply(std::string& sql, - ISqlLookupFormatter& formatter, - const std::vector& lookup, - ResourceType queryLevel, - const std::set& labels, - LabelsConstraint labelsConstraint, - size_t limit) + if (value == "*") + { + if (!constraint.IsMandatory()) + { + // Universal constraint on an optional tag, ignore it + return false; + } + } + else + { + std::string escaped; + escaped.reserve(value.size()); + + for (size_t i = 0; i < value.size(); i++) + { + if (value[i] == '*') + { + escaped += "%"; + } + else if (value[i] == '?') + { + escaped += "_"; + } + else if (value[i] == '%') + { + escaped += "\\%"; + } + else if (value[i] == '_') + { + escaped += "\\_"; + } + else if (value[i] == '\\') + { + escaped += "\\\\"; + } + else if (escapeBrackets && value[i] == '[') + { + escaped += "\\["; + } + else if (escapeBrackets && value[i] == ']') + { + escaped += "\\]"; + } + else + { + escaped += value[i]; + } + } + + std::string parameter = formatter.GenerateParameter(escaped); + + if (constraint.IsCaseSensitive()) + { + comparison = " AND value LIKE " + parameter + " " + formatter.FormatWildcardEscape(); + } + else + { + comparison = " AND lower(value) LIKE lower(" + parameter + ") " + formatter.FormatWildcardEscape(); + } + } + + break; + } + + default: + return false; + } + + if (constraint.IsMandatory()) + { + target = tagFilter + comparison; + } + else if (comparison.empty()) + { + target = tagFilter + " AND value IS NULL"; + } + else + { + target = tagFilter + " AND value IS NULL OR " + comparison; + } + + return true; + } + + + void ISqlLookupFormatter::GetLookupLevels(ResourceType& lowerLevel, ResourceType& upperLevel, const ResourceType& queryLevel, const std::vector& lookup) { assert(ResourceType_Patient < ResourceType_Study && ResourceType_Study < ResourceType_Series && ResourceType_Series < ResourceType_Instance); - ResourceType upperLevel = queryLevel; - ResourceType lowerLevel = queryLevel; + lowerLevel = queryLevel; + upperLevel = queryLevel; for (size_t i = 0; i < lookup.size(); i++) { @@ -332,7 +496,20 @@ lowerLevel = level; } } - + } + + + void ISqlLookupFormatter::Apply(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector& lookup, + ResourceType queryLevel, + const std::set& labels, + LabelsConstraint labelsConstraint, + size_t limit) + { + ResourceType lowerLevel, upperLevel; + GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup); + assert(upperLevel <= queryLevel && queryLevel <= lowerLevel); @@ -431,4 +608,119 @@ sql += " LIMIT " + boost::lexical_cast(limit); } } + + + void ISqlLookupFormatter::ApplySingleLevel(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector& lookup, + ResourceType queryLevel, + const std::set& labels, + LabelsConstraint labelsConstraint, + size_t limit + ) + { + ResourceType lowerLevel, upperLevel; + GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup); + + assert(upperLevel == queryLevel && + queryLevel == lowerLevel); + + const bool escapeBrackets = formatter.IsEscapeBrackets(); + + std::vector mainDicomTagsComparisons, dicomIdentifiersComparisons; + + for (size_t i = 0; i < lookup.size(); i++) + { + std::string comparison; + + if (FormatComparison2(comparison, formatter, lookup[i], escapeBrackets)) + { + if (!comparison.empty()) + { + if (lookup[i].IsIdentifier()) + { + dicomIdentifiersComparisons.push_back(comparison); + } + else + { + mainDicomTagsComparisons.push_back(comparison); + } + } + } + } + + sql = ("SELECT publicId, internalId " + "FROM Resources " + "WHERE resourceType = " + formatter.FormatResourceType(queryLevel) + + " "); + + if (dicomIdentifiersComparisons.size() > 0) + { + for (std::vector::const_iterator it = dicomIdentifiersComparisons.begin(); it < dicomIdentifiersComparisons.end(); ++it) + { + sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE " + *it + ") "); + } + } + + if (mainDicomTagsComparisons.size() > 0) + { + std::string comparisons; + for (std::vector::const_iterator it = mainDicomTagsComparisons.begin(); it < mainDicomTagsComparisons.end(); ++it) + { + sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE " + *it + ") "); + } + } + + if (!labels.empty()) + { + /** + * "In SQL Server, NOT EXISTS and NOT IN predicates are the best + * way to search for missing values, as long as both columns in + * question are NOT NULL." + * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ + **/ + + std::list formattedLabels; + for (std::set::const_iterator it = labels.begin(); it != labels.end(); ++it) + { + formattedLabels.push_back(formatter.GenerateParameter(*it)); + } + + std::string condition; + std::string inOrNotIn; + switch (labelsConstraint) + { + case LabelsConstraint_Any: + condition = "> 0"; + inOrNotIn = "IN"; + break; + + case LabelsConstraint_All: + condition = "= " + boost::lexical_cast(labels.size()); + inOrNotIn = "IN"; + break; + + case LabelsConstraint_None: + condition = "> 0"; + inOrNotIn = "NOT IN"; + break; + + default: + throw OrthancException(ErrorCode_ParameterOutOfRange); + } + + sql += (" AND internalId " + inOrNotIn + " (SELECT id" + " FROM (SELECT id, COUNT(1) AS labelsCount " + "FROM Labels " + "WHERE label IN (" + Join(formattedLabels, "", ", ") + ") GROUP BY id" + ") AS temp " + " WHERE labelsCount " + condition + ")"); + } + + if (limit != 0) + { + sql += " LIMIT " + boost::lexical_cast(limit); + } + } + } diff -r ac4e9fb87615 -r 7f37233359aa OrthancServer/Sources/Search/ISqlLookupFormatter.h --- a/OrthancServer/Sources/Search/ISqlLookupFormatter.h Thu Nov 09 08:51:01 2023 +0100 +++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.h Mon Nov 13 20:35:44 2023 +0100 @@ -63,6 +63,8 @@ **/ virtual bool IsEscapeBrackets() const = 0; + static void GetLookupLevels(ResourceType& lowerLevel, ResourceType& upperLevel, const ResourceType& queryLevel, const std::vector& lookup); + static void Apply(std::string& sql, ISqlLookupFormatter& formatter, const std::vector& lookup, @@ -70,5 +72,13 @@ const std::set& labels, // New in Orthanc 1.12.0 LabelsConstraint labelsConstraint, // New in Orthanc 1.12.0 size_t limit); + + static void ApplySingleLevel(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector& lookup, + ResourceType queryLevel, + const std::set& labels, // New in Orthanc 1.12.0 + LabelsConstraint labelsConstraint, // New in Orthanc 1.12.0 + size_t limit); }; }