Mercurial > hg > orthanc

comparison OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp @ 5809:023a99146dd0 attach-custom-data

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged find-refactoring -> attach-custom-data
author Alain Mazy <am@orthanc.team>
date Tue, 24 Sep 2024 12:53:43 +0200
parents 8279eaab0d1d 8a8756b2dd0b
children
comparison
equal deleted inserted replaced
5808:63c025cf6958 5809:023a99146dd0
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"
42 43
43 static std::map<std::string, std::string> filesToDeleteCustomData; 44 static std::map<std::string, std::string> filesToDeleteCustomData;
44 45
45 namespace Orthanc 46 namespace Orthanc
46 { 47 {
48 static std::string JoinRequestedMetadata(const FindRequest::ChildrenSpecification& childrenSpec)
49 {
50 std::set<std::string> metadataTypes;
51 for (std::set<MetadataType>::const_iterator it = childrenSpec.GetMetadata().begin(); it != childrenSpec.GetMetadata().end(); ++it)
52 {
53 metadataTypes.insert(boost::lexical_cast<std::string>(*it));
54 }
55 std::string joinedMetadataTypes;
56 Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ", ");
57
58 return joinedMetadataTypes;
59 }
60
61 static std::string JoinRequestedTags(const FindRequest::ChildrenSpecification& childrenSpec)
62 {
63 // note: SQLite does not seem to support (tagGroup, tagElement) in ((x, y), (z, w)) in complex subqueries.
64 // Therefore, since we expect the requested tag list to be short, we write it as
65 // ((tagGroup = x AND tagElement = y ) OR (tagGroup = z AND tagElement = w))
66
67 std::string sql = " (";
68 std::set<std::string> tags;
69 for (std::set<DicomTag>::const_iterator it = childrenSpec.GetMainDicomTags().begin(); it != childrenSpec.GetMainDicomTags().end(); ++it)
70 {
71 tags.insert("(tagGroup = " + boost::lexical_cast<std::string>(it->GetGroup())
72 + " AND tagElement = " + boost::lexical_cast<std::string>(it->GetElement()) + ")");
73 }
74 std::string joinedTags;
75 Orthanc::Toolbox::JoinStrings(joinedTags, tags, " OR ");
76
77 sql += joinedTags + ") ";
78 return sql;
79 }
80
81 static std::string JoinChanges(const std::set<ChangeType>& changeTypes)
82 {
83 std::set<std::string> changeTypesString;
84 for (std::set<ChangeType>::const_iterator it = changeTypes.begin(); it != changeTypes.end(); ++it)
85 {
86 changeTypesString.insert(boost::lexical_cast<std::string>(static_cast<uint32_t>(*it)));
87 }
88
89 std::string joinedChangesTypes;
90 Orthanc::Toolbox::JoinStrings(joinedChangesTypes, changeTypesString, ", ");
91
92 return joinedChangesTypes;
93 }
94
47 class SQLiteDatabaseWrapper::LookupFormatter : public ISqlLookupFormatter 95 class SQLiteDatabaseWrapper::LookupFormatter : public ISqlLookupFormatter
48 { 96 {
49 private: 97 private:
50 std::list<std::string> values_; 98 std::list<std::string> values_;
51 99
62 } 110 }
63 111
64 virtual std::string FormatWildcardEscape() ORTHANC_OVERRIDE 112 virtual std::string FormatWildcardEscape() ORTHANC_OVERRIDE
65 { 113 {
66 return "ESCAPE '\\'"; 114 return "ESCAPE '\\'";
115 }
116
117 virtual std::string FormatLimits(uint64_t since, uint64_t count) ORTHANC_OVERRIDE
118 {
119 std::string sql;
120
121 if (count > 0)
122 {
123 sql += " LIMIT " + boost::lexical_cast<std::string>(count);
124 }
125
126 if (since > 0)
127 {
128 if (count == 0)
129 {
130 sql += " LIMIT -1"; // In SQLite, "OFFSET" cannot appear without "LIMIT"
131 }
132
133 sql += " OFFSET " + boost::lexical_cast<std::string>(since);
134 }
135
136 return sql;
67 } 137 }
68 138
69 virtual bool IsEscapeBrackets() const ORTHANC_OVERRIDE 139 virtual bool IsEscapeBrackets() const ORTHANC_OVERRIDE
70 { 140 {
71 return false; 141 return false;
234 304
235 305
236 void GetChangesInternal(std::list<ServerIndexChange>& target, 306 void GetChangesInternal(std::list<ServerIndexChange>& target,
237 bool& done, 307 bool& done,
238 SQLite::Statement& s, 308 SQLite::Statement& s,
239 uint32_t limit) 309 uint32_t limit,
310 bool returnFirstResults) // the statement usually returns limit+1 results while we only need the limit results -> we need to know which ones to return, the firsts or the lasts
240 { 311 {
241 target.clear(); 312 target.clear();
242 313
243 while (target.size() < limit && s.Step()) 314 while (s.Step())
244 { 315 {
245 int64_t seq = s.ColumnInt64(0); 316 int64_t seq = s.ColumnInt64(0);
246 ChangeType changeType = static_cast<ChangeType>(s.ColumnInt(1)); 317 ChangeType changeType = static_cast<ChangeType>(s.ColumnInt(1));
247 ResourceType resourceType = static_cast<ResourceType>(s.ColumnInt(3)); 318 ResourceType resourceType = static_cast<ResourceType>(s.ColumnInt(3));
248 const std::string& date = s.ColumnString(4); 319 const std::string& date = s.ColumnString(4);
251 std::string publicId = GetPublicId(internalId); 322 std::string publicId = GetPublicId(internalId);
252 323
253 target.push_back(ServerIndexChange(seq, changeType, resourceType, publicId, date)); 324 target.push_back(ServerIndexChange(seq, changeType, resourceType, publicId, date));
254 } 325 }
255 326
256 done = !(target.size() == limit && s.Step()); 327 done = target.size() <= limit; // 'done' means "there are no more other changes of this type in that direction (depending on since/to)"
328
329 // if we have retrieved more changes than requested -> cleanup
330 if (target.size() > limit)
331 {
332 assert(target.size() == limit+1); // the statement should only request 1 element more
333
334 if (returnFirstResults)
335 {
336 target.pop_back();
337 }
338 else
339 {
340 target.pop_front();
341 }
342 }
257 } 343 }
258 344
259 345
260 void GetExportedResourcesInternal(std::list<ExportedResource>& target, 346 void GetExportedResourcesInternal(std::list<ExportedResource>& target,
261 bool& done, 347 bool& done,
353 LookupFormatter formatter; 439 LookupFormatter formatter;
354 440
355 std::string sql; 441 std::string sql;
356 LookupFormatter::Apply(sql, formatter, lookup, queryLevel, labels, labelsConstraint, limit); 442 LookupFormatter::Apply(sql, formatter, lookup, queryLevel, labels, labelsConstraint, limit);
357 443
358 sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; 444 sql = "CREATE TEMPORARY TABLE Lookup AS " + sql; // TODO-FIND: use a CTE (or is this method obsolete ?)
359 445
360 { 446 {
361 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup"); 447 SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
362 s.Run(); 448 s.Run();
363 } 449 }
383 resourcesId.push_back(s.ColumnString(0)); 469 resourcesId.push_back(s.ColumnString(0));
384 } 470 }
385 } 471 }
386 } 472 }
387 473
474 #define C0_QUERY_ID 0
475 #define C1_INTERNAL_ID 1
476 #define C2_ROW_NUMBER 2
477 #define C3_STRING_1 3
478 #define C4_STRING_2 4
479 #define C5_STRING_3 5
480 #define C6_STRING_4 6
481 #define C7_INT_1 7
482 #define C8_INT_2 8
483 #define C9_BIG_INT_1 9
484 #define C10_BIG_INT_2 10
485
486 #define QUERY_LOOKUP 1
487 #define QUERY_MAIN_DICOM_TAGS 2
488 #define QUERY_ATTACHMENTS 3
489 #define QUERY_METADATA 4
490 #define QUERY_LABELS 5
491 #define QUERY_PARENT_MAIN_DICOM_TAGS 10
492 #define QUERY_PARENT_IDENTIFIER 11
493 #define QUERY_PARENT_METADATA 12
494 #define QUERY_GRAND_PARENT_MAIN_DICOM_TAGS 15
495 #define QUERY_GRAND_PARENT_METADATA 16
496 #define QUERY_CHILDREN_IDENTIFIERS 20
497 #define QUERY_CHILDREN_MAIN_DICOM_TAGS 21
498 #define QUERY_CHILDREN_METADATA 22
499 #define QUERY_GRAND_CHILDREN_IDENTIFIERS 30
500 #define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 31
501 #define QUERY_GRAND_CHILDREN_METADATA 32
502 #define QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS 40
503 #define QUERY_ONE_INSTANCE_IDENTIFIER 50
504 #define QUERY_ONE_INSTANCE_METADATA 51
505 #define QUERY_ONE_INSTANCE_ATTACHMENTS 52
506
507 #define STRINGIFY(x) #x
508 #define TOSTRING(x) STRINGIFY(x)
509
510
511 virtual void ExecuteFind(FindResponse& response,
512 const FindRequest& request,
513 const Capabilities& capabilities) ORTHANC_OVERRIDE
514 {
515 LookupFormatter formatter;
516 std::string sql;
517 const ResourceType requestLevel = request.GetLevel();
518
519 std::string lookupSql;
520 LookupFormatter::Apply(lookupSql, formatter, request);
521
522 // base query, retrieve the ordered internalId and publicId of the selected resources
523 sql = "WITH Lookup AS (" + lookupSql + ") ";
524
525 // in SQLite, all CTEs must be created at the beginning of the query, you can not define local CTE inside subqueries
526 // need one instance info ? (part 1: create the CTE)
527 if (request.GetLevel() != ResourceType_Instance &&
528 request.IsRetrieveOneInstanceMetadataAndAttachments())
529 {
530 // Here, we create a nested CTE 'OneInstance' with one instance ID to join with metadata and main
531 sql += ", OneInstance AS";
532
533 switch (requestLevel)
534 {
535 case ResourceType_Series:
536 {
537 sql+= " (SELECT Lookup.internalId AS parentInternalId, childLevel.publicId AS instancePublicId, childLevel.internalId AS instanceInternalId"
538 " FROM Resources AS childLevel "
539 " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId) ";
540 break;
541 }
542
543 case ResourceType_Study:
544 {
545 sql+= " (SELECT Lookup.internalId AS parentInternalId, grandChildLevel.publicId AS instancePublicId, grandChildLevel.internalId AS instanceInternalId"
546 " FROM Resources AS grandChildLevel "
547 " INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
548 " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId) ";
549 break;
550 }
551
552 case ResourceType_Patient:
553 {
554 sql+= " (SELECT Lookup.internalId AS parentInternalId, grandGrandChildLevel.publicId AS instancePublicId, grandGrandChildLevel.internalId AS instanceInternalId"
555 " FROM Resources AS grandGrandChildLevel "
556 " INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId "
557 " INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
558 " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId) ";
559 break;
560 }
561
562 default:
563 throw OrthancException(ErrorCode_InternalError);
564 }
565 }
566
567 sql += "SELECT "
568 " " TOSTRING(QUERY_LOOKUP) " AS c0_queryId, "
569 " Lookup.internalId AS c1_internalId, "
570 " Lookup.rowNumber AS c2_rowNumber, "
571 " Lookup.publicId AS c3_string1, "
572 " NULL AS c4_string2, "
573 " NULL AS c5_string3, "
574 " NULL AS c6_string4, "
575 " NULL AS c7_int1, "
576 " NULL AS c8_int2, "
577 " NULL AS c9_big_int1, "
578 " NULL AS c10_big_int2 "
579 " FROM Lookup ";
580
581 // need one instance info ? (part 2: execute the queries)
582 if (request.GetLevel() != ResourceType_Instance &&
583 request.IsRetrieveOneInstanceMetadataAndAttachments())
584 {
585 sql += " UNION SELECT"
586 " " TOSTRING(QUERY_ONE_INSTANCE_IDENTIFIER) " AS c0_queryId, "
587 " parentInternalId AS c1_internalId, "
588 " NULL AS c2_rowNumber, "
589 " instancePublicId AS c3_string1, "
590 " NULL AS c4_string2, "
591 " NULL AS c5_string3, "
592 " NULL AS c6_string4, "
593 " NULL AS c7_int1, "
594 " NULL AS c8_int2, "
595 " instanceInternalId AS c9_big_int1, "
596 " NULL AS c10_big_int2 "
597 " FROM OneInstance ";
598
599 sql += " UNION SELECT"
600 " " TOSTRING(QUERY_ONE_INSTANCE_METADATA) " AS c0_queryId, "
601 " parentInternalId AS c1_internalId, "
602 " NULL AS c2_rowNumber, "
603 " Metadata.value AS c3_string1, "
604 " NULL AS c4_string2, "
605 " NULL AS c5_string3, "
606 " NULL AS c6_string4, "
607 " Metadata.type AS c7_int1, "
608 " NULL AS c8_int2, "
609 " NULL AS c9_big_int1, "
610 " NULL AS c10_big_int2 "
611 " FROM OneInstance "
612 " INNER JOIN Metadata ON Metadata.id = OneInstance.instanceInternalId ";
613
614 sql += " UNION SELECT"
615 " " TOSTRING(QUERY_ONE_INSTANCE_ATTACHMENTS) " AS c0_queryId, "
616 " parentInternalId AS c1_internalId, "
617 " NULL AS c2_rowNumber, "
618 " uuid AS c3_string1, "
619 " uncompressedMD5 AS c4_string2, "
620 " compressedMD5 AS c5_string3, "
621 " customData AS c6_string4, "
622 " fileType AS c7_int1, "
623 " compressionType AS c8_int2, "
624 " compressedSize AS c9_big_int1, "
625 " uncompressedSize AS c10_big_int2 "
626 " FROM OneInstance "
627 " INNER JOIN AttachedFiles ON AttachedFiles.id = OneInstance.instanceInternalId ";
628
629 }
630
631 // need MainDicomTags from resource ?
632 if (request.IsRetrieveMainDicomTags())
633 {
634 sql += "UNION SELECT "
635 " " TOSTRING(QUERY_MAIN_DICOM_TAGS) " AS c0_queryId, "
636 " Lookup.internalId AS c1_internalId, "
637 " NULL AS c2_rowNumber, "
638 " value AS c3_string1, "
639 " NULL AS c4_string2, "
640 " NULL AS c5_string3, "
641 " NULL AS c6_string4, "
642 " tagGroup AS c7_int1, "
643 " tagElement AS c8_int2, "
644 " NULL AS c9_big_int1, "
645 " NULL AS c10_big_int2 "
646 "FROM Lookup "
647 "INNER JOIN MainDicomTags ON MainDicomTags.id = Lookup.internalId ";
648 }
649
650 // need resource metadata ?
651 if (request.IsRetrieveMetadata())
652 {
653 sql += "UNION SELECT "
654 " " TOSTRING(QUERY_METADATA) " AS c0_queryId, "
655 " Lookup.internalId AS c1_internalId, "
656 " NULL AS c2_rowNumber, "
657 " value AS c3_string1, "
658 " NULL AS c4_string2, "
659 " NULL AS c5_string3, "
660 " NULL AS c6_string4, "
661 " type AS c7_int1, "
662 " NULL AS c8_int2, "
663 " NULL AS c9_big_int1, "
664 " NULL AS c10_big_int2 "
665 "FROM Lookup "
666 "INNER JOIN Metadata ON Metadata.id = Lookup.internalId ";
667 }
668
669 // need resource attachments ?
670 if (request.IsRetrieveAttachments())
671 {
672 sql += "UNION SELECT "
673 " " TOSTRING(QUERY_ATTACHMENTS) " AS c0_queryId, "
674 " Lookup.internalId AS c1_internalId, "
675 " NULL AS c2_rowNumber, "
676 " uuid AS c3_string1, "
677 " uncompressedMD5 AS c4_string2, "
678 " compressedMD5 AS c5_string3, "
679 " customData AS c6_string4, "
680 " fileType AS c7_int1, "
681 " compressionType AS c8_int2, "
682 " compressedSize AS c9_big_int1, "
683 " uncompressedSize AS c10_big_int2 "
684 "FROM Lookup "
685 "INNER JOIN AttachedFiles ON AttachedFiles.id = Lookup.internalId ";
686 }
687
688
689 // need resource labels ?
690 if (request.IsRetrieveLabels())
691 {
692 sql += "UNION SELECT "
693 " " TOSTRING(QUERY_LABELS) " AS c0_queryId, "
694 " Lookup.internalId AS c1_internalId, "
695 " NULL AS c2_rowNumber, "
696 " label AS c3_string1, "
697 " NULL AS c4_string2, "
698 " NULL AS c5_string3, "
699 " NULL AS c6_string4, "
700 " NULL AS c7_int1, "
701 " NULL AS c8_int2, "
702 " NULL AS c9_big_int1, "
703 " NULL AS c10_big_int2 "
704 "FROM Lookup "
705 "INNER JOIN Labels ON Labels.id = Lookup.internalId ";
706 }
707
708 if (requestLevel > ResourceType_Patient)
709 {
710 // need MainDicomTags from parent ?
711 if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMainDicomTags())
712 {
713 sql += "UNION SELECT "
714 " " TOSTRING(QUERY_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
715 " Lookup.internalId AS c1_internalId, "
716 " NULL AS c2_rowNumber, "
717 " value AS c3_string1, "
718 " NULL AS c4_string2, "
719 " NULL AS c5_string3, "
720 " NULL AS c6_string4, "
721 " tagGroup AS c7_int1, "
722 " tagElement AS c8_int2, "
723 " NULL AS c9_big_int1, "
724 " NULL AS c10_big_int2 "
725 "FROM Lookup "
726 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
727 "INNER JOIN MainDicomTags ON MainDicomTags.id = currentLevel.parentId ";
728 }
729
730 // need metadata from parent ?
731 if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMetadata())
732 {
733 sql += "UNION SELECT "
734 " " TOSTRING(QUERY_PARENT_METADATA) " AS c0_queryId, "
735 " Lookup.internalId AS c1_internalId, "
736 " NULL AS c2_rowNumber, "
737 " value AS c3_string1, "
738 " NULL AS c4_string2, "
739 " NULL AS c5_string3, "
740 " NULL AS c6_string4, "
741 " type AS c7_int1, "
742 " NULL AS c8_int2, "
743 " NULL AS c9_big_int1, "
744 " NULL AS c10_big_int2 "
745 "FROM Lookup "
746 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
747 "INNER JOIN Metadata ON Metadata.id = currentLevel.parentId ";
748 }
749
750 if (requestLevel > ResourceType_Study)
751 {
752 // need MainDicomTags from grandparent ?
753 if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMainDicomTags())
754 {
755 sql += "UNION SELECT "
756 " " TOSTRING(QUERY_GRAND_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
757 " Lookup.internalId AS c1_internalId, "
758 " NULL AS c2_rowNumber, "
759 " value AS c3_string1, "
760 " NULL AS c4_string2, "
761 " NULL AS c5_string3, "
762 " NULL AS c6_string4, "
763 " tagGroup AS c7_int1, "
764 " tagElement AS c8_int2, "
765 " NULL AS c9_big_int1, "
766 " NULL AS c10_big_int2 "
767 "FROM Lookup "
768 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
769 "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
770 "INNER JOIN MainDicomTags ON MainDicomTags.id = parentLevel.parentId ";
771 }
772
773 // need metadata from grandparent ?
774 if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMetadata())
775 {
776 sql += "UNION SELECT "
777 " " TOSTRING(QUERY_GRAND_PARENT_METADATA) " AS c0_queryId, "
778 " Lookup.internalId AS c1_internalId, "
779 " NULL AS c2_rowNumber, "
780 " value AS c3_string1, "
781 " NULL AS c4_string2, "
782 " NULL AS c5_string3, "
783 " NULL AS c6_string4, "
784 " type AS c7_int1, "
785 " NULL AS c8_int2, "
786 " NULL AS c9_big_int1, "
787 " NULL AS c10_big_int2 "
788 "FROM Lookup "
789 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
790 "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
791 "INNER JOIN Metadata ON Metadata.id = parentLevel.parentId ";
792 }
793 }
794 }
795
796 // need MainDicomTags from children ?
797 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMainDicomTags().size() > 0)
798 {
799 sql += "UNION SELECT "
800 " " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
801 " Lookup.internalId AS c1_internalId, "
802 " NULL AS c2_rowNumber, "
803 " value AS c3_string1, "
804 " NULL AS c4_string2, "
805 " NULL AS c5_string3, "
806 " NULL AS c6_string4, "
807 " tagGroup AS c7_int1, "
808 " tagElement AS c8_int2, "
809 " NULL AS c9_big_int1, "
810 " NULL AS c10_big_int2 "
811 "FROM Lookup "
812 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
813 " INNER JOIN MainDicomTags ON MainDicomTags.id = childLevel.internalId AND " + JoinRequestedTags(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)));
814 }
815
816 // need MainDicomTags from grandchildren ?
817 if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMainDicomTags().size() > 0)
818 {
819 sql += "UNION SELECT "
820 " " TOSTRING(QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
821 " Lookup.internalId AS c1_internalId, "
822 " NULL AS c2_rowNumber, "
823 " value AS c3_string1, "
824 " NULL AS c4_string2, "
825 " NULL AS c5_string3, "
826 " NULL AS c6_string4, "
827 " tagGroup AS c7_int1, "
828 " tagElement AS c8_int2, "
829 " NULL AS c9_big_int1, "
830 " NULL AS c10_big_int2 "
831 "FROM Lookup "
832 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
833 " INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId "
834 " INNER JOIN MainDicomTags ON MainDicomTags.id = grandChildLevel.internalId AND " + JoinRequestedTags(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)));
835 }
836
837 // need parent identifier ?
838 if (request.IsRetrieveParentIdentifier())
839 {
840 sql += "UNION SELECT "
841 " " TOSTRING(QUERY_PARENT_IDENTIFIER) " AS c0_queryId, "
842 " Lookup.internalId AS c1_internalId, "
843 " NULL AS c2_rowNumber, "
844 " parentLevel.publicId AS c3_string1, "
845 " NULL AS c4_string2, "
846 " NULL AS c5_string3, "
847 " NULL AS c6_string4, "
848 " NULL AS c7_int1, "
849 " NULL AS c8_int2, "
850 " NULL AS c9_big_int1, "
851 " NULL AS c10_big_int2 "
852 "FROM Lookup "
853 " INNER JOIN Resources currentLevel ON currentLevel.internalId = Lookup.internalId "
854 " INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId ";
855 }
856
857 // need children metadata ?
858 if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMetadata().size() > 0)
859 {
860 sql += "UNION SELECT "
861 " " TOSTRING(QUERY_CHILDREN_METADATA) " AS c0_queryId, "
862 " Lookup.internalId AS c1_internalId, "
863 " NULL AS c2_rowNumber, "
864 " value AS c3_string1, "
865 " NULL AS c4_string2, "
866 " NULL AS c5_string3, "
867 " NULL AS c6_string4, "
868 " type AS c7_int1, "
869 " NULL AS c8_int2, "
870 " NULL AS c9_big_int1, "
871 " NULL AS c10_big_int2 "
872 "FROM Lookup "
873 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
874 " INNER JOIN Metadata ON Metadata.id = childLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1))) + ") ";
875 }
876
877 // need grandchildren metadata ?
878 if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMetadata().size() > 0)
879 {
880 sql += "UNION SELECT "
881 " " TOSTRING(QUERY_GRAND_CHILDREN_METADATA) " AS c0_queryId, "
882 " Lookup.internalId AS c1_internalId, "
883 " NULL AS c2_rowNumber, "
884 " value AS c3_string1, "
885 " NULL AS c4_string2, "
886 " NULL AS c5_string3, "
887 " NULL AS c6_string4, "
888 " type AS c7_int1, "
889 " NULL AS c8_int2, "
890 " NULL AS c9_big_int1, "
891 " NULL AS c10_big_int2 "
892 "FROM Lookup "
893 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
894 " INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId "
895 " INNER JOIN Metadata ON Metadata.id = grandChildLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2))) + ") ";
896 }
897
898 // need children identifiers ?
899 if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Study).IsRetrieveIdentifiers()) ||
900 (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) ||
901 (requestLevel == ResourceType_Series && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers()))
902 {
903 sql += "UNION SELECT "
904 " " TOSTRING(QUERY_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
905 " Lookup.internalId AS c1_internalId, "
906 " NULL AS c2_rowNumber, "
907 " childLevel.publicId AS c3_string1, "
908 " NULL AS c4_string2, "
909 " NULL AS c5_string3, "
910 " NULL AS c6_string4, "
911 " NULL AS c7_int1, "
912 " NULL AS c8_int2, "
913 " NULL AS c9_big_int1, "
914 " NULL AS c10_big_int2 "
915 "FROM Lookup "
916 " INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId ";
917 }
918
919 // need grandchildren identifiers ?
920 if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) ||
921 (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers()))
922 {
923 sql += "UNION SELECT "
924 " " TOSTRING(QUERY_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
925 " Lookup.internalId AS c1_internalId, "
926 " NULL AS c2_rowNumber, "
927 " grandChildLevel.publicId AS c3_string1, "
928 " NULL AS c4_string2, "
929 " NULL AS c5_string3, "
930 " NULL AS c6_string4, "
931 " NULL AS c7_int1, "
932 " NULL AS c8_int2, "
933 " NULL AS c9_big_int1, "
934 " NULL AS c10_big_int2 "
935 "FROM Lookup "
936 "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
937 "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId ";
938 }
939
940 // need grandgrandchildren identifiers ?
941 if (requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers())
942 {
943 sql += "UNION SELECT "
944 " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
945 " Lookup.internalId AS c1_internalId, "
946 " NULL AS c2_rowNumber, "
947 " grandGrandChildLevel.publicId AS c3_string1, "
948 " NULL AS c4_string2, "
949 " NULL AS c5_string3, "
950 " NULL AS c6_string4, "
951 " NULL AS c7_int1, "
952 " NULL AS c8_int2, "
953 " NULL AS c9_big_int1, "
954 " NULL AS c10_big_int2 "
955 "FROM Lookup "
956 "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
957 "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "
958 "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId ";
959 }
960
961
962 sql += " ORDER BY c0_queryId, c2_rowNumber"; // this is really important to make sure that the Lookup query is the first one to provide results since we use it to create the responses element !
963
964 SQLite::Statement s(db_, SQLITE_FROM_HERE_DYNAMIC(sql), sql);
965 formatter.Bind(s);
966
967 while (s.Step())
968 {
969 int queryId = s.ColumnInt(C0_QUERY_ID);
970 int64_t internalId = s.ColumnInt64(C1_INTERNAL_ID);
971
972 // LOG(INFO) << queryId << ": " << internalId;
973 // continue;
974
975 assert(queryId == QUERY_LOOKUP || response.HasResource(internalId)); // the QUERY_LOOKUP must be read first and must create the response before any other query tries to populate the fields
976
977 switch (queryId)
978 {
979 case QUERY_LOOKUP:
980 response.Add(new FindResponse::Resource(requestLevel, internalId, s.ColumnString(C3_STRING_1)));
981 break;
982
983 case QUERY_LABELS:
984 {
985 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
986 res.AddLabel(s.ColumnString(C3_STRING_1));
987 }; break;
988
989 case QUERY_ATTACHMENTS:
990 {
991 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
992 FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C7_INT_1)),
993 s.ColumnInt64(C9_BIG_INT_1), s.ColumnString(C4_STRING_2),
994 static_cast<CompressionType>(s.ColumnInt(C8_INT_2)),
995 s.ColumnInt64(C10_BIG_INT_2), s.ColumnString(C5_STRING_3), s.ColumnString(C6_STRING_4));
996 res.AddAttachment(file);
997 }; break;
998
999 case QUERY_MAIN_DICOM_TAGS:
1000 {
1001 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1002 res.AddStringDicomTag(requestLevel,
1003 static_cast<uint16_t>(s.ColumnInt(C7_INT_1)),
1004 static_cast<uint16_t>(s.ColumnInt(C8_INT_2)),
1005 s.ColumnString(C3_STRING_1));
1006 }; break;
1007
1008 case QUERY_PARENT_MAIN_DICOM_TAGS:
1009 {
1010 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1011 res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 1),
1012 static_cast<uint16_t>(s.ColumnInt(C7_INT_1)),
1013 static_cast<uint16_t>(s.ColumnInt(C8_INT_2)),
1014 s.ColumnString(C3_STRING_1));
1015 }; break;
1016
1017 case QUERY_GRAND_PARENT_MAIN_DICOM_TAGS:
1018 {
1019 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1020 res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 2),
1021 static_cast<uint16_t>(s.ColumnInt(C7_INT_1)),
1022 static_cast<uint16_t>(s.ColumnInt(C8_INT_2)),
1023 s.ColumnString(C3_STRING_1));
1024 }; break;
1025
1026 case QUERY_CHILDREN_MAIN_DICOM_TAGS:
1027 {
1028 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1029 res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 1),
1030 DicomTag(static_cast<uint16_t>(s.ColumnInt(C7_INT_1)), static_cast<uint16_t>(s.ColumnInt(C8_INT_2))),
1031 s.ColumnString(C3_STRING_1));
1032 }; break;
1033
1034 case QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS:
1035 {
1036 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1037 res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 2),
1038 DicomTag(static_cast<uint16_t>(s.ColumnInt(C7_INT_1)), static_cast<uint16_t>(s.ColumnInt(C8_INT_2))),
1039 s.ColumnString(C3_STRING_1));
1040 }; break;
1041
1042 case QUERY_METADATA:
1043 {
1044 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1045 res.AddMetadata(static_cast<ResourceType>(requestLevel),
1046 static_cast<MetadataType>(s.ColumnInt(C7_INT_1)),
1047 s.ColumnString(C3_STRING_1));
1048 }; break;
1049
1050 case QUERY_PARENT_METADATA:
1051 {
1052 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1053 res.AddMetadata(static_cast<ResourceType>(requestLevel - 1),
1054 static_cast<MetadataType>(s.ColumnInt(C7_INT_1)),
1055 s.ColumnString(C3_STRING_1));
1056 }; break;
1057
1058 case QUERY_GRAND_PARENT_METADATA:
1059 {
1060 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1061 res.AddMetadata(static_cast<ResourceType>(requestLevel - 2),
1062 static_cast<MetadataType>(s.ColumnInt(C7_INT_1)),
1063 s.ColumnString(C3_STRING_1));
1064 }; break;
1065
1066 case QUERY_CHILDREN_METADATA:
1067 {
1068 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1069 res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 1),
1070 static_cast<MetadataType>(s.ColumnInt(C7_INT_1)),
1071 s.ColumnString(C3_STRING_1));
1072 }; break;
1073
1074 case QUERY_GRAND_CHILDREN_METADATA:
1075 {
1076 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1077 res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 2),
1078 static_cast<MetadataType>(s.ColumnInt(C7_INT_1)),
1079 s.ColumnString(C3_STRING_1));
1080 }; break;
1081
1082 case QUERY_PARENT_IDENTIFIER:
1083 {
1084 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1085 res.SetParentIdentifier(s.ColumnString(C3_STRING_1));
1086 }; break;
1087
1088 case QUERY_CHILDREN_IDENTIFIERS:
1089 {
1090 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1091 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1),
1092 s.ColumnString(C3_STRING_1));
1093 }; break;
1094
1095 case QUERY_GRAND_CHILDREN_IDENTIFIERS:
1096 {
1097 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1098 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2),
1099 s.ColumnString(C3_STRING_1));
1100 }; break;
1101
1102 case QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS:
1103 {
1104 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1105 res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 3),
1106 s.ColumnString(C3_STRING_1));
1107 }; break;
1108
1109 case QUERY_ONE_INSTANCE_IDENTIFIER:
1110 {
1111 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1112 res.SetOneInstancePublicId(s.ColumnString(C3_STRING_1));
1113 }; break;
1114
1115 case QUERY_ONE_INSTANCE_METADATA:
1116 {
1117 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1118 res.AddOneInstanceMetadata(static_cast<MetadataType>(s.ColumnInt(C7_INT_1)), s.ColumnString(C3_STRING_1));
1119 }; break;
1120
1121 case QUERY_ONE_INSTANCE_ATTACHMENTS:
1122 {
1123 FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
1124 FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C7_INT_1)),
1125 s.ColumnInt64(C9_BIG_INT_1), s.ColumnString(C4_STRING_2),
1126 static_cast<CompressionType>(s.ColumnInt(C8_INT_2)),
1127 s.ColumnInt64(C10_BIG_INT_2), s.ColumnString(C5_STRING_3), s.ColumnString(C6_STRING_4));
1128 res.AddOneInstanceAttachment(file);
1129 }; break;
1130
1131 default:
1132 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
1133 }
1134 }
1135 }
388 1136
389 // From the "ICreateInstance" interface 1137 // From the "ICreateInstance" interface
390 virtual void AttachChild(int64_t parent, 1138 virtual void AttachChild(int64_t parent,
391 int64_t child) ORTHANC_OVERRIDE 1139 int64_t child) ORTHANC_OVERRIDE
392 { 1140 {
539 virtual void GetAllPublicIds(std::list<std::string>& target, 1287 virtual void GetAllPublicIds(std::list<std::string>& target,
540 ResourceType resourceType, 1288 ResourceType resourceType,
541 int64_t since, 1289 int64_t since,
542 uint32_t limit) ORTHANC_OVERRIDE 1290 uint32_t limit) ORTHANC_OVERRIDE
543 { 1291 {
544 if (limit == 0)
545 {
546 target.clear();
547 return;
548 }
549
550 SQLite::Statement s(db_, SQLITE_FROM_HERE, 1292 SQLite::Statement s(db_, SQLITE_FROM_HERE,
551 "SELECT publicId FROM Resources WHERE " 1293 "SELECT publicId FROM Resources WHERE "
552 "resourceType=? LIMIT ? OFFSET ?"); 1294 "resourceType=? LIMIT ? OFFSET ?");
553 s.BindInt(0, resourceType); 1295 s.BindInt(0, resourceType);
554 s.BindInt64(1, limit); 1296 s.BindInt64(1, limit == 0 ? -1 : limit); // In SQLite, setting "LIMIT" to "-1" means "no limit"
555 s.BindInt64(2, since); 1297 s.BindInt64(2, since);
556 1298
557 target.clear(); 1299 target.clear();
558 while (s.Step()) 1300 while (s.Step())
559 { 1301 {
565 virtual void GetChanges(std::list<ServerIndexChange>& target /*out*/, 1307 virtual void GetChanges(std::list<ServerIndexChange>& target /*out*/,
566 bool& done /*out*/, 1308 bool& done /*out*/,
567 int64_t since, 1309 int64_t since,
568 uint32_t limit) ORTHANC_OVERRIDE 1310 uint32_t limit) ORTHANC_OVERRIDE
569 { 1311 {
570 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes WHERE seq>? ORDER BY seq LIMIT ?"); 1312 std::set<ChangeType> filter;
571 s.BindInt64(0, since); 1313 GetChangesExtended(target, done, since, -1, limit, filter);
572 s.BindInt(1, limit + 1); 1314 }
573 GetChangesInternal(target, done, s, limit); 1315
1316 virtual void GetChangesExtended(std::list<ServerIndexChange>& target /*out*/,
1317 bool& done /*out*/,
1318 int64_t since,
1319 int64_t to,
1320 uint32_t limit,
1321 const std::set<ChangeType>& filterType) ORTHANC_OVERRIDE
1322 {
1323 std::vector<std::string> filters;
1324 bool hasSince = false;
1325 bool hasTo = false;
1326
1327 if (since > 0)
1328 {
1329 hasSince = true;
1330 filters.push_back("seq>?");
1331 }
1332 if (to != -1)
1333 {
1334 hasTo = true;
1335 filters.push_back("seq<=?");
1336 }
1337 if (filterType.size() != 0)
1338 {
1339 filters.push_back("changeType IN ( " + JoinChanges(filterType) + " )");
1340 }
1341
1342 std::string filtersString;
1343 if (filters.size() > 0)
1344 {
1345 Toolbox::JoinStrings(filtersString, filters, " AND ");
1346 filtersString = "WHERE " + filtersString;
1347 }
1348
1349 std::string sql;
1350 bool returnFirstResults;
1351 if (hasTo && !hasSince)
1352 {
1353 // in this case, we want the largest values in the LIMIT clause but we want them ordered in ascending order
1354 sql = "SELECT * FROM (SELECT * FROM Changes " + filtersString + " ORDER BY seq DESC LIMIT ?) ORDER BY seq ASC";
1355 returnFirstResults = false;
1356 }
1357 else
1358 {
1359 // default query: we want the smallest values ordered in ascending order
1360 sql = "SELECT * FROM Changes " + filtersString + " ORDER BY seq ASC LIMIT ?";
1361 returnFirstResults = true;
1362 }
1363
1364 SQLite::Statement s(db_, SQLITE_FROM_HERE_DYNAMIC(sql), sql);
1365
1366 int paramCounter = 0;
1367 if (hasSince)
1368 {
1369 s.BindInt64(paramCounter++, since);
1370 }
1371 if (hasTo)
1372 {
1373 s.BindInt64(paramCounter++, to);
1374 }
1375
1376 s.BindInt(paramCounter++, limit + 1); // we take limit+1 because we use the +1 to know if "Done" must be set to true
1377 GetChangesInternal(target, done, s, limit, returnFirstResults);
574 } 1378 }
575 1379
576 1380
577 virtual void GetChildrenMetadata(std::list<std::string>& target, 1381 virtual void GetChildrenMetadata(std::list<std::string>& target,
578 int64_t resourceId, 1382 int64_t resourceId,
629 1433
630 virtual void GetLastChange(std::list<ServerIndexChange>& target /*out*/) ORTHANC_OVERRIDE 1434 virtual void GetLastChange(std::list<ServerIndexChange>& target /*out*/) ORTHANC_OVERRIDE
631 { 1435 {
632 bool done; // Ignored 1436 bool done; // Ignored
633 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes ORDER BY seq DESC LIMIT 1"); 1437 SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes ORDER BY seq DESC LIMIT 1");
634 GetChangesInternal(target, done, s, 1); 1438 GetChangesInternal(target, done, s, 1, true);
635 } 1439 }
636 1440
637 1441
638 int64_t GetLastChangeIndex() ORTHANC_OVERRIDE 1442 int64_t GetLastChangeIndex() ORTHANC_OVERRIDE
639 { 1443 {
1360 version_(0) 2164 version_(0)
1361 { 2165 {
1362 // TODO: implement revisions in SQLite 2166 // TODO: implement revisions in SQLite
1363 dbCapabilities_.SetFlushToDisk(true); 2167 dbCapabilities_.SetFlushToDisk(true);
1364 dbCapabilities_.SetLabelsSupport(true); 2168 dbCapabilities_.SetLabelsSupport(true);
2169 dbCapabilities_.SetHasExtendedChanges(true);
2170 dbCapabilities_.SetHasFindSupport(true);
1365 db_.Open(path); 2171 db_.Open(path);
1366 } 2172 }
1367 2173
1368 2174
1369 SQLiteDatabaseWrapper::SQLiteDatabaseWrapper() : 2175 SQLiteDatabaseWrapper::SQLiteDatabaseWrapper() :
1372 version_(0) 2178 version_(0)
1373 { 2179 {
1374 // TODO: implement revisions in SQLite 2180 // TODO: implement revisions in SQLite
1375 dbCapabilities_.SetFlushToDisk(true); 2181 dbCapabilities_.SetFlushToDisk(true);
1376 dbCapabilities_.SetLabelsSupport(true); 2182 dbCapabilities_.SetLabelsSupport(true);
2183 dbCapabilities_.SetHasExtendedChanges(true);
2184 dbCapabilities_.SetHasFindSupport(true);
1377 db_.OpenInMemory(); 2185 db_.OpenInMemory();
1378 } 2186 }
1379 2187
1380 SQLiteDatabaseWrapper::~SQLiteDatabaseWrapper() 2188 SQLiteDatabaseWrapper::~SQLiteDatabaseWrapper()
1381 { 2189 {