--- a/.hgignore	Sun Apr 16 14:46:17 2023 +0200
+++ b/.hgignore	Thu Jun 22 12:15:48 2023 +0200
@@ -3,5 +3,6 @@
 PostgreSQL/ThirdPartyDownloads/
 Odbc/ThirdPartyDownloads/
 MySQL/ThirdPartyDownloads/
+SQLite/ThirdPartyDownloads/
 .vscode/
 

--- a/Framework/Plugins/IndexBackend.cpp	Sun Apr 16 14:46:17 2023 +0200
+++ b/Framework/Plugins/IndexBackend.cpp	Thu Jun 22 12:15:48 2023 +0200
@@ -2072,41 +2072,63 @@
     LookupFormatter formatter(manager.GetDialect());
 
     std::string sql;
-    Orthanc::ISqlLookupFormatter::Apply(sql, formatter, lookup, Orthanc::Plugins::Convert(queryLevel),
-                                        labels, labelsConstraint, limit);
-
-    if (requestSomeInstance)
+    bool enableNewStudyCode = true;
+
+    if (enableNewStudyCode && queryLevel == OrthancPluginResourceType_Study)
     {
-      // Composite query to find some instance if requested
-      switch (queryLevel)
+      // separate path for the studies since it has been specifically optimized
+      Orthanc::ISqlLookupFormatter::ApplyExperimental(sql, formatter, lookup, Orthanc::Plugins::Convert(queryLevel), labels, labelsConstraint, limit);
+
+      if (requestSomeInstance)
+      {
+        sql = ("SELECT studies_series.studies_public_id, MIN(instances.publicId) AS instances_public_id "
+                "FROM (SELECT studies.publicId AS studies_public_id, MIN(series.internalId) AS series_internal_id "
+                      "FROM (" + sql + 
+                            ") AS studies "
+                            "INNER JOIN Resources series ON series.parentId = studies.internalId "
+                            "GROUP BY studies.publicId "
+                      ") AS studies_series "
+                "INNER JOIN Resources instances ON instances.parentId = studies_series.series_internal_id "
+                "GROUP BY studies_series.studies_public_id");
+      }
+    }
+    else
+    {
+      Orthanc::ISqlLookupFormatter::Apply(sql, formatter, lookup, Orthanc::Plugins::Convert(queryLevel),
+                                          labels, labelsConstraint, limit);      
+
+      if (requestSomeInstance)
       {
-        case OrthancPluginResourceType_Patient:
-          sql = ("SELECT patients.publicId, MIN(instances.publicId) FROM (" + sql + ") patients "
-                 "INNER JOIN Resources studies   ON studies.parentId   = patients.internalId "
-                 "INNER JOIN Resources series    ON series.parentId    = studies.internalId "
-                 "INNER JOIN Resources instances ON instances.parentId = series.internalId "
-                 "GROUP BY patients.publicId");
-          break;
-
-        case OrthancPluginResourceType_Study:
-          sql = ("SELECT studies.publicId, MIN(instances.publicId) FROM (" + sql + ") studies "
-                 "INNER JOIN Resources series    ON series.parentId    = studies.internalId "
-                 "INNER JOIN Resources instances ON instances.parentId = series.internalId "
-                 "GROUP BY studies.publicId");                 
-          break;
-
-        case OrthancPluginResourceType_Series:
-          sql = ("SELECT series.publicId, MIN(instances.publicId) FROM (" + sql + ") series "
-                 "INNER JOIN Resources instances ON instances.parentId = series.internalId "
-                 "GROUP BY series.publicId");
-          break;
-
-        case OrthancPluginResourceType_Instance:
-          sql = ("SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances");
-          break;
-
-        default:
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+        // Composite query to find some instance if requested
+        switch (queryLevel)
+        {
+          case OrthancPluginResourceType_Patient:
+            sql = ("SELECT patients.publicId, MIN(instances.publicId) FROM (" + sql + ") patients "
+                  "INNER JOIN Resources studies   ON studies.parentId   = patients.internalId "
+                  "INNER JOIN Resources series    ON series.parentId    = studies.internalId "
+                  "INNER JOIN Resources instances ON instances.parentId = series.internalId "
+                  "GROUP BY patients.publicId");
+            break;
+
+          case OrthancPluginResourceType_Study:
+            sql = ("SELECT studies.publicId, MIN(instances.publicId) FROM (" + sql + ") studies "
+                  "INNER JOIN Resources series    ON series.parentId    = studies.internalId "
+                  "INNER JOIN Resources instances ON instances.parentId = series.internalId "
+                  "GROUP BY studies.publicId");                 
+            break;
+          case OrthancPluginResourceType_Series:
+            sql = ("SELECT series.publicId, MIN(instances.publicId) FROM (" + sql + ") series "
+                  "INNER JOIN Resources instances ON instances.parentId = series.internalId "
+                  "GROUP BY series.publicId");
+            break;
+
+          case OrthancPluginResourceType_Instance:
+            sql = ("SELECT instances.publicId, instances.publicId FROM (" + sql + ") instances");
+            break;
+
+          default:
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+        }
       }
     }
 

