Mercurial > hg > orthanc
changeset 5825:881cd0965146 find-refactoring tip
added OrderBy support in SQLite
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Fri, 04 Oct 2024 19:03:14 +0200 |
| parents | 7c2b4fa94633 |
| children | |
| files | NEWS OrthancServer/Sources/OrthancRestApi/OrthancRestResources.cpp OrthancServer/Sources/ResourceFinder.h OrthancServer/Sources/Search/ISqlLookupFormatter.cpp |
| diffstat | 4 files changed, 209 insertions(+), 3 deletions(-) [+] |
line wrap: on
line diff
--- a/NEWS Fri Sep 27 14:38:20 2024 +0200 +++ b/NEWS Fri Oct 04 19:03:14 2024 +0200 @@ -28,12 +28,15 @@ * in /system, added a new field "Capabilities" with new values: - "HasExtendedChanges" for DB backend that provides this feature (the default SQLite DB or PostgreSQL vX.X, MySQL vX.X, ODBC vX.X). - - "HasExendedFind" for DB backend that provides this feature (the default SQLite DB + - "HasExtendedFind" for DB backend that provides this feature (the default SQLite DB or PostgreSQL vX.X, MySQL vX.X, ODBC vX.X). * With DB backend with "HasExtendedChanges" support, /changes now supports 2 more options: - 'type' to filter the changes returned by the query - 'to' to potentially cycle through changes in reverse order. example: /changes?type=StableStudy&to=7584&limit=100 +* With DB backend with "HasExtendedFind" support, /tools/find now supports new options: + - 'OrderBy' to order by DICOM Tag or metadata value + Maintenance -----------
--- a/OrthancServer/Sources/OrthancRestApi/OrthancRestResources.cpp Fri Sep 27 14:38:20 2024 +0200 +++ b/OrthancServer/Sources/OrthancRestApi/OrthancRestResources.cpp Fri Oct 04 19:03:14 2024 +0200 @@ -3248,6 +3248,11 @@ static const char* const KEY_SINCE = "Since"; static const char* const KEY_LABELS = "Labels"; // New in Orthanc 1.12.0 static const char* const KEY_LABELS_CONSTRAINT = "LabelsConstraint"; // New in Orthanc 1.12.0 + static const char* const KEY_ORDER_BY = "OrderBy"; // New in Orthanc 1.12.5 + static const char* const KEY_ORDER_BY_KEY = "Key"; // New in Orthanc 1.12.5 + static const char* const KEY_ORDER_BY_TYPE = "Type"; // New in Orthanc 1.12.5 + static const char* const KEY_ORDER_BY_DIRECTION = "Direction"; // New in Orthanc 1.12.5 + if (call.IsDocumentation()) { @@ -3281,6 +3286,8 @@ "List of strings specifying which labels to look for in the resources (new in Orthanc 1.12.0)", true) .SetRequestField(KEY_LABELS_CONSTRAINT, RestApiCallDocumentation::Type_String, "Constraint on the labels, can be `All`, `Any`, or `None` (defaults to `All`, new in Orthanc 1.12.0)", true) + .SetRequestField(KEY_ORDER_BY, RestApiCallDocumentation::Type_JsonListOfObjects, + "Array of associative arrays containing the requested ordering", true) .AddAnswerType(MimeType_Json, "JSON array containing either the Orthanc identifiers, or detailed information " "about the reported resources (if `Expand` argument is `true`)"); return; @@ -3343,6 +3350,12 @@ throw OrthancException(ErrorCode_BadRequest, "Field \"" + std::string(KEY_LABELS_CONSTRAINT) + "\" must be an array of strings"); } + else if (request.isMember(KEY_ORDER_BY) && + request[KEY_ORDER_BY].type() != Json::arrayValue) + { + throw OrthancException(ErrorCode_BadRequest, + "Field \"" + std::string(KEY_ORDER_BY) + "\" must be an array"); + } else if (true) { /** @@ -3468,6 +3481,69 @@ } } + if (request.isMember(KEY_ORDER_BY)) // New in Orthanc 1.12.5 + { + for (Json::Value::ArrayIndex i = 0; i < request[KEY_ORDER_BY].size(); i++) + { + if (request[KEY_ORDER_BY][i].type() != Json::objectValue) + { + throw OrthancException(ErrorCode_BadRequest, "Field \"" + std::string(KEY_ORDER_BY) + "\" must contain objects"); + } + else + { + const Json::Value& order = request[KEY_ORDER_BY][i]; + FindRequest::OrderingDirection direction; + std::string directionString; + std::string typeString; + + if (!order.isMember(KEY_ORDER_BY_KEY) || order[KEY_ORDER_BY_KEY].type() != Json::stringValue) + { + throw OrthancException(ErrorCode_BadRequest, "Field \"" + std::string(KEY_ORDER_BY_KEY) + "\" must be a string"); + } + + if (!order.isMember(KEY_ORDER_BY_DIRECTION) || order[KEY_ORDER_BY_DIRECTION].type() != Json::stringValue) + { + throw OrthancException(ErrorCode_BadRequest, "Field \"" + std::string(KEY_ORDER_BY_DIRECTION) + "\" must be \"ASC\" or \"DESC\""); + } + + Toolbox::ToLowerCase(directionString, order[KEY_ORDER_BY_DIRECTION].asString()); + if (directionString == "asc") + { + direction = FindRequest::OrderingDirection_Ascending; + } + else if (directionString == "desc") + { + direction = FindRequest::OrderingDirection_Descending; + } + else + { + throw OrthancException(ErrorCode_BadRequest, "Field \"" + std::string(KEY_ORDER_BY_DIRECTION) + "\" must be \"ASC\" or \"DESC\""); + } + + if (!order.isMember(KEY_ORDER_BY_TYPE) || order[KEY_ORDER_BY_TYPE].type() != Json::stringValue) + { + throw OrthancException(ErrorCode_BadRequest, "Field \"" + std::string(KEY_ORDER_BY_TYPE) + "\" must be \"DicomTag\" or \"Metadata\""); + } + + Toolbox::ToLowerCase(typeString, order[KEY_ORDER_BY_TYPE].asString()); + if (typeString == "dicomtag") + { + DicomTag tag = FromDcmtkBridge::ParseTag(order[KEY_ORDER_BY_KEY].asString()); + finder.AddOrdering(tag, direction); + } + else if (typeString == "metadata") + { + MetadataType metadata = StringToMetadata(order[KEY_ORDER_BY_KEY].asString()); + finder.AddOrdering(metadata, direction); + } + else + { + throw OrthancException(ErrorCode_BadRequest, "Field \"" + std::string(KEY_ORDER_BY_TYPE) + "\" must be \"DicomTag\" or \"Metadata\""); + } + } + } + } + Json::Value answer; finder.Execute(answer, context, format, false /* no "Metadata" field */); call.GetOutput().AnswerJson(answer);
--- a/OrthancServer/Sources/ResourceFinder.h Fri Sep 27 14:38:20 2024 +0200 +++ b/OrthancServer/Sources/ResourceFinder.h Fri Oct 04 19:03:14 2024 +0200 @@ -133,6 +133,19 @@ void AddRequestedTags(const std::set<DicomTag>& tags); + void AddOrdering(const DicomTag& tag, + FindRequest::OrderingDirection direction) + { + request_.AddOrdering(tag, direction); + } + + void AddOrdering(MetadataType metadataType, + FindRequest::OrderingDirection direction) + { + request_.AddOrdering(metadataType, direction); + } + + void SetLabels(const std::set<std::string>& labels) { request_.SetLabels(labels);
--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Fri Sep 27 14:38:20 2024 +0200 +++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp Fri Oct 04 19:03:14 2024 +0200 @@ -28,6 +28,7 @@ #include "../../../OrthancFramework/Sources/Toolbox.h" #include "../Database/FindRequest.h" #include "DatabaseConstraint.h" +#include "../Database/MainDicomTagsRegistry.h" #include <cassert> #include <boost/lexical_cast.hpp> @@ -263,6 +264,76 @@ } + static void FormatJoinForOrdering(std::string& target, + const DicomTag& tag, + size_t index, + ResourceType requestLevel) + { + std::string orderArg = "order" + boost::lexical_cast<std::string>(index); + + target.clear(); + + ResourceType tagLevel; + DicomTagType tagType; + MainDicomTagsRegistry registry; + + registry.LookupTag(tagLevel, tagType, tag); + + if (tagLevel == ResourceType_Patient && requestLevel == ResourceType_Study) + { // Patient tags are copied at study level + tagLevel = ResourceType_Study; + } + + std::string tagTable; + if (tagType == DicomTagType_Identifier) + { + tagTable = "DicomIdentifiers "; + } + else + { + tagTable = "MainDicomTags "; + } + + std::string tagFilter = orderArg + ".tagGroup = " + boost::lexical_cast<std::string>(tag.GetGroup()) + " AND " + orderArg + ".tagElement = " + boost::lexical_cast<std::string>(tag.GetElement()); + + if (tagLevel == requestLevel) + { + target = " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + FormatLevel(requestLevel) + + ".internalId AND " + tagFilter; + } + else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 1) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "parent.internalId AND " + tagFilter; + } + else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 2) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandparent.internalId AND " + tagFilter; + } + else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 3) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId " + " INNER JOIN Resources " + orderArg + "grandgrandparent ON " + orderArg + "grandgrandparent.internalId = " + orderArg + "grandparent.parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandgrandparent.internalId AND " + tagFilter; + } + } + + static void FormatJoinForOrdering(std::string& target, + const MetadataType& metadata, + size_t index, + ResourceType requestLevel) + { + std::string arg = "order" + boost::lexical_cast<std::string>(index); + + + target = " INNER JOIN Metadata " + arg + " ON " + arg + ".id = " + FormatLevel(requestLevel) + + ".internalId AND " + arg + ".type = " + + boost::lexical_cast<std::string>(metadata); + } + static std::string Join(const std::list<std::string>& values, const std::string& prefix, const std::string& separator) @@ -619,8 +690,51 @@ assert(upperLevel <= queryLevel && queryLevel <= lowerLevel); - 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 + std::string ordering; + std::string orderingJoins; + + if (request.GetOrdering().size() > 0) + { + int counter = 0; + std::vector<std::string> orderByFields; + for (std::deque<FindRequest::Ordering*>::const_iterator it = request.GetOrdering().begin(); it != request.GetOrdering().end(); ++it) + { + std::string orderingJoin; + switch ((*it)->GetKeyType()) + { + case FindRequest::KeyType_DicomTag: + FormatJoinForOrdering(orderingJoin, (*it)->GetDicomTag(), counter, request.GetLevel()); + break; + case FindRequest::KeyType_Metadata: + FormatJoinForOrdering(orderingJoin, (*it)->GetMetadataType(), counter, request.GetLevel()); + break; + default: + throw OrthancException(ErrorCode_InternalError); + } + orderingJoins += orderingJoin; + + std::string orderByField = "order" + boost::lexical_cast<std::string>(counter) + ".value"; + if ((*it)->GetDirection() == FindRequest::OrderingDirection_Ascending) + { + orderByField += " ASC"; + } + else + { + orderByField += " DESC"; + } + orderByFields.push_back(orderByField); + ++counter; + } + + std::string orderByFieldsString; + Toolbox::JoinStrings(orderByFieldsString, orderByFields, ", "); + ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + ") AS rowNumber"; + } + else + { + 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 + } sql = ("SELECT " + strQueryLevel + ".publicId, " + @@ -738,7 +852,7 @@ ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); } - sql += joins + Join(where, " WHERE ", " AND "); + sql += joins + orderingJoins + Join(where, " WHERE ", " AND "); if (request.HasLimits()) {