Mercurial > hg > orthanc-databases

changeset 760:05d20326b334 pg-next-699

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
integration mainline->pg-next-699
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 26 Nov 2025 14:23:53 +0100
parents 0a9a6f5b8157 (diff) da9d68c3bf6b (current diff)
children 75ab74d68f08
files Framework/Plugins/DatabaseBackendAdapterV4.cpp Framework/PostgreSQL/PostgreSQLParameters.cpp PostgreSQL/NEWS Resources/Orthanc/Plugins/OrthancPluginCppWrapper.cpp
diffstat 18 files changed, 364 insertions(+), 115 deletions(-) [+]
line wrap: on
line diff
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp	Wed Nov 26 14:20:16 2025 +0100
+++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp	Wed Nov 26 14:23:53 2025 +0100
@@ -464,6 +464,10 @@
         response.mutable_get_system_information()->set_supports_queues(accessor.GetBackend().HasQueues());
 #endif
 
+#if ORTHANC_PLUGINS_HAS_RESERVE_QUEUE_VALUE == 1
+        response.mutable_get_system_information()->set_supports_reserve_queue_value(accessor.GetBackend().HasReserveQueueValue());
+#endif
+
 #if ORTHANC_PLUGINS_HAS_KEY_VALUE_STORES == 1
         response.mutable_get_system_information()->set_supports_key_value_stores(accessor.GetBackend().HasKeyValueStores());
 #endif
@@ -1391,6 +1395,37 @@
 
 #endif
 
