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

annotate Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 415:7e123f047771

LookupResources optimization continued

author	Alain Mazy <am@osimis.io>
date	Thu, 22 Jun 2023 18:07:41 +0200
parents	f2d3b5c5a68d
children	6b16914b6655

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
405 1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	5 * Copyright (C) 2017-2023 Osimis S.A., Belgium
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	6 * Copyright (C) 2021-2023 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"
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	35 #else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	36 # include <OrthancException.h>
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	37 #endif
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	38
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	39 #include "DatabaseConstraint.h"
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	40
170 e712ff3eede3 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 152 diff changeset	41 #include <boost/lexical_cast.hpp>
405 1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	42 #include <list>
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	43 #include <Toolbox.h>
170 e712ff3eede3 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 152 diff changeset	44
e712ff3eede3 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 152 diff changeset	45
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	46 namespace Orthanc
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	47 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	48 static std::string FormatLevel(ResourceType level)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	49 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	50 switch (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 case ResourceType_Patient:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	53 return "patients";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	54
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	55 case ResourceType_Study:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	56 return "studies";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	57
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	58 case ResourceType_Series:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	59 return "series";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	60
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	61 case ResourceType_Instance:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	62 return "instances";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	63
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	64 default:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	65 throw OrthancException(ErrorCode_InternalError);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	66 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	67 }
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 static bool FormatComparison(std::string& target,
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	71 ISqlLookupFormatter& formatter,
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	72 const DatabaseConstraint& constraint,
354 2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	73 size_t index,
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	74 bool escapeBrackets)
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	75 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	76 std::string tag = "t" + boost::lexical_cast<std::string>(index);
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 comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	79
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	80 switch (constraint.GetConstraintType())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	81 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	82 case ConstraintType_Equal:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	83 case ConstraintType_SmallerOrEqual:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	84 case ConstraintType_GreaterOrEqual:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	85 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	86 std::string op;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	87 switch (constraint.GetConstraintType())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	88 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	89 case ConstraintType_Equal:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	90 op = "=";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	91 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	92
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	93 case ConstraintType_SmallerOrEqual:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	94 op = "<=";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	95 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	96
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	97 case ConstraintType_GreaterOrEqual:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	98 op = ">=";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	99 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	100
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	101 default:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	102 throw OrthancException(ErrorCode_InternalError);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	103 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	104
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	105 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	106
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	107 if (constraint.IsCaseSensitive())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	108 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	109 comparison = tag + ".value " + op + " " + parameter;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	110 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	111 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	112 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	113 comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	114 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	115
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	116 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	117 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	118
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	119 case ConstraintType_List:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	120 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	121 for (size_t i = 0; i < constraint.GetValuesCount(); i++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	122 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	123 if (!comparison.empty())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	124 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	125 comparison += ", ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	126 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	127
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	128 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	129
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	130 if (constraint.IsCaseSensitive())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	131 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	132 comparison += parameter;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	133 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	134 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	135 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	136 comparison += "lower(" + parameter + ")";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	137 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	138 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	139
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	140 if (constraint.IsCaseSensitive())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	141 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	142 comparison = tag + ".value IN (" + comparison + ")";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	143 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	144 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	145 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	146 comparison = "lower(" + tag + ".value) IN (" + comparison + ")";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	147 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	148
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	149 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	150 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	151
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	152 case ConstraintType_Wildcard:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	153 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	154 const std::string value = constraint.GetSingleValue();
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	155
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	156 if (value == "*")
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 (!constraint.IsMandatory())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	159 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	160 // Universal constraint on an optional tag, ignore it
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	161 return false;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	162 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	163 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	164 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	165 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	166 std::string escaped;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	167 escaped.reserve(value.size());
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	168
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	169 for (size_t i = 0; i < value.size(); i++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	170 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	171 if (value[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 escaped += "%";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	174 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	175 else if (value[i] == '?')
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	176 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	177 escaped += "_";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	178 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	179 else if (value[i] == '%')
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	180 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	181 escaped += "\\%";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	182 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	183 else if (value[i] == '_')
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	184 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	185 escaped += "\\_";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	186 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	187 else if (value[i] == '\\')
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	188 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	189 escaped += "\\\\";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	190 }
354 2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	191 else if (escapeBrackets && value[i] == '[')
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	192 {
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	193 escaped += "\\[";
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 else if (escapeBrackets && value[i] == ']')
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 escaped += "\\]";
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	198 }
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	199 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	200 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	201 escaped += value[i];
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	202 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	203 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	204
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	205 std::string parameter = formatter.GenerateParameter(escaped);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	206
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	207 if (constraint.IsCaseSensitive())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	208 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	209 comparison = (tag + ".value LIKE " + parameter + " " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	210 formatter.FormatWildcardEscape());
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	211 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	212 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	213 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	214 comparison = ("lower(" + tag + ".value) LIKE lower(" +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	215 parameter + ") " + formatter.FormatWildcardEscape());
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	216 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	217 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	218
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	219 break;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	220 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	221
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	222 default:
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	223 return false;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	224 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	225
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	226 if (constraint.IsMandatory())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	227 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	228 target = comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	229 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	230 else if (comparison.empty())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	231 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	232 target = tag + ".value IS NULL";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	233 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	234 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	235 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	236 target = tag + ".value IS NULL OR " + comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	237 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	238
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	239 return true;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	240 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	241
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	242
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	243 static void FormatJoin(std::string& target,
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	244 const DatabaseConstraint& constraint,
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	245 size_t index)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	246 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	247 std::string tag = "t" + boost::lexical_cast<std::string>(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 if (constraint.IsMandatory())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	250 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	251 target = " INNER JOIN ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	252 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	253 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	254 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	255 target = " LEFT JOIN ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	256 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	257
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	258 if (constraint.IsIdentifier())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	259 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	260 target += "DicomIdentifiers ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	261 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	262 else
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	263 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	264 target += "MainDicomTags ";
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	265 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	266
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	267 target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	268 ".internalId AND " + tag + ".tagGroup = " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	269 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	270 " AND " + tag + ".tagElement = " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	271 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	272 }
405 1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	273
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	274
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	275 static std::string Join(const std::list<std::string>& values,
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	276 const std::string& prefix,
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	277 const std::string& separator)
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	278 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	279 if (values.empty())
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 return "";
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 else
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 std::string s = prefix;
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 bool first = true;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	288 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	289 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	290 if (first)
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 first = false;
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 else
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 s += separator;
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
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	299 s += *it;
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
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	302 return s;
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 }
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	305
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	306 static bool FormatComparisonExperimental(std::string& target,
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	307 ISqlLookupFormatter& formatter,
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	308 const DatabaseConstraint& constraint,
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	309 //size_t index,
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	310 bool escapeBrackets)
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	311 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	312 //std::string tag = "t" + boost::lexical_cast<std::string>(index);
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	313
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 comparison;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	315 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	316 + " 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	317
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	318 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	319 {
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_Equal:
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_SmallerOrEqual:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	322 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	323 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	324 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	325 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	326 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	327 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	328 op = "=";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	329 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	330
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	331 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	332 op = "<=";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	333 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	334
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	335 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	336 op = ">=";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	337 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	338
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	339 default:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	340 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	343 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	344
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	345 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	346 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	347 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	348 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	349 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	350 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	351 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	354 break;
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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	357 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	358 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	359 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	360 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	361 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	362 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	363
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	364 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	365 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	366 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	367 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	368 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	369 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	370 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	374 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	375 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	376
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	377 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	378 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	379 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	380 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	381 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	382 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	383 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	386 break;
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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	389 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	390 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	391 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	392
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	393 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	394 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	395 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	396 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	397 // 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	398 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	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 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	401 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	402 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	403 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	404 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	405
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	406 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	407 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	408 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	409 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	410 escaped += "%";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	411 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	412 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	413 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	414 escaped += "_";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	415 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	416 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	417 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	418 escaped += "\\%";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	419 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	420 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	421 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	422 escaped += "\\_";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	423 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	424 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	425 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	426 escaped += "\\\\";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	427 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	428 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	429 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	430 escaped += "\\[";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	431 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	432 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	433 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	434 escaped += "\\]";
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	435 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	436 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	437 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	438 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	442 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	443
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	444 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	445 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	446 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	447 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	448 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	449 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	450 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	454 break;
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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	457 default:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	458 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	461 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	462 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	463 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	464 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	465 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	466 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	467 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	468 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	469 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	470 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	471 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	474 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	477
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	478 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	479 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	480 assert(ResourceType_Patient < ResourceType_Study &&
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	481 ResourceType_Study < ResourceType_Series &&
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	482 ResourceType_Series < ResourceType_Instance);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	483
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	484 lowerLevel = queryLevel;
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	485 upperLevel = queryLevel;
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	486
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	487 for (size_t i = 0; i < lookup.size(); i++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	488 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	489 ResourceType level = lookup[i].GetLevel();
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	490
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	491 if (level < upperLevel)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	492 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	493 upperLevel = level;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	494 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	495
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	496 if (level > lowerLevel)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	497 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	498 lowerLevel = level;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	499 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	500 }
415 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
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	504 void ISqlLookupFormatter::Apply(std::string& sql,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	505 ISqlLookupFormatter& formatter,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	506 const std::vector<DatabaseConstraint>& lookup,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	507 ResourceType queryLevel,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	508 const std::set<std::string>& labels,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	509 LabelsConstraint labelsConstraint,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	510 size_t limit)
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	511 {
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	512 ResourceType lowerLevel, upperLevel;
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	513 GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup);
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	514
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	515 assert(upperLevel <= queryLevel &&
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	516 queryLevel <= lowerLevel);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	517
354 2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	518 const bool escapeBrackets = formatter.IsEscapeBrackets();
2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	519
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	520 std::string joins, comparisons;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	521
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	522 size_t count = 0;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	523
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	524 for (size_t i = 0; i < lookup.size(); i++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	525 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	526 std::string comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	527
354 2a3bbb4104fa fix changeset 389c037387ea Sebastien Jodogne <s.jodogne@gmail.com> parents: 353 diff changeset	528 if (FormatComparison(comparison, formatter, lookup[i], count, escapeBrackets))
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	529 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	530 std::string join;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	531 FormatJoin(join, lookup[i], count);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	532 joins += join;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	533
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	534 if (!comparison.empty())
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	535 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	536 comparisons += " AND " + comparison;
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	537 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	538
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	539 count ++;
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
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	543 sql = ("SELECT " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	544 FormatLevel(queryLevel) + ".publicId, " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	545 FormatLevel(queryLevel) + ".internalId" +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	546 " FROM Resources AS " + FormatLevel(queryLevel));
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	547
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	548 for (int level = queryLevel - 1; level >= upperLevel; level--)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	549 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	550 sql += (" INNER JOIN Resources " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	551 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	552 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	553 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId");
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	554 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	555
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	556 for (int level = queryLevel + 1; level <= lowerLevel; level++)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	557 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	558 sql += (" INNER JOIN Resources " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	559 FormatLevel(static_cast<ResourceType>(level)) + " ON " +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	560 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	561 FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	562 }
405 1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	563
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	564 std::list<std::string> where;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	565 where.push_back(FormatLevel(queryLevel) + ".resourceType = " +
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	566 formatter.FormatResourceType(queryLevel) + comparisons);
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	567
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	568 if (!labels.empty())
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 /**
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	571 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	572 * way to search for missing values, as long as both columns in
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	573 * question are NOT NULL."
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	574 * 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	575 **/
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	576
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	577 std::list<std::string> formattedLabels;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	578 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	579 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	580 formattedLabels.push_back(formatter.GenerateParameter(*it));
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
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	583 std::string condition;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	584 switch (labelsConstraint)
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	585 {
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	586 case LabelsConstraint_Any:
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	587 condition = "> 0";
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	588 break;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	589
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	590 case LabelsConstraint_All:
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	591 condition = "= " + boost::lexical_cast<std::string>(labels.size());
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	592 break;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	593
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	594 case LabelsConstraint_None:
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	595 condition = "= 0";
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	596 break;
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	597
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	598 default:
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	599 throw OrthancException(ErrorCode_ParameterOutOfRange);
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	600 }
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	601
405 1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	602 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	603 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
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
1938ba8fba35 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: 358 diff changeset	606 sql += joins + Join(where, " WHERE ", " AND ");
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	607
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	608 if (limit != 0)
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	609 {
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	610 sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	611 }
063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	612 }
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	613
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	614
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	615 void ISqlLookupFormatter::ApplySingleLevel(std::string& sql,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	616 ISqlLookupFormatter& formatter,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	617 const std::vector<DatabaseConstraint>& lookup,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	618 ResourceType queryLevel,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	619 const std::set<std::string>& labels,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	620 LabelsConstraint labelsConstraint,
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	621 size_t limit
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	622 )
414 f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	623 {
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	624 ResourceType lowerLevel, upperLevel;
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	625 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	626
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	627 assert(upperLevel == queryLevel &&
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	628 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	629
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	630 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	631
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	632 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	633
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	634 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	635 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	636 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	637
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	638 if (FormatComparisonExperimental(comparison, formatter, lookup[i], escapeBrackets))
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	639 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	640 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	641 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	642 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	643 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	644 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	645 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	646 else
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	647 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	648 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	654 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	655 "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	656 "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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	659 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	660 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	661 std::string comparisons;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	662 Toolbox::JoinStrings(comparisons, dicomIdentifiersComparisons, " AND ");
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	663 sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE "
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	664 + comparisons + ") ");
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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	667 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	668 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	669 std::string comparisons;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	670 Toolbox::JoinStrings(comparisons, mainDicomTagsComparisons, " AND ");
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	671 sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE "
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	672 + comparisons + ") ");
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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	675 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	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 /**
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	678 * "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	679 * 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	680 * 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	681 * 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	684 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	685 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	686 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	687 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	690 std::string condition;
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	691 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	692 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	693 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	694 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	695 condition = "> 0";
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	696 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	697 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	698
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	699 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	700 condition = "= " + boost::lexical_cast<std::string>(labels.size());
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	701 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	702 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	703
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	704 case LabelsConstraint_None:
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	705 condition = "> 0";
7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	706 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	707 break;
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	708
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	709 default:
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	710 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	711 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	712
415 7e123f047771 LookupResources optimization continued Alain Mazy <am@osimis.io> parents: 414 diff changeset	713 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	714 " 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	715 "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	716 "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	717 ") 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	718 " 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	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
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	721 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	722 {
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	723 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	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 }
f2d3b5c5a68d Optimizing tools/find at studies level only. Integ Tests are ok with PG Alain Mazy <am@osimis.io> parents: 406 diff changeset	726
152 063aa53b5917 sync Sebastien Jodogne <s.jodogne@gmail.com> parents: diff changeset	727 }

Mercurial > hg > orthanc-databases

annotate Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 415:7e123f047771