# HG changeset patch
# User Sebastien Jodogne <s.jodogne@gmail.com>
# Date 1734363372 -3600
# Node ID 3d853c8f5a9e88a935533ddda36b6579710f186e
# Parent  987460ae14c93eccc29a2ebb5a1ce1ea7ab0a282# Parent  d4c373c74c0b11822efb7d787b57e05537731544
integration find-refactoring->mainline

diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/DatabaseManager.cpp
--- a/Framework/Common/DatabaseManager.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Common/DatabaseManager.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -79,9 +79,9 @@
   }
 
 
-  IPrecompiledStatement* DatabaseManager::LookupCachedStatement(const StatementLocation& location) const
+  IPrecompiledStatement* DatabaseManager::LookupCachedStatement(const StatementId& statementId) const
   {
-    CachedStatements::const_iterator found = cachedStatements_.find(location);
+    CachedStatements::const_iterator found = cachedStatements_.find(statementId);
 
     if (found == cachedStatements_.end())
     {
@@ -95,10 +95,10 @@
   }
 
     
-  IPrecompiledStatement& DatabaseManager::CacheStatement(const StatementLocation& location,
+  IPrecompiledStatement& DatabaseManager::CacheStatement(const StatementId& statementId,
                                                          const Query& query)
   {
-    LOG(TRACE) << "Caching statement from " << location.GetFile() << ":" << location.GetLine();
+    LOG(TRACE) << "Caching statement from " << statementId.GetFile() << ":" << statementId.GetLine() << "" << statementId.GetDynamicStatement();
       
     std::unique_ptr<IPrecompiledStatement> statement(GetDatabase().Compile(query));
       
@@ -108,8 +108,8 @@
       throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
     }
 
-    assert(cachedStatements_.find(location) == cachedStatements_.end());
-    cachedStatements_[location] = statement.release();
+    assert(cachedStatements_.find(statementId) == cachedStatements_.end());
+    cachedStatements_[statementId] = statement.release();
 
     return *tmp;
   }
@@ -551,13 +551,13 @@
   }
   
   
-  DatabaseManager::CachedStatement::CachedStatement(const StatementLocation& location,
+  DatabaseManager::CachedStatement::CachedStatement(const StatementId& statementId,
                                                     DatabaseManager& manager,
                                                     const std::string& sql) :
     StatementBase(manager),
-    location_(location)
+    statementId_(statementId)
   {
-    statement_ = GetManager().LookupCachedStatement(location_);
+    statement_ = GetManager().LookupCachedStatement(statementId_);
 
     if (statement_ == NULL)
     {
@@ -566,7 +566,7 @@
     else
     {
       LOG(TRACE) << "Reusing cached statement from "
-                 << location_.GetFile() << ":" << location_.GetLine();
+                 << statementId_.GetFile() << ":" << statementId_.GetLine() << " " << statementId_.GetDynamicStatement();
     }
   }
 
@@ -580,7 +580,7 @@
       {
         // Register the newly-created statement
         assert(statement_ == NULL);
-        statement_ = &GetManager().CacheStatement(location_, *query);
+        statement_ = &GetManager().CacheStatement(statementId_, *query);
       }
         
       assert(statement_ != NULL);
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/DatabaseManager.h
--- a/Framework/Common/DatabaseManager.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Common/DatabaseManager.h	Mon Dec 16 16:36:12 2024 +0100
@@ -24,7 +24,7 @@
 #pragma once
 
 #include "IDatabaseFactory.h"
-#include "StatementLocation.h"
+#include "StatementId.h"
 
 #include <Compatibility.h>  // For std::unique_ptr<>
 #include <Enumerations.h>
@@ -49,7 +49,7 @@
   class DatabaseManager : public boost::noncopyable
   {
   private:
-    typedef std::map<StatementLocation, IPrecompiledStatement*>  CachedStatements;
+    typedef std::map<StatementId, IPrecompiledStatement*>  CachedStatements;
 
     std::unique_ptr<IDatabaseFactory>  factory_;
     std::unique_ptr<IDatabase>     database_;
@@ -59,9 +59,9 @@
 
     void CloseIfUnavailable(Orthanc::ErrorCode e);
 
-    IPrecompiledStatement* LookupCachedStatement(const StatementLocation& location) const;
+    IPrecompiledStatement* LookupCachedStatement(const StatementId& statementId) const;
 
-    IPrecompiledStatement& CacheStatement(const StatementLocation& location,
+    IPrecompiledStatement& CacheStatement(const StatementId& statementId,
                                           const Query& query);
 
     ITransaction& GetTransaction();
@@ -194,6 +194,8 @@
       {
         IResult::Print(stream, GetResult());
       }
+
+      virtual void Execute(const Dictionary& parameters) = 0;
     };
 
 
@@ -207,11 +209,11 @@
     class CachedStatement : public StatementBase
     {
     private:
-      StatementLocation       location_;
+      StatementId             statementId_;
       IPrecompiledStatement*  statement_;
 
     public:
-      CachedStatement(const StatementLocation& location,
+      CachedStatement(const StatementId& statementId,
                       DatabaseManager& manager,
                       const std::string& sql);
 
@@ -221,7 +223,7 @@
         Execute(parameters);
       }
 
-      void Execute(const Dictionary& parameters);
+      virtual void Execute(const Dictionary& parameters);
 
       void ExecuteWithoutResult()
       {
@@ -253,7 +255,7 @@
         Execute(parameters);
       }
 
-      void Execute(const Dictionary& parameters);
+      virtual void Execute(const Dictionary& parameters);
 
       void ExecuteWithoutResult()
       {
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/DatabasesEnumerations.h
--- a/Framework/Common/DatabasesEnumerations.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Common/DatabasesEnumerations.h	Mon Dec 16 16:36:12 2024 +0100
@@ -31,6 +31,7 @@
     ValueType_BinaryString,
     ValueType_InputFile,
     ValueType_Integer64,
+    ValueType_Integer32,
     ValueType_Null,
     ValueType_ResultFile,
     ValueType_Utf8String
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/Dictionary.cpp
--- a/Framework/Common/Dictionary.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Common/Dictionary.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -25,6 +25,7 @@
 
 #include "BinaryStringValue.h"
 #include "InputFileValue.h"
+#include "Integer32Value.h"
 #include "Integer64Value.h"
 #include "NullValue.h"
 #include "Utf8StringValue.h"
@@ -126,7 +127,13 @@
     SetValue(key, new Integer64Value(value));
   }
 
-  
+
+  void Dictionary::SetInteger32Value(const std::string& key,
+                                     int32_t value)
+  {
+    SetValue(key, new Integer32Value(value));
+  }
+
   void Dictionary::SetNullValue(const std::string& key)
   {
     SetValue(key, new NullValue);
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/Dictionary.h
--- a/Framework/Common/Dictionary.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Common/Dictionary.h	Mon Dec 16 16:36:12 2024 +0100
@@ -68,6 +68,9 @@
     void SetIntegerValue(const std::string& key,
                          int64_t value);
 
+    void SetInteger32Value(const std::string& key,
+                           int32_t value);
+
     void SetNullValue(const std::string& key);
 
     const IValue& GetValue(const std::string& key) const;
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/ITransaction.h
--- a/Framework/Common/ITransaction.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Common/ITransaction.h	Mon Dec 16 16:36:12 2024 +0100
@@ -50,6 +50,8 @@
 
     virtual bool DoesTableExist(const std::string& name) = 0;
 
+    virtual bool DoesIndexExist(const std::string& name) = 0;
+
     virtual bool DoesTriggerExist(const std::string& name) = 0;  // Only for MySQL
 
     virtual void ExecuteMultiLines(const std::string& query) = 0;
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/Integer32Value.cpp
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/Integer32Value.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,55 @@
+/**
+ * Orthanc - A Lightweight, RESTful DICOM Store
+ * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
+ * Department, University Hospital of Liege, Belgium
+ * Copyright (C) 2017-2023 Osimis S.A., Belgium
+ * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
+ * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
+ *
+ * This program is free software: you can redistribute it and/or
+ * modify it under the terms of the GNU Affero General Public License
+ * as published by the Free Software Foundation, either version 3 of
+ * the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Affero General Public License for more details.
+ * 
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ **/
+
+
+#include "Integer32Value.h"
+
+#include "BinaryStringValue.h"
+#include "NullValue.h"
+#include "Utf8StringValue.h"
+
+#include <OrthancException.h>
+
+#include <boost/lexical_cast.hpp>
+
+namespace OrthancDatabases
+{
+  IValue* Integer32Value::Convert(ValueType target) const
+  {
+    std::string s = boost::lexical_cast<std::string>(value_);
+            
+    switch (target)
+    {
+      case ValueType_Null:
+        return new NullValue;
+
+      case ValueType_BinaryString:
+        return new BinaryStringValue(s);
+
+      case ValueType_Utf8String:
+        return new Utf8StringValue(s);
+
+      default:
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+    }
+  }
+}
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/Integer32Value.h
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/Integer32Value.h	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,57 @@
+/**
+ * Orthanc - A Lightweight, RESTful DICOM Store
+ * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
+ * Department, University Hospital of Liege, Belgium
+ * Copyright (C) 2017-2023 Osimis S.A., Belgium
+ * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
+ * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
+ *
+ * This program is free software: you can redistribute it and/or
+ * modify it under the terms of the GNU Affero General Public License
+ * as published by the Free Software Foundation, either version 3 of
+ * the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Affero General Public License for more details.
+ * 
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ **/
+
+
+#pragma once
+
+#include "IValue.h"
+
+#include <Compatibility.h>
+
+#include <stdint.h>
+
+namespace OrthancDatabases
+{
+  class Integer32Value : public IValue
+  {
+  private:
+    int32_t  value_;
+
+  public:
+    explicit Integer32Value(int32_t value) :
+    value_(value)
+    {
+    }
+
+    int32_t GetValue() const
+    {
+      return value_;
+    }
+
+    virtual ValueType GetType() const ORTHANC_OVERRIDE
+    {
+      return ValueType_Integer32;
+    }
+    
+    virtual IValue* Convert(ValueType target) const ORTHANC_OVERRIDE;
+  };
+}
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/Query.cpp
--- a/Framework/Common/Query.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Common/Query.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -76,7 +76,7 @@
       parameter = parameter.substr(2, parameter.size() - 3);
 
       tokens_.push_back(new Token(true, parameter));
-      parameters_[parameter] = ValueType_Null;
+      parameters_[parameter] = ValueType_Utf8String;
 
       last = it->second;
 
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/StatementId.cpp
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/StatementId.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,56 @@
+/**
+ * Orthanc - A Lightweight, RESTful DICOM Store
+ * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
+ * Department, University Hospital of Liege, Belgium
+ * Copyright (C) 2017-2023 Osimis S.A., Belgium
+ * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
+ * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
+ *
+ * This program is free software: you can redistribute it and/or
+ * modify it under the terms of the GNU Affero General Public License
+ * as published by the Free Software Foundation, either version 3 of
+ * the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Affero General Public License for more details.
+ * 
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ **/
+
+
+#include "StatementId.h"
+
+#include <string.h>
+#include <Toolbox.h>
+
+#include <boost/lexical_cast.hpp>
+
+namespace OrthancDatabases
+{
+  bool StatementId::operator< (const StatementId& other) const
+  {
+    return hash_ < other.hash_;
+  }
+
+  StatementId::StatementId(const char* file,
+                           int line) :
+      file_(file),
+      line_(line)
+  {
+    Orthanc::Toolbox::ComputeMD5(hash_, file_ + boost::lexical_cast<std::string>(line_));
+  }
+
+  StatementId::StatementId(const char* file,
+                           int line,
+                           const std::string& statement) :
+      file_(file),
+      line_(line),
+      statement_(statement)
+  {
+    Orthanc::Toolbox::ComputeMD5(hash_, file_ + boost::lexical_cast<std::string>(line_) + statement_);
+  }
+
+}
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/StatementId.h
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/StatementId.h	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,69 @@
+/**
+ * Orthanc - A Lightweight, RESTful DICOM Store
+ * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
+ * Department, University Hospital of Liege, Belgium
+ * Copyright (C) 2017-2023 Osimis S.A., Belgium
+ * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
+ * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
+ *
+ * This program is free software: you can redistribute it and/or
+ * modify it under the terms of the GNU Affero General Public License
+ * as published by the Free Software Foundation, either version 3 of
+ * the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Affero General Public License for more details.
+ * 
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ **/
+
+
+#pragma once
+
+#include <string>
+
+#define STATEMENT_FROM_HERE  ::OrthancDatabases::StatementId(__FILE__, __LINE__)
+#define STATEMENT_FROM_HERE_DYNAMIC(sql)  ::OrthancDatabases::StatementId(__FILE__, __LINE__, sql)
+
+
+namespace OrthancDatabases
+{
+  class StatementId
+  {
+  private:
+    const char* file_;
+    int line_;
+    std::string statement_;
+    std::string hash_;
+
+    StatementId(); // Forbidden
+    
+  public:
+    StatementId(const char* file,
+                int line);
+
+    StatementId(const char* file,
+                int line,
+                const std::string& statement);
+
+    const char* GetFile() const
+    {
+      return file_;
+    }
+
+    int GetLine() const
+    {
+      return line_;
+    }
+    
+    const std::string& GetDynamicStatement() const
+    {
+      return statement_;
+    }
+
+    bool operator< (const StatementId& other) const;
+  };
+}
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/StatementLocation.cpp
--- a/Framework/Common/StatementLocation.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,41 +0,0 @@
-/**
- * Orthanc - A Lightweight, RESTful DICOM Store
- * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
- * Department, University Hospital of Liege, Belgium
- * Copyright (C) 2017-2023 Osimis S.A., Belgium
- * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
- * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
- *
- * This program is free software: you can redistribute it and/or
- * modify it under the terms of the GNU Affero General Public License
- * as published by the Free Software Foundation, either version 3 of
- * the License, or (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful, but
- * WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
- * Affero General Public License for more details.
- * 
- * You should have received a copy of the GNU Affero General Public License
- * along with this program. If not, see <http://www.gnu.org/licenses/>.
- **/
-
-
-#include "StatementLocation.h"
-
-#include <string.h>
-
-namespace OrthancDatabases
-{
-  bool StatementLocation::operator< (const StatementLocation& other) const
-  {
-    if (line_ != other.line_)
-    {
-      return line_ < other.line_;
-    }
-    else
-    {
-      return strcmp(file_, other.file_) < 0;
-    }
-  }
-}
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Common/StatementLocation.h
--- a/Framework/Common/StatementLocation.h	Mon Sep 09 15:21:14 2024 +0200
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,59 +0,0 @@
-/**
- * Orthanc - A Lightweight, RESTful DICOM Store
- * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
- * Department, University Hospital of Liege, Belgium
- * Copyright (C) 2017-2023 Osimis S.A., Belgium
- * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
- * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
- *
- * This program is free software: you can redistribute it and/or
- * modify it under the terms of the GNU Affero General Public License
- * as published by the Free Software Foundation, either version 3 of
- * the License, or (at your option) any later version.
- *
- * This program is distributed in the hope that it will be useful, but
- * WITHOUT ANY WARRANTY; without even the implied warranty of
- * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
- * Affero General Public License for more details.
- * 
- * You should have received a copy of the GNU Affero General Public License
- * along with this program. If not, see <http://www.gnu.org/licenses/>.
- **/
-
-
-#pragma once
-
-#define STATEMENT_FROM_HERE  ::OrthancDatabases::StatementLocation(__FILE__, __LINE__)
-
-
-namespace OrthancDatabases
-{
-  class StatementLocation
-  {
-  private:
-    const char* file_;
-    int line_;
-    
-    StatementLocation(); // Forbidden
-    
-  public:
-    StatementLocation(const char* file,
-                      int line) :
-      file_(file),
-      line_(line)
-    {
-    }
-
-    const char* GetFile() const
-    {
-      return file_;
-    }
-
-    int GetLine() const
-    {
-      return line_;
-    }
-    
-    bool operator< (const StatementLocation& other) const;
-  };
-}
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/MySQL/MySQLDatabase.cpp
--- a/Framework/MySQL/MySQLDatabase.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/MySQL/MySQLDatabase.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -570,6 +570,11 @@
         throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed");
       }
 
+      virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
+      {
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed");
+      }
+
       virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
       {
         throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed");
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/MySQL/MySQLStatement.cpp
--- a/Framework/MySQL/MySQLStatement.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/MySQL/MySQLStatement.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -152,6 +152,10 @@
           buffer_.resize(8);
           break;
 
+        case MYSQL_TYPE_NULL:
+          orthancType_ = ValueType_Null;
+          break;
+
         case MYSQL_TYPE_STRING:
         case MYSQL_TYPE_VAR_STRING:
         case MYSQL_TYPE_BLOB:
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/MySQL/MySQLTransaction.h
--- a/Framework/MySQL/MySQLTransaction.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/MySQL/MySQLTransaction.h	Mon Dec 16 16:36:12 2024 +0100
@@ -64,6 +64,11 @@
       return db_.DoesTableExist(*this, name);
     }
 
+    virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return false;
+    }
+
     virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
     {
       return db_.DoesTriggerExist(*this, name);
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Odbc/OdbcDatabase.cpp
--- a/Framework/Odbc/OdbcDatabase.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Odbc/OdbcDatabase.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -84,6 +84,11 @@
       return db_.DoesTableExist(name.c_str());
     }
 
+    virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return false;
+    }
+
     virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
     {
       return false;
@@ -188,6 +193,11 @@
       return db_.DoesTableExist(name.c_str());
     }
 
+    virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return false;  // note implemented yet
+    }
+
     virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
     {
       return false;
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/DatabaseBackendAdapterV3.cpp
--- a/Framework/Plugins/DatabaseBackendAdapterV3.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/DatabaseBackendAdapterV3.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -1814,7 +1814,8 @@
     
   void DatabaseBackendAdapterV3::Register(IndexBackend* backend,
                                           size_t countConnections,
-                                          unsigned int maxDatabaseRetries)
+                                          unsigned int maxDatabaseRetries,
+                                          unsigned int housekeepingDelaySeconds)
   {
     std::unique_ptr<IndexBackend> protection(backend);
     
@@ -1905,7 +1906,7 @@
  
     if (OrthancPluginRegisterDatabaseBackendV3(
           context, &params, sizeof(params), maxDatabaseRetries,
-          new IndexConnectionsPool(protection.release(), countConnections)) != OrthancPluginErrorCode_Success)
+          new IndexConnectionsPool(protection.release(), countConnections, housekeepingDelaySeconds)) != OrthancPluginErrorCode_Success)
     {
       delete backend;
       throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "Unable to register the database backend");
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/DatabaseBackendAdapterV3.h
--- a/Framework/Plugins/DatabaseBackendAdapterV3.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/DatabaseBackendAdapterV3.h	Mon Dec 16 16:36:12 2024 +0100
@@ -60,7 +60,8 @@
 
     static void Register(IndexBackend* backend,
                          size_t countConnections,
-                         unsigned int maxDatabaseRetries);
+                         unsigned int maxDatabaseRetries,
+                         unsigned int housekeepingDelaySeconds);
 
     static void Finalize();
   };
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/DatabaseBackendAdapterV4.cpp
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -27,6 +27,7 @@
 #  if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
 
 #include "IndexConnectionsPool.h"
+#include "MessagesToolbox.h"
 
 #include <OrthancDatabasePlugin.pb.h>  // Include protobuf messages
 
@@ -91,28 +92,6 @@
   }
 
 
-  static Orthanc::ResourceType Convert2(Orthanc::DatabasePluginMessages::ResourceType resourceType)
-  {
-    switch (resourceType)
-    {
-      case Orthanc::DatabasePluginMessages::RESOURCE_PATIENT:
-        return Orthanc::ResourceType_Patient;
-
-      case Orthanc::DatabasePluginMessages::RESOURCE_STUDY:
-        return Orthanc::ResourceType_Study;
-
-      case Orthanc::DatabasePluginMessages::RESOURCE_SERIES:
-        return Orthanc::ResourceType_Series;
-
-      case Orthanc::DatabasePluginMessages::RESOURCE_INSTANCE:
-        return Orthanc::ResourceType_Instance;
-
-      default:
-        throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
-    }
-  }
-
-
   class Output : public IDatabaseBackendOutput
   {
   private:
@@ -126,6 +105,10 @@
     Orthanc::DatabasePluginMessages::LookupAttachment::Response*         lookupAttachment_;
     Orthanc::DatabasePluginMessages::LookupResources::Response*          lookupResources_;
 
+#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1
+    Orthanc::DatabasePluginMessages::GetChangesExtended::Response*       getChangesExtended_;
+#endif
+
     void Clear()
     {
       deleteAttachment_ = NULL;
@@ -137,6 +120,10 @@
       getMainDicomTags_ = NULL;
       lookupAttachment_ = NULL;
       lookupResources_ = NULL;
+
+#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1
+      getChangesExtended_ = NULL;
+#endif
     }
     
   public:
@@ -157,7 +144,15 @@
       Clear();
       getChanges_ = &getChanges;
     }
