changeset 5825:881cd0965146 find-refactoring

added OrderBy support in SQLite
author Alain Mazy <am@orthanc.team>
date Fri, 04 Oct 2024 19:03:14 +0200
parents 7c2b4fa94633
children d73dfb4548c6
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())
     {