+#if ORTHANC_PLUGINS_HAS_RESERVE_QUEUE_VALUE == 1
+      case Orthanc::DatabasePluginMessages::OPERATION_RESERVE_QUEUE_VALUE:
+      {
+        std::string value;
+        uint64_t valueId;
+        bool found = backend.ReserveQueueValue(value, valueId, manager,
+                                               request.reserve_queue_value().queue_id(),
+                                               request.reserve_queue_value().origin() == Orthanc::DatabasePluginMessages::QUEUE_ORIGIN_FRONT,
+                                               request.reserve_queue_value().release_timeout());
+        response.mutable_reserve_queue_value()->set_found(found);
+        
+        if (found)
+        {
+          response.mutable_reserve_queue_value()->set_value(value);
+          response.mutable_reserve_queue_value()->set_value_id(valueId);
+        }
+
+        break;
+      }
+
+      case Orthanc::DatabasePluginMessages::OPERATION_ACKNOWLEDGE_QUEUE_VALUE:
+      {
+        backend.AcknowledgeQueueValue(manager,
+                                      request.acknowledge_queue_value().queue_id(),
+                                      request.acknowledge_queue_value().value_id());
+
+        break;
+      }
+
+#endif
+
 #if ORTHANC_PLUGINS_HAS_ATTACHMENTS_CUSTOM_DATA == 1
       case Orthanc::DatabasePluginMessages::OPERATION_GET_ATTACHMENT_CUSTOM_DATA:
       {
--- a/Framework/Plugins/IDatabaseBackend.h	Wed Nov 26 14:20:16 2025 +0100
+++ b/Framework/Plugins/IDatabaseBackend.h	Wed Nov 26 14:23:53 2025 +0100
@@ -139,6 +139,8 @@
 
     virtual bool HasQueues() const = 0;
 
+    virtual bool HasReserveQueueValue() const = 0;
+
     virtual bool HasAuditLogs() const = 0;
 
     virtual void AddAttachment(DatabaseManager& manager,
@@ -525,6 +527,19 @@
                                   const std::string& queueId) = 0;
 #endif
 
+#if ORTHANC_PLUGINS_HAS_RESERVE_QUEUE_VALUE == 1
+    virtual bool ReserveQueueValue(std::string& value,
+                                   uint64_t& valueId,
+                                   DatabaseManager& manager,
+                                   const std::string& queueId,
+                                   bool fromFront,
+                                   uint32_t reserveTimeout) = 0;
+
+    virtual void AcknowledgeQueueValue(DatabaseManager& manager,
+                                       const std::string& queueId,
+                                       uint64_t valueId) = 0;
+#endif
+
 #if ORTHANC_PLUGINS_HAS_ATTACHMENTS_CUSTOM_DATA == 1
     virtual void GetAttachmentCustomData(std::string& customData,
                                          DatabaseManager& manager,
--- a/Framework/Plugins/ISqlLookupFormatter.h	Wed Nov 26 14:20:16 2025 +0100
+++ b/Framework/Plugins/ISqlLookupFormatter.h	Wed Nov 26 14:23:53 2025 +0100
@@ -30,6 +30,7 @@
 
 #pragma once
 
+#include "../Common/Dictionary.h"
 #include "MessagesToolbox.h"
 
 #include <boost/noncopyable.hpp>
@@ -79,6 +80,8 @@
 
     virtual std::string FormatFloatCast() const = 0;
 
+    virtual const Dictionary& GetDictionary() const = 0;
+
     static void GetLookupLevels(Orthanc::ResourceType& lowerLevel,
                                 Orthanc::ResourceType& upperLevel,
                                 const Orthanc::ResourceType& queryLevel,
--- a/Framework/Plugins/IndexBackend.cpp	Wed Nov 26 14:20:16 2025 +0100
+++ b/Framework/Plugins/IndexBackend.cpp	Wed Nov 26 14:23:53 2025 +0100
@@ -4505,30 +4505,38 @@
     }
 
 
-    bool IndexBackend::DequeueValueSQLite(std::string& value,
-                                          DatabaseManager& manager,
-                                          const std::string& queueId,
-                                          bool fromFront)
+    bool IndexBackend::DequeueValue(std::string& value,
+                                    DatabaseManager& manager,
+                                    const std::string& queueId,
+                                    bool fromFront)
     {
-      assert(manager.GetDialect() == Dialect_SQLite);
-
       LookupFormatter formatter(manager.GetDialect());
 
       std::unique_ptr<DatabaseManager::CachedStatement> statement;
 
       std::string queueIdParameter = formatter.GenerateParameter(queueId);
 
-      if (fromFront)
+      switch (manager.GetDialect())
       {
-        statement.reset(new DatabaseManager::CachedStatement(
-                          STATEMENT_FROM_HERE, manager,
-                          "SELECT id, value FROM Queues WHERE queueId=" + queueIdParameter + " ORDER BY id ASC LIMIT 1"));
-      }
-      else
-      {
-        statement.reset(new DatabaseManager::CachedStatement(
-                          STATEMENT_FROM_HERE, manager,
-                          "SELECT id, value FROM Queues WHERE queueId=" + queueIdParameter + " ORDER BY id DESC LIMIT 1"));
+        case Dialect_PostgreSQL:
+          if (fromFront)
+          {
+            statement.reset(new DatabaseManager::CachedStatement(
+                              STATEMENT_FROM_HERE, manager,
+                              "WITH poppedRows AS (DELETE FROM Queues WHERE id = (SELECT MIN(id) FROM Queues WHERE queueId=" + queueIdParameter + " AND (reservedUntil IS NULL OR reservedUntil < now())) RETURNING value) "
+                              "SELECT value FROM poppedRows"));
+          }
+          else
+          {
+            statement.reset(new DatabaseManager::CachedStatement(
+                              STATEMENT_FROM_HERE, manager,
+                              "WITH poppedRows AS (DELETE FROM Queues WHERE id = (SELECT MAX(id) FROM Queues WHERE queueId=" + queueIdParameter + " AND (reservedUntil IS NULL OR reservedUntil < now())) RETURNING value) "
+                              "SELECT value FROM poppedRows"));
+          }
+          break;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
       }
 
       statement->Execute(formatter.GetDictionary());
@@ -4539,83 +4547,12 @@
       }
       else
       {
-        statement->SetResultFieldType(0, ValueType_Integer64);
-        statement->SetResultFieldType(1, ValueType_BinaryString);
-
-        value = statement->ReadString(1);
-
-        {
-          DatabaseManager::CachedStatement s2(STATEMENT_FROM_HERE, manager,
-                                              "DELETE FROM Queues WHERE id=${id}");
-
-          s2.SetParameterType("id", ValueType_Integer64);
-
-          Dictionary args;
-          args.SetIntegerValue("id", statement->ReadInteger64(0));
-
-          s2.Execute(args);
-        }
-
+        statement->SetResultFieldType(0, ValueType_BinaryString);
+        value = statement->ReadString(0);
         return true;
       }
     }
 
-
-    bool IndexBackend::DequeueValue(std::string& value,
-                                    DatabaseManager& manager,
-                                    const std::string& queueId,
-                                    bool fromFront)
-    {
-      if (manager.GetDialect() == Dialect_SQLite)
-      {
-        return DequeueValueSQLite(value, manager, queueId, fromFront);
-      }
-      else
-      {
-        LookupFormatter formatter(manager.GetDialect());
-
-        std::unique_ptr<DatabaseManager::CachedStatement> statement;
-
-        std::string queueIdParameter = formatter.GenerateParameter(queueId);
-
-        switch (manager.GetDialect())
-        {
-          case Dialect_PostgreSQL:
-            if (fromFront)
-            {
-              statement.reset(new DatabaseManager::CachedStatement(
-                                STATEMENT_FROM_HERE, manager,
-                                "WITH poppedRows AS (DELETE FROM Queues WHERE id = (SELECT MIN(id) FROM Queues WHERE queueId=" + queueIdParameter + ") RETURNING value) "
-                                "SELECT value FROM poppedRows"));
-            }
-            else
-            {
-              statement.reset(new DatabaseManager::CachedStatement(
-                                STATEMENT_FROM_HERE, manager,
-                                "WITH poppedRows AS (DELETE FROM Queues WHERE id = (SELECT MAX(id) FROM Queues WHERE queueId=" + queueIdParameter + ") RETURNING value) "
-                                "SELECT value FROM poppedRows"));
-            }
-            break;
-
-          default:
-            throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
-        }
-
-        statement->Execute(formatter.GetDictionary());
-
-        if (statement->IsDone())
-        {
-          return false;
-        }
-        else
-        {
-          statement->SetResultFieldType(0, ValueType_BinaryString);
-          value = statement->ReadString(0);
-          return true;
-        }
-      }
-    }
-
     uint64_t IndexBackend::GetQueueSize(DatabaseManager& manager,
                                         const std::string& queueId)
     {
@@ -4636,6 +4573,79 @@
     }
 #endif
 
+#if ORTHANC_PLUGINS_HAS_RESERVE_QUEUE_VALUE == 1
+    bool IndexBackend::ReserveQueueValue(std::string& value,
+                                         uint64_t& valueId,
+                                         DatabaseManager& manager,
+                                         const std::string& queueId,
+                                         bool fromFront,
+                                         uint32_t reserveTimeout)
+    {
+      LookupFormatter formatter(manager.GetDialect());
+
+      std::string queueIdParameter = formatter.GenerateParameter(queueId);
+      std::string reserveTimeoutParameter = formatter.GenerateParameter(reserveTimeout);
+
+      std::string minMax = (fromFront ? "MIN" : "MAX");
+      std::string sql;
+
+      switch (manager.GetDialect())
+      {
+        case Dialect_PostgreSQL:
+          sql = "WITH RowToUpdate AS (SELECT " + minMax + "(id) FROM Queues WHERE queueId=" + queueIdParameter + " AND (reservedUntil IS NULL OR reservedUntil < now())) "
+                "  UPDATE Queues SET reservedUntil = now() + (" + reserveTimeoutParameter + "::text || ' seconds')::interval WHERE id IN (SELECT * FROM RowToUpdate) "
+                "  RETURNING id, value;";
+          break;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+      }
+
+      DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql);
+      statement.Execute(formatter.GetDictionary());
+
+      if (statement.IsDone())
+      {
+        return false;
+      }
+      else
+      {
+        statement.SetResultFieldType(0, ValueType_Integer64);
+        valueId = statement.ReadInteger64(0);
+
+        statement.SetResultFieldType(1, ValueType_BinaryString);
+        value = statement.ReadString(1);
+        return true;
+      }
+    }
+
+    void IndexBackend::AcknowledgeQueueValue(DatabaseManager& manager,
+                                             const std::string& queueId,
+                                             uint64_t valueId)
+    {
+      LookupFormatter formatter(manager.GetDialect());
+
+      std::unique_ptr<DatabaseManager::CachedStatement> statement;
+
+      std::string queueIdParameter = formatter.GenerateParameter(queueId);
+      std::string valueIdParameter = formatter.GenerateParameter(valueId);
+
+      switch (manager.GetDialect())
+      {
+        case Dialect_PostgreSQL:
+          statement.reset(new DatabaseManager::CachedStatement(
+                            STATEMENT_FROM_HERE, manager,
+                            "DELETE FROM Queues WHERE queueId=" + queueIdParameter + " AND id=" + valueIdParameter));
+          break;
+
+        default:
+          throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+      }
+
+      statement->Execute(formatter.GetDictionary());
+    }
+#endif
+
 #if ORTHANC_PLUGINS_HAS_ATTACHMENTS_CUSTOM_DATA == 1
     void IndexBackend::GetAttachmentCustomData(std::string& customData,
                                                DatabaseManager& manager,
@@ -4815,4 +4825,8 @@
     }
 #endif
 
