Mercurial > hg > orthanc-databases
diff Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 414:f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
author | Alain Mazy <am@osimis.io> |
---|---|
date | Thu, 22 Jun 2023 12:15:48 +0200 |
parents | de6de66d70b2 |
children | 7e123f047771 |
line wrap: on
line diff
--- a/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp Sun Apr 16 14:46:17 2023 +0200 +++ b/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp Thu Jun 22 12:15:48 2023 +0200 @@ -40,6 +40,7 @@ #include <boost/lexical_cast.hpp> #include <list> +#include <Toolbox.h> namespace Orthanc @@ -301,6 +302,180 @@ return s; } } + + static bool FormatComparisonExperimental(std::string& target, + ISqlLookupFormatter& formatter, + const DatabaseConstraint& constraint, + //size_t index, + bool escapeBrackets) + { + //std::string tag = "t" + boost::lexical_cast<std::string>(index); + + std::string comparison; + std::string tagFilter = ("tagGroup = " + boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) + + " AND tagElement = " + boost::lexical_cast<std::string>(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<std::string> 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(); + + 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::Apply(std::string& sql, @@ -431,4 +606,133 @@ sql += " LIMIT " + boost::lexical_cast<std::string>(limit); } } + + + void ISqlLookupFormatter::ApplyExperimental(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector<DatabaseConstraint>& lookup, + ResourceType queryLevel, + const std::set<std::string>& labels, + LabelsConstraint labelsConstraint, + size_t limit + ) + { + assert(ResourceType_Patient < ResourceType_Study && + ResourceType_Study < ResourceType_Series && + ResourceType_Series < ResourceType_Instance); + + ResourceType upperLevel = queryLevel; + ResourceType lowerLevel = queryLevel; + + for (size_t i = 0; i < lookup.size(); i++) + { + ResourceType level = lookup[i].GetLevel(); + + if (level < upperLevel) + { + upperLevel = level; + } + + if (level > lowerLevel) + { + lowerLevel = level; + } + } + + assert(upperLevel <= queryLevel && + queryLevel <= lowerLevel); + + const bool escapeBrackets = formatter.IsEscapeBrackets(); + + std::vector<std::string> mainDicomTagsComparisons, dicomIdentifiersComparisons; + + for (size_t i = 0; i < lookup.size(); i++) + { + std::string comparison; + + if (FormatComparisonExperimental(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) + { + std::string comparisons; + Toolbox::JoinStrings(comparisons, dicomIdentifiersComparisons, " AND "); + sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE " + + comparisons + ") "); + } + + if (mainDicomTagsComparisons.size() > 0) + { + std::string comparisons; + Toolbox::JoinStrings(comparisons, mainDicomTagsComparisons, " AND "); + sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE " + + comparisons + ") "); + } + + 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<std::string> formattedLabels; + for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it) + { + formattedLabels.push_back(formatter.GenerateParameter(*it)); + } + + std::string condition; + switch (labelsConstraint) + { + case LabelsConstraint_Any: + condition = "> 0"; + break; + + case LabelsConstraint_All: + condition = "= " + boost::lexical_cast<std::string>(labels.size()); + break; + + case LabelsConstraint_None: + condition = "= 0"; + break; + + default: + throw OrthancException(ErrorCode_ParameterOutOfRange); + } + + sql += (" AND internalId IN (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<std::string>(limit); + } + } + }