Mercurial > hg > orthanc

comparison OrthancServer/Sources/Search/ISqlLookupFormatter.cpp @ 5825:881cd0965146 find-refactoring

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
added OrderBy support in SQLite
author Alain Mazy <am@orthanc.team>
date Fri, 04 Oct 2024 19:03:14 +0200
parents 16ce3c920f71
children d73dfb4548c6
comparison
equal deleted inserted replaced
5819:7c2b4fa94633 5825:881cd0965146
26 26
27 #include "../../../OrthancFramework/Sources/OrthancException.h" 27 #include "../../../OrthancFramework/Sources/OrthancException.h"
28 #include "../../../OrthancFramework/Sources/Toolbox.h" 28 #include "../../../OrthancFramework/Sources/Toolbox.h"
29 #include "../Database/FindRequest.h" 29 #include "../Database/FindRequest.h"
30 #include "DatabaseConstraint.h" 30 #include "DatabaseConstraint.h"
31 #include "../Database/MainDicomTagsRegistry.h"
31 32
32 #include <cassert> 33 #include <cassert>
33 #include <boost/lexical_cast.hpp> 34 #include <boost/lexical_cast.hpp>
34 #include <list> 35 #include <list>
35 36
261 " AND " + tag + ".tagElement = " + 262 " AND " + tag + ".tagElement = " +
262 boost::lexical_cast<std::string>(constraint.GetTag().GetElement())); 263 boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
263 } 264 }
264 265
265 266
267 static void FormatJoinForOrdering(std::string& target,
268 const DicomTag& tag,
269 size_t index,
270 ResourceType requestLevel)
271 {
272 std::string orderArg = "order" + boost::lexical_cast<std::string>(index);
273
274 target.clear();
275
276 ResourceType tagLevel;
277 DicomTagType tagType;
278 MainDicomTagsRegistry registry;
279
280 registry.LookupTag(tagLevel, tagType, tag);
281
282 if (tagLevel == ResourceType_Patient && requestLevel == ResourceType_Study)
283 { // Patient tags are copied at study level
284 tagLevel = ResourceType_Study;
285 }
286
287 std::string tagTable;
288 if (tagType == DicomTagType_Identifier)
289 {
290 tagTable = "DicomIdentifiers ";
291 }
292 else
293 {
294 tagTable = "MainDicomTags ";
295 }
296
297 std::string tagFilter = orderArg + ".tagGroup = " + boost::lexical_cast<std::string>(tag.GetGroup()) + " AND " + orderArg + ".tagElement = " + boost::lexical_cast<std::string>(tag.GetElement());
298
299 if (tagLevel == requestLevel)
300 {
301 target = " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + FormatLevel(requestLevel) +
302 ".internalId AND " + tagFilter;
303 }
304 else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 1)
305 {
306 target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId "
307 " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "parent.internalId AND " + tagFilter;
308 }
309 else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 2)
310 {
311 target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId "
312 " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId "
313 " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandparent.internalId AND " + tagFilter;
314 }
315 else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 3)
316 {
317 target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId "
318 " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId "
319 " INNER JOIN Resources " + orderArg + "grandgrandparent ON " + orderArg + "grandgrandparent.internalId = " + orderArg + "grandparent.parentId "
320 " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandgrandparent.internalId AND " + tagFilter;
321 }
322 }
323
324 static void FormatJoinForOrdering(std::string& target,
325 const MetadataType& metadata,
326 size_t index,
327 ResourceType requestLevel)
328 {
329 std::string arg = "order" + boost::lexical_cast<std::string>(index);
330
331
332 target = " INNER JOIN Metadata " + arg + " ON " + arg + ".id = " + FormatLevel(requestLevel) +
333 ".internalId AND " + arg + ".type = " +
334 boost::lexical_cast<std::string>(metadata);
335 }
336
266 static std::string Join(const std::list<std::string>& values, 337 static std::string Join(const std::list<std::string>& values,
267 const std::string& prefix, 338 const std::string& prefix,
268 const std::string& separator) 339 const std::string& separator)
269 { 340 {
270 if (values.empty()) 341 if (values.empty())
617 GetLookupLevels(lowerLevel, upperLevel, queryLevel, request.GetDicomTagConstraints()); 688 GetLookupLevels(lowerLevel, upperLevel, queryLevel, request.GetDicomTagConstraints());
618 689
619 assert(upperLevel <= queryLevel && 690 assert(upperLevel <= queryLevel &&
620 queryLevel <= lowerLevel); 691 queryLevel <= lowerLevel);
621 692
622 std::string ordering = "row_number() over (order by " + strQueryLevel + ".publicId) as rowNumber"; // we need a default ordering in order to make default queries repeatable when using since&limit 693 std::string ordering;
623 694 std::string orderingJoins;
695
696 if (request.GetOrdering().size() > 0)
697 {
698 int counter = 0;
699 std::vector<std::string> orderByFields;
700 for (std::deque<FindRequest::Ordering*>::const_iterator it = request.GetOrdering().begin(); it != request.GetOrdering().end(); ++it)
701 {
702 std::string orderingJoin;
703
704 switch ((*it)->GetKeyType())
705 {
706 case FindRequest::KeyType_DicomTag:
707 FormatJoinForOrdering(orderingJoin, (*it)->GetDicomTag(), counter, request.GetLevel());
708 break;
709 case FindRequest::KeyType_Metadata:
710 FormatJoinForOrdering(orderingJoin, (*it)->GetMetadataType(), counter, request.GetLevel());
711 break;
712 default:
713 throw OrthancException(ErrorCode_InternalError);
714 }
715 orderingJoins += orderingJoin;
716
717 std::string orderByField = "order" + boost::lexical_cast<std::string>(counter) + ".value";
718 if ((*it)->GetDirection() == FindRequest::OrderingDirection_Ascending)
719 {
720 orderByField += " ASC";
721 }
722 else
723 {
724 orderByField += " DESC";
725 }
726 orderByFields.push_back(orderByField);
727 ++counter;
728 }
729
730 std::string orderByFieldsString;
731 Toolbox::JoinStrings(orderByFieldsString, orderByFields, ", ");
732 ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + ") AS rowNumber";
733 }
734 else
735 {
736 ordering = "ROW_NUMBER() OVER (ORDER BY " + strQueryLevel + ".publicId) AS rowNumber"; // we need a default ordering in order to make default queries repeatable when using since&limit
737 }
624 738
625 sql = ("SELECT " + 739 sql = ("SELECT " +
626 strQueryLevel + ".publicId, " + 740 strQueryLevel + ".publicId, " +
627 strQueryLevel + ".internalId, " + 741 strQueryLevel + ".internalId, " +
628 ordering + 742 ordering +
736 850
737 where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel + 851 where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel +
738 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); 852 ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
739 } 853 }
740 854
741 sql += joins + Join(where, " WHERE ", " AND "); 855 sql += joins + orderingJoins + Join(where, " WHERE ", " AND ");
742 856
743 if (request.HasLimits()) 857 if (request.HasLimits())
744 { 858 {
745 sql += formatter.FormatLimits(request.GetLimitsSince(), request.GetLimitsCount()); 859 sql += formatter.FormatLimits(request.GetLimitsSince(), request.GetLimitsCount());
746 } 860 }