Mercurial > hg > orthanc

comparison OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp @ 5774:f96abfe08946 find-refactoring

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
implementation of specialized SQL commands in SQLiteDatabaseWrapper
author Sebastien Jodogne <s.jodogne@gmail.com>
date Thu, 12 Sep 2024 08:25:41 +0200
parents 093a8693ba16
children de33982a0bf4
comparison
equal deleted inserted replaced
5773:3b7dce0e43c6 5774:f96abfe08946
66 } 66 }
67 67
68 virtual std::string FormatLimits(uint64_t since, uint64_t count) ORTHANC_OVERRIDE 68 virtual std::string FormatLimits(uint64_t since, uint64_t count) ORTHANC_OVERRIDE
69 { 69 {
70 std::string sql; 70 std::string sql;
71
71 if (count > 0) 72 if (count > 0)
72 { 73 {
73 sql += " LIMIT " + boost::lexical_cast<std::string>(count); 74 sql += " LIMIT " + boost::lexical_cast<std::string>(count);
74 } 75 }
76
75 if (since > 0) 77 if (since > 0)
76 { 78 {
79 if (count == 0)
80 {
81 sql += " LIMIT -1"; // In SQLite, "OFFSET" cannot appear without "LIMIT"
82 }
83
77 sql += " OFFSET " + boost::lexical_cast<std::string>(since); 84 sql += " OFFSET " + boost::lexical_cast<std::string>(since);
78 } 85 }
79 86
80 return sql; 87 return sql;
81 } 88 }
586 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 593 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
587 res.AddLabel(s.ColumnString(1)); 594 res.AddLabel(s.ColumnString(1));
588 } 595 }
589 } 596 }
590 597
591 if (request.IsRetrieveOneInstanceMetadataAndAttachments()) 598 if (request.GetLevel() != ResourceType_Instance &&
592 { 599 request.IsRetrieveOneInstanceMetadataAndAttachments())
593 throw OrthancException(ErrorCode_NotImplemented); 600 {
594 601 {
595 #if 0 602 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS OneInstance");
596 // need one instance identifier ? TODO: it might be actually more interesting to retrieve directly the attachment ids .... 603 s.Run();
597 if (requestLevel == ResourceType_Series) 604 }
598 { 605
599 sql = "SELECT Lookup.internalId, childLevel.publicId " 606 switch (requestLevel)
600 "FROM Resources AS childLevel " 607 {
601 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; 608 case ResourceType_Patient:
602 609 {
603 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 610 SQLite::Statement s(
611 db_, SQLITE_FROM_HERE,
612 "CREATE TEMPORARY TABLE OneInstance AS "
613 "SELECT Lookup.internalId AS parentInternalId, grandGrandChildLevel.publicId AS instancePublicId, grandGrandChildLevel.internalId AS instanceInternalId "
614 "FROM Resources AS grandGrandChildLevel "
615 "INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId "
616 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
617 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId");
618 s.Run();
619 break;
620 }
621
622 case ResourceType_Study:
623 {
624 SQLite::Statement s(
625 db_, SQLITE_FROM_HERE,
626 "CREATE TEMPORARY TABLE OneInstance AS "
627 "SELECT Lookup.internalId AS parentInternalId, grandChildLevel.publicId AS instancePublicId, grandChildLevel.internalId AS instanceInternalId "
628 "FROM Resources AS grandChildLevel "
629 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
630 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId");
631 s.Run();
632 break;
633 }
634
635 case ResourceType_Series:
636 {
637 SQLite::Statement s(
638 db_, SQLITE_FROM_HERE,
639 "CREATE TEMPORARY TABLE OneInstance AS "
640 "SELECT Lookup.internalId AS parentInternalId, childLevel.publicId AS instancePublicId, childLevel.internalId AS instanceInternalId "
641 "FROM Resources AS childLevel "
642 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId");
643 s.Run();
644 break;
645 }
646
647 default:
648 throw OrthancException(ErrorCode_InternalError);
649 }
650
651 {
652 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT parentInternalId, instancePublicId FROM OneInstance");
604 while (s.Step()) 653 while (s.Step())
605 { 654 {
606 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 655 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
607 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); 656 res.SetOneInstancePublicId(s.ColumnString(1));
608 } 657 }
609 } 658 }
610 else if (requestLevel == ResourceType_Study) 659
611 { 660 {
612 sql = "SELECT Lookup.internalId, grandChildLevel.publicId " 661 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT OneInstance.parentInternalId, Metadata.type, Metadata.value "
613 "FROM Resources AS grandChildLevel " 662 "FROM Metadata INNER JOIN OneInstance ON Metadata.id = OneInstance.instanceInternalId");
614 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
615 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId ";
616
617 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
618 while (s.Step()) 663 while (s.Step())
619 { 664 {
620 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 665 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
621 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); 666 res.AddOneInstanceMetadata(static_cast<MetadataType>(s.ColumnInt(1)), s.ColumnString(2));
622 } 667 }
623 } 668 }
624 else if (requestLevel == ResourceType_Patient) 669
625 { 670 {
626 sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId " 671 SQLite::Statement s(db_, SQLITE_FROM_HERE,
627 "FROM Resources AS grandGrandChildLevel " 672 "SELECT OneInstance.parentInternalId, AttachedFiles.fileType, AttachedFiles.uuid, "
628 "INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId " 673 "AttachedFiles.uncompressedSize, AttachedFiles.compressedSize, "
629 "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " 674 "AttachedFiles.compressionType, AttachedFiles.uncompressedMD5, AttachedFiles.compressedMD5 "
630 "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId "; 675 "FROM AttachedFiles INNER JOIN OneInstance ON AttachedFiles.id = OneInstance.instanceInternalId");
631
632 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
633 while (s.Step()) 676 while (s.Step())
634 { 677 {
635 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 678 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
636 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1)); 679 res.AddOneInstanceAttachment(
680 FileInfo(s.ColumnString(2), static_cast<FileContentType>(s.ColumnInt(1)),
681 s.ColumnInt64(3), s.ColumnString(6),
682 static_cast<CompressionType>(s.ColumnInt(5)),
683 s.ColumnInt64(4), s.ColumnString(7)));
637 } 684 }
638 } 685 }
639 else
640 {
641 throw OrthancException(ErrorCode_InternalError);
642 }
643 #endif
644 } 686 }
645 687
646 // need children metadata ? 688 // need children metadata ?
647 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMetadata().size() > 0) 689 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMetadata().size() > 0)
648 { 690 {
679 s.ColumnString(2)); 721 s.ColumnString(2));
680 } 722 }
681 } 723 }
682 724
683 // need children identifiers ? 725 // need children identifiers ?
684 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).IsRetrieveIdentifiers()) 726 if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Study).IsRetrieveIdentifiers()) ||
727 (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) ||
728 (requestLevel == ResourceType_Series && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers()))
685 { 729 {
686 sql = "SELECT Lookup.internalId, childLevel.publicId " 730 sql = "SELECT Lookup.internalId, childLevel.publicId "
687 "FROM Resources AS currentLevel " 731 "FROM Resources AS currentLevel "
688 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " 732 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
689 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "; 733 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId ";
695 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1), s.ColumnString(1)); 739 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1), s.ColumnString(1));
696 } 740 }
697 } 741 }
698 742
699 // need grandchildren identifiers ? 743 // need grandchildren identifiers ?
700 if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).IsRetrieveIdentifiers()) 744 if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) ||
745 (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers()))
701 { 746 {
702 sql = "SELECT Lookup.internalId, grandChildLevel.publicId " 747 sql = "SELECT Lookup.internalId, grandChildLevel.publicId "
703 "FROM Resources AS currentLevel " 748 "FROM Resources AS currentLevel "
704 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId " 749 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
705 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId " 750 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "
708 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql); 753 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
709 while (s.Step()) 754 while (s.Step())
710 { 755 {
711 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0)); 756 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
712 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2), s.ColumnString(1)); 757 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2), s.ColumnString(1));
758 }
759 }
760
761 // need grandgrandchildren identifiers ?
762 if (requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers())
763 {
764 sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId "
765 "FROM Resources AS currentLevel "
766 "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
767 "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "
768 "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "
769 "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId ";
770
771 SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
772 while (s.Step())
773 {
774 FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
775 res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1));
713 } 776 }
714 } 777 }
715 778
716 // need resource attachments ? 779 // need resource attachments ?
717 if (request.IsRetrieveAttachments()) 780 if (request.IsRetrieveAttachments())
867 virtual void GetAllPublicIds(std::list<std::string>& target, 930 virtual void GetAllPublicIds(std::list<std::string>& target,
868 ResourceType resourceType, 931 ResourceType resourceType,
869 int64_t since, 932 int64_t since,
870 uint32_t limit) ORTHANC_OVERRIDE 933 uint32_t limit) ORTHANC_OVERRIDE
871 { 934 {
872 if (limit == 0)
873 {
874 target.clear();
875 return;
876 }
877
878 SQLite::Statement s(db_, SQLITE_FROM_HERE, 935 SQLite::Statement s(db_, SQLITE_FROM_HERE,
879 "SELECT publicId FROM Resources WHERE " 936 "SELECT publicId FROM Resources WHERE "
880 "resourceType=? LIMIT ? OFFSET ?"); 937 "resourceType=? LIMIT ? OFFSET ?");
881 s.BindInt(0, resourceType); 938 s.BindInt(0, resourceType);
882 s.BindInt64(1, limit); 939 s.BindInt64(1, limit == 0 ? -1 : limit); // In SQLite, setting "LIMIT" to "-1" means "no limit"
883 s.BindInt64(2, since); 940 s.BindInt64(2, since);
884 941
885 target.clear(); 942 target.clear();
886 while (s.Step()) 943 while (s.Step())
887 { 944 {