Mercurial > hg > orthanc-databases
diff Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 536:4ecf50a4521c find-refactoring
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Fri, 06 Sep 2024 16:56:37 +0200 |
parents | 54d518dcd74a |
children | 12f1c5265081 |
line wrap: on
line diff
--- a/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp Fri Sep 06 15:44:40 2024 +0200 +++ b/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp Fri Sep 06 16:56:37 2024 +0200 @@ -34,6 +34,7 @@ #if ORTHANC_BUILDING_SERVER_LIBRARY == 1 # include "../../../OrthancFramework/Sources/OrthancException.h" # include "../../../OrthancFramework/Sources/Toolbox.h" +# include "../Database/FindRequest.h" #else # include <OrthancException.h> # include <Toolbox.h> @@ -475,7 +476,10 @@ } - void ISqlLookupFormatter::GetLookupLevels(ResourceType& lowerLevel, ResourceType& upperLevel, const ResourceType& queryLevel, const std::vector<DatabaseConstraint>& lookup) + void ISqlLookupFormatter::GetLookupLevels(ResourceType& lowerLevel, + ResourceType& upperLevel, + const ResourceType& queryLevel, + const DatabaseConstraints& lookup) { assert(ResourceType_Patient < ResourceType_Study && ResourceType_Study < ResourceType_Series && @@ -484,9 +488,9 @@ lowerLevel = queryLevel; upperLevel = queryLevel; - for (size_t i = 0; i < lookup.size(); i++) + for (size_t i = 0; i < lookup.GetSize(); i++) { - ResourceType level = lookup[i].GetLevel(); + ResourceType level = lookup.GetConstraint(i).GetLevel(); if (level < upperLevel) { @@ -503,7 +507,7 @@ void ISqlLookupFormatter::Apply(std::string& sql, ISqlLookupFormatter& formatter, - const std::vector<DatabaseConstraint>& lookup, + const DatabaseConstraints& lookup, ResourceType queryLevel, const std::set<std::string>& labels, LabelsConstraint labelsConstraint, @@ -521,14 +525,16 @@ size_t count = 0; - for (size_t i = 0; i < lookup.size(); i++) + for (size_t i = 0; i < lookup.GetSize(); i++) { + const DatabaseConstraint& constraint = lookup.GetConstraint(i); + std::string comparison; - if (FormatComparison(comparison, formatter, lookup[i], count, escapeBrackets)) + if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets)) { std::string join; - FormatJoin(join, lookup[i], count); + FormatJoin(join, constraint, count); joins += join; if (!comparison.empty()) @@ -611,10 +617,151 @@ } } +#if ORTHANC_BUILDING_SERVER_LIBRARY == 1 + void ISqlLookupFormatter::Apply(std::string& sql, + ISqlLookupFormatter& formatter, + const FindRequest& request) + { + const bool escapeBrackets = formatter.IsEscapeBrackets(); + ResourceType queryLevel = request.GetLevel(); + const std::string& strQueryLevel = FormatLevel(queryLevel); + + ResourceType lowerLevel, upperLevel; + GetLookupLevels(lowerLevel, upperLevel, queryLevel, request.GetDicomTagConstraints()); + + assert(upperLevel <= queryLevel && + queryLevel <= lowerLevel); + + + sql = ("SELECT " + + strQueryLevel + ".publicId, " + + strQueryLevel + ".internalId" + + " FROM Resources AS " + strQueryLevel); + + + std::string joins, comparisons; + + if (request.GetOrthancIdentifiers().IsDefined() && request.GetOrthancIdentifiers().DetectLevel() <= queryLevel) + { + // single child resource matching, there should not be other constraints (at least for now) + assert(request.GetDicomTagConstraints().GetSize() == 0); + assert(request.GetLabels().size() == 0); + assert(request.HasLimits() == false); + + ResourceType topParentLevel = request.GetOrthancIdentifiers().DetectLevel(); + const std::string& strTopParentLevel = FormatLevel(topParentLevel); + + comparisons = " AND " + strTopParentLevel + ".publicId = " + formatter.GenerateParameter(request.GetOrthancIdentifiers().GetLevel(topParentLevel)); + + for (int level = queryLevel; level > topParentLevel; level--) + { + sql += (" INNER JOIN Resources " + + FormatLevel(static_cast<ResourceType>(level - 1)) + " ON " + + FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + + FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); + } + } + else + { + size_t count = 0; + + const DatabaseConstraints& dicomTagsConstraints = request.GetDicomTagConstraints(); + for (size_t i = 0; i < dicomTagsConstraints.GetSize(); i++) + { + const DatabaseConstraint& constraint = dicomTagsConstraints.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 level = queryLevel - 1; level >= upperLevel; level--) + { + sql += (" INNER JOIN Resources " + + FormatLevel(static_cast<ResourceType>(level)) + " ON " + + FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" + + FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId"); + } + + for (int level = queryLevel + 1; level <= lowerLevel; level++) + { + sql += (" INNER JOIN Resources " + + FormatLevel(static_cast<ResourceType>(level)) + " ON " + + FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + + FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); + } + + std::list<std::string> where; + where.push_back(strQueryLevel + ".resourceType = " + + formatter.FormatResourceType(queryLevel) + comparisons); + + + if (!request.GetLabels().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/ + **/ + + const std::set<std::string>& labels = request.GetLabels(); + 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 (request.GetLabelsConstraint()) + { + 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); + } + + where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel + + ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); + } + + sql += joins + Join(where, " WHERE ", " AND "); + + if (request.HasLimits()) + { + sql += formatter.FormatLimits(request.GetLimitsSince(), request.GetLimitsCount()); + } + + } +#endif + void ISqlLookupFormatter::ApplySingleLevel(std::string& sql, ISqlLookupFormatter& formatter, - const std::vector<DatabaseConstraint>& lookup, + const DatabaseConstraints& lookup, ResourceType queryLevel, const std::set<std::string>& labels, LabelsConstraint labelsConstraint, @@ -631,15 +778,17 @@ std::vector<std::string> mainDicomTagsComparisons, dicomIdentifiersComparisons; - for (size_t i = 0; i < lookup.size(); i++) + for (size_t i = 0; i < lookup.GetSize(); i++) { + const DatabaseConstraint& constraint = lookup.GetConstraint(i); + std::string comparison; - if (FormatComparison2(comparison, formatter, lookup[i], escapeBrackets)) + if (FormatComparison2(comparison, formatter, constraint, escapeBrackets)) { if (!comparison.empty()) { - if (lookup[i].IsIdentifier()) + if (constraint.IsIdentifier()) { dicomIdentifiersComparisons.push_back(comparison); }