changeset 5553:28cc06e4859a large-queries

Added ExtendedApiV1: /changes
author Alain Mazy <am@orthanc.team>
date Thu, 11 Apr 2024 19:02:20 +0200
parents dcbf0c776945
children 3765085693e5
files NEWS OrthancFramework/Resources/CodeGeneration/ErrorCodes.json OrthancFramework/Sources/SQLite/Connection.h OrthancFramework/Sources/SQLite/StatementId.cpp OrthancFramework/Sources/SQLite/StatementId.h OrthancServer/Plugins/Engine/OrthancPluginDatabaseV4.cpp OrthancServer/Plugins/Engine/PluginsEnumerations.cpp OrthancServer/Plugins/Include/orthanc/OrthancCPlugin.h OrthancServer/Plugins/Include/orthanc/OrthancDatabasePlugin.proto OrthancServer/Resources/ImplementationNotes/DatabasesClassHierarchy.txt OrthancServer/Sources/Database/BaseDatabaseWrapper.cpp OrthancServer/Sources/Database/BaseDatabaseWrapper.h OrthancServer/Sources/Database/IDatabaseWrapper.h OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h OrthancServer/Sources/Database/StatelessDatabaseOperations.cpp OrthancServer/Sources/Database/StatelessDatabaseOperations.h OrthancServer/Sources/OrthancRestApi/OrthancRestChanges.cpp OrthancServer/Sources/OrthancRestApi/OrthancRestSystem.cpp OrthancServer/Sources/ServerEnumerations.cpp OrthancServer/Sources/ServerEnumerations.h
diffstat 21 files changed, 462 insertions(+), 23 deletions(-) [+]
line wrap: on
line diff
--- a/NEWS	Fri Mar 29 23:23:01 2024 +0100
+++ b/NEWS	Thu Apr 11 19:02:20 2024 +0200
@@ -16,6 +16,12 @@
 
 * API version upgraded to 24
 * Added "MaximumPatientCount" in /system
+* Added "ExtendedApiV1" if you have a DB that supports it (the default SQLite DB
+  or PostgreSQL vX.X, MySQL vX.X, ODBC vX.X).
+  - /extended-api-v1/changes now supports 2 more options: 'type' to filter
+    the changes returned by the query and 'to' to potentially cycle through
+    changes in reverse order.
+    example: /extended-api-v1/changes?type=StableStudy&to=7584&limit=100
 
 Plugins
 -------
--- a/OrthancFramework/Resources/CodeGeneration/ErrorCodes.json	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancFramework/Resources/CodeGeneration/ErrorCodes.json	Thu Apr 11 19:02:20 2024 +0200
@@ -337,7 +337,7 @@
   {
     "Code": 1011, 
     "Name": "SQLiteBindOutOfRange", 
-    "Description": "SQLite: Bing a value while out of range (serious error)",
+    "Description": "SQLite: Bind a value while out of range (serious error)",
     "SQLite": true
   },
   {
--- a/OrthancFramework/Sources/SQLite/Connection.h	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancFramework/Sources/SQLite/Connection.h	Thu Apr 11 19:02:20 2024 +0200
@@ -56,6 +56,8 @@
 #endif
 
 #define SQLITE_FROM_HERE ::Orthanc::SQLite::StatementId(__ORTHANC_FILE__, __LINE__)
+#define SQLITE_FROM_HERE_DYNAMIC(sql) ::Orthanc::SQLite::StatementId(__ORTHANC_FILE__, __LINE__, sql)
+
 
 namespace Orthanc
 {
--- a/OrthancFramework/Sources/SQLite/StatementId.cpp	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancFramework/Sources/SQLite/StatementId.cpp	Thu Apr 11 19:02:20 2024 +0200
@@ -56,12 +56,24 @@
     {
     }
 
+    Orthanc::SQLite::StatementId::StatementId(const char *file,
+                                              int line,
+                                              const std::string& statement) :
+      file_(file),
+      line_(line),
+      statement_(statement)
+    {
+    }
+
     bool StatementId::operator< (const StatementId& other) const
     {
       if (line_ != other.line_)
         return line_ < other.line_;
 
-      return strcmp(file_, other.file_) < 0;
+      if (strcmp(file_, other.file_) < 0)
+        return true;
+
+      return statement_ < other.statement_;
     }
   }
 }
--- a/OrthancFramework/Sources/SQLite/StatementId.h	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancFramework/Sources/SQLite/StatementId.h	Thu Apr 11 19:02:20 2024 +0200
@@ -54,6 +54,7 @@
     private:
       const char* file_;
       int line_;