--- a/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp	Sun Apr 16 14:46:17 2023 +0200
+++ b/Resources/Orthanc/Databases/ISqlLookupFormatter.cpp	Thu Jun 22 12:15:48 2023 +0200
@@ -40,6 +40,7 @@
 
 #include <boost/lexical_cast.hpp>
 #include <list>
+#include <Toolbox.h>
 
 
 namespace Orthanc
@@ -301,6 +302,180 @@
       return s;
     }
   }
+
+  static bool FormatComparisonExperimental(std::string& target,
+                                           ISqlLookupFormatter& formatter,
+                                           const DatabaseConstraint& constraint,
+                                           //size_t index,
+                                           bool escapeBrackets)
+  {
+    //std::string tag = "t" + boost::lexical_cast<std::string>(index);
+
+    std::string comparison;
+    std::string tagFilter = ("tagGroup = " + boost::lexical_cast<std::string>(constraint.GetTag().GetGroup())
+                              + " AND tagElement = " + boost::lexical_cast<std::string>(constraint.GetTag().GetElement()));
+
+    switch (constraint.GetConstraintType())
+    {
+      case ConstraintType_Equal:
+      case ConstraintType_SmallerOrEqual:
+      case ConstraintType_GreaterOrEqual:
+      {
+        std::string op;
+        switch (constraint.GetConstraintType())
+        {
+          case ConstraintType_Equal:
+            op = "=";
+            break;
+          
+          case ConstraintType_SmallerOrEqual:
+            op = "<=";
+            break;
+          
+          case ConstraintType_GreaterOrEqual:
+            op = ">=";
+            break;
+          
+          default:
+            throw OrthancException(ErrorCode_InternalError);
+        }
+
+        std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
+
+        if (constraint.IsCaseSensitive())
+        {
+          comparison = " AND value " + op + " " + parameter;
+        }
+        else
+        {
+          comparison = " AND lower(value) " + op + " lower(" + parameter + ")";
+        }
+
+        break;
+      }
+
+      case ConstraintType_List:
+      {
+        std::vector<std::string> comparisonValues;
+        for (size_t i = 0; i < constraint.GetValuesCount(); i++)
+        {
+          std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
+
+          if (constraint.IsCaseSensitive())
+          {
+            comparisonValues.push_back(parameter);
+          }
+          else
+          {
+            comparisonValues.push_back("lower(" + parameter + ")");
+          }
+        }
+
+        std::string values;
+        Toolbox::JoinStrings(values, comparisonValues, ", ");
+
+        if (constraint.IsCaseSensitive())
+        {
+          comparison = " AND value IN (" + values + ")";
+        }
+        else
+        {
+          comparison = " AND lower(value) IN (" + values + ")";
+        }
+            
+        break;
+      }
+
+      case ConstraintType_Wildcard:
+      {
+        const std::string value = constraint.GetSingleValue();
+
+        if (value == "*")
+        {
+          if (!constraint.IsMandatory())
+          {
+            // Universal constraint on an optional tag, ignore it
+            return false;
+          }
+        }
+        else
+        {
+          std::string escaped;
+          escaped.reserve(value.size());
+
+          for (size_t i = 0; i < value.size(); i++)
+          {
+            if (value[i] == '*')
+            {
+              escaped += "%";
+            }
+            else if (value[i] == '?')
+            {
+              escaped += "_";
+            }
+            else if (value[i] == '%')
+            {
+              escaped += "\\%";
+            }
+            else if (value[i] == '_')
+            {
+              escaped += "\\_";
+            }
+            else if (value[i] == '\\')
+            {
+              escaped += "\\\\";
+            }
+            else if (escapeBrackets && value[i] == '[')
+            {
+              escaped += "\\[";
+            }
+            else if (escapeBrackets && value[i] == ']')
+            {
+              escaped += "\\]";
+            }
+            else
+            {
+              escaped += value[i];
+            }               
+          }
+
+          std::string parameter = formatter.GenerateParameter(escaped);
+
+          if (constraint.IsCaseSensitive())
+          {
+            comparison = " AND value LIKE " + parameter + " " + formatter.FormatWildcardEscape();
+          }
+          else
+          {
+            comparison = " AND lower(value) LIKE lower(" + parameter + ") " + formatter.FormatWildcardEscape();
+          }
+        }
+          
+        break;
+      }
+
+      default:
+        return false;
+    }
+
+    if (constraint.IsMandatory())
+    {
+      target = tagFilter + comparison;
+    }
+    else if (comparison.empty())
+    {
+      target = tagFilter + " AND value IS NULL";
+    }
+    else
+    {
+      target = tagFilter + " AND value IS NULL OR " + comparison;
+    }
+
+    return true;
+  }
+
+
+
   
 
   void ISqlLookupFormatter::Apply(std::string& sql,
@@ -431,4 +606,133 @@
       sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
     }
   }
