--- a/OrthancServer/Sources/Database/FindResponse.cpp	Mon Sep 02 17:17:22 2024 +0200
+++ b/OrthancServer/Sources/Database/FindResponse.cpp	Fri Aug 30 18:03:37 2024 +0200
@@ -688,15 +688,17 @@
     else
     {
       const std::string& id = item->GetIdentifier();
+      int64_t internalId = item->GetInternalId();
 
-      if (index_.find(id) == index_.end())
+      if (identifierIndex_.find(id) == identifierIndex_.end() && internalIdIndex_.find(internalId) == internalIdIndex_.end())
       {
         items_.push_back(protection.release());
-        index_[id] = item;
+        identifierIndex_[id] = item;
+        internalIdIndex_[internalId] = item;
       }
       else
       {
-        throw OrthancException(ErrorCode_BadSequenceOfCalls, "This resource has already been added: " + id);
+        throw OrthancException(ErrorCode_BadSequenceOfCalls, "This resource has already been added: " + id + "/" + boost::lexical_cast<std::string>(internalId));
       }
     }
   }
@@ -718,9 +720,25 @@
 
   FindResponse::Resource& FindResponse::GetResourceByIdentifier(const std::string& id)
   {
-    Index::const_iterator found = index_.find(id);
+    IdentifierIndex::const_iterator found = identifierIndex_.find(id);
 
-    if (found == index_.end())
+    if (found == identifierIndex_.end())
+    {
+      throw OrthancException(ErrorCode_InexistentItem);
+    }
+    else
+    {
+      assert(found->second != NULL);
+      return *found->second;
+    }
+  }
+
+
+  FindResponse::Resource& FindResponse::GetResourceByInternalId(int64_t internalId)
+  {
+    InternalIdIndex::const_iterator found = internalIdIndex_.find(internalId);
+
+    if (found == internalIdIndex_.end())
     {
       throw OrthancException(ErrorCode_InexistentItem);
     }

--- a/OrthancServer/Sources/Database/FindResponse.h	Mon Sep 02 17:17:22 2024 +0200
+++ b/OrthancServer/Sources/Database/FindResponse.h	Fri Aug 30 18:03:37 2024 +0200
@@ -280,10 +280,12 @@
     };
 
   private:
-    typedef std::map<std::string, Resource*>  Index;
+    typedef std::map<std::string, Resource*>  IdentifierIndex;
+    typedef std::map<int64_t, Resource*>      InternalIdIndex;
 
     std::deque<Resource*>  items_;
-    Index                  index_;
+    IdentifierIndex        identifierIndex_;
+    InternalIdIndex        internalIdIndex_;
 
   public:
     ~FindResponse();
@@ -299,14 +301,26 @@
 
     Resource& GetResourceByIdentifier(const std::string& id);
 
+    Resource& GetResourceByInternalId(int64_t internalId);
+
     const Resource& GetResourceByIdentifier(const std::string& id) const
     {
       return const_cast<FindResponse&>(*this).GetResourceByIdentifier(id);
     }
 
+    const Resource& GetResourceByInternalId(int64_t internalId) const
+    {
+      return const_cast<FindResponse&>(*this).GetResourceByInternalId(internalId);
+    }
+
     bool HasResource(const std::string& id) const
     {
-      return (index_.find(id) != index_.end());
+      return (identifierIndex_.find(id) != identifierIndex_.end());
+    }
+
+    bool HasResource(int64_t& internalId) const
+    {
+      return (internalIdIndex_.find(internalId) != internalIdIndex_.end());
     }
   };
 }

--- a/OrthancServer/Sources/Database/OrthancIdentifiers.cpp	Mon Sep 02 17:17:22 2024 +0200
+++ b/OrthancServer/Sources/Database/OrthancIdentifiers.cpp	Fri Aug 30 18:03:37 2024 +0200
@@ -165,23 +165,23 @@
     {
       return ResourceType_Patient;
     }
