Mercurial > hg > orthanc-databases
comparison Resources/Orthanc/Databases/ISqlLookupFormatter.cpp @ 414:f2d3b5c5a68d
Optimizing tools/find at studies level only. Integ Tests are ok with PG
author | Alain Mazy <am@osimis.io> |
---|---|
date | Thu, 22 Jun 2023 12:15:48 +0200 |
parents | de6de66d70b2 |
children | 7e123f047771 |
comparison
equal
deleted
inserted
replaced
413:afb30d17f090 | 414:f2d3b5c5a68d |
---|---|
38 | 38 |
39 #include "DatabaseConstraint.h" | 39 #include "DatabaseConstraint.h" |
40 | 40 |
41 #include <boost/lexical_cast.hpp> | 41 #include <boost/lexical_cast.hpp> |
42 #include <list> | 42 #include <list> |
43 #include <Toolbox.h> | |
43 | 44 |
44 | 45 |
45 namespace Orthanc | 46 namespace Orthanc |
46 { | 47 { |
47 static std::string FormatLevel(ResourceType level) | 48 static std::string FormatLevel(ResourceType level) |
299 } | 300 } |
300 | 301 |
301 return s; | 302 return s; |
302 } | 303 } |
303 } | 304 } |
305 | |
306 static bool FormatComparisonExperimental(std::string& target, | |
307 ISqlLookupFormatter& formatter, | |
308 const DatabaseConstraint& constraint, | |
309 //size_t index, | |
310 bool escapeBrackets) | |
311 { | |
312 //std::string tag = "t" + boost::lexical_cast<std::string>(index); | |
313 | |
314 std::string comparison; | |
315 std::string tagFilter = ("tagGroup = " + boost::lexical_cast<std::string>(constraint.GetTag().GetGroup()) | |
316 + " AND tagElement = " + boost::lexical_cast<std::string>(constraint.GetTag().GetElement())); | |
317 | |
318 switch (constraint.GetConstraintType()) | |
319 { | |
320 case ConstraintType_Equal: | |
321 case ConstraintType_SmallerOrEqual: | |
322 case ConstraintType_GreaterOrEqual: | |
323 { | |
324 std::string op; | |
325 switch (constraint.GetConstraintType()) | |
326 { | |
327 case ConstraintType_Equal: | |
328 op = "="; | |
329 break; | |
330 | |
331 case ConstraintType_SmallerOrEqual: | |
332 op = "<="; | |
333 break; | |
334 | |
335 case ConstraintType_GreaterOrEqual: | |
336 op = ">="; | |
337 break; | |
338 | |
339 default: | |
340 throw OrthancException(ErrorCode_InternalError); | |
341 } | |
342 | |
343 std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue()); | |
344 | |
345 if (constraint.IsCaseSensitive()) | |
346 { | |
347 comparison = " AND value " + op + " " + parameter; | |
348 } | |
349 else | |
350 { | |
351 comparison = " AND lower(value) " + op + " lower(" + parameter + ")"; | |
352 } | |
353 | |
354 break; | |
355 } | |
356 | |
357 case ConstraintType_List: | |
358 { | |
359 std::vector<std::string> comparisonValues; | |
360 for (size_t i = 0; i < constraint.GetValuesCount(); i++) | |
361 { | |
362 std::string parameter = formatter.GenerateParameter(constraint.GetValue(i)); | |
363 | |
364 if (constraint.IsCaseSensitive()) | |
365 { | |
366 comparisonValues.push_back(parameter); | |
367 } | |
368 else | |
369 { | |
370 comparisonValues.push_back("lower(" + parameter + ")"); | |
371 } | |
372 } | |
373 | |
374 std::string values; | |
375 Toolbox::JoinStrings(values, comparisonValues, ", "); | |
376 | |
377 if (constraint.IsCaseSensitive()) | |
378 { | |
379 comparison = " AND value IN (" + values + ")"; | |
380 } | |
381 else | |
382 { | |
383 comparison = " AND lower(value) IN (" + values + ")"; | |
384 } | |
385 | |
386 break; | |
387 } | |
388 | |
389 case ConstraintType_Wildcard: | |
390 { | |
391 const std::string value = constraint.GetSingleValue(); | |
392 | |
393 if (value == "*") | |
394 { | |
395 if (!constraint.IsMandatory()) | |
396 { | |
397 // Universal constraint on an optional tag, ignore it | |
398 return false; | |
399 } | |
400 } | |
401 else | |
402 { | |
403 std::string escaped; | |
404 escaped.reserve(value.size()); | |
405 | |
406 for (size_t i = 0; i < value.size(); i++) | |
407 { | |
408 if (value[i] == '*') | |
409 { | |
410 escaped += "%"; | |
411 } | |
412 else if (value[i] == '?') | |
413 { | |
414 escaped += "_"; | |
415 } | |
416 else if (value[i] == '%') | |
417 { | |
418 escaped += "\\%"; | |
419 } | |
420 else if (value[i] == '_') | |
421 { | |
422 escaped += "\\_"; | |
423 } | |
424 else if (value[i] == '\\') | |
425 { | |
426 escaped += "\\\\"; | |
427 } | |
428 else if (escapeBrackets && value[i] == '[') | |
429 { | |
430 escaped += "\\["; | |
431 } | |
432 else if (escapeBrackets && value[i] == ']') | |
433 { | |
434 escaped += "\\]"; | |
435 } | |
436 else | |
437 { | |
438 escaped += value[i]; | |
439 } | |
440 } | |
441 | |
442 std::string parameter = formatter.GenerateParameter(escaped); | |
443 | |
444 if (constraint.IsCaseSensitive()) | |
445 { | |
446 comparison = " AND value LIKE " + parameter + " " + formatter.FormatWildcardEscape(); | |
447 } | |
448 else | |
449 { | |
450 comparison = " AND lower(value) LIKE lower(" + parameter + ") " + formatter.FormatWildcardEscape(); | |
451 } | |
452 } | |
453 | |
454 break; | |
455 } | |
456 | |
457 default: | |
458 return false; | |
459 } | |
460 | |
461 if (constraint.IsMandatory()) | |
462 { | |
463 target = tagFilter + comparison; | |
464 } | |
465 else if (comparison.empty()) | |
466 { | |
467 target = tagFilter + " AND value IS NULL"; | |
468 } | |
469 else | |
470 { | |
471 target = tagFilter + " AND value IS NULL OR " + comparison; | |
472 } | |
473 | |
474 return true; | |
475 } | |
476 | |
477 | |
478 | |
304 | 479 |
305 | 480 |
306 void ISqlLookupFormatter::Apply(std::string& sql, | 481 void ISqlLookupFormatter::Apply(std::string& sql, |
307 ISqlLookupFormatter& formatter, | 482 ISqlLookupFormatter& formatter, |
308 const std::vector<DatabaseConstraint>& lookup, | 483 const std::vector<DatabaseConstraint>& lookup, |
429 if (limit != 0) | 604 if (limit != 0) |
430 { | 605 { |
431 sql += " LIMIT " + boost::lexical_cast<std::string>(limit); | 606 sql += " LIMIT " + boost::lexical_cast<std::string>(limit); |
432 } | 607 } |
433 } | 608 } |
609 | |
610 | |
611 void ISqlLookupFormatter::ApplyExperimental(std::string& sql, | |
612 ISqlLookupFormatter& formatter, | |
613 const std::vector<DatabaseConstraint>& lookup, | |
614 ResourceType queryLevel, | |
615 const std::set<std::string>& labels, | |
616 LabelsConstraint labelsConstraint, | |
617 size_t limit | |
618 ) | |
619 { | |
620 assert(ResourceType_Patient < ResourceType_Study && | |
621 ResourceType_Study < ResourceType_Series && | |
622 ResourceType_Series < ResourceType_Instance); | |
623 | |
624 ResourceType upperLevel = queryLevel; | |
625 ResourceType lowerLevel = queryLevel; | |
626 | |
627 for (size_t i = 0; i < lookup.size(); i++) | |
628 { | |
629 ResourceType level = lookup[i].GetLevel(); | |
630 | |
631 if (level < upperLevel) | |
632 { | |
633 upperLevel = level; | |
634 } | |
635 | |
636 if (level > lowerLevel) | |
637 { | |
638 lowerLevel = level; | |
639 } | |
640 } | |
641 | |
642 assert(upperLevel <= queryLevel && | |
643 queryLevel <= lowerLevel); | |
644 | |
645 const bool escapeBrackets = formatter.IsEscapeBrackets(); | |
646 | |
647 std::vector<std::string> mainDicomTagsComparisons, dicomIdentifiersComparisons; | |
648 | |
649 for (size_t i = 0; i < lookup.size(); i++) | |
650 { | |
651 std::string comparison; | |
652 | |
653 if (FormatComparisonExperimental(comparison, formatter, lookup[i], escapeBrackets)) | |
654 { | |
655 if (!comparison.empty()) | |
656 { | |
657 if (lookup[i].IsIdentifier()) | |
658 { | |
659 dicomIdentifiersComparisons.push_back(comparison); | |
660 } | |
661 else | |
662 { | |
663 mainDicomTagsComparisons.push_back(comparison); | |
664 } | |
665 } | |
666 } | |
667 } | |
668 | |
669 sql = ("SELECT publicId, internalId " | |
670 "FROM Resources " | |
671 "WHERE resourceType = " + formatter.FormatResourceType(queryLevel) | |
672 + " "); | |
673 | |
674 if (dicomIdentifiersComparisons.size() > 0) | |
675 { | |
676 std::string comparisons; | |
677 Toolbox::JoinStrings(comparisons, dicomIdentifiersComparisons, " AND "); | |
678 sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE " | |
679 + comparisons + ") "); | |
680 } | |
681 | |
682 if (mainDicomTagsComparisons.size() > 0) | |
683 { | |
684 std::string comparisons; | |
685 Toolbox::JoinStrings(comparisons, mainDicomTagsComparisons, " AND "); | |
686 sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE " | |
687 + comparisons + ") "); | |
688 } | |
689 | |
690 if (!labels.empty()) | |
691 { | |
692 /** | |
693 * "In SQL Server, NOT EXISTS and NOT IN predicates are the best | |
694 * way to search for missing values, as long as both columns in | |
695 * question are NOT NULL." | |
696 * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ | |
697 **/ | |
698 | |
699 std::list<std::string> formattedLabels; | |
700 for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it) | |
701 { | |
702 formattedLabels.push_back(formatter.GenerateParameter(*it)); | |
703 } | |
704 | |
705 std::string condition; | |
706 switch (labelsConstraint) | |
707 { | |
708 case LabelsConstraint_Any: | |
709 condition = "> 0"; | |
710 break; | |
711 | |
712 case LabelsConstraint_All: | |
713 condition = "= " + boost::lexical_cast<std::string>(labels.size()); | |
714 break; | |
715 | |
716 case LabelsConstraint_None: | |
717 condition = "= 0"; | |
718 break; | |
719 | |
720 default: | |
721 throw OrthancException(ErrorCode_ParameterOutOfRange); | |
722 } | |
723 | |
724 sql += (" AND internalId IN (SELECT id" | |
725 " FROM (SELECT id, COUNT(1) AS labelsCount " | |
726 "FROM Labels " | |
727 "WHERE label IN (" + Join(formattedLabels, "", ", ") + ") GROUP BY id" | |
728 ") AS temp " | |
729 " WHERE labelsCount " + condition + ")"); | |
730 } | |
731 | |
732 if (limit != 0) | |
733 { | |
734 sql += " LIMIT " + boost::lexical_cast<std::string>(limit); | |
735 } | |
736 } | |
737 | |
434 } | 738 } |