+  ISqlLookupFormatter* IndexBackend::CreateLookupFormatter(Dialect dialect)
+  {
+    return new LookupFormatter(dialect);
+  }
 }
--- a/Framework/Plugins/IndexBackend.h	Wed Nov 26 14:20:16 2025 +0100
+++ b/Framework/Plugins/IndexBackend.h	Wed Nov 26 14:23:53 2025 +0100
@@ -39,7 +39,9 @@
   class IndexBackend : public IDatabaseBackend
   {
   private:
+#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
     class LookupFormatter;
+#endif
 
     OrthancPluginContext*  context_;
     bool                   readOnly_;
@@ -49,7 +51,6 @@
     std::unique_ptr<IDatabaseBackendOutput::IFactory>  outputFactory_;
     
   protected:
-
     virtual void ClearDeletedFiles(DatabaseManager& manager);
 
     virtual void ClearDeletedResources(DatabaseManager& manager);
@@ -84,13 +85,6 @@
                                        const Dictionary& args,
                                        uint32_t limit);
 
-#if ORTHANC_PLUGINS_HAS_QUEUES == 1
-    bool DequeueValueSQLite(std::string& value,
-                            DatabaseManager& manager,
-                            const std::string& queueId,
-                            bool fromFront);
-#endif
-
   public:
     explicit IndexBackend(OrthancPluginContext* context,
                           bool readOnly,
@@ -510,6 +504,19 @@
 
 #endif
 
+#if ORTHANC_PLUGINS_HAS_RESERVE_QUEUE_VALUE == 1
+    virtual bool ReserveQueueValue(std::string& value,
+                                   uint64_t& valueId,
+                                   DatabaseManager& manager,
+                                   const std::string& queueId,
+                                   bool fromFront,
+                                   uint32_t reserveTimeout) ORTHANC_OVERRIDE;
+
+    virtual void AcknowledgeQueueValue(DatabaseManager& manager,
+                                       const std::string& queueId,
+                                       uint64_t valueId) ORTHANC_OVERRIDE;
+#endif
+
 #if ORTHANC_PLUGINS_HAS_ATTACHMENTS_CUSTOM_DATA == 1
     virtual void GetAttachmentCustomData(std::string& customData,
                                          DatabaseManager& manager,
@@ -518,7 +525,6 @@
     virtual void SetAttachmentCustomData(DatabaseManager& manager,
                                          const std::string& attachmentUuid,
                                          const std::string& customData) ORTHANC_OVERRIDE;
-
 #endif
 
 #if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1
@@ -569,5 +575,9 @@
     static DatabaseManager* CreateSingleDatabaseManager(IDatabaseBackend& backend,
                                                         bool hasIdentifierTags,
                                                         const std::list<IdentifierTag>& identifierTags);
+
+#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1
+    ISqlLookupFormatter* CreateLookupFormatter(Dialect dialect);
+#endif
   };
 }