-    
+
+#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1
+    Output(Orthanc::DatabasePluginMessages::GetChangesExtended::Response& getChangesExtended)
+    {
+      Clear();
+      getChangesExtended_ = &getChangesExtended;
+    }
+#endif
+
     Output(Orthanc::DatabasePluginMessages::GetExportedResources::Response& getExportedResources)
     {
       Clear();
@@ -310,6 +305,12 @@
       {
         change = getChanges_->add_changes();
       }
+#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1
+      else if (getChangesExtended_ != NULL)
+      {
+        change = getChangesExtended_->add_changes();
+      }
+#endif
       else if (getLastChange_ != NULL)
       {
         if (getLastChange_->found())
@@ -439,6 +440,11 @@
         response.mutable_get_system_information()->set_has_measure_latency(accessor.GetBackend().HasMeasureLatency());
 #endif
 
+#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1
+        response.mutable_get_system_information()->set_supports_find(accessor.GetBackend().HasFindSupport());
+        response.mutable_get_system_information()->set_has_extended_changes(accessor.GetBackend().HasExtendedChanges());
+#endif
+
         break;
       }
 
@@ -455,7 +461,7 @@
         for (int i = 0; i < request.open().identifier_tags().size(); i++)
         {
           const Orthanc::DatabasePluginMessages::Open_Request_IdentifierTag& tag = request.open().identifier_tags(i);
-          identifierTags.push_back(IdentifierTag(Convert2(tag.level()),
+          identifierTags.push_back(IdentifierTag(MessagesToolbox::Convert(tag.level()),
                                                  Orthanc::DicomTag(tag.group(), tag.element()),
                                                  tag.name()));
         }
@@ -782,7 +788,25 @@
         response.mutable_get_changes()->set_done(done);
         break;
       }
-      
+#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1
+      case Orthanc::DatabasePluginMessages::OPERATION_GET_CHANGES_EXTENDED:
+      {
+        Output output(*response.mutable_get_changes_extended());
+
+        bool done;
+        std::set<uint32_t> changeTypes;
+        for (int i = 0; i < request.get_changes_extended().change_type_size(); ++i)
+        {
+          changeTypes.insert(request.get_changes_extended().change_type(i));
+        }
+
+        backend.GetChangesExtended(output, done, manager, request.get_changes_extended().since(), request.get_changes_extended().to(), changeTypes, request.get_changes_extended().limit());
+
+        response.mutable_get_changes_extended()->set_done(done);
+        break;
+      }
+#endif
+
       case Orthanc::DatabasePluginMessages::OPERATION_GET_CHILDREN_INTERNAL_ID:
       {
         std::list<int64_t>  values;
@@ -1297,6 +1321,20 @@
         break;
       }
       
+#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1
+      case Orthanc::DatabasePluginMessages::OPERATION_FIND:
+      {
+        backend.ExecuteFind(response, manager, request.find());
+        break;
+      }
+
+      case Orthanc::DatabasePluginMessages::OPERATION_COUNT_RESOURCES:
+      {
+        backend.ExecuteCount(response, manager, request.find());
+        break;
+      }
+#endif
+
       default:
         LOG(ERROR) << "Not implemented transaction operation from protobuf: " << request.operation();
         throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
@@ -1416,9 +1454,10 @@
   
   void DatabaseBackendAdapterV4::Register(IndexBackend* backend,
                                           size_t countConnections,
-                                          unsigned int maxDatabaseRetries)
+                                          unsigned int maxDatabaseRetries,
+                                          unsigned int housekeepingDelaySeconds)
   {
-    std::unique_ptr<IndexConnectionsPool> pool(new IndexConnectionsPool(backend, countConnections));
+    std::unique_ptr<IndexConnectionsPool> pool(new IndexConnectionsPool(backend, countConnections, housekeepingDelaySeconds));
     
     if (isBackendInUse_)
     {
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/DatabaseBackendAdapterV4.h
--- a/Framework/Plugins/DatabaseBackendAdapterV4.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/DatabaseBackendAdapterV4.h	Mon Dec 16 16:36:12 2024 +0100
@@ -50,7 +50,8 @@
   public:
     static void Register(IndexBackend* backend,
                          size_t countConnections,
-                         unsigned int maxDatabaseRetries);
+                         unsigned int maxDatabaseRetries,
+                         unsigned int housekeepingDelaySeconds);
 
     static void Finalize();
   };
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/DatabaseConstraint.cpp
--- a/Framework/Plugins/DatabaseConstraint.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/DatabaseConstraint.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -32,111 +32,12 @@
 
 #include <OrthancException.h>
 
+#include <boost/lexical_cast.hpp>
 #include <cassert>
 
 
 namespace OrthancDatabases
 {
-  namespace Plugins
-  {
-    OrthancPluginResourceType Convert(Orthanc::ResourceType type)
-    {
-      switch (type)
-      {
-        case Orthanc::ResourceType_Patient:
-          return OrthancPluginResourceType_Patient;
-
-        case Orthanc::ResourceType_Study:
-          return OrthancPluginResourceType_Study;
-
-        case Orthanc::ResourceType_Series:
-          return OrthancPluginResourceType_Series;
-
-        case Orthanc::ResourceType_Instance:
-          return OrthancPluginResourceType_Instance;
-
-        default:
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
-      }
-    }
-
-
-    Orthanc::ResourceType Convert(OrthancPluginResourceType type)
-    {
-      switch (type)
-      {
-        case OrthancPluginResourceType_Patient:
-          return Orthanc::ResourceType_Patient;
-
-        case OrthancPluginResourceType_Study:
-          return Orthanc::ResourceType_Study;
-
-        case OrthancPluginResourceType_Series:
-          return Orthanc::ResourceType_Series;
-
-        case OrthancPluginResourceType_Instance:
-          return Orthanc::ResourceType_Instance;
-
-        default:
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
-      }
-    }
-
-
-#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
-    OrthancPluginConstraintType Convert(ConstraintType constraint)
-    {
-      switch (constraint)
-      {
-        case ConstraintType_Equal:
-          return OrthancPluginConstraintType_Equal;
-
-        case ConstraintType_GreaterOrEqual:
-          return OrthancPluginConstraintType_GreaterOrEqual;
-
-        case ConstraintType_SmallerOrEqual:
-          return OrthancPluginConstraintType_SmallerOrEqual;
-
-        case ConstraintType_Wildcard:
-          return OrthancPluginConstraintType_Wildcard;
-
-        case ConstraintType_List:
-          return OrthancPluginConstraintType_List;
-
-        default:
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
-      }
-    }
-#endif
-
-
-#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
-    ConstraintType Convert(OrthancPluginConstraintType constraint)
-    {
-      switch (constraint)
-      {
-        case OrthancPluginConstraintType_Equal:
-          return ConstraintType_Equal;
-
-        case OrthancPluginConstraintType_GreaterOrEqual:
-          return ConstraintType_GreaterOrEqual;
-
-        case OrthancPluginConstraintType_SmallerOrEqual:
-          return ConstraintType_SmallerOrEqual;
-
-        case OrthancPluginConstraintType_Wildcard:
-          return ConstraintType_Wildcard;
-
-        case OrthancPluginConstraintType_List:
-          return ConstraintType_List;
-
-        default:
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
-      }
-    }
-#endif
-  }
-
   DatabaseConstraint::DatabaseConstraint(Orthanc::ResourceType level,
                                          const Orthanc::DicomTag& tag,
                                          bool isIdentifier,
@@ -162,10 +63,10 @@
 
 #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
   DatabaseConstraint::DatabaseConstraint(const OrthancPluginDatabaseConstraint& constraint) :
-    level_(Plugins::Convert(constraint.level)),
+    level_(MessagesToolbox::Convert(constraint.level)),
     tag_(constraint.tagGroup, constraint.tagElement),
     isIdentifier_(constraint.isIdentifierTag),
-    constraintType_(Plugins::Convert(constraint.type)),
+    constraintType_(MessagesToolbox::Convert(constraint.type)),
     caseSensitive_(constraint.isCaseSensitive),
     mandatory_(constraint.isMandatory)
   {
@@ -186,6 +87,56 @@
 #endif
 
 
+#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1
+  DatabaseConstraint::DatabaseConstraint(const Orthanc::DatabasePluginMessages::DatabaseConstraint& constraint) :
+    level_(MessagesToolbox::Convert(constraint.level())),
+    tag_(constraint.tag_group(), constraint.tag_element()),
+    isIdentifier_(constraint.is_identifier_tag()),
+    caseSensitive_(constraint.is_case_sensitive()),
+    mandatory_(constraint.is_mandatory())
+  {
+    switch (constraint.type())
+    {
+      case Orthanc::DatabasePluginMessages::CONSTRAINT_EQUAL:
+        constraintType_ = ConstraintType_Equal;
+        break;
+
+      case Orthanc::DatabasePluginMessages::CONSTRAINT_SMALLER_OR_EQUAL:
+        constraintType_ = ConstraintType_SmallerOrEqual;
+        break;
+
+      case Orthanc::DatabasePluginMessages::CONSTRAINT_GREATER_OR_EQUAL:
+        constraintType_ = ConstraintType_GreaterOrEqual;
+        break;
+
+      case Orthanc::DatabasePluginMessages::CONSTRAINT_WILDCARD:
+        constraintType_ = ConstraintType_Wildcard;
+        break;
+
+      case Orthanc::DatabasePluginMessages::CONSTRAINT_LIST:
+        constraintType_ = ConstraintType_List;
+        break;
+
+      default:
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+    }
+
+    if (constraintType_ != ConstraintType_List &&
+        constraint.values().size() != 1)
+    {
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+    }
+
+    values_.resize(constraint.values().size());
+
+    for (int i = 0; i < constraint.values().size(); i++)
+    {
+      values_[i] = constraint.values(i);
+    }
+  }
+#endif
+
+
   const std::string& DatabaseConstraint::GetValue(size_t index) const
   {
     if (index >= values_.size())
@@ -225,13 +176,13 @@
       tmpValues[i] = values_[i].c_str();
     }
 
-    constraint.level = Plugins::Convert(level_);
+    constraint.level = MessagesToolbox::ConvertToPlainC(level_);
     constraint.tagGroup = tag_.GetGroup();
     constraint.tagElement = tag_.GetElement();
     constraint.isIdentifierTag = isIdentifier_;
     constraint.isCaseSensitive = caseSensitive_;
     constraint.isMandatory = mandatory_;
-    constraint.type = Plugins::Convert(constraintType_);
+    constraint.type = MessagesToolbox::ConvertToPlainC(constraintType_);
     constraint.valuesCount = values_.size();
     constraint.values = (tmpValues.empty() ? NULL : &tmpValues[0]);
   }
@@ -275,4 +226,64 @@
       return *constraints_[index];
     }
   }
+
+
+  std::string DatabaseConstraints::Format() const
+  {
+    std::string s;
+
+    for (size_t i = 0; i < constraints_.size(); i++)
+    {
+      assert(constraints_[i] != NULL);
+      const DatabaseConstraint& constraint = *constraints_[i];
+      s += "Constraint " + boost::lexical_cast<std::string>(i) + " at " + EnumerationToString(constraint.GetLevel()) +
+        ": " + constraint.GetTag().Format();
+
+      switch (constraint.GetConstraintType())
+      {
+        case ConstraintType_Equal:
+          s += " == " + constraint.GetSingleValue();
+          break;
+
+        case ConstraintType_SmallerOrEqual:
+          s += " <= " + constraint.GetSingleValue();
+          break;
+
+        case ConstraintType_GreaterOrEqual:
+          s += " >= " + constraint.GetSingleValue();
+          break;
+
+        case ConstraintType_Wildcard:
+          s += " ~~ " + constraint.GetSingleValue();
+          break;
+
+        case ConstraintType_List:
+        {
+          s += " in [ ";
+          bool first = true;
+          for (size_t j = 0; j < constraint.GetValuesCount(); j++)
+          {
+            if (first)
+            {
+              first = false;
+            }
+            else
+            {
+              s += ", ";
+            }
+            s += constraint.GetValue(j);
+          }
+          s += "]";
+          break;
+        }
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+      }
+
+      s += "\n";
+    }
+
+    return s;
+  }
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/DatabaseConstraint.h
--- a/Framework/Plugins/DatabaseConstraint.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/DatabaseConstraint.h	Mon Dec 16 16:36:12 2024 +0100
@@ -30,16 +30,7 @@
 
 #pragma once
 
-#include <orthanc/OrthancCDatabasePlugin.h>
-
-#define ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT 0
-
-#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE)      // Macro introduced in 1.3.1
-#  if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 5, 2)
-#    undef  ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT
-#    define ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT 1
-#  endif
-#endif
+#include "MessagesToolbox.h"
 
 #include <DicomFormat/DicomMap.h>
 
@@ -47,31 +38,6 @@
 
 namespace OrthancDatabases
 {
-  enum ConstraintType
-  {
-    ConstraintType_Equal,
-    ConstraintType_SmallerOrEqual,
-    ConstraintType_GreaterOrEqual,
-    ConstraintType_Wildcard,
-    ConstraintType_List
-  };
-
-  namespace Plugins
-  {
-    OrthancPluginResourceType Convert(Orthanc::ResourceType type);
-
-    Orthanc::ResourceType Convert(OrthancPluginResourceType type);
-
-#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
-    OrthancPluginConstraintType Convert(ConstraintType constraint);
-#endif
-
-#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
-    ConstraintType Convert(OrthancPluginConstraintType constraint);
-#endif
-  }
-
-
   class DatabaseConstraint : public boost::noncopyable
   {
   private:
@@ -96,6 +62,10 @@
     explicit DatabaseConstraint(const OrthancPluginDatabaseConstraint& constraint);
 #endif
 
+#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1
+    explicit DatabaseConstraint(const Orthanc::DatabasePluginMessages::DatabaseConstraint& constraint);
+#endif
+
     Orthanc::ResourceType GetLevel() const
     {
       return level_;
@@ -170,5 +140,7 @@
     }
 
     const DatabaseConstraint& GetConstraint(size_t index) const;
+
+    std::string Format() const;
   };
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/IDatabaseBackend.h
--- a/Framework/Plugins/IDatabaseBackend.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/IDatabaseBackend.h	Mon Dec 16 16:36:12 2024 +0100
@@ -110,6 +110,14 @@
                             int64_t since,
                             uint32_t limit) = 0;
 
+    virtual void GetChangesExtended(IDatabaseBackendOutput& output,
+                                    bool& done /*out*/,
+                                    DatabaseManager& manager,
+                                    int64_t since,
+                                    int64_t to,
+                                    const std::set<uint32_t>& changeTypes,
+                                    uint32_t limit) = 0;
+
     virtual void GetChildrenInternalId(std::list<int64_t>& target /*out*/,
                                        DatabaseManager& manager,
                                        int64_t id) = 0;
@@ -377,6 +385,23 @@
     // New in Orthanc 1.12.3
     virtual uint64_t MeasureLatency(DatabaseManager& manager) = 0;
 
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+    virtual bool HasFindSupport() const = 0;
+    virtual bool HasExtendedChanges() const = 0;
 
+    // New in Orthanc 1.12.5
+    virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                             DatabaseManager& manager,
+                             const Orthanc::DatabasePluginMessages::Find_Request& request) = 0;
+
+    // New in Orthanc 1.12.5
+    virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                              DatabaseManager& manager,
+                              const Orthanc::DatabasePluginMessages::Find_Request& request) = 0;
+#endif
+
+    virtual bool HasPerformDbHousekeeping() = 0;
+
+    virtual void PerformDbHousekeeping(DatabaseManager& manager) = 0;
   };
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/ISqlLookupFormatter.cpp
--- a/Framework/Plugins/ISqlLookupFormatter.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/ISqlLookupFormatter.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -35,8 +35,9 @@
 #include <OrthancException.h>
 #include <Toolbox.h>
 
+#include <boost/algorithm/string/join.hpp>
+#include <boost/lexical_cast.hpp>
 #include <cassert>
-#include <boost/lexical_cast.hpp>
 #include <list>
 
 
@@ -64,9 +65,36 @@
   }
 
 
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  static std::string FormatLevel(const char* prefix, Orthanc::ResourceType level)
+  {
+    switch (level)
+    {
+      case Orthanc::ResourceType_Patient:
+        return std::string(prefix) + "patients";
+        
+      case Orthanc::ResourceType_Study:
+        return std::string(prefix) + "studies";
+        
+      case Orthanc::ResourceType_Series:
+        return std::string(prefix) + "series";
+        
+      case Orthanc::ResourceType_Instance:
+        return std::string(prefix) + "instances";
+
+      default:
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+    }
+  }      
+#endif
+
+
   static bool FormatComparison(std::string& target,
                                ISqlLookupFormatter& formatter,
-                               const DatabaseConstraint& constraint,
+                               OrthancDatabases::ConstraintType constraintType,
+                               const std::vector<std::string>& values,
+                               bool isCaseSensitive,
+                               bool isMandatory,
                                size_t index,
                                bool escapeBrackets)
   {
@@ -74,14 +102,14 @@
 
     std::string comparison;
 
-    switch (constraint.GetConstraintType())
+    switch (constraintType)
     {
       case ConstraintType_Equal:
       case ConstraintType_SmallerOrEqual:
       case ConstraintType_GreaterOrEqual:
       {
         std::string op;
-        switch (constraint.GetConstraintType())
+        switch (constraintType)
         {
           case ConstraintType_Equal:
             op = "=";
@@ -99,9 +127,9 @@
             throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
         }
 
-        std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue());
+        std::string parameter = formatter.GenerateParameter(values[0]);
 
-        if (constraint.IsCaseSensitive())
+        if (isCaseSensitive)
         {
           comparison = tag + ".value " + op + " " + parameter;
         }
@@ -115,16 +143,16 @@
 
       case ConstraintType_List:
       {
-        for (size_t i = 0; i < constraint.GetValuesCount(); i++)
+        for (size_t i = 0; i < values.size(); i++)
         {
           if (!comparison.empty())
           {
             comparison += ", ";
           }
 
-          std::string parameter = formatter.GenerateParameter(constraint.GetValue(i));
+          std::string parameter = formatter.GenerateParameter(values[i]);
 
-          if (constraint.IsCaseSensitive())
+          if (isCaseSensitive)
           {
             comparison += parameter;
           }
@@ -134,7 +162,7 @@
           }
         }
 
-        if (constraint.IsCaseSensitive())
+        if (isCaseSensitive)
         {
           comparison = tag + ".value IN (" + comparison + ")";
         }
@@ -148,11 +176,11 @@
 
       case ConstraintType_Wildcard:
       {
-        const std::string value = constraint.GetSingleValue();
+        const std::string value = values[0];
 
         if (value == "*")
         {
-          if (!constraint.IsMandatory())
+          if (!isMandatory)
           {
             // Universal constraint on an optional tag, ignore it
             return false;
@@ -201,7 +229,7 @@
 
           std::string parameter = formatter.GenerateParameter(escaped);
 
-          if (constraint.IsCaseSensitive())
+          if (isCaseSensitive)
           {
             comparison = (tag + ".value LIKE " + parameter + " " +
                           formatter.FormatWildcardEscape());
@@ -220,7 +248,7 @@
         return false;
     }
 
-    if (constraint.IsMandatory())
+    if (isMandatory)
     {
       target = comparison;
     }
@@ -237,6 +265,91 @@
   }
 
 
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  static bool FormatComparison(std::string& target,
+                               ISqlLookupFormatter& formatter,
+                               const Orthanc::DatabasePluginMessages::DatabaseMetadataConstraint& constraint,
+                               size_t index,
+                               bool escapeBrackets)
+  {
+    std::vector<std::string> values;
+    OrthancDatabases::ConstraintType constraintType;
+    switch (constraint.type())
+    {
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_EQUAL:
+      constraintType = OrthancDatabases::ConstraintType_Equal;
+      break;
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_GREATER_OR_EQUAL:
+      constraintType = OrthancDatabases::ConstraintType_GreaterOrEqual;
+      break;
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_LIST:
+      constraintType = OrthancDatabases::ConstraintType_List;
+      break;
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_SMALLER_OR_EQUAL:
+      constraintType = OrthancDatabases::ConstraintType_SmallerOrEqual;
+      break;
+    case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_WILDCARD:
+      constraintType = OrthancDatabases::ConstraintType_Wildcard;
+      break;
+    default:
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+    }
+
+    if (constraint.type() == Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_LIST)
+    {
+      for (int i = 0; i < constraint.values_size(); ++i)
+      {
+        values.push_back(constraint.values(i));
+      }
+    }
+    else
+    {
+      assert(constraint.values_size() == 1);
+      values.push_back(constraint.values(0));
+    }
+
+    return FormatComparison(target,
+                            formatter,
+                            constraintType,
+                            values,
+                            constraint.is_case_sensitive(),
+                            constraint.is_mandatory(),
+                            index,
+                            escapeBrackets); 
+
+  }
+#endif
+
+
+  static bool FormatComparison(std::string& target,
+                               ISqlLookupFormatter& formatter,
+                               const DatabaseConstraint& constraint,
+                               size_t index,
+                               bool escapeBrackets)
+  {
+    std::vector<std::string> values;
+    if (constraint.GetConstraintType() == OrthancDatabases::ConstraintType_List)
+    {
+      for (size_t i = 0; i < constraint.GetValuesCount(); ++i)
+      {
+        values.push_back(constraint.GetValue(i));
+      }
+    }
+    else
+    {
+      values.push_back(constraint.GetSingleValue());
+    }
+
+    return FormatComparison(target,
+                            formatter,
+                            constraint.GetConstraintType(),
+                            values,
+                            constraint.IsCaseSensitive(),
+                            constraint.IsMandatory(),
+                            index,
+                            escapeBrackets); 
+  }
+
   static void FormatJoin(std::string& target,
                          const DatabaseConstraint& constraint,
                          size_t index)
@@ -269,6 +382,104 @@
   }
 
 
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  static void FormatJoin(std::string& target,
+                         const Orthanc::DatabasePluginMessages::DatabaseMetadataConstraint& constraint,
+                         Orthanc::ResourceType level,
+                         size_t index)
+  {
+    std::string tag = "t" + boost::lexical_cast<std::string>(index);
+
+    if (constraint.is_mandatory())
+    {
+      target = " INNER JOIN ";
+    }
+    else
+    {
+      target = " LEFT JOIN ";
+    }
+
+    target += "Metadata ";
+
+    target += tag + " ON " + tag + ".id = " + FormatLevel(level) +
+               ".internalId AND " + tag + ".type = " +
+               boost::lexical_cast<std::string>(constraint.metadata());
+  }
+#endif
+
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  static void FormatJoinForOrdering(std::string& target,
+                                    uint32_t tagGroup,
+                                    uint32_t tagElement,
+                                    Orthanc::ResourceType tagLevel,
+                                    bool isIdentifierTag,
+                                    size_t index,
+                                    Orthanc::ResourceType requestLevel)
+  {
+    std::string orderArg = "order" + boost::lexical_cast<std::string>(index);
+
+    target.clear();
+
+    if (tagLevel == Orthanc::ResourceType_Patient && requestLevel == Orthanc::ResourceType_Study)
+    { // Patient tags are copied at study level
+      tagLevel = Orthanc::ResourceType_Study;
+    }
+
+    std::string tagTable;
+    if (isIdentifierTag)
+    {
+      tagTable = "DicomIdentifiers ";
+    }
+    else
+    {
+      tagTable = "MainDicomTags ";
+    }
+
+    std::string tagFilter = orderArg + ".tagGroup = " + boost::lexical_cast<std::string>(tagGroup) + " AND " + orderArg + ".tagElement = " + boost::lexical_cast<std::string>(tagElement);
+
+    if (tagLevel == requestLevel)
+    {
+      target = " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + FormatLevel(requestLevel) +
+                ".internalId AND " + tagFilter;
+    }
+    else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 1)
+    {
+      target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId "
+               " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "parent.internalId AND " + tagFilter;
+    }
+    else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 2)
+    {
+      target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId "
+               " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId "
+               " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandparent.internalId AND " + tagFilter;
+    }
+    else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 3)
+    {
+      target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId "
+               " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId "
+               " INNER JOIN Resources " + orderArg + "grandgrandparent ON " + orderArg + "grandgrandparent.internalId = " + orderArg + "grandparent.parentId "
+               " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandgrandparent.internalId AND " + tagFilter;
+    }
+  }
+#endif
+
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  static void FormatJoinForOrdering(std::string& target,
+                                    int32_t metadata,
+                                    size_t index,
+                                    Orthanc::ResourceType requestLevel)
+  {
+    std::string arg = "order" + boost::lexical_cast<std::string>(index);
+
+    target = " INNER JOIN Metadata " + arg + " ON " + arg + ".id = " + FormatLevel(requestLevel) +
+             ".internalId AND " + arg + ".type = " +
+             boost::lexical_cast<std::string>(metadata);
+  }
+#endif
+
+
   static std::string Join(const std::list<std::string>& values,
                           const std::string& prefix,
                           const std::string& separator)
@@ -365,8 +576,7 @@
           }
         }
 
-        std::string values;
-        Orthanc::Toolbox::JoinStrings(values, comparisonValues, ", ");
+        std::string values = boost::algorithm::join(comparisonValues, ", ");
 
         if (constraint.IsCaseSensitive())
         {
@@ -606,9 +816,299 @@
 
     if (limit != 0)
     {
-      sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
+      sql += " ORDER BY " + FormatLevel(queryLevel) + ".publicId ";  // we need an "order by" to use limits
+      sql += formatter.FormatLimits(0, limit);
+    }
+  }
+
+
+#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1
+  static Orthanc::ResourceType DetectLevel(const Orthanc::DatabasePluginMessages::Find_Request& request)
+  {
+    // This corresponds to "Orthanc::OrthancIdentifiers()::DetectLevel()" in the Orthanc core
+    if (!request.orthanc_id_patient().empty() &&
+        request.orthanc_id_study().empty() &&
+        request.orthanc_id_series().empty() &&
+        request.orthanc_id_instance().empty())
+    {
+      return Orthanc::ResourceType_Patient;
+    }
+    else if (!request.orthanc_id_study().empty() &&
+             request.orthanc_id_series().empty() &&
+             request.orthanc_id_instance().empty())
+    {
+      return Orthanc::ResourceType_Study;
+    }
+    else if (!request.orthanc_id_series().empty() &&
+             request.orthanc_id_instance().empty())
+    {
+      return Orthanc::ResourceType_Series;
+    }
+    else if (!request.orthanc_id_instance().empty())
+    {
+      return Orthanc::ResourceType_Instance;
+    }
+    else
+    {
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+    }
+  }
+
+  static const std::string& GetOrthancIdentifier(const Orthanc::DatabasePluginMessages::Find_Request& request, Orthanc::ResourceType level)
+  {
+    switch (level)
+    {
+      case Orthanc::ResourceType::ResourceType_Patient:
+        return request.orthanc_id_patient();
+      case Orthanc::ResourceType::ResourceType_Study:
+        return request.orthanc_id_study();
+      case Orthanc::ResourceType::ResourceType_Series:
+        return request.orthanc_id_series();
+      case Orthanc::ResourceType::ResourceType_Instance:
+        return request.orthanc_id_instance();
+      default:
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
     }
   }
