changeset 5796:16ce3c920f71 find-refactoring

rewrote SQLite find using CTEs instead of temporary tables
author Alain Mazy <am@orthanc.team>
date Thu, 19 Sep 2024 17:50:20 +0200
parents 9990b4140c1c
children ebd828518719
files OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp OrthancServer/Sources/Search/ISqlLookupFormatter.cpp
diffstat 2 files changed, 587 insertions(+), 330 deletions(-) [+]
line wrap: on
line diff
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Wed Sep 18 15:45:18 2024 +0200
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Thu Sep 19 17:50:20 2024 +0200
@@ -43,6 +43,39 @@
 
 namespace Orthanc
 {  
+  static std::string JoinRequestedMetadata(const FindRequest::ChildrenSpecification& childrenSpec)
+  {
+    std::set<std::string> metadataTypes;
+    for (std::set<MetadataType>::const_iterator it = childrenSpec.GetMetadata().begin(); it != childrenSpec.GetMetadata().end(); ++it)
+    {
+      metadataTypes.insert(boost::lexical_cast<std::string>(*it));
+    }
+    std::string joinedMetadataTypes;
+    Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ", ");
+
+    return joinedMetadataTypes;
+  }
+
+  static std::string JoinRequestedTags(const FindRequest::ChildrenSpecification& childrenSpec)
+  {
+    // note: SQLite does not seem to support (tagGroup, tagElement) in ((x, y), (z, w)) in complex subqueries.
+    // Therefore, since we expect the requested tag list to be short, we write it as 
+    // ((tagGroup = x AND tagElement = y ) OR (tagGroup = z AND tagElement = w))
+
+    std::string sql = " (";
+    std::set<std::string> tags;
+    for (std::set<DicomTag>::const_iterator it = childrenSpec.GetMainDicomTags().begin(); it != childrenSpec.GetMainDicomTags().end(); ++it)
+    {
+      tags.insert("(tagGroup = " + boost::lexical_cast<std::string>(it->GetGroup()) 
+                  + " AND tagElement = " + boost::lexical_cast<std::string>(it->GetElement()) + ")");
+    }
+    std::string joinedTags;
+    Orthanc::Toolbox::JoinStrings(joinedTags, tags, " OR ");
+
+    sql += joinedTags + ") ";
+    return sql;
+  }
+
   class SQLiteDatabaseWrapper::LookupFormatter : public ISqlLookupFormatter
   {
   private:
@@ -390,7 +423,7 @@
       std::string sql;
       LookupFormatter::Apply(sql, formatter, lookup, queryLevel, labels, labelsConstraint, limit);
 
-      sql = "CREATE TEMPORARY TABLE Lookup AS " + sql;
+      sql = "CREATE TEMPORARY TABLE Lookup AS " + sql;   // TODO-FIND: use a CTE (or is this method obsolete ?)
     
       {
         SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
@@ -420,134 +453,311 @@
       }
     }
 