+      std::string statement_;
 
       StatementId(); // Forbidden
 
@@ -61,6 +62,10 @@
       StatementId(const char* file,
                   int line);
 
+      StatementId(const char* file,
+                  int line,
+                  const std::string& statement);
+
       bool operator< (const StatementId& other) const;
     };
   }
--- a/OrthancServer/Plugins/Engine/OrthancPluginDatabaseV4.cpp	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Plugins/Engine/OrthancPluginDatabaseV4.cpp	Thu Apr 11 19:02:20 2024 +0200
@@ -494,6 +494,33 @@
       }
     }
 
+    virtual void GetChanges2(std::list<ServerIndexChange>& target /*out*/,
+                             bool& done /*out*/,
+                             int64_t since,
+                             int64_t to,
+                             uint32_t limit,
+                             ChangeType changeType) ORTHANC_OVERRIDE
+    {
+      assert(database_.GetDatabaseCapabilities().HasExtendedApiV1());
+
+      DatabasePluginMessages::TransactionRequest request;
+      DatabasePluginMessages::TransactionResponse response;
+
+      request.mutable_get_changes2()->set_since(since);
+      request.mutable_get_changes2()->set_limit(limit);
+      request.mutable_get_changes2()->set_to(to);
+      request.mutable_get_changes2()->set_change_type(changeType);
+      ExecuteTransaction(response, DatabasePluginMessages::OPERATION_GET_CHANGES_2, request);
+
+      done = response.get_changes().done();
+
+      target.clear();
+      for (int i = 0; i < response.get_changes().changes().size(); i++)
+      {
+        target.push_back(Convert(response.get_changes().changes(i)));
+      }
+    }
+
     
     virtual void GetChildrenInternalId(std::list<int64_t>& target,
                                        int64_t id) ORTHANC_OVERRIDE
@@ -1363,6 +1390,7 @@
       dbCapabilities_.SetAtomicIncrementGlobalProperty(systemInfo.supports_increment_global_property());
       dbCapabilities_.SetUpdateAndGetStatistics(systemInfo.has_update_and_get_statistics());
       dbCapabilities_.SetMeasureLatency(systemInfo.has_measure_latency());
+      dbCapabilities_.SetHasExtendedApiV1(systemInfo.has_extended_api_v1());
     }
 
     open_ = true;
--- a/OrthancServer/Plugins/Engine/PluginsEnumerations.cpp	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Plugins/Engine/PluginsEnumerations.cpp	Thu Apr 11 19:02:20 2024 +0200
@@ -121,6 +121,9 @@
         case ChangeType_UpdatedMetadata:
           return OrthancPluginChangeType_UpdatedMetadata;
 
+        case ChangeType_INTERNAL_All:
+          return _OrthancPluginChangeType_All;
+
         default:
           throw OrthancException(ErrorCode_ParameterOutOfRange);
       }
--- a/OrthancServer/Plugins/Include/orthanc/OrthancCPlugin.h	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Plugins/Include/orthanc/OrthancCPlugin.h	Thu Apr 11 19:02:20 2024 +0200
@@ -119,8 +119,8 @@
 #endif
 
 #define ORTHANC_PLUGINS_MINIMAL_MAJOR_NUMBER     1
-#define ORTHANC_PLUGINS_MINIMAL_MINOR_NUMBER     12
-#define ORTHANC_PLUGINS_MINIMAL_REVISION_NUMBER  4
+#define ORTHANC_PLUGINS_MINIMAL_MINOR_NUMBER     13
+#define ORTHANC_PLUGINS_MINIMAL_REVISION_NUMBER  0
 
 
 #if !defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE)
@@ -746,6 +746,7 @@
 
   /**
    * The supported types of changes that can be signaled to the change callback.
+   * Note: this enum is not used to store changes in the DB !
    * @ingroup Callbacks
    **/
   typedef enum
@@ -770,6 +771,7 @@
     OrthancPluginChangeType_JobSuccess = 17,        /*!< A Job has completed successfully */
     OrthancPluginChangeType_JobFailure = 18,        /*!< A Job has failed */
 
+    _OrthancPluginChangeType_All = 65535,           /*!< All jobs (when used as a filter in GetChanges) */
     _OrthancPluginChangeType_INTERNAL = 0x7fffffff
   } OrthancPluginChangeType;
 