+  
+
+  void ISqlLookupFormatter::Apply(std::string& sql,
+                                  ISqlLookupFormatter& formatter,
+                                  const Orthanc::DatabasePluginMessages::Find_Request& request)
+  {
+    const bool escapeBrackets = formatter.IsEscapeBrackets();
+    Orthanc::ResourceType queryLevel = MessagesToolbox::Convert(request.level());
+    const std::string& strQueryLevel = FormatLevel(queryLevel);
+
+    DatabaseConstraints constraints;
+
+    for (int i = 0; i < request.dicom_tag_constraints().size(); i++)
+    {
+      constraints.AddConstraint(new DatabaseConstraint(request.dicom_tag_constraints(i)));
+    }
+
+    Orthanc::ResourceType lowerLevel, upperLevel;
+    GetLookupLevels(lowerLevel, upperLevel, queryLevel, constraints);
+
+    assert(upperLevel <= queryLevel &&
+           queryLevel <= lowerLevel);
+
+    std::string ordering;
+    std::string orderingJoins;
+
+    if (request.ordering_size() > 0)
+    {
+      std::vector<std::string> orderByFields;
+
+      for (int i = 0; i < request.ordering_size(); ++i)
+      {
+        std::string orderingJoin;
+        const Orthanc::DatabasePluginMessages::Find_Request_Ordering& ordering = request.ordering(i);
+        
+        switch (ordering.key_type())
+        {
+          case Orthanc::DatabasePluginMessages::OrderingKeyType::ORDERING_KEY_TYPE_DICOM_TAG:
+            FormatJoinForOrdering(orderingJoin, ordering.tag_group(), ordering.tag_element(), MessagesToolbox::Convert(ordering.tag_level()), ordering.is_identifier_tag(), i, queryLevel);
+            break;
+          case Orthanc::DatabasePluginMessages::OrderingKeyType::ORDERING_KEY_TYPE_METADATA:
+            FormatJoinForOrdering(orderingJoin, ordering.metadata(), i, queryLevel);
+            break;
+          default:
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+        }
+
+        orderingJoins += orderingJoin;
+        
+        std::string orderByField;
+        if (!formatter.SupportsNullsLast())
+        {
+          orderByField = "CASE WHEN order" + boost::lexical_cast<std::string>(i) + ".value IS NULL THEN 1 ELSE 0 END, ";
+        }
+        orderByField += "order" + boost::lexical_cast<std::string>(i) + ".value";
+         
+        if (ordering.direction() == Orthanc::DatabasePluginMessages::OrderingDirection::ORDERING_DIRECTION_ASC)
+        {
+          orderByField += " ASC";
+        }
+        else
+        {
+          orderByField += " DESC";
+        }
+
+        orderByFields.push_back(orderByField);
+      }
+
+      std::string orderByFieldsString = boost::algorithm::join(orderByFields, ", ");
+
+      if (formatter.SupportsNullsLast())
+      {
+        ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + " NULLS LAST) AS rowNumber";
+      }
+      else
+      {
+        ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + ") AS rowNumber";
+      }
+    }
+    else
+    {
+      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, " +
+           ordering +
+           " FROM Resources AS " + strQueryLevel);
+
+
+    std::string joins, comparisons;
+
+    const bool isOrthancIdentifiersDefined = (!request.orthanc_id_patient().empty() ||
+                                              !request.orthanc_id_study().empty() ||
+                                              !request.orthanc_id_series().empty() ||
+                                              !request.orthanc_id_instance().empty());
+
+    // handle parent constraints
+    if (isOrthancIdentifiersDefined && Orthanc::IsResourceLevelAboveOrEqual(DetectLevel(request), queryLevel))
+    {
+      Orthanc::ResourceType topParentLevel = DetectLevel(request);
+
+      if (topParentLevel == queryLevel)
+      {
+        comparisons += " AND " + FormatLevel(topParentLevel) + ".publicId = " + formatter.GenerateParameter(GetOrthancIdentifier(request, topParentLevel));
+      }
+      else
+      {
+        comparisons += " AND " + FormatLevel("parent", topParentLevel) + ".publicId = " + formatter.GenerateParameter(GetOrthancIdentifier(request, topParentLevel));
+
+        for (int level = queryLevel; level > topParentLevel; level--)
+        {
+          joins += " INNER JOIN Resources " +
+                  FormatLevel("parent", static_cast<Orthanc::ResourceType>(level - 1)) + " ON " +
+                  FormatLevel("parent", static_cast<Orthanc::ResourceType>(level - 1)) + ".internalId = ";
+          if (level == queryLevel)
+          {
+            joins += FormatLevel(static_cast<Orthanc::ResourceType>(level)) + ".parentId";
+          }
+          else
+          {
+            joins += FormatLevel("parent", static_cast<Orthanc::ResourceType>(level)) + ".parentId";
+          }
+        }
+      }
+    }
+
+    size_t count = 0;
+
+    for (size_t i = 0; i < constraints.GetSize(); i++)
+    {
+      const DatabaseConstraint& constraint = constraints.GetConstraint(i);
+
+      std::string comparison;
+
+      if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets))
+      {
+        std::string join;
+        FormatJoin(join, constraint, count);
+        joins += join;
+
+        if (!comparison.empty())
+        {
+          comparisons += " AND " + comparison;
+        }
+
+        count ++;
+      }
+    }
+
+    for (int i = 0; i < request.metadata_constraints_size(); i++)
+    {
+      std::string comparison;
+      
+      if (FormatComparison(comparison, formatter, request.metadata_constraints(i), count, escapeBrackets))
+      {
+        std::string join;
+        FormatJoin(join, request.metadata_constraints(i), queryLevel, count);
+        joins += join;
+
+        if (!comparison.empty())
+        {
+          comparisons += " AND " + comparison;
+        }
+        
+        count ++;
+      }
+    }
+
+
+    for (int level = queryLevel - 1; level >= upperLevel; level--)
+    {
+      sql += (" INNER JOIN Resources " +
+              FormatLevel(static_cast<Orthanc::ResourceType>(level)) + " ON " +
+              FormatLevel(static_cast<Orthanc::ResourceType>(level)) + ".internalId=" +
+              FormatLevel(static_cast<Orthanc::ResourceType>(level + 1)) + ".parentId");
+    }
+
+    for (int level = queryLevel + 1; level <= lowerLevel; level++)
+    {
+      sql += (" INNER JOIN Resources " +
+              FormatLevel(static_cast<Orthanc::ResourceType>(level)) + " ON " +
+              FormatLevel(static_cast<Orthanc::ResourceType>(level - 1)) + ".internalId=" +
+              FormatLevel(static_cast<Orthanc::ResourceType>(level)) + ".parentId");
+    }
+
+    std::list<std::string> where;
+    where.push_back(strQueryLevel + ".resourceType = " +
+                    formatter.FormatResourceType(queryLevel) + comparisons);
+
+
+    if (!request.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 (int i = 0; i < request.labels().size(); i++)
+      {
+        formattedLabels.push_back(formatter.GenerateParameter(request.labels(i)));
+      }
+
+      std::string condition;
+      switch (request.labels_constraint())
+      {
+        case Orthanc::DatabasePluginMessages::LABELS_CONSTRAINT_ANY:
+          condition = "> 0";
+          break;
+
+        case Orthanc::DatabasePluginMessages::LABELS_CONSTRAINT_ALL:
+          condition = "= " + boost::lexical_cast<std::string>(request.labels().size());
+          break;
+
+        case Orthanc::DatabasePluginMessages::LABELS_CONSTRAINT_NONE:
+          condition = "= 0";
+          break;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+      }
+
+      where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel +
+                      ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition);
+    }
+
+    sql += joins + orderingJoins + Join(where, " WHERE ", " AND ");
+
+    if (request.has_limits())
+    {
+      sql += formatter.FormatLimits(request.limits().since(), request.limits().count());
+    }
+
+  }
+#endif
 
 
   void ISqlLookupFormatter::ApplySingleLevel(std::string& sql,
@@ -721,8 +1221,8 @@
 
     if (limit != 0)
     {
-      sql += " LIMIT " + boost::lexical_cast<std::string>(limit);
+      sql += " ORDER BY publicId ";  // we need an "order by" to use limits
+      sql += formatter.FormatLimits(0, limit);
     }
   }
-
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/ISqlLookupFormatter.h
--- a/Framework/Plugins/ISqlLookupFormatter.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/ISqlLookupFormatter.h	Mon Dec 16 16:36:12 2024 +0100
@@ -30,7 +30,7 @@
 
 #pragma once
 
-#include <Enumerations.h>
+#include "MessagesToolbox.h"
 
 #include <boost/noncopyable.hpp>
 #include <vector>
@@ -38,6 +38,7 @@
 namespace OrthancDatabases
 {
   class DatabaseConstraints;
+  class FindRequest;
 
   enum LabelsConstraint
   {
@@ -59,6 +60,10 @@
 
     virtual std::string FormatWildcardEscape() = 0;
 
+    virtual std::string FormatLimits(uint64_t since, uint64_t count) = 0;
+
+    virtual std::string FormatNull(const char* type) = 0;
+
     /**
      * Whether to escape '[' and ']', which is only needed for
      * MSSQL. New in Orthanc 1.10.0, from the following changeset:
@@ -66,6 +71,8 @@
      **/
     virtual bool IsEscapeBrackets() const = 0;
 
+    virtual bool SupportsNullsLast() const = 0;
+
     static void GetLookupLevels(Orthanc::ResourceType& lowerLevel,
                                 Orthanc::ResourceType& upperLevel,
                                 const Orthanc::ResourceType& queryLevel,
@@ -86,5 +93,11 @@
                                  const std::set<std::string>& labels,  // New in Orthanc 1.12.0
                                  LabelsConstraint labelsConstraint,    // New in Orthanc 1.12.0
                                  size_t limit);
+
+#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1
+    static void Apply(std::string& sql,
+                      ISqlLookupFormatter& formatter,
+                      const Orthanc::DatabasePluginMessages::Find_Request& request);
+#endif
   };
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/IndexBackend.cpp
--- a/Framework/Plugins/IndexBackend.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/IndexBackend.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -36,6 +36,8 @@
 #include <OrthancException.h>
 #include <Toolbox.h>
 
+#include <boost/algorithm/string/join.hpp>
+
 
 namespace OrthancDatabases
 {
@@ -60,7 +62,24 @@
     return s;
   }
 
-  
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  static std::string JoinChanges(const std::set<uint32_t>& changeTypes)
+  {
+    std::set<std::string> changeTypesString;
+    for (std::set<uint32_t>::const_iterator it = changeTypes.begin(); it != changeTypes.end(); ++it)
+    {
+      changeTypesString.insert(boost::lexical_cast<std::string>(*it));
+    }
+
+    std::string joinedChangesTypes;
+    Orthanc::Toolbox::JoinStrings(joinedChangesTypes, changeTypesString, ", ");
+
+    return joinedChangesTypes;
+  }
+#endif  
+
+
   template <typename T>
   static void ReadListOfIntegers(std::list<T>& target,
                                  DatabaseManager::CachedStatement& statement,
@@ -112,33 +131,69 @@
   }
 
 
+  namespace  // Anonymous namespace to avoid clashes between compilation modules
+  {
+    struct Change
+    {
+      int64_t       seq_;
+      int32_t       changeType_;
+      OrthancPluginResourceType       resourceType_;
+      std::string   publicId_;
+      std::string   changeDate_;
+
+      Change(int64_t seq, int32_t changeType, OrthancPluginResourceType resourceType, const std::string& publicId, const std::string& changeDate)
+      : seq_(seq), changeType_(changeType), resourceType_(resourceType), publicId_(publicId), changeDate_(changeDate)
+      {
+      }
+    };
+  }
+
+
   void IndexBackend::ReadChangesInternal(IDatabaseBackendOutput& output,
                                          bool& done,
                                          DatabaseManager& manager,
                                          DatabaseManager::CachedStatement& statement,
                                          const Dictionary& args,
-                                         uint32_t limit)
+                                         uint32_t limit,
+                                         bool returnFirstResults)
   {
     statement.Execute(args);
 
-    uint32_t count = 0;
-
-    while (count < limit &&
-           !statement.IsDone())
+    std::list<Change> changes;
+    while (!statement.IsDone())
     {
-      output.AnswerChange(
+      changes.push_back(Change(
         statement.ReadInteger64(0),
         statement.ReadInteger32(1),
         static_cast<OrthancPluginResourceType>(statement.ReadInteger32(2)),
         statement.ReadString(3),
-        statement.ReadString(4));
+        statement.ReadString(4)
+      ));
 
       statement.Next();
-      count++;
     }
-
-    done = (count < limit ||
-            statement.IsDone());
+    
+    done = changes.size() <= limit;  // 'done' means we have returned all requested changes
+
+    // if we have retrieved more changes than requested -> cleanup
+    if (changes.size() > limit)
+    {
+      assert(changes.size() == limit+1); // the statement should only request 1 element more
+
+      if (returnFirstResults)
+      {
+        changes.pop_back();
+      }
+      else
+      {
+        changes.pop_front();
+      }
+    }
+
+    for (std::list<Change>::const_iterator it = changes.begin(); it != changes.end(); ++it)
+    {
+      output.AnswerChange(it->seq_, it->changeType_, it->resourceType_, it->publicId_, it->changeDate_);
+    }
   }
 
 
@@ -256,8 +311,10 @@
   }
 
 
-  IndexBackend::IndexBackend(OrthancPluginContext* context) :
-    context_(context)
+  IndexBackend::IndexBackend(OrthancPluginContext* context,
+                             bool readOnly) :
+    context_(context),
+    readOnly_(readOnly)
   {
   }
 
@@ -531,62 +588,131 @@
     {
       suffix = "OFFSET ${since} ROWS FETCH FIRST ${limit} ROWS ONLY";
     }
-    else
+    else if (limit > 0)
     {
       suffix = "LIMIT ${limit} OFFSET ${since}";
     }
     
-    DatabaseManager::CachedStatement statement(
-      STATEMENT_FROM_HERE, manager,
-      "SELECT publicId FROM (SELECT publicId FROM Resources "
-      "WHERE resourceType=${type}) AS tmp ORDER BY tmp.publicId " + suffix);
+    std::string sql = "SELECT publicId FROM (SELECT publicId FROM Resources "
+      "WHERE resourceType=${type}) AS tmp ORDER BY tmp.publicId " + suffix;
+
+    DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
       
     statement.SetReadOnly(true);
-    statement.SetParameterType("type", ValueType_Integer64);
-    statement.SetParameterType("limit", ValueType_Integer64);
-    statement.SetParameterType("since", ValueType_Integer64);
 
     Dictionary args;
+
+    statement.SetParameterType("type", ValueType_Integer64);
     args.SetIntegerValue("type", static_cast<int>(resourceType));
-    args.SetIntegerValue("limit", limit);
-    args.SetIntegerValue("since", since);
+    
+    if (limit > 0)
+    {
+      statement.SetParameterType("limit", ValueType_Integer64);
+      statement.SetParameterType("since", ValueType_Integer64);
+      args.SetIntegerValue("limit", limit);
+      args.SetIntegerValue("since", since);
+    }
 
     ReadListOfStrings(target, statement, args);
   }
 
-    
-  /* Use GetOutput().AnswerChange() */
   void IndexBackend::GetChanges(IDatabaseBackendOutput& output,
                                 bool& done /*out*/,
                                 DatabaseManager& manager,
                                 int64_t since,
                                 uint32_t limit)
   {
-    std::string suffix;
+    std::set<uint32_t> changeTypes;
+    GetChangesExtended(output, done, manager, since, -1, changeTypes, limit);
+  }
+
+  /* Use GetOutput().AnswerChange() */
+  void IndexBackend::GetChangesExtended(IDatabaseBackendOutput& output,
+                                        bool& done /*out*/,
+                                        DatabaseManager& manager,
+                                        int64_t since,
+                                        int64_t to,
+                                        const std::set<uint32_t>& changeTypes,
+                                        uint32_t limit)
+  {
+    std::string limitSuffix;
     if (manager.GetDialect() == Dialect_MSSQL)
     {
-      suffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY";
+      limitSuffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY";
     }
     else
     {
-      suffix = "LIMIT ${limit}";
+      limitSuffix = "LIMIT ${limit}";
     }
     
-    DatabaseManager::CachedStatement statement(
-      STATEMENT_FROM_HERE, manager,
-      "SELECT Changes.seq, Changes.changeType, Changes.resourceType, Resources.publicId, "
-      "Changes.date FROM Changes INNER JOIN Resources "
-      "ON Changes.internalId = Resources.internalId WHERE seq>${since} ORDER BY seq " + suffix);
-
+    std::vector<std::string> filters;
+    bool hasSince = false;
+    bool hasTo = false;
+
+    if (since > 0)
+    {
+      hasSince = true;
+      filters.push_back("seq>${since}");
+    }
+    if (to != -1)
+    {
+      hasTo = true;
+      filters.push_back("seq<=${to}");
+    }
+#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1
+    if (changeTypes.size() > 0)
+    {
+      filters.push_back("changeType IN (" + JoinChanges(changeTypes) + ") ");
+    }
+#endif
+
+    std::string filtersString;
+    if (filters.size() > 0)
+    {
+      filtersString = "WHERE " + boost::algorithm::join(filters, " AND ");
+    }
+
+    std::string sql;
+    bool returnFirstResults;
+    if (hasTo && !hasSince)
+    {
+      // in this case, we want the largest values but we want them ordered in ascending order
+      sql = "SELECT * FROM (SELECT Changes.seq, Changes.changeType, Changes.resourceType, Resources.publicId, Changes.date "
+            "FROM Changes INNER JOIN Resources "
+            "ON Changes.internalId = Resources.internalId " + filtersString + " ORDER BY seq DESC " + limitSuffix + 
+            ") AS FilteredChanges ORDER BY seq ASC";
+
+      returnFirstResults = false;
+    }
+    else
+    {
+      // default query: we want the smallest values ordered in ascending order
+      sql = "SELECT Changes.seq, Changes.changeType, Changes.resourceType, Resources.publicId, "
+            "Changes.date FROM Changes INNER JOIN Resources "
+            "ON Changes.internalId = Resources.internalId " + filtersString + " ORDER BY seq ASC " + limitSuffix;
+      returnFirstResults = true;
+    }
+
+    DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
     statement.SetReadOnly(true);
+    Dictionary args;
+
     statement.SetParameterType("limit", ValueType_Integer64);
-    statement.SetParameterType("since", ValueType_Integer64);
-
-    Dictionary args;
-    args.SetIntegerValue("limit", limit + 1);
-    args.SetIntegerValue("since", since);
-
-    ReadChangesInternal(output, done, manager, statement, args, limit);
+    args.SetIntegerValue("limit", limit + 1);  // we take limit+1 because we use the +1 to know if "Done" must be set to true
+
+    if (hasSince)
+    {
+      statement.SetParameterType("since", ValueType_Integer64);
+      args.SetIntegerValue("since", since);
+    }
+
+    if (hasTo)
+    {
+      statement.SetParameterType("to", ValueType_Integer64);
+      args.SetIntegerValue("to", to);
+    }
+
+    ReadChangesInternal(output, done, manager, statement, args, limit, returnFirstResults);
   }
 
     
@@ -686,7 +812,7 @@
     Dictionary args;
 
     bool done;  // Ignored
-    ReadChangesInternal(output, done, manager, statement, args, 1);
+    ReadChangesInternal(output, done, manager, statement, args, 1, true);
   }
 
     
@@ -1240,7 +1366,11 @@
 
   bool IndexBackend::HasMeasureLatency()
   {
+#if ORTHANC_FRAMEWORK_VERSION_IS_ABOVE(1, 12, 2)
     return true;
+#else
+    return false;
+#endif
   }
 
 
@@ -2052,7 +2182,7 @@
 
     virtual std::string FormatResourceType(Orthanc::ResourceType level)
     {
-      return boost::lexical_cast<std::string>(Plugins::Convert(level));
+      return boost::lexical_cast<std::string>(MessagesToolbox::ConvertToPlainC(level));
     }
 
     virtual std::string FormatWildcardEscape()
@@ -2072,6 +2202,74 @@
       }
     }
 
+    virtual std::string FormatNull(const char* type)
+    {
+      switch (dialect_)
+      {
+        case Dialect_PostgreSQL:
+          return std::string("NULL::") + type;
+        case Dialect_MSSQL:
+        case Dialect_SQLite:
+        case Dialect_MySQL:
+          return "NULL";
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+      }
+    }
+
+
+    virtual std::string FormatLimits(uint64_t since, uint64_t count)
+    {
+      std::string sql;
+
+      switch (dialect_)
+      {
+        case Dialect_MSSQL:
+        {
+          if (count > 0 || since > 0)
+          {
+            sql += " OFFSET " + boost::lexical_cast<std::string>(since) + " ROWS ";
+          }
+          if (count > 0)
+          {
+            sql += " FETCH NEXT " + boost::lexical_cast<std::string>(count) + " ROWS ONLY ";
+          }
+        }; break;
+        case Dialect_SQLite:
+        case Dialect_PostgreSQL:
+        {
+          if (count > 0)
+          {
+            sql += " LIMIT " + boost::lexical_cast<std::string>(count);
+          }
+          if (since > 0)
+          {
+            sql += " OFFSET " + boost::lexical_cast<std::string>(since);
+          }
+        }; break;
+        case Dialect_MySQL:
+        {
+          if (count > 0 && since > 0)
+          {
+            sql += " LIMIT " + boost::lexical_cast<std::string>(since) + ", " + boost::lexical_cast<std::string>(count);
+          }
+          else if (count > 0)
+          {
+            sql += " LIMIT " + boost::lexical_cast<std::string>(count);
+          }
+          else if (since > 0)
+          {
+            sql += " LIMIT " + boost::lexical_cast<std::string>(since) + ", 18446744073709551615"; // max uint64 value when you don't want any limit
+          }
+        }; break;
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+      }
+      
+      return sql;
+    }
+
     virtual bool IsEscapeBrackets() const
     {
       // This was initially done at a bad location by the following changeset:
@@ -2079,6 +2277,11 @@
       return (dialect_ == Dialect_MSSQL);
     }
 
