Mercurial > hg > orthanc-databases
changeset 727:5d6ce8e26dec
merged sql-opti -> default
| author | Alain Mazy <am@orthanc.team> |
|---|---|
| date | Tue, 12 Aug 2025 15:27:09 +0200 |
| parents | a01deb8481f6 (current diff) 2811c0d5d0b1 (diff) |
| children | 9f41a8c94f9c |
| files | |
| diffstat | 37 files changed, 2003 insertions(+), 390 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Common/BinaryStringValue.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Common/BinaryStringValue.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -32,7 +32,8 @@ namespace OrthancDatabases { BinaryStringValue::BinaryStringValue(const void* data, - size_t size) + size_t size) : + isNull_(false) { if (data == NULL && size > 0) {
--- a/Framework/Common/BinaryStringValue.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Common/BinaryStringValue.h Tue Aug 12 15:27:09 2025 +0200 @@ -33,14 +33,17 @@ { private: std::string content_; + bool isNull_; public: - BinaryStringValue() + BinaryStringValue() : + isNull_(true) { } explicit BinaryStringValue(const std::string& content) : - content_(content) + content_(content), + isNull_(false) { } @@ -73,5 +76,10 @@ } virtual IValue* Convert(ValueType target) const ORTHANC_OVERRIDE; + + virtual bool IsNull() const ORTHANC_OVERRIDE + { + return isNull_; + } }; }
--- a/Framework/Common/DatabaseManager.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Common/DatabaseManager.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -160,7 +160,9 @@ DatabaseManager::DatabaseManager(IDatabaseFactory* factory) : factory_(factory), - dialect_(Dialect_Unknown) + dialect_(Dialect_Unknown), + creationTime_(boost::posix_time::second_clock::universal_time()), + lastUseTime_(boost::posix_time::second_clock::universal_time()) { if (factory == NULL) { @@ -168,6 +170,20 @@ } } + uint64_t DatabaseManager::GetElapsedSecondsSinceCreation() const + { + boost::posix_time::ptime now = boost::posix_time::second_clock::universal_time(); + boost::posix_time::time_duration diff = now - creationTime_; + return static_cast<uint64_t>(diff.total_seconds()); + } + + uint64_t DatabaseManager::GetElapsedSecondsSinceLastUse() const + { + boost::posix_time::ptime now = boost::posix_time::second_clock::universal_time(); + boost::posix_time::time_duration diff = now - lastUseTime_; + return static_cast<uint64_t>(diff.total_seconds()); + } + IDatabase& DatabaseManager::GetDatabase() { @@ -218,6 +234,8 @@ } transaction_.reset(GetDatabase().CreateTransaction(type)); + + lastUseTime_= boost::posix_time::second_clock::universal_time(); } catch (Orthanc::OrthancException& e) {
--- a/Framework/Common/DatabaseManager.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Common/DatabaseManager.h Tue Aug 12 15:27:09 2025 +0200 @@ -30,6 +30,7 @@ #include <Enumerations.h> #include <memory> +#include <boost/date_time/posix_time/posix_time.hpp> namespace OrthancDatabases @@ -56,6 +57,8 @@ std::unique_ptr<ITransaction> transaction_; CachedStatements cachedStatements_; Dialect dialect_; + boost::posix_time::ptime creationTime_; + boost::posix_time::ptime lastUseTime_; void CloseIfUnavailable(Orthanc::ErrorCode e); @@ -88,6 +91,9 @@ void RollbackTransaction(); + uint64_t GetElapsedSecondsSinceCreation() const; + + uint64_t GetElapsedSecondsSinceLastUse() const; // This class is only used in the "StorageBackend" and in // "IDatabaseBackend::ConfigureDatabase()"
--- a/Framework/Common/Dictionary.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Common/Dictionary.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -147,6 +147,10 @@ SetValue(key, new Utf8StringValue()); } + void Dictionary::SetBinaryNullValue(const std::string& key) + { + SetValue(key, new BinaryStringValue()); + } const IValue& Dictionary::GetValue(const std::string& key) const {
--- a/Framework/Common/Dictionary.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Common/Dictionary.h Tue Aug 12 15:27:09 2025 +0200 @@ -67,6 +67,8 @@ const void* data, size_t size); + void SetBinaryNullValue(const std::string& key); + void SetFileValue(const std::string& key, const std::string& file);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Plugins/BaseIndexConnectionsPool.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -0,0 +1,152 @@ +/** + * 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-2025 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2025 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 "BaseIndexConnectionsPool.h" + +#include <Logging.h> + + +namespace OrthancDatabases +{ + void BaseIndexConnectionsPool::HousekeepingThread(BaseIndexConnectionsPool* that) + { +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 2) + OrthancPluginSetCurrentThreadName(OrthancPlugins::GetGlobalContext(), "DB HOUSEKEEPING"); +#endif + + boost::posix_time::ptime lastInvocation = boost::posix_time::second_clock::local_time(); + + while (that->housekeepingContinue_) + { + if (boost::posix_time::second_clock::local_time() - lastInvocation >= that->housekeepingDelay_) + { + try + { + { + Accessor accessor(*that); + accessor.GetBackend().PerformDbHousekeeping(accessor.GetManager()); + } + + that->PerformPoolHousekeeping(); + } + catch (Orthanc::OrthancException& e) + { + LOG(ERROR) << "Exception during the database housekeeping: " << e.What(); + } + catch (...) + { + LOG(ERROR) << "Native exception during the database houskeeping"; + } + + lastInvocation = boost::posix_time::second_clock::local_time(); + } + + boost::this_thread::sleep(boost::posix_time::milliseconds(1000)); + } + } + + + BaseIndexConnectionsPool::BaseIndexConnectionsPool(IndexBackend* backend, + unsigned int houseKeepingDelaySeconds) : + backend_(backend), + housekeepingContinue_(true), + housekeepingDelay_(boost::posix_time::seconds(houseKeepingDelaySeconds)) + { + if (backend == NULL) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NullPointer); + } + else if (backend->HasPerformDbHousekeeping() && + houseKeepingDelaySeconds == 0) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange, + "The delay between two executions of housekeeping cannot be zero second"); + } + else + { + context_ = backend_->GetContext(); + } + } + + + BaseIndexConnectionsPool::~BaseIndexConnectionsPool() + { + } + + + void BaseIndexConnectionsPool::StartHousekeepingThread() + { + housekeepingContinue_ = true; + + if (backend_->HasPerformDbHousekeeping()) + { + housekeepingThread_ = boost::thread(HousekeepingThread, this); + } + } + + void BaseIndexConnectionsPool::StopHousekeepingThread() + { + housekeepingContinue_ = false; + if (housekeepingThread_.joinable()) + { + housekeepingThread_.join(); + } + } + + + BaseIndexConnectionsPool::Accessor::Accessor(BaseIndexConnectionsPool& pool) : + pool_(pool), + manager_(NULL) + { + for (;;) + { + std::unique_ptr<DatabaseManager> manager(pool.GetConnection()); + if (manager.get() != NULL) + { + manager_ = manager.release(); + return; + } + boost::this_thread::sleep(boost::posix_time::millisec(100)); + } + } + + + BaseIndexConnectionsPool::Accessor::~Accessor() + { + assert(manager_ != NULL); + pool_.ReleaseConnection(manager_); + } + + + IndexBackend& BaseIndexConnectionsPool::Accessor::GetBackend() const + { + return *pool_.backend_; + } + + + DatabaseManager& BaseIndexConnectionsPool::Accessor::GetManager() const + { + assert(manager_ != NULL); + return *manager_; + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Plugins/BaseIndexConnectionsPool.h Tue Aug 12 15:27:09 2025 +0200 @@ -0,0 +1,91 @@ +/** + * 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-2025 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2025 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 "IdentifierTag.h" +#include "IndexBackend.h" + +#include <MultiThreading/SharedMessageQueue.h> +#include <MultiThreading/Semaphore.h> + +#include <list> +#include <boost/thread.hpp> + +namespace OrthancDatabases +{ + class BaseIndexConnectionsPool : public boost::noncopyable + { + protected: + std::unique_ptr<IndexBackend> backend_; + OrthancPluginContext* context_; + + bool housekeepingContinue_; + boost::thread housekeepingThread_; + boost::posix_time::time_duration housekeepingDelay_; + + static void HousekeepingThread(BaseIndexConnectionsPool* that); + + virtual void PerformPoolHousekeeping() = 0; + + void StartHousekeepingThread(); + + void StopHousekeepingThread(); + + virtual DatabaseManager* GetConnection() = 0; + + virtual void ReleaseConnection(DatabaseManager* manager) = 0; + + public: + BaseIndexConnectionsPool(IndexBackend* backend /* takes ownership */, + unsigned int houseKeepingDelaySeconds); + + virtual ~BaseIndexConnectionsPool(); + + OrthancPluginContext* GetContext() const + { + return context_; + } + + virtual void OpenConnections(bool hasIdentifierTags, + const std::list<IdentifierTag>& identifierTags) = 0; + + virtual void CloseConnections() = 0; + + class Accessor : public boost::noncopyable + { + private: + BaseIndexConnectionsPool& pool_; + DatabaseManager* manager_; + + public: + explicit Accessor(BaseIndexConnectionsPool& pool); + + ~Accessor(); + + IndexBackend& GetBackend() const; + + DatabaseManager& GetManager() const; + }; + }; +}
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -26,8 +26,10 @@ #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in Orthanc 1.3.1 # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) +#include "DynamicIndexConnectionsPool.h" #include "IndexConnectionsPool.h" #include "MessagesToolbox.h" +#include <Toolbox.h> #include <OrthancDatabasePlugin.pb.h> // Include protobuf messages @@ -46,7 +48,7 @@ namespace OrthancDatabases { static bool isBackendInUse_ = false; // Only for sanity checks - + static BaseIndexConnectionsPool* connectionPool_ = NULL; // Only for the AuditLogHandler static Orthanc::DatabasePluginMessages::ResourceType Convert(OrthancPluginResourceType resourceType) { @@ -431,13 +433,13 @@ static void ProcessDatabaseOperation(Orthanc::DatabasePluginMessages::DatabaseResponse& response, const Orthanc::DatabasePluginMessages::DatabaseRequest& request, - IndexConnectionsPool& pool) + BaseIndexConnectionsPool& pool) { switch (request.operation()) { case Orthanc::DatabasePluginMessages::OPERATION_GET_SYSTEM_INFORMATION: { - IndexConnectionsPool::Accessor accessor(pool); + BaseIndexConnectionsPool::Accessor accessor(pool); response.mutable_get_system_information()->set_database_version(accessor.GetBackend().GetDatabaseVersion(accessor.GetManager())); response.mutable_get_system_information()->set_supports_flush_to_disk(false); response.mutable_get_system_information()->set_supports_revisions(accessor.GetBackend().HasRevisionsSupport()); @@ -502,7 +504,7 @@ case Orthanc::DatabasePluginMessages::OPERATION_START_TRANSACTION: { - std::unique_ptr<IndexConnectionsPool::Accessor> transaction(new IndexConnectionsPool::Accessor(pool)); + std::unique_ptr<BaseIndexConnectionsPool::Accessor> transaction(new BaseIndexConnectionsPool::Accessor(pool)); switch (request.start_transaction().type()) { @@ -524,7 +526,7 @@ case Orthanc::DatabasePluginMessages::OPERATION_UPGRADE: { - IndexConnectionsPool::Accessor accessor(pool); + BaseIndexConnectionsPool::Accessor accessor(pool); OrthancPluginStorageArea* storageArea = reinterpret_cast<OrthancPluginStorageArea*>(request.upgrade().storage_area()); accessor.GetBackend().UpgradeDatabase(accessor.GetManager(), request.upgrade().target_version(), storageArea); break; @@ -532,7 +534,7 @@ case Orthanc::DatabasePluginMessages::OPERATION_FINALIZE_TRANSACTION: { - IndexConnectionsPool::Accessor* transaction = reinterpret_cast<IndexConnectionsPool::Accessor*>(request.finalize_transaction().transaction()); + BaseIndexConnectionsPool::Accessor* transaction = reinterpret_cast<BaseIndexConnectionsPool::Accessor*>(request.finalize_transaction().transaction()); if (transaction == NULL) { @@ -549,7 +551,7 @@ #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 3) case Orthanc::DatabasePluginMessages::OPERATION_MEASURE_LATENCY: { - IndexConnectionsPool::Accessor accessor(pool); + BaseIndexConnectionsPool::Accessor accessor(pool); response.mutable_measure_latency()->set_latency_us(accessor.GetBackend().MeasureLatency(accessor.GetManager())); break; } @@ -1430,7 +1432,7 @@ return OrthancPluginErrorCode_InternalError; } - IndexConnectionsPool& pool = *reinterpret_cast<IndexConnectionsPool*>(rawPool); + BaseIndexConnectionsPool& pool = *reinterpret_cast<BaseIndexConnectionsPool*>(rawPool); try { @@ -1444,7 +1446,7 @@ case Orthanc::DatabasePluginMessages::REQUEST_TRANSACTION: { - IndexConnectionsPool::Accessor& transaction = *reinterpret_cast<IndexConnectionsPool::Accessor*>(request.transaction_request().transaction()); + BaseIndexConnectionsPool::Accessor& transaction = *reinterpret_cast<BaseIndexConnectionsPool::Accessor*>(request.transaction_request().transaction()); ProcessTransactionOperation(*response.mutable_transaction_response(), request.transaction_request(), transaction.GetBackend(), transaction.GetManager()); break; @@ -1502,11 +1504,12 @@ { if (rawPool != NULL) { - IndexConnectionsPool* pool = reinterpret_cast<IndexConnectionsPool*>(rawPool); + BaseIndexConnectionsPool* pool = reinterpret_cast<BaseIndexConnectionsPool*>(rawPool); if (isBackendInUse_) { isBackendInUse_ = false; + connectionPool_ = NULL; } else { @@ -1521,13 +1524,241 @@ } } + + OrthancPluginErrorCode AuditLogHandler(const char* sourcePlugin, + const char* userId, + OrthancPluginResourceType resourceType, + const char* resourceId, + const char* action, + const void* logData, + uint32_t logDataSize) + { + if (!isBackendInUse_ || + connectionPool_ == NULL) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_BadSequenceOfCalls); + } + +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + { + BaseIndexConnectionsPool::Accessor accessor(*connectionPool_); + accessor.GetBackend().RecordAuditLog(accessor.GetManager(), + sourcePlugin, + userId, + resourceType, + resourceId, + action, + logData, + logDataSize); + } +#endif + + return OrthancPluginErrorCode_Success; + } + void GetAuditLogs(OrthancPluginRestOutput* output, + const char* /*url*/, + const OrthancPluginHttpRequest* request) + { + OrthancPluginContext* context = OrthancPlugins::GetGlobalContext(); + + if (request->method != OrthancPluginHttpMethod_Get) + { + OrthancPluginSendMethodNotAllowed(context, output, "GET"); + } + + OrthancPlugins::GetArguments getArguments; + OrthancPlugins::GetGetArguments(getArguments, request); + + std::string userIdFilter; + std::string resourceIdFilter; + std::string actionFilter; + + uint64_t since = 0; + uint64_t limit = 0; + uint64_t fromTs = 0; + uint64_t toTs = 0; + bool logDataInJson = false; + + if (getArguments.find("user-id") != getArguments.end()) + { + userIdFilter = getArguments["user-id"]; + } + + if (getArguments.find("resource-id") != getArguments.end()) + { + resourceIdFilter = getArguments["resource-id"]; + } + + if (getArguments.find("action") != getArguments.end()) + { + actionFilter = getArguments["action"]; + } + + if (getArguments.find("limit") != getArguments.end()) + { + limit = boost::lexical_cast<uint64_t>(getArguments["limit"]); + } + + if (getArguments.find("since") != getArguments.end()) + { + since = boost::lexical_cast<uint64_t>(getArguments["since"]); + } + + if (getArguments.find("from-timestamp") != getArguments.end()) + { + fromTs = boost::lexical_cast<uint64_t>(getArguments["from-timestamp"]); + } + + if (getArguments.find("to-timestamp") != getArguments.end()) + { + toTs = boost::lexical_cast<uint64_t>(getArguments["to-timestamp"]); + } + + if (getArguments.find("log-data-format") != getArguments.end()) + { + const std::string format = getArguments["log-data-format"]; + if (format == "json") + { + logDataInJson = true; + } + else if (format == "base64") + { + logDataInJson = false; + } + else + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange, "Unsupported value for log-data-format: " + format); + } + } + + Json::Value jsonLogs; + +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + { + std::list<IDatabaseBackend::AuditLog> logs; + + BaseIndexConnectionsPool::Accessor accessor(*connectionPool_); + accessor.GetBackend().GetAuditLogs(accessor.GetManager(), + logs, + userIdFilter, + resourceIdFilter, + actionFilter, + fromTs, toTs, + since, limit); + + for (std::list<IDatabaseBackend::AuditLog>::const_iterator it = logs.begin(); it != logs.end(); ++it) + { + Json::Value serializedAuditLog; + serializedAuditLog["SourcePlugin"] = it->GetSourcePlugin(); + serializedAuditLog["Timestamp"] = it->GetTimestamp(); + serializedAuditLog["UserId"] = it->GetUserId(); + serializedAuditLog["ResourceId"] = it->GetResourceId(); + serializedAuditLog["Action"] = it->GetAction(); + + std::string level; + switch (it->GetResourceType()) + { + case OrthancPluginResourceType_Patient: + level = "Patient"; + break; + + case OrthancPluginResourceType_Study: + level = "Study"; + break; + + case OrthancPluginResourceType_Series: + level = "Series"; + break; + + case OrthancPluginResourceType_Instance: + level = "Instance"; + break; + + case OrthancPluginResourceType_None: + level = "None"; + break; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + + serializedAuditLog["ResourceType"] = level; + + bool fillBase64; + if (logDataInJson) + { + if (!it->HasLogData()) + { + serializedAuditLog["JsonLogData"] = Json::nullValue; + fillBase64 = false; + } + else + { + Json::Value logData; + if (Orthanc::Toolbox::ReadJson(logData, it->GetLogData())) + { + serializedAuditLog["JsonLogData"] = logData; + fillBase64 = false; + } + else + { + // If the data is not JSON compatible, export it in base64 anyway + fillBase64 = true; + } + } + } + else + { + fillBase64 = true; + } + + if (fillBase64) + { + if (it->HasLogData()) + { + std::string b64; + Orthanc::Toolbox::EncodeBase64(b64, it->GetLogData()); + serializedAuditLog["Base64LogData"] = b64; + } + else + { + serializedAuditLog["Base64LogData"] = Json::nullValue; + } + } + + jsonLogs.append(serializedAuditLog); + } + } +#else + { + // Disable warnings about unused variables if audit logs are unavailable in the SDK + (void) since; + (void) limit; + (void) fromTs; + (void) toTs; + } +#endif + + OrthancPlugins::AnswerJson(jsonLogs, output); + } + void DatabaseBackendAdapterV4::Register(IndexBackend* backend, size_t countConnections, + bool useDynamicConnectionPool, unsigned int maxDatabaseRetries, unsigned int housekeepingDelaySeconds) { - std::unique_ptr<IndexConnectionsPool> pool(new IndexConnectionsPool(backend, countConnections, housekeepingDelaySeconds)); + std::unique_ptr<BaseIndexConnectionsPool> pool; + + if (useDynamicConnectionPool) + { + pool.reset(new DynamicIndexConnectionsPool(backend, countConnections, housekeepingDelaySeconds)); + } + else + { + pool.reset(new IndexConnectionsPool(backend, countConnections, housekeepingDelaySeconds)); + } if (isBackendInUse_) { @@ -1535,6 +1766,7 @@ } OrthancPluginContext* context = backend->GetContext(); + connectionPool_ = pool.get(); // we need to keep a pointer on the connectionPool for the static Audit log handler if (OrthancPluginRegisterDatabaseBackendV4(context, pool.release(), maxDatabaseRetries, CallBackend, FinalizeBackend) != OrthancPluginErrorCode_Success) @@ -1544,6 +1776,11 @@ } isBackendInUse_ = true; + +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + OrthancPluginRegisterAuditLogHandler(context, AuditLogHandler); + OrthancPlugins::RegisterRestCallback<GetAuditLogs>("/plugins/postgresql/audit-logs", true); +#endif }
--- a/Framework/Plugins/DatabaseBackendAdapterV4.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV4.h Tue Aug 12 15:27:09 2025 +0200 @@ -50,6 +50,7 @@ public: static void Register(IndexBackend* backend, size_t countConnections, + bool useDynamicConnectionPool, unsigned int maxDatabaseRetries, unsigned int housekeepingDelaySeconds);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Plugins/DynamicIndexConnectionsPool.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -0,0 +1,157 @@ +/** + * 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-2025 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2025 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 "DynamicIndexConnectionsPool.h" + +#include <Logging.h> + + +namespace OrthancDatabases +{ + void DynamicIndexConnectionsPool::PerformPoolHousekeeping() + { + CleanupOldConnections(); + + OrthancPluginSetMetricsValue(OrthancPlugins::GetGlobalContext(), "orthanc_index_active_connections", maxConnectionsCount_ - connectionsSemaphore_.GetAvailableResourcesCount(), OrthancPluginMetricsType_Default); + } + + + DynamicIndexConnectionsPool::DynamicIndexConnectionsPool(IndexBackend* backend, + size_t maxConnectionsCount, + unsigned int houseKeepingDelaySeconds) : + BaseIndexConnectionsPool(backend, houseKeepingDelaySeconds), + maxConnectionsCount_(maxConnectionsCount), + connectionsSemaphore_(maxConnectionsCount), + availableConnectionsSemaphore_(0) + { + if (maxConnectionsCount == 0) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange, + "There must be a non-zero number of connections to the database"); + } + } + + + DynamicIndexConnectionsPool::~DynamicIndexConnectionsPool() + { + boost::mutex::scoped_lock lock(connectionsMutex_); + + for (std::list<DatabaseManager*>::iterator + it = connections_.begin(); it != connections_.end(); ++it) + { + assert(*it != NULL); + delete *it; + } + } + + + void DynamicIndexConnectionsPool::OpenConnections(bool hasIdentifierTags, + const std::list<IdentifierTag>& identifierTags) + { + assert(backend_.get() != NULL); + + DynamicIndexConnectionsPool::Accessor accessor(*this); + backend_->ConfigureDatabase(accessor.GetManager(), hasIdentifierTags, identifierTags); + + StartHousekeepingThread(); + } + + + void DynamicIndexConnectionsPool::CloseConnections() + { + StopHousekeepingThread(); + + boost::mutex::scoped_lock lock(connectionsMutex_); + + for (std::list<DatabaseManager*>::iterator + it = connections_.begin(); it != connections_.end(); ++it) + { + assert(*it != NULL); + (*it)->Close(); + } + } + + DatabaseManager* DynamicIndexConnectionsPool::GetConnection() + { + if (availableConnectionsSemaphore_.TryAcquire(1)) // there is a connection directly available, take it + { + // LOG(INFO) << "--- Reusing an available connection"; + + boost::mutex::scoped_lock lock(connectionsMutex_); + + std::unique_ptr<DatabaseManager> manager(availableConnections_.front()); + availableConnections_.pop_front(); + return manager.release(); + } + else if (connectionsSemaphore_.TryAcquire(1)) // no connection directly available, check if we can create a new one + { + // LOG(INFO) << "--- Creating a new connection"; + + boost::mutex::scoped_lock lock(connectionsMutex_); + connections_.push_back(new DatabaseManager(backend_->CreateDatabaseFactory())); + connections_.back()->GetDatabase(); // Make sure to open the database connection + + // no need to push it in the availableConnections since it is being used immediately + return connections_.back(); + } + else // unable to get a connection now + { + return NULL; + } + + } + + void DynamicIndexConnectionsPool::CleanupOldConnections() + { + boost::mutex::scoped_lock lock(connectionsMutex_); + + while (availableConnectionsSemaphore_.TryAcquire(1)) + { + DatabaseManager* manager = availableConnections_.front(); + if (manager->GetElapsedSecondsSinceLastUse() > 60 || manager->GetElapsedSecondsSinceCreation() > 3600) + { + // LOG(INFO) << "--- Deleting an old connection"; + + availableConnections_.pop_front(); + connections_.remove(manager); + + delete manager; + connectionsSemaphore_.Release(1); + } + else + { + availableConnectionsSemaphore_.Release(1); // we have not consumed it + break; + } + } + + } + + void DynamicIndexConnectionsPool::ReleaseConnection(DatabaseManager* manager) + { + boost::mutex::scoped_lock lock(connectionsMutex_); + availableConnections_.push_front(manager); + availableConnectionsSemaphore_.Release(1); + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Plugins/DynamicIndexConnectionsPool.h Tue Aug 12 15:27:09 2025 +0200 @@ -0,0 +1,69 @@ +/** + * 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-2025 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2025 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 "IdentifierTag.h" +#include "IndexBackend.h" +#include "BaseIndexConnectionsPool.h" + +#include <MultiThreading/SharedMessageQueue.h> +#include <MultiThreading/Semaphore.h> + +#include <list> +#include <boost/thread.hpp> + +namespace OrthancDatabases +{ + class DynamicIndexConnectionsPool : public BaseIndexConnectionsPool + { + private: + boost::mutex connectionsMutex_; + size_t maxConnectionsCount_; + Orthanc::Semaphore connectionsSemaphore_; + std::list<DatabaseManager*> connections_; + Orthanc::Semaphore availableConnectionsSemaphore_; + std::list<DatabaseManager*> availableConnections_; + + void CleanupOldConnections(); + + protected: + virtual DatabaseManager* GetConnection() ORTHANC_OVERRIDE; + + virtual void ReleaseConnection(DatabaseManager* manager) ORTHANC_OVERRIDE; + + virtual void PerformPoolHousekeeping() ORTHANC_OVERRIDE; + + public: + DynamicIndexConnectionsPool(IndexBackend* backend /* takes ownership */, + size_t maxConnectionsCount, + unsigned int houseKeepingDelaySeconds); + + virtual ~DynamicIndexConnectionsPool(); + + virtual void OpenConnections(bool hasIdentifierTags, + const std::list<IdentifierTag>& identifierTags) ORTHANC_OVERRIDE; + + virtual void CloseConnections() ORTHANC_OVERRIDE; + }; +}
--- a/Framework/Plugins/IDatabaseBackend.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Plugins/IDatabaseBackend.h Tue Aug 12 15:27:09 2025 +0200 @@ -37,6 +37,80 @@ class IDatabaseBackend : public boost::noncopyable { public: + class AuditLog + { + private: + std::string timestamp_; + std::string sourcePlugin_; + std::string userId_; + OrthancPluginResourceType resourceType_; + std::string resourceId_; + std::string action_; + std::string logData_; + bool hasLogData_; + + public: + AuditLog(const std::string& timestamp, + const std::string& sourcePlugin, + const std::string& userId, + OrthancPluginResourceType resourceType, + const std::string& resourceId, + const std::string& action, + const std::string& logData, + bool hasLogData) : + timestamp_(timestamp), + sourcePlugin_(sourcePlugin), + userId_(userId), + resourceType_(resourceType), + resourceId_(resourceId), + action_(action), + logData_(logData), + hasLogData_(hasLogData) + { + } + + const std::string& GetTimestamp() const + { + return timestamp_; + } + + const std::string& GetSourcePlugin() const + { + return sourcePlugin_; + } + + const std::string& GetUserId() const + { + return userId_; + } + + OrthancPluginResourceType GetResourceType() const + { + return resourceType_; + } + + const std::string& GetResourceId() const + { + return resourceId_; + } + + const std::string& GetAction() const + { + return action_; + } + + const std::string& GetLogData() const + { + return logData_; + } + + bool HasLogData() const + { + return hasLogData_; + } + }; + + public: virtual ~IDatabaseBackend() { } @@ -65,6 +139,8 @@ virtual bool HasQueues() const = 0; + virtual bool HasAuditLogs() const = 0; + virtual void AddAttachment(DatabaseManager& manager, int64_t id, const OrthancPluginAttachment& attachment, @@ -459,6 +535,27 @@ const std::string& customData) = 0; #endif +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + virtual void RecordAuditLog(DatabaseManager& manager, + const std::string& sourcePlugin, + const std::string& userId, + OrthancPluginResourceType type, + const std::string& resourceId, + const std::string& action, + const void* logData, + uint32_t logDataSize) = 0; + + virtual void GetAuditLogs(DatabaseManager& manager, + std::list<AuditLog>& logs, + const std::string& userIdFilter, + const std::string& resourceIdFilter, + const std::string& actionFilter, + uint64_t fromTs, + uint64_t toTs, + uint64_t since, + uint64_t limit) = 0; +#endif + virtual bool HasPerformDbHousekeeping() = 0; virtual void PerformDbHousekeeping(DatabaseManager& manager) = 0;
--- a/Framework/Plugins/IndexBackend.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Plugins/IndexBackend.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -2069,9 +2069,7 @@ break; case Dialect_PostgreSQL: - statement.reset(new DatabaseManager::CachedStatement( - STATEMENT_FROM_HERE, manager, - "SELECT CAST(COUNT(*) AS BIGINT) FROM PatientRecyclingOrder")); + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); break; case Dialect_MSSQL: @@ -3024,6 +3022,7 @@ void IndexBackend::Register(IndexBackend* backend, size_t countConnections, + bool useDynamicConnectionPool, unsigned int maxDatabaseRetries, unsigned int housekeepingDelaySeconds) { @@ -3039,7 +3038,7 @@ # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) if (OrthancPluginCheckVersionAdvanced(backend->GetContext(), 1, 12, 0) == 1) { - DatabaseBackendAdapterV4::Register(backend, countConnections, maxDatabaseRetries, housekeepingDelaySeconds); + DatabaseBackendAdapterV4::Register(backend, countConnections, useDynamicConnectionPool, maxDatabaseRetries, housekeepingDelaySeconds); return; } # endif @@ -4683,4 +4682,137 @@ statement.Execute(args); } #endif + +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + void IndexBackend::RecordAuditLog(DatabaseManager& manager, + const std::string& sourcePlugin, + const std::string& userId, + OrthancPluginResourceType resourceType, + const std::string& resourceId, + const std::string& action, + const void* logData, + uint32_t logDataSize) + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "INSERT INTO AuditLogs (sourcePlugin, userId, resourceType, resourceId, action, logData) " + "VALUES(${sourcePlugin}, ${userId}, ${resourceType}, ${resourceId}, ${action}, ${logData})"); + + statement.SetParameterType("sourcePlugin", ValueType_Utf8String); + statement.SetParameterType("userId", ValueType_Utf8String); + statement.SetParameterType("resourceType", ValueType_Integer64); + statement.SetParameterType("resourceId", ValueType_Utf8String); + statement.SetParameterType("action", ValueType_Utf8String); + statement.SetParameterType("logData", ValueType_BinaryString); + + Dictionary args; + args.SetUtf8Value("sourcePlugin", sourcePlugin); + args.SetUtf8Value("userId", userId); + args.SetIntegerValue("resourceType", static_cast<int>(resourceType)); + args.SetUtf8Value("resourceId", resourceId); + args.SetUtf8Value("action", action); + + if (logData != NULL && logDataSize > 0) + { + args.SetBinaryValue("logData", logData, logDataSize); + } + else + { + args.SetBinaryNullValue("logData"); + } + + statement.Execute(args); + } + + void IndexBackend::GetAuditLogs(DatabaseManager& manager, + std::list<AuditLog>& logs, + const std::string& userIdFilter, + const std::string& resourceIdFilter, + const std::string& actionFilter, + uint64_t fromTs, + uint64_t toTs, + uint64_t since, + uint64_t limit) + { + LookupFormatter formatter(manager.GetDialect()); + std::vector<std::string> filters; + + std::string sql = "SELECT to_char(ts, 'YYYY-MM-DD\"T\"HH24:MI:SS.MS\"Z\"'), sourcePlugin, userId, resourceType, resourceId, action, logData FROM AuditLogs "; + + if (!userIdFilter.empty()) + { + filters.push_back("userId = " + formatter.GenerateParameter(userIdFilter)); + } + + if (!resourceIdFilter.empty()) + { + filters.push_back("resourceId = " + formatter.GenerateParameter(resourceIdFilter)); + } + + if (!actionFilter.empty()) + { + filters.push_back("action = " + formatter.GenerateParameter(actionFilter)); + } + + if (fromTs > 0) + { + filters.push_back("ts >= " + formatter.GenerateParameter(fromTs)); + } + + if (toTs > 0) + { + filters.push_back("ts < " + formatter.GenerateParameter(toTs)); + } + + if (filters.size() > 0) + { + std::string joinedFilters; + Orthanc::Toolbox::JoinStrings(joinedFilters, filters, " AND "); + sql += " WHERE " + joinedFilters; + } + + if (since > 0 || limit > 0) + { + sql += formatter.FormatLimits(since, limit); + } + + sql += " ORDER BY ts ASC"; + + DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql); + statement.SetReadOnly(true); + + statement.Execute(formatter.GetDictionary()); + + if (!statement.IsDone()) + { + if (statement.GetResultFieldsCount() != 7) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + + statement.SetResultFieldType(0, ValueType_Utf8String); + statement.SetResultFieldType(1, ValueType_Utf8String); + statement.SetResultFieldType(2, ValueType_Utf8String); + statement.SetResultFieldType(3, ValueType_Integer64); + statement.SetResultFieldType(4, ValueType_Utf8String); + statement.SetResultFieldType(5, ValueType_Utf8String); + statement.SetResultFieldType(6, ValueType_BinaryString); + + while (!statement.IsDone()) + { + logs.push_back(AuditLog(statement.ReadString(0), + statement.ReadString(1), + statement.ReadString(2), + static_cast<OrthancPluginResourceType>(statement.ReadInteger64(3)), + statement.ReadString(4), + statement.ReadString(5), + statement.ReadStringOrNull(6), + !statement.IsNull(6))); + + statement.Next(); + } + } + } +#endif + }
--- a/Framework/Plugins/IndexBackend.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Plugins/IndexBackend.h Tue Aug 12 15:27:09 2025 +0200 @@ -521,6 +521,27 @@ #endif +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + virtual void RecordAuditLog(DatabaseManager& manager, + const std::string& sourcePlugin, + const std::string& userId, + OrthancPluginResourceType type, + const std::string& resourceId, + const std::string& action, + const void* logData, + uint32_t logDataSize) ORTHANC_OVERRIDE; + + virtual void GetAuditLogs(DatabaseManager& manager, + std::list<AuditLog>& logs, + const std::string& userIdFilter, + const std::string& resourceIdFilter, + const std::string& actionFilter, + uint64_t fromTs, + uint64_t toTs, + uint64_t since, + uint64_t limit) ORTHANC_OVERRIDE; +#endif + virtual bool HasPerformDbHousekeeping() ORTHANC_OVERRIDE { return false; @@ -539,6 +560,7 @@ **/ static void Register(IndexBackend* backend, size_t countConnections, + bool useDynamicConnectionPool, unsigned int maxDatabaseRetries, unsigned int housekeepingDelaySeconds);
--- a/Framework/Plugins/IndexConnectionsPool.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Plugins/IndexConnectionsPool.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -47,63 +47,17 @@ }; - void IndexConnectionsPool::HousekeepingThread(IndexConnectionsPool* that) - { -#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 2) - OrthancPluginSetCurrentThreadName(OrthancPlugins::GetGlobalContext(), "DB HOUSEKEEPING"); -#endif - - boost::posix_time::ptime lastInvocation = boost::posix_time::second_clock::local_time(); - - while (that->housekeepingContinue_) - { - if (boost::posix_time::second_clock::local_time() - lastInvocation >= that->housekeepingDelay_) - { - try - { - Accessor accessor(*that); - accessor.GetBackend().PerformDbHousekeeping(accessor.GetManager()); - } - catch (Orthanc::OrthancException& e) - { - LOG(ERROR) << "Exception during the database housekeeping: " << e.What(); - } - catch (...) - { - LOG(ERROR) << "Native exception during the database houskeeping"; - } - - lastInvocation = boost::posix_time::second_clock::local_time(); - } - - boost::this_thread::sleep(boost::posix_time::milliseconds(1000)); - } - } - - IndexConnectionsPool::IndexConnectionsPool(IndexBackend* backend, size_t countConnections, unsigned int houseKeepingDelaySeconds) : - backend_(backend), - countConnections_(countConnections), - housekeepingContinue_(true), - housekeepingDelay_(boost::posix_time::seconds(houseKeepingDelaySeconds)) + BaseIndexConnectionsPool(backend, houseKeepingDelaySeconds), + countConnections_(countConnections) { if (countConnections == 0) { throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange, "There must be a non-zero number of connections to the database"); } - else if (backend == NULL) - { - throw Orthanc::OrthancException(Orthanc::ErrorCode_NullPointer); - } - else if (backend->HasPerformDbHousekeeping() && - houseKeepingDelaySeconds == 0) - { - throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange, - "The delay between two executions of housekeeping cannot be zero second"); - } else { context_ = backend_->GetContext(); @@ -122,6 +76,13 @@ } + void IndexConnectionsPool::PerformPoolHousekeeping() + { + // this is actually a fixed value with this pool ! + OrthancPluginSetMetricsValue(OrthancPlugins::GetGlobalContext(), "orthanc_index_active_connections", countConnections_, OrthancPluginMetricsType_Default); + } + + void IndexConnectionsPool::OpenConnections(bool hasIdentifierTags, const std::list<IdentifierTag>& identifierTags) { @@ -152,13 +113,7 @@ availableConnections_.Enqueue(new ManagerReference(**it)); } - // Start the housekeeping thread - housekeepingContinue_ = true; - - if (backend_->HasPerformDbHousekeeping()) - { - housekeepingThread_ = boost::thread(HousekeepingThread, this); - } + StartHousekeepingThread(); } else { @@ -169,14 +124,7 @@ void IndexConnectionsPool::CloseConnections() { - { - // Stop the housekeeping thread - housekeepingContinue_ = false; - if (housekeepingThread_.joinable()) - { - housekeepingThread_.join(); - } - } + StopHousekeepingThread(); boost::unique_lock<boost::shared_mutex> lock(connectionsMutex_); @@ -199,40 +147,20 @@ } } - - IndexConnectionsPool::Accessor::Accessor(IndexConnectionsPool& pool) : - lock_(pool.connectionsMutex_), - pool_(pool), - manager_(NULL) + DatabaseManager* IndexConnectionsPool::GetConnection() { - for (;;) + std::unique_ptr<Orthanc::IDynamicObject> manager(availableConnections_.Dequeue(1)); + if (manager.get() != NULL) { - std::unique_ptr<Orthanc::IDynamicObject> manager(pool.availableConnections_.Dequeue(100)); - if (manager.get() != NULL) - { - manager_ = &dynamic_cast<ManagerReference&>(*manager).GetManager(); - return; - } + return &dynamic_cast<ManagerReference&>(*manager).GetManager(); } + return NULL; } - - IndexConnectionsPool::Accessor::~Accessor() + void IndexConnectionsPool::ReleaseConnection(DatabaseManager* manager) { - assert(manager_ != NULL); - pool_.availableConnections_.Enqueue(new ManagerReference(*manager_)); + assert(manager != NULL); + availableConnections_.Enqueue(new ManagerReference(*manager)); } - - IndexBackend& IndexConnectionsPool::Accessor::GetBackend() const - { - return *pool_.backend_; - } - - - DatabaseManager& IndexConnectionsPool::Accessor::GetManager() const - { - assert(manager_ != NULL); - return *manager_; - } }
--- a/Framework/Plugins/IndexConnectionsPool.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Plugins/IndexConnectionsPool.h Tue Aug 12 15:27:09 2025 +0200 @@ -25,6 +25,7 @@ #include "IdentifierTag.h" #include "IndexBackend.h" +#include "BaseIndexConnectionsPool.h" #include <MultiThreading/SharedMessageQueue.h> @@ -33,55 +34,39 @@ namespace OrthancDatabases { - class IndexConnectionsPool : public boost::noncopyable + /** + * This class corresponds to "IndexConnectionsPool.h" in + * OrthancPostgreSQL-8.0, but with a base class that is shared with + * a new class "DynamicIndexConnectionsPool": + * https://orthanc.uclouvain.be/hg/orthanc-databases/file/OrthancPostgreSQL-8.0/Framework/Plugins/IndexConnectionsPool.h + **/ + class IndexConnectionsPool : public BaseIndexConnectionsPool { private: class ManagerReference; - std::unique_ptr<IndexBackend> backend_; - OrthancPluginContext* context_; boost::shared_mutex connectionsMutex_; size_t countConnections_; std::list<DatabaseManager*> connections_; Orthanc::SharedMessageQueue availableConnections_; - bool housekeepingContinue_; - boost::thread housekeepingThread_; - boost::posix_time::time_duration housekeepingDelay_; - static void HousekeepingThread(IndexConnectionsPool* that); + protected: + virtual DatabaseManager* GetConnection() ORTHANC_OVERRIDE; + + virtual void ReleaseConnection(DatabaseManager* manager) ORTHANC_OVERRIDE; + + virtual void PerformPoolHousekeeping() ORTHANC_OVERRIDE; public: IndexConnectionsPool(IndexBackend* backend /* takes ownership */, size_t countConnections, unsigned int houseKeepingDelaySeconds); - ~IndexConnectionsPool(); - - OrthancPluginContext* GetContext() const - { - return context_; - } - - void OpenConnections(bool hasIdentifierTags, - const std::list<IdentifierTag>& identifierTags); - - void CloseConnections(); + virtual ~IndexConnectionsPool(); - class Accessor : public boost::noncopyable - { - private: - boost::shared_lock<boost::shared_mutex> lock_; - IndexConnectionsPool& pool_; - DatabaseManager* manager_; - - public: - explicit Accessor(IndexConnectionsPool& pool); + virtual void OpenConnections(bool hasIdentifierTags, + const std::list<IdentifierTag>& identifierTags) ORTHANC_OVERRIDE; - ~Accessor(); - - IndexBackend& GetBackend() const; - - DatabaseManager& GetManager() const; - }; + virtual void CloseConnections() ORTHANC_OVERRIDE; }; }
--- a/Framework/Plugins/IndexUnitTests.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Plugins/IndexUnitTests.h Tue Aug 12 15:27:09 2025 +0200 @@ -40,12 +40,17 @@ #if ORTHANC_ENABLE_POSTGRESQL == 1 # define HAS_REVISIONS 1 +// we can not test patient protection in PG because it is now deeply intricated in the CreateInstance function that is too difficult to call from here +# define CAN_TEST_PATIENT_PROTECTION 0 #elif ORTHANC_ENABLE_MYSQL == 1 # define HAS_REVISIONS 0 +# define CAN_TEST_PATIENT_PROTECTION 1 #elif ORTHANC_ENABLE_SQLITE == 1 # define HAS_REVISIONS 1 +# define CAN_TEST_PATIENT_PROTECTION 1 #elif ORTHANC_ENABLE_ODBC == 1 # define HAS_REVISIONS 1 +# define CAN_TEST_PATIENT_PROTECTION 1 #else # error Unknown database backend #endif @@ -369,54 +374,53 @@ ASSERT_EQ(0u, db.GetResourcesCount(*manager, OrthancPluginResourceType_Study)); ASSERT_EQ(0u, db.GetResourcesCount(*manager, OrthancPluginResourceType_Series)); - int64_t a = db.CreateResource(*manager, "study", OrthancPluginResourceType_Study); - ASSERT_TRUE(db.IsExistingResource(*manager, a)); - ASSERT_FALSE(db.IsExistingResource(*manager, a + 1)); + int64_t studyId = db.CreateResource(*manager, "study", OrthancPluginResourceType_Study); + ASSERT_TRUE(db.IsExistingResource(*manager, studyId)); + ASSERT_FALSE(db.IsExistingResource(*manager, studyId + 1)); - int64_t b; + int64_t tmp; OrthancPluginResourceType t; - ASSERT_FALSE(db.LookupResource(b, t, *manager, "world")); - ASSERT_TRUE(db.LookupResource(b, t, *manager, "study")); - ASSERT_EQ(a, b); + ASSERT_FALSE(db.LookupResource(tmp, t, *manager, "world")); + ASSERT_TRUE(db.LookupResource(tmp, t, *manager, "study")); + ASSERT_EQ(studyId, tmp); ASSERT_EQ(OrthancPluginResourceType_Study, t); - b = db.CreateResource(*manager, "series", OrthancPluginResourceType_Series); - ASSERT_NE(a, b); + int64_t seriesId = db.CreateResource(*manager, "series", OrthancPluginResourceType_Series); + ASSERT_NE(studyId, seriesId); - ASSERT_EQ("study", db.GetPublicId(*manager, a)); - ASSERT_EQ("series", db.GetPublicId(*manager, b)); - ASSERT_EQ(OrthancPluginResourceType_Study, db.GetResourceType(*manager, a)); - ASSERT_EQ(OrthancPluginResourceType_Series, db.GetResourceType(*manager, b)); + ASSERT_EQ("study", db.GetPublicId(*manager, studyId)); + ASSERT_EQ("series", db.GetPublicId(*manager, seriesId)); + ASSERT_EQ(OrthancPluginResourceType_Study, db.GetResourceType(*manager, studyId)); + ASSERT_EQ(OrthancPluginResourceType_Series, db.GetResourceType(*manager, seriesId)); - db.AttachChild(*manager, a, b); + db.AttachChild(*manager, studyId, seriesId); - int64_t c; - ASSERT_FALSE(db.LookupParent(c, *manager, a)); - ASSERT_TRUE(db.LookupParent(c, *manager, b)); - ASSERT_EQ(a, c); + ASSERT_FALSE(db.LookupParent(tmp, *manager, studyId)); + ASSERT_TRUE(db.LookupParent(tmp, *manager, seriesId)); + ASSERT_EQ(studyId, tmp); - c = db.CreateResource(*manager, "series2", OrthancPluginResourceType_Series); - db.AttachChild(*manager, a, c); + int64_t series2Id = db.CreateResource(*manager, "series2", OrthancPluginResourceType_Series); + db.AttachChild(*manager, studyId, series2Id); ASSERT_EQ(3u, db.GetAllResourcesCount(*manager)); ASSERT_EQ(0u, db.GetResourcesCount(*manager, OrthancPluginResourceType_Patient)); ASSERT_EQ(1u, db.GetResourcesCount(*manager, OrthancPluginResourceType_Study)); ASSERT_EQ(2u, db.GetResourcesCount(*manager, OrthancPluginResourceType_Series)); - ASSERT_FALSE(db.GetParentPublicId(s, *manager, a)); - ASSERT_TRUE(db.GetParentPublicId(s, *manager, b)); ASSERT_EQ("study", s); - ASSERT_TRUE(db.GetParentPublicId(s, *manager, c)); ASSERT_EQ("study", s); + ASSERT_FALSE(db.GetParentPublicId(s, *manager, studyId)); + ASSERT_TRUE(db.GetParentPublicId(s, *manager, seriesId)); ASSERT_EQ("study", s); + ASSERT_TRUE(db.GetParentPublicId(s, *manager, series2Id)); ASSERT_EQ("study", s); std::list<std::string> children; - db.GetChildren(children, *manager, a); + db.GetChildren(children, *manager, studyId); ASSERT_EQ(2u, children.size()); - db.GetChildren(children, *manager, b); + db.GetChildren(children, *manager, seriesId); ASSERT_EQ(0u, children.size()); - db.GetChildren(children, *manager, c); + db.GetChildren(children, *manager, series2Id); ASSERT_EQ(0u, children.size()); std::list<std::string> cp; - db.GetChildrenPublicId(cp, *manager, a); + db.GetChildrenPublicId(cp, *manager, studyId); ASSERT_EQ(2u, cp.size()); ASSERT_TRUE(cp.front() == "series" || cp.front() == "series2"); ASSERT_TRUE(cp.back() == "series" || cp.back() == "series2"); @@ -435,17 +439,17 @@ ASSERT_NE(pub.front(), pub.back()); std::list<int64_t> ci; - db.GetChildrenInternalId(ci, *manager, a); + db.GetChildrenInternalId(ci, *manager, studyId); ASSERT_EQ(2u, ci.size()); - ASSERT_TRUE(ci.front() == b || ci.front() == c); - ASSERT_TRUE(ci.back() == b || ci.back() == c); + ASSERT_TRUE(ci.front() == seriesId || ci.front() == series2Id); + ASSERT_TRUE(ci.back() == seriesId || ci.back() == series2Id); ASSERT_NE(ci.front(), ci.back()); - db.SetMetadata(*manager, a, Orthanc::MetadataType_ModifiedFrom, "modified", 42); - db.SetMetadata(*manager, a, Orthanc::MetadataType_LastUpdate, "update2", 43); + db.SetMetadata(*manager, studyId, Orthanc::MetadataType_ModifiedFrom, "modified", 42); + db.SetMetadata(*manager, studyId, Orthanc::MetadataType_LastUpdate, "update2", 43); int64_t revision = -1; - ASSERT_FALSE(db.LookupMetadata(s, revision, *manager, b, Orthanc::MetadataType_LastUpdate)); - ASSERT_TRUE(db.LookupMetadata(s, revision, *manager, a, Orthanc::MetadataType_LastUpdate)); + ASSERT_FALSE(db.LookupMetadata(s, revision, *manager, seriesId, Orthanc::MetadataType_LastUpdate)); + ASSERT_TRUE(db.LookupMetadata(s, revision, *manager, studyId, Orthanc::MetadataType_LastUpdate)); ASSERT_EQ("update2", s); #if HAS_REVISIONS == 1 @@ -454,8 +458,8 @@ ASSERT_EQ(0, revision); #endif - db.SetMetadata(*manager, a, Orthanc::MetadataType_LastUpdate, reinterpret_cast<const char*>(UTF8), 44); - ASSERT_TRUE(db.LookupMetadata(s, revision, *manager, a, Orthanc::MetadataType_LastUpdate)); + db.SetMetadata(*manager, studyId, Orthanc::MetadataType_LastUpdate, reinterpret_cast<const char*>(UTF8), 44); + ASSERT_TRUE(db.LookupMetadata(s, revision, *manager, studyId, Orthanc::MetadataType_LastUpdate)); ASSERT_STREQ(reinterpret_cast<const char*>(UTF8), s.c_str()); #if HAS_REVISIONS == 1 @@ -465,12 +469,12 @@ #endif std::list<int32_t> md; - db.ListAvailableMetadata(md, *manager, a); + db.ListAvailableMetadata(md, *manager, studyId); ASSERT_EQ(2u, md.size()); ASSERT_TRUE(md.front() == Orthanc::MetadataType_ModifiedFrom || md.back() == Orthanc::MetadataType_ModifiedFrom); ASSERT_TRUE(md.front() == Orthanc::MetadataType_LastUpdate || md.back() == Orthanc::MetadataType_LastUpdate); std::string mdd; - ASSERT_TRUE(db.LookupMetadata(mdd, revision, *manager, a, Orthanc::MetadataType_ModifiedFrom)); + ASSERT_TRUE(db.LookupMetadata(mdd, revision, *manager, studyId, Orthanc::MetadataType_ModifiedFrom)); ASSERT_EQ("modified", mdd); #if HAS_REVISIONS == 1 @@ -479,7 +483,7 @@ ASSERT_EQ(0, revision); #endif - ASSERT_TRUE(db.LookupMetadata(mdd, revision, *manager, a, Orthanc::MetadataType_LastUpdate)); + ASSERT_TRUE(db.LookupMetadata(mdd, revision, *manager, studyId, Orthanc::MetadataType_LastUpdate)); ASSERT_EQ(reinterpret_cast<const char*>(UTF8), mdd); #if HAS_REVISIONS == 1 @@ -488,16 +492,16 @@ ASSERT_EQ(0, revision); #endif - db.ListAvailableMetadata(md, *manager, b); + db.ListAvailableMetadata(md, *manager, seriesId); ASSERT_EQ(0u, md.size()); - ASSERT_TRUE(db.LookupMetadata(s, revision, *manager, a, Orthanc::MetadataType_LastUpdate)); - db.DeleteMetadata(*manager, a, Orthanc::MetadataType_LastUpdate); - ASSERT_FALSE(db.LookupMetadata(s, revision, *manager, a, Orthanc::MetadataType_LastUpdate)); - db.DeleteMetadata(*manager, b, Orthanc::MetadataType_LastUpdate); - ASSERT_FALSE(db.LookupMetadata(s, revision, *manager, a, Orthanc::MetadataType_LastUpdate)); + ASSERT_TRUE(db.LookupMetadata(s, revision, *manager, studyId, Orthanc::MetadataType_LastUpdate)); + db.DeleteMetadata(*manager, studyId, Orthanc::MetadataType_LastUpdate); + ASSERT_FALSE(db.LookupMetadata(s, revision, *manager, studyId, Orthanc::MetadataType_LastUpdate)); + db.DeleteMetadata(*manager, seriesId, Orthanc::MetadataType_LastUpdate); + ASSERT_FALSE(db.LookupMetadata(s, revision, *manager, studyId, Orthanc::MetadataType_LastUpdate)); - db.ListAvailableMetadata(md, *manager, a); + db.ListAvailableMetadata(md, *manager, studyId); ASSERT_EQ(1u, md.size()); ASSERT_EQ(Orthanc::MetadataType_ModifiedFrom, md.front()); @@ -507,37 +511,38 @@ std::list<int32_t> fc; - OrthancPluginAttachment a1; - a1.uuid = "uuid1"; - a1.contentType = Orthanc::FileContentType_Dicom; - a1.uncompressedSize = 42; - a1.uncompressedHash = "md5_1"; - a1.compressionType = Orthanc::CompressionType_None; - a1.compressedSize = 42; - a1.compressedHash = "md5_1"; + OrthancPluginAttachment att1; + att1.uuid = "uuid1"; + att1.contentType = Orthanc::FileContentType_Dicom; + att1.uncompressedSize = 42; + att1.uncompressedHash = "md5_1"; + att1.compressionType = Orthanc::CompressionType_None; + att1.compressedSize = 42; + att1.compressedHash = "md5_1"; - OrthancPluginAttachment a2; - a2.uuid = "uuid2"; - a2.contentType = Orthanc::FileContentType_DicomAsJson; - a2.uncompressedSize = 4242; - a2.uncompressedHash = "md5_2"; - a2.compressionType = Orthanc::CompressionType_None; - a2.compressedSize = 4242; - a2.compressedHash = "md5_2"; + OrthancPluginAttachment att2; + att2.uuid = "uuid2"; + att2.contentType = Orthanc::FileContentType_DicomAsJson; + att2.uncompressedSize = 4242; + att2.uncompressedHash = "md5_2"; + att2.compressionType = Orthanc::CompressionType_None; + att2.compressedSize = 4242; + att2.compressedHash = "md5_2"; #if ORTHANC_PLUGINS_HAS_ATTACHMENTS_CUSTOM_DATA == 1 - db.AddAttachment(*manager, a, a1, 42, "my_custom_data"); + db.AddAttachment(*manager, studyId, att1, 42, "my_custom_data"); + db.ListAvailableAttachments(fc, *manager, studyId); #else - db.AddAttachment(*manager, a, a1, 42); + db.AddAttachment(*manager, studyId, att1, 42); #endif - db.ListAvailableAttachments(fc, *manager, a); + db.ListAvailableAttachments(fc, *manager, studyId); ASSERT_EQ(1u, fc.size()); ASSERT_EQ(Orthanc::FileContentType_Dicom, fc.front()); - db.AddAttachment(*manager, a, a2, 43); - db.ListAvailableAttachments(fc, *manager, a); + db.AddAttachment(*manager, studyId, att2, 43); + db.ListAvailableAttachments(fc, *manager, studyId); ASSERT_EQ(2u, fc.size()); - ASSERT_FALSE(db.LookupAttachment(*output, revision, *manager, b, Orthanc::FileContentType_Dicom)); + ASSERT_FALSE(db.LookupAttachment(*output, revision, *manager, seriesId, Orthanc::FileContentType_Dicom)); #if ORTHANC_PLUGINS_HAS_ATTACHMENTS_CUSTOM_DATA == 1 { @@ -576,7 +581,7 @@ expectedAttachment->compressionType = Orthanc::CompressionType_None; expectedAttachment->compressedSize = 42; expectedAttachment->compressedHash = "md5_1"; - ASSERT_TRUE(db.LookupAttachment(*output, revision, *manager, a, Orthanc::FileContentType_Dicom)); + ASSERT_TRUE(db.LookupAttachment(*output, revision, *manager, studyId, Orthanc::FileContentType_Dicom)); #if HAS_REVISIONS == 1 ASSERT_EQ(42, revision); @@ -593,7 +598,7 @@ expectedAttachment->compressedSize = 4242; expectedAttachment->compressedHash = "md5_2"; revision = -1; - ASSERT_TRUE(db.LookupAttachment(*output, revision, *manager, a, Orthanc::FileContentType_DicomAsJson)); + ASSERT_TRUE(db.LookupAttachment(*output, revision, *manager, studyId, Orthanc::FileContentType_DicomAsJson)); #if HAS_REVISIONS == 1 ASSERT_EQ(43, revision); @@ -601,21 +606,21 @@ ASSERT_EQ(0, revision); #endif - db.ListAvailableAttachments(fc, *manager, b); + db.ListAvailableAttachments(fc, *manager, seriesId); ASSERT_EQ(0u, fc.size()); - db.DeleteAttachment(*output, *manager, a, Orthanc::FileContentType_Dicom); - db.ListAvailableAttachments(fc, *manager, a); + db.DeleteAttachment(*output, *manager, studyId, Orthanc::FileContentType_Dicom); + db.ListAvailableAttachments(fc, *manager, studyId); ASSERT_EQ(1u, fc.size()); ASSERT_EQ(Orthanc::FileContentType_DicomAsJson, fc.front()); - db.DeleteAttachment(*output, *manager, a, Orthanc::FileContentType_DicomAsJson); - db.ListAvailableAttachments(fc, *manager, a); + db.DeleteAttachment(*output, *manager, studyId, Orthanc::FileContentType_DicomAsJson); + db.ListAvailableAttachments(fc, *manager, studyId); ASSERT_EQ(0u, fc.size()); - db.SetIdentifierTag(*manager, a, 0x0010, 0x0020, "patient"); - db.SetIdentifierTag(*manager, a, 0x0020, 0x000d, "study"); - db.SetMainDicomTag(*manager, a, 0x0010, 0x0020, "patient"); - db.SetMainDicomTag(*manager, a, 0x0020, 0x000d, "study"); - db.SetMainDicomTag(*manager, a, 0x0008, 0x1030, reinterpret_cast<const char*>(UTF8)); + db.SetIdentifierTag(*manager, studyId, 0x0010, 0x0020, "patient"); + db.SetIdentifierTag(*manager, studyId, 0x0020, 0x000d, "study"); + db.SetMainDicomTag(*manager, studyId, 0x0010, 0x0020, "patient"); + db.SetMainDicomTag(*manager, studyId, 0x0020, 0x000d, "study"); + db.SetMainDicomTag(*manager, studyId, 0x0008, 0x1030, reinterpret_cast<const char*>(UTF8)); expectedDicomTags.clear(); expectedDicomTags.push_back(OrthancPluginDicomTag()); @@ -632,13 +637,13 @@ expectedDicomTags.back().value = reinterpret_cast<const char*>(UTF8); countDicomTags = 0; - db.GetMainDicomTags(*output, *manager, a); + db.GetMainDicomTags(*output, *manager, studyId); ASSERT_EQ(3u, countDicomTags); db.LookupIdentifier(ci, *manager, OrthancPluginResourceType_Study, 0x0010, 0x0020, OrthancPluginIdentifierConstraint_Equal, "patient"); ASSERT_EQ(1u, ci.size()); - ASSERT_EQ(a, ci.front()); + ASSERT_EQ(studyId, ci.front()); db.LookupIdentifier(ci, *manager, OrthancPluginResourceType_Study, 0x0010, 0x0020, OrthancPluginIdentifierConstraint_Equal, "study"); ASSERT_EQ(0u, ci.size()); @@ -668,8 +673,11 @@ db.GetAllPublicIds(pub, *manager, OrthancPluginResourceType_Instance); ASSERT_EQ(0u, pub.size()); ASSERT_EQ(3u, db.GetAllResourcesCount(*manager)); - ASSERT_EQ(0u, db.GetUnprotectedPatientsCount(*manager)); // No patient was inserted - ASSERT_TRUE(db.IsExistingResource(*manager, c)); + #if CAN_TEST_PATIENT_PROTECTION == 1 + ASSERT_EQ(0u, db.GetUnprotectedPatientsCount(*manager)); // No patient was inserted + #endif + + ASSERT_TRUE(db.IsExistingResource(*manager, series2Id)); { // A transaction is needed here for MySQL, as it was not possible @@ -681,7 +689,7 @@ deletedResources.clear(); remainingAncestor.reset(); - db.DeleteResource(*output, *manager, c); + db.DeleteResource(*output, *manager, series2Id); ASSERT_EQ(0u, deletedAttachments.size()); ASSERT_EQ(1u, deletedResources.size()); @@ -697,22 +705,22 @@ deletedResources.clear(); remainingAncestor.reset(); - ASSERT_FALSE(db.IsExistingResource(*manager, c)); - ASSERT_TRUE(db.IsExistingResource(*manager, a)); - ASSERT_TRUE(db.IsExistingResource(*manager, b)); + ASSERT_FALSE(db.IsExistingResource(*manager, series2Id)); + ASSERT_TRUE(db.IsExistingResource(*manager, studyId)); + ASSERT_TRUE(db.IsExistingResource(*manager, seriesId)); ASSERT_EQ(2u, db.GetAllResourcesCount(*manager)); { // An explicit transaction is needed here manager->StartTransaction(TransactionType_ReadWrite); - db.DeleteResource(*output, *manager, a); + db.DeleteResource(*output, *manager, studyId); // delete the study that only has one series left -> 2 resources shall be deleted manager->CommitTransaction(); } ASSERT_EQ(0u, db.GetAllResourcesCount(*manager)); - ASSERT_FALSE(db.IsExistingResource(*manager, a)); - ASSERT_FALSE(db.IsExistingResource(*manager, b)); - ASSERT_FALSE(db.IsExistingResource(*manager, c)); + ASSERT_FALSE(db.IsExistingResource(*manager, studyId)); + ASSERT_FALSE(db.IsExistingResource(*manager, seriesId)); + ASSERT_FALSE(db.IsExistingResource(*manager, series2Id)); ASSERT_EQ(0u, deletedAttachments.size()); ASSERT_EQ(2u, deletedResources.size()); @@ -721,6 +729,7 @@ ASSERT_FALSE(remainingAncestor.get() != NULL); ASSERT_EQ(0u, db.GetAllResourcesCount(*manager)); +#if CAN_TEST_PATIENT_PROTECTION == 1 ASSERT_EQ(0u, db.GetUnprotectedPatientsCount(*manager)); int64_t p1 = db.CreateResource(*manager, "patient1", OrthancPluginResourceType_Patient); int64_t p2 = db.CreateResource(*manager, "patient2", OrthancPluginResourceType_Patient); @@ -752,6 +761,14 @@ ASSERT_EQ(p1, r); { + manager->StartTransaction(TransactionType_ReadWrite); + db.DeleteResource(*output, *manager, p1); + db.DeleteResource(*output, *manager, p3); + manager->CommitTransaction(); + } +#endif + + { // Test creating a large property of 16MB (large properties are // notably necessary to serialize jobs) // https://groups.google.com/g/orthanc-users/c/1Y3nTBdr0uE/m/K7PA5pboAgAJ @@ -768,23 +785,14 @@ // column in "ServerProperties" is "TEXT" instead of "LONGTEXT" db.SetGlobalProperty(*manager, "some-server", Orthanc::GlobalProperty_DatabaseInternal8, longProperty.c_str()); - std::string tmp; - ASSERT_TRUE(db.LookupGlobalProperty(tmp, *manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseInternal8)); - ASSERT_EQ(longProperty, tmp); + ASSERT_TRUE(db.LookupGlobalProperty(s, *manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseInternal8)); + ASSERT_EQ(longProperty, s); - tmp.clear(); - ASSERT_TRUE(db.LookupGlobalProperty(tmp, *manager, "some-server", Orthanc::GlobalProperty_DatabaseInternal8)); - ASSERT_EQ(longProperty, tmp); + s.clear(); + ASSERT_TRUE(db.LookupGlobalProperty(s, *manager, "some-server", Orthanc::GlobalProperty_DatabaseInternal8)); + ASSERT_EQ(longProperty, s); } - { - manager->StartTransaction(TransactionType_ReadWrite); - db.DeleteResource(*output, *manager, p1); - db.DeleteResource(*output, *manager, p3); - manager->CommitTransaction(); - } - - for (size_t level = 0; level < 4; level++) { for (size_t attachmentLevel = 0; attachmentLevel < 4; attachmentLevel++)
--- a/Framework/Plugins/MessagesToolbox.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/Plugins/MessagesToolbox.h Tue Aug 12 15:27:09 2025 +0200 @@ -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_AUDIT_LOGS 0 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 8) @@ -66,6 +67,15 @@ # endif #endif + +#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) +# if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 9) +# undef ORTHANC_PLUGINS_HAS_AUDIT_LOGS +# define ORTHANC_PLUGINS_HAS_AUDIT_LOGS 1 +# endif +#endif + + #include <Enumerations.h>
--- a/Framework/PostgreSQL/PostgreSQLDatabase.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -63,7 +63,7 @@ { if (pg_ != NULL) { - LOG(INFO) << "Closing connection to PostgreSQL"; + LOG(TRACE) << "Closing connection to PostgreSQL"; PQfinish(reinterpret_cast<PGconn*>(pg_)); pg_ = NULL; }
--- a/Framework/PostgreSQL/PostgreSQLResult.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLResult.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -162,6 +162,13 @@ return htobe64(*reinterpret_cast<int64_t*>(v)); } + int64_t PostgreSQLResult::GetTimestamp(unsigned int column) const + { + CheckColumn(column, TIMESTAMPOID); + assert(PQfsize(reinterpret_cast<PGresult*>(result_), column) == 8); + char *v = PQgetvalue(reinterpret_cast<PGresult*>(result_), position_, column); + return htobe64(*reinterpret_cast<int64_t*>(v)); + } std::string PostgreSQLResult::GetString(unsigned int column) const { @@ -287,6 +294,9 @@ case VOIDOID: return NULL; + case TIMESTAMPOID: + return new Integer64Value(GetTimestamp(column)); + default: throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); }
--- a/Framework/PostgreSQL/PostgreSQLResult.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Framework/PostgreSQL/PostgreSQLResult.h Tue Aug 12 15:27:09 2025 +0200 @@ -72,6 +72,8 @@ int64_t GetInteger64(unsigned int column) const; + int64_t GetTimestamp(unsigned int column) const; + std::string GetString(unsigned int column) const; void GetBinaryString(std::string& target,
--- a/MySQL/Plugins/IndexPlugin.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/MySQL/Plugins/IndexPlugin.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -86,8 +86,11 @@ OrthancDatabases::MySQLParameters parameters(mysql, configuration); OrthancDatabases::IndexBackend::Register( - new OrthancDatabases::MySQLIndex(context, parameters, readOnly), countConnections, - parameters.GetMaxConnectionRetries(), housekeepingDelaySeconds); + new OrthancDatabases::MySQLIndex(context, parameters, readOnly), + countConnections, + false /* useDynamicConnectionPool */, + parameters.GetMaxConnectionRetries(), + housekeepingDelaySeconds); } catch (Orthanc::OrthancException& e) {
--- a/MySQL/Plugins/MySQLIndex.h Fri Aug 08 17:36:10 2025 +0200 +++ b/MySQL/Plugins/MySQLIndex.h Tue Aug 12 15:27:09 2025 +0200 @@ -76,6 +76,11 @@ return false; } + virtual bool HasAuditLogs() const ORTHANC_OVERRIDE + { + return false; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type)
--- a/Odbc/Plugins/IndexPlugin.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/Odbc/Plugins/IndexPlugin.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -130,7 +130,11 @@ index->SetMaxConnectionRetries(maxConnectionRetries); index->SetConnectionRetryInterval(connectionRetryInterval); - OrthancDatabases::IndexBackend::Register(index.release(), countConnections, maxConnectionRetries, housekeepingDelaySeconds); + OrthancDatabases::IndexBackend::Register(index.release(), + countConnections, + false /* useDynamicConnectionPool */, + maxConnectionRetries, + housekeepingDelaySeconds); } catch (Orthanc::OrthancException& e) {
--- a/Odbc/Plugins/OdbcIndex.h Fri Aug 08 17:36:10 2025 +0200 +++ b/Odbc/Plugins/OdbcIndex.h Tue Aug 12 15:27:09 2025 +0200 @@ -88,6 +88,11 @@ return false; } + virtual bool HasAuditLogs() const ORTHANC_OVERRIDE + { + return false; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type) ORTHANC_OVERRIDE;
--- a/PostgreSQL/CMakeLists.txt Fri Aug 08 17:36:10 2025 +0200 +++ b/PostgreSQL/CMakeLists.txt Tue Aug 12 15:27:09 2025 +0200 @@ -95,6 +95,7 @@ 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 )
--- a/PostgreSQL/NEWS Fri Aug 08 17:36:10 2025 +0200 +++ b/PostgreSQL/NEWS Tue Aug 12 15:27:09 2025 +0200 @@ -1,6 +1,36 @@ Pending changes in the mainline =============================== +DB schema revision: 5 +Minimum plugin SDK (for build): 1.12.5 +Optimal plugin SDK (for build): 1.12.5 +Minimum Orthanc runtime: 1.12.5 +Optimal Orthanc runtime: 1.12.6 + +Minimal Postgresql Server version: 9 +Optimal Postgresql Server version: 11+ + +* New configuration "UseDynamicConnectionPool" to dynamically create/release + connections to the PostgreSQL database. + When this option is disabled (default), the connections are created once + at the start of the plugin and are kept alive for the whole execution. + Using the dynamic mode enables cleanup of some temporary tables that + could otherwise accumulate dead rows. + When using a dynamic pool, connections are released either after 60 seconds + of being idle or one hour after their creation. +* New metrics "orthanc_index_active_connections" showing the current number + of active connections. +* Added support for AuditLogs. The PostgreSQL plugin is listening to audit logs + and is storing them in the SQL Database. + They can be browsed through the API route /plugins/postgresql/audit-logs + +Maintenance: +* Optimized the CreateInstance SQL query. +* Optimized UpdateStatistics and DeleteResource as described in + https://discourse.orthanc-server.org/t/increase-in-cpu-usage-of-database-after-update-to-orthanc-1-12-7/6057/6 +* Internals: + - The PatientRecyclingOrder has been refactored and is now stored in + Patient metadata (18: IsProtected, 19: PatientRecyclingOrder)
--- a/PostgreSQL/Plugins/IndexPlugin.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/PostgreSQL/Plugins/IndexPlugin.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -80,11 +80,12 @@ try { const size_t countConnections = postgresql.GetUnsignedIntegerValue("IndexConnectionsCount", 50); + const bool useDynamicConnectionPool = postgresql.GetBooleanValue("UseDynamicConnectionPool", false); const unsigned int housekeepingDelaySeconds = postgresql.GetUnsignedIntegerValue("HousekeepingInterval", 1); OrthancDatabases::PostgreSQLParameters parameters(postgresql); OrthancDatabases::IndexBackend::Register( - new OrthancDatabases::PostgreSQLIndex(context, parameters, readOnly), countConnections, + new OrthancDatabases::PostgreSQLIndex(context, parameters, readOnly), countConnections, useDynamicConnectionPool, parameters.GetMaxConnectionRetries(), housekeepingDelaySeconds); } catch (Orthanc::OrthancException& e)
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -49,7 +49,7 @@ static const GlobalProperty GlobalProperty_HasComputeStatisticsReadOnly = GlobalProperty_DatabaseInternal4; } -#define CURRENT_DB_REVISION 5 +#define CURRENT_DB_REVISION 6 namespace OrthancDatabases { @@ -248,6 +248,19 @@ currentRevision = 5; } + if (currentRevision == 5) + { + LOG(WARNING) << "Upgrading DB schema from revision 5 to revision 6"; + + std::string query; + + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV5_TO_REV6); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + hasAppliedAnUpgrade = true; + currentRevision = 6; + } + if (hasAppliedAnUpgrade) { LOG(WARNING) << "Upgrading DB schema by applying PrepareIndex.sql"; @@ -457,6 +470,30 @@ uncompressedSize = statement.ReadInteger64(5); } + void PostgreSQLIndex::DeleteAttachment(IDatabaseBackendOutput& output, + DatabaseManager& manager, + int64_t id, + int32_t attachment) + { + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "SELECT DeleteAttachment(${id}, ${type})"); + + statement.SetParameterType("id", ValueType_Integer64); + statement.SetParameterType("type", ValueType_Integer32); + + Dictionary args; + args.SetIntegerValue("id", id); + args.SetInteger32Value("type", attachment); + + statement.ExecuteWithoutResult(args); + } + + SignalDeletedFiles(output, manager); + } + + void PostgreSQLIndex::ClearDeletedFiles(DatabaseManager& manager) { { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS @@ -466,7 +503,6 @@ ); statement.ExecuteWithoutResult(); } - } void PostgreSQLIndex::ClearDeletedResources(DatabaseManager& manager) @@ -489,11 +525,11 @@ statement.Execute(); } - } void PostgreSQLIndex::ClearRemainingAncestor(DatabaseManager& manager) { + // not used anymore in PostgreSQL } void PostgreSQLIndex::DeleteResource(IDatabaseBackendOutput& output, @@ -783,6 +819,111 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); } + void PostgreSQLIndex::SetProtectedPatient(DatabaseManager& manager, + int64_t internalId, + bool isProtected) + { + std::unique_ptr<DatabaseManager::CachedStatement> statement; + Dictionary args; + + if (isProtected) + { + statement.reset(new DatabaseManager::CachedStatement( + STATEMENT_FROM_HERE, manager, + "SELECT ProtectPatient(${id})")); + } + else + { + statement.reset(new DatabaseManager::CachedStatement( + STATEMENT_FROM_HERE, manager, + "SELECT UnprotectPatient(${id})")); + } + + statement->SetParameterType("id", ValueType_Integer64); + args.SetIntegerValue("id", internalId); + + statement->Execute(args); + } + + bool PostgreSQLIndex::IsProtectedPatient(DatabaseManager& manager, + int64_t internalId) + { + std::string value; + int64_t revision; + + if (LookupMetadata(value, revision, manager, internalId, 18)) // 18 = IsProtected + { + return value == "true"; + } + + return false; + } + + bool PostgreSQLIndex::SelectPatientToRecycle(int64_t& internalId /*out*/, + DatabaseManager& manager) + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "SELECT r.internalId " + "FROM Resources r " + "JOIN Metadata m ON r.internalId = m.id AND m.type = 19 " // 19 = PatientRecyclingOrder + "WHERE r.resourceType = 0 " + " AND NOT EXISTS " + " (SELECT 1 FROM Metadata m " + " WHERE m.id = r.internalId AND m.type = 18 AND m.value = 'true') " // 18 = IsProtected + " ORDER BY CAST(m.value AS INTEGER) ASC LIMIT 1;"); + + statement.SetReadOnly(true); + statement.Execute(); + + if (statement.IsDone()) + { + return false; + } + else + { + internalId = statement.ReadInteger64(0); + return true; + } + } + + + bool PostgreSQLIndex::SelectPatientToRecycle(int64_t& internalId /*out*/, + DatabaseManager& manager, + int64_t patientIdToAvoid) + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "SELECT r.internalId " + "FROM Resources r " + "JOIN Metadata m ON r.internalId = m.id AND m.type = 19 " // 19 = PatientRecyclingOrder + "WHERE r.resourceType = 0 " + " AND r.internalId != ${id} " + " AND NOT EXISTS " + " (SELECT 1 FROM Metadata m " + " WHERE m.id = r.internalId AND m.type = 18 AND m.value = 'true') " // 18 = IsProtected + " ORDER BY CAST(m.value AS INTEGER) ASC LIMIT 1;"); + + statement.SetReadOnly(true); + statement.SetParameterType("id", ValueType_Integer64); + + Dictionary args; + args.SetIntegerValue("id", patientIdToAvoid); + + statement.Execute(args); + + if (statement.IsDone()) + { + return false; + } + else + { + internalId = statement.ReadInteger64(0); + return true; + } + } + + bool PostgreSQLIndex::HasPerformDbHousekeeping() { return !IsReadOnly(); // Don't start HK on ReadOnly databases !
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h Fri Aug 08 17:36:10 2025 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.h Tue Aug 12 15:27:09 2025 +0200 @@ -87,6 +87,11 @@ return true; } + virtual bool HasAuditLogs() const ORTHANC_OVERRIDE + { + return true; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type) ORTHANC_OVERRIDE; @@ -95,6 +100,11 @@ DatabaseManager& manager, int64_t id) ORTHANC_OVERRIDE; + virtual void DeleteAttachment(IDatabaseBackendOutput& output, + DatabaseManager& manager, + int64_t id, + int32_t attachment) ORTHANC_OVERRIDE; + virtual void SetResourcesContent(DatabaseManager& manager, uint32_t countIdentifierTags, const OrthancPluginResourcesContentTags* identifierTags, @@ -127,9 +137,24 @@ virtual int64_t GetLastChangeIndex(DatabaseManager& manager) ORTHANC_OVERRIDE; + // This is now obsolete virtual void TagMostRecentPatient(DatabaseManager& manager, int64_t patient) ORTHANC_OVERRIDE; + virtual void SetProtectedPatient(DatabaseManager& manager, + int64_t internalId, + bool isProtected) ORTHANC_OVERRIDE; + + virtual bool IsProtectedPatient(DatabaseManager& manager, + int64_t internalId) ORTHANC_OVERRIDE; + + virtual bool SelectPatientToRecycle(int64_t& internalId /*out*/, + DatabaseManager& manager) ORTHANC_OVERRIDE; + + virtual bool SelectPatientToRecycle(int64_t& internalId /*out*/, + DatabaseManager& manager, + int64_t patientIdToAvoid) ORTHANC_OVERRIDE; + // New primitive since Orthanc 1.12.0 virtual bool HasLabelsSupport() const ORTHANC_OVERRIDE {
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev6ToRev5.sql Tue Aug 12 15:27:09 2025 +0200 @@ -0,0 +1,279 @@ +-- This file contains an SQL procedure to downgrade from schema 6 to Rev5 (version = 6). + + +-- Re-installs the old PatientRecycling +----------- + +CREATE TABLE IF NOT EXISTS PatientRecyclingOrder( + seq BIGSERIAL NOT NULL PRIMARY KEY, + patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + CONSTRAINT UniquePatientId UNIQUE (patientId) + ); + +CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId); + +DROP TRIGGER IF EXISTS PatientAdded ON Resources; + +CREATE OR REPLACE FUNCTION PatientAddedOrUpdated( + IN patient_id BIGINT, + IN is_update BIGINT + ) +RETURNS VOID AS $body$ +BEGIN + DECLARE + newSeq BIGINT; + BEGIN + IF is_update > 0 THEN + -- Note: Protected patients are not listed in this table ! So, they won't be updated + WITH deleted_rows AS ( + DELETE FROM PatientRecyclingOrder + WHERE PatientRecyclingOrder.patientId = patient_id + RETURNING patientId + ) + INSERT INTO PatientRecyclingOrder (patientId) + SELECT patientID FROM deleted_rows + WHERE EXISTS(SELECT 1 FROM deleted_rows); + ELSE + INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, patient_id); + END IF; + END; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION PatientAddedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + -- The "0" corresponds to "OrthancPluginResourceType_Patient" + IF new.resourceType = 0 THEN + PERFORM PatientAddedOrUpdated(new.internalId, 0); + END IF; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS PatientAdded on Resources; +CREATE TRIGGER PatientAdded +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE PatientAddedFunc(); + +DROP FUNCTION IF EXISTS ProtectPatient(patient_id BIGINT); + +DROP FUNCTION IF EXISTS UnprotectPatient; + +-- repopulate the PatientRecyclingOrderTable +WITH UnprotectedPatients AS (SELECT r.internalId + FROM Resources r + RIGHT JOIN Metadata m ON r.internalId = m.id AND m.type = 19 -- 19 = PatientRecyclingOrder + WHERE r.resourceType = 0 + AND NOT EXISTS (SELECT 1 FROM Metadata m + WHERE m.id = r.internalId AND m.type = 18 AND m.value = 'true') -- 18 = IsProtected + ORDER BY CAST(m.value AS INTEGER) ASC) + +INSERT INTO PatientRecyclingOrder (patientId) +SELECT internalId +FROM UnprotectedPatients; + +DROP SEQUENCE IF EXISTS PatientRecyclingOrderSequence; + +-- remove the IsProtected and PatientRecyclingOrder metadata +DELETE FROM Metadata WHERE type IN (18, 19); + +-- Re-installs the old CreateInstance method +----------- + +CREATE OR REPLACE FUNCTION CreateInstance( + IN patient_public_id TEXT, + IN study_public_id TEXT, + IN series_public_id TEXT, + IN instance_public_id TEXT, + OUT is_new_patient BIGINT, + OUT is_new_study BIGINT, + OUT is_new_series BIGINT, + OUT is_new_instance BIGINT, + OUT patient_internal_id BIGINT, + OUT study_internal_id BIGINT, + OUT series_internal_id BIGINT, + OUT instance_internal_id BIGINT) AS $body$ + +BEGIN + is_new_patient := 1; + is_new_study := 1; + is_new_series := 1; + is_new_instance := 1; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL, 0) RETURNING internalid INTO patient_internal_id; + EXCEPTION + WHEN unique_violation THEN + is_new_patient := 0; + SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction + END; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id, 0) RETURNING internalid INTO study_internal_id; + EXCEPTION + WHEN unique_violation THEN + is_new_study := 0; + SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END; + END; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id, 0) RETURNING internalid INTO series_internal_id; + EXCEPTION + WHEN unique_violation THEN + is_new_series := 0; + SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END; + END; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id, 0) RETURNING internalid INTO instance_internal_id; + EXCEPTION + WHEN unique_violation THEN + is_new_instance := 0; + SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction + END; + + IF is_new_instance > 0 THEN + -- Move the patient to the end of the recycling order. + PERFORM PatientAddedOrUpdated(patient_internal_id, 1); + END IF; +END; +$body$ LANGUAGE plpgsql; + +-- Restore the DeleteResource function that has been optimized + +------------------- DeleteResource function ------------------- + +CREATE OR REPLACE FUNCTION DeleteResource( + IN id BIGINT, + OUT remaining_ancestor_resource_type INTEGER, + OUT remaining_anncestor_public_id TEXT) AS $body$ + +DECLARE + deleted_row RECORD; + locked_row RECORD; + +BEGIN + + SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping + + -- note: temporary tables are created at session (connection) level -> they are likely to exist + -- these tables are used by the triggers + CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources( + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL + ); + + RESET client_min_messages; + + -- clear the temporary table in case it has been created earlier in the session + DELETE FROM DeletedResources; + + -- create/clear the DeletedFiles temporary table + PERFORM CreateDeletedFilesTemporaryTable(); + + -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that + -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize + -- that they are deleting the last instance and the parent resources would not be deleted. + -- Locking only the immediate parent is sufficient to prevent from this. + SELECT * INTO locked_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE; + + -- delete the resource itself + DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row; + -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + + + -- If this resource still has siblings, keep track of the remaining parent + -- (a parent that must not be deleted but whose LastUpdate must be updated) + SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id + FROM Resources + WHERE internalId = deleted_row.parentId + AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId); + +END; + +$body$ LANGUAGE plpgsql; + + +-- restore the DeletedResource trigger + +------------------- ResourceDeleted trigger ------------------- +DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; + +-- The following trigger combines 2 triggers from SQLite: +-- ResourceDeleted + ResourceDeletedParentCleaning +CREATE OR REPLACE FUNCTION ResourceDeletedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId; + INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); + + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = old.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS ResourceDeleted on Resources; +CREATE TRIGGER ResourceDeleted +AFTER DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE ResourceDeletedFunc(); + + +-- remove the new DeleteAttachment function + +DROP FUNCTION IF EXISTS DeleteAttachment; + +-- Restore the ON DELETE CASCADE on the Resources.parentId +-- Drop the existing foreign key constraint and add a new one without ON DELETE CASCADE in a single command +ALTER TABLE Resources +DROP CONSTRAINT IF EXISTS resources_parentid_fkey, +ADD CONSTRAINT resources_parentid_fkey FOREIGN KEY (parentId) REFERENCES Resources(internalId) ON DELETE CASCADE; + + +-- Remove the AuditLogs table +----------- + +DROP INDEX IF EXISTS AuditLogsUserId; +DROP INDEX IF EXISTS AuditLogsResourceId; +DROP INDEX IF EXISTS AuditLogsAction; +DROP INDEX IF EXISTS AuditLogsSourcePlugin; +DROP TABLE IF EXISTS AuditLogs; + +-- Remove the InvlalidChildCountsId index +DROP INDEX IF EXISTS InvlalidChildCountsId; + +-- Restore the previous UpdateSingleStatistics function +CREATE OR REPLACE FUNCTION UpdateSingleStatistic( + IN statistics_key INTEGER, + OUT new_value BIGINT +) AS $body$ +BEGIN + + -- Delete the current changes, sum them and update the GlobalIntegers row. + -- New rows can be added in the meantime, they won't be deleted or summed. + WITH deleted_rows AS ( + DELETE FROM GlobalIntegersChanges + WHERE GlobalIntegersChanges.key = statistics_key + RETURNING value + ) + UPDATE GlobalIntegers + SET value = value + ( + SELECT COALESCE(SUM(value), 0) + FROM deleted_rows + ) + WHERE GlobalIntegers.key = statistics_key + RETURNING value INTO new_value; + +END; +$body$ LANGUAGE plpgsql; + + +---------- + +-- 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, 11); +INSERT INTO GlobalProperties VALUES (4, 5); -- GlobalProperty_DatabasePatchLevel
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Fri Aug 08 17:36:10 2025 +0200 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Tue Aug 12 15:27:09 2025 +0200 @@ -14,7 +14,8 @@ internalId BIGSERIAL NOT NULL PRIMARY KEY, resourceType INTEGER NOT NULL, publicId VARCHAR(64) NOT NULL, - parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + -- parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + parentId BIGINT REFERENCES Resources(internalId), childCount INTEGER, CONSTRAINT UniquePublicId UNIQUE (publicId) ); @@ -77,12 +78,6 @@ date VARCHAR(64) ); -CREATE TABLE IF NOT EXISTS PatientRecyclingOrder( - seq BIGSERIAL NOT NULL PRIMARY KEY, - patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, - CONSTRAINT UniquePatientId UNIQUE (patientId) - ); - CREATE TABLE IF NOT EXISTS Labels( id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, label TEXT, @@ -127,7 +122,6 @@ CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId); CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType); -CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId); CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id); CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id); @@ -159,86 +153,24 @@ END $body$; -------------------- PatientAdded trigger & PatientRecyclingOrder ------------------- -DROP TRIGGER IF EXISTS PatientAdded ON Resources; - +--------------------- PatientRecyclingOrder ------------------- +-- from rev 99, we always maintain a PatientRecyclingOrder metadata, no matter if the patient is protected or not CREATE OR REPLACE FUNCTION PatientAddedOrUpdated( - IN patient_id BIGINT, - IN is_update BIGINT + IN patient_id BIGINT ) RETURNS VOID AS $body$ BEGIN DECLARE newSeq BIGINT; BEGIN - IF is_update > 0 THEN - -- Note: Protected patients are not listed in this table ! So, they won't be updated - WITH deleted_rows AS ( - DELETE FROM PatientRecyclingOrder - WHERE PatientRecyclingOrder.patientId = patient_id - RETURNING patientId - ) - INSERT INTO PatientRecyclingOrder (patientId) - SELECT patientID FROM deleted_rows - WHERE EXISTS(SELECT 1 FROM deleted_rows); - ELSE - INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, patient_id); - END IF; + INSERT INTO Metadata (id, type, value, revision) + VALUES (patient_id, 19, nextval('PatientRecyclingOrderSequence')::TEXT, 0) + ON CONFLICT (id, type) + DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision; END; END; $body$ LANGUAGE plpgsql; -CREATE OR REPLACE FUNCTION PatientAddedFunc() -RETURNS TRIGGER AS $body$ -BEGIN - -- The "0" corresponds to "OrthancPluginResourceType_Patient" - IF new.resourceType = 0 THEN - PERFORM PatientAddedOrUpdated(new.internalId, 0); - END IF; - RETURN NULL; -END; -$body$ LANGUAGE plpgsql; - -DROP TRIGGER IF EXISTS PatientAdded on Resources; -CREATE TRIGGER PatientAdded -AFTER INSERT ON Resources -FOR EACH ROW -EXECUTE PROCEDURE PatientAddedFunc(); - --- initial population of -SELECT setval('patientrecyclingorder_seq_seq', MAX(seq)) FROM PatientRecyclingOrder; -DELETE FROM GlobalIntegers WHERE key = 7; - -- UPDATE GlobalIntegers SET value = value + 1 WHERE key = 7 RETURNING value INTO newSeq; - --- INSERT INTO GlobalIntegers --- SELECT 7, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM PatientRecyclingOrder --- ON CONFLICT DO NOTHING; - - -------------------- ResourceDeleted trigger ------------------- -DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; - --- The following trigger combines 2 triggers from SQLite: --- ResourceDeleted + ResourceDeletedParentCleaning -CREATE OR REPLACE FUNCTION ResourceDeletedFunc() -RETURNS TRIGGER AS $body$ -BEGIN - -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId; - INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); - - -- If this resource is the latest child, delete the parent - DELETE FROM Resources WHERE internalId = old.parentId - AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); - RETURN NULL; -END; -$body$ LANGUAGE plpgsql; - -DROP TRIGGER IF EXISTS ResourceDeleted on Resources; -CREATE TRIGGER ResourceDeleted -AFTER DELETE ON Resources -FOR EACH ROW -EXECUTE PROCEDURE ResourceDeletedFunc(); - ------------------- DeleteResource function ------------------- @@ -248,15 +180,20 @@ OUT remaining_anncestor_public_id TEXT) AS $body$ DECLARE - deleted_row RECORD; - locked_row RECORD; + deleted_resource_row RECORD; + deleted_parent_row RECORD; + deleted_grand_parent_row RECORD; + deleted_grand_grand_parent_row RECORD; + + locked_parent_row RECORD; + locked_resource_row RECORD; BEGIN SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping - - -- note: temporary tables are created at session (connection) level -> they are likely to exist - -- these tables are used by the triggers + + -- note: temporary tables are created at connection level -> they are likely to exist. + -- These tables are used by the triggers CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources( resourceType INTEGER NOT NULL, publicId VARCHAR(64) NOT NULL @@ -264,28 +201,104 @@ RESET client_min_messages; - -- clear the temporary table in case it has been created earlier in the session + -- clear the temporary table in case it has been created earlier in the connection DELETE FROM DeletedResources; - + -- create/clear the DeletedFiles temporary table PERFORM CreateDeletedFilesTemporaryTable(); + -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize -- that they are deleting the last instance and the parent resources would not be deleted. -- Locking only the immediate parent is sufficient to prevent from this. - SELECT * INTO locked_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE; + SELECT * INTO locked_parent_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE; + + -- Before deleting the resource itself, we lock it to retrieve the resourceType and to make sure not 2 connections try to + -- delete it at the same time + SELECT * INTO locked_resource_row FROM resources WHERE internalid = id FOR UPDATE; + + -- before delete the resource itself, we must delete its grand-grand-children, the grand-children and its children no to violate + -- the parentId referencing an existing primary key constrain. This is actually implementing the ON DELETE CASCADE that was on the parentId in previous revisions. + + -- If this resource has grand-grand-children, delete them + if locked_resource_row.resourceType < 1 THEN + WITH grand_grand_children_to_delete AS (SELECT grandGrandChildLevel.internalId, grandGrandChildLevel.resourceType, grandGrandChildLevel.publicId + FROM Resources childLevel + INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId + INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId + WHERE childLevel.parentId = id), + + deleted_grand_grand_children_rows AS (DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM grand_grand_children_to_delete) + RETURNING resourceType, publicId) + + INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_grand_grand_children_rows; + END IF; + + -- If this resource has grand-children, delete them + if locked_resource_row.resourceType < 2 THEN + WITH grand_children_to_delete AS (SELECT grandChildLevel.internalId, grandChildLevel.resourceType, grandChildLevel.publicId + FROM Resources childLevel + INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId + WHERE childLevel.parentId = id), + + deleted_grand_children_rows AS (DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM grand_children_to_delete) + RETURNING resourceType, publicId) + + INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_grand_children_rows; + END IF; + + -- If this resource has children, delete them + if locked_resource_row.resourceType < 3 THEN + WITH deleted_children AS (DELETE FROM Resources + WHERE parentId = id + RETURNING resourceType, publicId) + INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_children; + END IF; + -- delete the resource itself - DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row; - -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + + DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_resource_row; + -- keep track of the deleted resources for C++ code + INSERT INTO DeletedResources VALUES (deleted_resource_row.resourceType, deleted_resource_row.publicId); + -- If this resource still has siblings, keep track of the remaining parent -- (a parent that must not be deleted but whose LastUpdate must be updated) SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id FROM Resources - WHERE internalId = deleted_row.parentId - AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId); + WHERE internalId = deleted_resource_row.parentId + AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId); + + IF deleted_resource_row.resourceType > 0 THEN + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = deleted_resource_row.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId) + RETURNING * INTO deleted_parent_row; + IF FOUND THEN + INSERT INTO DeletedResources VALUES (deleted_parent_row.resourceType, deleted_parent_row.publicId); + + IF deleted_parent_row.resourceType > 0 THEN + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = deleted_parent_row.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_parent_row.parentId) + RETURNING * INTO deleted_grand_parent_row; + IF FOUND THEN + INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId); + + IF deleted_grand_parent_row.resourceType > 0 THEN + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = deleted_grand_parent_row.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_grand_parent_row.parentId) + RETURNING * INTO deleted_grand_parent_row; + IF FOUND THEN + INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId); + END IF; + END IF; + END IF; + END IF; + END IF; + END IF; END; @@ -296,9 +309,9 @@ BEGIN - SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping - - -- note: temporary tables are created at session (connection) level -> they are likely to exist + SET client_min_messages = warning; -- suppress NOTICE: relation "DeletedFiles" already exists, skipping + + -- note: temporary tables created at connection level -> they are likely to exist CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles( uuid VARCHAR(64) NOT NULL, fileType INTEGER, @@ -313,13 +326,14 @@ RESET client_min_messages; - -- clear the temporary table in case it has been created earlier in the session + -- clear the temporary table in case it has been created earlier in the connection DELETE FROM DeletedFiles; END; $body$ LANGUAGE plpgsql; - +-- Keep track of deleted files such that the C++ code knows which files have been deleted. +-- Attached files are deleted by cascade when the related resource is deleted. CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc() RETURNS TRIGGER AS $body$ BEGIN @@ -339,6 +353,19 @@ EXECUTE PROCEDURE AttachedFileDeletedFunc(); +CREATE OR REPLACE FUNCTION DeleteAttachment( + IN resource_id BIGINT, + IN file_type INTEGER) +RETURNS VOID AS $body$ +BEGIN + -- create/clear the DeletedFiles temporary table + PERFORM CreateDeletedFilesTemporaryTable(); + + DELETE FROM AttachedFiles WHERE id = resource_id AND fileType = file_type; +END; +$body$ LANGUAGE plpgsql; + + ------------------- Fast Statistics ------------------- -- initial population of GlobalIntegers if not already there @@ -384,9 +411,15 @@ -- Delete the current changes, sum them and update the GlobalIntegers row. -- New rows can be added in the meantime, they won't be deleted or summed. - WITH deleted_rows AS ( + WITH rows_to_delete AS ( + SELECT ctid + FROM GlobalIntegersChanges + WHERE GlobalIntegersChanges.key = statistics_key + LIMIT 10000 -- by default, the UpdateSingleStatistics is called every seconds -> we should never get more than 10000 entries to compute so this is mainly useful to catch up with long standing entries from previous plugins version without the Housekeeping thread (see https://discourse.orthanc-server.org/t/increase-in-cpu-usage-of-database-after-update-to-orthanc-1-12-7/6057/6) + ), + deleted_rows AS ( DELETE FROM GlobalIntegersChanges - WHERE GlobalIntegersChanges.key = statistics_key + WHERE GlobalIntegersChanges.ctid IN (SELECT ctid FROM rows_to_delete) RETURNING value ) UPDATE GlobalIntegers @@ -538,50 +571,82 @@ OUT instance_internal_id BIGINT) AS $body$ BEGIN + -- Assume the parent series already exists to minimize exceptions. + -- Most of the instances are not the first of their series - especially when we need high performances. + is_new_patient := 1; is_new_study := 1; is_new_series := 1; is_new_instance := 1; - BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL, 0) RETURNING internalid INTO patient_internal_id; - EXCEPTION - WHEN unique_violation THEN - is_new_patient := 0; - SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction - END; + -- First, check if the series already exists + SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id; + + IF series_internal_id IS NOT NULL THEN + -- RAISE NOTICE 'series-found %', series_internal_id; + is_new_patient := 0; + is_new_study := 0; + is_new_series := 0; - BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id, 0) RETURNING internalid INTO study_internal_id; - EXCEPTION - WHEN unique_violation THEN - is_new_study := 0; - SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END; - END; + -- If the series exists, insert the instance directly + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id, 0) RETURNING internalid INTO instance_internal_id; + EXCEPTION + WHEN unique_violation THEN + is_new_instance := 0; + SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id; + END; + + SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id; + SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id; + + ELSE + -- RAISE NOTICE 'series-not-found'; - BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id, 0) RETURNING internalid INTO series_internal_id; - EXCEPTION - WHEN unique_violation THEN - is_new_series := 0; - SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction END; - END; + -- If the series does not exist, execute the "full" steps + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL, 0) RETURNING internalid INTO patient_internal_id; + EXCEPTION + WHEN unique_violation THEN + is_new_patient := 0; + SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id; + END; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id, 0) RETURNING internalid INTO study_internal_id; + EXCEPTION + WHEN unique_violation THEN + is_new_study := 0; + SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id; + END; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id, 0) RETURNING internalid INTO series_internal_id; + EXCEPTION + WHEN unique_violation THEN + is_new_series := 0; + SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id; + END; + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id, 0) RETURNING internalid INTO instance_internal_id; + EXCEPTION + WHEN unique_violation THEN + is_new_instance := 0; + SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id; + END; - BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id, 0) RETURNING internalid INTO instance_internal_id; - EXCEPTION - WHEN unique_violation THEN - is_new_instance := 0; - SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction - END; + END IF; + - IF is_new_instance > 0 THEN - -- Move the patient to the end of the recycling order. - PERFORM PatientAddedOrUpdated(patient_internal_id, 1); - END IF; + IF is_new_instance > 0 THEN + -- Move the patient to the end of the recycling order. + PERFORM PatientAddedOrUpdated(patient_internal_id); + END IF; END; $body$ LANGUAGE plpgsql; + -- function to compute a statistic in a ReadOnly transaction CREATE OR REPLACE FUNCTION ComputeStatisticsReadOnly( IN statistics_key INTEGER, @@ -642,6 +707,8 @@ id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, updatedAt TIMESTAMP DEFAULT NOW()); +-- note: an index has been added in rev6 + -- Updates the Resources.childCount column with the delta that have not been committed yet. -- A thread will call this function at regular interval to update all pending values. CREATE OR REPLACE FUNCTION UpdateInvalidChildCounts( @@ -656,7 +723,7 @@ SELECT ARRAY(SELECT internalId FROM Resources WHERE internalId IN (SELECT DISTINCT id FROM InvalidChildCounts) - FOR UPDATE) + FOR UPDATE SKIP LOCKED) INTO locked_resources_ids; -- New rows can be added in the meantime, they won't be taken into account this time. @@ -723,26 +790,71 @@ -- new in 1.12.8 (rev 5) -CREATE TABLE KeyValueStores( +CREATE TABLE IF NOT EXISTS KeyValueStores( storeId TEXT NOT NULL, key TEXT NOT NULL, value BYTEA NOT NULL, PRIMARY KEY(storeId, key) -- Prevents duplicates ); -CREATE TABLE Queues ( +CREATE TABLE IF NOT EXISTS Queues ( id BIGSERIAL NOT NULL PRIMARY KEY, queueId TEXT NOT NULL, value BYTEA NOT NULL ); -CREATE INDEX QueuesIndex ON Queues (queueId, id); +CREATE INDEX IF NOT EXISTS QueuesIndex ON Queues (queueId, id); + +-- new in rev 6 + +CREATE SEQUENCE IF NOT EXISTS PatientRecyclingOrderSequence INCREMENT 1 START 1; + +CREATE OR REPLACE FUNCTION ProtectPatient(patient_id BIGINT) +RETURNS VOID AS $$ +BEGIN + INSERT INTO Metadata (id, type, value, revision) -- 18 = IsProtected + VALUES (patient_id, 18, 'true', 0) + ON CONFLICT (id, type) + DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision; +END; +$$ LANGUAGE plpgsql; + +-- remove IsProtected and update PatientRecyclingOrder +CREATE OR REPLACE FUNCTION UnprotectPatient(patient_id BIGINT) +RETURNS VOID AS $$ +BEGIN + DELETE FROM Metadata WHERE id = patient_id AND type = 18; -- 18 = IsProtected + + INSERT INTO Metadata (id, type, value, revision) + VALUES (patient_id, 19, nextval('PatientRecyclingOrderSequence')::TEXT, 0) + ON CONFLICT (id, type) + DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision; +END; +$$ LANGUAGE plpgsql; + +CREATE TABLE IF NOT EXISTS AuditLogs ( + ts TIMESTAMP DEFAULT NOW(), + sourcePlugin TEXT NOT NULL, + userId TEXT NOT NULL, + resourceType INTEGER NOT NULL, + resourceId VARCHAR(64) NOT NULL, + action TEXT NOT NULL, + logData BYTEA +); + +CREATE INDEX IF NOT EXISTS AuditLogsUserId ON AuditLogs (userId); +CREATE INDEX IF NOT EXISTS AuditLogsResourceId ON AuditLogs (resourceId); +CREATE INDEX IF NOT EXISTS AuditLogsAction ON AuditLogs (action); +CREATE INDEX IF NOT EXISTS AuditLogsSourcePlugin ON AuditLogs (sourcePlugin); + +CREATE INDEX IF NOT EXISTS InvalidChildCountsId ON InvalidChildCounts (id); -- see https://discourse.orthanc-server.org/t/increase-in-cpu-usage-of-database-after-update-to-orthanc-1-12-7/6057/6 + -- 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, 5); -- GlobalProperty_DatabasePatchLevel +INSERT INTO GlobalProperties VALUES (4, 6); -- 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/Rev5ToRev6.sql Tue Aug 12 15:27:09 2025 +0200 @@ -0,0 +1,59 @@ +CREATE SEQUENCE IF NOT EXISTS PatientRecyclingOrderSequence INCREMENT 1 START 1; + +-- the protection mechanisms changed in rev 499. We now use a metadata (18: IsProtected) +-- while, in the past, patients where protected by not appearing in the PatientRecyclingOrder + +-- Step 1: Identify all patients that are not in PatientRecyclingOrder (those are the protected patients) +-- The "0" corresponds to "OrthancPluginResourceType_Patient" +WITH ProtectedPatients AS ( + SELECT r.internalId AS internalId + FROM Resources r + LEFT JOIN PatientRecyclingOrder pro ON r.internalId = pro.patientId + WHERE r.resourceType = 0 + AND pro.patientId IS NULL +) +, UnprotectedPatients AS ( + SELECT patientId AS internalId + FROM PatientRecyclingOrder + ORDER BY seq ASC +) + +-- Step 2: Prepare the data for both metadata types +, MetadataToInsert AS ( + -- mark protected patient in 18: IsProtected + SELECT internalId, 18 AS metadataType, 'true' AS metadataValue + FROM ProtectedPatients + + UNION ALL + + -- copy previous recycling order in 19: RecyclingOrder + SELECT internalId, 19 AS metadataType, nextval('PatientRecyclingOrderSequence')::TEXT AS metadataValue + FROM UnprotectedPatients +) + +-- Step 3: Insert the Metadata (since the metadata are new, there should not be any conflicts) +INSERT INTO Metadata (id, type, value, revision) +SELECT internalId, metadataType, metadataValue, 0 +FROM MetadataToInsert +ON CONFLICT (id, type) +DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision; + +-- The PatientRecyclingOrder table can now be removed + +DROP TABLE PatientRecyclingOrder; + +DROP TRIGGER IF EXISTS PatientAdded on Resources; +DROP FUNCTION IF EXISTS PatientAddedFunc; +DROP FUNCTION IF EXISTS PatientAddedOrUpdated; + +-- The DeletedResources trigger is not used anymore + +DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; +DROP FUNCTION IF EXISTS ResourceDeletedFunc(); + +-- The ON DELETE CASCADE on the Resources.parentId has been removed since this is now implemented +-- in the DeleteResource function +-- Drop the existing foreign key constraint and add a new one without ON DELETE CASCADE in a single command +ALTER TABLE Resources +DROP CONSTRAINT IF EXISTS resources_parentid_fkey, +ADD CONSTRAINT resources_parentid_fkey FOREIGN KEY (parentId) REFERENCES Resources(internalId); \ No newline at end of file
--- a/Resources/CMake/DatabasesPluginConfiguration.cmake Fri Aug 08 17:36:10 2025 +0200 +++ b/Resources/CMake/DatabasesPluginConfiguration.cmake Tue Aug 12 15:27:09 2025 +0200 @@ -113,10 +113,12 @@ list(APPEND DATABASES_SOURCES ${ORTHANC_CORE_SOURCES} + ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/BaseIndexConnectionsPool.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/DatabaseBackendAdapterV2.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/DatabaseBackendAdapterV3.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/DatabaseBackendAdapterV4.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/DatabaseConstraint.cpp + ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/DynamicIndexConnectionsPool.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/ISqlLookupFormatter.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/IndexBackend.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/IndexConnectionsPool.cpp
--- a/SQLite/Plugins/IndexPlugin.cpp Fri Aug 08 17:36:10 2025 +0200 +++ b/SQLite/Plugins/IndexPlugin.cpp Tue Aug 12 15:27:09 2025 +0200 @@ -75,6 +75,7 @@ OrthancDatabases::IndexBackend::Register( new OrthancDatabases::SQLiteIndex(context, "index.db"), // TODO parameter 1 /* only 1 connection is possible with SQLite */, + false /* useDynamicConnectionPool */, 0 /* no collision is possible, as SQLite has a global lock */, 0 /* housekeeping delay, unused for SQLite */); }
--- a/SQLite/Plugins/SQLiteIndex.h Fri Aug 08 17:36:10 2025 +0200 +++ b/SQLite/Plugins/SQLiteIndex.h Tue Aug 12 15:27:09 2025 +0200 @@ -70,6 +70,11 @@ return true; } + virtual bool HasAuditLogs() const ORTHANC_OVERRIDE + { + return false; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type) ORTHANC_OVERRIDE;