orthanc-databases: Resources/Orthanc/Databases/ISqlLookupFormatter.cpp comparison

comparison 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

comparison

equal deleted inserted replaced

-:afb30d17f090
+:f2d3b5c5a68d
 #include "DatabaseConstraint.h"
 #include <boost/lexical_cast.hpp>
 #include <list>
+#include <Toolbox.h>
 namespace Orthanc
 {
 static std::string FormatLevel(ResourceType level)
 }
 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,
 ISqlLookupFormatter& formatter,
 const std::vector<DatabaseConstraint>& lookup,
 if (limit != 0)
 {
 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);
+}
+}
 }

Mercurial > hg > orthanc-databases

comparison Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 414:f2d3b5c5a68d