+    virtual bool SupportsNullsLast() const
+    {
+      return (dialect_ == Dialect_PostgreSQL);
+    }
+
     void PrepareStatement(DatabaseManager::StandaloneStatement& statement) const
     {
       statement.SetReadOnly(true);
@@ -2109,7 +2312,7 @@
                                      bool requestSomeInstance)
   {
     LookupFormatter formatter(manager.GetDialect());
-    Orthanc::ResourceType queryLevel = Plugins::Convert(queryLevel_);
+    Orthanc::ResourceType queryLevel = MessagesToolbox::Convert(queryLevel_);
     Orthanc::ResourceType lowerLevel, upperLevel;
     ISqlLookupFormatter::GetLookupLevels(lowerLevel, upperLevel,  queryLevel, lookup);
 
@@ -2788,7 +2991,8 @@
   
   void IndexBackend::Register(IndexBackend* backend,
                               size_t countConnections,
-                              unsigned int maxDatabaseRetries)
+                              unsigned int maxDatabaseRetries,
+                              unsigned int housekeepingDelaySeconds)
   {
     if (backend == NULL)
     {
@@ -2802,7 +3006,7 @@
 #  if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
     if (OrthancPluginCheckVersionAdvanced(backend->GetContext(), 1, 12, 0) == 1)
     {
-      DatabaseBackendAdapterV4::Register(backend, countConnections, maxDatabaseRetries);
+      DatabaseBackendAdapterV4::Register(backend, countConnections, maxDatabaseRetries, housekeepingDelaySeconds);
       return;
     }
 #  endif
@@ -2812,7 +3016,7 @@
 #  if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 9, 2)
     if (OrthancPluginCheckVersionAdvanced(backend->GetContext(), 1, 9, 2) == 1)
     {
-      DatabaseBackendAdapterV3::Register(backend, countConnections, maxDatabaseRetries);
+      DatabaseBackendAdapterV3::Register(backend, countConnections, maxDatabaseRetries, housekeepingDelaySeconds);
       return;
     }
 #  endif
@@ -2880,6 +3084,7 @@
 
   uint64_t IndexBackend::MeasureLatency(DatabaseManager& manager)
   {
+#if ORTHANC_FRAMEWORK_VERSION_IS_ABOVE(1, 12, 2)
     // execute 11x the simplest statement and return the median value
     std::vector<uint64_t> measures;
 
@@ -2897,6 +3102,9 @@
     std::sort(measures.begin(), measures.end());
 
     return measures[measures.size() / 2];
+#else
+    throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+#endif
   }
 
 
@@ -2908,4 +3116,1160 @@
     backend.ConfigureDatabase(*manager, hasIdentifierTags, identifierTags);
     return manager.release();
   }
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  bool IndexBackend::HasFindSupport() const
+  {
+    // TODO-FIND  move to child plugins ?
+    return true;
+  }
+#endif
+
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* GetResourceContent(
+                              Orthanc::DatabasePluginMessages::Find_Response* response,
+                              Orthanc::DatabasePluginMessages::ResourceType level)
+  {
+    Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = NULL;  // the protobuf response will be the owner
+    
+    switch (level)
+    {
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
+        content = response->mutable_patient_content();
+        break;
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
+        content = response->mutable_study_content();
+        break;
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
+        content =response->mutable_series_content();
+        break;
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
+        content = response->mutable_instance_content();
+        break;
+      default:
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+    }
+    return content;
+  }
+
+  Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* GetChildrenContent(
+                              Orthanc::DatabasePluginMessages::Find_Response* response,
+                              Orthanc::DatabasePluginMessages::ResourceType childrenLevel)
+  {
+    Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = NULL;  // the protobuf response will be the owner
+    
+    switch (childrenLevel)
+    {
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
+        content = response->mutable_children_studies_content();
+        break;
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
+        content =response->mutable_children_series_content();
+        break;
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
+        content = response->mutable_children_instances_content();
+        break;
+      default:
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+    }
+    return content;
+  }
+
+  std::string JoinRequestedMetadata(const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec)
+  {
+    std::set<std::string> metadataTypes;
+    for (int i = 0; i < childrenSpec->retrieve_metadata_size(); ++i)
+    {
+      metadataTypes.insert(boost::lexical_cast<std::string>(childrenSpec->retrieve_metadata(i)));
+    }
+    std::string joinedMetadataTypes;
+    Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ", ");
+
+    return joinedMetadataTypes;
+  }
+
+  std::string JoinRequestedTags(const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec)
+  {
+    std::set<std::string> tags;
+    for (int i = 0; i < childrenSpec->retrieve_main_dicom_tags_size(); ++i)
+    {
+      tags.insert("(" + boost::lexical_cast<std::string>(childrenSpec->retrieve_main_dicom_tags(i).group()) 
+                  + ", " + boost::lexical_cast<std::string>(childrenSpec->retrieve_main_dicom_tags(i).element()) + ")");
+    }
+    std::string joinedTags;
+    Orthanc::Toolbox::JoinStrings(joinedTags, tags, ", ");
+
+    return joinedTags;
+  }
+
+
+#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_INT_3 8
+#define C9_BIG_INT_1 9
+#define C10_BIG_INT_2 10
+
+#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_CHILDREN_COUNT 23
+#define QUERY_GRAND_CHILDREN_IDENTIFIERS 30
+#define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 31
+#define QUERY_GRAND_CHILDREN_METADATA 32
+#define QUERY_GRAND_CHILDREN_COUNT 33
+#define QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS 40
+#define QUERY_GRAND_GRAND_CHILDREN_COUNT 41
+#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)
+
+  void IndexBackend::ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                                  DatabaseManager& manager,
+                                  const Orthanc::DatabasePluginMessages::Find_Request& request)
+  {
+    std::string sql;
+
+    LookupFormatter formatter(manager.GetDialect());
+    std::string lookupSql;
+    ISqlLookupFormatter::Apply(lookupSql, formatter, request);
+
+    sql = "WITH Lookup AS (" + lookupSql + ") SELECT COUNT(*) FROM Lookup";
+
+    DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
+    statement.Execute(formatter.GetDictionary());
+    response.mutable_count_resources()->set_count(statement.ReadInteger64(0));
+  }
+
+  void IndexBackend::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                                    DatabaseManager& manager,
+                                    const Orthanc::DatabasePluginMessages::Find_Request& request)
+  {
+    // TODO-FIND move to child plugins ?
+
+
+    // If we want the Find to use a read-only transaction, we can not create temporary tables with
+    // the lookup results.  So we must use a CTE (Common Table Expression).  
+    // However, a CTE can only be used in a single query -> we must unionize all the following 
+    // queries to retrieve values from various tables.
+    // However, to use UNION, all tables must have the same columns (numbers and types).  That's
+    // why we have generic column names.
+    // So, at the end we'll have only one very big query !
+
+    std::string sql;
+
+    // extract the resource id of interest by executing the lookup in a CTE
+    LookupFormatter formatter(manager.GetDialect());
+    std::string lookupSqlCTE;
+    ISqlLookupFormatter::Apply(lookupSqlCTE, formatter, request);
+
+    // base query, retrieve the ordered internalId and publicId of the selected resources
+    sql = "WITH Lookup AS (" + lookupSqlCTE + ") ";
+
+    std::string oneInstanceSqlCTE;
+
+    if (request.level() != Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE &&
+        request.retrieve_one_instance_metadata_and_attachments())
+    {
+      switch (request.level())
+      {
+        case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
+        {
+          oneInstanceSqlCTE = "SELECT Lookup.internalId AS parentInternalId, childLevel.publicId AS instancePublicId, childLevel.internalId AS instanceInternalId, ROW_NUMBER() OVER (PARTITION BY Lookup.internalId ORDER BY childLevel.publicId) AS rowNum"
+                "   FROM Resources AS childLevel "
+                "   INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId";
+        }; break;
+        case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
+        {
+          oneInstanceSqlCTE = "SELECT Lookup.internalId AS parentInternalId, grandChildLevel.publicId AS instancePublicId, grandChildLevel.internalId AS instanceInternalId, ROW_NUMBER() OVER (PARTITION BY Lookup.internalId ORDER BY grandChildLevel.publicId) AS rowNum"
+                "   FROM Resources AS grandChildLevel "
+                "   INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId "
+                "   INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId";
+        }; break;
+        case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
+        {
+          oneInstanceSqlCTE = "SELECT Lookup.internalId AS parentInternalId, grandGrandChildLevel.publicId AS instancePublicId, grandGrandChildLevel.internalId AS instanceInternalId, ROW_NUMBER() OVER (PARTITION BY Lookup.internalId ORDER BY grandGrandChildLevel.publicId) AS rowNum"
+                "   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";
+        }; break;
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
+      }
+      sql += ", _OneInstance AS (" + oneInstanceSqlCTE + ") ";
+      sql += ", OneInstance AS (SELECT parentInternalId, instancePublicId, instanceInternalId FROM _OneInstance WHERE rowNum = 1) ";  // this is a generic way to implement DISTINCT ON
+    }
+
+    // if (!oneInstanceSqlCTE.empty() && (manager.GetDialect() == Dialect_MySQL || manager.GetDialect() == Dialect_SQLite))
+    // { // all CTEs must be declared first in some dialects
+    // }
+
+    std::string revisionInC7;
+    if (HasRevisionsSupport())
+    {
+      revisionInC7 = "  revision AS c7_int2, ";
+    }
+    else
+    {
+      revisionInC7 = "  0 AS C7_int2, ";
+    }
+
+
+    sql += " SELECT "
+          "  " TOSTRING(QUERY_LOOKUP) " AS c0_queryId, "
+          "  Lookup.internalId AS c1_internalId, "
+          "  Lookup.rowNumber AS c2_rowNumber, "
+          "  Lookup.publicId AS c3_string1, "
+          "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+          "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+          "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+          "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+          "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+          "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+          "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+          "  FROM Lookup ";
+
+    // need MainDicomTags from resource ?
+    if (request.retrieve_main_dicom_tags())
+    {
+      sql += "UNION ALL SELECT "
+             "  " TOSTRING(QUERY_MAIN_DICOM_TAGS) " AS c0_queryId, "
+             "  Lookup.internalId AS c1_internalId, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+             "  value AS c3_string1, "
+             "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+             "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+             "  tagGroup AS c6_int1, "
+             "  tagElement AS c7_int2, "
+             "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+             "FROM Lookup "
+             "INNER JOIN MainDicomTags ON MainDicomTags.id = Lookup.internalId ";
+    }
+    
+    // need resource metadata ?
+    if (request.retrieve_metadata())
+    {
+      sql += "UNION ALL SELECT "
+             "  " TOSTRING(QUERY_METADATA) " AS c0_queryId, "
+             "  Lookup.internalId AS c1_internalId, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+             "  value AS c3_string1, "
+             "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+             "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+             "  type AS c6_int1, "
+             + revisionInC7 +
+             "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+             "FROM Lookup "
+             "INNER JOIN Metadata ON Metadata.id = Lookup.internalId ";
+    }
+
+    // need resource attachments ?
+    if (request.retrieve_attachments())
+    {
+      sql += "UNION ALL SELECT "
+             "  " TOSTRING(QUERY_ATTACHMENTS) " AS c0_queryId, "
+             "  Lookup.internalId AS c1_internalId, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+             "  uuid AS c3_string1, "
+             "  uncompressedHash AS c4_string2, "
+             "  compressedHash AS c5_string3, "
+             "  fileType AS c6_int1, "
+             + revisionInC7 +
+             "  compressionType AS c8_int3, "
+             "  compressedSize AS c9_big_int1, "
+             "  uncompressedSize AS c10_big_int2 "
+             "FROM Lookup "
+             "INNER JOIN AttachedFiles ON AttachedFiles.id = Lookup.internalId ";
+    }
+
+    // need resource labels ?
+    if (request.retrieve_labels())
+    {
+      sql += "UNION ALL SELECT "
+             "  " TOSTRING(QUERY_LABELS) " AS c0_queryId, "
+             "  Lookup.internalId AS c1_internalId, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+             "  label AS c3_string1, "
+             "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+             "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+             "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+             "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+             "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+             "FROM Lookup "
+             "INNER JOIN Labels ON Labels.id = Lookup.internalId ";
+    }
+
+    // need MainDicomTags from parent ?
+    if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT)
+    {
+      const Orthanc::DatabasePluginMessages::Find_Request_ParentSpecification* parentSpec = NULL;
+      switch (request.level())
+      {
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
+        parentSpec = &(request.parent_patient());
+        break;
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
+        parentSpec = &(request.parent_study());
+        break;
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
+        parentSpec = &(request.parent_series());
+        break;
+      
+      default:
+        break;
+      }
+
+      if (parentSpec->retrieve_main_dicom_tags())
+      {
+        sql += "UNION ALL SELECT "
+               "  " TOSTRING(QUERY_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+               "  value AS c3_string1, "
+               "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+               "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+               "  tagGroup AS c6_int1, "
+               "  tagElement AS c7_int2, "
+               "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+               "FROM Lookup "
+               "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
+               "INNER JOIN MainDicomTags ON MainDicomTags.id = currentLevel.parentId ";
+      }
+
+      if (parentSpec->retrieve_metadata())
+      {
+        sql += "UNION ALL SELECT "
+               "  " TOSTRING(QUERY_PARENT_METADATA) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+               "  value AS c3_string1, "
+               "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+               "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+               "  type AS c6_int1, "
+               + revisionInC7 +
+               "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+               "FROM Lookup "
+               "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId "
+               "INNER JOIN Metadata ON Metadata.id = currentLevel.parentId ";
+      }
+
+      // need MainDicomTags from grandparent ?
+      if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY)
+      {
+        const Orthanc::DatabasePluginMessages::Find_Request_ParentSpecification* grandparentSpec = NULL;
+        switch (request.level())
+        {
+        case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
+          grandparentSpec = &(request.parent_patient());
+          break;
+        case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE:
+          grandparentSpec = &(request.parent_study());
+          break;
+        
+        default:
+          break;
+        }
+
+        if (grandparentSpec->retrieve_main_dicom_tags())
+        {
+          sql += "UNION ALL SELECT "
+               "  " TOSTRING(QUERY_GRAND_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+               "  value AS c3_string1, "
+               "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+               "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+               "  tagGroup AS c6_int1, "
+               "  tagElement AS c7_int2, "
+               "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c10_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 ";
+        }
+
+        if (grandparentSpec->retrieve_metadata())
+        {
+          sql += "UNION ALL SELECT "
+                "  " TOSTRING(QUERY_GRAND_PARENT_METADATA) " AS c0_queryId, "
+                "  Lookup.internalId AS c1_internalId, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                "  value AS c3_string1, "
+                "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                "  type AS c6_int1, "
+                + revisionInC7 +
+                "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c10_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 ";
+        }
+      }
+    }
+
+    // need MainDicomTags from children ?
+    if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES)
+    {
+      const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec = NULL;
+      switch (request.level())
+      {
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
+        childrenSpec = &(request.children_studies());
+        break;
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
+        childrenSpec = &(request.children_series());
+        break;
+      case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES:
+        childrenSpec = &(request.children_instances());
+        break;
+      
+      default:
+        break;
+      }
+
+      if (childrenSpec->retrieve_main_dicom_tags_size() > 0)
+      {
+        sql += "UNION ALL SELECT "
+               "  " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+               "  value AS c3_string1, "
+               "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+               "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+               "  tagGroup AS c6_int1, "
+               "  tagElement AS c7_int2, "
+               "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+               "FROM Lookup "
+               "  INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
+               "  INNER JOIN MainDicomTags ON MainDicomTags.id = childLevel.internalId AND (tagGroup, tagElement) IN (" + JoinRequestedTags(childrenSpec) + ")";
+      }
+
+      // need children identifiers ?
+      if (childrenSpec->retrieve_identifiers())  
+      {
+        sql += "UNION ALL SELECT "
+               "  " TOSTRING(QUERY_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
+               "  Lookup.internalId AS c1_internalId, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+               "  childLevel.publicId AS c3_string1, "
+               "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+               "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+               "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+               "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+               "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+               "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+               "FROM Lookup "
+               "  INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId ";
+      }
+      else if (childrenSpec->retrieve_count())  // no need to count if we have retrieved the list of identifiers
+      {
+        if (HasChildCountTable())  // TODO: rename in HasChildCountColumn ?
+        {
+          // // we get the count value either from the childCount table if it has been computed or from the Resources table
+          // sql += "UNION ALL SELECT "
+          //       "  " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, "
+          //       "  Lookup.internalId AS c1_internalId, "
+          //       "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+          //       "  " + formatter.FormatNull("TEXT") + " AS c3_string1, "
+          //       "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+          //       "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+          //       "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+          //       "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+          //       "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+          //       "  COALESCE("
+          //       "           (ChildCount.childCount),"
+          //       "        		(SELECT COUNT(childLevel.internalId)"
+          //       "            FROM Resources AS childLevel"
+          //       "            WHERE Lookup.internalId = childLevel.parentId"
+          //       "           )) AS c9_big_int1, "
+          //       "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+          //       "FROM Lookup "
+          //       "LEFT JOIN ChildCount ON Lookup.internalId = ChildCount.parentId ";
+
+          // we get the count value either from the childCount column if it has been computed or from the Resources table
+          sql += "UNION ALL SELECT "
+                "  " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, "
+                "  Lookup.internalId AS c1_internalId, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                "  " + formatter.FormatNull("TEXT") + " AS c3_string1, "
+                "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+                "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+                "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                "  COALESCE("
+                "           (Resources.childCount),"
+                "        		(SELECT COUNT(childLevel.internalId)"
+                "            FROM Resources AS childLevel"
+                "            WHERE Lookup.internalId = childLevel.parentId"
+                "           )) AS c9_big_int1, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                "FROM Lookup "
+                "LEFT JOIN Resources ON Lookup.internalId = Resources.internalId ";
+        }
+        else
+        {
+          sql += "UNION ALL SELECT "
+                "  " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, "
+                "  Lookup.internalId AS c1_internalId, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                "  " + formatter.FormatNull("TEXT") + " AS c3_string1, "
+                "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+                "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+                "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                "  COUNT(childLevel.internalId) AS c9_big_int1, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                "FROM Lookup "
+                "  INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId GROUP BY Lookup.internalId ";
+        }
+      }
+
+      if (childrenSpec->retrieve_metadata_size() > 0)
+      {
+        sql += "UNION ALL SELECT "
+                "  " TOSTRING(QUERY_CHILDREN_METADATA) " AS c0_queryId, "
+                "  Lookup.internalId AS c1_internalId, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                "  value AS c3_string1, "
+                "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                "  type AS c6_int1, "
+                + revisionInC7 +
+                "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c10_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(childrenSpec) + ") ";
+      }
+
+      if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY)
+      {
+        const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandchildrenSpec = NULL;
+        switch (request.level())
+        {
+        case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT:
+          grandchildrenSpec = &(request.children_series());
+          break;
+        case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY:
+          grandchildrenSpec = &(request.children_instances());
+          break;
+        
+        default:
+          break;
+        }
+
+        // need grand children identifiers ?
+        if (grandchildrenSpec->retrieve_identifiers())  
+        {
+          sql += "UNION ALL SELECT "
+                "  " TOSTRING(QUERY_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
+                "  Lookup.internalId AS c1_internalId, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                "  grandChildLevel.publicId AS c3_string1, "
+                "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+                "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+                "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+                "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                "FROM Lookup "
+                "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
+                "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId ";
+        }
+        else if (grandchildrenSpec->retrieve_count())  // no need to count if we have retrieved the list of identifiers
+        {
+          if (HasChildCountTable())
+          {
+            // // we get the count value either from the childCount table if it has been computed or from the Resources table
+            // sql += "UNION ALL SELECT "
+            //       "  " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, "
+            //       "  Lookup.internalId AS c1_internalId, "
+            //       "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+            //       "  " + formatter.FormatNull("TEXT") + " AS c3_string1, "
+            //       "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+            //       "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+            //       "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+            //       "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+            //       "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+            //       "  COALESCE("
+		        //       "           (SELECT SUM(ChildCount.childCount)"
+		        //       "            FROM ChildCount"
+            //       "            INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId"
+            //       "            WHERE ChildCount.parentId = childLevel.internalId),"
+            //       "        		(SELECT COUNT(grandChildLevel.internalId)"
+            //       "            FROM Resources AS childLevel"
+            //       "            INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId"
+            //       "            WHERE Lookup.internalId = childLevel.parentId"
+            //       "           )) AS c9_big_int1, "
+            //       "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+            //       "FROM Lookup ";
+
+            // we get the count value either from the childCount column if it has been computed or from the Resources table
+            sql += "UNION ALL SELECT "
+                  "  " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, "
+                  "  Lookup.internalId AS c1_internalId, "
+                  "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                  "  " + formatter.FormatNull("TEXT") + " AS c3_string1, "
+                  "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                  "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                  "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+                  "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+                  "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                  "  COALESCE("
+		              "           (SELECT SUM(childLevel.childCount)"
+		              "            FROM Resources AS childLevel"
+                  "            WHERE childLevel.parentId = Lookup.internalId),"
+                  "        		(SELECT COUNT(grandChildLevel.internalId)"
+                  "            FROM Resources AS childLevel"
+                  "            INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId"
+                  "            WHERE Lookup.internalId = childLevel.parentId"
+                  "           )) AS c9_big_int1, "
+                  "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                  "FROM Lookup ";
+          }
+          else
+          {
+            sql += "UNION ALL SELECT "
+                  "  " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, "
+                  "  Lookup.internalId AS c1_internalId, "
+                  "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                  "  " + formatter.FormatNull("TEXT") + " AS c3_string1, "
+                  "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                  "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                  "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+                  "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+                  "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                  "  COUNT(grandChildLevel.internalId) AS c9_big_int1, "
+                  "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                  "FROM Lookup "
+                  "  INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
+                  "  INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId GROUP BY Lookup.internalId ";
+          }
+        }
+
+        if (grandchildrenSpec->retrieve_main_dicom_tags_size() > 0)
+        {
+          sql += "UNION ALL SELECT "
+                 "  " TOSTRING(QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, "
+                 "  Lookup.internalId AS c1_internalId, "
+                 "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                 "  value AS c3_string1, "
+                 "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                 "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                 "  tagGroup AS c6_int1, "
+                 "  tagElement AS c7_int2, "
+                 "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                 "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+                 "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                 "FROM Lookup "
+                 "  INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
+                 "  INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId "
+                 "  INNER JOIN MainDicomTags ON MainDicomTags.id = grandChildLevel.internalId AND (tagGroup, tagElement) IN (" + JoinRequestedTags(grandchildrenSpec) + ")";
+        }
+
+        if (grandchildrenSpec->retrieve_metadata_size() > 0)
+        {
+          sql += "UNION ALL SELECT "
+                 "  " TOSTRING(QUERY_GRAND_CHILDREN_METADATA) " AS c0_queryId, "
+                 "  Lookup.internalId AS c1_internalId, "
+                 "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                 "  value AS c3_string1, "
+                 "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                 "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                 "  type AS c6_int1, "
+                 + revisionInC7 +
+                 "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                 "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+                 "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                 "FROM Lookup "
+                 "  INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId "
+                 "  INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId "
+                 "  INNER JOIN Metadata ON Metadata.id = grandChildLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(grandchildrenSpec) + ") ";
+        }
+
+        if (request.level() == Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT)
+        {
+          const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandgrandchildrenSpec = &(request.children_instances());
+
+          // need grand children identifiers ?
+          if (grandgrandchildrenSpec->retrieve_identifiers())  
+          {
+            sql += "UNION ALL SELECT "
+                  "  " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, "
+                  "  Lookup.internalId AS c1_internalId, "
+                  "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                  "  grandGrandChildLevel.publicId AS c3_string1, "
+                  "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                  "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                  "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+                  "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+                  "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                  "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+                  "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                  "FROM Lookup "
+                  "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
+                  "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "
+                  "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId ";
+          }
+          else if (grandgrandchildrenSpec->retrieve_count())  // no need to count if we have retrieved the list of identifiers
+          {
+            if (HasChildCountTable())
+            {
+              // // we get the count value either from the childCount table if it has been computed or from the Resources table
+              // sql += "UNION ALL SELECT "
+              //       "  " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, "
+              //       "  Lookup.internalId AS c1_internalId, "
+              //       "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+              //       "  " + formatter.FormatNull("TEXT") + " AS c3_string1, "
+              //       "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+              //       "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+              //       "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+              //       "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+              //       "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+              //       "  COALESCE("
+              //       "           (SELECT SUM(ChildCount.childCount)"
+              //       "            FROM ChildCount"
+              //       "            INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId"
+              //       "            INNER JOIN Resources AS grandChildLevel ON grandChildLevel.parentId = childLevel.internalId"
+              //       "            WHERE ChildCount.parentId = grandChildLevel.internalId),"
+              //       "        		(SELECT COUNT(grandGrandChildLevel.internalId)"
+              //       "            FROM Resources AS childLevel"
+              //       "            INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId"
+              //       "            INNER JOIN Resources AS grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId"
+              //       "            WHERE Lookup.internalId = childLevel.parentId"
+              //       "           )) AS c9_big_int1, "
+              //       "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+              //       "FROM Lookup ";
+
+              // we get the count value either from the childCount column if it has been computed or from the Resources table
+              sql += "UNION ALL SELECT "
+                    "  " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, "
+                    "  Lookup.internalId AS c1_internalId, "
+                    "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                    "  " + formatter.FormatNull("TEXT") + " AS c3_string1, "
+                    "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                    "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                    "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+                    "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+                    "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                    "  COALESCE("
+                    "           (SELECT SUM(grandChildLevel.childCount)"
+                    "            FROM Resources AS grandChildLevel"
+                    "            INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId"
+                    "            WHERE grandChildLevel.parentId = childLevel.internalId),"
+                    "        		(SELECT COUNT(grandGrandChildLevel.internalId)"
+                    "            FROM Resources AS childLevel"
+                    "            INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId"
+                    "            INNER JOIN Resources AS grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId"
+                    "            WHERE Lookup.internalId = childLevel.parentId"
+                    "           )) AS c9_big_int1, "
+                    "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                    "FROM Lookup ";
+            }
+            else
+            {
+              sql += "UNION ALL SELECT "
+                    "  " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, "
+                    "  Lookup.internalId AS c1_internalId, "
+                    "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+                    "  " + formatter.FormatNull("TEXT") + " AS c3_string1, "
+                    "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+                    "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+                    "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+                    "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+                    "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+                    "  COUNT(grandChildLevel.internalId) AS c9_big_int1, "
+                    "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+                    "FROM Lookup "
+                    "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "
+                    "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "
+                    "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId GROUP BY Lookup.internalId ";
+            }
+          }
+        }
+      }
+    }
+
+    // need parent identifier ?
+    if (request.retrieve_parent_identifier())
+    {
+      sql += "UNION ALL SELECT "
+             "  " TOSTRING(QUERY_PARENT_IDENTIFIER) " AS c0_queryId, "
+             "  Lookup.internalId AS c1_internalId, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+             "  parentLevel.publicId AS c3_string1, "
+             "  " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+             "  " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+             "  " + formatter.FormatNull("INT") + " AS c6_int1, "
+             "  " + formatter.FormatNull("INT") + " AS c7_int2, "
+             "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+             "  " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+             "FROM Lookup "
+             "  INNER JOIN Resources currentLevel ON currentLevel.internalId = Lookup.internalId "
+             "  INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId ";
+    }
+
+    // need one instance info ?
+    if (request.level() != Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE &&
+        request.retrieve_one_instance_metadata_and_attachments())
+    {
+      sql += "   UNION ALL SELECT"
+             "    " TOSTRING(QUERY_ONE_INSTANCE_IDENTIFIER) " AS c0_queryId, "
+             "    parentInternalId AS c1_internalId, "
+             "    " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+             "    instancePublicId AS c3_string1, "
+             "    " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+             "    " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+             "    " + formatter.FormatNull("INT") + " AS c6_int1, "
+             "    " + formatter.FormatNull("INT") + " AS c7_int2, "
+             "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+             "    instanceInternalId AS c9_big_int1, "
+             "    " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+             "   FROM OneInstance ";
+
+      sql += "   UNION ALL SELECT"
+             "    " TOSTRING(QUERY_ONE_INSTANCE_METADATA) " AS c0_queryId, "
+             "    parentInternalId AS c1_internalId, "
+             "    " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+             "    Metadata.value AS c3_string1, "
+             "    " + formatter.FormatNull("TEXT") + " AS c4_string2, "
+             "    " + formatter.FormatNull("TEXT") + " AS c5_string3, "
+             "    Metadata.type AS c6_int1, "
+             + revisionInC7 +
+             "  " + formatter.FormatNull("INT") + " AS c8_int3, "
+             "    " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, "
+             "    " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 "
+             "   FROM Metadata "
+             "   INNER JOIN OneInstance ON Metadata.id = OneInstance.instanceInternalId";
+             
+      sql += "   UNION ALL SELECT"
+             "    " TOSTRING(QUERY_ONE_INSTANCE_ATTACHMENTS) " AS c0_queryId, "
+             "    parentInternalId AS c1_internalId, "
+             "    " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, "
+             "    uuid AS c3_string1, "
+             "    uncompressedHash AS c4_string2, "
+             "    compressedHash AS c5_string3, "
+             "    fileType AS c6_int1, "
+             + revisionInC7 +
+             "    compressionType AS c8_int3, "
+             "    compressedSize AS c9_big_int1, "
+             "    uncompressedSize AS c10_big_int2 "
+             "   FROM AttachedFiles "
+             "   INNER JOIN OneInstance ON AttachedFiles.id = OneInstance.instanceInternalId";
+
+      // sql += "  ) ";
+
+    }
+
+    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 !
+
+    std::unique_ptr<DatabaseManager::StatementBase> statement;
+    if (manager.GetDialect() == Dialect_MySQL)
+    { // TODO: investigate why "complex" cached statement do not seem to work properly in MySQL
+      statement.reset(new DatabaseManager::StandaloneStatement(manager, sql));
+    }
+    else
+    {
+      statement.reset(new DatabaseManager::CachedStatement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql));
+    }
+    
+    statement->Execute(formatter.GetDictionary());
+    
+    // LOG(INFO) << sql;
+
+    std::map<int64_t, Orthanc::DatabasePluginMessages::Find_Response*> responses;
+
+    while (!statement->IsDone())
+    {
+      int32_t queryId = statement->ReadInteger32(C0_QUERY_ID);
+      int64_t internalId = statement->ReadInteger64(C1_INTERNAL_ID);
+      
+      assert(queryId == QUERY_LOOKUP || responses.find(internalId) != responses.end()); // the QUERY_LOOKUP must be read first and must create the response before any other query tries to populate the fields
+
+      // LOG(INFO) << queryId << "  " << statement->ReadString(C3_STRING_1);
+
+      switch (queryId)
+      {
+        case QUERY_LOOKUP:
+          responses[internalId] = response.add_find();
+          responses[internalId]->set_public_id(statement->ReadString(C3_STRING_1));
+          responses[internalId]->set_internal_id(internalId);
+          break;
+
+        case QUERY_LABELS:
+          responses[internalId]->add_labels(statement->ReadString(C3_STRING_1));
+          break;
+
+        case QUERY_MAIN_DICOM_TAGS:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level());
+          Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
+
+          tag->set_value(statement->ReadString(C3_STRING_1));
+          tag->set_group(statement->ReadInteger32(C6_INT_1));
+          tag->set_element(statement->ReadInteger32(C7_INT_2));
+          }; break;
+
+        case QUERY_PARENT_MAIN_DICOM_TAGS:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 1));
+          Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
+
+          tag->set_value(statement->ReadString(C3_STRING_1));
+          tag->set_group(statement->ReadInteger32(C6_INT_1));
+          tag->set_element(statement->ReadInteger32(C7_INT_2));
+        }; break;
+
+        case QUERY_GRAND_PARENT_MAIN_DICOM_TAGS:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 2));
+          Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
+
+          tag->set_value(statement->ReadString(C3_STRING_1));
+          tag->set_group(statement->ReadInteger32(C6_INT_1));
+          tag->set_element(statement->ReadInteger32(C7_INT_2));
+        }; break;
+
+        case QUERY_CHILDREN_IDENTIFIERS:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
+          content->add_identifiers(statement->ReadString(C3_STRING_1));
+          content->set_count(content->identifiers_size());
+        }; break;
+
+        case QUERY_CHILDREN_COUNT:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
+          content->set_count(statement->ReadInteger64(C9_BIG_INT_1));
+        }; break;
+
+        case QUERY_CHILDREN_MAIN_DICOM_TAGS:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
+          Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
+          tag->set_value(statement->ReadString(C3_STRING_1)); // TODO: handle sequences ??
+          tag->set_group(statement->ReadInteger32(C6_INT_1));
+          tag->set_element(statement->ReadInteger32(C7_INT_2));
+        }; break;
+
+        case QUERY_CHILDREN_METADATA:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1));
+          Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
+
+          metadata->set_value(statement->ReadString(C3_STRING_1));
+          metadata->set_key(statement->ReadInteger32(C6_INT_1));
+          metadata->set_revision(0);  // Setting a revision is not required in this case, as of Orthanc 1.12.5
+        }; break;
+
+        case QUERY_GRAND_CHILDREN_IDENTIFIERS:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
+          content->add_identifiers(statement->ReadString(C3_STRING_1));
+          content->set_count(content->identifiers_size());
+        }; break;
+
+        case QUERY_GRAND_CHILDREN_COUNT:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
+          content->set_count(statement->ReadInteger64(C9_BIG_INT_1));
+        }; break;
+
+        case QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
+          Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags();
+
+          tag->set_value(statement->ReadString(C3_STRING_1)); // TODO: handle sequences ??
+          tag->set_group(statement->ReadInteger32(C6_INT_1));
+          tag->set_element(statement->ReadInteger32(C7_INT_2));
+        }; break;
+
+        case QUERY_GRAND_CHILDREN_METADATA:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2));
+          Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
+
+          metadata->set_value(statement->ReadString(C3_STRING_1));
+          metadata->set_key(statement->ReadInteger32(C6_INT_1));
+          metadata->set_revision(0);  // Setting a revision is not required in this case, as of Orthanc 1.12.5
+        }; break;
+
+        case QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 3));
+          content->add_identifiers(statement->ReadString(C3_STRING_1));
+          content->set_count(content->identifiers_size());
+        }; break;
+
+        case QUERY_GRAND_GRAND_CHILDREN_COUNT:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 3));
+          content->set_count(statement->ReadInteger64(C9_BIG_INT_1));
+        }; break;
+
+        case QUERY_ATTACHMENTS:
+        {
+          Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_attachments();
+
+          attachment->set_uuid(statement->ReadString(C3_STRING_1));
+          attachment->set_uncompressed_hash(statement->ReadString(C4_STRING_2));
+          attachment->set_compressed_hash(statement->ReadString(C5_STRING_3));
+          attachment->set_content_type(statement->ReadInteger32(C6_INT_1));
+          attachment->set_compression_type(statement->ReadInteger32(C8_INT_3));
+          attachment->set_compressed_size(statement->ReadInteger64(C9_BIG_INT_1));
+          attachment->set_uncompressed_size(statement->ReadInteger64(C10_BIG_INT_2));
+
+          if (!statement->IsNull(C7_INT_2))  // revision can be null for files that have been atttached by older Orthanc versions
+          {
+            responses[internalId]->add_attachments_revisions(statement->ReadInteger32(C7_INT_2));
+          }
+          else
+          {
+            responses[internalId]->add_attachments_revisions(0);
+          }
+        }; break;
+
+        case QUERY_METADATA:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level());
+          Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
+
+          metadata->set_value(statement->ReadString(C3_STRING_1));
+          metadata->set_key(statement->ReadInteger32(C6_INT_1));
+          
+          if (!statement->IsNull(C7_INT_2))  // revision can be null for metadata that have been created by older Orthanc versions
+          {
+            metadata->set_revision(statement->ReadInteger32(C7_INT_2));
+          }
+          else
+          {
+            metadata->set_revision(0);
+          }
+        }; break;
+
+        case QUERY_PARENT_METADATA:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 1));
+          Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
+
+          metadata->set_value(statement->ReadString(C3_STRING_1));
+          metadata->set_key(statement->ReadInteger32(C6_INT_1));
+
+          if (!statement->IsNull(C7_INT_2))  // revision can be null for metadata that have been created by older Orthanc versions
+          {
+            metadata->set_revision(statement->ReadInteger32(C7_INT_2));
+          }
+          else
+          {
+            metadata->set_revision(0);
+          }
+        }; break;
+
+        case QUERY_GRAND_PARENT_METADATA:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 2));
+          Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata();
+
+          metadata->set_value(statement->ReadString(C3_STRING_1));
+          metadata->set_key(statement->ReadInteger32(C6_INT_1));
+
+          if (!statement->IsNull(C7_INT_2))  // revision can be null for metadata that have been created by older Orthanc versions
+          {
+            metadata->set_revision(statement->ReadInteger32(C7_INT_2));
+          }
+          else
+          {
+            metadata->set_revision(0);
+          }
+        }; break;
+
+        case QUERY_PARENT_IDENTIFIER:
+        {
+          responses[internalId]->set_parent_public_id(statement->ReadString(C3_STRING_1));
+        }; break;
+
+        case QUERY_ONE_INSTANCE_IDENTIFIER:
+        {
+          responses[internalId]->set_one_instance_public_id(statement->ReadString(C3_STRING_1));
+        }; break;
+        case QUERY_ONE_INSTANCE_METADATA:
+        {
+          Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = responses[internalId]->add_one_instance_metadata();
+
+          metadata->set_value(statement->ReadString(C3_STRING_1));
+          metadata->set_key(statement->ReadInteger32(C6_INT_1));
+
+          if (!statement->IsNull(C7_INT_2))  // revision can be null for metadata that have been created by older Orthanc versions
+          {
+            metadata->set_revision(statement->ReadInteger32(C7_INT_2));
+          }
+          else
+          {
+            metadata->set_revision(0);
+          }
+        }; break;
+        case QUERY_ONE_INSTANCE_ATTACHMENTS:
+        {
+          Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_one_instance_attachments();
+          
+          attachment->set_uuid(statement->ReadString(C3_STRING_1));
+          attachment->set_uncompressed_hash(statement->ReadString(C4_STRING_2));
+          attachment->set_compressed_hash(statement->ReadString(C5_STRING_3));
+          attachment->set_content_type(statement->ReadInteger32(C6_INT_1));
+          attachment->set_compression_type(statement->ReadInteger32(C8_INT_3));
+          attachment->set_compressed_size(statement->ReadInteger64(C9_BIG_INT_1));
+          attachment->set_uncompressed_size(statement->ReadInteger64(C10_BIG_INT_2));
+        }; break;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+      }
+      statement->Next();
+    }    
+  }
+#endif
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/IndexBackend.h
--- a/Framework/Plugins/IndexBackend.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/IndexBackend.h	Mon Dec 16 16:36:12 2024 +0100
@@ -42,6 +42,7 @@
     class LookupFormatter;
 
     OrthancPluginContext*  context_;