--- a/Framework/Plugins/IndexUnitTests.h	Wed Nov 26 14:20:16 2025 +0100
+++ b/Framework/Plugins/IndexUnitTests.h	Wed Nov 26 14:23:53 2025 +0100
@@ -28,6 +28,7 @@
 #include "GlobalProperties.h"
 
 #include <Compatibility.h>  // For std::unique_ptr<>
+#include <SystemToolbox.h>
 
 #include <gtest/gtest.h>
 #include <list>
@@ -1065,5 +1066,49 @@
   }
 #endif
 
+#if ORTHANC_PLUGINS_HAS_RESERVE_QUEUE_VALUE == 1
+  {
+    manager->StartTransaction(TransactionType_ReadWrite);
+
+    db.EnqueueValue(*manager, "test", "a");
+    db.EnqueueValue(*manager, "test", "b");
+    db.EnqueueValue(*manager, "test", "c");
+    db.EnqueueValue(*manager, "test", "d");
+    db.EnqueueValue(*manager, "test", "e");
+
+
+    ASSERT_EQ(5u, db.GetQueueSize(*manager, "test"));
+
+    std::string value;
+    uint64_t valueIdA, valueIdB, valueIdC, valueIdD, valueIdE, valueIdFail;
+
+    ASSERT_TRUE(db.ReserveQueueValue(value, valueIdA, *manager, "test", true, 1));
+    ASSERT_EQ("a", value);
+    ASSERT_TRUE(db.ReserveQueueValue(value, valueIdB, *manager, "test", true, 1));
+    ASSERT_EQ("b", value);
+    ASSERT_TRUE(db.ReserveQueueValue(value, valueIdE, *manager, "test", false, 1));
+    ASSERT_EQ("e", value);
+    ASSERT_TRUE(db.ReserveQueueValue(value, valueIdD, *manager, "test", false, 1));
+    ASSERT_EQ("d", value);
+
+    db.AcknowledgeQueueValue(*manager, "test", valueIdA);
+    db.AcknowledgeQueueValue(*manager, "test", valueIdE);
+    manager->CommitTransaction();  // NOW() is constant during a transaction -> we need to commit it to get a new NOW() value for the second part of the test
+
+    Orthanc::SystemToolbox::USleep(2000000);  // Wait 2 seconds -> b and d should be released
+
+    manager->StartTransaction(TransactionType_ReadWrite);
+    ASSERT_TRUE(db.ReserveQueueValue(value, valueIdB, *manager, "test", true, 1));
+    ASSERT_EQ("b", value);
+    ASSERT_TRUE(db.ReserveQueueValue(value, valueIdD, *manager, "test", false, 1));
+    ASSERT_EQ("d", value);
+    ASSERT_TRUE(db.ReserveQueueValue(value, valueIdC, *manager, "test", false, 1));
+    ASSERT_EQ("c", value);
+    ASSERT_FALSE(db.ReserveQueueValue(value, valueIdFail, *manager, "test", false, 1));
+
+    manager->CommitTransaction();
+  }
+#endif
+
   manager->Close();
 }