+#define C0_QUERY_ID 0
+#define C1_INTERNAL_ID 1
+#define C2_ROW_NUMBER 2
+#define C3_STRING_1 3
+#define C4_STRING_2 4
+#define C5_STRING_3 5
+#define C6_INT_1 6
+#define C7_INT_2 7
+#define C8_BIG_INT_1 8
+#define C9_BIG_INT_2 9
+
+#define QUERY_LOOKUP 1
+#define QUERY_MAIN_DICOM_TAGS 2
+#define QUERY_ATTACHMENTS 3
+#define QUERY_METADATA 4
+#define QUERY_LABELS 5
+#define QUERY_PARENT_MAIN_DICOM_TAGS 10
+#define QUERY_PARENT_IDENTIFIER 11
+#define QUERY_PARENT_METADATA 12
+#define QUERY_GRAND_PARENT_MAIN_DICOM_TAGS 15
+#define QUERY_GRAND_PARENT_METADATA 16
+#define QUERY_CHILDREN_IDENTIFIERS 20
+#define QUERY_CHILDREN_MAIN_DICOM_TAGS 21
+#define QUERY_CHILDREN_METADATA 22
+#define QUERY_GRAND_CHILDREN_IDENTIFIERS 30
+#define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 31
+#define QUERY_GRAND_CHILDREN_METADATA 32
+#define QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS 40
+#define QUERY_ONE_INSTANCE_IDENTIFIER 50
+#define QUERY_ONE_INSTANCE_METADATA 51
+#define QUERY_ONE_INSTANCE_ATTACHMENTS 52
+
+#define STRINGIFY(x) #x
+#define TOSTRING(x) STRINGIFY(x)
+
+
     virtual void ExecuteFind(FindResponse& response,
                              const FindRequest& request,
                              const Capabilities& capabilities) ORTHANC_OVERRIDE
     {
-      const ResourceType requestLevel = request.GetLevel();
+      LookupFormatter formatter;
       std::string sql;
+      const ResourceType requestLevel = request.GetLevel();
 
+      std::string lookupSql;
+      LookupFormatter::Apply(lookupSql, formatter, request);
+
+      // base query, retrieve the ordered internalId and publicId of the selected resources
+      sql = "WITH Lookup AS (" + lookupSql + ") ";
+
+      // in SQLite, all CTEs must be created at the beginning of the query, you can not define local CTE inside subqueries
+      // need one instance info ? (part 1: create the CTE)
+      if (request.GetLevel() != ResourceType_Instance &&
+          request.IsRetrieveOneInstanceMetadataAndAttachments())
       {
-        // clean previous lookup table
-        SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
-        s.Run();
+        // Here, we create a nested CTE 'OneInstance' with one instance ID to join with metadata and main
+        sql += ", OneInstance AS";
+
+        switch (requestLevel)
+        {
+          case ResourceType_Series:
+          {
+            sql+= "  (SELECT Lookup.internalId AS parentInternalId, childLevel.publicId AS instancePublicId, childLevel.internalId AS instanceInternalId"
+                  "   FROM Resources AS childLevel "
+                  "   INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId) ";
+            break;
+          }
+
+          case ResourceType_Study:
+          {
+            sql+= "  (SELECT Lookup.internalId AS parentInternalId, grandChildLevel.publicId AS instancePublicId, grandChildLevel.internalId AS instanceInternalId"
+                  "   FROM Resources AS grandChildLevel "
+                  "   INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
+                  "   INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId) ";
+            break;
+          }
+
+          case ResourceType_Patient:
+          {
+            sql+= "  (SELECT Lookup.internalId AS parentInternalId, grandGrandChildLevel.publicId AS instancePublicId, grandGrandChildLevel.internalId AS instanceInternalId"
+                  "   FROM Resources AS grandGrandChildLevel "
+                  "   INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId "
+                  "   INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
+                  "   INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId) ";
+            break;
+          }
+
+          default:
+            throw OrthancException(ErrorCode_InternalError);
+        }
       }
 
-      {
-        // extract the resource id of interest by executing the lookup
-        LookupFormatter formatter;
-        LookupFormatter::Apply(sql, formatter, request);
-
-        sql = "CREATE TEMPORARY TABLE Lookup AS " + sql;   // TODO-FIND: use a CTE
+      sql += "SELECT "
+             "  " TOSTRING(QUERY_LOOKUP) " AS c0_queryId, "
+             "  Lookup.internalId AS c1_internalId, "
+             "  Lookup.rowNumber AS c2_rowNumber, "
+             "  Lookup.publicId AS c3_string1, "
+             "  NULL AS c4_string2, "
+             "  NULL AS c5_string3, "
+             "  NULL AS c6_int1, "
+             "  NULL AS c7_int2, "
+             "  NULL AS c8_big_int1, "
+             "  NULL AS c9_big_int2 "
+             "  FROM Lookup ";
 
-        SQLite::Statement statement(db_, sql);
-        formatter.Bind(statement);
-        statement.Run();
+      // need one instance info ? (part 2: execute the queries)
+      if (request.GetLevel() != ResourceType_Instance &&
+          request.IsRetrieveOneInstanceMetadataAndAttachments())
+      {
+        sql += "   UNION SELECT"
+               "    " TOSTRING(QUERY_ONE_INSTANCE_IDENTIFIER) " AS c0_queryId, "
+               "    parentInternalId AS c1_internalId, "
+               "    NULL AS c2_rowNumber, "
+               "    instancePublicId AS c3_string1, "
+               "    NULL AS c4_string2, "
+               "    NULL AS c5_string3, "
+               "    NULL AS c6_int1, "
+               "    NULL AS c7_int2, "
+               "    instanceInternalId AS c8_big_int1, "
+               "    NULL AS c9_big_int2 "
+               "   FROM OneInstance ";
 
-        SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT publicId, internalId FROM Lookup");
-        while (s.Step())
-        {
-          response.Add(new FindResponse::Resource(requestLevel, s.ColumnInt64(1), s.ColumnString(0)));
-        }
+        sql += "   UNION SELECT"
+               "    " TOSTRING(QUERY_ONE_INSTANCE_METADATA) " AS c0_queryId, "
+               "    parentInternalId AS c1_internalId, "
+               "    NULL AS c2_rowNumber, "
+               "    Metadata.value AS c3_string1, "
+               "    NULL AS c4_string2, "
+               "    NULL AS c5_string3, "
+               "    Metadata.type AS c6_int1, "
+               "    NULL AS c7_int2, "
+               "    NULL AS c8_big_int1, "
+               "    NULL AS c9_big_int2 "
+               "   FROM Metadata "
+               "   INNER JOIN OneInstance ON Metadata.id = OneInstance.instanceInternalId ";
+              
+        sql += "   UNION SELECT"
+               "    " TOSTRING(QUERY_ONE_INSTANCE_ATTACHMENTS) " AS c0_queryId, "
+               "    parentInternalId AS c1_internalId, "
+               "    NULL AS c2_rowNumber, "
+               "    uuid AS c3_string1, "
+               "    uncompressedMD5 AS c4_string2, "
+               "    compressedMD5 AS c5_string3, "
+               "    fileType AS c6_int1, "
+               "    compressionType AS c7_int2, "
+               "    compressedSize AS c8_big_int1, "
+               "    uncompressedSize AS c9_big_int2 "
+               "   FROM AttachedFiles "
+               "   INNER JOIN OneInstance ON AttachedFiles.id = OneInstance.instanceInternalId ";
+
       }
 
       // need MainDicomTags from resource ?
       if (request.IsRetrieveMainDicomTags())
       {
-        sql = "SELECT id, tagGroup, tagElement, value "
-              "FROM MainDicomTags "
-              "INNER JOIN Lookup ON MainDicomTags.id = Lookup.internalId";
+        sql += "UNION SELECT "
+               "  " TOSTRING(QUERY_MAIN_DICOM_TAGS) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  NULL AS c2_rowNumber, "
+               "  value AS c3_string1, "
+               "  NULL AS c4_string2, "
+               "  NULL AS c5_string3, "
+               "  tagGroup AS c6_int1, "
+               "  tagElement AS c7_int2, "
+               "  NULL AS c8_big_int1, "
+               "  NULL AS c9_big_int2 "
+               "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(requestLevel, 
-                                static_cast<uint16_t>(s.ColumnInt(1)),
-                                static_cast<uint16_t>(s.ColumnInt(2)),
-                                s.ColumnString(3));
-        }
+      // need resource metadata ?
+      if (request.IsRetrieveMetadata())
+      {
+        sql += "UNION SELECT "
+               "  " TOSTRING(QUERY_METADATA) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  NULL AS c2_rowNumber, "
+               "  value AS c3_string1, "
+               "  NULL AS c4_string2, "
+               "  NULL AS c5_string3, "
+               "  type AS c6_int1, "
+               "  NULL AS c7_int2, "
+               "  NULL AS c8_big_int1, "
+               "  NULL AS c9_big_int2 "
+               "FROM Metadata "
+               "INNER JOIN Lookup ON Metadata.id = Lookup.internalId ";
       }
 
-      
+      // need resource attachments ?
+      if (request.IsRetrieveAttachments())
+      {
+        sql += "UNION SELECT "
+               "  " TOSTRING(QUERY_ATTACHMENTS) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  NULL AS c2_rowNumber, "
+               "  uuid AS c3_string1, "
+               "  uncompressedMD5 AS c4_string2, "
+               "  compressedMD5 AS c5_string3, "
+               "  fileType AS c6_int1, "
+               "  compressionType AS c7_int2, "
+               "  compressedSize AS c8_big_int1, "
+               "  uncompressedSize AS c9_big_int2 "
+               "FROM AttachedFiles "
+               "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId ";
+      }
+
+
+      // need resource labels ?
+      if (request.IsRetrieveLabels())
+      {
+        sql += "UNION SELECT "
+               "  " TOSTRING(QUERY_LABELS) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  NULL AS c2_rowNumber, "
+               "  label AS c3_string1, "
+               "  NULL AS c4_string2, "
+               "  NULL AS c5_string3, "
+               "  NULL AS c6_int1, "
+               "  NULL AS c7_int2, "
+               "  NULL AS c8_big_int1, "
+               "  NULL AS c9_big_int2 "
+               "FROM Labels "
+               "INNER JOIN Lookup ON Labels.id = Lookup.internalId ";
+      }
+
       if (requestLevel > ResourceType_Patient)
       {
         // need MainDicomTags from parent ?
         if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMainDicomTags())
         {
-          sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value "
-                "FROM MainDicomTags "
-                "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
-                "INNER JOIN Lookup ON MainDicomTags.id = currentLevel.parentId";
-
-          SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-          while (s.Step())
-          {
-            FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-            res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 1), 
-                                  static_cast<uint16_t>(s.ColumnInt(1)),
-                                  static_cast<uint16_t>(s.ColumnInt(2)),
-                                  s.ColumnString(3));
-          }
+          sql += "UNION SELECT "
+                 "  " TOSTRING(QUERY_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
+                 "  Lookup.internalId AS c1_internalId, "
+                 "  NULL AS c2_rowNumber, "
+                 "  value AS c3_string1, "
+                 "  NULL AS c4_string2, "
+                 "  NULL AS c5_string3, "
+                 "  tagGroup AS c6_int1, "
+                 "  tagElement AS c7_int2, "
+                 "  NULL AS c8_big_int1, "
+                 "  NULL AS c9_big_int2 "
+                 "FROM Lookup "
+                 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
+                 "INNER JOIN MainDicomTags ON MainDicomTags.id = currentLevel.parentId ";
         }
 
         // need metadata from parent ?
         if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 1)).IsRetrieveMetadata())
         {
-          sql = "SELECT currentLevel.internalId, type, value "
-                "FROM Metadata "
-                "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
-                "INNER JOIN Lookup ON Metadata.id = currentLevel.parentId";
-
-          SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-          while (s.Step())
-          {
-            FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-            res.AddMetadata(static_cast<ResourceType>(requestLevel - 1), 
-                            static_cast<MetadataType>(s.ColumnInt(1)),
-                            s.ColumnString(2));
-          }
-        }
-      }
-
-      if (requestLevel > ResourceType_Study)
-      {
-        // need MainDicomTags from grandparent ?
-        if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMainDicomTags())
-        {
-          sql = "SELECT currentLevel.internalId, tagGroup, tagElement, value "
-                "FROM MainDicomTags "
-                "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
-                "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
-                "INNER JOIN Lookup ON MainDicomTags.id = parentLevel.parentId";
-
-          SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-          while (s.Step())
-          {
-            FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-            res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 2), 
-                                  static_cast<uint16_t>(s.ColumnInt(1)),
-                                  static_cast<uint16_t>(s.ColumnInt(2)),
-                                  s.ColumnString(3));
-          }
+          sql += "UNION SELECT "
+                 "  " TOSTRING(QUERY_PARENT_METADATA) " AS c0_queryId, "
+                 "  Lookup.internalId AS c1_internalId, "
+                 "  NULL AS c2_rowNumber, "
+                 "  value AS c3_string1, "
+                 "  NULL AS c4_string2, "
+                 "  NULL AS c5_string3, "
+                 "  type AS c6_int1, "
+                 "  NULL AS c7_int2, "
+                 "  NULL AS c8_big_int1, "
+                 "  NULL AS c9_big_int2 "
+                 "FROM Lookup "
+                 "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
+                 "INNER JOIN Metadata ON Metadata.id = currentLevel.parentId ";        
         }
 