+    bool                   readOnly_;
 
     boost::shared_mutex                                outputFactoryMutex_;
     std::unique_ptr<IDatabaseBackendOutput::IFactory>  outputFactory_;
@@ -53,19 +54,27 @@
 
     virtual void ClearRemainingAncestor(DatabaseManager& manager);
 
+    virtual bool HasChildCountTable() const = 0;
+
     void SignalDeletedFiles(IDatabaseBackendOutput& output,
                             DatabaseManager& manager);
 
     void SignalDeletedResources(IDatabaseBackendOutput& output,
                                 DatabaseManager& manager);
 
+    bool IsReadOnly()
+    {
+      return readOnly_;
+    }
+
   private:
     void ReadChangesInternal(IDatabaseBackendOutput& output,
                              bool& done,
                              DatabaseManager& manager,
                              DatabaseManager::CachedStatement& statement,
                              const Dictionary& args,
-                             uint32_t limit);
+                             uint32_t limit,
+                             bool returnFirstResults);
 
     void ReadExportedResourcesInternal(IDatabaseBackendOutput& output,
                                        bool& done,
@@ -74,7 +83,8 @@
                                        uint32_t limit);
 
   public:
-    explicit IndexBackend(OrthancPluginContext* context);
+    explicit IndexBackend(OrthancPluginContext* context,
+                          bool readOnly);
 
     virtual OrthancPluginContext* GetContext() ORTHANC_OVERRIDE
     {
@@ -130,7 +140,15 @@
                             DatabaseManager& manager,
                             int64_t since,
                             uint32_t limit) ORTHANC_OVERRIDE;
-    
+
+    virtual void GetChangesExtended(IDatabaseBackendOutput& output,
+                                    bool& done /*out*/,
+                                    DatabaseManager& manager,
+                                    int64_t since,
+                                    int64_t to,
+                                    const std::set<uint32_t>& changeTypes,
+                                    uint32_t limit) ORTHANC_OVERRIDE;
+
     virtual void GetChildrenInternalId(std::list<int64_t>& target /*out*/,
                                        DatabaseManager& manager,
                                        int64_t id) ORTHANC_OVERRIDE;
@@ -420,6 +438,34 @@
 
     virtual uint64_t MeasureLatency(DatabaseManager& manager) ORTHANC_OVERRIDE;
 
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+    // New primitives since Orthanc 1.12.5
+    virtual bool HasExtendedChanges() const ORTHANC_OVERRIDE
+    {
+      return true;
+    }
+
+    virtual bool HasFindSupport() const ORTHANC_OVERRIDE;
+
+    virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                             DatabaseManager& manager,
+                             const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE;
+
+    virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                              DatabaseManager& manager,
+                              const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE;
+#endif
+
+    virtual bool HasPerformDbHousekeeping() ORTHANC_OVERRIDE
+    {
+      return false;
+    }
+
+    virtual void PerformDbHousekeeping(DatabaseManager& manager) ORTHANC_OVERRIDE
+    {
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+    }
+
     /**
      * "maxDatabaseRetries" is to handle
      * "OrthancPluginErrorCode_DatabaseCannotSerialize" if there is a
@@ -428,7 +474,8 @@
      **/
     static void Register(IndexBackend* backend,
                          size_t countConnections,
-                         unsigned int maxDatabaseRetries);
+                         unsigned int maxDatabaseRetries,
+                         unsigned int housekeepingDelaySeconds);
 
     static void Finalize();
 
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/IndexConnectionsPool.cpp
--- a/Framework/Plugins/IndexConnectionsPool.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/IndexConnectionsPool.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -23,6 +23,9 @@
 
 #include "IndexConnectionsPool.h"
 
+#include <Logging.h>
+
+
 namespace OrthancDatabases
 {
   class IndexConnectionsPool::ManagerReference : public Orthanc::IDynamicObject
@@ -44,10 +47,44 @@
   };
 
 
+  void IndexConnectionsPool::HousekeepingThread(IndexConnectionsPool* that)
+  {
+    boost::posix_time::ptime lastInvocation = boost::posix_time::second_clock::local_time();
+
+    while (that->housekeepingContinue_)
+    {
+      if (boost::posix_time::second_clock::local_time() - lastInvocation >= that->housekeepingDelay_)
+      {
+        Accessor accessor(*that);
+
+        try
+        {
+          accessor.GetBackend().PerformDbHousekeeping(accessor.GetManager());
+        }
+        catch (Orthanc::OrthancException& e)
+        {
+          LOG(ERROR) << "Exception during the database housekeeping: " << e.What();
+        }
+        catch (...)
+        {
+          LOG(ERROR) << "Native exception during the database houskeeping";
+        }
+
+        lastInvocation = boost::posix_time::second_clock::local_time();
+      }
+
+      boost::this_thread::sleep(boost::posix_time::milliseconds(1000));
+    }
+  }
+
+
   IndexConnectionsPool::IndexConnectionsPool(IndexBackend* backend,
-                                             size_t countConnections) :
+                                             size_t countConnections,
+                                             unsigned int houseKeepingDelaySeconds) :
     backend_(backend),
