# HG changeset patch
# User Sebastien Jodogne <s.jodogne@gmail.com>
# Date 1680800991 -7200
# Node ID 8dedfd982b83064be1279395bb500afabfabcd66
# Parent  c4f0f80875647c9d69976dae1daa4043f9fd6e4d
implemented lookup for labels in postgresql

diff -r c4f0f8087564 -r 8dedfd982b83 Framework/Plugins/IndexBackend.cpp
--- a/Framework/Plugins/IndexBackend.cpp	Thu Apr 06 19:07:19 2023 +0200
+++ b/Framework/Plugins/IndexBackend.cpp	Thu Apr 06 19:09:51 2023 +0200
@@ -2070,17 +2070,12 @@
                                      uint32_t limit,
                                      bool requestSomeInstance)
   {
-    if (!withLabels.empty() ||
-        !withoutLabels.empty())
-    {
-      throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
-    }
-
     LookupFormatter formatter(manager.GetDialect());
 
     std::string sql;
-    Orthanc::ISqlLookupFormatter::Apply(sql, formatter, lookup,
-                                        Orthanc::Plugins::Convert(queryLevel), limit);
+    Orthanc::ISqlLookupFormatter::Apply(
+      sql, formatter, lookup, Orthanc::Plugins::Convert(queryLevel),
+      withLabels, withoutLabels, limit);
 
     if (requestSomeInstance)
     {
diff -r c4f0f8087564 -r 8dedfd982b83 Resources/Orthanc/Databases/ISqlLookupFormatter.cpp
--- a/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp	Thu Apr 06 19:07:19 2023 +0200
+++ b/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp	Thu Apr 06 19:09:51 2023 +0200
@@ -39,6 +39,7 @@
 #include "DatabaseConstraint.h"
 
 #include <boost/lexical_cast.hpp>
+#include <list>
 
 
 namespace Orthanc
@@ -268,12 +269,46 @@
                " AND " + tag + ".tagElement = " +
                boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
   }
+
+
+  static std::string Join(const std::list<std::string>& values,
+                          const std::string& prefix,
+                          const std::string& separator)
+  {
+    if (values.empty())
+    {
+      return "";
+    }
+    else
+    {
+      std::string s = prefix;
+
+      bool first = true;
+      for (std::list<std::string>::const_iterator it = values.begin(); it != values.end(); ++it)
+      {
+        if (first)
+        {
+          first = false;
+        }
+        else
+        {
+          s += separator;
+        }
+
+        s += *it;
+      }
+
+      return s;
+    }
+  }
   
 
   void ISqlLookupFormatter::Apply(std::string& sql,
                                   ISqlLookupFormatter& formatter,
                                   const std::vector<DatabaseConstraint>& lookup,
                                   ResourceType queryLevel,
+                                  const std::set<std::string>& withLabels,
+                                  const std::set<std::string>& withoutLabels,
                                   size_t limit)
   {
     assert(ResourceType_Patient < ResourceType_Study &&
@@ -346,9 +381,44 @@
               FormatLevel(static_cast<ResourceType>(level - 1)) + ".internalId=" +
               FormatLevel(static_cast<ResourceType>(level)) + ".parentId");
     }
-      
-    sql += (joins + " WHERE " + FormatLevel(queryLevel) + ".resourceType = " +
-            formatter.FormatResourceType(queryLevel) + comparisons);
+
+    std::list<std::string> where;
+
+    if (!withLabels.empty())
+    {
+      std::list<std::string> labels;
+      for (std::set<std::string>::const_iterator it = withLabels.begin(); it != withLabels.end(); ++it)
+      {
+        labels.push_back(formatter.GenerateParameter(*it));
+      }
+
+      where.push_back(boost::lexical_cast<std::string>(withLabels.size()) +
+                      " = (SELECT COUNT(1) FROM Labels WHERE internalId = " + FormatLevel(queryLevel) +
+                      ".internalId AND label IN (" + Join(labels, "", ", ") + "))");
+    }
+    
+    if (!withoutLabels.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/
+       **/
+      std::list<std::string> labels;
+      for (std::set<std::string>::const_iterator it = withoutLabels.begin(); it != withoutLabels.end(); ++it)
+      {
+        labels.push_back(formatter.GenerateParameter(*it));
+      }
+
+      where.push_back("NOT EXISTS (SELECT 1 FROM Labels WHERE internalId = " + FormatLevel(queryLevel) +
+                      ".internalId AND label IN (" + Join(labels, "", ", ") + "))");
+    }
+    
+    where.push_back(FormatLevel(queryLevel) + ".resourceType = " +
+                    formatter.FormatResourceType(queryLevel) + comparisons);
+
+    sql += joins + Join(where, " WHERE ", " AND ");
 
     if (limit != 0)
     {
diff -r c4f0f8087564 -r 8dedfd982b83 Resources/Orthanc/Databases/ISqlLookupFormatter.h
--- a/Resources/Orthanc/Databases/ISqlLookupFormatter.h	Thu Apr 06 19:07:19 2023 +0200
+++ b/Resources/Orthanc/Databases/ISqlLookupFormatter.h	Thu Apr 06 19:09:51 2023 +0200
@@ -60,6 +60,8 @@
                       ISqlLookupFormatter& formatter,
                       const std::vector<DatabaseConstraint>& lookup,
                       ResourceType queryLevel,
+                      const std::set<std::string>& withLabels,     // New in Orthanc 1.12.0
+                      const std::set<std::string>& withoutLabels,  // New in Orthanc 1.12.0
                       size_t limit);
   };
 }
diff -r c4f0f8087564 -r 8dedfd982b83 Resources/SyncOrthancFolder.py
--- a/Resources/SyncOrthancFolder.py	Thu Apr 06 19:07:19 2023 +0200
+++ b/Resources/SyncOrthancFolder.py	Thu Apr 06 19:09:51 2023 +0200
@@ -39,9 +39,10 @@
     ('default', 'OrthancServer/Plugins/Samples/Common/VersionScriptPlugins.map', 'Plugins'),
     ('default', 'OrthancServer/Sources/Search/DatabaseConstraint.cpp', 'Databases'),
     ('default', 'OrthancServer/Sources/Search/DatabaseConstraint.h', 'Databases'),
-    
-    ('default', 'OrthancServer/Sources/Search/ISqlLookupFormatter.cpp', 'Databases'),
-    ('default', 'OrthancServer/Sources/Search/ISqlLookupFormatter.h', 'Databases'),
+
+    # TODO - Replace "db-protobuf" by "default" once Orthanc 1.12.0 is released
+    ('db-protobuf', 'OrthancServer/Sources/Search/ISqlLookupFormatter.cpp', 'Databases'),
+    ('db-protobuf', 'OrthancServer/Sources/Search/ISqlLookupFormatter.h', 'Databases'),
 ]
 
 SDK = [