Mercurial > hg > orthanc
comparison OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp @ 5758:ca06dde85358 large-queries
merged find-refactoring -> large-queries
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Thu, 05 Sep 2024 18:52:27 +0200 |
parents | 5463c3ae3235 717acb0ea546 |
children | d52b3f394f69 |
comparison
equal
deleted
inserted
replaced
5757:5463c3ae3235 | 5758:ca06dde85358 |
---|---|
27 #include "../../../OrthancFramework/Sources/DicomFormat/DicomArray.h" | 27 #include "../../../OrthancFramework/Sources/DicomFormat/DicomArray.h" |
28 #include "../../../OrthancFramework/Sources/Logging.h" | 28 #include "../../../OrthancFramework/Sources/Logging.h" |
29 #include "../../../OrthancFramework/Sources/SQLite/Transaction.h" | 29 #include "../../../OrthancFramework/Sources/SQLite/Transaction.h" |
30 #include "../Search/ISqlLookupFormatter.h" | 30 #include "../Search/ISqlLookupFormatter.h" |
31 #include "../ServerToolbox.h" | 31 #include "../ServerToolbox.h" |
32 #include "Compatibility/GenericFind.h" | |
32 #include "Compatibility/ICreateInstance.h" | 33 #include "Compatibility/ICreateInstance.h" |
33 #include "Compatibility/IGetChildrenMetadata.h" | 34 #include "Compatibility/IGetChildrenMetadata.h" |
34 #include "Compatibility/ILookupResourceAndParent.h" | 35 #include "Compatibility/ILookupResourceAndParent.h" |
35 #include "Compatibility/ISetResourcesContent.h" | 36 #include "Compatibility/ISetResourcesContent.h" |
36 #include "VoidDatabaseListener.h" | 37 #include "VoidDatabaseListener.h" |
354 } | 355 } |
355 | 356 |
356 | 357 |
357 virtual void ApplyLookupResources(std::list<std::string>& resourcesId, | 358 virtual void ApplyLookupResources(std::list<std::string>& resourcesId, |
358 std::list<std::string>* instancesId, | 359 std::list<std::string>* instancesId, |
359 const std::vector<DatabaseConstraint>& lookup, | 360 const DatabaseConstraints& lookup, |
360 ResourceType queryLevel, | 361 ResourceType queryLevel, |
361 const std::set<std::string>& labels, | 362 const std::set<std::string>& labels, |
362 LabelsConstraint labelsConstraint, | 363 LabelsConstraint labelsConstraint, |
363 uint32_t limit) ORTHANC_OVERRIDE | 364 uint32_t limit) ORTHANC_OVERRIDE |
364 { | 365 { |
394 { | 395 { |
395 resourcesId.push_back(s.ColumnString(0)); | 396 resourcesId.push_back(s.ColumnString(0)); |
396 } | 397 } |
397 } | 398 } |
398 } | 399 } |
400 | |
401 virtual void ExecuteFind(FindResponse& response, | |
402 const FindRequest& request, | |
403 const Capabilities& capabilities) ORTHANC_OVERRIDE | |
404 { | |
405 const ResourceType requestLevel = request.GetLevel(); | |
406 std::string sql; | |
407 | |
408 { | |
409 // clean previous lookup table | |
410 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup"); | |
411 s.Run(); | |
412 } | |
413 | |
414 { | |
415 // extract the resource id of interest by executing the lookup | |
416 LookupFormatter formatter; | |
417 LookupFormatter::Apply(sql, formatter, request); | |
418 | |
419 sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; | |
420 | |
421 SQLite::Statement statement(db_, sql); | |
422 formatter.Bind(statement); | |
423 statement.Run(); | |
424 | |
425 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId, internalId FROM Lookup"); | |
426 while (s.Step()) | |
427 { | |
428 response.Add(new FindResponse::Resource(requestLevel, s.ColumnInt64(1), s.ColumnString(0))); | |
429 } | |
430 } | |
431 | |
432 // need MainDicomTags from resource ? | |
433 if (request.IsRetrieveMainDicomTags()) | |
434 { | |
435 sql = "SELECT id, tagGroup, tagElement, value " | |
436 "FROM MainDicomTags " | |
437 "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId"; | |
438 | |
439 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
440 while (s.Step()) | |
441 { | |
442 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
443 res.AddStringDicomTag(requestLevel, | |
444 static_cast<uint16_t>(s.ColumnInt(1)), | |
445 static_cast<uint16_t>(s.ColumnInt(2)), | |
446 s.ColumnString(3)); | |
447 } | |
448 } | |
449 | |
450 // need MainDicomTags from parent ? | |
451 if (requestLevel > ResourceType_Patient && request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMainDicomTags()) | |
452 { | |
453 sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value " | |
454 "FROM MainDicomTags " | |
455 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " | |
456 "INNER JOIN Lookup ON MainDicomTags.id = currentLevel.parentId"; | |
457 | |
458 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
459 while (s.Step()) | |
460 { | |
461 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
462 res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 1), | |
463 static_cast<uint16_t>(s.ColumnInt(1)), | |
464 static_cast<uint16_t>(s.ColumnInt(2)), | |
465 s.ColumnString(3)); | |
466 } | |
467 } | |
468 | |
469 // need MainDicomTags from grandparent ? | |
470 if (requestLevel > ResourceType_Study && request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMainDicomTags()) | |
471 { | |
472 sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value " | |
473 "FROM MainDicomTags " | |
474 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " | |
475 "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId " | |
476 "INNER JOIN Lookup ON MainDicomTags.id = parentLevel.parentId"; | |
477 | |
478 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
479 while (s.Step()) | |
480 { | |
481 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
482 res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 2), | |
483 static_cast<uint16_t>(s.ColumnInt(1)), | |
484 static_cast<uint16_t>(s.ColumnInt(2)), | |
485 s.ColumnString(3)); | |
486 } | |
487 } | |
488 | |
489 // need MainDicomTags from children ? | |
490 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMainDicomTags().size() > 0) | |
491 { | |
492 sql = "SELECT Lookup.internalId, tagGroup, tagElement, value " | |
493 "FROM MainDicomTags " | |
494 "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " | |
495 "INNER JOIN Lookup ON MainDicomTags.id = childLevel.internalId "; | |
496 | |
497 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
498 while (s.Step()) | |
499 { | |
500 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
501 res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 1), | |
502 DicomTag(static_cast<uint16_t>(s.ColumnInt(1)), | |
503 static_cast<uint16_t>(s.ColumnInt(2))), | |
504 s.ColumnString(3)); | |
505 } | |
506 } | |
507 | |
508 // need MainDicomTags from grandchildren ? | |
509 if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMainDicomTags().size() > 0) | |
510 { | |
511 sql = "SELECT Lookup.internalId, tagGroup, tagElement, value " | |
512 "FROM MainDicomTags " | |
513 "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " | |
514 "INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId " | |
515 "INNER JOIN Lookup ON MainDicomTags.id = grandChildLevel.internalId "; | |
516 | |
517 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
518 while (s.Step()) | |
519 { | |
520 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
521 res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 2), | |
522 DicomTag(static_cast<uint16_t>(s.ColumnInt(1)), | |
523 static_cast<uint16_t>(s.ColumnInt(2))), | |
524 s.ColumnString(3)); | |
525 } | |
526 } | |
527 | |
528 // need parent identifier ? | |
529 if (request.IsRetrieveParentIdentifier()) | |
530 { | |
531 sql = "SELECT currentLevel.internalId, parentLevel.publicId " | |
532 "FROM Resources AS currentLevel " | |
533 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " | |
534 "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "; | |
535 | |
536 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
537 while (s.Step()) | |
538 { | |
539 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
540 res.SetParentIdentifier(s.ColumnString(1)); | |
541 } | |
542 } | |
543 | |
544 // need resource metadata ? | |
545 if (request.IsRetrieveMetadata()) | |
546 { | |
547 sql = "SELECT id, type, value " | |
548 "FROM Metadata " | |
549 "INNER JOIN Lookup ON Metadata.id = Lookup.internalId"; | |
550 | |
551 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
552 while (s.Step()) | |
553 { | |
554 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
555 res.AddMetadata(requestLevel, | |
556 static_cast<MetadataType>(s.ColumnInt(1)), | |
557 s.ColumnString(2)); | |
558 } | |
559 } | |
560 | |
561 // need resource labels ? | |
562 if (request.IsRetrieveLabels()) | |
563 { | |
564 sql = "SELECT Lookup.internalId, label " | |
565 "FROM Labels " | |
566 "INNER JOIN Lookup ON Labels.id = Lookup.internalId"; | |
567 | |
568 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
569 while (s.Step()) | |
570 { | |
571 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
572 res.AddLabel(s.ColumnString(1)); | |
573 } | |
574 } | |
575 | |
576 // need one instance identifier ? TODO: it might be actually more interesting to retrieve directly the attachment ids .... | |
577 if (request.IsRetrieveOneInstanceIdentifier()) | |
578 { | |
579 if (requestLevel == ResourceType_Series) | |
580 { | |
581 sql = "SELECT Lookup.internalId, childLevel.publicId " | |
582 "FROM Resources AS childLevel " | |
583 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; | |
584 | |
585 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
586 while (s.Step()) | |
587 { | |
588 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
589 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); | |
590 } | |
591 } | |
592 else if (requestLevel == ResourceType_Study) | |
593 { | |
594 sql = "SELECT Lookup.internalId, grandChildLevel.publicId " | |
595 "FROM Resources AS grandChildLevel " | |
596 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " | |
597 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; | |
598 | |
599 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
600 while (s.Step()) | |
601 { | |
602 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
603 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); | |
604 } | |
605 } | |
606 else if (requestLevel == ResourceType_Patient) | |
607 { | |
608 sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId " | |
609 "FROM Resources AS grandGrandChildLevel " | |
610 "INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId " | |
611 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " | |
612 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; | |
613 | |
614 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
615 while (s.Step()) | |
616 { | |
617 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
618 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); | |
619 } | |
620 } | |
621 else | |
622 { | |
623 throw OrthancException(ErrorCode_InternalError); | |
624 } | |
625 } | |
626 | |
627 // need children metadata ? | |
628 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMetadata().size() > 0) | |
629 { | |
630 sql = "SELECT Lookup.internalId, type, value " | |
631 "FROM Metadata " | |
632 "INNER JOIN Lookup ON Lookup.internalId = childLevel.parentId " | |
633 "INNER JOIN Resources childLevel ON childLevel.internalId = Metadata.id"; | |
634 | |
635 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
636 while (s.Step()) | |
637 { | |
638 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
639 res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 1), | |
640 static_cast<MetadataType>(s.ColumnInt(1)), | |
641 s.ColumnString(2)); | |
642 } | |
643 } | |
644 | |
645 // need grandchildren metadata ? | |
646 if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMetadata().size() > 0) | |
647 { | |
648 sql = "SELECT Lookup.internalId, type, value " | |
649 "FROM Metadata " | |
650 "INNER JOIN Lookup ON Lookup.internalId = childLevel.parentId " | |
651 "INNER JOIN Resources childLevel ON childLevel.internalId = grandChildLevel.parentId " | |
652 "INNER JOIN Resources grandChildLevel ON grandChildLevel.internalId = Metadata.id"; | |
653 | |
654 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
655 while (s.Step()) | |
656 { | |
657 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
658 res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 2), | |
659 static_cast<MetadataType>(s.ColumnInt(1)), | |
660 s.ColumnString(2)); | |
661 } | |
662 } | |
663 | |
664 // need children identifiers ? | |
665 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).IsRetrieveIdentifiers()) | |
666 { | |
667 sql = "SELECT Lookup.internalId, childLevel.publicId " | |
668 "FROM Resources AS currentLevel " | |
669 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " | |
670 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "; | |
671 | |
672 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
673 while (s.Step()) | |
674 { | |
675 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
676 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1), s.ColumnString(1)); | |
677 } | |
678 } | |
679 | |
680 // need grandchildren identifiers ? | |
681 if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).IsRetrieveIdentifiers()) | |
682 { | |
683 sql = "SELECT Lookup.internalId, grandChildLevel.publicId " | |
684 "FROM Resources AS currentLevel " | |
685 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " | |
686 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId " | |
687 "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "; | |
688 | |
689 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
690 while (s.Step()) | |
691 { | |
692 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
693 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2), s.ColumnString(1)); | |
694 } | |
695 } | |
696 | |
697 // need resource attachments ? | |
698 if (request.IsRetrieveAttachments()) | |
699 { | |
700 sql = "SELECT Lookup.internalId, fileType, uuid, uncompressedSize, compressedSize, compressionType, uncompressedMD5, compressedMD5 " | |
701 "FROM AttachedFiles " | |
702 "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId"; | |
703 | |
704 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); | |
705 while (s.Step()) | |
706 { | |
707 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); | |
708 FileInfo file(s.ColumnString(2), static_cast<FileContentType>(s.ColumnInt(1)), | |
709 s.ColumnInt64(3), s.ColumnString(6), | |
710 static_cast<CompressionType>(s.ColumnInt(5)), | |
711 s.ColumnInt64(4), s.ColumnString(7)); | |
712 res.AddAttachment(file); | |
713 } | |
714 | |
715 } | |
716 } | |
717 | |
399 | 718 |
400 | 719 |
401 // From the "ICreateInstance" interface | 720 // From the "ICreateInstance" interface |
402 virtual void AttachChild(int64_t parent, | 721 virtual void AttachChild(int64_t parent, |
403 int64_t child) ORTHANC_OVERRIDE | 722 int64_t child) ORTHANC_OVERRIDE |