-    countConnections_(countConnections)
+    countConnections_(countConnections),
+    housekeepingContinue_(true),
+    housekeepingDelay_(boost::posix_time::seconds(houseKeepingDelaySeconds))
   {
     if (countConnections == 0)
     {
@@ -58,6 +95,12 @@
     {
       throw Orthanc::OrthancException(Orthanc::ErrorCode_NullPointer);
     }
+    else if (backend->HasPerformDbHousekeeping() &&
+             houseKeepingDelaySeconds == 0)
+    {
+      throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange,
+                                      "The delay between two executions of housekeeping cannot be zero second");
+    }
     else
     {
       context_ = backend_->GetContext();
@@ -104,7 +147,15 @@
       {
         assert(*it != NULL);
         availableConnections_.Enqueue(new ManagerReference(**it));
-      }        
+      }
+
+      // Start the housekeeping thread
+      housekeepingContinue_ = true;
+
+      if (backend_->HasPerformDbHousekeeping())
+      {
+        housekeepingThread_ = boost::thread(HousekeepingThread, this);
+      }
     }
     else
     {
@@ -115,6 +166,15 @@
 
   void IndexConnectionsPool::CloseConnections()
   {
+    {
+      // Stop the housekeeping thread
+      housekeepingContinue_ = false;
+      if (housekeepingThread_.joinable())
+      {
+        housekeepingThread_.join();
+      }
+    }
+
     boost::unique_lock<boost::shared_mutex>  lock(connectionsMutex_);
 
     if (connections_.size() != countConnections_)
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/IndexConnectionsPool.h
--- a/Framework/Plugins/IndexConnectionsPool.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/IndexConnectionsPool.h	Mon Dec 16 16:36:12 2024 +0100
@@ -29,6 +29,7 @@
 #include <MultiThreading/SharedMessageQueue.h>
 
 #include <list>
+#include <boost/thread.hpp>
 
 namespace OrthancDatabases
 {
@@ -43,10 +44,16 @@
     size_t                         countConnections_;
     std::list<DatabaseManager*>    connections_;
     Orthanc::SharedMessageQueue    availableConnections_;
+    bool                           housekeepingContinue_;
+    boost::thread                  housekeepingThread_;
+    boost::posix_time::time_duration  housekeepingDelay_;
+
+    static void HousekeepingThread(IndexConnectionsPool* that);
 
   public:
     IndexConnectionsPool(IndexBackend* backend /* takes ownership */,
-                         size_t countConnections);
+                         size_t countConnections,
+                         unsigned int houseKeepingDelaySeconds);
 
     ~IndexConnectionsPool();
 
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/IndexUnitTests.h
--- a/Framework/Plugins/IndexUnitTests.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/Plugins/IndexUnitTests.h	Mon Dec 16 16:36:12 2024 +0100
@@ -29,8 +29,6 @@
 
 #include <Compatibility.h>  // For std::unique_ptr<>
 
-#include <orthanc/OrthancCDatabasePlugin.h>
-
 #include <gtest/gtest.h>
 #include <list>
 
@@ -232,13 +230,13 @@
   ImplicitTransaction::SetErrorOnDoubleExecution(true);
 
 #if ORTHANC_ENABLE_POSTGRESQL == 1
-  PostgreSQLIndex db(&context, globalParameters_);
+  PostgreSQLIndex db(&context, globalParameters_, false);
   db.SetClearAll(true);
 #elif ORTHANC_ENABLE_MYSQL == 1
-  MySQLIndex db(&context, globalParameters_);
+  MySQLIndex db(&context, globalParameters_, false);
   db.SetClearAll(true);
 #elif ORTHANC_ENABLE_ODBC == 1
-  OdbcIndex db(&context, connectionString_);
+  OdbcIndex db(&context, connectionString_, false);
 #elif ORTHANC_ENABLE_SQLITE == 1  // Must be the last one
   SQLiteIndex db(&context);  // Open in memory
 #else
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/MessagesToolbox.cpp
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Plugins/MessagesToolbox.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,172 @@
+/**
+ * Orthanc - A Lightweight, RESTful DICOM Store
+ * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
+ * Department, University Hospital of Liege, Belgium
+ * Copyright (C) 2017-2023 Osimis S.A., Belgium
+ * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
+ * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
+ *
+ * This program is free software: you can redistribute it and/or
+ * modify it under the terms of the GNU Affero General Public License
+ * as published by the Free Software Foundation, either version 3 of
+ * the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Affero General Public License for more details.
+ * 
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ **/
+
+
+#include "MessagesToolbox.h"
+
+
+namespace OrthancDatabases
+{
+  namespace MessagesToolbox
+  {
+    Orthanc::ResourceType Convert(Orthanc::DatabasePluginMessages::ResourceType resourceType)
+    {
+      switch (resourceType)
+      {
+        case Orthanc::DatabasePluginMessages::RESOURCE_PATIENT:
+          return Orthanc::ResourceType_Patient;
+
+        case Orthanc::DatabasePluginMessages::RESOURCE_STUDY:
+          return Orthanc::ResourceType_Study;
+
+        case Orthanc::DatabasePluginMessages::RESOURCE_SERIES:
+          return Orthanc::ResourceType_Series;
+
+        case Orthanc::DatabasePluginMessages::RESOURCE_INSTANCE:
+          return Orthanc::ResourceType_Instance;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+      }
+    }
+
+
+    OrthancPluginResourceType ConvertToPlainC(Orthanc::ResourceType type)
+    {
+      switch (type)
+      {
+        case Orthanc::ResourceType_Patient:
+          return OrthancPluginResourceType_Patient;
+
+        case Orthanc::ResourceType_Study:
+          return OrthancPluginResourceType_Study;
+
+        case Orthanc::ResourceType_Series:
+          return OrthancPluginResourceType_Series;
+
+        case Orthanc::ResourceType_Instance:
+          return OrthancPluginResourceType_Instance;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+      }
+    }
+
+
+    Orthanc::ResourceType Convert(OrthancPluginResourceType type)
+    {
+      switch (type)
+      {
+        case OrthancPluginResourceType_Patient:
+          return Orthanc::ResourceType_Patient;
+
+        case OrthancPluginResourceType_Study:
+          return Orthanc::ResourceType_Study;
+
+        case OrthancPluginResourceType_Series:
+          return Orthanc::ResourceType_Series;
+
+        case OrthancPluginResourceType_Instance:
+          return Orthanc::ResourceType_Instance;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+      }
+    }
+
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
+    OrthancPluginConstraintType ConvertToPlainC(ConstraintType constraint)
+    {
+      switch (constraint)
+      {
+        case ConstraintType_Equal:
+          return OrthancPluginConstraintType_Equal;
+
+        case ConstraintType_GreaterOrEqual:
+          return OrthancPluginConstraintType_GreaterOrEqual;
+
+        case ConstraintType_SmallerOrEqual:
+          return OrthancPluginConstraintType_SmallerOrEqual;
+
+        case ConstraintType_Wildcard:
+          return OrthancPluginConstraintType_Wildcard;
+
+        case ConstraintType_List:
+          return OrthancPluginConstraintType_List;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+      }
+    }
+#endif
+
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
+    ConstraintType Convert(OrthancPluginConstraintType constraint)
+    {
+      switch (constraint)
+      {
+        case OrthancPluginConstraintType_Equal:
+          return ConstraintType_Equal;
+
+        case OrthancPluginConstraintType_GreaterOrEqual:
+          return ConstraintType_GreaterOrEqual;
+
+        case OrthancPluginConstraintType_SmallerOrEqual:
+          return ConstraintType_SmallerOrEqual;
+
+        case OrthancPluginConstraintType_Wildcard:
+          return ConstraintType_Wildcard;
+
+        case OrthancPluginConstraintType_List:
+          return ConstraintType_List;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+      }
+    }
+#endif
+
+
+    Orthanc::DatabasePluginMessages::ResourceType ConvertToProtobuf(OrthancPluginResourceType resourceType)
+    {
+      switch (resourceType)
+      {
+        case OrthancPluginResourceType_Patient:
+          return Orthanc::DatabasePluginMessages::RESOURCE_PATIENT;
+
+        case OrthancPluginResourceType_Study:
+          return Orthanc::DatabasePluginMessages::RESOURCE_STUDY;
+
+        case OrthancPluginResourceType_Series:
+          return Orthanc::DatabasePluginMessages::RESOURCE_SERIES;
+
+        case OrthancPluginResourceType_Instance:
+          return Orthanc::DatabasePluginMessages::RESOURCE_INSTANCE;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
+      }
+    }
+  }
+}
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/Plugins/MessagesToolbox.h
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Plugins/MessagesToolbox.h	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,92 @@
+/**
+ * Orthanc - A Lightweight, RESTful DICOM Store
+ * Copyright (C) 2012-2016 Sebastien Jodogne, Medical Physics
+ * Department, University Hospital of Liege, Belgium
+ * Copyright (C) 2017-2023 Osimis S.A., Belgium
+ * Copyright (C) 2024-2024 Orthanc Team SRL, Belgium
+ * Copyright (C) 2021-2024 Sebastien Jodogne, ICTEAM UCLouvain, Belgium
+ *
+ * This program is free software: you can redistribute it and/or
+ * modify it under the terms of the GNU Affero General Public License
+ * as published by the Free Software Foundation, either version 3 of
+ * the License, or (at your option) any later version.
+ *
+ * This program is distributed in the hope that it will be useful, but
+ * WITHOUT ANY WARRANTY; without even the implied warranty of
+ * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the GNU
+ * Affero General Public License for more details.
+ * 
+ * You should have received a copy of the GNU Affero General Public License
+ * along with this program. If not, see <http://www.gnu.org/licenses/>.
+ **/
+
+
+#pragma once
+
+#include <orthanc/OrthancCDatabasePlugin.h>
+#include <OrthancDatabasePlugin.pb.h>
+
+// Ensure that "ORTHANC_PLUGINS_VERSION_IS_ABOVE" is defined
+#include "../../Resources/Orthanc/Plugins/OrthancPluginCppWrapper.h"
+
+#define ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT 0
+
+#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE)
+#  if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 5, 2)
+#    undef  ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT
+#    define ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT 1
+#  endif
+#endif
+
+
+#define ORTHANC_PLUGINS_HAS_INTEGRATED_FIND 0
+
+#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE)
+#  if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+#    undef  ORTHANC_PLUGINS_HAS_INTEGRATED_FIND
+#    define ORTHANC_PLUGINS_HAS_INTEGRATED_FIND 1
+#  endif
+#endif
+
+
+#define ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED 0
+
+#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE)
+#  if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+#    undef  ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED
+#    define ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED 1
+#  endif
+#endif
+
+
+#include <Enumerations.h>
+
+
+namespace OrthancDatabases
+{
+  enum ConstraintType
+  {
+    ConstraintType_Equal,
+    ConstraintType_SmallerOrEqual,
+    ConstraintType_GreaterOrEqual,
+    ConstraintType_Wildcard,
+    ConstraintType_List
+  };
+
+  namespace MessagesToolbox
+  {
+    Orthanc::ResourceType Convert(Orthanc::DatabasePluginMessages::ResourceType resourceType);
+
+    OrthancPluginResourceType ConvertToPlainC(Orthanc::ResourceType type);
+
+    Orthanc::ResourceType Convert(OrthancPluginResourceType type);
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
+    OrthancPluginConstraintType ConvertToPlainC(ConstraintType constraint);
+#endif
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
+    ConstraintType Convert(OrthancPluginConstraintType constraint);
+#endif
+  }
+}
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/PostgreSQL/PostgreSQLDatabase.cpp
--- a/Framework/PostgreSQL/PostgreSQLDatabase.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/PostgreSQL/PostgreSQLDatabase.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -210,6 +210,26 @@
     return !result.IsDone();
   }
 
+  bool PostgreSQLDatabase::DoesIndexExist(const std::string& name)
+  {
+    std::string lower;
+    Orthanc::Toolbox::ToLowerCase(lower, name);
+
+    // http://stackoverflow.com/a/24089729/881731
+
+    PostgreSQLStatement statement(*this, 
+                                  "SELECT 1 FROM pg_catalog.pg_class c "
+                                  "JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace "
+                                  "WHERE n.nspname = 'public' AND c.relkind='i' "
+                                  "AND c.relname=$1");
+
+    statement.DeclareInputString(0);
+    statement.BindString(0, lower);
+
+    PostgreSQLResult result(statement);
+    return !result.IsDone();
+  }
+
 
   bool PostgreSQLDatabase::DoesColumnExist(const std::string& tableName,
                                            const std::string& columnName)
@@ -290,6 +310,11 @@
         return db_.DoesTableExist(name.c_str());
       }
 
+      virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
+      {
+        return db_.DoesIndexExist(name.c_str());
+      }
+
       virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
       {
         return false;
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/PostgreSQL/PostgreSQLDatabase.h
--- a/Framework/PostgreSQL/PostgreSQLDatabase.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/PostgreSQL/PostgreSQLDatabase.h	Mon Dec 16 16:36:12 2024 +0100
@@ -74,6 +74,8 @@
 
     void ExecuteMultiLines(const std::string& sql);
 
+    bool DoesIndexExist(const std::string& name);
+
     bool DoesTableExist(const std::string& name);
 
     bool DoesColumnExist(const std::string& tableName,
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/PostgreSQL/PostgreSQLParameters.cpp
--- a/Framework/PostgreSQL/PostgreSQLParameters.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/PostgreSQL/PostgreSQLParameters.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -102,7 +102,7 @@
     maxConnectionRetries_ = configuration.GetUnsignedIntegerValue("MaximumConnectionRetries", 10);
     connectionRetryInterval_ = configuration.GetUnsignedIntegerValue("ConnectionRetryInterval", 5);
 
-    std::string transactionMode = configuration.GetStringValue("TransactionMode", "Serializable");
+    std::string transactionMode = configuration.GetStringValue("TransactionMode", "ReadCommitted");
     if (transactionMode == "ReadCommitted")
     {
       LOG(WARNING) << "PostgreSQL: using READ COMMITTED transaction mode";
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/PostgreSQL/PostgreSQLStatement.cpp
--- a/Framework/PostgreSQL/PostgreSQLStatement.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/PostgreSQL/PostgreSQLStatement.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -26,6 +26,7 @@
 
 #include "../Common/BinaryStringValue.h"
 #include "../Common/InputFileValue.h"
+#include "../Common/Integer32Value.h"
 #include "../Common/Integer64Value.h"
 #include "../Common/NullValue.h"
 #include "../Common/ResultBase.h"
@@ -338,6 +339,10 @@
           DeclareInputInteger64(i);
           break;
 
+        case ValueType_Integer32:
+          DeclareInputInteger(i);
+          break;
+
         case ValueType_Utf8String:
           DeclareInputString(i);
           break;
@@ -529,6 +534,10 @@
           BindInteger64(i, dynamic_cast<const Integer64Value&>(parameters.GetValue(name)).GetValue());
           break;
 
+        case ValueType_Integer32:
+          BindInteger(i, dynamic_cast<const Integer32Value&>(parameters.GetValue(name)).GetValue());
+          break;
+
         case ValueType_Null:
           BindNull(i);
           break;
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/PostgreSQL/PostgreSQLTransaction.cpp
--- a/Framework/PostgreSQL/PostgreSQLTransaction.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/PostgreSQL/PostgreSQLTransaction.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -65,37 +65,28 @@
       LOG(ERROR) << "PostgreSQL: Beginning a transaction twice!";
       throw Orthanc::OrthancException(Orthanc::ErrorCode_BadSequenceOfCalls);
     }
-
-    database_.ExecuteMultiLines("BEGIN");
+    std::string transactionStatement;  // if not defined, will use the default DB transaction isolation level
 
     switch (type)
     {
       case TransactionType_ReadWrite:
       {
-        std::string statement = database_.GetReadWriteTransactionStatement();
-        if (!statement.empty()) // if not defined, will use the default DB transaction isolation level
-        {
-          database_.ExecuteMultiLines(statement);
-        }
-
+        transactionStatement = database_.GetReadWriteTransactionStatement();
         break;
       }
 
       case TransactionType_ReadOnly:
       {
-        std::string statement = database_.GetReadOnlyTransactionStatement();
-        if (!statement.empty()) // if not defined, will use the default DB transaction isolation level
-        {
-          database_.ExecuteMultiLines(statement);
-        }
-
+        transactionStatement = database_.GetReadOnlyTransactionStatement();
         break;
       }
 
       default:
         throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
     }
-        
+
+    database_.ExecuteMultiLines("BEGIN; " + transactionStatement);
+
     isOpen_ = true;
   }
 
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/PostgreSQL/PostgreSQLTransaction.h
--- a/Framework/PostgreSQL/PostgreSQLTransaction.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/PostgreSQL/PostgreSQLTransaction.h	Mon Dec 16 16:36:12 2024 +0100
@@ -67,6 +67,11 @@
       return database_.DoesTableExist(name.c_str());
     }
 
+    virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return database_.DoesIndexExist(name.c_str());
+    }
+
     virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
     {
       return false;
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/SQLite/SQLiteDatabase.cpp
--- a/Framework/SQLite/SQLiteDatabase.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/SQLite/SQLiteDatabase.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -77,6 +77,11 @@
         return db_.GetObject().DoesTableExist(name.c_str());
       }
 
+      virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
+      {
+        return false;
+      }
+
       virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
       {
         return false;
diff -r 987460ae14c9 -r 3d853c8f5a9e Framework/SQLite/SQLiteTransaction.h
--- a/Framework/SQLite/SQLiteTransaction.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Framework/SQLite/SQLiteTransaction.h	Mon Dec 16 16:36:12 2024 +0100
@@ -69,6 +69,11 @@
       return database_.GetObject().DoesTableExist(name.c_str());
     }
 
+    virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE
+    {
+      return false;  // Not implemented yet
+    }
+
     virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE
     {
       return false;
diff -r 987460ae14c9 -r 3d853c8f5a9e MySQL/NEWS
--- a/MySQL/NEWS	Mon Sep 09 15:21:14 2024 +0200
+++ b/MySQL/NEWS	Mon Dec 16 16:36:12 2024 +0100
@@ -1,3 +1,9 @@
+Pending changes in the mainline
+===============================
+
+* Added support for ExtendedChanges:
+  - changes?type=...&to=...
+* Added support for ExtendedFind
 * Fixed a memory leak when executing non cached SQL statements (rarely used)
 
 
diff -r 987460ae14c9 -r 3d853c8f5a9e MySQL/Plugins/IndexPlugin.cpp
--- a/MySQL/Plugins/IndexPlugin.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/MySQL/Plugins/IndexPlugin.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -29,7 +29,9 @@
 #include <Logging.h>
 #include <Toolbox.h>
 
-#include <google/protobuf/any.h>
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
+#  include <google/protobuf/any.h>
+#endif
 
 #define ORTHANC_PLUGIN_NAME "mysql-index"
 
@@ -37,7 +39,9 @@
 {
   ORTHANC_PLUGINS_API int32_t OrthancPluginInitialize(OrthancPluginContext* context)
   {
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
     GOOGLE_PROTOBUF_VERIFY_VERSION;
+#endif
 
     if (!OrthancDatabases::InitializePlugin(context, ORTHANC_PLUGIN_NAME, "MySQL", true))
     {
@@ -67,14 +71,22 @@
       return 0;
     }
 
+    bool readOnly = configuration.GetBooleanValue("ReadOnly", false);
+
+    if (readOnly)
+    {
+      LOG(WARNING) << "READ-ONLY SYSTEM: the Database plugin is working in read-only mode";
+    }
+
     try
     {
       const size_t countConnections = mysql.GetUnsignedIntegerValue("IndexConnectionsCount", 1);
+      const unsigned int housekeepingDelaySeconds = 5;  // TODO - PARAMETER
 
       OrthancDatabases::MySQLParameters parameters(mysql, configuration);
       OrthancDatabases::IndexBackend::Register(
-        new OrthancDatabases::MySQLIndex(context, parameters), countConnections,
-        parameters.GetMaxConnectionRetries());
+        new OrthancDatabases::MySQLIndex(context, parameters, readOnly), countConnections,
+        parameters.GetMaxConnectionRetries(), housekeepingDelaySeconds);
     }
     catch (Orthanc::OrthancException& e)
     {
@@ -99,7 +111,10 @@
     OrthancDatabases::MySQLDatabase::GlobalFinalization();
     Orthanc::HttpClient::GlobalFinalize();
     Orthanc::Toolbox::FinalizeOpenSsl();
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
     google::protobuf::ShutdownProtobufLibrary();
+#endif
   }
 
 
diff -r 987460ae14c9 -r 3d853c8f5a9e MySQL/Plugins/MySQLIndex.cpp
--- a/MySQL/Plugins/MySQLIndex.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/MySQL/Plugins/MySQLIndex.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -39,8 +39,9 @@
 namespace OrthancDatabases
 {
   MySQLIndex::MySQLIndex(OrthancPluginContext* context,
-                         const MySQLParameters& parameters) :
-    IndexBackend(context),
+                         const MySQLParameters& parameters,
+                         bool readOnly) :
+    IndexBackend(context, readOnly),
     parameters_(parameters),
     clearAll_(false)
   {
@@ -593,4 +594,32 @@
     }   
   }
 #endif
+
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  bool MySQLIndex::HasFindSupport() const
+  {
+    // TODO-FIND
+    return true;
+  }
+#endif
+
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  // void MySQLIndex::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+  //                              DatabaseManager& manager,
+  //                              const Orthanc::DatabasePluginMessages::Find_Request& request)
+  // {
+  //   // TODO-FIND
+  //   throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+  // }
+
+  // void MySQLIndex::ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+  //                               DatabaseManager& manager,
+  //                               const Orthanc::DatabasePluginMessages::Find_Request& request)
+  // {
+  //   // TODO-FIND
+  //   throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+  // }
+#endif
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e MySQL/Plugins/MySQLIndex.h
--- a/MySQL/Plugins/MySQLIndex.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/MySQL/Plugins/MySQLIndex.h	Mon Dec 16 16:36:12 2024 +0100
@@ -34,9 +34,16 @@
     MySQLParameters        parameters_;
     bool                   clearAll_;
 
+  protected:
+    virtual bool HasChildCountTable() const
+    {
+      return false;
+    }
+
   public:
     MySQLIndex(OrthancPluginContext* context,
-               const MySQLParameters& parameters);
+               const MySQLParameters& parameters,
+               bool readOnly);
 
     void SetClearAll(bool clear)
     {
@@ -85,5 +92,19 @@
     {
       return true;
     }
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+    virtual bool HasFindSupport() const ORTHANC_OVERRIDE;
+#endif
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+    // virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+    //                          DatabaseManager& manager,
+    //                          const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE;
+
+    // virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+    //                           DatabaseManager& manager,
+    //                           const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE;
+#endif
   };
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e MySQL/UnitTests/UnitTestsMain.cpp
--- a/MySQL/UnitTests/UnitTestsMain.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/MySQL/UnitTests/UnitTestsMain.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -49,7 +49,7 @@
   OrthancDatabases::MySQLParameters lock = globalParameters_;
   lock.SetLock(true);
 
-  OrthancDatabases::MySQLIndex db1(NULL, noLock);
+  OrthancDatabases::MySQLIndex db1(NULL, noLock, false);
   db1.SetClearAll(true);
 
   std::list<OrthancDatabases::IdentifierTag> identifierTags;
@@ -57,15 +57,15 @@
   std::unique_ptr<OrthancDatabases::DatabaseManager> manager1(OrthancDatabases::IndexBackend::CreateSingleDatabaseManager(db1, false, identifierTags));
 
   {
-    OrthancDatabases::MySQLIndex db2(NULL, lock);
+    OrthancDatabases::MySQLIndex db2(NULL, lock, false);
     std::unique_ptr<OrthancDatabases::DatabaseManager> manager2(OrthancDatabases::IndexBackend::CreateSingleDatabaseManager(db2, false, identifierTags));
 
-    OrthancDatabases::MySQLIndex db3(NULL, lock);
+    OrthancDatabases::MySQLIndex db3(NULL, lock, false);
     ASSERT_THROW(OrthancDatabases::IndexBackend::CreateSingleDatabaseManager(db3, false, identifierTags), Orthanc::OrthancException);
 
   }
 
-  OrthancDatabases::MySQLIndex db4(NULL, lock);
+  OrthancDatabases::MySQLIndex db4(NULL, lock, false);
   std::unique_ptr<OrthancDatabases::DatabaseManager> manager4(OrthancDatabases::IndexBackend::CreateSingleDatabaseManager(db4, false, identifierTags));
 }
 
diff -r 987460ae14c9 -r 3d853c8f5a9e Odbc/NEWS
--- a/Odbc/NEWS	Mon Sep 09 15:21:14 2024 +0200
+++ b/Odbc/NEWS	Mon Dec 16 16:36:12 2024 +0100
@@ -7,6 +7,10 @@
 Optimal Orthanc runtime: 1.12.0+
 
 * Fix check of Orthanc runtime version
+* Added support for ExtendedChanges:
+  - changes?type=...&to=...
+* Fix bug 224, error when using LIMIT with MSSQLServer
+  https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=224
 * Fixed a memory leak when executing non cached SQL statements (rarely used)
 
 
diff -r 987460ae14c9 -r 3d853c8f5a9e Odbc/Plugins/IndexPlugin.cpp
--- a/Odbc/Plugins/IndexPlugin.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Odbc/Plugins/IndexPlugin.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -45,7 +45,9 @@
 #endif
 
 
-#include <google/protobuf/any.h>
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
+#  include <google/protobuf/any.h>
+#endif
 
 
 static const char* const KEY_ODBC = "Odbc";
@@ -60,7 +62,9 @@
   
   ORTHANC_PLUGINS_API int32_t OrthancPluginInitialize(OrthancPluginContext* context)
   {
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
     GOOGLE_PROTOBUF_VERIFY_VERSION;
+#endif
 
     if (!OrthancDatabases::InitializePlugin(context, ORTHANC_PLUGIN_NAME, "ODBC", true))
     {
@@ -106,6 +110,7 @@
       const unsigned int countConnections = odbc.GetUnsignedIntegerValue("IndexConnectionsCount", 1);
       const unsigned int maxConnectionRetries = odbc.GetUnsignedIntegerValue("MaximumConnectionRetries", 10);
       const unsigned int connectionRetryInterval = odbc.GetUnsignedIntegerValue("ConnectionRetryInterval", 5);
+      const unsigned int housekeepingDelaySeconds = 5;  // TODO - PARAMETER
 
       if (connectionString.empty())
       {
@@ -113,11 +118,18 @@
                                         "No connection string provided for the ODBC index");
       }
 
-      std::unique_ptr<OrthancDatabases::OdbcIndex> index(new OrthancDatabases::OdbcIndex(context, connectionString));
+      bool readOnly = configuration.GetBooleanValue("ReadOnly", false);
+
+      if (readOnly)
+      {
+        LOG(WARNING) << "READ-ONLY SYSTEM: the Database plugin is working in read-only mode";
+      }
+
+      std::unique_ptr<OrthancDatabases::OdbcIndex> index(new OrthancDatabases::OdbcIndex(context, connectionString, readOnly));
       index->SetMaxConnectionRetries(maxConnectionRetries);
       index->SetConnectionRetryInterval(connectionRetryInterval);
 
-      OrthancDatabases::IndexBackend::Register(index.release(), countConnections, maxConnectionRetries);
+      OrthancDatabases::IndexBackend::Register(index.release(), countConnections, maxConnectionRetries, housekeepingDelaySeconds);
     }
     catch (Orthanc::OrthancException& e)
     {
@@ -138,7 +150,10 @@
   {
     LOG(WARNING) << "ODBC index is finalizing";
     OrthancDatabases::IndexBackend::Finalize();
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
     google::protobuf::ShutdownProtobufLibrary();
+#endif
   }
 
 
diff -r 987460ae14c9 -r 3d853c8f5a9e Odbc/Plugins/OdbcIndex.cpp
--- a/Odbc/Plugins/OdbcIndex.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/Odbc/Plugins/OdbcIndex.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -135,8 +135,9 @@
 
 
   OdbcIndex::OdbcIndex(OrthancPluginContext* context,
-                       const std::string& connectionString) :
-    IndexBackend(context),
+                       const std::string& connectionString,
+                       bool readOnly) :
+    IndexBackend(context, readOnly),
     maxConnectionRetries_(10),
     connectionRetryInterval_(5),
     connectionString_(connectionString)
@@ -695,4 +696,32 @@
 
     SignalDeletedFiles(output, manager);
   }