--- a/OrthancServer/Plugins/Include/orthanc/OrthancDatabasePlugin.proto	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Plugins/Include/orthanc/OrthancDatabasePlugin.proto	Thu Apr 11 19:02:20 2024 +0200
@@ -140,6 +140,7 @@
     bool supports_increment_global_property = 5;
     bool has_update_and_get_statistics = 6;
     bool has_measure_latency = 7;
+    bool has_extended_api_v1 = 8;
   }
 }
 
@@ -292,6 +293,7 @@
   OPERATION_LIST_LABELS = 47;      // New in Orthanc 1.12.0
   OPERATION_INCREMENT_GLOBAL_PROPERTY = 48;      // New in Orthanc 1.12.3
   OPERATION_UPDATE_AND_GET_STATISTICS = 49;      // New in Orthanc 1.12.3
+  OPERATION_GET_CHANGES_2 = 50;    // New in Orthanc 1.13.0
 }
 
 message Rollback {
@@ -412,6 +414,19 @@
   }
 }
 
+message GetChanges2 {
+  message Request {
+    int64 since = 1;
+    int64 to = 2;
+    int32 change_type = 3;
+    uint32 limit = 4;
+  }
+  message Response {
+    repeated ServerIndexChange changes = 1;
+    bool done = 2;
+  }
+}
+
 message GetChildrenInternalId {
   message Request {
     int64 id = 1;
@@ -877,6 +892,7 @@
   ListLabels.Request                      list_labels = 147;
   IncrementGlobalProperty.Request         increment_global_property = 148;
   UpdateAndGetStatistics.Request          update_and_get_statistics = 149;
+  GetChanges2.Request                     get_changes2 = 150;
 }
 
 message TransactionResponse {
@@ -930,6 +946,7 @@
   ListLabels.Response                      list_labels = 147;
   IncrementGlobalProperty.Response         increment_global_property = 148;
   UpdateAndGetStatistics.Response          update_and_get_statistics = 149;
+  GetChanges2.Response                     get_changes2 = 150;
 }
 
 enum RequestType {
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/OrthancServer/Resources/ImplementationNotes/DatabasesClassHierarchy.txt	Thu Apr 11 19:02:20 2024 +0200
@@ -0,0 +1,30 @@
+The main object to access the DB is the ServerIndex class that is accessible from the ServerContext.
+
+ServerIndex inherits from StatelessDatabaseOperations.
+
+StatelessDatabaseOperations owns an IDatabaseWrapper member (db).
+StatelessDatabaseOperations has 2 internal Transaction classes (ReadOnlyTransactions and ReadWriteTransactions) that implements the DB
+operations by calling the methods from IDatabaseWrapper:ITransaction.
+
+IDatabaseWrapper has 2 direct derived classes:
+- BaseDatabaseWrapper which simply provides a "not implemented" implementation of new methods to its derived classes:
+  - OrthancPluginDatabase    that is a legacy plugin interface
+  - OrthancPluginDatabaseV3  that is a legacy plugin interface
+  - SQLiteDatabaseWrapper    that is used by the default SQLite DB in Orthanc
+- OrthancPluginDatabaseV4 that is the latest plugin interface and uses protobuf
+
+When you add a new method in the DB (e.g: UpdateAndGetStatistics with a new signature), you must:
+- define it as a member of StatelessDatabaseOperations
+- define it as a member of StatelessDatabaseOperations::ReadWriteTransactions or StatelessDatabaseOperations::ReadOnlyTransactions
+- define it as a member of IDatabaseWrapper:ITransaction
+- define it in OrthancDatabasePlugin.proto (new request + new response + new message)
+- define it in OrthancPluginDatabaseV4
+- define a NotImplemented default implementation in BaseDatabaseWrapper
+- optionally define it in SQLiteDatabaseWrapper if it can be implemented in SQLite
+- very likely define it as a DbCapabilities in IDatabaseWrapper::DbCapabilities (e.g: Has/SetUpdateAndGetStatistics()) such that the Orthanc
+  core knows if it can use it or not.
+
+Then, in the orthanc-databases repo, you should:
+- define it as a virtual member of IDatabaseBackend
+- define it as a member of IndexBackend
+- add a handler for the new protobuf message in DatabaseBackendAdapterV4
--- a/OrthancServer/Sources/Database/BaseDatabaseWrapper.cpp	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/Database/BaseDatabaseWrapper.cpp	Thu Apr 11 19:02:20 2024 +0200
@@ -44,6 +44,17 @@
     throw OrthancException(ErrorCode_NotImplemented);  // Not supported
   }
 
+  void BaseDatabaseWrapper::BaseTransaction::GetChanges2(std::list<ServerIndexChange>& target /*out*/,
+                                                         bool& done /*out*/,
+                                                         int64_t since,
+                                                         int64_t to,
+                                                         uint32_t limit,
+                                                         ChangeType filterType)
+  {
+    throw OrthancException(ErrorCode_NotImplemented);  // Not supported
+  }
+
+
 
   uint64_t BaseDatabaseWrapper::MeasureLatency()
   {
--- a/OrthancServer/Sources/Database/BaseDatabaseWrapper.h	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/Database/BaseDatabaseWrapper.h	Thu Apr 11 19:02:20 2024 +0200
@@ -46,6 +46,14 @@
                                           int64_t& instancesCount,
                                           int64_t& compressedSize,
                                           int64_t& uncompressedSize) ORTHANC_OVERRIDE;
+
+      virtual void GetChanges2(std::list<ServerIndexChange>& target /*out*/,
+                               bool& done /*out*/,
+                               int64_t since,
+                               int64_t to,
+                               uint32_t limit,
+                               ChangeType filterType) ORTHANC_OVERRIDE;
+
     };
 
     virtual uint64_t MeasureLatency() ORTHANC_OVERRIDE;
