Mercurial > hg > orthanc-databases

changeset 532:25cfcb752af6 large-queries

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
merged find-refactoring -> large-queries
author Alain Mazy <am@orthanc.team>
date Fri, 06 Sep 2024 15:31:33 +0200
parents 48aba35fe64e (diff) 61338585e7f7 (current diff)
children 2d3163d992fd f3738a6351c2
files Framework/Common/DatabaseManager.cpp Framework/Plugins/DatabaseBackendAdapterV4.cpp Framework/Plugins/IDatabaseBackend.h Framework/Plugins/IndexBackend.cpp Framework/Plugins/IndexBackend.h PostgreSQL/Plugins/PostgreSQLIndex.cpp
diffstat 22 files changed, 520 insertions(+), 190 deletions(-) [+]
line wrap: on
line diff
--- a/Framework/Common/DatabaseManager.cpp	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/Common/DatabaseManager.cpp	Fri Sep 06 15:31:33 2024 +0200
@@ -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);
--- a/Framework/Common/DatabaseManager.h	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/Common/DatabaseManager.h	Fri Sep 06 15:31:33 2024 +0200
@@ -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();
@@ -207,11 +207,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);
 
--- a/Framework/Common/DatabasesEnumerations.h	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/Common/DatabasesEnumerations.h	Fri Sep 06 15:31:33 2024 +0200
@@ -31,6 +31,7 @@
     ValueType_BinaryString,
     ValueType_InputFile,
     ValueType_Integer64,
+    ValueType_Integer32,
     ValueType_Null,
     ValueType_ResultFile,
     ValueType_Utf8String
--- a/Framework/Common/Dictionary.cpp	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/Common/Dictionary.cpp	Fri Sep 06 15:31:33 2024 +0200
@@ -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);
--- a/Framework/Common/Dictionary.h	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/Common/Dictionary.h	Fri Sep 06 15:31:33 2024 +0200
@@ -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;
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/Integer32Value.cpp	Fri Sep 06 15:31:33 2024 +0200
@@ -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);
+    }
+  }
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/Integer32Value.h	Fri Sep 06 15:31:33 2024 +0200
@@ -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;
+  };
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/StatementId.cpp	Fri Sep 06 15:31:33 2024 +0200
@@ -0,0 +1,44 @@
+/**
+ * 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>
+
+namespace OrthancDatabases
+{
+  bool StatementId::operator< (const StatementId& other) const
+  {
+    if (line_ != other.line_)
+    {
+      return line_ < other.line_;
+    }
+
+    if (strcmp(file_, other.file_) < 0)
+    {
+      return true;
+    }
+
+    return statement_ < other.statement_;
+  }
+}
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/Framework/Common/StatementId.h	Fri Sep 06 15:31:33 2024 +0200
@@ -0,0 +1,77 @@
+/**
+ * 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_;
+
+    StatementId(); // Forbidden
+    
+  public:
+    StatementId(const char* file,
+                int line) :
+      file_(file),
+      line_(line)
+    {
+    }
+
+    StatementId(const char* file,
+                int line,
+                const std::string& statement) :
+      file_(file),
+      line_(line),
+      statement_(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;
+  };
+}
--- a/Framework/Common/StatementLocation.cpp	Fri Jul 19 14:00:35 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;
-    }
-  }
-}
--- a/Framework/Common/StatementLocation.h	Fri Jul 19 14:00:35 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;
-  };
-}
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp	Fri Sep 06 15:31:33 2024 +0200
@@ -441,6 +441,7 @@
 
 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
         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;
@@ -787,7 +788,19 @@
         response.mutable_get_changes()->set_done(done);
         break;
       }
-      
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
+      case Orthanc::DatabasePluginMessages::OPERATION_GET_CHANGES_EXTENDED:
+      {
+        Output output(*response.mutable_get_changes());
+
+        bool done;
+        backend.GetChangesExtended(output, done, manager, request.get_changes_extended().since(), request.get_changes_extended().to(), static_cast<OrthancPluginChangeType>(request.get_changes_extended().change_type()), 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;
--- a/Framework/Plugins/IDatabaseBackend.h	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/Plugins/IDatabaseBackend.h	Fri Sep 06 15:31:33 2024 +0200
@@ -118,6 +118,14 @@
                             int64_t since,
                             uint32_t limit) = 0;
 
+    virtual void GetChangesExtended(IDatabaseBackendOutput& output,
+                                    bool& done /*out*/,
+                                    DatabaseManager& manager,
+                                    int64_t since,
+                                    int64_t to,
+                                    int32_t changeType,
+                                    uint32_t limit) = 0;
+
     virtual void GetChildrenInternalId(std::list<int64_t>& target /*out*/,
                                        DatabaseManager& manager,
                                        int64_t id) = 0;
@@ -387,6 +395,7 @@
 
 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
     virtual bool HasFindSupport() const = 0;
+    virtual bool HasExtendedChanges() const = 0;
 #endif
 
 #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)
--- a/Framework/Plugins/IndexBackend.cpp	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/Plugins/IndexBackend.cpp	Fri Sep 06 15:31:33 2024 +0200
@@ -117,28 +117,60 @@
                                          DatabaseManager& manager,
                                          DatabaseManager::CachedStatement& statement,
                                          const Dictionary& args,
-                                         uint32_t limit)
+                                         uint32_t limit,
+                                         bool returnFirstResults)
   {
+    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)
+      {
+      }
+    };
+
     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_);
+    }
   }
 
 
@@ -554,39 +586,113 @@
     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;