+
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  bool OdbcIndex::HasFindSupport() const
+  {
+    // TODO-FIND
+    return false;
+  }
+#endif
+
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  // void OdbcIndex::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+  //                             DatabaseManager& manager,
+  //                             const Orthanc::DatabasePluginMessages::Find_Request& request)
+  // {
+  //   // TODO-FIND
+  //   throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+  // }
+
+  // void OdbcIndex::ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+  //                              DatabaseManager& manager,
+  //                              const Orthanc::DatabasePluginMessages::Find_Request& request)
+  // {
+  //   // TODO-FIND
+  //   throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+  // }
+#endif
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e Odbc/Plugins/OdbcIndex.h
--- a/Odbc/Plugins/OdbcIndex.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/Odbc/Plugins/OdbcIndex.h	Mon Dec 16 16:36:12 2024 +0100
@@ -34,9 +34,16 @@
     unsigned int connectionRetryInterval_;
     std::string  connectionString_;
     
+  protected:
+    virtual bool HasChildCountTable() const
+    {
+      return false;
+    }
+
   public:
     OdbcIndex(OrthancPluginContext* context,
-              const std::string& connectionString);
+              const std::string& connectionString,
+              bool readOnly);
 
     unsigned int GetMaxConnectionRetries() const
     {
@@ -92,5 +99,19 @@
     {
       return false;
     }
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+    virtual bool HasFindSupport() const ORTHANC_OVERRIDE;
+#endif
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+    // virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+    //                          DatabaseManager& manager,
+    //                          const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE;
+
+    // virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+    //                           DatabaseManager& manager,
+    //                           const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE;
+#endif
   };
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/CMakeLists.txt
--- a/PostgreSQL/CMakeLists.txt	Mon Sep 09 15:21:14 2024 +0200
+++ b/PostgreSQL/CMakeLists.txt	Mon Dec 16 16:36:12 2024 +0100
@@ -25,11 +25,11 @@
 set(ORTHANC_PLUGIN_VERSION "mainline")
 
 # This is the preferred version of the Orthanc SDK for this plugin
-set(ORTHANC_SDK_DEFAULT_VERSION "1.12.3")
+set(ORTHANC_SDK_DEFAULT_VERSION "1.12.3")  # TODO: 1.12.5
 
 # This is the list of the versions of the Orthanc SDK against which
 # this plugin will compile
-set(ORTHANC_SDK_COMPATIBLE_VERSIONS "1.12.3" "1.12.4")
+set(ORTHANC_SDK_COMPATIBLE_VERSIONS "1.12.3" "1.12.4")  # TODO: 1.12.5
 
 # This is the minimal version of the Orthanc runtime that will provide
 # best performance. If the version of the Orthanc runtime is below
@@ -37,13 +37,13 @@
 # plugin will still start).
 set(ORTHANC_OPTIMAL_VERSION_MAJOR    1)
 set(ORTHANC_OPTIMAL_VERSION_MINOR    12)
-set(ORTHANC_OPTIMAL_VERSION_REVISION 3)
+set(ORTHANC_OPTIMAL_VERSION_REVISION 3)  # TODO: 1.12.5
 
 if (ORTHANC_PLUGIN_VERSION STREQUAL "mainline")
   set(ORTHANC_FRAMEWORK_VERSION "mainline")
   set(ORTHANC_FRAMEWORK_DEFAULT_SOURCE "hg")
 else()
-  set(ORTHANC_FRAMEWORK_VERSION "1.12.4")
+  set(ORTHANC_FRAMEWORK_VERSION "1.12.4")  # TODO: 1.12.5
   set(ORTHANC_FRAMEWORK_DEFAULT_SOURCE "web")
 endif()
 
@@ -92,6 +92,7 @@
   POSTGRESQL_PREPARE_INDEX           ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndex.sql
   POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToRev1.sql
   POSTGRESQL_UPGRADE_REV1_TO_REV2    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev1ToRev2.sql
+  POSTGRESQL_UPGRADE_REV2_TO_REV3    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3b.sql
   )
 
 
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/NEWS
--- a/PostgreSQL/NEWS	Mon Sep 09 15:21:14 2024 +0200
+++ b/PostgreSQL/NEWS	Mon Dec 16 16:36:12 2024 +0100
@@ -1,12 +1,44 @@
-Pending changes in the mainline
+Pending changes in the mainline (future 7.0)
 ===============================
 
-DB schema revision: 2
-Minimum plugin SDK (for build): 1.12.3
-Minimum Orthanc runtime: 1.12.3
+DB schema revision: 3
+Minimum plugin SDK (for build): 1.12.5+
+Optimal plugin SDK (for build): 1.12.5+
+Minimum Orthanc runtime: 1.12.5+
+Optimal Orthanc runtime: 1.12.5+
+
+Minimal Postgresql Server version: 9
+Optimal Postgresql Server version: 11+
+
+* WARNING: An Index is being replaced to improve performance.  The creation
+  of the new index can take some time (we have observed 3 minutes on a
+  DB with 70M instances).  Orthanc will not be available during the
+  creation of this index.  If needed, you can create it manually before installing
+  the new plugin by executing these SQL commands:
+
+    https://orthanc.uclouvain.be/hg/orthanc-databases/file/tip/PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_To_Rev3b.sql
+
+* WARNING: A new column has been added in the resources table when upgrading to this version.  A new thread
+  is populating this new table and might consume DB bandwitdh and CPU.
+
 
 * Fix updates from plugin version 3.3 to latest version
+* Added support for ExtendedChanges:
+  - changes?type=...&to=...
+* Performance optimizations (to be summarized before release):
+  - using more prepared SQL statements:
+    - InsertOrUpdateMetadata
+    - ExecuteSetResourcesContentTags
+  - merged BEGIN and SET TRANSACTION statements
+  - reduced the number of round-trips between Orthanc and the PostgreSQL server:
+    - e.g: when receiving an instance in an existing series, reduced the number of SQL queries from 13 to 9
 * Fixed a memory leak when executing non cached SQL statements (rarely used)
+* New default values for configurations:
+  - "IndexConnectionsCount": 50
+  - "TransactionMode": "ReadCommitted"
+* Introduced a new thread to perform DB Housekeeping at regular interval (5s) for the
+  DB plugins requiring it (currently only PostgreSQL).  E.g: This avoids very long update
+  times in case you don't call /statistics for a long period.
 
 
 Release 6.2 (2024-03-25)
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/Plugins/IndexPlugin.cpp
--- a/PostgreSQL/Plugins/IndexPlugin.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/PostgreSQL/Plugins/IndexPlugin.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -27,7 +27,9 @@
 #include <Logging.h>
 #include <Toolbox.h>
 
-#include <google/protobuf/any.h>
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
+#  include <google/protobuf/any.h>
+#endif
 
 #define ORTHANC_PLUGIN_NAME "postgresql-index"
 
@@ -36,7 +38,9 @@
 {
   ORTHANC_PLUGINS_API int32_t OrthancPluginInitialize(OrthancPluginContext* context)
   {
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
     GOOGLE_PROTOBUF_VERIFY_VERSION;
+#endif
 
     if (!OrthancDatabases::InitializePlugin(context, ORTHANC_PLUGIN_NAME, "PostgreSQL", true))
     {
@@ -65,14 +69,22 @@
       return 0;
     }
 
+    bool readOnly = configuration.GetBooleanValue("ReadOnly", false);
+
+    if (readOnly)
+    {
+      LOG(WARNING) << "READ-ONLY SYSTEM: the Database plugin is working in read-only mode";
+    }
+    
     try
     {
-      const size_t countConnections = postgresql.GetUnsignedIntegerValue("IndexConnectionsCount", 1);
+      const size_t countConnections = postgresql.GetUnsignedIntegerValue("IndexConnectionsCount", 50);
+      const unsigned int housekeepingDelaySeconds = 5;  // TODO - PARAMETER
 
       OrthancDatabases::PostgreSQLParameters parameters(postgresql);
       OrthancDatabases::IndexBackend::Register(
-        new OrthancDatabases::PostgreSQLIndex(context, parameters), countConnections,
-        parameters.GetMaxConnectionRetries());
+        new OrthancDatabases::PostgreSQLIndex(context, parameters, readOnly), countConnections,
+        parameters.GetMaxConnectionRetries(), housekeepingDelaySeconds);
     }
     catch (Orthanc::OrthancException& e)
     {
@@ -94,7 +106,10 @@
     LOG(WARNING) << "PostgreSQL index is finalizing";
     OrthancDatabases::IndexBackend::Finalize();
     Orthanc::Toolbox::FinalizeOpenSsl();
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
     google::protobuf::ShutdownProtobufLibrary();
+#endif
   }
 
 
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/Plugins/PostgreSQLIndex.cpp
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -32,9 +32,12 @@
 
 #include <Compatibility.h>  // For std::unique_ptr<>
 #include <Toolbox.h>
+#include <SystemToolbox.h>
 #include <Logging.h>
 #include <OrthancException.h>
 
+#include <boost/algorithm/string/join.hpp>
+
 
 namespace Orthanc
 {
@@ -43,16 +46,19 @@
   static const GlobalProperty GlobalProperty_HasCreateInstance = GlobalProperty_DatabaseInternal1;
   static const GlobalProperty GlobalProperty_HasFastCountResources = GlobalProperty_DatabaseInternal2;
   static const GlobalProperty GlobalProperty_GetLastChangeIndex = GlobalProperty_DatabaseInternal3;
+  static const GlobalProperty GlobalProperty_HasComputeStatisticsReadOnly = GlobalProperty_DatabaseInternal4;
 }
 
 
 namespace OrthancDatabases
 {
   PostgreSQLIndex::PostgreSQLIndex(OrthancPluginContext* context,
-                                   const PostgreSQLParameters& parameters) :
-    IndexBackend(context),
+                                   const PostgreSQLParameters& parameters,
+                                   bool readOnly) :
+    IndexBackend(context, readOnly),
     parameters_(parameters),
-    clearAll_(false)
+    clearAll_(false),
+    hkHasComputedAllMissingChildCount_(false)
   {
   }
 
@@ -93,13 +99,20 @@
 
     PostgreSQLDatabase& db = dynamic_cast<PostgreSQLDatabase&>(manager.GetDatabase());
 
-    if (parameters_.HasLock())
+    if (parameters_.HasLock()) 
     {
+      if (IsReadOnly())
+      {
+        LOG(ERROR) << "READ-ONLY SYSTEM: Unable to lock the database when working in ReadOnly mode."; 
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_Plugin);
+      }
+
       db.AdvisoryLock(POSTGRESQL_LOCK_INDEX);
     }
 
+    if (!IsReadOnly())
     {
-      // lock the full DB while checking if it needs to be create/ugraded
+      // lock the full DB while checking if it needs to be created/ugraded
       PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP);
 
       if (clearAll_)
@@ -134,22 +147,30 @@
             throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);        
           }
 
-          bool needToRunUpgradeFromUnknownToV1 = false;
-          bool needToRunUpgradeV1toV2 = false;
+          bool applyUpgradeFromUnknownToV1 = false;
+          bool applyUpgradeV1toV2 = false;
+          bool applyUpgradeV2toV3 = false;
+          bool applyPrepareIndex = false;
 
           int revision;
           if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel))
           {
             LOG(WARNING) << "No DatabasePatchLevel found, assuming it's 1";
             revision = 1;
-            needToRunUpgradeFromUnknownToV1 = true;
-            needToRunUpgradeV1toV2 = true;
+            applyUpgradeFromUnknownToV1 = true;
+            applyUpgradeV1toV2 = true;
+            applyUpgradeV2toV3 = true;
           }
           else if (revision == 1)
           {
             LOG(WARNING) << "DatabasePatchLevel is 1";
-            needToRunUpgradeFromUnknownToV1 = true;
-            needToRunUpgradeV1toV2 = true;
+            applyUpgradeV1toV2 = true;
+            applyUpgradeV2toV3 = true;
+          }
+          else if (revision == 2)
+          {
+            LOG(WARNING) << "DatabasePatchLevel is 2";
+            applyUpgradeV2toV3 = true;
           }
 
           int hasTrigram = 0;
@@ -160,30 +181,24 @@
             // We've observed 9 minutes on DB with 100000 studies
             LOG(WARNING) << "The DB schema update will try to enable trigram matching on the PostgreSQL database "
                          << "to speed up wildcard searches. This may take several minutes";
-            needToRunUpgradeV1toV2 = true;
+            applyUpgradeV1toV2 = true;
+            applyUpgradeV2toV3 = true;
           }
 
           int property = 0;
           if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
-                                           Orthanc::GlobalProperty_HasFastCountResources) ||
-              property != 1)
-          {
-            needToRunUpgradeV1toV2 = true;
-          }
-          if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
-                                          Orthanc::GlobalProperty_GetTotalSizeIsFast) ||
-              property != 1)
-          {
-            needToRunUpgradeV1toV2 = true;
-          }
-          if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
                                           Orthanc::GlobalProperty_GetLastChangeIndex) ||
               property != 1)
           {
-            needToRunUpgradeV1toV2 = true;
+            applyUpgradeV1toV2 = true;
+            applyUpgradeV2toV3 = true;
           }
 
-          if (needToRunUpgradeFromUnknownToV1)
+          // If you add new tests here, update the test in the "ReadOnly" code below
+
+          applyPrepareIndex = applyUpgradeV2toV3;
+
+          if (applyUpgradeFromUnknownToV1)
           {
             LOG(WARNING) << "Upgrading DB schema from unknown to revision 1";
             std::string query;
@@ -193,7 +208,7 @@
             t.GetDatabaseTransaction().ExecuteMultiLines(query);
           }
           
-          if (needToRunUpgradeV1toV2)
+          if (applyUpgradeV1toV2)
           {
             LOG(WARNING) << "Upgrading DB schema from revision 1 to revision 2";
 
@@ -202,15 +217,45 @@
             Orthanc::EmbeddedResources::GetFileResource
               (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV1_TO_REV2);
             t.GetDatabaseTransaction().ExecuteMultiLines(query);
+          }
 
-            // apply all idempotent changes that are in the PrepareIndexV2
+          if (applyUpgradeV2toV3)
+          {
+            LOG(WARNING) << "Upgrading DB schema from revision 2 to revision 3";
+
+            std::string query;
+
+            Orthanc::EmbeddedResources::GetFileResource
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV2_TO_REV3);
+            t.GetDatabaseTransaction().ExecuteMultiLines(query);
+          }
+
+          if (applyPrepareIndex)
+          {
+            // apply all idempotent changes that are in the PrepareIndex.sql
             ApplyPrepareIndex(t, manager);
           }
+
         }
 
         t.Commit();
       }
     }
+    else
+    {
+      LOG(WARNING) << "READ-ONLY SYSTEM: checking if the DB already exists and has the right schema"; 
+
+      DatabaseManager::Transaction t(manager, TransactionType_ReadOnly);
+
+      // test if the latest "extension" has been installed
+      int revision;
+      if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)
+          || revision != 3)
+      {      
+        LOG(ERROR) << "READ-ONLY SYSTEM: the DB does not have the correct schema to run with this version of the plugin"; 
+        throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
+      }
+    }
   }
 
 
@@ -237,17 +282,23 @@
 
   uint64_t PostgreSQLIndex::GetTotalCompressedSize(DatabaseManager& manager)
   {
-    // Fast version if extension "./FastTotalSize.sql" is installed
     uint64_t result;
 
     {
       DatabaseManager::CachedStatement statement(
         STATEMENT_FROM_HERE, manager,
-        "SELECT * FROM UpdateSingleStatistic(0)");
+        "SELECT * FROM ComputeStatisticsReadOnly(0)");
 
       statement.Execute();
 
-      result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      if (statement.IsNull(0))
+      {
+        return 0;
+      }
+      else
+      {
+        result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      }
     }
     
     // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION.  This is however true when no files are being delete/added
@@ -258,17 +309,23 @@
   
   uint64_t PostgreSQLIndex::GetTotalUncompressedSize(DatabaseManager& manager)
   {
-    // Fast version if extension "./FastTotalSize.sql" is installed
     uint64_t result;
 
     {
       DatabaseManager::CachedStatement statement(
         STATEMENT_FROM_HERE, manager,
-        "SELECT * FROM UpdateSingleStatistic(1)");
+        "SELECT * FROM ComputeStatisticsReadOnly(1)");
 
       statement.Execute();
 
-      result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      if (statement.IsNull(0))
+      {
+        return 0;
+      }
+      else
+      {
+        result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      }
     }
     
     // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION.  This is however true when no files are being delete/added
@@ -487,23 +544,34 @@
   static void ExecuteSetResourcesContentTags(
     DatabaseManager& manager,
     const std::string& table,
-    const std::string& variablePrefix,
     uint32_t count,
     const OrthancPluginResourcesContentTags* tags)
   {
     std::string sql;
+
+    std::vector<std::string> resourceIds;
+    std::vector<std::string> groups;
+    std::vector<std::string> elements;
+    std::vector<std::string> values;
+
     Dictionary args;
     
     for (uint32_t i = 0; i < count; i++)
     {
-      std::string name = variablePrefix + boost::lexical_cast<std::string>(i);
+      std::string resourceArgName = "r" + boost::lexical_cast<std::string>(i);
+      std::string groupArgName = "g" + boost::lexical_cast<std::string>(i);
+      std::string elementArgName = "e" + boost::lexical_cast<std::string>(i);
+      std::string valueArgName = "v" + boost::lexical_cast<std::string>(i);
 
-      args.SetUtf8Value(name, tags[i].value);
-      
-      std::string insert = ("(" + boost::lexical_cast<std::string>(tags[i].resource) + ", " +
-                            boost::lexical_cast<std::string>(tags[i].group) + ", " +
-                            boost::lexical_cast<std::string>(tags[i].element) + ", " +
-                            "${" + name + "})");
+      args.SetIntegerValue(resourceArgName, tags[i].resource);
+      args.SetInteger32Value(elementArgName, tags[i].element);
+      args.SetInteger32Value(groupArgName, tags[i].group);
+      args.SetUtf8Value(valueArgName, tags[i].value);
+
+      std::string insert = ("(${" + resourceArgName + "}, ${" +
+                            groupArgName + "}, ${" +
+                            elementArgName + "}, " +
+                            "${" + valueArgName + "})");
 
       if (sql.empty())
       {
@@ -517,11 +585,17 @@
 
     if (!sql.empty())
     {
-      DatabaseManager::StandaloneStatement statement(manager, sql);
-
+      DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
+      
       for (uint32_t i = 0; i < count; i++)
       {
-        statement.SetParameterType(variablePrefix + boost::lexical_cast<std::string>(i),
+        statement.SetParameterType("r" + boost::lexical_cast<std::string>(i),
+                                    ValueType_Integer64);
+        statement.SetParameterType("g" + boost::lexical_cast<std::string>(i),
+                                    ValueType_Integer32);
+        statement.SetParameterType("e" + boost::lexical_cast<std::string>(i),
+                                    ValueType_Integer32);
+        statement.SetParameterType("v" + boost::lexical_cast<std::string>(i),
                                    ValueType_Utf8String);
       }
 
@@ -552,25 +626,24 @@
     
     for (uint32_t i = 0; i < count; i++)
     {
-      std::string argName = "m" + boost::lexical_cast<std::string>(i);
-
-      args.SetUtf8Value(argName, metadata[i].value);
+      std::string resourceArgName = "r" + boost::lexical_cast<std::string>(i);
+      std::string typeArgName = "t" + boost::lexical_cast<std::string>(i);
+      std::string valueArgName = "v" + boost::lexical_cast<std::string>(i);
 
-      resourceIds.push_back(boost::lexical_cast<std::string>(metadata[i].resource));
-      metadataTypes.push_back(boost::lexical_cast<std::string>(metadata[i].metadata));
-      metadataValues.push_back("${" + argName + "}");
+      args.SetIntegerValue(resourceArgName, metadata[i].resource);
+      args.SetInteger32Value(typeArgName, metadata[i].metadata);
+      args.SetUtf8Value(valueArgName, metadata[i].value);
+
+      resourceIds.push_back("${" + resourceArgName + "}");
+      metadataTypes.push_back("${" + typeArgName + "}");
+      metadataValues.push_back("${" + valueArgName + "}");
       revisions.push_back("0");
     }
 
-    std::string joinedResourceIds;
-    std::string joinedMetadataTypes;
-    std::string joinedMetadataValues;
-    std::string joinedRevisions;
-
-    Orthanc::Toolbox::JoinStrings(joinedResourceIds, resourceIds, ",");
-    Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ",");
-    Orthanc::Toolbox::JoinStrings(joinedMetadataValues, metadataValues, ",");
-    Orthanc::Toolbox::JoinStrings(joinedRevisions, revisions, ",");
+    std::string joinedResourceIds = boost::algorithm::join(resourceIds, ",");
+    std::string joinedMetadataTypes = boost::algorithm::join(metadataTypes, ",");
+    std::string joinedMetadataValues = boost::algorithm::join(metadataValues, ",");
+    std::string joinedRevisions = boost::algorithm::join(revisions, ",");
 
     std::string sql = std::string("SELECT InsertOrUpdateMetadata(ARRAY[") + 
                                   joinedResourceIds + "], ARRAY[" + 
@@ -578,12 +651,16 @@
                                   joinedMetadataValues + "], ARRAY[" + 
                                   joinedRevisions + "])";
 
-    DatabaseManager::StandaloneStatement statement(manager, sql);
+    DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
 
     for (uint32_t i = 0; i < count; i++)
     {
-      statement.SetParameterType("m" + boost::lexical_cast<std::string>(i),
+      statement.SetParameterType("v" + boost::lexical_cast<std::string>(i),
                                   ValueType_Utf8String);
+      statement.SetParameterType("r" + boost::lexical_cast<std::string>(i),
+                                  ValueType_Integer64);
+      statement.SetParameterType("t" + boost::lexical_cast<std::string>(i),
+                                  ValueType_Integer32);
     }
 
     statement.Execute(args);
@@ -599,11 +676,9 @@
                                      uint32_t countMetadata,
                                      const OrthancPluginResourcesContentMetadata* metadata)
   {
-    ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i",
-                                   countIdentifierTags, identifierTags);
+    ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", countIdentifierTags, identifierTags);
 
-    ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t",
-                                   countMainDicomTags, mainDicomTags);
+    ExecuteSetResourcesContentTags(manager, "MainDicomTags", countMainDicomTags, mainDicomTags);
     
     ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata);
 
@@ -613,8 +688,6 @@
   uint64_t PostgreSQLIndex::GetResourcesCount(DatabaseManager& manager,
                                               OrthancPluginResourceType resourceType)
   {
-    // Optimized version thanks to the "FastCountResources.sql" extension
-
     assert(OrthancPluginResourceType_Patient == 0 &&
            OrthancPluginResourceType_Study == 1 &&
            OrthancPluginResourceType_Series == 2 &&
@@ -625,15 +698,22 @@
     {
       DatabaseManager::StandaloneStatement statement(
         manager,
-        std::string("SELECT * FROM UpdateSingleStatistic(") + boost::lexical_cast<std::string>(resourceType + 2) + ")");  // For an explanation of the "+ 2" below, check out "PrepareIndex.sql"
+        std::string("SELECT * FROM ComputeStatisticsReadOnly(") + boost::lexical_cast<std::string>(resourceType + 2) + ")");  // For an explanation of the "+ 2" below, check out "PrepareIndex.sql"
 
       statement.Execute();
 
-      result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      if (statement.IsNull(0))
+      {
+        return 0;
+      }
+      else
+      {
+        result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      }
     }
       
     // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION.  This is however true when no files are being delete/added
-    assert(result == IndexBackend::GetResourcesCount(manager, resourceType));
+    // assert(result == IndexBackend::GetResourcesCount(manager, resourceType));
 
     return result;
   }
@@ -659,4 +739,39 @@
     // backward compatibility is necessary
     throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);
   }