--- a/OrthancServer/Sources/Database/IDatabaseWrapper.h	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/Database/IDatabaseWrapper.h	Thu Apr 11 19:02:20 2024 +0200
@@ -51,6 +51,7 @@
       bool hasAtomicIncrementGlobalProperty_;
       bool hasUpdateAndGetStatistics_;
       bool hasMeasureLatency_;
+      bool hasExtendedApiV1_;
 
     public:
       Capabilities() :
@@ -59,7 +60,8 @@
         hasLabelsSupport_(false),
         hasAtomicIncrementGlobalProperty_(false),
         hasUpdateAndGetStatistics_(false),
-        hasMeasureLatency_(false)
+        hasMeasureLatency_(false),
+        hasExtendedApiV1_(false)
       {
       }
 
@@ -93,6 +95,16 @@
         return hasLabelsSupport_;
       }
 
+      void SetHasExtendedApiV1(bool value)
+      {
+        hasExtendedApiV1_ = value;
+      }
+
+      bool HasExtendedApiV1() const
+      {
+        return hasExtendedApiV1_;
+      }
+
       void SetAtomicIncrementGlobalProperty(bool value)
       {
         hasAtomicIncrementGlobalProperty_ = value;
@@ -344,12 +356,22 @@
                                               int64_t increment,
                                               bool shared) = 0;
 
+      // New in Orthanc 1.12.3
       virtual void UpdateAndGetStatistics(int64_t& patientsCount,
                                           int64_t& studiesCount,
                                           int64_t& seriesCount,
                                           int64_t& instancesCount,
                                           int64_t& compressedSize,
                                           int64_t& uncompressedSize) = 0;
+
+      // New in Orthanc 1.13.0
+      virtual void GetChanges2(std::list<ServerIndexChange>& target /*out*/,
+                               bool& done /*out*/,
+                               int64_t since,
+                               int64_t to,
+                               uint32_t limit,
+                               ChangeType filterType) = 0;
+
     };
 
 
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.cpp	Thu Apr 11 19:02:20 2024 +0200
@@ -233,11 +233,12 @@
     void GetChangesInternal(std::list<ServerIndexChange>& target,
                             bool& done,
                             SQLite::Statement& s,
-                            uint32_t limit)
+                            uint32_t limit,
+                            bool returnFirstResults) // the statement usually returns limit+1 results while we only need the limit results -> we need to know which ones to return, the firsts or the lasts
     {
       target.clear();
 
-      while (target.size() < limit && s.Step())
+      while (s.Step())
       {
         int64_t seq = s.ColumnInt64(0);
         ChangeType changeType = static_cast<ChangeType>(s.ColumnInt(1));
@@ -250,7 +251,22 @@
         target.push_back(ServerIndexChange(seq, changeType, resourceType, publicId, date));
       }
 
-      done = !(target.size() == limit && s.Step());
+      done = target.size() <= limit;  // 'done' means "there are no more other changes of this type in that direction (depending on since/to)"
+      
+      // if we have retrieved more changes than requested -> cleanup
+      if (target.size() > limit)
+      {
+        assert(target.size() == limit+1); // the statement should only request 1 element more
+
+        if (returnFirstResults)
+        {
+          target.pop_back();
+        }
+        else
+        {
+          target.pop_front();
+        }
+      }
     }
 
 