-        // need metadata from grandparent ?
-        if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMetadata())
+        if (requestLevel > ResourceType_Study)
         {
-          sql = "SELECT currentLevel.internalId, type, value "
-                "FROM Metadata "
-                "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
-                "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
-                "INNER JOIN Lookup ON Metadata.id = parentLevel.parentId";
+          // need MainDicomTags from grandparent ?
+          if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMainDicomTags())
+          {
+            sql += "UNION SELECT "
+                  "  " TOSTRING(QUERY_GRAND_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
+                  "  Lookup.internalId AS c1_internalId, "
+                  "  NULL AS c2_rowNumber, "
+                  "  value AS c3_string1, "
+                  "  NULL AS c4_string2, "
+                  "  NULL AS c5_string3, "
+                  "  tagGroup AS c6_int1, "
+                  "  tagElement AS c7_int2, "
+                  "  NULL AS c8_big_int1, "
+                  "  NULL AS c9_big_int2 "
+                  "FROM Lookup "
+                  "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
+                  "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
+                  "INNER JOIN MainDicomTags ON MainDicomTags.id = parentLevel.parentId ";
+          }
 
-          SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-          while (s.Step())
+          // need metadata from grandparent ?
+          if (request.GetParentSpecification(static_cast<ResourceType>(requestLevel - 2)).IsRetrieveMetadata())
           {
-            FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-            res.AddMetadata(static_cast<ResourceType>(requestLevel - 2), 
-                            static_cast<MetadataType>(s.ColumnInt(1)),
-                            s.ColumnString(2));
+            sql += "UNION SELECT "
+                  "  " TOSTRING(QUERY_GRAND_PARENT_METADATA) " AS c0_queryId, "
+                  "  Lookup.internalId AS c1_internalId, "
+                  "  NULL AS c2_rowNumber, "
+                  "  value AS c3_string1, "
+                  "  NULL AS c4_string2, "
+                  "  NULL AS c5_string3, "
+                  "  type AS c6_int1, "
+                  "  NULL AS c7_int2, "
+                  "  NULL AS c8_big_int1, "
+                  "  NULL AS c9_big_int2 "
+                  "FROM Lookup "
+                  "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
+                  "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "
+                  "INNER JOIN Metadata ON Metadata.id = parentLevel.parentId ";
           }
         }
       }