+
+
+  void ISqlLookupFormatter::ApplyExperimental(std::string& sql,
+                                              ISqlLookupFormatter& formatter,
+                                              const std::vector<DatabaseConstraint>& lookup,
+                                              ResourceType queryLevel,
+                                              const std::set<std::string>& labels,
+                                              LabelsConstraint labelsConstraint,
+                                              size_t limit
+                                              )
+  {
+    assert(ResourceType_Patient < ResourceType_Study &&
+           ResourceType_Study < ResourceType_Series &&
+           ResourceType_Series < ResourceType_Instance);
+    
+    ResourceType upperLevel = queryLevel;
+    ResourceType lowerLevel = queryLevel;
+
+    for (size_t i = 0; i < lookup.size(); i++)
+    {
+      ResourceType level = lookup[i].GetLevel();
+
+      if (level < upperLevel)
+      {
+        upperLevel = level;
+      }
+
+      if (level > lowerLevel)
+      {
+        lowerLevel = level;
+      }
+    }
+    
+    assert(upperLevel <= queryLevel &&
+           queryLevel <= lowerLevel);
+
+    const bool escapeBrackets = formatter.IsEscapeBrackets();
+    
+    std::vector<std::string> mainDicomTagsComparisons, dicomIdentifiersComparisons;
+
+    for (size_t i = 0; i < lookup.size(); i++)
+    {
+      std::string comparison;
+      
+      if (FormatComparisonExperimental(comparison, formatter, lookup[i], escapeBrackets))
+      {
+        if (!comparison.empty())
+        {
+          if (lookup[i].IsIdentifier())
+          {
+            dicomIdentifiersComparisons.push_back(comparison);
+          }
+          else
+          {
+            mainDicomTagsComparisons.push_back(comparison);
+          }
+        }
+      }
+    }
+
+    sql = ("SELECT publicId, internalId "
+           "FROM Resources "
+           "WHERE resourceType = " + formatter.FormatResourceType(queryLevel) 
+            + " ");
+
+    if (dicomIdentifiersComparisons.size() > 0)
+    {
+      std::string comparisons;
+      Toolbox::JoinStrings(comparisons, dicomIdentifiersComparisons, " AND ");
+      sql += (" AND internalId IN (SELECT id FROM DicomIdentifiers WHERE "
+              + comparisons + ") ");
+    }
+
+    if (mainDicomTagsComparisons.size() > 0)
+    {
+      std::string comparisons;
+      Toolbox::JoinStrings(comparisons, mainDicomTagsComparisons, " AND ");
+      sql += (" AND internalId IN (SELECT id FROM MainDicomTags WHERE "
+              + comparisons + ") ");
+    }
+
+    if (!labels.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> formattedLabels;
+      for (std::set<std::string>::const_iterator it = labels.begin(); it != labels.end(); ++it)
+      {
+        formattedLabels.push_back(formatter.GenerateParameter(*it));
+      }
+
+      std::string condition;
+      switch (labelsConstraint)
+      {
+        case LabelsConstraint_Any:
+          condition = "> 0";
+          break;
+          
+        case LabelsConstraint_All:
+          condition = "= " + boost::lexical_cast<std::string>(labels.size());
+          break;
+          
+        case LabelsConstraint_None:
+          condition = "= 0";
+          break;
+          
+        default:
+          throw OrthancException(ErrorCode_ParameterOutOfRange);
+      }
+      
+      sql += (" AND internalId IN (SELECT id"
+                                 " FROM (SELECT id, COUNT(1) AS labelsCount "
+                                        "FROM Labels "
+                                        "WHERE label IN (" + Join(formattedLabels, "", ", ") + ") GROUP BY id"
+                                        ") AS temp "
+                                 " WHERE labelsCount " + condition + ")");
+    }
+
+    if (limit != 0)
+    {
+      sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
+    }
+  }
+
 }

--- a/Resources/Orthanc/Databases/ISqlLookupFormatter.h	Sun Apr 16 14:46:17 2023 +0200
+++ b/Resources/Orthanc/Databases/ISqlLookupFormatter.h	Thu Jun 22 12:15:48 2023 +0200
@@ -70,5 +70,13 @@
                       const std::set<std::string>& labels,  // New in Orthanc 1.12.0
                       LabelsConstraint labelsConstraint,    // New in Orthanc 1.12.0
                       size_t limit);
+
+    static void ApplyExperimental(std::string& sql,
+                                  ISqlLookupFormatter& formatter,
+                                  const std::vector<DatabaseConstraint>& lookup,
+                                  ResourceType queryLevel,
+                                  const std::set<std::string>& labels,  // New in Orthanc 1.12.0
+                                  LabelsConstraint labelsConstraint,    // New in Orthanc 1.12.0
+                                  size_t limit);
   };
 }