@@ -540,10 +556,76 @@
                             int64_t since,
                             uint32_t limit) ORTHANC_OVERRIDE
     {
-      SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes WHERE seq>? ORDER BY seq LIMIT ?");
-      s.BindInt64(0, since);
-      s.BindInt(1, limit + 1);
-      GetChangesInternal(target, done, s, limit);
+      GetChanges2(target, done, since, -1, limit, ChangeType_INTERNAL_All);
+    }
+
+    virtual void GetChanges2(std::list<ServerIndexChange>& target /*out*/,
+                             bool& done /*out*/,
+                             int64_t since,
+                             int64_t to,
+                             uint32_t limit,
+                             ChangeType filterType) ORTHANC_OVERRIDE
+    {
+      std::vector<std::string> filters;
+      bool hasSince = false;
+      bool hasTo = false;
+      bool hasFilterType = false;
+
+      if (since > 0)
+      {
+        hasSince = true;
+        filters.push_back("seq>?");
+      }
+      if (to != -1)
+      {
+        hasTo = true;
+        filters.push_back("seq<=?");
+      }
+      if (filterType != ChangeType_INTERNAL_All)
+      {
+        hasFilterType = true;
+        filters.push_back("changeType=?");
+      }
+
+      std::string filtersString;
+      if (filters.size() > 0)
+      {
+        Toolbox::JoinStrings(filtersString, filters, " AND ");
+        filtersString = "WHERE " + filtersString;
+      }
+
+      std::string sql;
+      bool returnFirstResults;
+      if (hasTo && !hasSince)
+      {
+        // in this case, we want the largest values in the LIMIT clause but we want them ordered in ascending order
+        sql = "SELECT * FROM (SELECT * FROM Changes " + filtersString + " ORDER BY seq DESC LIMIT ?) ORDER BY seq ASC";
+        returnFirstResults = false;
+      }
+      else
+      {
+        // default query: we want the smallest values ordered in ascending order
+        sql = "SELECT * FROM Changes " + filtersString + " ORDER BY seq ASC LIMIT ?";
+        returnFirstResults = true;
+      }
+       
+      SQLite::Statement s(db_, SQLITE_FROM_HERE_DYNAMIC(sql), sql);
+
+      int paramCounter = 0;
+      if (hasSince)
+      {
+        s.BindInt64(paramCounter++, since);
+      }
+      if (hasTo)
+      {
+        s.BindInt64(paramCounter++, to);
+      }
+      if (hasFilterType)
+      {
+        s.BindInt(paramCounter++, filterType);
+      }
+      s.BindInt(paramCounter++, limit + 1); // we take limit+1 because we use the +1 to know if "Done" must be set to true
+      GetChangesInternal(target, done, s, limit, returnFirstResults);
     }
 
 
@@ -604,7 +686,7 @@
     {
       bool done;  // Ignored
       SQLite::Statement s(db_, SQLITE_FROM_HERE, "SELECT * FROM Changes ORDER BY seq DESC LIMIT 1");
-      GetChangesInternal(target, done, s, 1);
+      GetChangesInternal(target, done, s, 1, true);
     }
 
 
@@ -1327,6 +1409,7 @@
     // TODO: implement revisions in SQLite
     dbCapabilities_.SetFlushToDisk(true);
     dbCapabilities_.SetLabelsSupport(true);
+    dbCapabilities_.SetHasExtendedApiV1(true);
     db_.Open(path);
   }
 
@@ -1339,6 +1422,7 @@
     // TODO: implement revisions in SQLite
     dbCapabilities_.SetFlushToDisk(true);
     dbCapabilities_.SetLabelsSupport(true);
+    dbCapabilities_.SetHasExtendedApiV1(true);
     db_.OpenInMemory();
   }
 
--- a/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/Database/SQLiteDatabaseWrapper.h	Thu Apr 11 19:02:20 2024 +0200
@@ -56,7 +56,8 @@
     void GetChangesInternal(std::list<ServerIndexChange>& target,
                             bool& done,
                             SQLite::Statement& s,
-                            uint32_t maxResults);
+                            uint32_t maxResults,
+                            bool returnFirstResults);
 
     void GetExportedResourcesInternal(std::list<ExportedResource>& target,
                                       bool& done,
--- a/OrthancServer/Sources/Database/StatelessDatabaseOperations.cpp	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/Database/StatelessDatabaseOperations.cpp	Thu Apr 11 19:02:20 2024 +0200
@@ -1221,6 +1221,39 @@
   }
 
 
