Mercurial > hg > orthanc-databases

changeset 684:e6f14f12117c attach-custom-data

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
enabling queues and key-value stores in SQLite
author Sebastien Jodogne <s.jodogne@gmail.com>
date Wed, 11 Jun 2025 20:41:00 +0200
parents 4d46dc4fcc63
children 08ebec631f74
files Framework/Plugins/IndexBackend.cpp Framework/Plugins/IndexBackend.h SQLite/Plugins/PrepareIndex.sql SQLite/Plugins/SQLiteIndex.h
diffstat 4 files changed, 103 insertions(+), 47 deletions(-) [+]
line wrap: on
line diff
--- a/Framework/Plugins/IndexBackend.cpp	Wed Jun 11 19:55:25 2025 +0200
+++ b/Framework/Plugins/IndexBackend.cpp	Wed Jun 11 20:41:00 2025 +0200
@@ -4511,53 +4511,31 @@
       statement.Execute(args);
     }
 
-    bool IndexBackend::DequeueValue(std::string& value,
-                                    DatabaseManager& manager,
-                                    const std::string& queueId,
-                                    bool fromFront)
+
+    bool IndexBackend::DequeueValueSQLite(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);
 
-      switch (manager.GetDialect())
+      if (fromFront)
       {
-        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;
-
-        case Dialect_SQLite:
-          if (fromFront)
-          {
-            statement.reset(new DatabaseManager::CachedStatement(
-                              STATEMENT_FROM_HERE, manager,
-                              "DELETE FROM Queues WHERE id = (SELECT id FROM Queues WHERE queueId=" + queueIdParameter + " ORDER BY id ASC LIMIT 1) RETURNING value"));
-          }
-          else
-          {
-            statement.reset(new DatabaseManager::CachedStatement(
-                              STATEMENT_FROM_HERE, manager,
-                              "DELETE FROM Queues WHERE id = (SELECT id FROM Queues WHERE queueId=" + queueIdParameter + " ORDER BY id DESC LIMIT 1) RETURNING value"));
-          }
-          break;
-
-        default:
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented);
+        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());
@@ -4565,12 +4543,83 @@
       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;
+      }
+    }
+
+
+    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
       {
-        statement->SetResultFieldType(0, ValueType_BinaryString);
-        value = statement->ReadString(0);
-        return true;
+        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;
+        }
       }
     }
 
--- a/Framework/Plugins/IndexBackend.h	Wed Jun 11 19:55:25 2025 +0200
+++ b/Framework/Plugins/IndexBackend.h	Wed Jun 11 20:41:00 2025 +0200
@@ -84,6 +84,13 @@
                                        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,
--- a/SQLite/Plugins/PrepareIndex.sql	Wed Jun 11 19:55:25 2025 +0200
+++ b/SQLite/Plugins/PrepareIndex.sql	Wed Jun 11 20:41:00 2025 +0200
@@ -169,7 +169,7 @@
 CREATE TABLE Queues (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        queueId TEXT NOT NULL,
-       value BLOB
+       value BLOB NOT NULL
 );
 
 CREATE INDEX QueuesIndex ON Queues (queueId, id);
--- a/SQLite/Plugins/SQLiteIndex.h	Wed Jun 11 19:55:25 2025 +0200
+++ b/SQLite/Plugins/SQLiteIndex.h	Wed Jun 11 20:41:00 2025 +0200
@@ -62,12 +62,12 @@
 
     virtual bool HasKeyValueStores() const ORTHANC_OVERRIDE
     {
-      return false;
+      return true;
     }
 
     virtual bool HasQueues() const ORTHANC_OVERRIDE
     {
-      return false;
+      return true;
     }
 
     virtual int64_t CreateResource(DatabaseManager& manager,