@@ -555,216 +765,98 @@
       // need MainDicomTags from children ?
       if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMainDicomTags().size() > 0)
       {
-        sql = "SELECT Lookup.internalId, tagGroup, tagElement, value "
-              "FROM MainDicomTags "
-              "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
-              "INNER JOIN Lookup ON MainDicomTags.id = childLevel.internalId ";
-
-        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-        while (s.Step())
-        {
-          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 1), 
-                                DicomTag(static_cast<uint16_t>(s.ColumnInt(1)),
-                                         static_cast<uint16_t>(s.ColumnInt(2))),
-                                s.ColumnString(3));
-        }
+        sql += "UNION SELECT "
+               "  " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  NULL AS c2_rowNumber, "
+               "  value AS c3_string1, "
+               "  NULL AS c4_string2, "
+               "  NULL AS c5_string3, "
+               "  tagGroup AS c6_int1, "
+               "  tagElement AS c7_int2, "
+               "  NULL AS c8_big_int1, "
+               "  NULL AS c9_big_int2 "
+               "FROM Lookup "
+               "  INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
+               "  INNER JOIN MainDicomTags ON MainDicomTags.id = childLevel.internalId AND " + JoinRequestedTags(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1))); 
       }
 
       // need MainDicomTags from grandchildren ?
       if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMainDicomTags().size() > 0)
       {
-        sql = "SELECT Lookup.internalId, tagGroup, tagElement, value "
-              "FROM MainDicomTags "
-              "INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
-              "INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId "
-              "INNER JOIN Lookup ON MainDicomTags.id = grandChildLevel.internalId ";
-
-        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-        while (s.Step())
-        {
-          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 2), 
-                                DicomTag(static_cast<uint16_t>(s.ColumnInt(1)),
-                                         static_cast<uint16_t>(s.ColumnInt(2))),
-                                s.ColumnString(3));
-        }
+        sql += "UNION SELECT "
+                "  " TOSTRING(QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
+                "  Lookup.internalId AS c1_internalId, "
+                "  NULL AS c2_rowNumber, "
+                "  value AS c3_string1, "
+                "  NULL AS c4_string2, "
+                "  NULL AS c5_string3, "
+                "  tagGroup AS c6_int1, "
+                "  tagElement AS c7_int2, "
+                "  NULL AS c8_big_int1, "
+                "  NULL AS c9_big_int2 "
+                "FROM Lookup "
+                "  INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
+                "  INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId "
+                "  INNER JOIN MainDicomTags ON MainDicomTags.id = grandChildLevel.internalId AND " + JoinRequestedTags(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2))); 
       }
 
       // need parent identifier ?
       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));
