# HG changeset patch # User Alain Mazy # Date 1687428948 -7200 # Node ID f2d3b5c5a68d52a3500a8bb55f4cc25c8da7b355 # Parent afb30d17f0900e3efe54c069435b4a16e5e25902 Optimizing tools/find at studies level only. Integ Tests are ok with PG diff -r afb30d17f090 -r f2d3b5c5a68d .hgignore --- a/.hgignore Sun Apr 16 14:46:17 2023 +0200 +++ b/.hgignore Thu Jun 22 12:15:48 2023 +0200 @@ -3,5 +3,6 @@ PostgreSQL/ThirdPartyDownloads/ Odbc/ThirdPartyDownloads/ MySQL/ThirdPartyDownloads/ +SQLite/ThirdPartyDownloads/ .vscode/ diff -r afb30d17f090 -r f2d3b5c5a68d Framework/Plugins/IndexBackend.cpp --- a/Framework/Plugins/IndexBackend.cpp Sun Apr 16 14:46:17 2023 +0200 +++ b/Framework/Plugins/IndexBackend.cpp Thu Jun 22 12:15:48 2023 +0200 @@ -2072,41 +2072,63 @@ LookupFormatter formatter(manager.GetDialect()); std::string sql; - Orthanc::ISqlLookupFormatter::Apply(sql, formatter, lookup, Orthanc::Plugins::Convert(queryLevel), - labels, labelsConstraint, limit); - - if (requestSomeInstance) + bool enableNewStudyCode = true; + + if (enableNewStudyCode && queryLevel == OrthancPluginResourceType_Study) { - // Composite query to find some instance if requested - switch (queryLevel) + // separate path for the studies since it has been specifically optimized + Orthanc::ISqlLookupFormatter::ApplyExperimental(sql, formatter, lookup, Orthanc::Plugins::Convert(queryLevel), labels, labelsConstraint, limit); + + if (requestSomeInstance) + { + sql = ("SELECT studies_series.studies_public_id, MIN(instances.publicId) AS instances_public_id " + "FROM (SELECT studies.publicId AS studies_public_id, MIN(series.internalId) AS series_internal_id " + "FROM (" + sql + + ") AS studies " + "INNER JOIN Resources series ON series.parentId = studies.internalId " + "GROUP BY studies.publicId " + ") AS studies_series " + "INNER JOIN Resources instances ON instances.parentId = studies_series.series_internal_id " + "GROUP BY studies_series.studies_public_id"); + } + } + else + { + Orthanc::ISqlLookupFormatter::Apply(sql, formatter, lookup, Orthanc::Plugins::Convert(queryLevel), + labels, labelsConstraint, limit); + + if (requestSomeInstance) { - case OrthancPluginResourceType_Patient: - sql = ("SELECT patients.publicId, MIN(instances.publicId) FROM (" + sql + ") patients " - "INNER JOIN Resources studies ON studies.parentId = patients.internalId " - "INNER JOIN Resources series ON series.parentId = studies.internalId " - "INNER JOIN Resources instances ON instances.parentId = series.internalId " - "GROUP BY patients.publicId"); - break; - - case OrthancPluginResourceType_Study: - sql = ("SELECT studies.publicId, MIN(instances.publicId) FROM (" + sql + ") studies " - "INNER JOIN Resources series ON series.parentId = studies.internalId " - "INNER JOIN Resources instances ON instances.parentId = series.internalId " - "GROUP BY studies.publicId"); - break; - - case OrthancPluginResourceType_Series: - sql = ("SELECT series.publicId, MIN(instances.publicId) FROM (" + sql + ") series " - "INNER JOIN Resources instances ON instances.parentId = series.internalId " - "GROUP BY series.publicId"); - break; - - case OrthancPluginResourceType_Instance: - sql = ("SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances"); - break; - - default: - throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + // Composite query to find some instance if requested + switch (queryLevel) + { + case OrthancPluginResourceType_Patient: + sql = ("SELECT patients.publicId, MIN(instances.publicId) FROM (" + sql + ") patients " + "INNER JOIN Resources studies ON studies.parentId = patients.internalId " + "INNER JOIN Resources series ON series.parentId = studies.internalId " + "INNER JOIN Resources instances ON instances.parentId = series.internalId " + "GROUP BY patients.publicId"); + break; + + case OrthancPluginResourceType_Study: + sql = ("SELECT studies.publicId, MIN(instances.publicId) FROM (" + sql + ") studies " + "INNER JOIN Resources series ON series.parentId = studies.internalId " + "INNER JOIN Resources instances ON instances.parentId = series.internalId " + "GROUP BY studies.publicId"); + break; + case OrthancPluginResourceType_Series: + sql = ("SELECT series.publicId, MIN(instances.publicId) FROM (" + sql + ") series " + "INNER JOIN Resources instances ON instances.parentId = series.internalId " + "GROUP BY series.publicId"); + break; + + case OrthancPluginResourceType_Instance: + sql = ("SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances"); + break; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } } } diff -r afb30d17f090 -r f2d3b5c5a68d Resources/Orthanc/Databases/ISqlLookupFormatter.cpp --- 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 #include +#include 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(index); + + 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(); + + 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(limit); } } + + + void ISqlLookupFormatter::ApplyExperimental(std::string& sql, + ISqlLookupFormatter& formatter, + const std::vector& lookup, + ResourceType queryLevel, + const std::set& 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 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 formattedLabels; + for (std::set::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(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(limit); + } + } + } diff -r afb30d17f090 -r f2d3b5c5a68d Resources/Orthanc/Databases/ISqlLookupFormatter.h --- a/Resources/Orthanc/Databases/ISqlLookupFormatter.h Sun Apr 16 14:46:17 2023 +0200 +++ b/Resources/Orthanc/Databases/ISqlLookupFormatter.h Thu Jun 22 12:15:48 2023 +0200 @@ -70,5 +70,13 @@ const std::set& labels, // New in Orthanc 1.12.0 LabelsConstraint labelsConstraint, // New in Orthanc 1.12.0 size_t limit); + + static void ApplyExperimental(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); }; }