-    else if (HasPatientId() &&
+    else if (// HasPatientId() &&
              HasStudyId() &&
              !HasSeriesId() &&
              !HasInstanceId())
     {
       return ResourceType_Study;
     }
-    else if (HasPatientId() &&
-             HasStudyId() &&
+    else if (// HasPatientId() &&
+             // HasStudyId() &&
              HasSeriesId() &&
              !HasInstanceId())
     {
       return ResourceType_Series;
     }
-    else if (HasPatientId() &&
-             HasStudyId() &&
-             HasSeriesId() &&
+    else if (// HasPatientId() &&
+             // HasStudyId() &&
+             // HasSeriesId() &&
              HasInstanceId())
     {
       return ResourceType_Instance;
@@ -240,4 +240,9 @@
         throw OrthancException(ErrorCode_ParameterOutOfRange);
     }
   }
+
+  bool OrthancIdentifiers::IsDefined() const
+  {
+    return HasPatientId() || HasStudyId() || HasSeriesId() || HasInstanceId();
+  }
 }

--- a/OrthancServer/Sources/Database/OrthancIdentifiers.h	Mon Sep 02 17:17:22 2024 +0200
+++ b/OrthancServer/Sources/Database/OrthancIdentifiers.h	Fri Aug 30 18:03:37 2024 +0200
@@ -89,5 +89,7 @@
                   const std::string& id);
 
     std::string GetLevel(ResourceType level) const;
+
+    bool IsDefined() const;
   };
 }

--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Mon Sep 02 17:17:22 2024 +0200
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Fri Aug 30 18:03:37 2024 +0200
@@ -382,6 +382,131 @@
       }
     }
 
+    virtual void ExecuteFind(FindResponse& response,
+                             const FindRequest& request,
+                             const Capabilities& capabilities) ORTHANC_OVERRIDE
+    {
+      LookupFormatter formatter;
+
+      std::string sql;
+      LookupFormatter::Apply(sql, formatter, request);
+
+      sql = "CREATE TEMPORARY TABLE Lookup AS " + sql;
+    
+      {
+        SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
+        s.Run();
+      }
+
+      {
+        SQLite::Statement statement(db_, sql);
+        formatter.Bind(statement);
+        statement.Run();
+      }
+
+      {
+        SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId, internalId FROM Lookup");
+        while (s.Step())
+        {
+          response.Add(new FindResponse::Resource(request.GetLevel(), s.ColumnInt64(1), s.ColumnString(0)));
+        }
+      }
+
+      if (request.IsRetrieveMainDicomTags())
+      {
+        sql = "SELECT id, tagGroup, tagElement, value "
+              "FROM MainDicomTags "
+              "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId";
+
+        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
+        while (s.Step())
+        {
+          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
+          res.AddStringDicomTag(request.GetLevel(), 
+                                static_cast<uint16_t>(s.ColumnInt(1)),
+                                static_cast<uint16_t>(s.ColumnInt(2)),
+                                s.ColumnString(3));
+        }
+      }
+
+      if (request.IsRetrieveParentIdentifier())
+      {
+        sql = "SELECT currentLevel.internalId, parentLevel.publicId "
+              "FROM Resources AS currentLevel "
+              "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
+              "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId ";
+
+        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
+        while (s.Step())
+        {
+          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
+          res.SetParentIdentifier(s.ColumnString(1));
+        }
+      }
+
+      if (request.IsRetrieveMetadata())
+      {
+        sql = "SELECT id, type, value "
+              "FROM Metadata "
+              "INNER JOIN Lookup ON Metadata.id = Lookup.internalId";
+
+        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
+        while (s.Step())
+        {
+          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
+          res.AddMetadata(request.GetLevel(),
+                          static_cast<MetadataType>(s.ColumnInt(1)),
+                          s.ColumnString(2));
+        }
+      }
+
+      if (request.IsRetrieveLabels())
+      {
+        sql = "SELECT id, label "
+              "FROM Labels "
+              "INNER JOIN Lookup ON Labels.id = Lookup.internalId";
+
+        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
+        while (s.Step())
+        {
+          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
+          res.AddLabel(s.ColumnString(1));
+        }
+      }
+
+      if (request.GetLevel() <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 1)).IsRetrieveIdentifiers())
+      {
+        sql = "SELECT currentLevel.internalId, childLevel.publicId "
+              "FROM Resources AS currentLevel "
+              "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
+              "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId ";
+
+        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
+        while (s.Step())
+        {
+          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
+          res.AddChildIdentifier(static_cast<ResourceType>(request.GetLevel() + 1), s.ColumnString(1));
+        }
+      }
+
+      if (request.GetLevel() <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 2)).IsRetrieveIdentifiers())
+      {
+        sql = "SELECT currentLevel.internalId, grandChildLevel.publicId "
+              "FROM Resources AS currentLevel "
+              "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
+              "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "
+              "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId ";
+
+        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
+        while (s.Step())
+        {
+          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
+          res.AddChildIdentifier(static_cast<ResourceType>(request.GetLevel() + 2), s.ColumnString(1));
+        }
+      }
+    }
+
+
 
     // From the "ICreateInstance" interface
     virtual void AttachChild(int64_t parent,

--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h	Mon Sep 02 17:17:22 2024 +0200
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h	Fri Aug 30 18:03:37 2024 +0200
@@ -99,6 +99,11 @@
       throw OrthancException(ErrorCode_NotImplemented);
     }
 