-        }
-      }
-
-      // need resource metadata ?
-      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(requestLevel,
-                          static_cast<MetadataType>(s.ColumnInt(1)),
-                          s.ColumnString(2));
-        }
-      }
-
-      // need resource labels ?
-      if (request.IsRetrieveLabels())
-      {
-        sql = "SELECT Lookup.internalId, 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));
-        }
-      }
-
-      // need one instance info ?
-      if (request.GetLevel() != ResourceType_Instance &&
-          request.IsRetrieveOneInstanceMetadataAndAttachments())
-      {
-        {
-          SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS OneInstance");  // TODO-FIND: use a CTE
-          s.Run();
-        }
-
-        switch (requestLevel)
-        {
-          case ResourceType_Patient:
-          {
-            SQLite::Statement s(
-              db_, SQLITE_FROM_HERE,
-              "CREATE TEMPORARY TABLE OneInstance AS "
-              "SELECT Lookup.internalId AS parentInternalId, grandGrandChildLevel.publicId AS instancePublicId, grandGrandChildLevel.internalId AS instanceInternalId "
-              "FROM Resources AS grandGrandChildLevel "
-              "INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId "
-              "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
-              "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId");
-            s.Run();
-            break;
-          }
-
-          case ResourceType_Study:
-          {
-            SQLite::Statement s(
-              db_, SQLITE_FROM_HERE,
-              "CREATE TEMPORARY TABLE OneInstance AS "
-              "SELECT Lookup.internalId AS parentInternalId, grandChildLevel.publicId AS instancePublicId, grandChildLevel.internalId AS instanceInternalId "
-              "FROM Resources AS grandChildLevel "
-              "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
-              "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId");
-            s.Run();
-            break;
-          }
-
-          case ResourceType_Series:
-          {
-            SQLite::Statement s(
-              db_, SQLITE_FROM_HERE,
-              "CREATE TEMPORARY TABLE OneInstance AS "
-              "SELECT Lookup.internalId AS parentInternalId, childLevel.publicId AS instancePublicId, childLevel.internalId AS instanceInternalId "
-              "FROM Resources AS childLevel "
-              "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId GROUP BY Lookup.internalId");
-            s.Run();
-            break;
-          }
-
-          default:
-            throw OrthancException(ErrorCode_InternalError);
-        }
-
-        {
-          SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT parentInternalId, instancePublicId FROM OneInstance");
-          while (s.Step())
-          {
-            FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-            res.SetOneInstancePublicId(s.ColumnString(1));
-          }
-        }
-
-        {
-          SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT OneInstance.parentInternalId, Metadata.type, Metadata.value "
-                              "FROM Metadata INNER JOIN OneInstance ON Metadata.id = OneInstance.instanceInternalId");
-          while (s.Step())
-          {
-            FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-            res.AddOneInstanceMetadata(static_cast<MetadataType>(s.ColumnInt(1)), s.ColumnString(2));
-          }
-        }
-
-        {
-          SQLite::Statement s(db_, SQLITE_FROM_HERE,
-                              "SELECT OneInstance.parentInternalId, AttachedFiles.fileType, AttachedFiles.uuid, "
-                              "AttachedFiles.uncompressedSize, AttachedFiles.compressedSize, "
-                              "AttachedFiles.compressionType, AttachedFiles.uncompressedMD5, AttachedFiles.compressedMD5 "
-                              "FROM AttachedFiles INNER JOIN OneInstance ON AttachedFiles.id = OneInstance.instanceInternalId");
-          while (s.Step())
-          {
-            FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-            res.AddOneInstanceAttachment(
-              FileInfo(s.ColumnString(2), static_cast<FileContentType>(s.ColumnInt(1)),
-                       s.ColumnInt64(3), s.ColumnString(6),
-                       static_cast<CompressionType>(s.ColumnInt(5)),
-                       s.ColumnInt64(4), s.ColumnString(7)));
-          }
-        }
+        sql += "UNION SELECT "
+               "  " TOSTRING(QUERY_PARENT_IDENTIFIER) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  NULL AS c2_rowNumber, "
+               "  parentLevel.publicId AS c3_string1, "
+               "  NULL AS c4_string2, "
+               "  NULL AS c5_string3, "
+               "  NULL AS c6_int1, "
+               "  NULL AS c7_int2, "
+               "  NULL AS c8_big_int1, "
+               "  NULL AS c9_big_int2 "
+               "FROM Resources AS currentLevel "
+               "  INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
+               "  INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId ";
       }
 
       // need children metadata ?
       if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).GetMetadata().size() > 0)
       {
-        sql = "SELECT Lookup.internalId, type, value "
-              "FROM Metadata "
-              "INNER JOIN Lookup ON Lookup.internalId = childLevel.parentId "
-              "INNER JOIN Resources childLevel ON childLevel.internalId = Metadata.id";
-
-        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-        while (s.Step())
-        {
-          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 1), 
-                                       static_cast<MetadataType>(s.ColumnInt(1)),
-                                       s.ColumnString(2));
-        }
+        sql += "UNION SELECT "
+                "  " TOSTRING(QUERY_CHILDREN_METADATA) " AS c0_queryId, "
+                "  Lookup.internalId AS c1_internalId, "
+                "  NULL AS c2_rowNumber, "
+                "  value AS c3_string1, "
+                "  NULL AS c4_string2, "
+                "  NULL AS c5_string3, "
+                "  type AS c6_int1, "
+                "  NULL AS c7_int2, "
+                "  NULL AS c8_big_int1, "
+                "  NULL AS c9_big_int2 "
+                "FROM Lookup "
+                "  INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
+                "  INNER JOIN Metadata ON Metadata.id = childLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1))) + ") ";
       }
 
       // need grandchildren metadata ?
       if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).GetMetadata().size() > 0)
       {
-        sql = "SELECT Lookup.internalId, type, value "
-              "FROM Metadata "
-              "INNER JOIN Lookup ON Lookup.internalId = childLevel.parentId "
-              "INNER JOIN Resources childLevel ON childLevel.internalId = grandChildLevel.parentId "
-              "INNER JOIN Resources grandChildLevel ON grandChildLevel.internalId = Metadata.id";
-
-        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-        while (s.Step())
-        {
-          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 2), 
-                                       static_cast<MetadataType>(s.ColumnInt(1)),
-                                       s.ColumnString(2));
-        }
+        sql += "UNION SELECT "
+                "  " TOSTRING(QUERY_GRAND_CHILDREN_METADATA) " AS c0_queryId, "
+                "  Lookup.internalId AS c1_internalId, "
+                "  NULL AS c2_rowNumber, "
+                "  value AS c3_string1, "
+                "  NULL AS c4_string2, "
+                "  NULL AS c5_string3, "
+                "  type AS c6_int1, "
+                "  NULL AS c7_int2, "
+                "  NULL AS c8_big_int1, "
+                "  NULL AS c9_big_int2 "
+                "FROM Lookup "
+                "  INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
+                "  INNER JOIN Resources grandChildLevel ON childLevel.parentId = Lookup.internalId "
+                "  INNER JOIN Metadata ON Metadata.id = grandChildLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2))) + ") ";
       }
 
       // need children identifiers ?
