Mercurial > hg > orthanc-databases
annotate Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 536:4ecf50a4521c find-refactoring
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Fri, 06 Sep 2024 16:56:37 +0200 |
| parents | 54d518dcd74a |
| children | 12f1c5265081 |
| rev | line source |
|---|---|
| 152 | 1 /** |
| 2 * Orthanc - A Lightweight, RESTful DICOM Store | |
| 3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics | |
| 4 * Department, University Hospital of Liege, Belgium | |
|
507
54d518dcd74a
updated copyright, as Orthanc Team now replaces Osimis
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
459
diff
changeset
|
5 * Copyright (C) 2017-2023 Osimis S.A., Belgium |
|
54d518dcd74a
updated copyright, as Orthanc Team now replaces Osimis
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
459
diff
changeset
|
6 * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium |
|
459
ecd0b719cff5
update year to 2024
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
425
diff
changeset
|
7 * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium |
| 152 | 8 * |
| 9 * This program is free software: you can redistribute it and/or | |
| 10 * modify it under the terms of the GNU General Public License as | |
| 11 * published by the Free Software Foundation, either version 3 of the | |
| 12 * License, or (at your option) any later version. | |
| 13 * | |
| 14 * This program is distributed in the hope that it will be useful, but | |
| 15 * WITHOUT ANY WARRANTY; without even the implied warranty of | |
| 16 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU | |
| 17 * General Public License for more details. | |
| 18 * | |
| 19 * You should have received a copy of the GNU General Public License | |
| 20 * along with this program. If not, see <http://www.gnu.org/licenses/>. | |
| 21 **/ | |
| 22 | |
| 23 | |
| 24 #if !defined(ORTHANC_BUILDING_SERVER_LIBRARY) | |
| 25 # error Macro ORTHANC_BUILDING_SERVER_LIBRARY must be defined | |
| 26 #endif | |
| 27 | |
| 28 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1 | |
| 29 # include "../PrecompiledHeadersServer.h" | |
| 30 #endif | |
| 31 | |
| 32 #include "ISqlLookupFormatter.h" | |
| 33 | |
| 34 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1 | |
| 35 # include "../../../OrthancFramework/Sources/OrthancException.h" | |
| 425 | 36 # include "../../../OrthancFramework/Sources/Toolbox.h" |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
37 # include "../Database/FindRequest.h" |
| 152 | 38 #else |
| 39 # include <OrthancException.h> | |
| 425 | 40 # include <Toolbox.h> |
| 152 | 41 #endif |
| 42 | |
| 43 #include "DatabaseConstraint.h" | |
| 44 | |
|
459
ecd0b719cff5
update year to 2024
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
425
diff
changeset
|
45 #include <cassert> |
| 170 | 46 #include <boost/lexical_cast.hpp> |
| 405 | 47 #include <list> |
| 170 | 48 |
| 49 | |
| 152 | 50 namespace Orthanc |
| 51 { | |
| 52 static std::string FormatLevel(ResourceType level) | |
| 53 { | |
| 54 switch (level) | |
| 55 { | |
| 56 case ResourceType_Patient: | |
| 57 return "patients"; | |
| 58 | |
| 59 case ResourceType_Study: | |
| 60 return "studies"; | |
| 61 | |
| 62 case ResourceType_Series: | |
| 63 return "series"; | |
| 64 | |
| 65 case ResourceType_Instance: | |
| 66 return "instances"; | |
| 67 | |
| 68 default: | |
| 69 throw OrthancException(ErrorCode_InternalError); | |
| 70 } | |
| 71 } | |
| 72 | |
| 73 | |
| 74 static bool FormatComparison(std::string& target, | |
| 75 ISqlLookupFormatter& formatter, | |
| 76 const DatabaseConstraint& constraint, | |
|
354
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
77 size_t index, |
|
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
78 bool escapeBrackets) |
| 152 | 79 { |
| 80 std::string tag = "t" + boost::lexical_cast<std::string>(index); | |
| 81 | |
| 82 std::string comparison; | |
| 83 | |
| 84 switch (constraint.GetConstraintType()) | |
| 85 { | |
| 86 case ConstraintType_Equal: | |
| 87 case ConstraintType_SmallerOrEqual: | |
| 88 case ConstraintType_GreaterOrEqual: | |
| 89 { | |
| 90 std::string op; | |
| 91 switch (constraint.GetConstraintType()) | |
| 92 { | |
| 93 case ConstraintType_Equal: | |
| 94 op = "="; | |
| 95 break; | |
| 96 | |
| 97 case ConstraintType_SmallerOrEqual: | |
| 98 op = "<="; | |
| 99 break; | |
| 100 | |
| 101 case ConstraintType_GreaterOrEqual: | |
| 102 op = ">="; | |
| 103 break; | |
| 104 | |
| 105 default: | |
| 106 throw OrthancException(ErrorCode_InternalError); | |
| 107 } | |
| 108 | |
| 109 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue()); | |
| 110 | |
| 111 if (constraint.IsCaseSensitive()) | |
| 112 { | |
| 113 comparison = tag + ".value " + op + " " + parameter; | |
| 114 } | |
| 115 else | |
| 116 { | |
| 117 comparison = "lower(" + tag + ".value) " + op + " lower(" + parameter + ")"; | |
| 118 } | |
| 119 | |
| 120 break; | |
| 121 } | |
| 122 | |
| 123 case ConstraintType_List: | |
| 124 { | |
| 125 for (size_t i = 0; i < constraint.GetValuesCount(); i++) | |
| 126 { | |
| 127 if (!comparison.empty()) | |
| 128 { | |
| 129 comparison += ", "; | |
| 130 } | |
| 131 | |
| 132 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i)); | |
| 133 | |
| 134 if (constraint.IsCaseSensitive()) | |
| 135 { | |
| 136 comparison += parameter; | |
| 137 } | |
| 138 else | |
| 139 { | |
| 140 comparison += "lower(" + parameter + ")"; | |
| 141 } | |
| 142 } | |
| 143 | |
| 144 if (constraint.IsCaseSensitive()) | |
| 145 { | |
| 146 comparison = tag + ".value IN (" + comparison + ")"; | |
| 147 } | |
| 148 else | |
| 149 { | |
| 150 comparison = "lower(" + tag + ".value) IN (" + comparison + ")"; | |
| 151 } | |
| 152 | |
| 153 break; | |
| 154 } | |
| 155 | |
| 156 case ConstraintType_Wildcard: | |
| 157 { | |
| 158 const std::string value = constraint.GetSingleValue(); | |
| 159 | |
| 160 if (value == "*") | |
| 161 { | |
| 162 if (!constraint.IsMandatory()) | |
| 163 { | |
| 164 // Universal constraint on an optional tag, ignore it | |
| 165 return false; | |
| 166 } | |
| 167 } | |
| 168 else | |
| 169 { | |
| 170 std::string escaped; | |
| 171 escaped.reserve(value.size()); | |
| 172 | |
| 173 for (size_t i = 0; i < value.size(); i++) | |
| 174 { | |
| 175 if (value[i] == '*') | |
| 176 { | |
| 177 escaped += "%"; | |
| 178 } | |
| 179 else if (value[i] == '?') | |
| 180 { | |
| 181 escaped += "_"; | |
| 182 } | |
| 183 else if (value[i] == '%') | |
| 184 { | |
| 185 escaped += "\\%"; | |
| 186 } | |
| 187 else if (value[i] == '_') | |
| 188 { | |
| 189 escaped += "\\_"; | |
| 190 } | |
| 191 else if (value[i] == '\\') | |
| 192 { | |
| 193 escaped += "\\\\"; | |
| 194 } | |
|
354
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 } |
|
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
199 else if (escapeBrackets && value[i] == ']') |
|
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
200 { |
|
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
201 escaped += "\\]"; |
|
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
202 } |
| 152 | 203 else |
| 204 { | |
| 205 escaped += value[i]; | |
| 206 } | |
| 207 } | |
| 208 | |
| 209 std::string parameter = formatter.GenerateParameter(escaped); | |
| 210 | |
| 211 if (constraint.IsCaseSensitive()) | |
| 212 { | |
| 213 comparison = (tag + ".value LIKE " + parameter + " " + | |
| 214 formatter.FormatWildcardEscape()); | |
| 215 } | |
| 216 else | |
| 217 { | |
| 218 comparison = ("lower(" + tag + ".value) LIKE lower(" + | |
| 219 parameter + ") " + formatter.FormatWildcardEscape()); | |
| 220 } | |
| 221 } | |
| 222 | |
| 223 break; | |
| 224 } | |
| 225 | |
| 226 default: | |
| 227 return false; | |
| 228 } | |
| 229 | |
| 230 if (constraint.IsMandatory()) | |
| 231 { | |
| 232 target = comparison; | |
| 233 } | |
| 234 else if (comparison.empty()) | |
| 235 { | |
| 236 target = tag + ".value IS NULL"; | |
| 237 } | |
| 238 else | |
| 239 { | |
| 240 target = tag + ".value IS NULL OR " + comparison; | |
| 241 } | |
| 242 | |
| 243 return true; | |
| 244 } | |
| 245 | |
| 246 | |
| 247 static void FormatJoin(std::string& target, | |
| 248 const DatabaseConstraint& constraint, | |
| 249 size_t index) | |
| 250 { | |
| 251 std::string tag = "t" + boost::lexical_cast<std::string>(index); | |
| 252 | |
| 253 if (constraint.IsMandatory()) | |
| 254 { | |
| 255 target = " INNER JOIN "; | |
| 256 } | |
| 257 else | |
| 258 { | |
| 259 target = " LEFT JOIN "; | |
| 260 } | |
| 261 | |
| 262 if (constraint.IsIdentifier()) | |
| 263 { | |
| 264 target += "DicomIdentifiers "; | |
| 265 } | |
| 266 else | |
| 267 { | |
| 268 target += "MainDicomTags "; | |
| 269 } | |
| 270 | |
| 271 target += (tag + " ON " + tag + ".id = " + FormatLevel(constraint.GetLevel()) + | |
| 272 ".internalId AND " + tag + ".tagGroup = " + | |
| 273 boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) + | |
| 274 " AND " + tag + ".tagElement = " + | |
| 275 boost::lexical_cast<std::string>(constraint.GetTag().GetElement())); | |
| 276 } | |
| 405 | 277 |
| 278 | |
| 279 static std::string Join(const std::list<std::string>& values, | |
| 280 const std::string& prefix, | |
| 281 const std::string& separator) | |
| 282 { | |
| 283 if (values.empty()) | |
| 284 { | |
| 285 return ""; | |
| 286 } | |
| 287 else | |
| 288 { | |
| 289 std::string s = prefix; | |
| 290 | |
| 291 bool first = true; | |
| 292 for (std::list<std::string>::const_iterator it = values.begin(); it != values.end(); ++it) | |
| 293 { | |
| 294 if (first) | |
| 295 { | |
| 296 first = false; | |
| 297 } | |
| 298 else | |
| 299 { | |
| 300 s += separator; | |
| 301 } | |
| 302 | |
| 303 s += *it; | |
| 304 } | |
| 305 | |
| 306 return s; | |
| 307 } | |
| 308 } | |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
309 |
| 425 | 310 static bool FormatComparison2(std::string& target, |
| 311 ISqlLookupFormatter& formatter, | |
| 312 const DatabaseConstraint& constraint, | |
| 313 bool escapeBrackets) | |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
314 { |
|
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 comparison; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
316 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
|
317 + " 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
|
318 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
319 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
|
320 { |
|
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_Equal: |
|
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_SmallerOrEqual: |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
323 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
|
324 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
325 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
|
326 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
|
327 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
328 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
|
329 op = "="; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
330 break; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
331 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
332 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
|
333 op = "<="; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
334 break; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
335 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
336 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
|
337 op = ">="; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
338 break; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
339 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
340 default: |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
341 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
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
344 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
|
345 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
346 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
|
347 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
348 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
|
349 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
350 else |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
351 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
352 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
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
355 break; |
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
358 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
|
359 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
360 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
|
361 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
|
362 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
363 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
|
364 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
365 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
|
366 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
367 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
|
368 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
369 else |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
370 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
371 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
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
375 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
|
376 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
|
377 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
378 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
|
379 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
380 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
|
381 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
382 else |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
383 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
384 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
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
387 break; |
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
390 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
|
391 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
392 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
|
393 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
394 if (value == "*") |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
395 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
396 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
|
397 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
398 // 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
|
399 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
|
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 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
402 else |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
403 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
404 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
|
405 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
|
406 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
407 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
|
408 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
409 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
|
410 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
411 escaped += "%"; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
412 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
413 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
|
414 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
415 escaped += "_"; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
416 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
417 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
|
418 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
419 escaped += "\\%"; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
420 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
421 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
|
422 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
423 escaped += "\\_"; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
424 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
425 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
|
426 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
427 escaped += "\\\\"; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
428 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
429 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
|
430 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
431 escaped += "\\["; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
432 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
433 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
|
434 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
435 escaped += "\\]"; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
436 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
437 else |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
438 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
439 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
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
443 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
|
444 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
445 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
|
446 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
447 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
|
448 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
449 else |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
450 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
451 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
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
455 break; |
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
458 default: |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
459 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
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
462 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
|
463 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
464 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
|
465 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
466 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
|
467 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
468 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
|
469 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
470 else |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
471 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
472 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
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
475 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
|
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 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
478 |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
479 void ISqlLookupFormatter::GetLookupLevels(ResourceType& lowerLevel, |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
480 ResourceType& upperLevel, |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
481 const ResourceType& queryLevel, |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
482 const DatabaseConstraints& lookup) |
| 152 | 483 { |
| 484 assert(ResourceType_Patient < ResourceType_Study && | |
| 485 ResourceType_Study < ResourceType_Series && | |
| 486 ResourceType_Series < ResourceType_Instance); | |
| 487 | |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
488 lowerLevel = queryLevel; |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
489 upperLevel = queryLevel; |
| 152 | 490 |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
491 for (size_t i = 0; i < lookup.GetSize(); i++) |
| 152 | 492 { |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
493 ResourceType level = lookup.GetConstraint(i).GetLevel(); |
| 152 | 494 |
| 495 if (level < upperLevel) | |
| 496 { | |
| 497 upperLevel = level; | |
| 498 } | |
| 499 | |
| 500 if (level > lowerLevel) | |
| 501 { | |
| 502 lowerLevel = level; | |
| 503 } | |
| 504 } | |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
505 } |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
506 |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
507 |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
508 void ISqlLookupFormatter::Apply(std::string& sql, |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
509 ISqlLookupFormatter& formatter, |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
510 const DatabaseConstraints& lookup, |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
511 ResourceType queryLevel, |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
512 const std::set<std::string>& labels, |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
513 LabelsConstraint labelsConstraint, |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
514 size_t limit) |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
515 { |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
516 ResourceType lowerLevel, upperLevel; |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
517 GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup); |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
518 |
| 152 | 519 assert(upperLevel <= queryLevel && |
| 520 queryLevel <= lowerLevel); | |
| 521 | |
|
354
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
522 const bool escapeBrackets = formatter.IsEscapeBrackets(); |
|
2a3bbb4104fa
fix changeset 389c037387ea
Sebastien Jodogne <s.jodogne@gmail.com>
parents:
353
diff
changeset
|
523 |
| 152 | 524 std::string joins, comparisons; |
| 525 | |
| 526 size_t count = 0; | |
| 527 | |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
528 for (size_t i = 0; i < lookup.GetSize(); i++) |
| 152 | 529 { |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
530 const DatabaseConstraint& constraint = lookup.GetConstraint(i); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
531 |
| 152 | 532 std::string comparison; |
| 533 | |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
534 if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets)) |
| 152 | 535 { |
| 536 std::string join; | |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
537 FormatJoin(join, constraint, count); |
| 152 | 538 joins += join; |
| 539 | |
| 540 if (!comparison.empty()) | |
| 541 { | |
| 542 comparisons += " AND " + comparison; | |
| 543 } | |
| 544 | |
| 545 count ++; | |
| 546 } | |
| 547 } | |
| 548 | |
| 549 sql = ("SELECT " + | |
| 550 FormatLevel(queryLevel) + ".publicId, " + | |
| 551 FormatLevel(queryLevel) + ".internalId" + | |
| 552 " FROM Resources AS " + FormatLevel(queryLevel)); | |
| 553 | |
| 554 for (int level = queryLevel - 1; level >= upperLevel; level--) | |
| 555 { | |
| 556 sql += (" INNER JOIN Resources " + | |
| 557 FormatLevel(static_cast<ResourceType>(level)) + " ON " + | |
| 558 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" + | |
| 559 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId"); | |
| 560 } | |
| 561 | |
| 562 for (int level = queryLevel + 1; level <= lowerLevel; level++) | |
| 563 { | |
| 564 sql += (" INNER JOIN Resources " + | |
| 565 FormatLevel(static_cast<ResourceType>(level)) + " ON " + | |
| 566 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + | |
| 567 FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); | |
| 568 } | |
| 405 | 569 |
| 570 std::list<std::string> where; | |
| 571 where.push_back(FormatLevel(queryLevel) + ".resourceType = " + | |
| 572 formatter.FormatResourceType(queryLevel) + comparisons); | |
| 573 | |
| 574 if (!labels.empty()) | |
| 575 { | |
| 576 /** | |
| 577 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best | |
| 578 * way to search for missing values, as long as both columns in | |
| 579 * question are NOT NULL." | |
| 580 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ | |
| 581 **/ | |
| 582 | |
| 583 std::list<std::string> formattedLabels; | |
| 584 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it) | |
| 585 { | |
| 586 formattedLabels.push_back(formatter.GenerateParameter(*it)); | |
| 587 } | |
| 588 | |
| 589 std::string condition; | |
| 590 switch (labelsConstraint) | |
| 591 { | |
| 592 case LabelsConstraint_Any: | |
| 593 condition = "> 0"; | |
| 594 break; | |
| 595 | |
| 596 case LabelsConstraint_All: | |
| 597 condition = "= " + boost::lexical_cast<std::string>(labels.size()); | |
| 598 break; | |
| 599 | |
| 600 case LabelsConstraint_None: | |
| 601 condition = "= 0"; | |
| 602 break; | |
| 603 | |
| 604 default: | |
| 605 throw OrthancException(ErrorCode_ParameterOutOfRange); | |
| 606 } | |
| 152 | 607 |
| 405 | 608 where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + FormatLevel(queryLevel) + |
| 609 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); | |
| 610 } | |
| 611 | |
| 612 sql += joins + Join(where, " WHERE ", " AND "); | |
| 152 | 613 |
| 614 if (limit != 0) | |
| 615 { | |
| 616 sql += " LIMIT " + boost::lexical_cast<std::string>(limit); | |
| 617 } | |
| 618 } | |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
619 |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
620 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
621 void ISqlLookupFormatter::Apply(std::string& sql, |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
622 ISqlLookupFormatter& formatter, |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
623 const FindRequest& request) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
624 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
625 const bool escapeBrackets = formatter.IsEscapeBrackets(); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
626 ResourceType queryLevel = request.GetLevel(); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
627 const std::string& strQueryLevel = FormatLevel(queryLevel); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
628 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
629 ResourceType lowerLevel, upperLevel; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
630 GetLookupLevels(lowerLevel, upperLevel, queryLevel, request.GetDicomTagConstraints()); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
631 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
632 assert(upperLevel <= queryLevel && |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
633 queryLevel <= lowerLevel); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
634 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
635 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
636 sql = ("SELECT " + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
637 strQueryLevel + ".publicId, " + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
638 strQueryLevel + ".internalId" + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
639 " FROM Resources AS " + strQueryLevel); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
640 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
641 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
642 std::string joins, comparisons; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
643 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
644 if (request.GetOrthancIdentifiers().IsDefined() && request.GetOrthancIdentifiers().DetectLevel() <= queryLevel) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
645 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
646 // single child resource matching, there should not be other constraints (at least for now) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
647 assert(request.GetDicomTagConstraints().GetSize() == 0); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
648 assert(request.GetLabels().size() == 0); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
649 assert(request.HasLimits() == false); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
650 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
651 ResourceType topParentLevel = request.GetOrthancIdentifiers().DetectLevel(); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
652 const std::string& strTopParentLevel = FormatLevel(topParentLevel); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
653 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
654 comparisons = " AND " + strTopParentLevel + ".publicId = " + formatter.GenerateParameter(request.GetOrthancIdentifiers().GetLevel(topParentLevel)); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
655 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
656 for (int level = queryLevel; level > topParentLevel; level--) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
657 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
658 sql += (" INNER JOIN Resources " + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
659 FormatLevel(static_cast<ResourceType>(level - 1)) + " ON " + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
660 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
661 FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
662 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
663 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
664 else |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
665 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
666 size_t count = 0; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
667 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
668 const DatabaseConstraints& dicomTagsConstraints = request.GetDicomTagConstraints(); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
669 for (size_t i = 0; i < dicomTagsConstraints.GetSize(); i++) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
670 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
671 const DatabaseConstraint& constraint = dicomTagsConstraints.GetConstraint(i); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
672 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
673 std::string comparison; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
674 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
675 if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets)) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
676 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
677 std::string join; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
678 FormatJoin(join, constraint, count); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
679 joins += join; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
680 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
681 if (!comparison.empty()) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
682 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
683 comparisons += " AND " + comparison; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
684 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
685 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
686 count ++; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
687 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
688 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
689 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
690 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
691 for (int level = queryLevel - 1; level >= upperLevel; level--) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
692 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
693 sql += (" INNER JOIN Resources " + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
694 FormatLevel(static_cast<ResourceType>(level)) + " ON " + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
695 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
696 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId"); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
697 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
698 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
699 for (int level = queryLevel + 1; level <= lowerLevel; level++) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
700 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
701 sql += (" INNER JOIN Resources " + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
702 FormatLevel(static_cast<ResourceType>(level)) + " ON " + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
703 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
704 FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
705 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
706 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
707 std::list<std::string> where; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
708 where.push_back(strQueryLevel + ".resourceType = " + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
709 formatter.FormatResourceType(queryLevel) + comparisons); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
710 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
711 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
712 if (!request.GetLabels().empty()) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
713 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
714 /** |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
715 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
716 * way to search for missing values, as long as both columns in |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
717 * question are NOT NULL." |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
718 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
719 **/ |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
720 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
721 const std::set<std::string>& labels = request.GetLabels(); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
722 std::list<std::string> formattedLabels; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
723 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
724 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
725 formattedLabels.push_back(formatter.GenerateParameter(*it)); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
726 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
727 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
728 std::string condition; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
729 switch (request.GetLabelsConstraint()) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
730 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
731 case LabelsConstraint_Any: |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
732 condition = "> 0"; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
733 break; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
734 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
735 case LabelsConstraint_All: |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
736 condition = "= " + boost::lexical_cast<std::string>(labels.size()); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
737 break; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
738 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
739 case LabelsConstraint_None: |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
740 condition = "= 0"; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
741 break; |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
742 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
743 default: |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
744 throw OrthancException(ErrorCode_ParameterOutOfRange); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
745 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
746 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
747 where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel + |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
748 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
749 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
750 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
751 sql += joins + Join(where, " WHERE ", " AND "); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
752 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
753 if (request.HasLimits()) |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
754 { |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
755 sql += formatter.FormatLimits(request.GetLimitsSince(), request.GetLimitsCount()); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
756 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
757 |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
758 } |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
759 #endif |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
760 |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
761 |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
762 void ISqlLookupFormatter::ApplySingleLevel(std::string& sql, |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
763 ISqlLookupFormatter& formatter, |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
764 const DatabaseConstraints& lookup, |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
765 ResourceType queryLevel, |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
766 const std::set<std::string>& labels, |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
767 LabelsConstraint labelsConstraint, |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
768 size_t limit |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
769 ) |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
770 { |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
771 ResourceType lowerLevel, upperLevel; |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
772 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
|
773 |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
774 assert(upperLevel == queryLevel && |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
775 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
|
776 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
777 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
|
778 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
779 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
|
780 |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
781 for (size_t i = 0; i < lookup.GetSize(); i++) |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
782 { |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
783 const DatabaseConstraint& constraint = lookup.GetConstraint(i); |
|
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
784 |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
785 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
|
786 |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
787 if (FormatComparison2(comparison, formatter, constraint, escapeBrackets)) |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
788 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
789 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
|
790 { |
|
536
4ecf50a4521c
sync ISqlLookupFormatter from Orthanc + fix bug 224: LIMIT shall not be used with MSSQL
Alain Mazy <am@orthanc.team>
parents:
507
diff
changeset
|
791 if (constraint.IsIdentifier()) |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
792 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
793 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
|
794 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
795 else |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
796 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
797 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
|
798 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
799 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
800 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
801 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
802 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
803 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
|
804 "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
|
805 "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
|
806 + " "); |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
807 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
808 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
|
809 { |
| 416 | 810 for (std::vector<std::string>::const_iterator it = dicomIdentifiersComparisons.begin(); it < dicomIdentifiersComparisons.end(); ++it) |
| 811 { | |
| 812 sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE " + *it + ") "); | |
| 813 } | |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
814 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
815 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
816 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
|
817 { |
| 416 | 818 for (std::vector<std::string>::const_iterator it = mainDicomTagsComparisons.begin(); it < mainDicomTagsComparisons.end(); ++it) |
| 819 { | |
| 820 sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE " + *it + ") "); | |
| 821 } | |
|
414
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
822 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
823 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
824 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
|
825 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
826 /** |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
827 * "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
|
828 * 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
|
829 * 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
|
830 * 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
|
831 **/ |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
832 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
833 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
|
834 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
|
835 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
836 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
|
837 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
838 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
839 std::string condition; |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
840 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
|
841 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
|
842 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
843 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
|
844 condition = "> 0"; |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
845 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
|
846 break; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
847 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
848 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
|
849 condition = "= " + boost::lexical_cast<std::string>(labels.size()); |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
850 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
|
851 break; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
852 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
853 case LabelsConstraint_None: |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
854 condition = "> 0"; |
|
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
855 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
|
856 break; |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
857 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
858 default: |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
859 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
|
860 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
861 |
|
415
7e123f047771
LookupResources optimization continued
Alain Mazy <am@osimis.io>
parents:
414
diff
changeset
|
862 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
|
863 " 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
|
864 "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
|
865 "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
|
866 ") 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
|
867 " 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
|
868 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
869 |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
870 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
|
871 { |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
872 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
|
873 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
874 } |
|
f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
Alain Mazy <am@osimis.io>
parents:
406
diff
changeset
|
875 |
| 152 | 876 } |