+    virtual bool HasIntegratedFind() const ORTHANC_OVERRIDE
+    {
+      return true;
+    }
+
     /**
      * The "StartTransaction()" method is guaranteed to return a class
      * derived from "UnitTestsTransaction". The methods of

--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Mon Sep 02 17:17:22 2024 +0200
+++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Fri Aug 30 18:03:37 2024 +0200
@@ -34,6 +34,7 @@
 #if ORTHANC_BUILDING_SERVER_LIBRARY == 1
 #  include "../../../OrthancFramework/Sources/OrthancException.h"
 #  include "../../../OrthancFramework/Sources/Toolbox.h"
+#  include "../Database/FindRequest.h"
 #else
 #  include <OrthancException.h>
 #  include <Toolbox.h>
@@ -616,6 +617,151 @@
     }
   }
 
+#if ORTHANC_BUILDING_SERVER_LIBRARY == 1
+  void ISqlLookupFormatter::Apply(std::string& sql,
+                                  ISqlLookupFormatter& formatter,
+                                  const FindRequest& request)
+  {
+    const bool escapeBrackets = formatter.IsEscapeBrackets();
+    ResourceType queryLevel = request.GetLevel();
+    const std::string& strQueryLevel = FormatLevel(queryLevel);
+    
+    std::string joins, comparisons;
+
+    if (request.GetOrthancIdentifiers().IsDefined() && request.GetOrthancIdentifiers().DetectLevel() == queryLevel)
+    {
+      // single resource matching, there should not be other constraints
+      assert(request.GetDicomTagConstraints().GetSize() == 0);
+      assert(request.GetLabels().size() == 0);
+      assert(request.HasLimits() == false);
+
+      comparisons = " AND " + strQueryLevel + ".publicId = " + formatter.GenerateParameter(request.GetOrthancIdentifiers().GetLevel(queryLevel));
+    }
+    else if (request.GetOrthancIdentifiers().IsDefined() && request.GetOrthancIdentifiers().DetectLevel() == queryLevel - 1)
+    {
+      // single child resource matching, there should not be other constraints (at least for now)
+      assert(request.GetDicomTagConstraints().GetSize() == 0);
+      assert(request.GetLabels().size() == 0);
+      assert(request.HasLimits() == false);
+
+      ResourceType parentLevel = static_cast<ResourceType>(queryLevel - 1);
+      const std::string& strParentLevel = FormatLevel(parentLevel);
+
+      comparisons = " AND " + strParentLevel + ".publicId = " + formatter.GenerateParameter(request.GetOrthancIdentifiers().GetLevel(parentLevel));
+      joins += (" INNER JOIN Resources " +
+              strParentLevel + " ON " +
+              strQueryLevel + ".parentId=" +
+              strParentLevel + ".internalId");
+    }
+    // TODO-FIND other levels (there's probably a way to make it generic as it was done before !!!): 
+    //    /studies/../instances
+    //    /patients/../instances
+    //    /series/../study
+    //    /instances/../study
+    // ...
+    else
+    {
+      size_t count = 0;
+      
+      const DatabaseConstraints& dicomTagsConstraints = request.GetDicomTagConstraints();
+      for (size_t i = 0; i < dicomTagsConstraints.GetSize(); i++)
+      {
+        const DatabaseConstraint& constraint = dicomTagsConstraints.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 ++;
+        }
+      }
+    }
+
+    sql = ("SELECT " +
+           strQueryLevel + ".publicId, " +
+           strQueryLevel + ".internalId" +
+           " FROM Resources AS " + strQueryLevel);
+
+    // for (int level = queryLevel - 1; level >= upperLevel; level--)
+    // {
+    //   sql += (" INNER JOIN Resources " +
+    //           FormatLevel(static_cast<ResourceType>(level)) + " ON " +
+    //           FormatLevel(static_cast<ResourceType>(level)) + ".internalId=" +
+    //           FormatLevel(static_cast<ResourceType>(level + 1)) + ".parentId");
+    // }
+      
+    // for (int level = queryLevel + 1; level <= lowerLevel; level++)
+    // {
+    //   sql += (" INNER JOIN Resources " +
+    //           FormatLevel(static_cast<ResourceType>(level)) + " ON " +
+    //           FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
+    //           FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
+    // }
+
+    std::list<std::string> where;
+    where.push_back(strQueryLevel + ".resourceType = " +
+                    formatter.FormatResourceType(queryLevel) + comparisons);
+
+
+    if (!request.GetLabels().empty())
+    {
+      /**
+       * "In SQL Server, NOT EXISTS and NOT IN predicates are the best
+       * way to search for missing values, as long as both columns in
+       * question are NOT NULL."
+       * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/
+       **/
+
+      const std::set<std::string>& labels = request.GetLabels();
+      std::list<std::string> formattedLabels;
+      for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it)
+      {
+        formattedLabels.push_back(formatter.GenerateParameter(*it));
+      }
+
+      std::string condition;
+      switch (request.GetLabelsConstraint())
+      {
+        case LabelsConstraint_Any:
+          condition = "> 0";
+          break;
+          
+        case LabelsConstraint_All:
+          condition = "= " + boost::lexical_cast<std::string>(labels.size());
+          break;
+          
+        case LabelsConstraint_None:
+          condition = "= 0";
+          break;
+          
+        default:
+          throw OrthancException(ErrorCode_ParameterOutOfRange);
+      }
+      
+      where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel +
+                      ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
+    }
+
+    sql += joins + Join(where, " WHERE ", " AND ");
+
+    if (request.HasLimits())
+    {
+      sql += " LIMIT " + boost::lexical_cast<std::string>(request.GetLimitsCount());
+      sql += " OFFSET " + boost::lexical_cast<std::string>(request.GetLimitsSince());
+    }
+
+  }
+#endif
+
 
   void ISqlLookupFormatter::ApplySingleLevel(std::string& sql,
                                              ISqlLookupFormatter& formatter,

--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.h	Mon Sep 02 17:17:22 2024 +0200
+++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.h	Fri Aug 30 18:03:37 2024 +0200
@@ -35,7 +35,8 @@
 namespace Orthanc
 {
   class DatabaseConstraints;
-  
+  class FindRequest;
+
   enum LabelsConstraint
   {
     LabelsConstraint_All,
@@ -84,5 +85,11 @@
                                  const std::set<std::string>& labels,  // New in Orthanc 1.12.0
                                  LabelsConstraint labelsConstraint,    // New in Orthanc 1.12.0
                                  size_t limit);
+
+#if ORTHANC_BUILDING_SERVER_LIBRARY == 1
+    static void Apply(std::string& sql,
+                      ISqlLookupFormatter& formatter,
+                      const FindRequest& request);
+#endif
   };
 }