@@ -772,78 +864,240 @@
           (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) ||
           (requestLevel == ResourceType_Series && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers()))
       {
-        sql = "SELECT Lookup.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>(requestLevel + 1), s.ColumnString(1));
-        }
+        sql += "UNION SELECT "
+               "  " TOSTRING(QUERY_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  NULL AS c2_rowNumber, "
+               "  childLevel.publicId AS c3_string1, "
+               "  NULL AS c4_string2, "
+               "  NULL AS c5_string3, "
+               "  NULL AS c6_int1, "
+               "  NULL AS c7_int2, "
+               "  NULL AS c8_big_int1, "
+               "  NULL AS c9_big_int2 "
+               "FROM Resources AS currentLevel "
+               "  INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
+               "  INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId ";
       }
 
       // need grandchildren identifiers ?
       if ((requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Series).IsRetrieveIdentifiers()) ||
           (requestLevel == ResourceType_Study && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers()))
       {
-        sql = "SELECT Lookup.internalId, grandChildLevel.publicId "
+        sql += "UNION SELECT "
+              "  " TOSTRING(QUERY_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
+              "  Lookup.internalId AS c1_internalId, "
+              "  NULL AS c2_rowNumber, "
+              "  grandChildLevel.publicId AS c3_string1, "
+              "  NULL AS c4_string2, "
+              "  NULL AS c5_string3, "
+              "  NULL AS c6_int1, "
+              "  NULL AS c7_int2, "
+              "  NULL AS c8_big_int1, "
+              "  NULL AS c9_big_int2 "
               "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>(requestLevel + 2), s.ColumnString(1));
-        }
       }
 
       // need grandgrandchildren identifiers ?
       if (requestLevel == ResourceType_Patient && request.GetChildrenSpecification(ResourceType_Instance).IsRetrieveIdentifiers())
       {
-        sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId "
+        sql += "UNION SELECT "
+              "  " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
+              "  Lookup.internalId AS c1_internalId, "
+              "  NULL AS c2_rowNumber, "
+              "  grandGrandChildLevel.publicId AS c3_string1, "
+              "  NULL AS c4_string2, "
+              "  NULL AS c5_string3, "
+              "  NULL AS c6_int1, "
+              "  NULL AS c7_int2, "
+              "  NULL AS c8_big_int1, "
+              "  NULL AS c9_big_int2 "
               "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 "
               "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId ";
-
-        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-        while (s.Step())
-        {
-          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          res.AddChildIdentifier(ResourceType_Instance, s.ColumnString(1));
-        }
       }
 
