Mercurial > hg > orthanc-databases
comparison Framework/Plugins/ISqlLookupFormatter.cpp @ 550:9ed9a91bde33 find-refactoring
un-sharing DatabaseConstraint and ISqlLookupFormatter with Orthanc core
author | Sebastien Jodogne <s.jodogne@gmail.com> |
---|---|
date | Mon, 09 Sep 2024 15:04:48 +0200 |
parents | 25005693297b |
children | 7f45f23b10d0 |
comparison
equal
deleted
inserted
replaced
546:cd9766f294fa | 550:9ed9a91bde33 |
---|---|
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 Orthanc | |
44 { | |
45 static std::string FormatLevel(ResourceType level) | |
46 { | |
47 switch (level) | |
48 { | |
49 case ResourceType_Patient: | |
50 return "patients"; | |
51 | |
52 case ResourceType_Study: | |
53 return "studies"; | |
54 | |
55 case ResourceType_Series: | |
56 return "series"; | |
57 | |
58 case ResourceType_Instance: | |
59 return "instances"; | |
60 | |
61 default: | |
62 throw OrthancException(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 OrthancException(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 OrthancException(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 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(ResourceType& lowerLevel, | |
473 ResourceType& upperLevel, | |
474 const ResourceType& queryLevel, | |
475 const DatabaseConstraints& lookup) | |
476 { | |
477 assert(ResourceType_Patient < ResourceType_Study && | |
478 ResourceType_Study < ResourceType_Series && | |
479 ResourceType_Series < ResourceType_Instance); | |
480 | |
481 lowerLevel = queryLevel; | |
482 upperLevel = queryLevel; | |
483 | |
484 for (size_t i = 0; i < lookup.GetSize(); i++) | |
485 { | |
486 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 ResourceType queryLevel, | |
505 const std::set<std::string>& labels, | |
506 LabelsConstraint labelsConstraint, | |
507 size_t limit) | |
508 { | |
509 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<ResourceType>(level)) + " ON " + | |
551 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" + | |
552 FormatLevel(static_cast<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<ResourceType>(level)) + " ON " + | |
559 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + | |
560 FormatLevel(static_cast<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 OrthancException(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 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 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 ResourceType_Study; | |
630 } | |
631 else if (!request.orthanc_id_series().empty() && | |
632 request.orthanc_id_instance().empty()) | |
633 { | |
634 return ResourceType_Series; | |
635 } | |
636 else if (!request.orthanc_id_instance().empty()) | |
637 { | |
638 return ResourceType_Instance; | |
639 } | |
640 else | |
641 { | |
642 throw OrthancException(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 ResourceType queryLevel = OrthancDatabases::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 ResourceType lowerLevel, upperLevel; | |
662 GetLookupLevels(lowerLevel, upperLevel, queryLevel, constraints); | |
663 | |
664 assert(upperLevel <= queryLevel && | |
665 queryLevel <= lowerLevel); | |
666 | |
667 | |
668 sql = ("SELECT " + | |
669 strQueryLevel + ".publicId, " + | |
670 strQueryLevel + ".internalId" + | |
671 " FROM Resources AS " + strQueryLevel); | |
672 | |
673 | |
674 std::string joins, comparisons; | |
675 | |
676 const bool isOrthancIdentifiersDefined = (!request.orthanc_id_patient().empty() || | |
677 !request.orthanc_id_study().empty() || | |
678 !request.orthanc_id_series().empty() || | |
679 !request.orthanc_id_instance().empty()); | |
680 | |
681 if (isOrthancIdentifiersDefined && | |
682 Orthanc::IsResourceLevelAboveOrEqual(DetectLevel(request), queryLevel)) | |
683 { | |
684 // single child resource matching, there should not be other constraints (at least for now) | |
685 if (request.dicom_tag_constraints().size() != 0 || | |
686 request.labels().size() != 0 || | |
687 request.has_limits()) | |
688 { | |
689 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); | |
690 } | |
691 | |
692 ResourceType topParentLevel = DetectLevel(request); | |
693 const std::string& strTopParentLevel = FormatLevel(topParentLevel); | |
694 | |
695 std::string publicId; | |
696 switch (topParentLevel) | |
697 { | |
698 case ResourceType_Patient: | |
699 publicId = request.orthanc_id_patient(); | |
700 break; | |
701 | |
702 case ResourceType_Study: | |
703 publicId = request.orthanc_id_study(); | |
704 break; | |
705 | |
706 case ResourceType_Series: | |
707 publicId = request.orthanc_id_series(); | |
708 break; | |
709 | |
710 case ResourceType_Instance: | |
711 publicId = request.orthanc_id_instance(); | |
712 break; | |
713 | |
714 default: | |
715 throw OrthancException(ErrorCode_InternalError); | |
716 } | |
717 | |
718 if (publicId.empty()) | |
719 { | |
720 throw OrthancException(ErrorCode_InternalError); | |
721 } | |
722 | |
723 comparisons = " AND " + strTopParentLevel + ".publicId = " + formatter.GenerateParameter(publicId); | |
724 | |
725 for (int level = queryLevel; level > topParentLevel; level--) | |
726 { | |
727 sql += (" INNER JOIN Resources " + | |
728 FormatLevel(static_cast<ResourceType>(level - 1)) + " ON " + | |
729 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + | |
730 FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); | |
731 } | |
732 } | |
733 else | |
734 { | |
735 size_t count = 0; | |
736 | |
737 for (size_t i = 0; i < constraints.GetSize(); i++) | |
738 { | |
739 const DatabaseConstraint& constraint = constraints.GetConstraint(i); | |
740 | |
741 std::string comparison; | |
742 | |
743 if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets)) | |
744 { | |
745 std::string join; | |
746 FormatJoin(join, constraint, count); | |
747 joins += join; | |
748 | |
749 if (!comparison.empty()) | |
750 { | |
751 comparisons += " AND " + comparison; | |
752 } | |
753 | |
754 count ++; | |
755 } | |
756 } | |
757 } | |
758 | |
759 for (int level = queryLevel - 1; level >= upperLevel; level--) | |
760 { | |
761 sql += (" INNER JOIN Resources " + | |
762 FormatLevel(static_cast<ResourceType>(level)) + " ON " + | |
763 FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" + | |
764 FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId"); | |
765 } | |
766 | |
767 for (int level = queryLevel + 1; level <= lowerLevel; level++) | |
768 { | |
769 sql += (" INNER JOIN Resources " + | |
770 FormatLevel(static_cast<ResourceType>(level)) + " ON " + | |
771 FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" + | |
772 FormatLevel(static_cast<ResourceType>(level)) + ".parentId"); | |
773 } | |
774 | |
775 std::list<std::string> where; | |
776 where.push_back(strQueryLevel + ".resourceType = " + | |
777 formatter.FormatResourceType(queryLevel) + comparisons); | |
778 | |
779 | |
780 if (!request.labels().empty()) | |
781 { | |
782 /** | |
783 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best | |
784 * way to search for missing values, as long as both columns in | |
785 * question are NOT NULL." | |
786 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ | |
787 **/ | |
788 | |
789 std::list<std::string> formattedLabels; | |
790 for (int i = 0; i < request.labels().size(); i++) | |
791 { | |
792 formattedLabels.push_back(formatter.GenerateParameter(request.labels(i))); | |
793 } | |
794 | |
795 std::string condition; | |
796 switch (request.labels_constraint()) | |
797 { | |
798 case Orthanc::DatabasePluginMessages::LABELS_CONSTRAINT_ANY: | |
799 condition = "> 0"; | |
800 break; | |
801 | |
802 case Orthanc::DatabasePluginMessages::LABELS_CONSTRAINT_ALL: | |
803 condition = "= " + boost::lexical_cast<std::string>(request.labels().size()); | |
804 break; | |
805 | |
806 case Orthanc::DatabasePluginMessages::LABELS_CONSTRAINT_NONE: | |
807 condition = "= 0"; | |
808 break; | |
809 | |
810 default: | |
811 throw OrthancException(ErrorCode_ParameterOutOfRange); | |
812 } | |
813 | |
814 where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel + | |
815 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); | |
816 } | |
817 | |
818 sql += joins + Join(where, " WHERE ", " AND "); | |
819 | |
820 if (request.has_limits()) | |
821 { | |
822 sql += formatter.FormatLimits(request.limits().since(), request.limits().count()); | |
823 } | |
824 | |
825 } | |
826 #endif | |
827 | |
828 | |
829 void ISqlLookupFormatter::ApplySingleLevel(std::string& sql, | |
830 ISqlLookupFormatter& formatter, | |
831 const DatabaseConstraints& lookup, | |
832 ResourceType queryLevel, | |
833 const std::set<std::string>& labels, | |
834 LabelsConstraint labelsConstraint, | |
835 size_t limit | |
836 ) | |
837 { | |
838 ResourceType lowerLevel, upperLevel; | |
839 GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup); | |
840 | |
841 assert(upperLevel == queryLevel && | |
842 queryLevel == lowerLevel); | |
843 | |
844 const bool escapeBrackets = formatter.IsEscapeBrackets(); | |
845 | |
846 std::vector<std::string> mainDicomTagsComparisons, dicomIdentifiersComparisons; | |
847 | |
848 for (size_t i = 0; i < lookup.GetSize(); i++) | |
849 { | |
850 const DatabaseConstraint& constraint = lookup.GetConstraint(i); | |
851 | |
852 std::string comparison; | |
853 | |
854 if (FormatComparison2(comparison, formatter, constraint, escapeBrackets)) | |
855 { | |
856 if (!comparison.empty()) | |
857 { | |
858 if (constraint.IsIdentifier()) | |
859 { | |
860 dicomIdentifiersComparisons.push_back(comparison); | |
861 } | |
862 else | |
863 { | |
864 mainDicomTagsComparisons.push_back(comparison); | |
865 } | |
866 } | |
867 } | |
868 } | |
869 | |
870 sql = ("SELECT publicId, internalId " | |
871 "FROM Resources " | |
872 "WHERE resourceType = " + formatter.FormatResourceType(queryLevel) | |
873 + " "); | |
874 | |
875 if (dicomIdentifiersComparisons.size() > 0) | |
876 { | |
877 for (std::vector<std::string>::const_iterator it = dicomIdentifiersComparisons.begin(); it < dicomIdentifiersComparisons.end(); ++it) | |
878 { | |
879 sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE " + *it + ") "); | |
880 } | |
881 } | |
882 | |
883 if (mainDicomTagsComparisons.size() > 0) | |
884 { | |
885 for (std::vector<std::string>::const_iterator it = mainDicomTagsComparisons.begin(); it < mainDicomTagsComparisons.end(); ++it) | |
886 { | |
887 sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE " + *it + ") "); | |
888 } | |
889 } | |
890 | |
891 if (!labels.empty()) | |
892 { | |
893 /** | |
894 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best | |
895 * way to search for missing values, as long as both columns in | |
896 * question are NOT NULL." | |
897 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ | |
898 **/ | |
899 | |
900 std::list<std::string> formattedLabels; | |
901 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it) | |
902 { | |
903 formattedLabels.push_back(formatter.GenerateParameter(*it)); | |
904 } | |
905 | |
906 std::string condition; | |
907 std::string inOrNotIn; | |
908 switch (labelsConstraint) | |
909 { | |
910 case LabelsConstraint_Any: | |
911 condition = "> 0"; | |
912 inOrNotIn = "IN"; | |
913 break; | |
914 | |
915 case LabelsConstraint_All: | |
916 condition = "= " + boost::lexical_cast<std::string>(labels.size()); | |
917 inOrNotIn = "IN"; | |
918 break; | |
919 | |
920 case LabelsConstraint_None: | |
921 condition = "> 0"; | |
922 inOrNotIn = "NOT IN"; | |
923 break; | |
924 | |
925 default: | |
926 throw OrthancException(ErrorCode_ParameterOutOfRange); | |
927 } | |
928 | |
929 sql += (" AND internalId " + inOrNotIn + " (SELECT id" | |
930 " FROM (SELECT id, COUNT(1) AS labelsCount " | |
931 "FROM Labels " | |
932 "WHERE label IN (" + Join(formattedLabels, "", ", ") + ") GROUP BY id" | |
933 ") AS temp " | |
934 " WHERE labelsCount " + condition + ")"); | |
935 } | |
936 | |
937 if (limit != 0) | |
938 { | |
939 sql += " LIMIT " + boost::lexical_cast<std::string>(limit); | |
940 } | |
941 } | |
942 } |