# HG changeset patch # User Sebastien Jodogne # Date 1749667260 -7200 # Node ID e6f14f12117c95c622c2be7038d4df85e89f7db6 # Parent 4d46dc4fcc63bc894016397115a00cf424c82a13 enabling queues and key-value stores in SQLite diff -r 4d46dc4fcc63 -r e6f14f12117c Framework/Plugins/IndexBackend.cpp --- 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 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 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; + } } } diff -r 4d46dc4fcc63 -r e6f14f12117c Framework/Plugins/IndexBackend.h --- 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, diff -r 4d46dc4fcc63 -r e6f14f12117c SQLite/Plugins/PrepareIndex.sql --- 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); diff -r 4d46dc4fcc63 -r e6f14f12117c SQLite/Plugins/SQLiteIndex.h --- 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,