-      // need resource attachments ?
-      if (request.IsRetrieveAttachments())
+
+      sql += " ORDER BY c0_queryId, c2_rowNumber";  // this is really important to make sure that the Lookup query is the first one to provide results since we use it to create the responses element !
+
+      SQLite::Statement s(db_, SQLITE_FROM_HERE_DYNAMIC(sql), sql);
+      formatter.Bind(s);
+
+      while (s.Step())
       {
-        sql = "SELECT Lookup.internalId, fileType, uuid, uncompressedSize, compressedSize, compressionType, uncompressedMD5, compressedMD5 "
-              "FROM AttachedFiles "
-              "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId";
+        int queryId = s.ColumnInt(C0_QUERY_ID);
+        int64_t internalId = s.ColumnInt64(C1_INTERNAL_ID);
+
+        // LOG(INFO) << queryId << ": " << internalId;
+        // continue;
+
+        assert(queryId == QUERY_LOOKUP || response.HasResource(internalId)); // the QUERY_LOOKUP must be read first and must create the response before any other query tries to populate the fields
+
+        switch (queryId)
+        {
+          case QUERY_LOOKUP:
+            response.Add(new FindResponse::Resource(requestLevel, internalId, s.ColumnString(C3_STRING_1)));
+            break;
+
+          case QUERY_LABELS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddLabel(s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_ATTACHMENTS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C6_INT_1)), 
+                          s.ColumnInt64(C8_BIG_INT_1), s.ColumnString(C4_STRING_2),
+                          static_cast<CompressionType>(s.ColumnInt(C7_INT_2)),
+                          s.ColumnInt64(C9_BIG_INT_2), s.ColumnString(C5_STRING_3));
+            res.AddAttachment(file);
+          }; break;
+
+          case QUERY_MAIN_DICOM_TAGS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddStringDicomTag(requestLevel, 
+                                  static_cast<uint16_t>(s.ColumnInt(C6_INT_1)),
+                                  static_cast<uint16_t>(s.ColumnInt(C7_INT_2)),
+                                  s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_PARENT_MAIN_DICOM_TAGS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 1), 
+                                  static_cast<uint16_t>(s.ColumnInt(C6_INT_1)),
+                                  static_cast<uint16_t>(s.ColumnInt(C7_INT_2)),
+                                  s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_GRAND_PARENT_MAIN_DICOM_TAGS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddStringDicomTag(static_cast<ResourceType>(requestLevel - 2), 
+                                  static_cast<uint16_t>(s.ColumnInt(C6_INT_1)),
+                                  static_cast<uint16_t>(s.ColumnInt(C7_INT_2)),
+                                  s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_CHILDREN_MAIN_DICOM_TAGS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 1), 
+                                             DicomTag(static_cast<uint16_t>(s.ColumnInt(C6_INT_1)), static_cast<uint16_t>(s.ColumnInt(C7_INT_2))),
+                                             s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddChildrenMainDicomTagValue(static_cast<ResourceType>(requestLevel + 2), 
+                                             DicomTag(static_cast<uint16_t>(s.ColumnInt(C6_INT_1)), static_cast<uint16_t>(s.ColumnInt(C7_INT_2))),
+                                             s.ColumnString(C3_STRING_1));
+          }; break;
 