--- a/Framework/Plugins/MessagesToolbox.h	Wed Nov 26 14:20:16 2025 +0100
+++ b/Framework/Plugins/MessagesToolbox.h	Wed Nov 26 14:23:53 2025 +0100
@@ -54,6 +54,7 @@
 #define ORTHANC_PLUGINS_HAS_ATTACHMENTS_CUSTOM_DATA 0
 #define ORTHANC_PLUGINS_HAS_KEY_VALUE_STORES 0
 #define ORTHANC_PLUGINS_HAS_QUEUES 0
+#define ORTHANC_PLUGINS_HAS_RESERVE_QUEUE_VALUE 0
 #define ORTHANC_PLUGINS_HAS_AUDIT_LOGS 0
 
 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE)
@@ -75,6 +76,13 @@
 #  endif
 #endif
 
+#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE)
+#  if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 10)
+#    undef  ORTHANC_PLUGINS_HAS_RESERVE_QUEUE_VALUE
+#    define ORTHANC_PLUGINS_HAS_RESERVE_QUEUE_VALUE 1
+#  endif
+#endif
+
 
 #include <Enumerations.h>
 
--- a/MySQL/Plugins/MySQLIndex.h	Wed Nov 26 14:20:16 2025 +0100
+++ b/MySQL/Plugins/MySQLIndex.h	Wed Nov 26 14:23:53 2025 +0100
@@ -76,6 +76,11 @@
       return false;
     }
 