+  void StatelessDatabaseOperations::GetChanges2(Json::Value& target,
+                                                int64_t since,
+                                                int64_t to,                               
+                                                unsigned int maxResults,
+                                                ChangeType changeType)
+  {
+    class Operations : public ReadOnlyOperationsT5<Json::Value&, int64_t, int64_t, unsigned int, unsigned int>
+    {
+    public:
+      virtual void ApplyTuple(ReadOnlyTransaction& transaction,
+                              const Tuple& tuple) ORTHANC_OVERRIDE
+      {
+        std::list<ServerIndexChange> changes;
+        bool done;
+        bool hasLast = false;
+        int64_t last = 0;
+
+        transaction.GetChanges2(changes, done, tuple.get<1>(), tuple.get<2>(), tuple.get<3>(), static_cast<ChangeType>(tuple.get<4>()));
+        if (changes.empty())
+        {
+          last = transaction.GetLastChangeIndex();
+          hasLast = true;
+        }
+
+        FormatLog(tuple.get<0>(), changes, "Changes", done, tuple.get<1>(), hasLast, last);
+      }
+    };
+    
+    Operations operations;
+    operations.Apply(*this, target, since, to, maxResults, changeType);
+  }
+
+
   void StatelessDatabaseOperations::GetLastChange(Json::Value& target)
   {
     class Operations : public ReadOnlyOperationsT1<Json::Value&>
@@ -3775,4 +3808,10 @@
     boost::shared_lock<boost::shared_mutex> lock(mutex_);
     return db_.GetDatabaseCapabilities().HasLabelsSupport();
   }
+
+  bool StatelessDatabaseOperations::HasExtendedApiV1()
+  {
+    boost::shared_lock<boost::shared_mutex> lock(mutex_);
+    return db_.GetDatabaseCapabilities().HasExtendedApiV1();
+  }
 }
--- a/OrthancServer/Sources/Database/StatelessDatabaseOperations.h	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/Database/StatelessDatabaseOperations.h	Thu Apr 11 19:02:20 2024 +0200
@@ -245,6 +245,16 @@
         transaction_.GetChanges(target, done, since, limit);
       }
 
+      void GetChanges2(std::list<ServerIndexChange>& target /*out*/,
+                       bool& done /*out*/,
+                       int64_t since,
+                       int64_t to,
+                       uint32_t limit,
+                       ChangeType filterType)
+      {
+        transaction_.GetChanges2(target, done, since, to, limit, filterType);
+      }
+
       void GetChildrenInternalId(std::list<int64_t>& target,
                                  int64_t id)
       {
@@ -628,8 +638,16 @@
                     int64_t since,
                     uint32_t limit);
 
+    void GetChanges2(Json::Value& target,
+                     int64_t since,
+                     int64_t to,
+                     uint32_t limit,
+                     ChangeType filterType);
+
     void GetLastChange(Json::Value& target);
 
+    bool HasExtendedApiV1();
+
     void GetExportedResources(Json::Value& target,
                               int64_t since,
                               uint32_t limit);
