changeset 5754:f75596b224e0 find-refactoring-clean

merged find-refactoring -> find-refactoring-clean (integration tests ok)
author Alain Mazy <am@orthanc.team>
date Wed, 04 Sep 2024 10:54:00 +0200
parents afd421225eb4 (current diff) fc591f166d53 (diff)
children 58c549b881ae
files OrthancServer/Resources/Graveyard/FindRefactoringForSQLite.cpp OrthancServer/Sources/Database/FindResponse.cpp OrthancServer/Sources/ServerContext.cpp OrthancServer/Sources/ServerContext.h
diffstat 5 files changed, 270 insertions(+), 72 deletions(-) [+]
line wrap: on
line diff
--- a/NEWS	Fri Aug 30 18:03:37 2024 +0200
+++ b/NEWS	Wed Sep 04 10:54:00 2024 +0200
@@ -2,7 +2,8 @@
 ===============================
 
 * TODO-FIND: complete the list of updated routes:
-  /studies?expand and sibbling routes now also return "Metadata" (if the DB implements 'extended-api-v1')
+  - /studies?expand and sibbling routes now also return "Metadata" (if the DB implements 'extended-api-v1')
+  - /studies?since=x&limit=0 and sibbling routes: limit=0 now means "no limit" instead of "no results"
 
 REST API
 --------
--- a/OrthancServer/Sources/Database/FindResponse.cpp	Fri Aug 30 18:03:37 2024 +0200
+++ b/OrthancServer/Sources/Database/FindResponse.cpp	Wed Sep 04 10:54:00 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);
   }
 
 
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Fri Aug 30 18:03:37 2024 +0200
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Wed Sep 04 10:54:00 2024 +0200
@@ -386,32 +386,34 @@
                              const FindRequest& request,
                              const Capabilities& capabilities) ORTHANC_OVERRIDE
     {
-      LookupFormatter formatter;
-
+      const ResourceType requestLevel = request.GetLevel();
       std::string sql;
-      LookupFormatter::Apply(sql, formatter, request);
 
-      sql = "CREATE TEMPORARY TABLE Lookup AS " + sql;
-    
       {
+        // clean previous lookup table
         SQLite::Statement s(db_, SQLITE_FROM_HERE, "DROP TABLE IF EXISTS Lookup");
         s.Run();
       }
 
       {
+        // extract the resource id of interest by executing the lookup
+        LookupFormatter formatter;
+        LookupFormatter::Apply(sql, formatter, request);
+
+        sql = "CREATE TEMPORARY TABLE Lookup AS " + sql;
+
         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)));
+          response.Add(new FindResponse::Resource(requestLevel, s.ColumnInt64(1), s.ColumnString(0)));
         }
       }
 
+      // need MainDicomTags from resource ?
       if (request.IsRetrieveMainDicomTags())
       {
         sql = "SELECT id, tagGroup, tagElement, value "
@@ -422,13 +424,92 @@
         while (s.Step())
         {
           FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          res.AddStringDicomTag(request.GetLevel(), 
+          res.AddStringDicomTag(requestLevel, 
+                                static_cast<uint16_t>(s.ColumnInt(1)),
+                                static_cast<uint16_t>(s.ColumnInt(2)),
+                                s.ColumnString(3));
+        }
+      }
+
+      // need MainDicomTags from parent ?
+      if (requestLevel > ResourceType_Patient && 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));
         }
       }
 
+      // need MainDicomTags from grandparent ?
+      if (requestLevel > ResourceType_Study && 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));
+        }
+      }
+
+      // 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));
+        }
+      }
+
+      // 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));
+        }
+      }
+
+      // need parent identifier ?
       if (request.IsRetrieveParentIdentifier())
       {
         sql = "SELECT currentLevel.internalId, parentLevel.publicId "
@@ -444,6 +525,7 @@
         }
       }
 
+      // need resource metadata ?
       if (request.IsRetrieveMetadata())
       {
         sql = "SELECT id, type, value "
@@ -454,15 +536,16 @@
         while (s.Step())
         {
           FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          res.AddMetadata(request.GetLevel(),
+          res.AddMetadata(requestLevel,
                           static_cast<MetadataType>(s.ColumnInt(1)),
                           s.ColumnString(2));
         }
       }
 
+      // need resource labels ?
       if (request.IsRetrieveLabels())
       {
-        sql = "SELECT id, label "
+        sql = "SELECT Lookup.internalId, label "
               "FROM Labels "
               "INNER JOIN Lookup ON Labels.id = Lookup.internalId";
 
@@ -474,9 +557,98 @@
         }
       }
 
