--- a/Framework/Plugins/ISqlLookupFormatter.cpp	Mon Sep 30 18:02:36 2024 +0200
+++ b/Framework/Plugins/ISqlLookupFormatter.cpp	Tue Oct 08 17:57:22 2024 +0200
@@ -64,9 +64,33 @@
   }
 
 
+  static std::string FormatLevel(const char* prefix, Orthanc::ResourceType level)
+  {
+    switch (level)
+    {
+      case Orthanc::ResourceType_Patient:
+        return std::string(prefix) + "patients";
+        
+      case Orthanc::ResourceType_Study:
+        return std::string(prefix) + "studies";
+        
+      case Orthanc::ResourceType_Series:
+        return std::string(prefix) + "series";
+        
+      case Orthanc::ResourceType_Instance:
+        return std::string(prefix) + "instances";
+
+      default:
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+    }
+  }      
+
   static bool FormatComparison(std::string& target,
                                ISqlLookupFormatter& formatter,
-                               const DatabaseConstraint& constraint,
+                               OrthancDatabases::ConstraintType constraintType,
+                               const std::vector<std::string>& values,
+                               bool isCaseSensitive,
+                               bool isMandatory,
                                size_t index,
                                bool escapeBrackets)
   {
@@ -74,14 +98,14 @@
 
     std::string comparison;
 
-    switch (constraint.GetConstraintType())
+    switch (constraintType)
     {
       case ConstraintType_Equal:
       case ConstraintType_SmallerOrEqual:
       case ConstraintType_GreaterOrEqual:
       {
         std::string op;
-        switch (constraint.GetConstraintType())
+        switch (constraintType)
         {
           case ConstraintType_Equal:
             op = "=";
@@ -99,9 +123,9 @@
             throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
         }
 
-        std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
+        std::string parameter = formatter.GenerateParameter(values[0]);
 
-        if (constraint.IsCaseSensitive())
+        if (isCaseSensitive)
         {
           comparison = tag + ".value " + op + " " + parameter;
         }
@@ -115,16 +139,16 @@
 
       case ConstraintType_List:
       {
-        for (size_t i = 0; i < constraint.GetValuesCount(); i++)
+        for (size_t i = 0; i < values.size(); i++)
         {
           if (!comparison.empty())
           {
             comparison += ", ";
           }
 
-          std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
+          std::string parameter = formatter.GenerateParameter(values[i]);
 
-          if (constraint.IsCaseSensitive())
+          if (isCaseSensitive)
           {
             comparison += parameter;
           }
@@ -134,7 +158,7 @@
           }
         }
 
-        if (constraint.IsCaseSensitive())
+        if (isCaseSensitive)
         {
           comparison = tag + ".value IN (" + comparison + ")";
         }
@@ -148,11 +172,11 @@
 
       case ConstraintType_Wildcard:
       {
-        const std::string value = constraint.GetSingleValue();
+        const std::string value = values[0];
 
         if (value == "*")
         {
-          if (!constraint.IsMandatory())
+          if (!isMandatory)
           {
             // Universal constraint on an optional tag, ignore it
             return false;
@@ -201,7 +225,7 @@
 
           std::string parameter = formatter.GenerateParameter(escaped);
 
-          if (constraint.IsCaseSensitive())
+          if (isCaseSensitive)
           {
             comparison = (tag + ".value LIKE " + parameter + " " +
                           formatter.FormatWildcardEscape());
@@ -220,7 +244,7 @@
         return false;
     }
 
-    if (constraint.IsMandatory())
+    if (isMandatory)
     {
       target = comparison;
     }
@@ -237,6 +261,88 @@
   }
 
 
+  static bool FormatComparison(std::string& target,
+                               ISqlLookupFormatter& formatter,
+                               const Orthanc::DatabasePluginMessages::DatabaseMetadataConstraint& constraint,
+                               size_t index,
+                               bool escapeBrackets)
+  {
+    std::vector<std::string> values;
+    OrthancDatabases::ConstraintType constraintType;
+    switch (constraint.type())
+    {
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_EQUAL:
+      constraintType = OrthancDatabases::ConstraintType_Equal;
+      break;
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_GREATER_OR_EQUAL:
+      constraintType = OrthancDatabases::ConstraintType_GreaterOrEqual;
+      break;
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_LIST:
+      constraintType = OrthancDatabases::ConstraintType_List;
+      break;
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_SMALLER_OR_EQUAL:
+      constraintType = OrthancDatabases::ConstraintType_SmallerOrEqual;
+      break;
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_WILDCARD:
+      constraintType = OrthancDatabases::ConstraintType_Wildcard;
+      break;
+    default:
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+    }
+
+    if (constraint.type() == Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_LIST)
+    {
+      for (int i = 0; i < constraint.values_size(); ++i)
+      {
+        values.push_back(constraint.values(i));
+      }
+    }
+    else
+    {
+      assert(constraint.values_size() == 1);
+      values.push_back(constraint.values(0));
+    }
+
+    return FormatComparison(target,
+                            formatter,
+                            constraintType,
+                            values,
+                            constraint.is_case_sensitive(),
+                            constraint.is_mandatory(),
+                            index,
+                            escapeBrackets); 
+
+  }
+
+  static bool FormatComparison(std::string& target,
+                               ISqlLookupFormatter& formatter,
+                               const DatabaseConstraint& constraint,
+                               size_t index,
+                               bool escapeBrackets)
+  {
+    std::vector<std::string> values;
+    if (constraint.GetConstraintType() == OrthancDatabases::ConstraintType_List)
+    {
+      for (size_t i = 0; i < constraint.GetValuesCount(); ++i)
+      {
+        values.push_back(constraint.GetValue(i));
+      }
+    }
+    else
+    {
+      values.push_back(constraint.GetSingleValue());
+    }
+
+    return FormatComparison(target,
+                            formatter,
+                            constraint.GetConstraintType(),
+                            values,
+                            constraint.IsCaseSensitive(),
+                            constraint.IsMandatory(),
+                            index,
+                            escapeBrackets); 
+  }
+
   static void FormatJoin(std::string& target,
                          const DatabaseConstraint& constraint,
                          size_t index)
@@ -268,6 +374,95 @@
                boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
   }
 
+  static void FormatJoin(std::string& target,
+                         const Orthanc::DatabasePluginMessages::DatabaseMetadataConstraint& constraint,
+                         Orthanc::ResourceType level,
+                         size_t index)
+  {
+    std::string tag = "t" + boost::lexical_cast<std::string>(index);
+
+    if (constraint.is_mandatory())
+    {
+      target = " INNER JOIN ";
+    }
+    else
+    {
+      target = " LEFT JOIN ";
+    }
+
+    target += "Metadata ";
+
+    target += tag + " ON " + tag + ".id = " + FormatLevel(level) +
+               ".internalId AND " + tag + ".type = " +
+               boost::lexical_cast<std::string>(constraint.metadata());
+  }
+
+  static void FormatJoinForOrdering(std::string& target,
+                                    uint32_t tagGroup,
+                                    uint32_t tagElement,
+                                    Orthanc::ResourceType tagLevel,
+                                    bool isIdentifierTag,
+                                    size_t index,
+                                    Orthanc::ResourceType requestLevel)
+  {
+    std::string orderArg = "order" + boost::lexical_cast<std::string>(index);
+
+    target.clear();
+
+    if (tagLevel == Orthanc::ResourceType_Patient && requestLevel == Orthanc::ResourceType_Study)
+    { // Patient tags are copied at study level
+      tagLevel = Orthanc::ResourceType_Study;
+    }
+
+    std::string tagTable;
+    if (isIdentifierTag)
+    {
+      tagTable = "DicomIdentifiers ";
+    }
+    else
+    {
+      tagTable = "MainDicomTags ";
+    }
+
+    std::string tagFilter = orderArg + ".tagGroup = " + boost::lexical_cast<std::string>(tagGroup) + " AND " + orderArg + ".tagElement = " + boost::lexical_cast<std::string>(tagElement);
+
+    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,
+                                    int32_t metadata,
+                                    size_t index,
+                                    Orthanc::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,
@@ -643,6 +838,24 @@
     }
   }
 
+  static const std::string& GetOrthancIdentifier(const Orthanc::DatabasePluginMessages::Find_Request& request, Orthanc::ResourceType level)
+  {
+    switch (level)
+    {
+      case Orthanc::ResourceType::ResourceType_Patient:
+        return request.orthanc_id_patient();
+      case Orthanc::ResourceType::ResourceType_Study:
+        return request.orthanc_id_study();
+      case Orthanc::ResourceType::ResourceType_Series:
+        return request.orthanc_id_series();
+      case Orthanc::ResourceType::ResourceType_Instance:
+        return request.orthanc_id_instance();
+      default:
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+    }
+  }
+  
+
   void ISqlLookupFormatter::Apply(std::string& sql,
                                   ISqlLookupFormatter& formatter,
                                   const Orthanc::DatabasePluginMessages::Find_Request& request)
@@ -664,7 +877,53 @@
     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.ordering_size() > 0)
+    {
+      std::vector<std::string> orderByFields;
+
+      for (int i = 0; i < request.ordering_size(); ++i)
+      {
+        std::string orderingJoin;
+        const Orthanc::DatabasePluginMessages::Find_Request_Ordering& ordering = request.ordering(i);
+        
+        switch (ordering.key_type())
+        {
+          case Orthanc::DatabasePluginMessages::OrderingKeyType::ORDERING_KEY_TYPE_DICOM_TAG:
+            FormatJoinForOrdering(orderingJoin, ordering.tag_group(), ordering.tag_element(), MessagesToolbox::Convert(ordering.tag_level()), ordering.is_identifier_tag(), i, queryLevel);
+            break;
+          case Orthanc::DatabasePluginMessages::OrderingKeyType::ORDERING_KEY_TYPE_METADATA:
+            FormatJoinForOrdering(orderingJoin, ordering.metadata(), i, queryLevel);
+            break;
+          default:
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+        }
+
+        orderingJoins += orderingJoin;
+        
+        std::string orderByField = "order" + boost::lexical_cast<std::string>(i) + ".value";
+        if (ordering.direction() == Orthanc::DatabasePluginMessages::OrderingDirection::ORDERING_DIRECTION_ASC)
+        {
+          orderByField += " ASC";
+        }
+        else
+        {
+          orderByField += " DESC";
+        }
+
+        orderByFields.push_back(orderByField);
+      }
+
+      std::string orderByFieldsString;
+      Orthanc::Toolbox::JoinStrings(orderByFieldsString, orderByFields, ", ");
+      ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + " NULLS LAST) 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, " +
@@ -680,84 +939,79 @@
                                               !request.orthanc_id_series().empty() ||
                                               !request.orthanc_id_instance().empty());
 
-    if (isOrthancIdentifiersDefined &&
-        Orthanc::IsResourceLevelAboveOrEqual(DetectLevel(request), queryLevel))
+    // handle parent constraints
+    if (isOrthancIdentifiersDefined && Orthanc::IsResourceLevelAboveOrEqual(DetectLevel(request), queryLevel))
     {
-      // single child resource matching, there should not be other constraints (at least for now)
-      if (request.dicom_tag_constraints().size() != 0 ||
-          request.labels().size() != 0 ||
-          request.has_limits())
-      {
-        throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
-      }
+      Orthanc::ResourceType topParentLevel = DetectLevel(request);
 
-      Orthanc::ResourceType topParentLevel = DetectLevel(request);
-      const std::string& strTopParentLevel = FormatLevel(topParentLevel);
-
-      std::string publicId;
-      switch (topParentLevel)
+      if (topParentLevel == queryLevel)
       {
-        case Orthanc::ResourceType_Patient:
-          publicId = request.orthanc_id_patient();
-          break;
-
-        case Orthanc::ResourceType_Study:
-          publicId = request.orthanc_id_study();
-          break;
-
-        case Orthanc::ResourceType_Series:
-          publicId = request.orthanc_id_series();
-          break;
-
-        case Orthanc::ResourceType_Instance:
-          publicId = request.orthanc_id_instance();
-          break;
-
-        default:
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+        comparisons += " AND " + FormatLevel(topParentLevel) + ".publicId = " + formatter.GenerateParameter(GetOrthancIdentifier(request, topParentLevel));
       }
+      else
+      {
+        comparisons += " AND " + FormatLevel("parent", topParentLevel) + ".publicId = " + formatter.GenerateParameter(GetOrthancIdentifier(request, topParentLevel));
 
-      if (publicId.empty())
-      {
-        throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
-      }
-
-      comparisons = " AND " + strTopParentLevel + ".publicId = " + formatter.GenerateParameter(publicId);
-
-      for (int level = queryLevel; level > topParentLevel; level--)
-      {
-        sql += (" INNER JOIN Resources " +
-                FormatLevel(static_cast<Orthanc::ResourceType>(level - 1)) + " ON " +
-                FormatLevel(static_cast<Orthanc::ResourceType>(level - 1)) + ".internalId=" +
-                FormatLevel(static_cast<Orthanc::ResourceType>(level)) + ".parentId");
-      }
-    }
-    else
-    {
-      size_t count = 0;
-
-      for (size_t i = 0; i < constraints.GetSize(); i++)
-      {
-        const DatabaseConstraint& constraint = constraints.GetConstraint(i);
-
-        std::string comparison;
-
-        if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets))
+        for (int level = queryLevel; level > topParentLevel; level--)
         {
-          std::string join;
-          FormatJoin(join, constraint, count);
-          joins += join;
-
-          if (!comparison.empty())
+          joins += " INNER JOIN Resources " +
+                  FormatLevel("parent", static_cast<Orthanc::ResourceType>(level - 1)) + " ON " +
+                  FormatLevel("parent", static_cast<Orthanc::ResourceType>(level - 1)) + ".internalId = ";
+          if (level == queryLevel)
           {
-            comparisons += " AND " + comparison;
+            joins += FormatLevel(static_cast<Orthanc::ResourceType>(level)) + ".parentId";
           }
-
-          count ++;
+          else
+          {
+            joins += FormatLevel("parent", static_cast<Orthanc::ResourceType>(level)) + ".parentId";
+          }
         }
       }
     }
 
+    size_t count = 0;
+
+    for (size_t i = 0; i < constraints.GetSize(); i++)
+    {
+      const DatabaseConstraint& constraint = constraints.GetConstraint(i);
+
+      std::string comparison;
+
+      if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets))
+      {
+        std::string join;
+        FormatJoin(join, constraint, count);
+        joins += join;
+
+        if (!comparison.empty())
+        {
+          comparisons += " AND " + comparison;
+        }
+
+        count ++;
+      }
+    }
+
+    for (int i = 0; i < request.metadata_constraints_size(); i++)
+    {
+      std::string comparison;
+      
+      if (FormatComparison(comparison, formatter, request.metadata_constraints(i), count, escapeBrackets))
+      {
+        std::string join;
+        FormatJoin(join, request.metadata_constraints(i), queryLevel, count);
+        joins += join;
+
+        if (!comparison.empty())
+        {
+          comparisons += " AND " + comparison;
+        }
+        
+        count ++;
+      }
+    }
+
+
     for (int level = queryLevel - 1; level >= upperLevel; level--)
     {
       sql += (" INNER JOIN Resources " +
@@ -817,7 +1071,7 @@
                       ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
     }
 
-    sql += joins + Join(where, " WHERE ", " AND ");
+    sql += joins + orderingJoins + Join(where, " WHERE ", " AND ");
 
     if (request.has_limits())
     {