+    virtual bool HasReserveQueueValue() const ORTHANC_OVERRIDE
+    {
+      return false;
+    }
+
     virtual bool HasAuditLogs() const ORTHANC_OVERRIDE
     {
       return false;
--- a/Odbc/Plugins/OdbcIndex.h	Wed Nov 26 14:20:16 2025 +0100
+++ b/Odbc/Plugins/OdbcIndex.h	Wed Nov 26 14:23:53 2025 +0100
@@ -88,6 +88,11 @@
       return false;
     }
 
+    virtual bool HasReserveQueueValue() const ORTHANC_OVERRIDE
+    {
+      return false;
+    }
+
     virtual bool HasAuditLogs() const ORTHANC_OVERRIDE
     {
       return false;
--- a/PostgreSQL/CMakeLists.txt	Wed Nov 26 14:20:16 2025 +0100
+++ b/PostgreSQL/CMakeLists.txt	Wed Nov 26 14:23:53 2025 +0100
@@ -95,7 +95,8 @@
   POSTGRESQL_UPGRADE_REV2_TO_REV3    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3.sql
   POSTGRESQL_UPGRADE_REV3_TO_REV4    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev3ToRev4.sql
   POSTGRESQL_UPGRADE_REV4_TO_REV5    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev4ToRev5.sql
-  POSTGRESQL_UPGRADE_REV5_TO_REV6  ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev5ToRev6.sql
+  POSTGRESQL_UPGRADE_REV5_TO_REV6    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev5ToRev6.sql
+  POSTGRESQL_UPGRADE_REV6_TO_REV699  ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev6ToRev699.sql
   )
 
 
--- a/PostgreSQL/NEWS	Wed Nov 26 14:20:16 2025 +0100
+++ b/PostgreSQL/NEWS	Wed Nov 26 14:23:53 2025 +0100
@@ -3,13 +3,15 @@
 
 DB schema revision: 6
 Minimum plugin SDK (for build): 1.12.5
-Optimal plugin SDK (for build): 1.12.9
+Optimal plugin SDK (for build): 1.12.10   (TODO: update once released !)
 Minimum Orthanc runtime: 1.12.5
-Optimal Orthanc runtime: 1.12.9
+Optimal Orthanc runtime: 1.12.10
 
 Minimal Postgresql Server version: 9
 Optimal Postgresql Server version: 11+
 
+TODO before release: update SDK to 1.12.10
+
 Changes:
 * New configuration "Schema" (default value: 'public') to allow Orthanc
   to use another schema.  Note that, if you are not using the default 'public'
@@ -23,11 +25,12 @@
 * New configuration "ApplicationName" (default value is empty) that is copied in 
   the application_name argument in the connection string.  This name is used to
   identify the origin of queries in statistics and logs in the PostgreSQL server.
-
+* SDK: Added support for ReserveQueueValue and AcknowledgeQueueValue (new in SDK 1.12.10)
 
 Maintenance:
-* Now verifying the DatabasePatchLevel (revision) in another transaction than
-  the one that upgrades the schema.
+* Added a new primary key column in the InvalidChildCounts and GlobalIntegersChanges
+  tables.  This new column is required for pg_repack to be able to reclaim space on
+  these tables.
 
 
 Release 9.0 (2025-08-13)
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Nov 26 14:20:16 2025 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Nov 26 14:23:53 2025 +0100
@@ -49,7 +49,7 @@
   static const GlobalProperty GlobalProperty_HasComputeStatisticsReadOnly = GlobalProperty_DatabaseInternal4;
 }
 
-#define CURRENT_DB_REVISION 6
+#define CURRENT_DB_REVISION 699
 
 namespace OrthancDatabases
 {
@@ -268,6 +268,19 @@
             currentRevision = 6;
           }
 
+          if (currentRevision == 6)
+          {
+            LOG(WARNING) << "Upgrading DB schema from revision 6 to revision 699";
+
+            std::string query;
+
+            Orthanc::EmbeddedResources::GetFileResource
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV6_TO_REV699);
+            t.GetDatabaseTransaction().ExecuteMultiLines(query);
+            hasAppliedAnUpgrade = true;
+            currentRevision = 699;
+          }
+
           if (hasAppliedAnUpgrade)
           {
             LOG(WARNING) << "Upgrading DB schema by applying PrepareIndex.sql";
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h	Wed Nov 26 14:20:16 2025 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.h	Wed Nov 26 14:23:53 2025 +0100
@@ -87,6 +87,11 @@
       return true;
     }
 