-      if (request.GetLevel() <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 1)).IsRetrieveIdentifiers())
+      // need one instance identifier ?  TODO: it might be actually more interesting to retrieve directly the attachment ids ....
+      if (request.IsRetrieveOneInstanceIdentifier())
       {
-        sql = "SELECT currentLevel.internalId, childLevel.publicId "
+        if (requestLevel == 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 (requestLevel == 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 (requestLevel == 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);
+        }
+      }
+
+      // 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));
+        }
+      }
+
+      // 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));
+        }
+      }
+
+      // need children identifiers ?
+      if (requestLevel <= ResourceType_Series && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 1)).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 ";
@@ -485,13 +657,14 @@
         while (s.Step())
         {
           FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          res.AddChildIdentifier(static_cast<ResourceType>(request.GetLevel() + 1), s.ColumnString(1));
+          res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 1), s.ColumnString(1));
         }
       }
 
-      if (request.GetLevel() <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(request.GetLevel() + 2)).IsRetrieveIdentifiers())
+      // need grandchildren identifiers ?
+      if (requestLevel <= ResourceType_Study && request.GetChildrenSpecification(static_cast<ResourceType>(requestLevel + 2)).IsRetrieveIdentifiers())
       {
-        sql = "SELECT currentLevel.internalId, grandChildLevel.publicId "
+        sql = "SELECT Lookup.internalId, grandChildLevel.publicId "
               "FROM Resources AS currentLevel "
               "INNER JOIN Lookup ON currentLevel.internalId = Lookup.internalId "
               "INNER JOIN Resources childLevel ON currentLevel.internalId = childLevel.parentId "
@@ -501,9 +674,29 @@
         while (s.Step())
         {
           FindResponse::Resource& res = response.GetResourceByInternalId(s.ColumnInt64(0));
-          res.AddChildIdentifier(static_cast<ResourceType>(request.GetLevel() + 2), s.ColumnString(1));
+          res.AddChildIdentifier(static_cast<ResourceType>(requestLevel + 2), s.ColumnString(1));
         }
       }
+
+      // need resource attachments ?
+      if (request.IsRetrieveAttachments())
+      {
+        sql = "SELECT Lookup.internalId, 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);
+        }
+
+      }
     }
 
 
--- a/OrthancServer/Sources/ResourceFinder.cpp	Fri Aug 30 18:03:37 2024 +0200
+++ b/OrthancServer/Sources/ResourceFinder.cpp	Wed Sep 04 10:54:00 2024 +0200
@@ -758,7 +758,14 @@
     {
       requestedComputedTags_.insert(tag);
       hasRequestedTags_ = true;
-      request_.GetChildrenSpecification(ResourceType_Series).AddMainDicomTag(DICOM_TAG_MODALITY);
+      if (request_.GetLevel() < ResourceType_Series)
+      {
+        request_.GetChildrenSpecification(ResourceType_Series).AddMainDicomTag(DICOM_TAG_MODALITY);
+      }
+      else if (request_.GetLevel() == ResourceType_Instance)  // this happens in QIDO-RS when searching for instances without specifying a StudyInstanceUID -> all Study level tags must be included in the response
+      {
+        request_.GetParentSpecification(ResourceType_Series).SetRetrieveMainDicomTags(true);
+      }
     }
     else if (tag == DICOM_TAG_INSTANCE_AVAILABILITY)
     {
--- a/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Fri Aug 30 18:03:37 2024 +0200
+++ b/OrthancServer/Sources/Search/ISqlLookupFormatter.cpp	Wed Sep 04 10:54:00 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 = " +
@@ -755,8 +752,14 @@
 
     if (request.HasLimits())
     {
-      sql += " LIMIT " + boost::lexical_cast<std::string>(request.GetLimitsCount());
-      sql += " OFFSET " + boost::lexical_cast<std::string>(request.GetLimitsSince());
+      if (request.GetLimitsCount() > 0)
+      {
+        sql += " LIMIT " + boost::lexical_cast<std::string>(request.GetLimitsCount());
+      }
+      if (request.GetLimitsSince() > 0)
+      {
+        sql += " OFFSET " + boost::lexical_cast<std::string>(request.GetLimitsSince());
+      }
     }
 
   }