--- a/OrthancServer/Sources/OrthancRestApi/OrthancRestChanges.cpp	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/OrthancRestApi/OrthancRestChanges.cpp	Thu Apr 11 19:02:20 2024 +0200
@@ -29,12 +29,14 @@
 {
   // Changes API --------------------------------------------------------------
  
-  static void GetSinceAndLimit(int64_t& since,
-                               unsigned int& limit,
-                               bool& last,
-                               const RestApiGetCall& call)
+  static void GetSinceToAndLimit(int64_t& since,
+                                 int64_t& to,
+                                 unsigned int& limit,
+                                 bool& last,
+                                 const RestApiGetCall& call)
   {
     static const unsigned int DEFAULT_LIMIT = 100;
+    static const int64_t DEFAULT_TO = -1;
     
     if (call.HasArgument("last"))
     {
@@ -47,11 +49,13 @@
     try
     {
       since = boost::lexical_cast<int64_t>(call.GetArgument("since", "0"));
+      to = boost::lexical_cast<int64_t>(call.GetArgument("to", boost::lexical_cast<std::string>(DEFAULT_TO)));
       limit = boost::lexical_cast<unsigned int>(call.GetArgument("limit", boost::lexical_cast<std::string>(DEFAULT_LIMIT)));
     }
     catch (boost::bad_lexical_cast&)
     {
       since = 0;
+      to = DEFAULT_TO;
       limit = DEFAULT_LIMIT;
       return;
     }
@@ -79,11 +83,11 @@
     
     ServerContext& context = OrthancRestApi::GetContext(call);
 
-    //std::string filter = GetArgument(getArguments, "filter", "");
     int64_t since;
+    int64_t toNotUsed;
     unsigned int limit;
     bool last;
-    GetSinceAndLimit(since, limit, last, call);
+    GetSinceToAndLimit(since, toNotUsed, limit, last, call);
 
     Json::Value result;
     if (last)
@@ -98,6 +102,61 @@
     call.GetOutput().AnswerJson(result);
   }
 
+  static void GetChanges2(RestApiGetCall& call)
+  {
+    if (call.IsDocumentation())
+    {
+      call.GetDocumentation()
+        .SetTag("Tracking changes")
+        .SetSummary("List changes")
+        .SetDescription("Whenever Orthanc receives a new DICOM instance, this event is recorded in the so-called _Changes Log_. This enables remote scripts to react to the arrival of new DICOM resources. A typical application is auto-routing, where an external script waits for a new DICOM instance to arrive into Orthanc, then forward this instance to another modality.")
+        .SetHttpGetArgument("limit", RestApiCallDocumentation::Type_Number, "Limit the number of results", false)
+        .SetHttpGetArgument("since", RestApiCallDocumentation::Type_Number, "Show only the resources since the provided index", false)
+        .SetHttpGetArgument("to", RestApiCallDocumentation::Type_Number, "Show only the resources till the provided index", false)
+        .SetHttpGetArgument("type", RestApiCallDocumentation::Type_String, "Show only the changes of the provided type", false)
+        .AddAnswerType(MimeType_Json, "The list of changes")
+        .SetAnswerField("Changes", RestApiCallDocumentation::Type_JsonListOfObjects, "The individual changes")
+        .SetAnswerField("Done", RestApiCallDocumentation::Type_Boolean,
+                        "Whether the last reported change is the last of the full history")
+        .SetAnswerField("Last", RestApiCallDocumentation::Type_Number,
+                        "The index of the last reported change, can be used for the `since` argument in subsequent calls to this route")
+        .SetHttpGetSample("https://orthanc.uclouvain.be/demo/changes?since=0&limit=2", true);
+      return;
+    }
+    
+    ServerContext& context = OrthancRestApi::GetContext(call);
+
+    int64_t since, to;
+    ChangeType filterType = ChangeType_INTERNAL_All;
+
+    unsigned int limit;
+    bool last;
+    GetSinceToAndLimit(since, to, limit, last, call);
+
+    std::string filterArgument = call.GetArgument("type", "all");
+    if (filterArgument != "all" && filterArgument != "All")
+    {
+      filterType = StringToChangeType(filterArgument);
+    }
+
+    Json::Value result;
+    if (last)
+    {
+      context.GetIndex().GetLastChange(result);
+    }
+    else
+    {
+      if (filterType != ChangeType_INTERNAL_All && !context.GetIndex().HasExtendedApiV1())
+      {
+        throw OrthancException(ErrorCode_ParameterOutOfRange, "Trying to filter changes while the Database backend does not support it (requires ExtendedApiV1)");
+      }
+
+      context.GetIndex().GetChanges2(result, since, to, limit, filterType);
+    }
+
+    call.GetOutput().AnswerJson(result);
+  }
+
 
   static void DeleteChanges(RestApiDeleteCall& call)
   {
@@ -138,10 +197,10 @@
 
     ServerContext& context = OrthancRestApi::GetContext(call);
 
-    int64_t since;
+    int64_t since, to;
     unsigned int limit;
     bool last;
-    GetSinceAndLimit(since, limit, last, call);
+    GetSinceToAndLimit(since, to, limit, last, call);
 
     Json::Value result;
     if (last)
@@ -179,5 +238,9 @@
     Register("/changes", DeleteChanges);
     Register("/exports", GetExports);
     Register("/exports", DeleteExports);
+    if (context_.GetIndex().HasExtendedApiV1())
+    {
+      Register("/extended-api-v1/changes", GetChanges2);
+    }
   }
 }
--- a/OrthancServer/Sources/OrthancRestApi/OrthancRestSystem.cpp	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/OrthancRestApi/OrthancRestSystem.cpp	Thu Apr 11 19:02:20 2024 +0200
@@ -91,6 +91,7 @@
     static const char* const MAXIMUM_STORAGE_MODE = "MaximumStorageMode";
     static const char* const USER_METADATA = "UserMetadata";
     static const char* const HAS_LABELS = "HasLabels";
+    static const char* const HAS_EXTENDED_API_V1 = "HasExtendedApiV1";
 
     if (call.IsDocumentation())
     {
@@ -137,6 +138,8 @@
                         "The configured UserMetadata (new in Orthanc 1.12.0)")
         .SetAnswerField(HAS_LABELS, RestApiCallDocumentation::Type_Boolean,
                         "Whether the database back-end supports labels (new in Orthanc 1.12.0)")
+        .SetAnswerField(HAS_EXTENDED_API_V1, RestApiCallDocumentation::Type_Boolean,
+                        "Whether the database back-end supports extended API v1 (new in Orthanc 1.13.0)")
         .SetHttpGetSample("https://orthanc.uclouvain.be/demo/system", true);
       return;
     }