+    virtual bool HasReserveQueueValue() const ORTHANC_OVERRIDE
+    {
+      return true;
+    }
+
     virtual bool HasAuditLogs() const ORTHANC_OVERRIDE
     {
       return true;
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev699ToRev6.sql	Wed Nov 26 14:23:53 2025 +0100
@@ -0,0 +1,8 @@
+ALTER TABLE InvalidChildCounts DROP COLUMN pk;
+ALTER TABLE GlobalIntegersChanges DROP COLUMN pk;
+----------
+
+-- 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);
+INSERT INTO GlobalProperties VALUES (4, 6); -- GlobalProperty_DatabasePatchLevel
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Wed Nov 26 14:20:16 2025 +0100
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Wed Nov 26 14:23:53 2025 +0100
@@ -400,6 +400,7 @@
 -- These changes will be applied at regular interval by an external thread or when someone
 -- requests the statistics
 CREATE TABLE IF NOT EXISTS GlobalIntegersChanges(
+    pk BIGSERIAL PRIMARY KEY,   -- new in rev699 required for pg_repack to be able to reclaim space
     key INTEGER,
     value BIGINT);
 
@@ -458,7 +459,7 @@
 CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc()
 RETURNS TRIGGER AS $$
 BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(new.resourceType + 2, 1);
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
@@ -466,7 +467,7 @@
 CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc()
 RETURNS TRIGGER AS $$
 BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(old.resourceType + 2, -1);
   RETURN NULL;
 END;
 $$ LANGUAGE plpgsql;
@@ -475,8 +476,8 @@
 CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
 RETURNS TRIGGER AS $body$
 BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize);
-  INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(0, new.compressedSize);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(1, new.uncompressedSize);
   RETURN NULL;
 END;
 $body$ LANGUAGE plpgsql;
@@ -484,8 +485,8 @@
 CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() 
 RETURNS TRIGGER AS $body$
 BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize);
-  INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(0, -old.compressedSize);
+  INSERT INTO GlobalIntegersChanges (key, value) VALUES(1, -old.uncompressedSize);
   RETURN NULL;
 END;
 $body$ LANGUAGE plpgsql;
@@ -704,6 +705,7 @@
 -- At regular interval, the DB housekeeping thread updates the childCount column of
 -- resources with an entry in this table.
 CREATE TABLE IF NOT EXISTS InvalidChildCounts(
+    pk BIGSERIAL PRIMARY KEY,   -- new in rev699 required for pg_repack to be able to reclaim space
     id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
     updatedAt TIMESTAMP DEFAULT NOW());
 
@@ -756,7 +758,7 @@
     IF TG_OP = 'INSERT' THEN
 		IF new.parentId IS NOT NULL THEN
             -- mark the parent's childCount as invalid
-			INSERT INTO InvalidChildCounts VALUES(new.parentId);
+			INSERT INTO InvalidChildCounts (id) VALUES(new.parentId);
         END IF;
 	
     ELSIF TG_OP = 'DELETE' THEN
@@ -764,7 +766,7 @@
 		IF old.parentId IS NOT NULL THEN
             BEGIN
                 -- mark the parent's childCount as invalid
-                INSERT INTO InvalidChildCounts VALUES(old.parentId);
+                INSERT INTO InvalidChildCounts (id) VALUES(old.parentId);
             EXCEPTION
                 -- when deleting the last child of a parent, the insert will fail (this is expected)
                 WHEN foreign_key_violation THEN NULL;
@@ -800,7 +802,8 @@
 CREATE TABLE IF NOT EXISTS Queues (
        id BIGSERIAL NOT NULL PRIMARY KEY,
        queueId TEXT NOT NULL,
-       value BYTEA NOT NULL
+       value BYTEA NOT NULL,
+       reservedUntil TIMESTAMP DEFAULT NULL
 );
 
 CREATE INDEX IF NOT EXISTS QueuesIndex ON Queues (queueId, id);
