# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1725348881 -7200
# Node ID f39406a9eda465210be28e06da4216aed4285723
# Parent  3cee66f96e2e53f01d15b87e7e33ee4cfceb1950
ExtendedFind in SQLite continued (6 tests still failing)

diff -r 3cee66f96e2e -r f39406a9eda4 OrthancServer/Sources/Database/FindResponse.cpp
--- a/OrthancServer/Sources/Database/FindResponse.cpp	Mon Sep 02 17:21:38 2024 +0200
+++ b/OrthancServer/Sources/Database/FindResponse.cpp	Tue Sep 03 09:34:41 2024 +0200
@@ -160,14 +160,8 @@
 
   void FindResponse::ChildrenInformation::AddIdentifier(const std::string& identifier)
   {
-    if (identifiers_.find(identifier) == identifiers_.end())
-    {
-      identifiers_.insert(identifier);
-    }
-    else
-    {
-      throw OrthancException(ErrorCode_BadSequenceOfCalls);
-    }
+    // The same identifier can be added through AddChildIdentifier and through AddOneInstanceIdentifier
+    identifiers_.insert(identifier);
   }
 
 
diff -r 3cee66f96e2e -r f39406a9eda4 OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Mon Sep 02 17:21:38 2024 +0200
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Tue Sep 03 09:34:41 2024 +0200
@@ -429,6 +429,84 @@
         }
       }
 
+      // need MainDicomTags from parent
+      if (request.GetLevel() > ResourceType_Patient && request.GetParentSpecification(static_cast<ResourceType>(request.GetLevel() - 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>(request.GetLevel() - 1), 
+                                static_cast<uint16_t>(s.ColumnInt(1)),
+                                static_cast<uint16_t>(s.ColumnInt(2)),
+                                s.ColumnString(3));
+        }
+      }
+
+      // need MainDicomTags from grandparent
+      if (request.GetLevel() > ResourceType_Study && request.GetParentSpecification(static_cast<ResourceType>(request.GetLevel() - 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>(request.GetLevel() - 2), 
+                                static_cast<uint16_t>(s.ColumnInt(1)),
+                                static_cast<uint16_t>(s.ColumnInt(2)),
+                                s.ColumnString(3));
+        }
+      }
+
+      // need MainDicomTags from children
+      if (request.GetLevel() <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 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>(request.GetLevel() + 1), 
+                                DicomTag(static_cast<uint16_t>(s.ColumnInt(1)),
+                                         static_cast<uint16_t>(s.ColumnInt(2))),
+                                s.ColumnString(3));
+        }
+      }
+
+      // need MainDicomTags from grandchildren
+      if (request.GetLevel() <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 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>(request.GetLevel() + 2), 
+                                DicomTag(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 "
@@ -474,6 +552,56 @@
         }
       }
 
+      if (request.IsRetrieveOneInstanceIdentifier())
+      {
+        if (request.GetLevel() == ResourceType_Series)
+        {
+          sql = "SELECT Lookup.internalId, childLevel.publicId "
+                "FROM Resources AS childLevel "
+                "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId ";
+
+          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));
+          }
+        }
+        else if (request.GetLevel() == ResourceType_Study)
+        {
+          sql = "SELECT Lookup.internalId, grandChildLevel.publicId "
+                "FROM Resources AS grandChildLevel "
+                "INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
+                "INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId ";
+
+          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));
+          }
+        }
+        else if (request.GetLevel() == ResourceType_Patient)
+        {
+          sql = "SELECT Lookup.internalId, grandGrandChildLevel.publicId "
+                "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 ";
+
+          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));
+          }
+        }
+        else
+        {
+          throw OrthancException(ErrorCode_InternalError);
+        }
+      }
+
       if (request.GetLevel() <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 1)).IsRetrieveIdentifiers())
       {
         sql = "SELECT currentLevel.internalId, childLevel.publicId "
@@ -504,6 +632,25 @@
           res.AddChildIdentifier(static_cast<ResourceType>(request.GetLevel() + 2), s.ColumnString(1));
         }
       }
+
+      if (request.IsRetrieveAttachments())
+      {
+        sql = "SELECT id, fileType, uuid, uncompressedSize, compressedSize, compressionType, uncompressedMD5, compressedMD5 "
+              "FROM AttachedFiles "
+              "INNER JOIN Lookup ON AttachedFiles.id = Lookup.internalId";
+
+        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);
+        }
+
+      }
     }
 
 
diff -r 3cee66f96e2e -r f39406a9eda4 OrthancServer/Sources/Search/ISqlLookupFormatter.cpp
--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Mon Sep 02 17:21:38 2024 +0200
+++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Tue Sep 03 09:34:41 2024 +0200
@@ -625,40 +625,42 @@
     const bool escapeBrackets = formatter.IsEscapeBrackets();
     ResourceType queryLevel = request.GetLevel();
     const std::string& strQueryLevel = FormatLevel(queryLevel);
-    
+
+    ResourceType lowerLevel, upperLevel;
+    GetLookupLevels(lowerLevel, upperLevel, queryLevel, request.GetDicomTagConstraints());
+
+    assert(upperLevel <= queryLevel &&
+           queryLevel <= lowerLevel);
+
+
+    sql = ("SELECT " +
+           strQueryLevel + ".publicId, " +
+           strQueryLevel + ".internalId" +
+           " FROM Resources AS " + strQueryLevel);
+
+
     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)
+    if (request.GetOrthancIdentifiers().IsDefined() && request.GetOrthancIdentifiers().DetectLevel() <= queryLevel)
     {
       // 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);
+      ResourceType topParentLevel = request.GetOrthancIdentifiers().DetectLevel();
+      const std::string& strTopParentLevel = FormatLevel(topParentLevel);
+
+      comparisons = " AND " + strTopParentLevel + ".publicId = " + formatter.GenerateParameter(request.GetOrthancIdentifiers().GetLevel(topParentLevel));
 
-      comparisons = " AND " + strParentLevel + ".publicId = " + formatter.GenerateParameter(request.GetOrthancIdentifiers().GetLevel(parentLevel));
-      joins += (" INNER JOIN Resources " +
-              strParentLevel + " ON " +
-              strQueryLevel + ".parentId=" +
-              strParentLevel + ".internalId");
+      for (int level = queryLevel; level > topParentLevel; level--)
+      {
+        sql += (" INNER JOIN Resources " +
+                FormatLevel(static_cast<ResourceType>(level - 1)) + " ON " +
+                FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
+                FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
+      }
     }
-    // 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;
@@ -686,26 +688,21 @@
       }
     }
 
-    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 >= 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");
-    // }
+    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 = " +