+
+  bool PostgreSQLIndex::HasPerformDbHousekeeping()
+  {
+    return true;
+  }
+
+  void PostgreSQLIndex::PerformDbHousekeeping(DatabaseManager& manager)
+  {
+    // Compute the missing child count (table introduced in rev3)
+    if (!hkHasComputedAllMissingChildCount_)
+    {
+      DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE, manager,
+        "SELECT ComputeMissingChildCount(50)");
+
+      statement.Execute();
+
+      int64_t updatedCount = statement.ReadInteger64(0);
+      hkHasComputedAllMissingChildCount_ = updatedCount == 0;
+
+      if (updatedCount > 0)
+      {
+        LOG(INFO) << "Computed " << updatedCount << " missing ChildCount entries";
+      }
+      else
+      {
+        LOG(INFO) << "No missing ChildCount entries";
+      }
+    }
+
+    // Consume the statistics delta to minimize computation when calling ComputeStatisticsReadOnly
+    {
+      int64_t patientsCount, studiesCount, seriesCount, instancesCount, compressedSize, uncompressedSize;
+      UpdateAndGetStatistics(manager, patientsCount, studiesCount, seriesCount, instancesCount, compressedSize, uncompressedSize);
+    }
+  }
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/Plugins/PostgreSQLIndex.h
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.h	Mon Dec 16 16:36:12 2024 +0100
@@ -25,6 +25,8 @@
 
 #include "../../Framework/Plugins/IndexBackend.h"
 #include "../../Framework/PostgreSQL/PostgreSQLParameters.h"
+#include <boost/thread.hpp>
+
 
 namespace OrthancDatabases
 {
@@ -33,6 +35,7 @@
   private:
     PostgreSQLParameters   parameters_;
     bool                   clearAll_;
+    bool                   hkHasComputedAllMissingChildCount_;
 
   protected:
     virtual void ClearDeletedFiles(DatabaseManager& manager) ORTHANC_OVERRIDE;
@@ -41,11 +44,17 @@
 
     virtual void ClearRemainingAncestor(DatabaseManager& manager) ORTHANC_OVERRIDE;
 
+    virtual bool HasChildCountTable() const ORTHANC_OVERRIDE
+    {
+      return true;
+    }
+
     void ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager);
 
   public:
     PostgreSQLIndex(OrthancPluginContext* context,
-                    const PostgreSQLParameters& parameters);
+                    const PostgreSQLParameters& parameters,
+                    bool readOnly = false);
 
     void SetClearAll(bool clear)
     {
@@ -135,5 +144,9 @@
                                         int64_t& compressedSize,
                                         int64_t& uncompressedSize) ORTHANC_OVERRIDE;
 
+    virtual bool HasPerformDbHousekeeping() ORTHANC_OVERRIDE;
+
+    virtual void PerformDbHousekeeping(DatabaseManager& manager) ORTHANC_OVERRIDE;
+
   };
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/Plugins/SQL/Downgrades/Rev3ToRev2.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev3ToRev2.sql	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,15 @@
+-- This file contains an SQL procedure to downgrade from schema Rev3 to Rev2 (version = 6, revision = 1).
+  -- It actually deletes the ChildCount table and triggers
+  -- It actually does not uninstall ChildrenIndex2 because it is anyway more efficient than 
+     -- ChildrenIndex and is not incompatible with previous revisions.
+
+DROP TRIGGER IF EXISTS DecrementChildCount ON Resources;
+DROP TRIGGER IF EXISTS IncrementChildCount ON Resources;
+DROP TABLE ChildCount;
+DROP FUNCTION UpdateChildCount;
+
+
+-- set the global properties that actually documents the DB version, revision and some of the capabilities
+-- modify only the ones that have changed
+DELETE FROM GlobalProperties WHERE property IN (4, 11);
+INSERT INTO GlobalProperties VALUES (4, 2); -- GlobalProperty_DatabasePatchLevel
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/Plugins/SQL/Downgrades/Rev3bToRev2.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev3bToRev2.sql	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,84 @@
+-- This file contains an SQL procedure to downgrade from schema Rev3 to Rev2 (version = 6, revision = 1).
+  -- It actually deletes the ChildCount table and triggers
+  -- It actually does not uninstall ChildrenIndex2 because it is anyway more efficient than 
+     -- ChildrenIndex and is not incompatible with previous revisions.
+
+-- remove the childCount column in resources
+DO $body$
+BEGIN
+	IF EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN
+		ALTER TABLE Resources DROP COLUMN childcount;
+	ELSE
+		raise notice 'the resources.childcount column does not exists';
+	END IF;
+
+END $body$;
+
+
+------------------- re-install old CreateInstance function -------------------
+CREATE OR REPLACE FUNCTION CreateInstance(
+  IN patient_public_id TEXT,
+  IN study_public_id TEXT,
+  IN series_public_id TEXT,
+  IN instance_public_id TEXT,
+  OUT is_new_patient BIGINT,
+  OUT is_new_study BIGINT,
+  OUT is_new_series BIGINT,
+  OUT is_new_instance BIGINT,
+  OUT patient_internal_id BIGINT,
+  OUT study_internal_id BIGINT,
+  OUT series_internal_id BIGINT,
+  OUT instance_internal_id BIGINT) AS $body$
+
+BEGIN
+	is_new_patient := 1;
+	is_new_study := 1;
+	is_new_series := 1;
+	is_new_instance := 1;
+
+	BEGIN
+        INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL) RETURNING internalid INTO patient_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_patient := 0;
+            SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
+    END;
+
+	BEGIN
+        INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id) RETURNING internalid INTO study_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_study := 0;
+            SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
+    END;
+
+	BEGIN
+	    INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id) RETURNING internalid INTO series_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_series := 0;
+            SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
+    END;
+
+  	BEGIN
+		INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id) RETURNING internalid INTO instance_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_instance := 0;
+            SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
+    END;
+
+    IF is_new_instance > 0 THEN
+        -- Move the patient to the end of the recycling order.
+        PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
+    END IF;  
+END;
+$body$ LANGUAGE plpgsql;
+
+
+
+
+-- set the global properties that actually documents the DB version, revision and some of the capabilities
+-- modify only the ones that have changed
+DELETE FROM GlobalProperties WHERE property IN (4, 11);
+INSERT INTO GlobalProperties VALUES (4, 2); -- GlobalProperty_DatabasePatchLevel
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/Plugins/SQL/PrepareIndex.sql
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Mon Sep 09 15:21:14 2024 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Mon Dec 16 16:36:12 2024 +0100
@@ -15,6 +15,7 @@
        resourceType INTEGER NOT NULL,
        publicId VARCHAR(64) NOT NULL,
        parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+	   childCount INTEGER,
        CONSTRAINT UniquePublicId UNIQUE (publicId)
        );
 
@@ -106,7 +107,23 @@
         PRIMARY KEY(server, property)
         );
 
-CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId);
+DO $$
+DECLARE
+    pg_version text;
+BEGIN
+    SELECT version() INTO pg_version;
+
+    IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN
+        -- PostgreSQL 11 or later
+
+        -- new ChildrenIndex2 introduced in Rev3 (replacing previous ChildrenIndex)
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)';
+    ELSE
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)';
+    END IF;
+END $$;
+
+
 CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId);
 CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType);
 CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
@@ -114,6 +131,7 @@
 CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id);
 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id);
 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
+CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex3 ON DicomIdentifiers(tagGroup, tagElement, value);
 CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value);
 
 CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId);
@@ -174,6 +192,7 @@
 END;
 $body$ LANGUAGE plpgsql;
 
+DROP TRIGGER IF EXISTS PatientAdded on Resources;
 CREATE TRIGGER PatientAdded
 AFTER INSERT ON Resources
 FOR EACH ROW
@@ -203,6 +222,7 @@
 END;
 $body$ LANGUAGE plpgsql;
 
+DROP TRIGGER IF EXISTS ResourceDeleted on Resources;
 CREATE TRIGGER ResourceDeleted
 AFTER DELETE ON Resources
 FOR EACH ROW
@@ -481,6 +501,7 @@
 END;
 $body$ LANGUAGE plpgsql;
 
+DROP TRIGGER IF EXISTS InsertedChange on Changes;
 CREATE TRIGGER InsertedChange
 AFTER INSERT ON Changes
 FOR EACH ROW
@@ -509,7 +530,7 @@
 	is_new_instance := 1;
 
 	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL) RETURNING internalid INTO patient_internal_id;
+        INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL, 0) RETURNING internalid INTO patient_internal_id;
     EXCEPTION
         WHEN unique_violation THEN
             is_new_patient := 0;
@@ -517,7 +538,7 @@
     END;
 
 	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id) RETURNING internalid INTO study_internal_id;
+        INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id, 0) RETURNING internalid INTO study_internal_id;
     EXCEPTION
         WHEN unique_violation THEN
             is_new_study := 0;
@@ -525,7 +546,7 @@
     END;
 
 	BEGIN
-	    INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id) RETURNING internalid INTO series_internal_id;
+	    INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id, 0) RETURNING internalid INTO series_internal_id;
     EXCEPTION
         WHEN unique_violation THEN
             is_new_series := 0;
@@ -533,7 +554,7 @@
     END;
 
   	BEGIN
-		INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id) RETURNING internalid INTO instance_internal_id;
+		INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id, 0) RETURNING internalid INTO instance_internal_id;
     EXCEPTION
         WHEN unique_violation THEN
             is_new_instance := 0;
@@ -545,17 +566,128 @@
         PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
     END IF;  
 END;
+$body$ LANGUAGE plpgsql;
 
+-- function to compute a statistic in a ReadOnly transaction
+CREATE OR REPLACE FUNCTION ComputeStatisticsReadOnly(
+    IN statistics_key INTEGER,
+    OUT accumulated_value BIGINT
+) RETURNS BIGINT AS $body$
+
+DECLARE
+    current_value BIGINT;
+    
+BEGIN
+
+    SELECT VALUE FROM GlobalIntegers
+    INTO current_value
+    WHERE key = statistics_key;
+
+    SELECT COALESCE(SUM(value), 0) + current_value FROM GlobalIntegersChanges
+    INTO accumulated_value
+    WHERE key = statistics_key;
+
+END;
+$body$ LANGUAGE plpgsql;
+
+
+-- -- new in Rev3b
+
+-- Computes the childCount for a number of resources for which it has not been computed yet.
+-- This is actually used only after an update to Rev3.  A thread will call this function
+-- at regular interval to update all missing values and stop once all values have been processed.
+CREATE OR REPLACE FUNCTION ComputeMissingChildCount(
+    IN batch_size BIGINT,
+    OUT updated_rows_count BIGINT
+) RETURNS BIGINT AS $body$
+BEGIN
+	UPDATE Resources AS r
+    SET childCount = (SELECT COUNT(childLevel.internalId)
+                      FROM Resources AS childLevel
+                      WHERE childLevel.parentId = r.internalId)
+    WHERE internalId IN (
+        SELECT internalId FROM Resources
+        WHERE resourceType < 3 AND childCount IS NULL
+        LIMIT batch_size);
+    
+    -- Get the number of rows affected
+    GET DIAGNOSTICS updated_rows_count = ROW_COUNT;
+END;
 $body$ LANGUAGE plpgsql;
 
 
 
+DROP TRIGGER IF EXISTS IncrementChildCount on Resources;
+DROP TRIGGER IF EXISTS DecrementChildCount on Resources;
+
+CREATE OR REPLACE FUNCTION UpdateChildCount()
+RETURNS TRIGGER AS $body$
+BEGIN
+    IF TG_OP = 'INSERT' THEN
+		IF new.parentId IS NOT NULL THEN
+            -- try to increment the childcount from the parent
+            -- note that we already have the lock on this row because the parent is locked in CreateInstance
+			UPDATE Resources
+		    SET childCount = childCount + 1
+		    WHERE internalId = new.parentId AND childCount IS NOT NULL;
+		
+            -- this should only happen for old studies whose childCount has not yet been computed
+            -- note: this child has already been added so it will be counted
+		    IF NOT FOUND THEN
+		        UPDATE Resources
+                SET childCount = (SELECT COUNT(*)
+		                              FROM Resources
+		                              WHERE internalId = new.parentId)
+		        WHERE internalId = new.parentId;
+		    END IF;
+        END IF;
+	
+    ELSIF TG_OP = 'DELETE' THEN
+
+		IF old.parentId IS NOT NULL THEN
+
+            -- Decrement the child count for the parent
+            -- note that we already have the lock on this row because the parent is locked in DeleteResource
+            UPDATE Resources
+            SET childCount = childCount - 1
+            WHERE internalId = old.parentId AND childCount IS NOT NULL;
+		
+            -- this should only happen for old studies whose childCount has not yet been computed
+            -- note: this child has already been removed so it will not be counted
+		    IF NOT FOUND THEN
+		        UPDATE Resources
+                SET childCount = (SELECT COUNT(*)
+		                              FROM Resources
+		                              WHERE internalId = new.parentId)
+		        WHERE internalId = new.parentId;
+		    END IF;
+        END IF;
+        
+    END IF;
+    RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE TRIGGER IncrementChildCount
+AFTER INSERT ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE UpdateChildCount();
+
+CREATE TRIGGER DecrementChildCount
+AFTER DELETE ON Resources
+FOR EACH ROW
+WHEN (OLD.parentId IS NOT NULL)
+EXECUTE PROCEDURE UpdateChildCount();
+
+
+
 -- set the global properties that actually documents the DB version, revision and some of the capabilities
-DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13);
+DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13, 14);
 INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion
-INSERT INTO GlobalProperties VALUES (4, 2); -- GlobalProperty_DatabasePatchLevel
+INSERT INTO GlobalProperties VALUES (4, 3); -- GlobalProperty_DatabasePatchLevel
 INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast
 INSERT INTO GlobalProperties VALUES (10, 1); -- GlobalProperty_HasTrigramIndex
 INSERT INTO GlobalProperties VALUES (11, 3); -- GlobalProperty_HasCreateInstance  -- this is actually the 3rd version of HasCreateInstance
 INSERT INTO GlobalProperties VALUES (12, 1); -- GlobalProperty_HasFastCountResources
 INSERT INTO GlobalProperties VALUES (13, 1); -- GlobalProperty_GetLastChangeIndex
+INSERT INTO GlobalProperties VALUES (14, 1); -- GlobalProperty_HasComputeStatisticsReadOnly
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql
--- a/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql	Mon Sep 09 15:21:14 2024 +0200
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql	Mon Dec 16 16:36:12 2024 +0100
@@ -1,4 +1,4 @@
--- This file contains part of the changes required to upgrade from Revision 1 to Revision 2 (DB version 6 and revision 1 or 2)
+-- This file contains part of the changes required to upgrade from Revision 1 to Revision 2 (DB version 6 and revision 1)
 -- It actually contains only the changes that:
    -- can not be executed with an idempotent statement in SQL
    -- or would polute the PrepareIndex.sql
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,31 @@
+-- This file contains part of the changes required to upgrade from Revision 2 to Revision 3 (DB version 6 and revision 2)
+-- It actually contains only the changes that:
+   -- can not be executed with an idempotent statement in SQL
+   -- or would polute the PrepareIndex.sql
+   -- do facilite an up-time upgrade
+-- This file is executed only if the current schema is in revision 2 and it is executed 
+-- before PrepareIndex.sql that is idempotent.
+
+
+-- create a new ChildrenIndex2 that is replacing ChildrenIndex.
+-- We create it in this partial update so it can be created while the system is up !
+DO $$
+DECLARE
+    pg_version text;
+BEGIN
+    SELECT version() INTO pg_version;
+
+    IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN
+        -- PostgreSQL 11 or later
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)';
+    ELSE
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)';
+    END IF;
+END $$;
+
+DROP INDEX IF EXISTS ChildrenIndex;  -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading
+
+
+-- other changes performed in PrepareIndex.sql:
+  -- add ChildCount tables and triggers
+
diff -r 987460ae14c9 -r 3d853c8f5a9e PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3b.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3b.sql	Mon Dec 16 16:36:12 2024 +0100
@@ -0,0 +1,44 @@
+-- This file contains part of the changes required to upgrade from Revision 2 to Revision 3 (DB version 6 and revision 2)
+-- It actually contains only the changes that:
+   -- can not be executed with an idempotent statement in SQL
+   -- or would polute the PrepareIndex.sql
+   -- do facilite an up-time upgrade
+-- This file is executed only if the current schema is in revision 2 and it is executed 
+-- before PrepareIndex.sql that is idempotent.
+
+
+-- create a new ChildrenIndex2 that is replacing ChildrenIndex.
+-- We create it in this partial update so it can be created while the system is up !
+DO $$
+DECLARE
+    pg_version text;
+BEGIN
+    SELECT version() INTO pg_version;
+
+    IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN
+        -- PostgreSQL 11 or later
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)';
+    ELSE
+        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)';
+    END IF;
+END $$;
+
+DROP INDEX IF EXISTS ChildrenIndex;  -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading
+
+-- add the childCount columns in Resources if not yet done
+
+DO $body$
+BEGIN
+	IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN
+		ALTER TABLE Resources ADD COLUMN childcount INTEGER;
+	ELSE
+		raise notice 'the resources.childcount column already exists';
+	END IF;
+
+END $body$;
+
+
+
+-- other changes performed in PrepareIndex.sql:
+  -- add ChildCount tables and triggers
+
diff -r 987460ae14c9 -r 3d853c8f5a9e Resources/CMake/DatabasesFrameworkConfiguration.cmake
--- a/Resources/CMake/DatabasesFrameworkConfiguration.cmake	Mon Sep 09 15:21:14 2024 +0200
+++ b/Resources/CMake/DatabasesFrameworkConfiguration.cmake	Mon Dec 16 16:36:12 2024 +0100
@@ -113,6 +113,7 @@
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/IResult.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/ImplicitTransaction.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/InputFileValue.cpp
+  ${ORTHANC_DATABASES_ROOT}/Framework/Common/Integer32Value.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/Integer64Value.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/NullValue.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/Query.cpp
@@ -120,7 +121,7 @@
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/ResultFileValue.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/RetryDatabaseFactory.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/RetryDatabaseFactory.cpp
-  ${ORTHANC_DATABASES_ROOT}/Framework/Common/StatementLocation.cpp
+  ${ORTHANC_DATABASES_ROOT}/Framework/Common/StatementId.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Common/Utf8StringValue.cpp
   )
 
diff -r 987460ae14c9 -r 3d853c8f5a9e Resources/CMake/DatabasesPluginConfiguration.cmake
--- a/Resources/CMake/DatabasesPluginConfiguration.cmake	Mon Sep 09 15:21:14 2024 +0200
+++ b/Resources/CMake/DatabasesPluginConfiguration.cmake	Mon Dec 16 16:36:12 2024 +0100
@@ -116,6 +116,7 @@
   ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/ISqlLookupFormatter.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/IndexBackend.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/IndexConnectionsPool.cpp
+  ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/MessagesToolbox.cpp
   ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/StorageBackend.cpp
   ${ORTHANC_DATABASES_ROOT}/Resources/Orthanc/Plugins/OrthancPluginCppWrapper.cpp
   )
diff -r 987460ae14c9 -r 3d853c8f5a9e Resources/SyncOrthancFolder.py
diff -r 987460ae14c9 -r 3d853c8f5a9e SQLite/Plugins/IndexPlugin.cpp
--- a/SQLite/Plugins/IndexPlugin.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/SQLite/Plugins/IndexPlugin.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -26,7 +26,9 @@
 
 #include <Logging.h>
 
-#include <google/protobuf/any.h>
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
+#  include <google/protobuf/any.h>
+#endif
 
 #define ORTHANC_PLUGIN_NAME "sqlite-index"
 
@@ -35,7 +37,9 @@
 {
   ORTHANC_PLUGINS_API int32_t OrthancPluginInitialize(OrthancPluginContext* context)
   {
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
     GOOGLE_PROTOBUF_VERIFY_VERSION;
+#endif
 
     if (!OrthancDatabases::InitializePlugin(context, ORTHANC_PLUGIN_NAME, "SQLite", true))
     {
@@ -70,7 +74,8 @@
       OrthancDatabases::IndexBackend::Register(
         new OrthancDatabases::SQLiteIndex(context, "index.db"),   // TODO parameter
         1 /* only 1 connection is possible with SQLite */,
-        0 /* no collision is possible, as SQLite has a global lock */);
+        0 /* no collision is possible, as SQLite has a global lock */,
+        0 /* housekeeping delay, unused for SQLite */);
     }
     catch (Orthanc::OrthancException& e)
     {
@@ -91,7 +96,10 @@
   {
     LOG(WARNING) << "SQLite index is finalizing";
     OrthancDatabases::IndexBackend::Finalize();
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0)
     google::protobuf::ShutdownProtobufLibrary();
+#endif
   }
 
 
diff -r 987460ae14c9 -r 3d853c8f5a9e SQLite/Plugins/SQLiteIndex.cpp
--- a/SQLite/Plugins/SQLiteIndex.cpp	Mon Sep 09 15:21:14 2024 +0200
+++ b/SQLite/Plugins/SQLiteIndex.cpp	Mon Dec 16 16:36:12 2024 +0100
@@ -191,7 +191,7 @@
 
   SQLiteIndex::SQLiteIndex(OrthancPluginContext* context,
                            const std::string& path) :
-    IndexBackend(context),
+    IndexBackend(context, false /* not read-only */),
     path_(path),
     fast_(true)
   {
@@ -203,7 +203,7 @@
 
 
   SQLiteIndex::SQLiteIndex(OrthancPluginContext* context) :
-    IndexBackend(context),
+    IndexBackend(context, false /* not read-only */),
     fast_(true)
   {
   }
@@ -259,4 +259,32 @@
       }
     }
   }
+
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  bool SQLiteIndex::HasFindSupport() const
+  {
+    // TODO-FIND
+    return false;
+  }
+#endif
+
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+  void SQLiteIndex::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                                DatabaseManager& manager,
+                                const Orthanc::DatabasePluginMessages::Find_Request& request)
+  {
+    // TODO-FIND
+    throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+  }
+
+  void SQLiteIndex::ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                                 DatabaseManager& manager,
+                                 const Orthanc::DatabasePluginMessages::Find_Request& request)
+  {
+    // TODO-FIND
+    throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+  }
+#endif
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e SQLite/Plugins/SQLiteIndex.h
--- a/SQLite/Plugins/SQLiteIndex.h	Mon Sep 09 15:21:14 2024 +0200
+++ b/SQLite/Plugins/SQLiteIndex.h	Mon Dec 16 16:36:12 2024 +0100
@@ -67,5 +67,26 @@
     {
       return true;
     }
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+    virtual bool HasFindSupport() const ORTHANC_OVERRIDE;
+#endif
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+    virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                             DatabaseManager& manager,
+                             const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE;
+#endif
+
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+    virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response,
+                              DatabaseManager& manager,
+                              const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE;
+#endif
+
+    virtual bool HasChildCountTable() const ORTHANC_OVERRIDE
+    {
+      return false;
+    }
   };
 }
diff -r 987460ae14c9 -r 3d853c8f5a9e TODO
--- a/TODO	Mon Sep 09 15:21:14 2024 +0200
+++ b/TODO	Mon Dec 16 16:36:12 2024 +0100
@@ -49,6 +49,10 @@
   https://groups.google.com/g/orthanc-users/c/xQelEcKqL9U/m/HsvxwlkvAQAJ
   https://groups.google.com/g/orthanc-users/c/1bkClfZ0KBA/m/s4AlwVh3CQAJ 
 
+* use ReadCommited mode by default
+* use 40 index connection count by default (not 50 because, by default, PG has 100 connections and we want at least
+  a few connections to remain available for sys admins if e.g, 2 Orthanc instances are running on the same server)
+
 -----
 MySQL
 -----
@@ -72,4 +76,4 @@
 ODBC
 ----
 
-* https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=224
\ No newline at end of file
+* Implement labels
\ No newline at end of file