+#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5)    
+    GetChangesExtended(output, done, manager, since, -1, _OrthancPluginChangeType_All, limit);
+#else
+    GetChangesExtended(output, done, manager, since, -1, 65535, limit);
+#endif
+  }
+
+  /* Use GetOutput().AnswerChange() */
+  void IndexBackend::GetChangesExtended(IDatabaseBackendOutput& output,
+                                        bool& done /*out*/,
+                                        DatabaseManager& manager,
+                                        int64_t since,
+                                        int64_t to,
+                                        int32_t changeType,
+                                        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;
+    bool hasFilterType = false;    
+
+    if (since > 0)
+    {
+      hasSince = true;
+      filters.push_back("seq>${since}");
+    }
+    if (to != -1)
+    {
+      hasTo = true;
+      filters.push_back("seq<=${to}");
+    }
+    if (changeType != _OrthancPluginChangeType_All)
+    {
+      hasFilterType = true;
+      filters.push_back("changeType=${changeType}");
+    }
+
+    std::string filtersString;
+    if (filters.size() > 0)
+    {
+      Orthanc::Toolbox::JoinStrings(filtersString, filters, " AND ");
+      filtersString = "WHERE " + filtersString;
+    }
+
+    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);
+    }
+
+    if (hasFilterType)
+    {
+      statement.SetParameterType("changeType", ValueType_Integer64);
+      args.SetIntegerValue("changeType", changeType);
+    }
+
+    ReadChangesInternal(output, done, manager, statement, args, limit, returnFirstResults);
   }
 
     
@@ -686,7 +792,7 @@
     Dictionary args;
 
     bool done;  // Ignored
-    ReadChangesInternal(output, done, manager, statement, args, 1);
+    ReadChangesInternal(output, done, manager, statement, args, 1, true);
   }
 
     
--- a/Framework/Plugins/IndexBackend.h	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/Plugins/IndexBackend.h	Fri Sep 06 15:31:33 2024 +0200
@@ -65,7 +65,8 @@
                              DatabaseManager& manager,
                              DatabaseManager::CachedStatement& statement,
                              const Dictionary& args,
-                             uint32_t limit);
+                             uint32_t limit,
+                             bool returnFirstResults);
 
     void ReadExportedResourcesInternal(IDatabaseBackendOutput& output,
                                        bool& done,
@@ -130,7 +131,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,
+                                    int32_t changeType,
+                                    uint32_t limit) ORTHANC_OVERRIDE;
+
     virtual void GetChildrenInternalId(std::list<int64_t>& target /*out*/,
                                        DatabaseManager& manager,
                                        int64_t id) ORTHANC_OVERRIDE;
@@ -420,6 +429,13 @@
 
     virtual uint64_t MeasureLatency(DatabaseManager& manager) ORTHANC_OVERRIDE;
 
+    // New primitive since Orthanc 1.12.5
+    virtual bool HasExtendedChanges() const ORTHANC_OVERRIDE
+    {
+      return true;
+    }
+
+
     /**
      * "maxDatabaseRetries" is to handle
      * "OrthancPluginErrorCode_DatabaseCannotSerialize" if there is a
--- a/Framework/PostgreSQL/PostgreSQLStatement.cpp	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/PostgreSQL/PostgreSQLStatement.cpp	Fri Sep 06 15:31:33 2024 +0200
@@ -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;
--- a/Framework/PostgreSQL/PostgreSQLTransaction.cpp	Fri Jul 19 14:00:35 2024 +0200
+++ b/Framework/PostgreSQL/PostgreSQLTransaction.cpp	Fri Sep 06 15:31:33 2024 +0200
@@ -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;
   }
 
--- a/MySQL/NEWS	Fri Jul 19 14:00:35 2024 +0200
+++ b/MySQL/NEWS	Fri Sep 06 15:31:33 2024 +0200
@@ -1,3 +1,10 @@
+Pending changes in the mainline
+===============================
+
+* Added support for ExtendedApiV1:
+  - changes?type=...&to=...
+
+
 Release 5.2 (2024-06-06)
 ========================
 
--- a/Odbc/NEWS	Fri Jul 19 14:00:35 2024 +0200
+++ b/Odbc/NEWS	Fri Sep 06 15:31:33 2024 +0200
@@ -7,6 +7,8 @@
 Optimal Orthanc runtime: 1.12.0+
 
 * Fix check of Orthanc runtime version
+* Added support for ExtendedApiV1:
+  - changes?type=...&to=...
 
 
 Release 1.2 (2024-03-06)
--- a/PostgreSQL/NEWS	Fri Jul 19 14:00:35 2024 +0200
+++ b/PostgreSQL/NEWS	Fri Sep 06 15:31:33 2024 +0200
@@ -6,6 +6,15 @@
 Minimum Orthanc runtime: 1.12.3
 
 * Fix updates from plugin version 3.3 to latest version
+* Added support for ExtendedApiV1:
+  - 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 (to be continued)
 
 
 Release 6.2 (2024-03-25)
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Fri Jul 19 14:00:35 2024 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Fri Sep 06 15:31:33 2024 +0200
@@ -487,23 +487,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 +528,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,13 +569,17 @@
     
     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");
     }
 
@@ -578,12 +599,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 +624,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);
 
--- a/Resources/CMake/DatabasesFrameworkConfiguration.cmake	Fri Jul 19 14:00:35 2024 +0200
+++ b/Resources/CMake/DatabasesFrameworkConfiguration.cmake	Fri Sep 06 15:31:33 2024 +0200
@@ -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
   )