@@ -195,6 +198,7 @@
     GetUserMetadataConfiguration(result[USER_METADATA]);
 
     result[HAS_LABELS] = OrthancRestApi::GetIndex(call).HasLabelsSupport();
+    result[HAS_EXTENDED_API_V1] = OrthancRestApi::GetIndex(call).HasExtendedApiV1();
     
     call.GetOutput().AnswerJson(result);
   }
--- a/OrthancServer/Sources/ServerEnumerations.cpp	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/ServerEnumerations.cpp	Thu Apr 11 19:02:20 2024 +0200
@@ -430,6 +430,86 @@
     }
   }
 
+  ChangeType StringToChangeType(const std::string& value)
+  {
+    if (value == "CompletedSeries")
+    {
+      return ChangeType_CompletedSeries;
+    }
+    else if (value == "NewInstance")
+    {
+      return ChangeType_NewInstance;
+    }
+    else if (value == "NewPatient")
+    {
+      return ChangeType_NewPatient;
+    }
+    else if (value == "NewSeries")
+    {
+      return ChangeType_NewSeries;
+    }
+    else if (value == "NewStudy")
+    {
+      return ChangeType_NewStudy;
+    }
+    else if (value == "AnonymizedStudy")
+    {
+      return ChangeType_AnonymizedStudy;
+    }
+    else if (value == "AnonymizedSeries")
+    {
+      return ChangeType_AnonymizedSeries;
+    }
+    else if (value == "ModifiedStudy")
+    {
+      return ChangeType_ModifiedStudy;
+    }
+    else if (value == "ModifiedSeries")
+    {
+      return ChangeType_ModifiedSeries;
+    }
+    else if (value == "AnonymizedPatient")
+    {
+      return ChangeType_AnonymizedPatient;
+    }
+    else if (value == "ModifiedPatient")
+    {
+      return ChangeType_ModifiedPatient;
+    }
+    else if (value == "StablePatient")
+    {
+      return ChangeType_StablePatient;
+    }
+    else if (value == "StableStudy")
+    {
+      return ChangeType_StableStudy;
+    }
+    else if (value == "StableSeries")
+    {
+      return ChangeType_StableSeries;
+    }
+    else if (value == "Deleted")
+    {
+      return ChangeType_Deleted;
+    }
+    else if (value == "NewChildInstance")
+    {
+      return ChangeType_NewChildInstance;
+    }
+    else if (value == "UpdatedAttachment")
+    {
+      return ChangeType_UpdatedAttachment;
+    }
+    else if (value == "UpdatedMetadata")
+    {
+      return ChangeType_UpdatedMetadata;
+    }
+    else
+    {
+      throw OrthancException(ErrorCode_ParameterOutOfRange, "Invalid value for a change: " + value);
+    }
+  }
+
 
   const char* EnumerationToString(Verbosity verbosity)
   {
--- a/OrthancServer/Sources/ServerEnumerations.h	Fri Mar 29 23:23:01 2024 +0100
+++ b/OrthancServer/Sources/ServerEnumerations.h	Thu Apr 11 19:02:20 2024 +0200
@@ -190,7 +190,9 @@
 
     // The changes below this point are not logged into the database
     ChangeType_Deleted = 4096,
-    ChangeType_NewChildInstance = 4097
+    ChangeType_NewChildInstance = 4097,
+
+    ChangeType_INTERNAL_All = 65535 // used to filter changes
   };
 
   enum BuiltinDecoderTranscoderOrder
@@ -249,6 +251,8 @@
   const char* EnumerationToString(StoreStatus status);
 
   const char* EnumerationToString(ChangeType type);
+  
+  ChangeType StringToChangeType(const std::string& value);
 
   const char* EnumerationToString(Verbosity verbosity);