Mercurial > hg > orthanc-databases

comparison Framework/Plugins/IndexBackend.cpp @ 569:f18e46d7dbf8 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 15:04:21 +0200
parents cd9521e04249 77c8544bbd7d
children
comparison
equal deleted inserted replaced
368:82f73188b58d 569:f18e46d7dbf8
1 /** 1 /**
2 * Orthanc - A Lightweight, RESTful DICOM Store 2 * Orthanc - A Lightweight, RESTful DICOM Store
3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics 3 * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
4 * Department, University Hospital of Liege, Belgium 4 * Department, University Hospital of Liege, Belgium
5 * Copyright (C) 2017-2021 Osimis S.A., Belgium 5 * Copyright (C) 2017-2023 Osimis S.A., Belgium
6 * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
7 * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
6 * 8 *
7 * This program is free software: you can redistribute it and/or 9 * This program is free software: you can redistribute it and/or
8 * modify it under the terms of the GNU Affero General Public License 10 * modify it under the terms of the GNU Affero General Public License
9 * as published by the Free Software Foundation, either version 3 of 11 * as published by the Free Software Foundation, either version 3 of
10 * the License, or (at your option) any later version. 12 * the License, or (at your option) any later version.
19 **/ 21 **/
20 22
21 23
22 #include "IndexBackend.h" 24 #include "IndexBackend.h"
23 25
24 #include "../../Resources/Orthanc/Databases/ISqlLookupFormatter.h"
25 #include "../Common/BinaryStringValue.h" 26 #include "../Common/BinaryStringValue.h"
26 #include "../Common/Integer64Value.h" 27 #include "../Common/Integer64Value.h"
27 #include "../Common/Utf8StringValue.h" 28 #include "../Common/Utf8StringValue.h"
28 #include "DatabaseBackendAdapterV2.h" 29 #include "DatabaseBackendAdapterV2.h"
29 #include "DatabaseBackendAdapterV3.h" 30 #include "DatabaseBackendAdapterV3.h"
31 #include "GlobalProperties.h" 32 #include "GlobalProperties.h"
32 33
33 #include <Compatibility.h> // For std::unique_ptr<> 34 #include <Compatibility.h> // For std::unique_ptr<>
34 #include <Logging.h> 35 #include <Logging.h>
35 #include <OrthancException.h> 36 #include <OrthancException.h>
37 #include <Toolbox.h>
36 38
37 39
38 namespace OrthancDatabases 40 namespace OrthancDatabases
39 { 41 {
40 static std::string ConvertWildcardToLike(const std::string& query) 42 static std::string ConvertWildcardToLike(const std::string& query)
56 // TODO Escape underscores and percents 58 // TODO Escape underscores and percents
57 59
58 return s; 60 return s;
59 } 61 }
60 62
63 static std::string JoinChanges(const std::set<uint32_t>& changeTypes)
64 {
65 std::set<std::string> changeTypesString;
66 for (std::set<uint32_t>::const_iterator it = changeTypes.begin(); it != changeTypes.end(); ++it)
67 {
68 changeTypesString.insert(boost::lexical_cast<std::string>(*it));
69 }
70
71 std::string joinedChangesTypes;
72 Orthanc::Toolbox::JoinStrings(joinedChangesTypes, changeTypesString, ", ");
73
74 return joinedChangesTypes;
75 }
61 76
62 template <typename T> 77 template <typename T>
63 static void ReadListOfIntegers(std::list<T>& target, 78 static void ReadListOfIntegers(std::list<T>& target,
64 DatabaseManager::CachedStatement& statement, 79 DatabaseManager::CachedStatement& statement,
65 const Dictionary& args) 80 const Dictionary& args)
108 } 123 }
109 } 124 }
110 } 125 }
111 126
112 127
128 namespace // Anonymous namespace to avoid clashes between compilation modules
129 {
130 struct Change
131 {
132 int64_t seq_;
133 int32_t changeType_;
134 OrthancPluginResourceType resourceType_;
135 std::string publicId_;
136 std::string changeDate_;
137
138 Change(int64_t seq, int32_t changeType, OrthancPluginResourceType resourceType, const std::string& publicId, const std::string& changeDate)
139 : seq_(seq), changeType_(changeType), resourceType_(resourceType), publicId_(publicId), changeDate_(changeDate)
140 {
141 }
142 };
143 }
144
145
113 void IndexBackend::ReadChangesInternal(IDatabaseBackendOutput& output, 146 void IndexBackend::ReadChangesInternal(IDatabaseBackendOutput& output,
114 bool& done, 147 bool& done,
115 DatabaseManager& manager, 148 DatabaseManager& manager,
116 DatabaseManager::CachedStatement& statement, 149 DatabaseManager::CachedStatement& statement,
117 const Dictionary& args, 150 const Dictionary& args,
118 uint32_t maxResults) 151 uint32_t limit,
152 bool returnFirstResults)
119 { 153 {
120 statement.Execute(args); 154 statement.Execute(args);
121 155
122 uint32_t count = 0; 156 std::list<Change> changes;
123 157 while (!statement.IsDone())
124 while (count < maxResults && 158 {
125 !statement.IsDone()) 159 changes.push_back(Change(
126 {
127 output.AnswerChange(
128 statement.ReadInteger64(0), 160 statement.ReadInteger64(0),
129 statement.ReadInteger32(1), 161 statement.ReadInteger32(1),
130 static_cast<OrthancPluginResourceType>(statement.ReadInteger32(2)), 162 static_cast<OrthancPluginResourceType>(statement.ReadInteger32(2)),
131 statement.ReadString(3), 163 statement.ReadString(3),
132 statement.ReadString(4)); 164 statement.ReadString(4)
165 ));
133 166
134 statement.Next(); 167 statement.Next();
135 count++; 168 }
136 } 169
137 170 done = changes.size() <= limit; // 'done' means we have returned all requested changes
138 done = (count < maxResults || 171
139 statement.IsDone()); 172 // if we have retrieved more changes than requested -> cleanup
173 if (changes.size() > limit)
174 {
175 assert(changes.size() == limit+1); // the statement should only request 1 element more
176
177 if (returnFirstResults)
178 {
179 changes.pop_back();
180 }
181 else
182 {
183 changes.pop_front();
184 }
185 }
186
187 for (std::list<Change>::const_iterator it = changes.begin(); it != changes.end(); ++it)
188 {
189 output.AnswerChange(it->seq_, it->changeType_, it->resourceType_, it->publicId_, it->changeDate_);
190 }
140 } 191 }
141 192
142 193
143 void IndexBackend::ReadExportedResourcesInternal(IDatabaseBackendOutput& output, 194 void IndexBackend::ReadExportedResourcesInternal(IDatabaseBackendOutput& output,
144 bool& done, 195 bool& done,
145 DatabaseManager::CachedStatement& statement, 196 DatabaseManager::CachedStatement& statement,
146 const Dictionary& args, 197 const Dictionary& args,
147 uint32_t maxResults) 198 uint32_t limit)
148 { 199 {
149 statement.Execute(args); 200 statement.Execute(args);
150 201
151 uint32_t count = 0; 202 uint32_t count = 0;
152 203
153 while (count < maxResults && 204 while (count < limit &&
154 !statement.IsDone()) 205 !statement.IsDone())
155 { 206 {
156 int64_t seq = statement.ReadInteger64(0); 207 int64_t seq = statement.ReadInteger64(0);
157 OrthancPluginResourceType resourceType = 208 OrthancPluginResourceType resourceType =
158 static_cast<OrthancPluginResourceType>(statement.ReadInteger32(1)); 209 static_cast<OrthancPluginResourceType>(statement.ReadInteger32(1));
170 221
171 statement.Next(); 222 statement.Next();
172 count++; 223 count++;
173 } 224 }
174 225
175 done = (count < maxResults || 226 done = (count < limit ||
176 statement.IsDone()); 227 statement.IsDone());
177 } 228 }
229
230 void IndexBackend::ClearRemainingAncestor(DatabaseManager& manager)
231 {
232 DatabaseManager::CachedStatement statement(
233 STATEMENT_FROM_HERE, manager,
234 "DELETE FROM RemainingAncestor");
235
236 statement.Execute();
237 }
238
178 239
179 240
180 void IndexBackend::ClearDeletedFiles(DatabaseManager& manager) 241 void IndexBackend::ClearDeletedFiles(DatabaseManager& manager)
181 { 242 {
182 DatabaseManager::CachedStatement statement( 243 DatabaseManager::CachedStatement statement(
215 statement.ReadInteger64(2), 276 statement.ReadInteger64(2),
216 statement.ReadString(3), 277 statement.ReadString(3),
217 statement.ReadInteger32(4), 278 statement.ReadInteger32(4),
218 statement.ReadInteger64(5), 279 statement.ReadInteger64(5),
219 statement.ReadString(6), 280 statement.ReadString(6),
220 statement.ReadString(8)); 281 statement.ReadStringOrNull(8));
221 282
222 statement.Next(); 283 statement.Next();
223 } 284 }
224 } 285 }
225 286
439 DatabaseManager& manager, 500 DatabaseManager& manager,
440 int64_t id) 501 int64_t id)
441 { 502 {
442 ClearDeletedFiles(manager); 503 ClearDeletedFiles(manager);
443 ClearDeletedResources(manager); 504 ClearDeletedResources(manager);
444 505 ClearRemainingAncestor(manager);
445 {
446 DatabaseManager::CachedStatement statement(
447 STATEMENT_FROM_HERE, manager,
448 "DELETE FROM RemainingAncestor");
449
450 statement.Execute();
451 }
452 506
453 { 507 {
454 DatabaseManager::CachedStatement statement( 508 DatabaseManager::CachedStatement statement(
455 STATEMENT_FROM_HERE, manager, 509 STATEMENT_FROM_HERE, manager,
456 "DELETE FROM Resources WHERE internalId=${id}"); 510 "DELETE FROM Resources WHERE internalId=${id}");
466 520
467 { 521 {
468 DatabaseManager::CachedStatement statement( 522 DatabaseManager::CachedStatement statement(
469 STATEMENT_FROM_HERE, manager, 523 STATEMENT_FROM_HERE, manager,
470 "SELECT * FROM RemainingAncestor"); 524 "SELECT * FROM RemainingAncestor");
471
472 statement.Execute(); 525 statement.Execute();
473 526
474 if (!statement.IsDone()) 527 if (!statement.IsDone())
475 { 528 {
476 output.SignalRemainingAncestor( 529 output.SignalRemainingAncestor(
479 532
480 // There is at most 1 remaining ancestor 533 // There is at most 1 remaining ancestor
481 assert((statement.Next(), statement.IsDone())); 534 assert((statement.Next(), statement.IsDone()));
482 } 535 }
483 } 536 }
484 537
485 SignalDeletedFiles(output, manager); 538 SignalDeletedFiles(output, manager);
486 SignalDeletedResources(output, manager); 539 SignalDeletedResources(output, manager);
540
487 } 541 }
488 542
489 543
490 void IndexBackend::GetAllInternalIds(std::list<int64_t>& target, 544 void IndexBackend::GetAllInternalIds(std::list<int64_t>& target,
491 DatabaseManager& manager, 545 DatabaseManager& manager,
524 578
525 579
526 void IndexBackend::GetAllPublicIds(std::list<std::string>& target, 580 void IndexBackend::GetAllPublicIds(std::list<std::string>& target,
527 DatabaseManager& manager, 581 DatabaseManager& manager,
528 OrthancPluginResourceType resourceType, 582 OrthancPluginResourceType resourceType,
529 uint64_t since, 583 int64_t since,
530 uint64_t limit) 584 uint32_t limit)
531 { 585 {
532 std::string suffix; 586 std::string suffix;
533 if (manager.GetDialect() == Dialect_MSSQL) 587 if (manager.GetDialect() == Dialect_MSSQL)
534 { 588 {
535 suffix = "OFFSET ${since} ROWS FETCH FIRST ${limit} ROWS ONLY"; 589 suffix = "OFFSET ${since} ROWS FETCH FIRST ${limit} ROWS ONLY";
536 } 590 }
537 else 591 else if (limit > 0)
538 { 592 {
539 suffix = "LIMIT ${limit} OFFSET ${since}"; 593 suffix = "LIMIT ${limit} OFFSET ${since}";
540 } 594 }
541 595
542 DatabaseManager::CachedStatement statement( 596 std::string sql = "SELECT publicId FROM (SELECT publicId FROM Resources "
543 STATEMENT_FROM_HERE, manager, 597 "WHERE resourceType=${type}) AS tmp ORDER BY tmp.publicId " + suffix;
544 "SELECT publicId FROM (SELECT publicId FROM Resources " 598
545 "WHERE resourceType=${type}) AS tmp ORDER BY tmp.publicId " + suffix); 599 DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
546 600
547 statement.SetReadOnly(true); 601 statement.SetReadOnly(true);
602
603 Dictionary args;
604
548 statement.SetParameterType("type", ValueType_Integer64); 605 statement.SetParameterType("type", ValueType_Integer64);
549 statement.SetParameterType("limit", ValueType_Integer64);
550 statement.SetParameterType("since", ValueType_Integer64);
551
552 Dictionary args;
553 args.SetIntegerValue("type", static_cast<int>(resourceType)); 606 args.SetIntegerValue("type", static_cast<int>(resourceType));
554 args.SetIntegerValue("limit", limit); 607
555 args.SetIntegerValue("since", since); 608 if (limit > 0)
609 {
610 statement.SetParameterType("limit", ValueType_Integer64);
611 statement.SetParameterType("since", ValueType_Integer64);
612 args.SetIntegerValue("limit", limit);
613 args.SetIntegerValue("since", since);
614 }
556 615
557 ReadListOfStrings(target, statement, args); 616 ReadListOfStrings(target, statement, args);
558 } 617 }
559 618
560
561 /* Use GetOutput().AnswerChange() */
562 void IndexBackend::GetChanges(IDatabaseBackendOutput& output, 619 void IndexBackend::GetChanges(IDatabaseBackendOutput& output,
563 bool& done /*out*/, 620 bool& done /*out*/,
564 DatabaseManager& manager, 621 DatabaseManager& manager,
565 int64_t since, 622 int64_t since,
566 uint32_t maxResults) 623 uint32_t limit)
567 { 624 {
568 std::string suffix; 625 std::set<uint32_t> changeTypes;
626 GetChangesExtended(output, done, manager, since, -1, changeTypes, limit);
627 }
628
629 /* Use GetOutput().AnswerChange() */
630 void IndexBackend::GetChangesExtended(IDatabaseBackendOutput& output,
631 bool& done /*out*/,
632 DatabaseManager& manager,
633 int64_t since,
634 int64_t to,
635 const std::set<uint32_t>& changeTypes,
636 uint32_t limit)
637 {
638 std::string limitSuffix;
569 if (manager.GetDialect() == Dialect_MSSQL) 639 if (manager.GetDialect() == Dialect_MSSQL)
570 { 640 {
571 suffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY"; 641 limitSuffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY";
572 } 642 }
573 else 643 else
574 { 644 {
575 suffix = "LIMIT ${limit}"; 645 limitSuffix = "LIMIT ${limit}";
576 } 646 }
577 647
578 DatabaseManager::CachedStatement statement( 648 std::vector<std::string> filters;
579 STATEMENT_FROM_HERE, manager, 649 bool hasSince = false;
580 "SELECT Changes.seq, Changes.changeType, Changes.resourceType, Resources.publicId, " 650 bool hasTo = false;
581 "Changes.date FROM Changes INNER JOIN Resources " 651
582 "ON Changes.internalId = Resources.internalId WHERE seq>${since} ORDER BY seq " + suffix); 652 if (since > 0)
583 653 {
654 hasSince = true;
655 filters.push_back("seq>${since}");
656 }
657 if (to != -1)
658 {
659 hasTo = true;
660 filters.push_back("seq<=${to}");
661 }
662 #if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1
663 if (changeTypes.size() > 0)
664 {
665 filters.push_back("changeType IN (" + JoinChanges(changeTypes) + ") ");
666 }
667 #endif
668
669 std::string filtersString;
670 if (filters.size() > 0)
671 {
672 Orthanc::Toolbox::JoinStrings(filtersString, filters, " AND ");
673 filtersString = "WHERE " + filtersString;
674 }
675
676 std::string sql;
677 bool returnFirstResults;
678 if (hasTo && !hasSince)
679 {
680 // in this case, we want the largest values but we want them ordered in ascending order
681 sql = "SELECT * FROM (SELECT Changes.seq, Changes.changeType, Changes.resourceType, Resources.publicId, Changes.date "
682 "FROM Changes INNER JOIN Resources "
683 "ON Changes.internalId = Resources.internalId " + filtersString + " ORDER BY seq DESC " + limitSuffix +
684 ") AS FilteredChanges ORDER BY seq ASC";
685
686 returnFirstResults = false;
687 }
688 else
689 {
690 // default query: we want the smallest values ordered in ascending order
691 sql = "SELECT Changes.seq, Changes.changeType, Changes.resourceType, Resources.publicId, "
692 "Changes.date FROM Changes INNER JOIN Resources "
693 "ON Changes.internalId = Resources.internalId " + filtersString + " ORDER BY seq ASC " + limitSuffix;
694 returnFirstResults = true;
695 }
696
697 DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
584 statement.SetReadOnly(true); 698 statement.SetReadOnly(true);
699 Dictionary args;
700
585 statement.SetParameterType("limit", ValueType_Integer64); 701 statement.SetParameterType("limit", ValueType_Integer64);
586 statement.SetParameterType("since", ValueType_Integer64); 702 args.SetIntegerValue("limit", limit + 1); // we take limit+1 because we use the +1 to know if "Done" must be set to true
587 703
588 Dictionary args; 704 if (hasSince)
589 args.SetIntegerValue("limit", maxResults + 1); 705 {
590 args.SetIntegerValue("since", since); 706 statement.SetParameterType("since", ValueType_Integer64);
591 707 args.SetIntegerValue("since", since);
592 ReadChangesInternal(output, done, manager, statement, args, maxResults); 708 }
709
710 if (hasTo)
711 {
712 statement.SetParameterType("to", ValueType_Integer64);
713 args.SetIntegerValue("to", to);
714 }
715
716 ReadChangesInternal(output, done, manager, statement, args, limit, returnFirstResults);
593 } 717 }
594 718
595 719
596 void IndexBackend::GetChildrenInternalId(std::list<int64_t>& target /*out*/, 720 void IndexBackend::GetChildrenInternalId(std::list<int64_t>& target /*out*/,
597 DatabaseManager& manager, 721 DatabaseManager& manager,
634 /* Use GetOutput().AnswerExportedResource() */ 758 /* Use GetOutput().AnswerExportedResource() */
635 void IndexBackend::GetExportedResources(IDatabaseBackendOutput& output, 759 void IndexBackend::GetExportedResources(IDatabaseBackendOutput& output,
636 bool& done /*out*/, 760 bool& done /*out*/,
637 DatabaseManager& manager, 761 DatabaseManager& manager,
638 int64_t since, 762 int64_t since,
639 uint32_t maxResults) 763 uint32_t limit)
640 { 764 {
641 std::string suffix; 765 std::string suffix;
642 if (manager.GetDialect() == Dialect_MSSQL) 766 if (manager.GetDialect() == Dialect_MSSQL)
643 { 767 {
644 suffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY"; 768 suffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY";
655 statement.SetReadOnly(true); 779 statement.SetReadOnly(true);
656 statement.SetParameterType("limit", ValueType_Integer64); 780 statement.SetParameterType("limit", ValueType_Integer64);
657 statement.SetParameterType("since", ValueType_Integer64); 781 statement.SetParameterType("since", ValueType_Integer64);
658 782
659 Dictionary args; 783 Dictionary args;
660 args.SetIntegerValue("limit", maxResults + 1); 784 args.SetIntegerValue("limit", limit + 1);
661 args.SetIntegerValue("since", since); 785 args.SetIntegerValue("since", since);
662 786
663 ReadExportedResourcesInternal(output, done, statement, args, maxResults); 787 ReadExportedResourcesInternal(output, done, statement, args, limit);
664 } 788 }
665 789
666 790
667 /* Use GetOutput().AnswerChange() */ 791 /* Use GetOutput().AnswerChange() */
668 void IndexBackend::GetLastChange(IDatabaseBackendOutput& output, 792 void IndexBackend::GetLastChange(IDatabaseBackendOutput& output,
687 statement.SetReadOnly(true); 811 statement.SetReadOnly(true);
688 812
689 Dictionary args; 813 Dictionary args;
690 814
691 bool done; // Ignored 815 bool done; // Ignored
692 ReadChangesInternal(output, done, manager, statement, args, 1); 816 ReadChangesInternal(output, done, manager, statement, args, 1, true);
693 } 817 }
694 818
695 819
696 /* Use GetOutput().AnswerExportedResource() */ 820 /* Use GetOutput().AnswerExportedResource() */
697 void IndexBackend::GetLastExportedResource(IDatabaseBackendOutput& output, 821 void IndexBackend::GetLastExportedResource(IDatabaseBackendOutput& output,
762 886
763 statement.Execute(args); 887 statement.Execute(args);
764 888
765 if (statement.IsDone()) 889 if (statement.IsDone())
766 { 890 {
767 throw Orthanc::OrthancException(Orthanc::ErrorCode_UnknownResource); 891 throw Orthanc::OrthancException(Orthanc::ErrorCode_UnknownResource, "No public id found for internal id");
768 } 892 }
769 else 893 else
770 { 894 {
771 return statement.ReadString(0); 895 return statement.ReadString(0);
772 } 896 }
830 954
831 statement.Execute(args); 955 statement.Execute(args);
832 956
833 if (statement.IsDone()) 957 if (statement.IsDone())
834 { 958 {
835 throw Orthanc::OrthancException(Orthanc::ErrorCode_UnknownResource); 959 throw Orthanc::OrthancException(Orthanc::ErrorCode_UnknownResource, "No resource type found for internal id.");
836 } 960 }
837 else 961 else
838 { 962 {
839 return static_cast<OrthancPluginResourceType>(statement.ReadInteger32(0)); 963 return static_cast<OrthancPluginResourceType>(statement.ReadInteger32(0));
840 } 964 }
1015 statement.Execute(args); 1139 statement.Execute(args);
1016 } 1140 }
1017 1141
1018 1142
1019 void IndexBackend::LogExportedResource(DatabaseManager& manager, 1143 void IndexBackend::LogExportedResource(DatabaseManager& manager,
1020 const OrthancPluginExportedResource& resource) 1144 OrthancPluginResourceType resourceType,
1145 const char* publicId,
1146 const char* modality,
1147 const char* date,
1148 const char* patientId,
1149 const char* studyInstanceUid,
1150 const char* seriesInstanceUid,
1151 const char* sopInstanceUid)
1021 { 1152 {
1022 DatabaseManager::CachedStatement statement( 1153 DatabaseManager::CachedStatement statement(
1023 STATEMENT_FROM_HERE, manager, 1154 STATEMENT_FROM_HERE, manager,
1024 "INSERT INTO ExportedResources VALUES(${AUTOINCREMENT} ${type}, ${publicId}, " 1155 "INSERT INTO ExportedResources VALUES(${AUTOINCREMENT} ${type}, ${publicId}, "
1025 "${modality}, ${patient}, ${study}, ${series}, ${instance}, ${date})"); 1156 "${modality}, ${patient}, ${study}, ${series}, ${instance}, ${date})");
1032 statement.SetParameterType("series", ValueType_Utf8String); 1163 statement.SetParameterType("series", ValueType_Utf8String);
1033 statement.SetParameterType("instance", ValueType_Utf8String); 1164 statement.SetParameterType("instance", ValueType_Utf8String);
1034 statement.SetParameterType("date", ValueType_Utf8String); 1165 statement.SetParameterType("date", ValueType_Utf8String);
1035 1166
1036 Dictionary args; 1167 Dictionary args;
1037 args.SetIntegerValue("type", resource.resourceType); 1168 args.SetIntegerValue("type", resourceType);
1038 args.SetUtf8Value("publicId", resource.publicId); 1169 args.SetUtf8Value("publicId", publicId);
1039 args.SetUtf8Value("modality", resource.modality); 1170 args.SetUtf8Value("modality", modality);
1040 args.SetUtf8Value("patient", resource.patientId); 1171 args.SetUtf8Value("patient", patientId);
1041 args.SetUtf8Value("study", resource.studyInstanceUid); 1172 args.SetUtf8Value("study", studyInstanceUid);
1042 args.SetUtf8Value("series", resource.seriesInstanceUid); 1173 args.SetUtf8Value("series", seriesInstanceUid);
1043 args.SetUtf8Value("instance", resource.sopInstanceUid); 1174 args.SetUtf8Value("instance", sopInstanceUid);
1044 args.SetUtf8Value("date", resource.date); 1175 args.SetUtf8Value("date", date);
1045 1176
1046 statement.Execute(args); 1177 statement.Execute(args);
1047 } 1178 }
1048 1179
1049 1180
1183 return ReadGlobalProperty(target, statement, args); 1314 return ReadGlobalProperty(target, statement, args);
1184 } 1315 }
1185 } 1316 }
1186 } 1317 }
1187 1318
1188 1319 bool IndexBackend::HasAtomicIncrementGlobalProperty()
1320 {
1321 return false; // currently only implemented in Postgres
1322 }
1323
1324 int64_t IndexBackend::IncrementGlobalProperty(DatabaseManager& manager,
1325 const char* serverIdentifier,
1326 int32_t property,
1327 int64_t increment)
1328 {
1329 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
1330 }
1331
1332 bool IndexBackend::HasUpdateAndGetStatistics()
1333 {
1334 return false; // currently only implemented in Postgres
1335 }
1336
1337 void IndexBackend::UpdateAndGetStatistics(DatabaseManager& manager,
1338 int64_t& patientsCount,
1339 int64_t& studiesCount,
1340 int64_t& seriesCount,
1341 int64_t& instancesCount,
1342 int64_t& compressedSize,
1343 int64_t& uncompressedSize)
1344 {
1345 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
1346 }
1347
1348 bool IndexBackend::HasMeasureLatency()
1349 {
1350 return true;
1351 }
1352
1353
1189 void IndexBackend::LookupIdentifier(std::list<int64_t>& target /*out*/, 1354 void IndexBackend::LookupIdentifier(std::list<int64_t>& target /*out*/,
1190 DatabaseManager& manager, 1355 DatabaseManager& manager,
1191 OrthancPluginResourceType resourceType, 1356 OrthancPluginResourceType resourceType,
1192 uint16_t group, 1357 uint16_t group,
1193 uint16_t element, 1358 uint16_t element,
1961 ReadListOfStrings(childrenPublicIds, statement, args); 2126 ReadListOfStrings(childrenPublicIds, statement, args);
1962 } 2127 }
1963 2128
1964 2129
1965 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 2130 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
1966 class IndexBackend::LookupFormatter : public Orthanc::ISqlLookupFormatter 2131 class IndexBackend::LookupFormatter : public ISqlLookupFormatter
1967 { 2132 {
1968 private: 2133 private:
1969 Dialect dialect_; 2134 Dialect dialect_;
1970 size_t count_; 2135 size_t count_;
1971 Dictionary dictionary_; 2136 Dictionary dictionary_;
1992 return "${" + key + "}"; 2157 return "${" + key + "}";
1993 } 2158 }
1994 2159
1995 virtual std::string FormatResourceType(Orthanc::ResourceType level) 2160 virtual std::string FormatResourceType(Orthanc::ResourceType level)
1996 { 2161 {
1997 return boost::lexical_cast<std::string>(Orthanc::Plugins::Convert(level)); 2162 return boost::lexical_cast<std::string>(MessagesToolbox::ConvertToPlainC(level));
1998 } 2163 }
1999 2164
2000 virtual std::string FormatWildcardEscape() 2165 virtual std::string FormatWildcardEscape()
2001 { 2166 {
2002 switch (dialect_) 2167 switch (dialect_)
2012 default: 2177 default:
2013 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); 2178 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
2014 } 2179 }
2015 } 2180 }
2016 2181
2182 virtual std::string FormatLimits(uint64_t since, uint64_t count)
2183 {
2184 std::string sql;
2185
2186 switch (dialect_)
2187 {
2188 case Dialect_MSSQL:
2189 {
2190 if (since > 0)
2191 {
2192 sql += " OFFSET " + boost::lexical_cast<std::string>(since) + " ROWS ";
2193 }
2194 if (count > 0)
2195 {
2196 sql += " FETCH NEXT " + boost::lexical_cast<std::string>(count) + " ROWS ONLY ";
2197 }
2198 }; break;
2199 case Dialect_SQLite:
2200 case Dialect_PostgreSQL:
2201 case Dialect_MySQL:
2202 {
2203 if (count > 0)
2204 {
2205 sql += " LIMIT " + boost::lexical_cast<std::string>(count);
2206 }
2207 if (since > 0)
2208 {
2209 sql += " OFFSET " + boost::lexical_cast<std::string>(since);
2210 }
2211 }; break;
2212 default:
2213 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
2214 }
2215
2216 return sql;
2217 }
2218
2017 virtual bool IsEscapeBrackets() const 2219 virtual bool IsEscapeBrackets() const
2018 { 2220 {
2019 // This was initially done at a bad location by the following changeset: 2221 // This was initially done at a bad location by the following changeset:
2020 // https://hg.orthanc-server.com/orthanc-databases/rev/389c037387ea 2222 // https://orthanc.uclouvain.be/hg/orthanc-databases/rev/389c037387ea
2021 return (dialect_ == Dialect_MSSQL); 2223 return (dialect_ == Dialect_MSSQL);
2022 } 2224 }
2023 2225
2024 void PrepareStatement(DatabaseManager::StandaloneStatement& statement) const 2226 void PrepareStatement(DatabaseManager::StandaloneStatement& statement) const
2025 { 2227 {
2041 2243
2042 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 2244 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
2043 // New primitive since Orthanc 1.5.2 2245 // New primitive since Orthanc 1.5.2
2044 void IndexBackend::LookupResources(IDatabaseBackendOutput& output, 2246 void IndexBackend::LookupResources(IDatabaseBackendOutput& output,
2045 DatabaseManager& manager, 2247 DatabaseManager& manager,
2046 const std::vector<Orthanc::DatabaseConstraint>& lookup, 2248 const DatabaseConstraints& lookup,
2047 OrthancPluginResourceType queryLevel, 2249 OrthancPluginResourceType queryLevel_,
2250 const std::set<std::string>& labels,
2251 LabelsConstraint labelsConstraint,
2048 uint32_t limit, 2252 uint32_t limit,
2049 bool requestSomeInstance) 2253 bool requestSomeInstance)
2050 { 2254 {
2051 LookupFormatter formatter(manager.GetDialect()); 2255 LookupFormatter formatter(manager.GetDialect());
2256 Orthanc::ResourceType queryLevel = MessagesToolbox::Convert(queryLevel_);
2257 Orthanc::ResourceType lowerLevel, upperLevel;
2258 ISqlLookupFormatter::GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup);
2052 2259
2053 std::string sql; 2260 std::string sql;
2054 Orthanc::ISqlLookupFormatter::Apply(sql, formatter, lookup, 2261 bool enableNewStudyCode = true;
2055 Orthanc::Plugins::Convert(queryLevel), limit); 2262
2056 2263 if (enableNewStudyCode && lowerLevel == queryLevel && upperLevel == queryLevel)
2057 if (requestSomeInstance) 2264 {
2058 { 2265 ISqlLookupFormatter::ApplySingleLevel(sql, formatter, lookup, queryLevel, labels, labelsConstraint, limit);
2059 // Composite query to find some instance if requested 2266
2060 switch (queryLevel) 2267 if (requestSomeInstance)
2061 { 2268 {
2062 case OrthancPluginResourceType_Patient: 2269 // Composite query to find some instance if requested
2063 sql = ("SELECT patients.publicId, MIN(instances.publicId) FROM (" + sql + ") patients " 2270 switch (queryLevel)
2064 "INNER JOIN Resources studies ON studies.parentId = patients.internalId " 2271 {
2065 "INNER JOIN Resources series ON series.parentId = studies.internalId " 2272 case Orthanc::ResourceType_Patient:
2066 "INNER JOIN Resources instances ON instances.parentId = series.internalId " 2273 sql = ("SELECT patients_studies.patients_public_id, MIN(instances.publicId) AS instances_public_id "
2067 "GROUP BY patients.publicId"); 2274 "FROM (SELECT patients.publicId AS patients_public_id, MIN(studies.internalId) AS studies_internal_id "
2068 break; 2275 "FROM (" + sql +
2069 2276 ") AS patients "
2070 case OrthancPluginResourceType_Study: 2277 "INNER JOIN Resources studies ON studies.parentId = patients.internalId "
2071 sql = ("SELECT studies.publicId, MIN(instances.publicId) FROM (" + sql + ") studies " 2278 "GROUP BY patients.publicId "
2072 "INNER JOIN Resources series ON series.parentId = studies.internalId " 2279 ") AS patients_studies "
2073 "INNER JOIN Resources instances ON instances.parentId = series.internalId " 2280 "INNER JOIN Resources series ON series.parentId = patients_studies.studies_internal_id "
2074 "GROUP BY studies.publicId"); 2281 "INNER JOIN Resources instances ON instances.parentId = series.internalId "
2075 break; 2282 "GROUP BY patients_studies.patients_public_id");
2076 2283 break;
2077 case OrthancPluginResourceType_Series: 2284 case Orthanc::ResourceType_Study:
2078 sql = ("SELECT series.publicId, MIN(instances.publicId) FROM (" + sql + ") series " 2285 sql = ("SELECT studies_series.studies_public_id, MIN(instances.publicId) AS instances_public_id "
2079 "INNER JOIN Resources instances ON instances.parentId = series.internalId " 2286 "FROM (SELECT studies.publicId AS studies_public_id, MIN(series.internalId) AS series_internal_id "
2080 "GROUP BY series.publicId"); 2287 "FROM (" + sql +
2081 break; 2288 ") AS studies "
2082 2289 "INNER JOIN Resources series ON series.parentId = studies.internalId "
2083 case OrthancPluginResourceType_Instance: 2290 "GROUP BY studies.publicId "
2084 sql = ("SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances"); 2291 ") AS studies_series "
2085 break; 2292 "INNER JOIN Resources instances ON instances.parentId = studies_series.series_internal_id "
2086 2293 "GROUP BY studies_series.studies_public_id");
2087 default: 2294 break;
2088 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); 2295 case Orthanc::ResourceType_Series:
2296 sql = ("SELECT series.publicId AS series_public_id, MIN(instances.publicId) AS instances_public_id "
2297 "FROM (" + sql +
2298 ") AS series "
2299 "INNER JOIN Resources instances ON instances.parentId = series.internalId "
2300 "GROUP BY series.publicId ");
2301 break;
2302
2303 case Orthanc::ResourceType_Instance:
2304 sql = ("SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances");
2305 break;
2306
2307 default:
2308 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
2309 }
2310 }
2311 }
2312 else
2313 {
2314 ISqlLookupFormatter::Apply(sql, formatter, lookup, queryLevel, labels, labelsConstraint, limit);
2315
2316 if (requestSomeInstance)
2317 {
2318 // Composite query to find some instance if requested
2319 switch (queryLevel)
2320 {
2321 case Orthanc::ResourceType_Patient:
2322 sql = ("SELECT patients.publicId, MIN(instances.publicId) FROM (" + sql + ") patients "
2323 "INNER JOIN Resources studies ON studies.parentId = patients.internalId "
2324 "INNER JOIN Resources series ON series.parentId = studies.internalId "
2325 "INNER JOIN Resources instances ON instances.parentId = series.internalId "
2326 "GROUP BY patients.publicId");
2327 break;
2328
2329 case Orthanc::ResourceType_Study:
2330 sql = ("SELECT studies.publicId, MIN(instances.publicId) FROM (" + sql + ") studies "
2331 "INNER JOIN Resources series ON series.parentId = studies.internalId "
2332 "INNER JOIN Resources instances ON instances.parentId = series.internalId "
2333 "GROUP BY studies.publicId");
2334 break;
2335 case Orthanc::ResourceType_Series:
2336 sql = ("SELECT series.publicId, MIN(instances.publicId) FROM (" + sql + ") series "
2337 "INNER JOIN Resources instances ON instances.parentId = series.internalId "
2338 "GROUP BY series.publicId");
2339 break;
2340
2341 case Orthanc::ResourceType_Instance:
2342 sql = ("SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances");
2343 break;
2344
2345 default:
2346 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
2347 }
2089 } 2348 }
2090 } 2349 }
2091 2350
2092 DatabaseManager::StandaloneStatement statement(manager, sql); 2351 DatabaseManager::StandaloneStatement statement(manager, sql);
2093 formatter.PrepareStatement(statement); 2352 formatter.PrepareStatement(statement);
2361 statement.Execute(args); 2620 statement.Execute(args);
2362 } 2621 }
2363 } 2622 }
2364 2623
2365 2624
2366 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in 1.3.1 2625 // New primitive since Orthanc 1.5.4
2367 # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 5, 4)
2368 // New primitive since Orthanc 1.5.4
2369 bool IndexBackend::LookupResourceAndParent(int64_t& id, 2626 bool IndexBackend::LookupResourceAndParent(int64_t& id,
2370 OrthancPluginResourceType& type, 2627 OrthancPluginResourceType& type,
2371 std::string& parentPublicId, 2628 std::string& parentPublicId,
2372 DatabaseManager& manager, 2629 DatabaseManager& manager,
2373 const char* publicId) 2630 const char* publicId)
2421 2678
2422 assert((statement.Next(), statement.IsDone())); 2679 assert((statement.Next(), statement.IsDone()));
2423 return true; 2680 return true;
2424 } 2681 }
2425 } 2682 }
2426 # endif
2427 #endif
2428 2683
2429 2684
2430 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in 1.3.1
2431 # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 5, 4)
2432 // New primitive since Orthanc 1.5.4 2685 // New primitive since Orthanc 1.5.4
2433 void IndexBackend::GetAllMetadata(std::map<int32_t, std::string>& result, 2686 void IndexBackend::GetAllMetadata(std::map<int32_t, std::string>& result,
2434 DatabaseManager& manager, 2687 DatabaseManager& manager,
2435 int64_t id) 2688 int64_t id)
2436 { 2689 {
2463 result[statement.ReadInteger32(0)] = statement.ReadString(1); 2716 result[statement.ReadInteger32(0)] = statement.ReadString(1);
2464 statement.Next(); 2717 statement.Next();
2465 } 2718 }
2466 } 2719 }
2467 } 2720 }
2468 # endif
2469 #endif
2470 2721
2471 2722
2472 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 2723 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
2473 void IndexBackend::CreateInstanceGeneric(OrthancPluginCreateInstanceResult& result, 2724 void IndexBackend::CreateInstanceGeneric(OrthancPluginCreateInstanceResult& result,
2474 DatabaseManager& manager, 2725 DatabaseManager& manager,
2587 assert(result.instanceId != -1); 2838 assert(result.instanceId != -1);
2588 } 2839 }
2589 #endif 2840 #endif
2590 2841
2591 2842
2843 void IndexBackend::AddLabel(DatabaseManager& manager,
2844 int64_t resource,
2845 const std::string& label)
2846 {
2847 std::unique_ptr<DatabaseManager::CachedStatement> statement;
2848
2849 switch (manager.GetDialect())
2850 {
2851 case Dialect_PostgreSQL:
2852 statement.reset(new DatabaseManager::CachedStatement(
2853 STATEMENT_FROM_HERE, manager,
2854 "INSERT INTO Labels VALUES(${id}, ${label}) ON CONFLICT DO NOTHING"));
2855 break;
2856
2857 case Dialect_SQLite:
2858 statement.reset(new DatabaseManager::CachedStatement(
2859 STATEMENT_FROM_HERE, manager,
2860 "INSERT OR IGNORE INTO Labels VALUES(${id}, ${label})"));
2861 break;
2862
2863 case Dialect_MySQL:
2864 statement.reset(new DatabaseManager::CachedStatement(
2865 STATEMENT_FROM_HERE, manager,
2866 "INSERT IGNORE INTO Labels VALUES(${id}, ${label})"));
2867 break;
2868
2869 default:
2870 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
2871 }
2872
2873 statement->SetParameterType("id", ValueType_Integer64);
2874 statement->SetParameterType("label", ValueType_Utf8String);
2875
2876 Dictionary args;
2877 args.SetIntegerValue("id", resource);
2878 args.SetUtf8Value("label", label);
2879
2880 statement->Execute(args);
2881 }
2882
2883
2884 void IndexBackend::RemoveLabel(DatabaseManager& manager,
2885 int64_t resource,
2886 const std::string& label)
2887 {
2888 DatabaseManager::CachedStatement statement(
2889 STATEMENT_FROM_HERE, manager,
2890 "DELETE FROM Labels WHERE id=${id} AND label=${label}");
2891
2892 statement.SetParameterType("id", ValueType_Integer64);
2893 statement.SetParameterType("label", ValueType_Utf8String);
2894
2895 Dictionary args;
2896 args.SetIntegerValue("id", resource);
2897 args.SetUtf8Value("label", label);
2898
2899 statement.Execute(args);
2900 }
2901
2902
2903 void IndexBackend::ListLabels(std::list<std::string>& target,
2904 DatabaseManager& manager,
2905 int64_t resource)
2906 {
2907 DatabaseManager::CachedStatement statement(
2908 STATEMENT_FROM_HERE, manager,
2909 "SELECT label FROM Labels WHERE id=${id}");
2910
2911 statement.SetReadOnly(true);
2912 statement.SetParameterType("id", ValueType_Integer64);
2913
2914 Dictionary args;
2915 args.SetIntegerValue("id", resource);
2916
2917 ReadListOfStrings(target, statement, args);
2918 }
2919
2920
2921 void IndexBackend::ListAllLabels(std::list<std::string>& target,
2922 DatabaseManager& manager)
2923 {
2924 DatabaseManager::CachedStatement statement(
2925 STATEMENT_FROM_HERE, manager,
2926 "SELECT DISTINCT label FROM Labels");
2927
2928 Dictionary args;
2929 ReadListOfStrings(target, statement, args);
2930 }
2931
2932
2592 void IndexBackend::Register(IndexBackend* backend, 2933 void IndexBackend::Register(IndexBackend* backend,
2593 size_t countConnections, 2934 size_t countConnections,
2594 unsigned int maxDatabaseRetries) 2935 unsigned int maxDatabaseRetries)
2595 { 2936 {
2596 if (backend == NULL) 2937 if (backend == NULL)
2597 { 2938 {
2598 throw Orthanc::OrthancException(Orthanc::ErrorCode_NullPointer); 2939 throw Orthanc::OrthancException(Orthanc::ErrorCode_NullPointer);
2599 } 2940 }
2600 2941
2601 bool hasLoadedV3OrAbove = false; 2942 LOG(WARNING) << "The index plugin will use " << countConnections << " connection(s) to the database, "
2943 << "and will retry up to " << maxDatabaseRetries << " time(s) in the case of a collision";
2602 2944
2603 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in Orthanc 1.3.1 2945 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in Orthanc 1.3.1
2604 # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) 2946 # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
2605 if (OrthancPluginCheckVersionAdvanced(backend->GetContext(), 1, 12, 0) == 1) 2947 if (OrthancPluginCheckVersionAdvanced(backend->GetContext(), 1, 12, 0) == 1)
2606 { 2948 {
2607 LOG(WARNING) << "The index plugin will use " << countConnections << " connection(s) to the database, " 2949 DatabaseBackendAdapterV4::Register(backend, countConnections, maxDatabaseRetries);
2608 << "and will retry up to " << maxDatabaseRetries << " time(s) in the case of a collision"; 2950 return;
2609
2610 OrthancDatabases::DatabaseBackendAdapterV4::Register(backend, countConnections, maxDatabaseRetries);
2611 hasLoadedV3OrAbove = true;
2612 }
2613 # elif ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 9, 2)
2614 if (OrthancPluginCheckVersionAdvanced(backend->GetContext(), 1, 9, 2) == 1)
2615 {
2616 LOG(WARNING) << "The index plugin will use " << countConnections << " connection(s) to the database, "
2617 << "and will retry up to " << maxDatabaseRetries << " time(s) in the case of a collision";
2618
2619 OrthancDatabases::DatabaseBackendAdapterV3::Register(backend, countConnections, maxDatabaseRetries);
2620 hasLoadedV3OrAbove = true;
2621 } 2951 }
2622 # endif 2952 # endif
2623 #endif 2953 #endif
2624 2954
2625 if (!hasLoadedV3OrAbove) 2955 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in Orthanc 1.3.1
2626 { 2956 # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 9, 2)
2627 LOG(WARNING) << "Performance warning: Your version of the Orthanc core or SDK doesn't support multiple readers/writers"; 2957 if (OrthancPluginCheckVersionAdvanced(backend->GetContext(), 1, 9, 2) == 1)
2628 OrthancDatabases::DatabaseBackendAdapterV2::Register(backend); 2958 {
2629 } 2959 DatabaseBackendAdapterV3::Register(backend, countConnections, maxDatabaseRetries);
2960 return;
2961 }
2962 # endif
2963 #endif
2964
2965 LOG(WARNING) << "Performance warning: Your version of the Orthanc core or SDK doesn't support multiple readers/writers";
2966 DatabaseBackendAdapterV2::Register(backend);
2630 } 2967 }
2631 2968
2632 2969
2633 bool IndexBackend::LookupGlobalIntegerProperty(int& target, 2970 bool IndexBackend::LookupGlobalIntegerProperty(int& target,
2634 DatabaseManager& manager, 2971 DatabaseManager& manager,
2644 target = boost::lexical_cast<int>(value); 2981 target = boost::lexical_cast<int>(value);
2645 return true; 2982 return true;
2646 } 2983 }
2647 catch (boost::bad_lexical_cast&) 2984 catch (boost::bad_lexical_cast&)
2648 { 2985 {
2649 LOG(ERROR) << "Corrupted PostgreSQL database"; 2986 LOG(ERROR) << "Corrupted database";
2650 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); 2987 throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
2651 } 2988 }
2652 } 2989 }
2653 else 2990 else
2654 { 2991 {
2667 } 3004 }
2668 3005
2669 3006
2670 void IndexBackend::Finalize() 3007 void IndexBackend::Finalize()
2671 { 3008 {
2672 OrthancDatabases::DatabaseBackendAdapterV2::Finalize(); 3009 DatabaseBackendAdapterV2::Finalize();
2673 3010
2674 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in Orthanc 1.3.1 3011 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in Orthanc 1.3.1
2675 # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 9, 2) 3012 # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 9, 2)
2676 OrthancDatabases::DatabaseBackendAdapterV3::Finalize(); 3013 DatabaseBackendAdapterV3::Finalize();
2677 # endif 3014 # endif
2678 #endif 3015 #endif
2679 } 3016
2680 3017 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in Orthanc 1.3.1
2681 3018 # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
2682 DatabaseManager* IndexBackend::CreateSingleDatabaseManager(IDatabaseBackend& backend) 3019 DatabaseBackendAdapterV4::Finalize();
3020 # endif
3021 #endif
3022 }
3023
3024
3025 uint64_t IndexBackend::MeasureLatency(DatabaseManager& manager)
3026 {
3027 // execute 11x the simplest statement and return the median value
3028 std::vector<uint64_t> measures;
3029
3030 for (int i = 0; i < 11; i++)
3031 {
3032 DatabaseManager::StandaloneStatement statement(manager, "SELECT 1");
3033
3034 Orthanc::Toolbox::ElapsedTimer timer;
3035
3036 statement.ExecuteWithoutResult();
3037
3038 measures.push_back(timer.GetElapsedMicroseconds());
3039 }
3040
3041 std::sort(measures.begin(), measures.end());
3042
3043 return measures[measures.size() / 2];
3044 }
3045
3046
3047 DatabaseManager* IndexBackend::CreateSingleDatabaseManager(IDatabaseBackend& backend,
3048 bool hasIdentifierTags,
3049 const std::list<IdentifierTag>& identifierTags)
2683 { 3050 {
2684 std::unique_ptr<DatabaseManager> manager(new DatabaseManager(backend.CreateDatabaseFactory())); 3051 std::unique_ptr<DatabaseManager> manager(new DatabaseManager(backend.CreateDatabaseFactory()));
2685 backend.ConfigureDatabase(*manager); 3052 backend.ConfigureDatabase(*manager, hasIdentifierTags, identifierTags);
2686 return manager.release(); 3053 return manager.release();
2687 } 3054 }
3055
3056 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
3057 bool IndexBackend::HasFindSupport() const
3058 {
3059 // TODO-FIND move to child plugins ?
3060 return true;
3061 }
3062 #endif
3063
3064
3065 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
3066 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* GetResourceContent(
3067 Orthanc::DatabasePluginMessages::Find_Response* response,
3068 Orthanc::DatabasePluginMessages::ResourceType level)
3069 {
3070 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = NULL; // the protobuf response will be the owner
3071
3072 switch (level)
3073 {
3074 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
3075 content = response->mutable_patient_content();
3076 break;
3077 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3078 content = response->mutable_study_content();
3079 break;
3080 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3081 content =response->mutable_series_content();
3082 break;
3083 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
3084 content = response->mutable_instance_content();
3085 break;
3086 default:
3087 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
3088 }
3089 return content;
3090 }
3091
3092 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* GetChildrenContent(
3093 Orthanc::DatabasePluginMessages::Find_Response* response,
3094 Orthanc::DatabasePluginMessages::ResourceType childrenLevel)
3095 {
3096 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = NULL; // the protobuf response will be the owner
3097
3098 switch (childrenLevel)
3099 {
3100 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3101 content = response->mutable_children_studies_content();
3102 break;
3103 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3104 content =response->mutable_children_series_content();
3105 break;
3106 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
3107 content = response->mutable_children_instances_content();
3108 break;
3109 default:
3110 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
3111 }
3112 return content;
3113 }
3114
3115 std::string JoinRequestedMetadata(const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec)
3116 {
3117 std::set<std::string> metadataTypes;
3118 for (int i = 0; i < childrenSpec->retrieve_metadata_size(); ++i)
3119 {
3120 metadataTypes.insert(boost::lexical_cast<std::string>(childrenSpec->retrieve_metadata(i)));
3121 }
3122 std::string joinedMetadataTypes;
3123 Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ", ");
3124
3125 return joinedMetadataTypes;
3126 }
3127
3128 std::string JoinRequestedTags(const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec)
3129 {
3130 std::set<std::string> tags;
3131 for (int i = 0; i < childrenSpec->retrieve_main_dicom_tags_size(); ++i)
3132 {
3133 tags.insert("(" + boost::lexical_cast<std::string>(childrenSpec->retrieve_main_dicom_tags(i).group())
3134 + ", " + boost::lexical_cast<std::string>(childrenSpec->retrieve_main_dicom_tags(i).element()) + ")");
3135 }
3136 std::string joinedTags;
3137 Orthanc::Toolbox::JoinStrings(joinedTags, tags, ", ");
3138
3139 return joinedTags;
3140 }
3141
3142
3143 #define C0_QUERY_ID 0
3144 #define C1_INTERNAL_ID 1
3145 #define C2_ROW_NUMBER 2
3146 #define C3_STRING_1 3
3147 #define C4_STRING_2 4
3148 #define C5_STRING_3 5
3149 #define C6_INT_1 6
3150 #define C7_INT_2 7
3151 #define C8_BIG_INT_1 8
3152 #define C9_BIG_INT_2 9
3153
3154 #define QUERY_LOOKUP 1
3155 #define QUERY_MAIN_DICOM_TAGS 2
3156 #define QUERY_ATTACHMENTS 3
3157 #define QUERY_METADATA 4
3158 #define QUERY_LABELS 5
3159 #define QUERY_PARENT_MAIN_DICOM_TAGS 10
3160 #define QUERY_PARENT_IDENTIFIER 11
3161 #define QUERY_PARENT_METADATA 12
3162 #define QUERY_GRAND_PARENT_MAIN_DICOM_TAGS 15
3163 #define QUERY_GRAND_PARENT_METADATA 16
3164 #define QUERY_CHILDREN_IDENTIFIERS 20
3165 #define QUERY_CHILDREN_MAIN_DICOM_TAGS 21
3166 #define QUERY_CHILDREN_METADATA 22
3167 #define QUERY_GRAND_CHILDREN_IDENTIFIERS 30
3168 #define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 31
3169 #define QUERY_GRAND_CHILDREN_METADATA 32
3170 #define QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS 40
3171 #define QUERY_ONE_INSTANCE_IDENTIFIER 50
3172 #define QUERY_ONE_INSTANCE_METADATA 51
3173 #define QUERY_ONE_INSTANCE_ATTACHMENTS 52
3174
3175 #define STRINGIFY(x) #x
3176 #define TOSTRING(x) STRINGIFY(x)
3177
3178 void IndexBackend::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
3179 DatabaseManager& manager,
3180 const Orthanc::DatabasePluginMessages::Find_Request& request)
3181 {
3182 // TODO-FIND move to child plugins ?
3183
3184
3185 // If we want the Find to use a read-only transaction, we can not create temporary tables with
3186 // the lookup results. So we must use a CTE (Common Table Expression).
3187 // However, a CTE can only be used in a single query -> we must unionize all the following
3188 // queries to retrieve values from various tables.
3189 // However, to use UNION, all tables must have the same columns (numbers and types). That's
3190 // why we have generic column names.
3191 // So, at the end we'll have only one very big query !
3192
3193 std::string sql;
3194
3195 // extract the resource id of interest by executing the lookup in a CTE
3196 LookupFormatter formatter(manager.GetDialect());
3197 std::string lookupSql;
3198 ISqlLookupFormatter::Apply(lookupSql, formatter, request);
3199
3200 // base query, retrieve the ordered internalId and publicId of the selected resources
3201 sql = "WITH Lookup AS (" + lookupSql + ") "
3202 "SELECT "
3203 " " TOSTRING(QUERY_LOOKUP) " AS c0_queryId, "
3204 " Lookup.internalId AS c1_internalId, "
3205 " Lookup.rowNumber AS c2_rowNumber, "
3206 " Lookup.publicId AS c3_string1, "
3207 " NULL::TEXT AS c4_string2, "
3208 " NULL::TEXT AS c5_string3, "
3209 " NULL::INT AS c6_int1, "
3210 " NULL::INT AS c7_int2, "
3211 " NULL::BIGINT AS c8_big_int1, "
3212 " NULL::BIGINT AS c9_big_int2 "
3213 " FROM Lookup ";
3214
3215 // need MainDicomTags from resource ?
3216 if (request.retrieve_main_dicom_tags())
3217 {
3218 sql += "UNION SELECT "
3219 " " TOSTRING(QUERY_MAIN_DICOM_TAGS) " AS c0_queryId, "
3220 " Lookup.internalId AS c1_internalId, "
3221 " NULL::BIGINT AS c2_rowNumber, "
3222 " value AS c3_string1, "
3223 " NULL::TEXT AS c4_string2, "
3224 " NULL::TEXT AS c5_string3, "
3225 " tagGroup AS c6_int1, "
3226 " tagElement AS c7_int2, "
3227 " NULL::BIGINT AS c8_big_int1, "
3228 " NULL::BIGINT AS c9_big_int2 "
3229 "FROM Lookup "
3230 "INNER JOIN MainDicomTags ON MainDicomTags.id = Lookup.internalId ";
3231 }
3232
3233 // need resource metadata ?
3234 if (request.retrieve_metadata())
3235 {
3236 sql += "UNION SELECT "
3237 " " TOSTRING(QUERY_METADATA) " AS c0_queryId, "
3238 " Lookup.internalId AS c1_internalId, "
3239 " NULL::BIGINT AS c2_rowNumber, "
3240 " value AS c3_string1, "
3241 " NULL::TEXT AS c4_string2, "
3242 " NULL::TEXT AS c5_string3, "
3243 " type AS c6_int1, "
3244 " NULL::INT AS c7_int2, "
3245 " NULL::BIGINT AS c8_big_int1, "
3246 " NULL::BIGINT AS c9_big_int2 "
3247 "FROM Lookup "
3248 "INNER JOIN Metadata ON Metadata.id = Lookup.internalId ";
3249 }
3250
3251 // need resource attachments ?
3252 if (request.retrieve_attachments())
3253 {
3254 sql += "UNION SELECT "
3255 " " TOSTRING(QUERY_ATTACHMENTS) " AS c0_queryId, "
3256 " Lookup.internalId AS c1_internalId, "
3257 " NULL::BIGINT AS c2_rowNumber, "
3258 " uuid AS c3_string1, "
3259 " uncompressedHash AS c4_string2, "
3260 " compressedHash AS c5_string3, "
3261 " fileType AS c6_int1, "
3262 " compressionType AS c7_int2, "
3263 " compressedSize AS c8_big_int1, "
3264 " uncompressedSize AS c9_big_int2 "
3265 "FROM Lookup "
3266 "INNER JOIN AttachedFiles ON AttachedFiles.id = Lookup.internalId ";
3267 }
3268
3269 // need resource labels ?
3270 if (request.retrieve_labels())
3271 {
3272 sql += "UNION SELECT "
3273 " " TOSTRING(QUERY_LABELS) " AS c0_queryId, "
3274 " Lookup.internalId AS c1_internalId, "
3275 " NULL::BIGINT AS c2_rowNumber, "
3276 " label AS c3_string1, "
3277 " NULL::TEXT AS c4_string2, "
3278 " NULL::TEXT AS c5_string3, "
3279 " NULL::INT AS c6_int1, "
3280 " NULL::INT AS c7_int2, "
3281 " NULL::BIGINT AS c8_big_int1, "
3282 " NULL::BIGINT AS c9_big_int2 "
3283 "FROM Lookup "
3284 "INNER JOIN Labels ON Labels.id = Lookup.internalId ";
3285 }
3286
3287 // need MainDicomTags from parent ?
3288 if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT)
3289 {
3290 const Orthanc::DatabasePluginMessages::Find_Request_ParentSpecification* parentSpec = NULL;
3291 switch (request.level())
3292 {
3293 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3294 parentSpec = &(request.parent_patient());
3295 break;
3296 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3297 parentSpec = &(request.parent_study());
3298 break;
3299 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
3300 parentSpec = &(request.parent_series());
3301 break;
3302
3303 default:
3304 break;
3305 }
3306
3307 if (parentSpec->retrieve_main_dicom_tags())
3308 {
3309 sql += "UNION SELECT "
3310 " " TOSTRING(QUERY_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
3311 " Lookup.internalId AS c1_internalId, "
3312 " NULL::BIGINT AS c2_rowNumber, "
3313 " value AS c3_string1, "
3314 " NULL::TEXT AS c4_string2, "
3315 " NULL::TEXT AS c5_string3, "
3316 " tagGroup AS c6_int1, "
3317 " tagElement AS c7_int2, "
3318 " NULL::BIGINT AS c8_big_int1, "
3319 " NULL::BIGINT AS c9_big_int2 "
3320 "FROM Lookup "
3321 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
3322 "INNER JOIN MainDicomTags ON MainDicomTags.id = currentLevel.parentId ";
3323 }
3324
3325 if (parentSpec->retrieve_metadata())
3326 {
3327 sql += "UNION SELECT "
3328 " " TOSTRING(QUERY_PARENT_METADATA) " AS c0_queryId, "
3329 " Lookup.internalId AS c1_internalId, "
3330 " NULL::BIGINT AS c2_rowNumber, "
3331 " value AS c3_string1, "
3332 " NULL::TEXT AS c4_string2, "
3333 " NULL::TEXT AS c5_string3, "
3334 " type AS c6_int1, "
3335 " NULL::INT AS c7_int2, "
3336 " NULL::BIGINT AS c8_big_int1, "
3337 " NULL::BIGINT AS c9_big_int2 "
3338 "FROM Lookup "
3339 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
3340 "INNER JOIN Metadata ON Metadata.id = currentLevel.parentId ";
3341 }
3342
3343 // need MainDicomTags from grandparent ?
3344 if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY)
3345 {
3346 const Orthanc::DatabasePluginMessages::Find_Request_ParentSpecification* grandparentSpec = NULL;
3347 switch (request.level())
3348 {
3349 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3350 grandparentSpec = &(request.parent_patient());
3351 break;
3352 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
3353 grandparentSpec = &(request.parent_study());
3354 break;
3355
3356 default:
3357 break;
3358 }
3359
3360 if (grandparentSpec->retrieve_main_dicom_tags())
3361 {
3362 sql += "UNION SELECT "
3363 " " TOSTRING(QUERY_GRAND_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
3364 " Lookup.internalId AS c1_internalId, "
3365 " NULL::BIGINT AS c2_rowNumber, "
3366 " value AS c3_string1, "
3367 " NULL::TEXT AS c4_string2, "
3368 " NULL::TEXT AS c5_string3, "
3369 " tagGroup AS c6_int1, "
3370 " tagElement AS c7_int2, "
3371 " NULL::BIGINT AS c8_big_int1, "
3372 " NULL::BIGINT AS c9_big_int2 "
3373 "FROM Lookup "
3374 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
3375 "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
3376 "INNER JOIN MainDicomTags ON MainDicomTags.id = parentLevel.parentId ";
3377 }
3378
3379 if (grandparentSpec->retrieve_metadata())
3380 {
3381 sql += "UNION SELECT "
3382 " " TOSTRING(QUERY_GRAND_PARENT_METADATA) " AS c0_queryId, "
3383 " Lookup.internalId AS c1_internalId, "
3384 " NULL::BIGINT AS c2_rowNumber, "
3385 " value AS c3_string1, "
3386 " NULL::TEXT AS c4_string2, "
3387 " NULL::TEXT AS c5_string3, "
3388 " type AS c6_int1, "
3389 " NULL::INT AS c7_int2, "
3390 " NULL::BIGINT AS c8_big_int1, "
3391 " NULL::BIGINT AS c9_big_int2 "
3392 "FROM Lookup "
3393 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
3394 "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
3395 "INNER JOIN Metadata ON Metadata.id = parentLevel.parentId ";
3396 }
3397 }
3398 }
3399
3400 // need MainDicomTags from children ?
3401 if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES)
3402 {
3403 const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec = NULL;
3404 switch (request.level())
3405 {
3406 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
3407 childrenSpec = &(request.children_studies());
3408 break;
3409 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3410 childrenSpec = &(request.children_series());
3411 break;
3412 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3413 childrenSpec = &(request.children_instances());
3414 break;
3415
3416 default:
3417 break;
3418 }
3419
3420 if (childrenSpec->retrieve_main_dicom_tags_size() > 0)
3421 {
3422 sql += "UNION SELECT "
3423 " " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
3424 " Lookup.internalId AS c1_internalId, "
3425 " NULL::BIGINT AS c2_rowNumber, "
3426 " value AS c3_string1, "
3427 " NULL::TEXT AS c4_string2, "
3428 " NULL::TEXT AS c5_string3, "
3429 " tagGroup AS c6_int1, "
3430 " tagElement AS c7_int2, "
3431 " NULL::BIGINT AS c8_big_int1, "
3432 " NULL::BIGINT AS c9_big_int2 "
3433 "FROM Lookup "
3434 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3435 " INNER JOIN MainDicomTags ON MainDicomTags.id = childLevel.internalId AND (tagGroup, tagElement) IN (" + JoinRequestedTags(childrenSpec) + ")";
3436 }
3437
3438 // need children identifiers ?
3439 if (childrenSpec->retrieve_identifiers())
3440 {
3441 sql += "UNION SELECT "
3442 " " TOSTRING(QUERY_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
3443 " Lookup.internalId AS c1_internalId, "
3444 " NULL::BIGINT AS c2_rowNumber, "
3445 " childLevel.publicId AS c3_string1, "
3446 " NULL::TEXT AS c4_string2, "
3447 " NULL::TEXT AS c5_string3, "
3448 " NULL::INT AS c6_int1, "
3449 " NULL::INT AS c7_int2, "
3450 " NULL::BIGINT AS c8_big_int1, "
3451 " NULL::BIGINT AS c9_big_int2 "
3452 "FROM Lookup "
3453 " INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId ";
3454 }
3455
3456 if (childrenSpec->retrieve_metadata_size() > 0)
3457 {
3458 sql += "UNION SELECT "
3459 " " TOSTRING(QUERY_CHILDREN_METADATA) " AS c0_queryId, "
3460 " Lookup.internalId AS c1_internalId, "
3461 " NULL::BIGINT AS c2_rowNumber, "
3462 " value AS c3_string1, "
3463 " NULL::TEXT AS c4_string2, "
3464 " NULL::TEXT AS c5_string3, "
3465 " type AS c6_int1, "
3466 " NULL::INT AS c7_int2, "
3467 " NULL::BIGINT AS c8_big_int1, "
3468 " NULL::BIGINT AS c9_big_int2 "
3469 "FROM Lookup "
3470 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3471 " INNER JOIN Metadata ON Metadata.id = childLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(childrenSpec) + ") ";
3472 }
3473
3474 if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY)
3475 {
3476 const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandchildrenSpec = NULL;
3477 switch (request.level())
3478 {
3479 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
3480 grandchildrenSpec = &(request.children_series());
3481 break;
3482 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3483 grandchildrenSpec = &(request.children_instances());
3484 break;
3485
3486 default:
3487 break;
3488 }
3489
3490 // need grand children identifiers ?
3491 if (grandchildrenSpec->retrieve_identifiers())
3492 {
3493 sql += "UNION SELECT "
3494 " " TOSTRING(QUERY_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
3495 " Lookup.internalId AS c1_internalId, "
3496 " NULL::BIGINT AS c2_rowNumber, "
3497 " grandChildLevel.publicId AS c3_string1, "
3498 " NULL::TEXT AS c4_string2, "
3499 " NULL::TEXT AS c5_string3, "
3500 " NULL::INT AS c6_int1, "
3501 " NULL::INT AS c7_int2, "
3502 " NULL::BIGINT AS c8_big_int1, "
3503 " NULL::BIGINT AS c9_big_int2 "
3504 "FROM Lookup "
3505 "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
3506 "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId ";
3507 }
3508
3509 if (grandchildrenSpec->retrieve_main_dicom_tags_size() > 0)
3510 {
3511 sql += "UNION SELECT "
3512 " " TOSTRING(QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
3513 " Lookup.internalId AS c1_internalId, "
3514 " NULL::BIGINT AS c2_rowNumber, "
3515 " value AS c3_string1, "
3516 " NULL::TEXT AS c4_string2, "
3517 " NULL::TEXT AS c5_string3, "
3518 " tagGroup AS c6_int1, "
3519 " tagElement AS c7_int2, "
3520 " NULL::BIGINT AS c8_big_int1, "
3521 " NULL::BIGINT AS c9_big_int2 "
3522 "FROM Lookup "
3523 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3524 " INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId "
3525 " INNER JOIN MainDicomTags ON MainDicomTags.id = grandChildLevel.internalId AND (tagGroup, tagElement) IN (" + JoinRequestedTags(grandchildrenSpec) + ")";
3526 }
3527
3528 if (grandchildrenSpec->retrieve_metadata_size() > 0)
3529 {
3530 sql += "UNION SELECT "
3531 " " TOSTRING(QUERY_GRAND_CHILDREN_METADATA) " AS c0_queryId, "
3532 " Lookup.internalId AS c1_internalId, "
3533 " NULL::BIGINT AS c2_rowNumber, "
3534 " value AS c3_string1, "
3535 " NULL::TEXT AS c4_string2, "
3536 " NULL::TEXT AS c5_string3, "
3537 " type AS c6_int1, "
3538 " NULL::INT AS c7_int2, "
3539 " NULL::BIGINT AS c8_big_int1, "
3540 " NULL::BIGINT AS c9_big_int2 "
3541 "FROM Lookup "
3542 " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
3543 " INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId "
3544 " INNER JOIN Metadata ON Metadata.id = grandChildLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(grandchildrenSpec) + ") ";
3545 }
3546
3547 if (request.level() == Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT)
3548 {
3549 const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandgrandchildrenSpec = &(request.children_instances());
3550
3551 // need grand children identifiers ?
3552 if (grandgrandchildrenSpec->retrieve_identifiers())
3553 {
3554 sql += "UNION SELECT "
3555 " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
3556 " Lookup.internalId AS c1_internalId, "
3557 " NULL::BIGINT AS c2_rowNumber, "
3558 " grandGrandChildLevel.publicId AS c3_string1, "
3559 " NULL::TEXT AS c4_string2, "
3560 " NULL::TEXT AS c5_string3, "
3561 " NULL::INT AS c6_int1, "
3562 " NULL::INT AS c7_int2, "
3563 " NULL::BIGINT AS c8_big_int1, "
3564 " NULL::BIGINT AS c9_big_int2 "
3565 "FROM Lookup "
3566 "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
3567 "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "
3568 "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId ";
3569 }
3570 }
3571 }
3572 }
3573
3574 // need parent identifier ?
3575 if (request.retrieve_parent_identifier())
3576 {
3577 sql += "UNION SELECT "
3578 " " TOSTRING(QUERY_PARENT_IDENTIFIER) " AS c0_queryId, "
3579 " Lookup.internalId AS c1_internalId, "
3580 " NULL::BIGINT AS c2_rowNumber, "
3581 " parentLevel.publicId AS c3_string1, "
3582 " NULL::TEXT AS c4_string2, "
3583 " NULL::TEXT AS c5_string3, "
3584 " NULL::INT AS c6_int1, "
3585 " NULL::INT AS c7_int2, "
3586 " NULL::BIGINT AS c8_big_int1, "
3587 " NULL::BIGINT AS c9_big_int2 "
3588 "FROM Lookup "
3589 " INNER JOIN Resources currentLevel ON currentLevel.internalId = Lookup.internalId "
3590 " INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId ";
3591 }
3592
3593 // need one instance info ?
3594 if (request.level() != Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE &&
3595 request.retrieve_one_instance_metadata_and_attachments())
3596 {
3597 // Here, we create a nested CTE 'OneInstance' with one instance ID to join with metadata and main
3598 sql += "UNION"
3599 " (WITH OneInstance AS";
3600
3601 switch (request.level())
3602 {
3603 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
3604 {
3605 sql+= " (SELECT DISTINCT ON (Lookup.internalId) Lookup.internalId AS parentInternalId, childLevel.publicId AS instancePublicId, childLevel.internalId AS instanceInternalId"
3606 " FROM Resources AS childLevel "
3607 " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId) ";
3608 }; break;
3609 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
3610 {
3611 sql+= " (SELECT DISTINCT ON (Lookup.internalId) Lookup.internalId AS parentInternalId, grandChildLevel.publicId AS instancePublicId, grandChildLevel.internalId AS instanceInternalId"
3612 " FROM Resources AS grandChildLevel "
3613 " INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
3614 " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId) ";
3615 }; break;
3616 case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
3617 {
3618 sql+= " (SELECT DISTINCT ON (Lookup.internalId) Lookup.internalId AS parentInternalId, grandGrandChildLevel.publicId AS instancePublicId, grandGrandChildLevel.internalId AS instanceInternalId"
3619 " FROM Resources AS grandGrandChildLevel "
3620 " INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId "
3621 " INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
3622 " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId) ";
3623 }; break;
3624 default:
3625 throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
3626 }
3627
3628 sql += " SELECT"
3629 " " TOSTRING(QUERY_ONE_INSTANCE_IDENTIFIER) " AS c0_queryId, "
3630 " parentInternalId AS c1_internalId, "
3631 " NULL::BIGINT AS c2_rowNumber, "
3632 " instancePublicId AS c3_string1, "
3633 " NULL::TEXT AS c4_string2, "
3634 " NULL::TEXT AS c5_string3, "
3635 " NULL::INT AS c6_int1, "
3636 " NULL::INT AS c7_int2, "
3637 " instanceInternalId AS c8_big_int1, "
3638 " NULL::BIGINT AS c9_big_int2 "
3639 " FROM OneInstance ";
3640
3641 sql += " UNION SELECT"
3642 " " TOSTRING(QUERY_ONE_INSTANCE_METADATA) " AS c0_queryId, "
3643 " parentInternalId AS c1_internalId, "
3644 " NULL::BIGINT AS c2_rowNumber, "
3645 " Metadata.value AS c3_string1, "
3646 " NULL::TEXT AS c4_string2, "
3647 " NULL::TEXT AS c5_string3, "
3648 " Metadata.type AS c6_int1, "
3649 " NULL::INT AS c7_int2, "
3650 " NULL::BIGINT AS c8_big_int1, "
3651 " NULL::BIGINT AS c9_big_int2 "
3652 " FROM Metadata "
3653 " INNER JOIN OneInstance ON Metadata.id = OneInstance.instanceInternalId";
3654
3655 sql += " UNION SELECT"
3656 " " TOSTRING(QUERY_ONE_INSTANCE_ATTACHMENTS) " AS c0_queryId, "
3657 " parentInternalId AS c1_internalId, "
3658 " NULL::BIGINT AS c2_rowNumber, "
3659 " uuid AS c3_string1, "
3660 " uncompressedHash AS c4_string2, "
3661 " compressedHash AS c5_string3, "
3662 " fileType AS c6_int1, "
3663 " compressionType AS c7_int2, "
3664 " compressedSize AS c8_big_int1, "
3665 " uncompressedSize AS c9_big_int2 "
3666 " FROM AttachedFiles "
3667 " INNER JOIN OneInstance ON AttachedFiles.id = OneInstance.instanceInternalId";
3668
3669 sql += " ) ";
3670
3671 }
3672
3673 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 !
3674
3675 DatabaseManager::StandaloneStatement statement(manager, sql); // TODO-FIND: cache dynamic statement ? Probably worth it since it can be very complex queries !
3676 formatter.PrepareStatement(statement);
3677 statement.Execute(formatter.GetDictionary());
3678
3679
3680 std::map<int64_t, Orthanc::DatabasePluginMessages::Find_Response*> responses;
3681
3682 while (!statement.IsDone())
3683 {
3684 int32_t queryId = statement.ReadInteger32(C0_QUERY_ID);
3685 int64_t internalId = statement.ReadInteger64(C1_INTERNAL_ID);
3686
3687 assert(queryId == QUERY_LOOKUP || responses.find(internalId) != responses.end()); // the QUERY_LOOKUP must be read first and must create the response before any other query tries to populate the fields
3688
3689 switch (queryId)
3690 {
3691 case QUERY_LOOKUP:
3692 responses[internalId] = response.add_find();
3693 responses[internalId]->set_public_id(statement.ReadString(C3_STRING_1));
3694 responses[internalId]->set_internal_id(internalId);
3695 break;
3696
3697 case QUERY_LABELS:
3698 responses[internalId]->add_labels(statement.ReadString(C3_STRING_1));
3699 break;
3700
3701 case QUERY_MAIN_DICOM_TAGS:
3702 {
3703 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level());
3704 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
3705
3706 tag->set_value(statement.ReadString(C3_STRING_1));
3707 tag->set_group(statement.ReadInteger32(C6_INT_1));
3708 tag->set_element(statement.ReadInteger32(C7_INT_2));
3709 }; break;
3710
3711 case QUERY_PARENT_MAIN_DICOM_TAGS:
3712 {
3713 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 1));
3714 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
3715
3716 tag->set_value(statement.ReadString(C3_STRING_1));
3717 tag->set_group(statement.ReadInteger32(C6_INT_1));
3718 tag->set_element(statement.ReadInteger32(C7_INT_2));
3719 }; break;
3720
3721 case QUERY_GRAND_PARENT_MAIN_DICOM_TAGS:
3722 {
3723 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 2));
3724 Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
3725
3726 tag->set_value(statement.ReadString(C3_STRING_1));
3727 tag->set_group(statement.ReadInteger32(C6_INT_1));
3728 tag->set_element(statement.ReadInteger32(C7_INT_2));
3729 }; break;
3730
3731 case QUERY_CHILDREN_IDENTIFIERS:
3732 {
3733 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
3734 content->add_identifiers(statement.ReadString(C3_STRING_1));
3735 }; break;
3736
3737 case QUERY_CHILDREN_MAIN_DICOM_TAGS:
3738 {
3739 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
3740 Orthanc::DatabasePluginMessages::Find_Response_MultipleTags* tag = content->add_main_dicom_tags();
3741 tag->add_values(statement.ReadString(C3_STRING_1)); // TODO: handle sequences ??
3742 tag->set_group(statement.ReadInteger32(C6_INT_1));
3743 tag->set_element(statement.ReadInteger32(C7_INT_2));
3744 }; break;
3745
3746 case QUERY_CHILDREN_METADATA:
3747 {
3748 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
3749 Orthanc::DatabasePluginMessages::Find_Response_MultipleMetadata* metadata = content->add_metadata();
3750
3751 metadata->add_values(statement.ReadString(C3_STRING_1));
3752 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3753 }; break;
3754
3755 case QUERY_GRAND_CHILDREN_IDENTIFIERS:
3756 {
3757 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
3758 content->add_identifiers(statement.ReadString(C3_STRING_1));
3759 }; break;
3760
3761 case QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS:
3762 {
3763 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
3764 Orthanc::DatabasePluginMessages::Find_Response_MultipleTags* tag = content->add_main_dicom_tags();
3765
3766 tag->add_values(statement.ReadString(C3_STRING_1)); // TODO: handle sequences ??
3767 tag->set_group(statement.ReadInteger32(C6_INT_1));
3768 tag->set_element(statement.ReadInteger32(C7_INT_2));
3769 }; break;
3770
3771 case QUERY_GRAND_CHILDREN_METADATA:
3772 {
3773 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
3774 Orthanc::DatabasePluginMessages::Find_Response_MultipleMetadata* metadata = content->add_metadata();
3775
3776 metadata->add_values(statement.ReadString(C3_STRING_1));
3777 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3778 }; break;
3779
3780 case QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS:
3781 {
3782 Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 3));
3783 content->add_identifiers(statement.ReadString(C3_STRING_1));
3784 }; break;
3785
3786 case QUERY_ATTACHMENTS:
3787 {
3788 Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_attachments();
3789
3790 attachment->set_uuid(statement.ReadString(C3_STRING_1));
3791 attachment->set_uncompressed_hash(statement.ReadString(C4_STRING_2));
3792 attachment->set_compressed_hash(statement.ReadString(C5_STRING_3));
3793 attachment->set_content_type(statement.ReadInteger32(C6_INT_1));
3794 attachment->set_compression_type(statement.ReadInteger32(C7_INT_2));
3795 attachment->set_compressed_size(statement.ReadInteger64(C8_BIG_INT_1));
3796 attachment->set_uncompressed_size(statement.ReadInteger64(C9_BIG_INT_2));
3797 }; break;
3798
3799 case QUERY_METADATA:
3800 {
3801 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level());
3802 Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
3803
3804 metadata->set_value(statement.ReadString(C3_STRING_1));
3805 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3806 }; break;
3807
3808 case QUERY_PARENT_METADATA:
3809 {
3810 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 1));
3811 Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
3812
3813 metadata->set_value(statement.ReadString(C3_STRING_1));
3814 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3815 }; break;
3816
3817 case QUERY_GRAND_PARENT_METADATA:
3818 {
3819 Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 2));
3820 Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
3821
3822 metadata->set_value(statement.ReadString(C3_STRING_1));
3823 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3824 }; break;
3825
3826 case QUERY_PARENT_IDENTIFIER:
3827 {
3828 responses[internalId]->set_parent_public_id(statement.ReadString(C3_STRING_1));
3829 }; break;
3830
3831 case QUERY_ONE_INSTANCE_IDENTIFIER:
3832 {
3833 responses[internalId]->set_one_instance_public_id(statement.ReadString(C3_STRING_1));
3834 }; break;
3835 case QUERY_ONE_INSTANCE_METADATA:
3836 {
3837 Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = responses[internalId]->add_one_instance_metadata();
3838
3839 metadata->set_value(statement.ReadString(C3_STRING_1));
3840 metadata->set_key(statement.ReadInteger32(C6_INT_1));
3841 }; break;
3842 case QUERY_ONE_INSTANCE_ATTACHMENTS:
3843 {
3844 Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_one_instance_attachments();
3845
3846 attachment->set_uuid(statement.ReadString(C3_STRING_1));
3847 attachment->set_uncompressed_hash(statement.ReadString(C4_STRING_2));
3848 attachment->set_compressed_hash(statement.ReadString(C5_STRING_3));
3849 attachment->set_content_type(statement.ReadInteger32(C6_INT_1));
3850 attachment->set_compression_type(statement.ReadInteger32(C7_INT_2));
3851 attachment->set_compressed_size(statement.ReadInteger64(C8_BIG_INT_1));
3852 attachment->set_uncompressed_size(statement.ReadInteger64(C9_BIG_INT_2));
3853 }; break;
3854
3855 default:
3856 throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
3857 }
3858 statement.Next();
3859 }
3860 }
3861 #endif
3862
2688 } 3863 }