Mercurial > hg > orthanc

comparison OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp @ 5758:ca06dde85358 large-queries

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
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