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

annotate Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 501:594859656a06 large-queries

Added support for ExtendedApiV1: /changes

author	Alain Mazy <am@orthanc.team>
date	Thu, 11 Apr 2024 18:52:42 +0200
parents	ecd0b719cff5
children	54d518dcd74a

rev	line source
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	1 /**
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	2 * Orthanc - A Lightweight, RESTful DICOM Store
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	4 * Department, University Hospital of Liege, Belgium
459 ecd0b719cff5 update year to 2024 Sebastien Jodogne <s.jodogne@gmail.com> parents: 425 diff changeset	5 * Copyright (C) 2017-2024 Osimis S.A., Belgium
ecd0b719cff5 update year to 2024 Sebastien Jodogne <s.jodogne@gmail.com> parents: 425 diff changeset	6 * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	7 *
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	8 * This program is free software: you can redistribute it and/or
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	9 * modify it under the terms of the GNU General Public License as
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	10 * published by the Free Software Foundation, either version 3 of the
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	11 * License, or (at your option) any later version.
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	12 *
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	13 * This program is distributed in the hope that it will be useful, but
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	14 * WITHOUT ANY WARRANTY; without even the implied warranty of
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	15 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	16 * General Public License for more details.
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	17 *
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	18 * You should have received a copy of the GNU General Public License
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	19 * along with this program. If not, see <http://www.gnu.org/licenses/>.
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	20 **/
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	21
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	22
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	23 #if !defined(ORTHANC_BUILDING_SERVER_LIBRARY)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	24 # error Macro ORTHANC_BUILDING_SERVER_LIBRARY must be defined
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	25 #endif
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	26
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	27 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	28 # include "../PrecompiledHeadersServer.h"
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	29 #endif
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	30
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	31 #include "ISqlLookupFormatter.h"
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	32
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	33 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	34 # include "../../../OrthancFramework/Sources/OrthancException.h"
425 c1b0f3c4e1f5 sync Orthanc code Alain Mazy <am@osimis.io> parents: 416 diff changeset	35 # include "../../../OrthancFramework/Sources/Toolbox.h"
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	36 #else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	37 # include <OrthancException.h>
425 c1b0f3c4e1f5 sync Orthanc code Alain Mazy <am@osimis.io> parents: 416 diff changeset	38 # include <Toolbox.h>
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	39 #endif
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	40
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	41 #include "DatabaseConstraint.h"
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	42
459 ecd0b719cff5 update year to 2024 Sebastien Jodogne <s.jodogne@gmail.com> parents: 425 diff changeset	43 #include <cassert>
170 e712ff3eede3 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 152 diff changeset	44 #include <boost/lexical_cast.hpp>
405 1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	45 #include <list>
170 e712ff3eede3 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 152 diff changeset	46
e712ff3eede3 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 152 diff changeset	47
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	48 namespace Orthanc
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	49 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	50 static std::string FormatLevel(ResourceType level)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	51 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	52 switch (level)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	53 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	54 case ResourceType_Patient:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	55 return "patients";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	56
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	57 case ResourceType_Study:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	58 return "studies";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	59
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	60 case ResourceType_Series:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	61 return "series";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	62
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	63 case ResourceType_Instance:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	64 return "instances";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	65
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	66 default:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	67 throw OrthancException(ErrorCode_InternalError);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	68 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	69 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	70
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	71
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	72 static bool FormatComparison(std::string& target,
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	73 ISqlLookupFormatter& formatter,
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	74 const DatabaseConstraint& constraint,
354 2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	75 size_t index,
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	76 bool escapeBrackets)
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	77 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	78 std::string tag = "t" + boost::lexical_cast<std::string>(index);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	79
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	80 std::string comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	81
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	82 switch (constraint.GetConstraintType())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	83 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	84 case ConstraintType_Equal:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	85 case ConstraintType_SmallerOrEqual:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	86 case ConstraintType_GreaterOrEqual:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	87 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	88 std::string op;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	89 switch (constraint.GetConstraintType())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	90 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	91 case ConstraintType_Equal:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	92 op = "=";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	93 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	94
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	95 case ConstraintType_SmallerOrEqual:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	96 op = "<=";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	97 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	98
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	99 case ConstraintType_GreaterOrEqual:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	100 op = ">=";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	101 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	102
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	103 default:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	104 throw OrthancException(ErrorCode_InternalError);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	105 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	106
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	107 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	108
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	109 if (constraint.IsCaseSensitive())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	110 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	111 comparison = tag + ".value " + op + " " + parameter;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	112 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	113 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	114 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	115 comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	116 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	117
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	118 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	119 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	120
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	121 case ConstraintType_List:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	122 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	123 for (size_t i = 0; i < constraint.GetValuesCount(); i++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	124 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	125 if (!comparison.empty())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	126 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	127 comparison += ", ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	128 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	129
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	130 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	131
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	132 if (constraint.IsCaseSensitive())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	133 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	134 comparison += parameter;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	135 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	136 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	137 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	138 comparison += "lower(" + parameter + ")";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	139 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	140 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	141
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	142 if (constraint.IsCaseSensitive())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	143 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	144 comparison = tag + ".value IN (" + comparison + ")";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	145 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	146 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	147 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	148 comparison = "lower(" + tag + ".value) IN (" + comparison + ")";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	149 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	150
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	151 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	152 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	153
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	154 case ConstraintType_Wildcard:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	155 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	156 const std::string value = constraint.GetSingleValue();
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	157
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	158 if (value == "*")
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	159 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	160 if (!constraint.IsMandatory())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	161 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	162 // Universal constraint on an optional tag, ignore it
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	163 return false;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	164 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	165 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	166 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	167 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	168 std::string escaped;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	169 escaped.reserve(value.size());
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	170
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	171 for (size_t i = 0; i < value.size(); i++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	172 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	173 if (value[i] == '*')
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	174 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	175 escaped += "%";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	176 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	177 else if (value[i] == '?')
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	178 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	179 escaped += "_";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	180 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	181 else if (value[i] == '%')
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	182 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	183 escaped += "\\%";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	184 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	185 else if (value[i] == '_')
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	186 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	187 escaped += "\\_";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	188 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	189 else if (value[i] == '\\')
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	190 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	191 escaped += "\\\\";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	192 }
354 2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	193 else if (escapeBrackets && value[i] == '[')
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	194 {
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	195 escaped += "\\[";
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	196 }
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	197 else if (escapeBrackets && value[i] == ']')
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	198 {
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	199 escaped += "\\]";
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	200 }
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	201 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	202 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	203 escaped += value[i];
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	204 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	205 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	206
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	207 std::string parameter = formatter.GenerateParameter(escaped);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	208
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	209 if (constraint.IsCaseSensitive())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	210 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	211 comparison = (tag + ".value LIKE " + parameter + " " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	212 formatter.FormatWildcardEscape());
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	213 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	214 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	215 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	216 comparison = ("lower(" + tag + ".value) LIKE lower(" +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	217 parameter + ") " + formatter.FormatWildcardEscape());
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	218 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	219 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	220
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	221 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	222 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	223
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	224 default:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	225 return false;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	226 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	227
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	228 if (constraint.IsMandatory())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	229 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	230 target = comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	231 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	232 else if (comparison.empty())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	233 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	234 target = tag + ".value IS NULL";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	235 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	236 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	237 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	238 target = tag + ".value IS NULL OR " + comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	239 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	240
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	241 return true;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	242 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	243
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	244
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	245 static void FormatJoin(std::string& target,
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	246 const DatabaseConstraint& constraint,
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	247 size_t index)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	248 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	249 std::string tag = "t" + boost::lexical_cast<std::string>(index);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	250
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	251 if (constraint.IsMandatory())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	252 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	253 target = " INNER JOIN ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	254 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	255 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	256 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	257 target = " LEFT JOIN ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	258 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	259
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	260 if (constraint.IsIdentifier())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	261 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	262 target += "DicomIdentifiers ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	263 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	264 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	265 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	266 target += "MainDicomTags ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	267 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	268
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	269 target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	270 ".internalId AND " + tag + ".tagGroup = " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	271 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	272 " AND " + tag + ".tagElement = " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	273 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	274 }
405 1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	275
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	276
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	277 static std::string Join(const std::list<std::string>& values,
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	278 const std::string& prefix,
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	279 const std::string& separator)
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	280 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	281 if (values.empty())
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	282 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	283 return "";
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	284 }
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	285 else
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	286 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	287 std::string s = prefix;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	288
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	289 bool first = true;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	290 for (std::list<std::string>::const_iterator it = values.begin(); it != values.end(); ++it)
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	291 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	292 if (first)
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	293 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	294 first = false;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	295 }
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	296 else
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	297 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	298 s += separator;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	299 }
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	300
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	301 s += *it;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	302 }
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	303
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	304 return s;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	305 }
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	306 }
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	307
425 c1b0f3c4e1f5 sync Orthanc code Alain Mazy <am@osimis.io> parents: 416 diff changeset	308 static bool FormatComparison2(std::string& target,
c1b0f3c4e1f5 sync Orthanc code Alain Mazy <am@osimis.io> parents: 416 diff changeset	309 ISqlLookupFormatter& formatter,
c1b0f3c4e1f5 sync Orthanc code Alain Mazy <am@osimis.io> parents: 416 diff changeset	310 const DatabaseConstraint& constraint,
c1b0f3c4e1f5 sync Orthanc code Alain Mazy <am@osimis.io> parents: 416 diff changeset	311 bool escapeBrackets)
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	312 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	313 std::string comparison;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	314 std::string tagFilter = ("tagGroup = " + boost::lexical_cast<std::string>(constraint.GetTag().GetGroup())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	315 + " AND tagElement = " + boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	316
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	317 switch (constraint.GetConstraintType())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	318 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	319 case ConstraintType_Equal:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	320 case ConstraintType_SmallerOrEqual:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	321 case ConstraintType_GreaterOrEqual:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	322 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	323 std::string op;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	324 switch (constraint.GetConstraintType())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	325 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	326 case ConstraintType_Equal:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	327 op = "=";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	328 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	329
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	330 case ConstraintType_SmallerOrEqual:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	331 op = "<=";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	332 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	333
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	334 case ConstraintType_GreaterOrEqual:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	335 op = ">=";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	336 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	337
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	338 default:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	339 throw OrthancException(ErrorCode_InternalError);
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	340 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	341
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	342 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	343
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	344 if (constraint.IsCaseSensitive())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	345 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	346 comparison = " AND value " + op + " " + parameter;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	347 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	348 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	349 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	350 comparison = " AND lower(value) " + op + " lower(" + parameter + ")";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	351 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	352
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	353 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	354 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	355
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	356 case ConstraintType_List:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	357 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	358 std::vector<std::string> comparisonValues;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	359 for (size_t i = 0; i < constraint.GetValuesCount(); i++)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	360 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	361 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	362
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	363 if (constraint.IsCaseSensitive())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	364 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	365 comparisonValues.push_back(parameter);
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	366 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	367 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	368 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	369 comparisonValues.push_back("lower(" + parameter + ")");
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	370 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	371 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	372
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	373 std::string values;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	374 Toolbox::JoinStrings(values, comparisonValues, ", ");
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	375
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	376 if (constraint.IsCaseSensitive())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	377 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	378 comparison = " AND value IN (" + values + ")";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	379 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	380 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	381 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	382 comparison = " AND lower(value) IN (" + values + ")";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	383 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	384
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	385 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	386 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	387
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	388 case ConstraintType_Wildcard:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	389 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	390 const std::string value = constraint.GetSingleValue();
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	391
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	392 if (value == "*")
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	393 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	394 if (!constraint.IsMandatory())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	395 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	396 // Universal constraint on an optional tag, ignore it
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	397 return false;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	398 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	399 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	400 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	401 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	402 std::string escaped;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	403 escaped.reserve(value.size());
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	404
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	405 for (size_t i = 0; i < value.size(); i++)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	406 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	407 if (value[i] == '*')
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	408 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	409 escaped += "%";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	410 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	411 else if (value[i] == '?')
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	412 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	413 escaped += "_";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	414 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	415 else if (value[i] == '%')
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	416 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	417 escaped += "\\%";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	418 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	419 else if (value[i] == '_')
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	420 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	421 escaped += "\\_";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	422 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	423 else if (value[i] == '\\')
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	424 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	425 escaped += "\\\\";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	426 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	427 else if (escapeBrackets && value[i] == '[')
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	428 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	429 escaped += "\\[";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	430 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	431 else if (escapeBrackets && value[i] == ']')
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	432 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	433 escaped += "\\]";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	434 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	435 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	436 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	437 escaped += value[i];
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	438 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	439 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	440
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	441 std::string parameter = formatter.GenerateParameter(escaped);
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	442
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	443 if (constraint.IsCaseSensitive())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	444 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	445 comparison = " AND value LIKE " + parameter + " " + formatter.FormatWildcardEscape();
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	446 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	447 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	448 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	449 comparison = " AND lower(value) LIKE lower(" + parameter + ") " + formatter.FormatWildcardEscape();
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	450 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	451 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	452
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	453 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	454 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	455
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	456 default:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	457 return false;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	458 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	459
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	460 if (constraint.IsMandatory())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	461 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	462 target = tagFilter + comparison;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	463 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	464 else if (comparison.empty())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	465 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	466 target = tagFilter + " AND value IS NULL";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	467 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	468 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	469 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	470 target = tagFilter + " AND value IS NULL OR " + comparison;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	471 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	472
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	473 return true;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	474 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	475
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	476
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	477 void ISqlLookupFormatter::GetLookupLevels(ResourceType& lowerLevel, ResourceType& upperLevel, const ResourceType& queryLevel, const std::vector<DatabaseConstraint>& lookup)
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	478 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	479 assert(ResourceType_Patient < ResourceType_Study &&
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	480 ResourceType_Study < ResourceType_Series &&
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	481 ResourceType_Series < ResourceType_Instance);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	482
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	483 lowerLevel = queryLevel;
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	484 upperLevel = queryLevel;
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	485
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	486 for (size_t i = 0; i < lookup.size(); i++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	487 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	488 ResourceType level = lookup[i].GetLevel();
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	489
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	490 if (level < upperLevel)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	491 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	492 upperLevel = level;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	493 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	494
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	495 if (level > lowerLevel)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	496 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	497 lowerLevel = level;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	498 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	499 }
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	500 }
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	501
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	502
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	503 void ISqlLookupFormatter::Apply(std::string& sql,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	504 ISqlLookupFormatter& formatter,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	505 const std::vector<DatabaseConstraint>& lookup,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	506 ResourceType queryLevel,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	507 const std::set<std::string>& labels,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	508 LabelsConstraint labelsConstraint,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	509 size_t limit)
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	510 {
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	511 ResourceType lowerLevel, upperLevel;
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	512 GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup);
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	513
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	514 assert(upperLevel <= queryLevel &&
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	515 queryLevel <= lowerLevel);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	516
354 2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	517 const bool escapeBrackets = formatter.IsEscapeBrackets();
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	518
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	519 std::string joins, comparisons;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	520
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	521 size_t count = 0;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	522
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	523 for (size_t i = 0; i < lookup.size(); i++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	524 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	525 std::string comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	526
354 2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	527 if (FormatComparison(comparison, formatter, lookup[i], count, escapeBrackets))
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	528 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	529 std::string join;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	530 FormatJoin(join, lookup[i], count);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	531 joins += join;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	532
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	533 if (!comparison.empty())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	534 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	535 comparisons += " AND " + comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	536 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	537
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	538 count ++;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	539 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	540 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	541
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	542 sql = ("SELECT " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	543 FormatLevel(queryLevel) + ".publicId, " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	544 FormatLevel(queryLevel) + ".internalId" +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	545 " FROM Resources AS " + FormatLevel(queryLevel));
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	546
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	547 for (int level = queryLevel - 1; level >= upperLevel; level--)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	548 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	549 sql += (" INNER JOIN Resources " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	550 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	551 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	552 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId");
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	553 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	554
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	555 for (int level = queryLevel + 1; level <= lowerLevel; level++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	556 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	557 sql += (" INNER JOIN Resources " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	558 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	559 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	560 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	561 }
405 1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	562
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	563 std::list<std::string> where;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	564 where.push_back(FormatLevel(queryLevel) + ".resourceType = " +
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	565 formatter.FormatResourceType(queryLevel) + comparisons);
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	566
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	567 if (!labels.empty())
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	568 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	569 /**
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	570 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	571 * way to search for missing values, as long as both columns in
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	572 * question are NOT NULL."
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	573 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	574 **/
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	575
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	576 std::list<std::string> formattedLabels;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	577 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it)
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	578 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	579 formattedLabels.push_back(formatter.GenerateParameter(*it));
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	580 }
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	581
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	582 std::string condition;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	583 switch (labelsConstraint)
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	584 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	585 case LabelsConstraint_Any:
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	586 condition = "> 0";
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	587 break;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	588
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	589 case LabelsConstraint_All:
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	590 condition = "= " + boost::lexical_cast<std::string>(labels.size());
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	591 break;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	592
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	593 case LabelsConstraint_None:
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	594 condition = "= 0";
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	595 break;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	596
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	597 default:
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	598 throw OrthancException(ErrorCode_ParameterOutOfRange);
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	599 }
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	600
405 1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	601 where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + FormatLevel(queryLevel) +
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	602 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	603 }
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	604
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	605 sql += joins + Join(where, " WHERE ", " AND ");
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	606
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	607 if (limit != 0)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	608 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	609 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	610 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	611 }
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	612
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	613
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	614 void ISqlLookupFormatter::ApplySingleLevel(std::string& sql,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	615 ISqlLookupFormatter& formatter,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	616 const std::vector<DatabaseConstraint>& lookup,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	617 ResourceType queryLevel,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	618 const std::set<std::string>& labels,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	619 LabelsConstraint labelsConstraint,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	620 size_t limit
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	621 )
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	622 {
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	623 ResourceType lowerLevel, upperLevel;
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	624 GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup);
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	625
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	626 assert(upperLevel == queryLevel &&
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	627 queryLevel == lowerLevel);
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	628
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	629 const bool escapeBrackets = formatter.IsEscapeBrackets();
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	630
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	631 std::vector<std::string> mainDicomTagsComparisons, dicomIdentifiersComparisons;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	632
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	633 for (size_t i = 0; i < lookup.size(); i++)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	634 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	635 std::string comparison;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	636
425 c1b0f3c4e1f5 sync Orthanc code Alain Mazy <am@osimis.io> parents: 416 diff changeset	637 if (FormatComparison2(comparison, formatter, lookup[i], escapeBrackets))
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	638 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	639 if (!comparison.empty())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	640 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	641 if (lookup[i].IsIdentifier())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	642 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	643 dicomIdentifiersComparisons.push_back(comparison);
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	644 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	645 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	646 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	647 mainDicomTagsComparisons.push_back(comparison);
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	648 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	649 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	650 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	651 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	652
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	653 sql = ("SELECT publicId, internalId "
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	654 "FROM Resources "
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	655 "WHERE resourceType = " + formatter.FormatResourceType(queryLevel)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	656 + " ");
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	657
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	658 if (dicomIdentifiersComparisons.size() > 0)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	659 {
416 6b16914b6655 fix Alain Mazy <am@osimis.io> parents: 415 diff changeset	660 for (std::vector<std::string>::const_iterator it = dicomIdentifiersComparisons.begin(); it < dicomIdentifiersComparisons.end(); ++it)
6b16914b6655 fix Alain Mazy <am@osimis.io> parents: 415 diff changeset	661 {
6b16914b6655 fix Alain Mazy <am@osimis.io> parents: 415 diff changeset	662 sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE " + *it + ") ");
6b16914b6655 fix Alain Mazy <am@osimis.io> parents: 415 diff changeset	663 }
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	664 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	665
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	666 if (mainDicomTagsComparisons.size() > 0)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	667 {
416 6b16914b6655 fix Alain Mazy <am@osimis.io> parents: 415 diff changeset	668 for (std::vector<std::string>::const_iterator it = mainDicomTagsComparisons.begin(); it < mainDicomTagsComparisons.end(); ++it)
6b16914b6655 fix Alain Mazy <am@osimis.io> parents: 415 diff changeset	669 {
6b16914b6655 fix Alain Mazy <am@osimis.io> parents: 415 diff changeset	670 sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE " + *it + ") ");
6b16914b6655 fix Alain Mazy <am@osimis.io> parents: 415 diff changeset	671 }
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	672 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	673
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	674 if (!labels.empty())
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	675 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	676 /**
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	677 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	678 * way to search for missing values, as long as both columns in
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	679 * question are NOT NULL."
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	680 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	681 **/
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	682
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	683 std::list<std::string> formattedLabels;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	684 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	685 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	686 formattedLabels.push_back(formatter.GenerateParameter(*it));
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	687 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	688
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	689 std::string condition;
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	690 std::string inOrNotIn;
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	691 switch (labelsConstraint)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	692 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	693 case LabelsConstraint_Any:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	694 condition = "> 0";
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	695 inOrNotIn = "IN";
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	696 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	697
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	698 case LabelsConstraint_All:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	699 condition = "= " + boost::lexical_cast<std::string>(labels.size());
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	700 inOrNotIn = "IN";
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	701 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	702
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	703 case LabelsConstraint_None:
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	704 condition = "> 0";
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	705 inOrNotIn = "NOT IN";
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	706 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	707
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	708 default:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	709 throw OrthancException(ErrorCode_ParameterOutOfRange);
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	710 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	711
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	712 sql += (" AND internalId " + inOrNotIn + " (SELECT id"
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	713 " FROM (SELECT id, COUNT(1) AS labelsCount "
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	714 "FROM Labels "
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	715 "WHERE label IN (" + Join(formattedLabels, "", ", ") + ") GROUP BY id"
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	716 ") AS temp "
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	717 " WHERE labelsCount " + condition + ")");
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	718 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	719
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	720 if (limit != 0)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	721 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	722 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	723 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	724 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	725
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	726 }

Mercurial > hg > orthanc-databases

annotate Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 501:594859656a06 large-queries