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