@@ -854,7 +857,7 @@
 -- 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, 14);
 INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion
-INSERT INTO GlobalProperties VALUES (4, 6); -- GlobalProperty_DatabasePatchLevel
+INSERT INTO GlobalProperties VALUES (4, 699); -- 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
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev6ToRev699.sql	Wed Nov 26 14:23:53 2025 +0100
@@ -0,0 +1,7 @@
+-- Adding a PK to these 2 table to allow pg_repack to process these tables, enabling reclaiming disk space and defragmenting the tables.
+
+ALTER TABLE InvalidChildCounts ADD COLUMN pk BIGSERIAL PRIMARY KEY;
+ALTER TABLE GlobalIntegersChanges ADD COLUMN pk BIGSERIAL PRIMARY KEY;
+
+-- Adding the queues timeout
+ALTER TABLE Queues ADD COLUMN reservedUntil TIMESTAMP DEFAULT NULL;
\ No newline at end of file
--- a/SQLite/Plugins/SQLiteIndex.cpp	Wed Nov 26 14:20:16 2025 +0100
+++ b/SQLite/Plugins/SQLiteIndex.cpp	Wed Nov 26 14:23:53 2025 +0100
@@ -299,4 +299,61 @@
     throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
   }
 #endif
+
+#if ORTHANC_PLUGINS_HAS_QUEUES == 1
+  bool SQLiteIndex::DequeueValue(std::string& value,
+                                 DatabaseManager& manager,
+                                 const std::string& queueId,
+                                 bool fromFront)
+  {
+    assert(manager.GetDialect() == Dialect_SQLite);
+
+    std::unique_ptr<ISqlLookupFormatter> formatter(CreateLookupFormatter(manager.GetDialect()));
+
+    std::unique_ptr<DatabaseManager::CachedStatement> statement;
+
+    std::string queueIdParameter = formatter->GenerateParameter(queueId);
+
+    if (fromFront)
+    {
+      statement.reset(new DatabaseManager::CachedStatement(
+                        STATEMENT_FROM_HERE, manager,
+                        "SELECT id, value FROM Queues WHERE queueId=" + queueIdParameter + " ORDER BY id ASC LIMIT 1"));
+    }
+    else
+    {
+      statement.reset(new DatabaseManager::CachedStatement(
+                        STATEMENT_FROM_HERE, manager,
+                        "SELECT id, value FROM Queues WHERE queueId=" + queueIdParameter + " ORDER BY id DESC LIMIT 1"));
+    }
+
+    statement->Execute(formatter->GetDictionary());
+
+    if (statement->IsDone())
+    {
+      return false;
+    }
+    else
+    {
+      statement->SetResultFieldType(0, ValueType_Integer64);
+      statement->SetResultFieldType(1, ValueType_BinaryString);
+
+      value = statement->ReadString(1);
+
+      {
+        DatabaseManager::CachedStatement s2(STATEMENT_FROM_HERE, manager,
+                                            "DELETE FROM Queues WHERE id=${id}");
+
+        s2.SetParameterType("id", ValueType_Integer64);
+
+        Dictionary args;
+        args.SetIntegerValue("id", statement->ReadInteger64(0));
+
+        s2.Execute(args);
+      }
+
+      return true;
+    }
+  }
+#endif
 }
--- a/SQLite/Plugins/SQLiteIndex.h	Wed Nov 26 14:20:16 2025 +0100
+++ b/SQLite/Plugins/SQLiteIndex.h	Wed Nov 26 14:23:53 2025 +0100
@@ -70,6 +70,11 @@
       return true;
     }
 
+    virtual bool HasReserveQueueValue() const ORTHANC_OVERRIDE
+    {
+      return false;
+    }
+
     virtual bool HasAuditLogs() const ORTHANC_OVERRIDE
     {
       return false;
@@ -108,5 +113,12 @@
     {
       return false;
     }
+
+#if ORTHANC_PLUGINS_HAS_QUEUES == 1
+    virtual bool DequeueValue(std::string& value,
+                              DatabaseManager& manager,
+                              const std::string& queueId,
+                              bool fromFront) ORTHANC_OVERRIDE;
+#endif
   };
 }