-        SQLite::Statement s(db_, SQLITE_FROM_HERE, sql);
-        while (s.Step())
-        {
-          FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          FileInfo file(s.ColumnString(2), static_cast<FileContentType>(s.ColumnInt(1)), 
-                        s.ColumnInt64(3), s.ColumnString(6),
-                        static_cast<CompressionType>(s.ColumnInt(5)),
-                        s.ColumnInt64(4), s.ColumnString(7));
-          res.AddAttachment(file);
+          case QUERY_METADATA:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddMetadata(static_cast<ResourceType>(requestLevel), 
+                            static_cast<MetadataType>(s.ColumnInt(C6_INT_1)),
+                            s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_PARENT_METADATA:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddMetadata(static_cast<ResourceType>(requestLevel - 1), 
+                            static_cast<MetadataType>(s.ColumnInt(C6_INT_1)),
+                            s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_GRAND_PARENT_METADATA:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddMetadata(static_cast<ResourceType>(requestLevel - 2), 
+                            static_cast<MetadataType>(s.ColumnInt(C6_INT_1)),
+                            s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_CHILDREN_METADATA:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 1), 
+                                         static_cast<MetadataType>(s.ColumnInt(C6_INT_1)),
+                                         s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_GRAND_CHILDREN_METADATA:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddChildrenMetadataValue(static_cast<ResourceType>(requestLevel + 2), 
+                                         static_cast<MetadataType>(s.ColumnInt(C6_INT_1)),
+                                         s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_PARENT_IDENTIFIER:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.SetParentIdentifier(s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_CHILDREN_IDENTIFIERS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1),
+                                   s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_GRAND_CHILDREN_IDENTIFIERS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2),
+                                   s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 3),
+                                   s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_ONE_INSTANCE_IDENTIFIER:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.SetOneInstancePublicId(s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_ONE_INSTANCE_METADATA:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            res.AddOneInstanceMetadata(static_cast<MetadataType>(s.ColumnInt(C6_INT_1)), s.ColumnString(C3_STRING_1));
+          }; break;
+
+          case QUERY_ONE_INSTANCE_ATTACHMENTS:
+          {
+            FindResponse::Resource& res = response.GetResourceByInternalId(internalId);
+            FileInfo file(s.ColumnString(C3_STRING_1), static_cast<FileContentType>(s.ColumnInt(C6_INT_1)), 
+                          s.ColumnInt64(C8_BIG_INT_1), s.ColumnString(C4_STRING_2),
+                          static_cast<CompressionType>(s.ColumnInt(C7_INT_2)),
+                          s.ColumnInt64(C9_BIG_INT_2), s.ColumnString(C5_STRING_3));
+            res.AddOneInstanceAttachment(file);
+          }; break;
+
+          default:
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
         }
-
       }
     }
 
-
-
     // From the "ICreateInstance" interface
     virtual void AttachChild(int64_t parent,
                              int64_t child) ORTHANC_OVERRIDE
--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Wed Sep 18 15:45:18 2024 +0200
+++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Thu Sep 19 17:50:20 2024 +0200
@@ -619,10 +619,13 @@
     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
+
 
     sql = ("SELECT " +
            strQueryLevel + ".publicId, " +
-           strQueryLevel + ".internalId" +
+           strQueryLevel + ".internalId, " +
+           ordering + 
            " FROM Resources AS " + strQueryLevel);