Mercurial > hg > orthanc-databases
changeset 607:3d853c8f5a9e
integration find-refactoring->mainline
| author | Sebastien Jodogne <s.jodogne@gmail.com> |
|---|---|
| date | Mon, 16 Dec 2024 16:36:12 +0100 |
| parents | 987460ae14c9 (current diff) d4c373c74c0b (diff) |
| children | 591ce1e2d367 |
| files | |
| diffstat | 67 files changed, 3708 insertions(+), 502 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Common/DatabaseManager.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Common/DatabaseManager.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -79,9 +79,9 @@ } - IPrecompiledStatement* DatabaseManager::LookupCachedStatement(const StatementLocation& location) const + IPrecompiledStatement* DatabaseManager::LookupCachedStatement(const StatementId& statementId) const { - CachedStatements::const_iterator found = cachedStatements_.find(location); + CachedStatements::const_iterator found = cachedStatements_.find(statementId); if (found == cachedStatements_.end()) { @@ -95,10 +95,10 @@ } - IPrecompiledStatement& DatabaseManager::CacheStatement(const StatementLocation& location, + IPrecompiledStatement& DatabaseManager::CacheStatement(const StatementId& statementId, const Query& query) { - LOG(TRACE) << "Caching statement from " << location.GetFile() << ":" << location.GetLine(); + LOG(TRACE) << "Caching statement from " << statementId.GetFile() << ":" << statementId.GetLine() << "" << statementId.GetDynamicStatement(); std::unique_ptr<IPrecompiledStatement> statement(GetDatabase().Compile(query)); @@ -108,8 +108,8 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); } - assert(cachedStatements_.find(location) == cachedStatements_.end()); - cachedStatements_[location] = statement.release(); + assert(cachedStatements_.find(statementId) == cachedStatements_.end()); + cachedStatements_[statementId] = statement.release(); return *tmp; } @@ -551,13 +551,13 @@ } - DatabaseManager::CachedStatement::CachedStatement(const StatementLocation& location, + DatabaseManager::CachedStatement::CachedStatement(const StatementId& statementId, DatabaseManager& manager, const std::string& sql) : StatementBase(manager), - location_(location) + statementId_(statementId) { - statement_ = GetManager().LookupCachedStatement(location_); + statement_ = GetManager().LookupCachedStatement(statementId_); if (statement_ == NULL) { @@ -566,7 +566,7 @@ else { LOG(TRACE) << "Reusing cached statement from " - << location_.GetFile() << ":" << location_.GetLine(); + << statementId_.GetFile() << ":" << statementId_.GetLine() << " " << statementId_.GetDynamicStatement(); } } @@ -580,7 +580,7 @@ { // Register the newly-created statement assert(statement_ == NULL); - statement_ = &GetManager().CacheStatement(location_, *query); + statement_ = &GetManager().CacheStatement(statementId_, *query); } assert(statement_ != NULL);
--- a/Framework/Common/DatabaseManager.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Common/DatabaseManager.h Mon Dec 16 16:36:12 2024 +0100 @@ -24,7 +24,7 @@ #pragma once #include "IDatabaseFactory.h" -#include "StatementLocation.h" +#include "StatementId.h" #include <Compatibility.h> // For std::unique_ptr<> #include <Enumerations.h> @@ -49,7 +49,7 @@ class DatabaseManager : public boost::noncopyable { private: - typedef std::map<StatementLocation, IPrecompiledStatement*> CachedStatements; + typedef std::map<StatementId, IPrecompiledStatement*> CachedStatements; std::unique_ptr<IDatabaseFactory> factory_; std::unique_ptr<IDatabase> database_; @@ -59,9 +59,9 @@ void CloseIfUnavailable(Orthanc::ErrorCode e); - IPrecompiledStatement* LookupCachedStatement(const StatementLocation& location) const; + IPrecompiledStatement* LookupCachedStatement(const StatementId& statementId) const; - IPrecompiledStatement& CacheStatement(const StatementLocation& location, + IPrecompiledStatement& CacheStatement(const StatementId& statementId, const Query& query); ITransaction& GetTransaction(); @@ -194,6 +194,8 @@ { IResult::Print(stream, GetResult()); } + + virtual void Execute(const Dictionary& parameters) = 0; }; @@ -207,11 +209,11 @@ class CachedStatement : public StatementBase { private: - StatementLocation location_; + StatementId statementId_; IPrecompiledStatement* statement_; public: - CachedStatement(const StatementLocation& location, + CachedStatement(const StatementId& statementId, DatabaseManager& manager, const std::string& sql); @@ -221,7 +223,7 @@ Execute(parameters); } - void Execute(const Dictionary& parameters); + virtual void Execute(const Dictionary& parameters); void ExecuteWithoutResult() { @@ -253,7 +255,7 @@ Execute(parameters); } - void Execute(const Dictionary& parameters); + virtual void Execute(const Dictionary& parameters); void ExecuteWithoutResult() {
--- a/Framework/Common/DatabasesEnumerations.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Common/DatabasesEnumerations.h Mon Dec 16 16:36:12 2024 +0100 @@ -31,6 +31,7 @@ ValueType_BinaryString, ValueType_InputFile, ValueType_Integer64, + ValueType_Integer32, ValueType_Null, ValueType_ResultFile, ValueType_Utf8String
--- a/Framework/Common/Dictionary.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Common/Dictionary.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -25,6 +25,7 @@ #include "BinaryStringValue.h" #include "InputFileValue.h" +#include "Integer32Value.h" #include "Integer64Value.h" #include "NullValue.h" #include "Utf8StringValue.h" @@ -126,7 +127,13 @@ SetValue(key, new Integer64Value(value)); } - + + void Dictionary::SetInteger32Value(const std::string& key, + int32_t value) + { + SetValue(key, new Integer32Value(value)); + } + void Dictionary::SetNullValue(const std::string& key) { SetValue(key, new NullValue);
--- a/Framework/Common/Dictionary.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Common/Dictionary.h Mon Dec 16 16:36:12 2024 +0100 @@ -68,6 +68,9 @@ void SetIntegerValue(const std::string& key, int64_t value); + void SetInteger32Value(const std::string& key, + int32_t value); + void SetNullValue(const std::string& key); const IValue& GetValue(const std::string& key) const;
--- a/Framework/Common/ITransaction.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Common/ITransaction.h Mon Dec 16 16:36:12 2024 +0100 @@ -50,6 +50,8 @@ virtual bool DoesTableExist(const std::string& name) = 0; + virtual bool DoesIndexExist(const std::string& name) = 0; + virtual bool DoesTriggerExist(const std::string& name) = 0; // Only for MySQL virtual void ExecuteMultiLines(const std::string& query) = 0;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Common/Integer32Value.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -0,0 +1,55 @@ +/** + * 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-2024 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2024 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 "Integer32Value.h" + +#include "BinaryStringValue.h" +#include "NullValue.h" +#include "Utf8StringValue.h" + +#include <OrthancException.h> + +#include <boost/lexical_cast.hpp> + +namespace OrthancDatabases +{ + IValue* Integer32Value::Convert(ValueType target) const + { + std::string s = boost::lexical_cast<std::string>(value_); + + switch (target) + { + case ValueType_Null: + return new NullValue; + + case ValueType_BinaryString: + return new BinaryStringValue(s); + + case ValueType_Utf8String: + return new Utf8StringValue(s); + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Common/Integer32Value.h Mon Dec 16 16:36:12 2024 +0100 @@ -0,0 +1,57 @@ +/** + * 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-2024 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2024 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 "IValue.h" + +#include <Compatibility.h> + +#include <stdint.h> + +namespace OrthancDatabases +{ + class Integer32Value : public IValue + { + private: + int32_t value_; + + public: + explicit Integer32Value(int32_t value) : + value_(value) + { + } + + int32_t GetValue() const + { + return value_; + } + + virtual ValueType GetType() const ORTHANC_OVERRIDE + { + return ValueType_Integer32; + } + + virtual IValue* Convert(ValueType target) const ORTHANC_OVERRIDE; + }; +}
--- a/Framework/Common/Query.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Common/Query.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -76,7 +76,7 @@ parameter = parameter.substr(2, parameter.size() - 3); tokens_.push_back(new Token(true, parameter)); - parameters_[parameter] = ValueType_Null; + parameters_[parameter] = ValueType_Utf8String; last = it->second;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Common/StatementId.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -0,0 +1,56 @@ +/** + * 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-2024 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2024 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 "StatementId.h" + +#include <string.h> +#include <Toolbox.h> + +#include <boost/lexical_cast.hpp> + +namespace OrthancDatabases +{ + bool StatementId::operator< (const StatementId& other) const + { + return hash_ < other.hash_; + } + + StatementId::StatementId(const char* file, + int line) : + file_(file), + line_(line) + { + Orthanc::Toolbox::ComputeMD5(hash_, file_ + boost::lexical_cast<std::string>(line_)); + } + + StatementId::StatementId(const char* file, + int line, + const std::string& statement) : + file_(file), + line_(line), + statement_(statement) + { + Orthanc::Toolbox::ComputeMD5(hash_, file_ + boost::lexical_cast<std::string>(line_) + statement_); + } + +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Common/StatementId.h Mon Dec 16 16:36:12 2024 +0100 @@ -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-2024 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2024 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 <string> + +#define STATEMENT_FROM_HERE ::OrthancDatabases::StatementId(__FILE__, __LINE__) +#define STATEMENT_FROM_HERE_DYNAMIC(sql) ::OrthancDatabases::StatementId(__FILE__, __LINE__, sql) + + +namespace OrthancDatabases +{ + class StatementId + { + private: + const char* file_; + int line_; + std::string statement_; + std::string hash_; + + StatementId(); // Forbidden + + public: + StatementId(const char* file, + int line); + + StatementId(const char* file, + int line, + const std::string& statement); + + const char* GetFile() const + { + return file_; + } + + int GetLine() const + { + return line_; + } + + const std::string& GetDynamicStatement() const + { + return statement_; + } + + bool operator< (const StatementId& other) const; + }; +}
--- a/Framework/Common/StatementLocation.cpp Mon Sep 09 15:21:14 2024 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,41 +0,0 @@ -/** - * 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-2024 Orthanc Team SRL, Belgium - * Copyright (C) 2021-2024 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 "StatementLocation.h" - -#include <string.h> - -namespace OrthancDatabases -{ - bool StatementLocation::operator< (const StatementLocation& other) const - { - if (line_ != other.line_) - { - return line_ < other.line_; - } - else - { - return strcmp(file_, other.file_) < 0; - } - } -}
--- a/Framework/Common/StatementLocation.h Mon Sep 09 15:21:14 2024 +0200 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,59 +0,0 @@ -/** - * 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-2024 Orthanc Team SRL, Belgium - * Copyright (C) 2021-2024 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 - -#define STATEMENT_FROM_HERE ::OrthancDatabases::StatementLocation(__FILE__, __LINE__) - - -namespace OrthancDatabases -{ - class StatementLocation - { - private: - const char* file_; - int line_; - - StatementLocation(); // Forbidden - - public: - StatementLocation(const char* file, - int line) : - file_(file), - line_(line) - { - } - - const char* GetFile() const - { - return file_; - } - - int GetLine() const - { - return line_; - } - - bool operator< (const StatementLocation& other) const; - }; -}
--- a/Framework/MySQL/MySQLDatabase.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/MySQL/MySQLDatabase.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -570,6 +570,11 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed"); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed"); + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "An explicit transaction is needed");
--- a/Framework/MySQL/MySQLStatement.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/MySQL/MySQLStatement.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -152,6 +152,10 @@ buffer_.resize(8); break; + case MYSQL_TYPE_NULL: + orthancType_ = ValueType_Null; + break; + case MYSQL_TYPE_STRING: case MYSQL_TYPE_VAR_STRING: case MYSQL_TYPE_BLOB:
--- a/Framework/MySQL/MySQLTransaction.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/MySQL/MySQLTransaction.h Mon Dec 16 16:36:12 2024 +0100 @@ -64,6 +64,11 @@ return db_.DoesTableExist(*this, name); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return false; + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return db_.DoesTriggerExist(*this, name);
--- a/Framework/Odbc/OdbcDatabase.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Odbc/OdbcDatabase.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -84,6 +84,11 @@ return db_.DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return false; + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false; @@ -188,6 +193,11 @@ return db_.DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return false; // note implemented yet + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false;
--- a/Framework/Plugins/DatabaseBackendAdapterV3.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV3.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -1814,7 +1814,8 @@ void DatabaseBackendAdapterV3::Register(IndexBackend* backend, size_t countConnections, - unsigned int maxDatabaseRetries) + unsigned int maxDatabaseRetries, + unsigned int housekeepingDelaySeconds) { std::unique_ptr<IndexBackend> protection(backend); @@ -1905,7 +1906,7 @@ if (OrthancPluginRegisterDatabaseBackendV3( context, ¶ms, sizeof(params), maxDatabaseRetries, - new IndexConnectionsPool(protection.release(), countConnections)) != OrthancPluginErrorCode_Success) + new IndexConnectionsPool(protection.release(), countConnections, housekeepingDelaySeconds)) != OrthancPluginErrorCode_Success) { delete backend; throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "Unable to register the database backend");
--- a/Framework/Plugins/DatabaseBackendAdapterV3.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV3.h Mon Dec 16 16:36:12 2024 +0100 @@ -60,7 +60,8 @@ static void Register(IndexBackend* backend, size_t countConnections, - unsigned int maxDatabaseRetries); + unsigned int maxDatabaseRetries, + unsigned int housekeepingDelaySeconds); static void Finalize(); };
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -27,6 +27,7 @@ # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) #include "IndexConnectionsPool.h" +#include "MessagesToolbox.h" #include <OrthancDatabasePlugin.pb.h> // Include protobuf messages @@ -91,28 +92,6 @@ } - static Orthanc::ResourceType Convert2(Orthanc::DatabasePluginMessages::ResourceType resourceType) - { - switch (resourceType) - { - case Orthanc::DatabasePluginMessages::RESOURCE_PATIENT: - return Orthanc::ResourceType_Patient; - - case Orthanc::DatabasePluginMessages::RESOURCE_STUDY: - return Orthanc::ResourceType_Study; - - case Orthanc::DatabasePluginMessages::RESOURCE_SERIES: - return Orthanc::ResourceType_Series; - - case Orthanc::DatabasePluginMessages::RESOURCE_INSTANCE: - return Orthanc::ResourceType_Instance; - - default: - throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); - } - } - - class Output : public IDatabaseBackendOutput { private: @@ -126,6 +105,10 @@ Orthanc::DatabasePluginMessages::LookupAttachment::Response* lookupAttachment_; Orthanc::DatabasePluginMessages::LookupResources::Response* lookupResources_; +#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1 + Orthanc::DatabasePluginMessages::GetChangesExtended::Response* getChangesExtended_; +#endif + void Clear() { deleteAttachment_ = NULL; @@ -137,6 +120,10 @@ getMainDicomTags_ = NULL; lookupAttachment_ = NULL; lookupResources_ = NULL; + +#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1 + getChangesExtended_ = NULL; +#endif } public: @@ -157,7 +144,15 @@ Clear(); getChanges_ = &getChanges; } - + +#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1 + Output(Orthanc::DatabasePluginMessages::GetChangesExtended::Response& getChangesExtended) + { + Clear(); + getChangesExtended_ = &getChangesExtended; + } +#endif + Output(Orthanc::DatabasePluginMessages::GetExportedResources::Response& getExportedResources) { Clear(); @@ -310,6 +305,12 @@ { change = getChanges_->add_changes(); } +#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1 + else if (getChangesExtended_ != NULL) + { + change = getChangesExtended_->add_changes(); + } +#endif else if (getLastChange_ != NULL) { if (getLastChange_->found()) @@ -439,6 +440,11 @@ response.mutable_get_system_information()->set_has_measure_latency(accessor.GetBackend().HasMeasureLatency()); #endif +#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1 + response.mutable_get_system_information()->set_supports_find(accessor.GetBackend().HasFindSupport()); + response.mutable_get_system_information()->set_has_extended_changes(accessor.GetBackend().HasExtendedChanges()); +#endif + break; } @@ -455,7 +461,7 @@ for (int i = 0; i < request.open().identifier_tags().size(); i++) { const Orthanc::DatabasePluginMessages::Open_Request_IdentifierTag& tag = request.open().identifier_tags(i); - identifierTags.push_back(IdentifierTag(Convert2(tag.level()), + identifierTags.push_back(IdentifierTag(MessagesToolbox::Convert(tag.level()), Orthanc::DicomTag(tag.group(), tag.element()), tag.name())); } @@ -782,7 +788,25 @@ response.mutable_get_changes()->set_done(done); break; } - +#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1 + case Orthanc::DatabasePluginMessages::OPERATION_GET_CHANGES_EXTENDED: + { + Output output(*response.mutable_get_changes_extended()); + + bool done; + std::set<uint32_t> changeTypes; + for (int i = 0; i < request.get_changes_extended().change_type_size(); ++i) + { + changeTypes.insert(request.get_changes_extended().change_type(i)); + } + + backend.GetChangesExtended(output, done, manager, request.get_changes_extended().since(), request.get_changes_extended().to(), changeTypes, request.get_changes_extended().limit()); + + response.mutable_get_changes_extended()->set_done(done); + break; + } +#endif + case Orthanc::DatabasePluginMessages::OPERATION_GET_CHILDREN_INTERNAL_ID: { std::list<int64_t> values; @@ -1297,6 +1321,20 @@ break; } +#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1 + case Orthanc::DatabasePluginMessages::OPERATION_FIND: + { + backend.ExecuteFind(response, manager, request.find()); + break; + } + + case Orthanc::DatabasePluginMessages::OPERATION_COUNT_RESOURCES: + { + backend.ExecuteCount(response, manager, request.find()); + break; + } +#endif + default: LOG(ERROR) << "Not implemented transaction operation from protobuf: " << request.operation(); throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); @@ -1416,9 +1454,10 @@ void DatabaseBackendAdapterV4::Register(IndexBackend* backend, size_t countConnections, - unsigned int maxDatabaseRetries) + unsigned int maxDatabaseRetries, + unsigned int housekeepingDelaySeconds) { - std::unique_ptr<IndexConnectionsPool> pool(new IndexConnectionsPool(backend, countConnections)); + std::unique_ptr<IndexConnectionsPool> pool(new IndexConnectionsPool(backend, countConnections, housekeepingDelaySeconds)); if (isBackendInUse_) {
--- a/Framework/Plugins/DatabaseBackendAdapterV4.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV4.h Mon Dec 16 16:36:12 2024 +0100 @@ -50,7 +50,8 @@ public: static void Register(IndexBackend* backend, size_t countConnections, - unsigned int maxDatabaseRetries); + unsigned int maxDatabaseRetries, + unsigned int housekeepingDelaySeconds); static void Finalize(); };
--- a/Framework/Plugins/DatabaseConstraint.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/DatabaseConstraint.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -32,111 +32,12 @@ #include <OrthancException.h> +#include <boost/lexical_cast.hpp> #include <cassert> namespace OrthancDatabases { - namespace Plugins - { - OrthancPluginResourceType Convert(Orthanc::ResourceType type) - { - switch (type) - { - case Orthanc::ResourceType_Patient: - return OrthancPluginResourceType_Patient; - - case Orthanc::ResourceType_Study: - return OrthancPluginResourceType_Study; - - case Orthanc::ResourceType_Series: - return OrthancPluginResourceType_Series; - - case Orthanc::ResourceType_Instance: - return OrthancPluginResourceType_Instance; - - default: - throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); - } - } - - - Orthanc::ResourceType Convert(OrthancPluginResourceType type) - { - switch (type) - { - case OrthancPluginResourceType_Patient: - return Orthanc::ResourceType_Patient; - - case OrthancPluginResourceType_Study: - return Orthanc::ResourceType_Study; - - case OrthancPluginResourceType_Series: - return Orthanc::ResourceType_Series; - - case OrthancPluginResourceType_Instance: - return Orthanc::ResourceType_Instance; - - default: - throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); - } - } - - -#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 - OrthancPluginConstraintType Convert(ConstraintType constraint) - { - switch (constraint) - { - case ConstraintType_Equal: - return OrthancPluginConstraintType_Equal; - - case ConstraintType_GreaterOrEqual: - return OrthancPluginConstraintType_GreaterOrEqual; - - case ConstraintType_SmallerOrEqual: - return OrthancPluginConstraintType_SmallerOrEqual; - - case ConstraintType_Wildcard: - return OrthancPluginConstraintType_Wildcard; - - case ConstraintType_List: - return OrthancPluginConstraintType_List; - - default: - throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); - } - } -#endif - - -#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 - ConstraintType Convert(OrthancPluginConstraintType constraint) - { - switch (constraint) - { - case OrthancPluginConstraintType_Equal: - return ConstraintType_Equal; - - case OrthancPluginConstraintType_GreaterOrEqual: - return ConstraintType_GreaterOrEqual; - - case OrthancPluginConstraintType_SmallerOrEqual: - return ConstraintType_SmallerOrEqual; - - case OrthancPluginConstraintType_Wildcard: - return ConstraintType_Wildcard; - - case OrthancPluginConstraintType_List: - return ConstraintType_List; - - default: - throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); - } - } -#endif - } - DatabaseConstraint::DatabaseConstraint(Orthanc::ResourceType level, const Orthanc::DicomTag& tag, bool isIdentifier, @@ -162,10 +63,10 @@ #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 DatabaseConstraint::DatabaseConstraint(const OrthancPluginDatabaseConstraint& constraint) : - level_(Plugins::Convert(constraint.level)), + level_(MessagesToolbox::Convert(constraint.level)), tag_(constraint.tagGroup, constraint.tagElement), isIdentifier_(constraint.isIdentifierTag), - constraintType_(Plugins::Convert(constraint.type)), + constraintType_(MessagesToolbox::Convert(constraint.type)), caseSensitive_(constraint.isCaseSensitive), mandatory_(constraint.isMandatory) { @@ -186,6 +87,56 @@ #endif +#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1 + DatabaseConstraint::DatabaseConstraint(const Orthanc::DatabasePluginMessages::DatabaseConstraint& constraint) : + level_(MessagesToolbox::Convert(constraint.level())), + tag_(constraint.tag_group(), constraint.tag_element()), + isIdentifier_(constraint.is_identifier_tag()), + caseSensitive_(constraint.is_case_sensitive()), + mandatory_(constraint.is_mandatory()) + { + switch (constraint.type()) + { + case Orthanc::DatabasePluginMessages::CONSTRAINT_EQUAL: + constraintType_ = ConstraintType_Equal; + break; + + case Orthanc::DatabasePluginMessages::CONSTRAINT_SMALLER_OR_EQUAL: + constraintType_ = ConstraintType_SmallerOrEqual; + break; + + case Orthanc::DatabasePluginMessages::CONSTRAINT_GREATER_OR_EQUAL: + constraintType_ = ConstraintType_GreaterOrEqual; + break; + + case Orthanc::DatabasePluginMessages::CONSTRAINT_WILDCARD: + constraintType_ = ConstraintType_Wildcard; + break; + + case Orthanc::DatabasePluginMessages::CONSTRAINT_LIST: + constraintType_ = ConstraintType_List; + break; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + + if (constraintType_ != ConstraintType_List && + constraint.values().size() != 1) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + + values_.resize(constraint.values().size()); + + for (int i = 0; i < constraint.values().size(); i++) + { + values_[i] = constraint.values(i); + } + } +#endif + + const std::string& DatabaseConstraint::GetValue(size_t index) const { if (index >= values_.size()) @@ -225,13 +176,13 @@ tmpValues[i] = values_[i].c_str(); } - constraint.level = Plugins::Convert(level_); + constraint.level = MessagesToolbox::ConvertToPlainC(level_); constraint.tagGroup = tag_.GetGroup(); constraint.tagElement = tag_.GetElement(); constraint.isIdentifierTag = isIdentifier_; constraint.isCaseSensitive = caseSensitive_; constraint.isMandatory = mandatory_; - constraint.type = Plugins::Convert(constraintType_); + constraint.type = MessagesToolbox::ConvertToPlainC(constraintType_); constraint.valuesCount = values_.size(); constraint.values = (tmpValues.empty() ? NULL : &tmpValues[0]); } @@ -275,4 +226,64 @@ return *constraints_[index]; } } + + + std::string DatabaseConstraints::Format() const + { + std::string s; + + for (size_t i = 0; i < constraints_.size(); i++) + { + assert(constraints_[i] != NULL); + const DatabaseConstraint& constraint = *constraints_[i]; + s += "Constraint " + boost::lexical_cast<std::string>(i) + " at " + EnumerationToString(constraint.GetLevel()) + + ": " + constraint.GetTag().Format(); + + switch (constraint.GetConstraintType()) + { + case ConstraintType_Equal: + s += " == " + constraint.GetSingleValue(); + break; + + case ConstraintType_SmallerOrEqual: + s += " <= " + constraint.GetSingleValue(); + break; + + case ConstraintType_GreaterOrEqual: + s += " >= " + constraint.GetSingleValue(); + break; + + case ConstraintType_Wildcard: + s += " ~~ " + constraint.GetSingleValue(); + break; + + case ConstraintType_List: + { + s += " in [ "; + bool first = true; + for (size_t j = 0; j < constraint.GetValuesCount(); j++) + { + if (first) + { + first = false; + } + else + { + s += ", "; + } + s += constraint.GetValue(j); + } + s += "]"; + break; + } + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + + s += "\n"; + } + + return s; + } }
--- a/Framework/Plugins/DatabaseConstraint.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/DatabaseConstraint.h Mon Dec 16 16:36:12 2024 +0100 @@ -30,16 +30,7 @@ #pragma once -#include <orthanc/OrthancCDatabasePlugin.h> - -#define ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT 0 - -#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) // Macro introduced in 1.3.1 -# if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 5, 2) -# undef ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT -# define ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT 1 -# endif -#endif +#include "MessagesToolbox.h" #include <DicomFormat/DicomMap.h> @@ -47,31 +38,6 @@ namespace OrthancDatabases { - enum ConstraintType - { - ConstraintType_Equal, - ConstraintType_SmallerOrEqual, - ConstraintType_GreaterOrEqual, - ConstraintType_Wildcard, - ConstraintType_List - }; - - namespace Plugins - { - OrthancPluginResourceType Convert(Orthanc::ResourceType type); - - Orthanc::ResourceType Convert(OrthancPluginResourceType type); - -#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 - OrthancPluginConstraintType Convert(ConstraintType constraint); -#endif - -#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 - ConstraintType Convert(OrthancPluginConstraintType constraint); -#endif - } - - class DatabaseConstraint : public boost::noncopyable { private: @@ -96,6 +62,10 @@ explicit DatabaseConstraint(const OrthancPluginDatabaseConstraint& constraint); #endif +#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1 + explicit DatabaseConstraint(const Orthanc::DatabasePluginMessages::DatabaseConstraint& constraint); +#endif + Orthanc::ResourceType GetLevel() const { return level_; @@ -170,5 +140,7 @@ } const DatabaseConstraint& GetConstraint(size_t index) const; + + std::string Format() const; }; }
--- a/Framework/Plugins/IDatabaseBackend.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/IDatabaseBackend.h Mon Dec 16 16:36:12 2024 +0100 @@ -110,6 +110,14 @@ int64_t since, uint32_t limit) = 0; + virtual void GetChangesExtended(IDatabaseBackendOutput& output, + bool& done /*out*/, + DatabaseManager& manager, + int64_t since, + int64_t to, + const std::set<uint32_t>& changeTypes, + uint32_t limit) = 0; + virtual void GetChildrenInternalId(std::list<int64_t>& target /*out*/, DatabaseManager& manager, int64_t id) = 0; @@ -377,6 +385,23 @@ // New in Orthanc 1.12.3 virtual uint64_t MeasureLatency(DatabaseManager& manager) = 0; +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + virtual bool HasFindSupport() const = 0; + virtual bool HasExtendedChanges() const = 0; + // New in Orthanc 1.12.5 + virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) = 0; + + // New in Orthanc 1.12.5 + virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) = 0; +#endif + + virtual bool HasPerformDbHousekeeping() = 0; + + virtual void PerformDbHousekeeping(DatabaseManager& manager) = 0; }; }
--- a/Framework/Plugins/ISqlLookupFormatter.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/ISqlLookupFormatter.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -35,8 +35,9 @@ #include <OrthancException.h> #include <Toolbox.h> +#include <boost/algorithm/string/join.hpp> +#include <boost/lexical_cast.hpp> #include <cassert> -#include <boost/lexical_cast.hpp> #include <list> @@ -64,9 +65,36 @@ } +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + static std::string FormatLevel(const char* prefix, Orthanc::ResourceType level) + { + switch (level) + { + case Orthanc::ResourceType_Patient: + return std::string(prefix) + "patients"; + + case Orthanc::ResourceType_Study: + return std::string(prefix) + "studies"; + + case Orthanc::ResourceType_Series: + return std::string(prefix) + "series"; + + case Orthanc::ResourceType_Instance: + return std::string(prefix) + "instances"; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + } +#endif + + static bool FormatComparison(std::string& target, ISqlLookupFormatter& formatter, - const DatabaseConstraint& constraint, + OrthancDatabases::ConstraintType constraintType, + const std::vector<std::string>& values, + bool isCaseSensitive, + bool isMandatory, size_t index, bool escapeBrackets) { @@ -74,14 +102,14 @@ std::string comparison; - switch (constraint.GetConstraintType()) + switch (constraintType) { case ConstraintType_Equal: case ConstraintType_SmallerOrEqual: case ConstraintType_GreaterOrEqual: { std::string op; - switch (constraint.GetConstraintType()) + switch (constraintType) { case ConstraintType_Equal: op = "="; @@ -99,9 +127,9 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); } - std::string parameter = formatter.GenerateParameter(constraint.GetSingleValue()); + std::string parameter = formatter.GenerateParameter(values[0]); - if (constraint.IsCaseSensitive()) + if (isCaseSensitive) { comparison = tag + ".value " + op + " " + parameter; } @@ -115,16 +143,16 @@ case ConstraintType_List: { - for (size_t i = 0; i < constraint.GetValuesCount(); i++) + for (size_t i = 0; i < values.size(); i++) { if (!comparison.empty()) { comparison += ", "; } - std::string parameter = formatter.GenerateParameter(constraint.GetValue(i)); + std::string parameter = formatter.GenerateParameter(values[i]); - if (constraint.IsCaseSensitive()) + if (isCaseSensitive) { comparison += parameter; } @@ -134,7 +162,7 @@ } } - if (constraint.IsCaseSensitive()) + if (isCaseSensitive) { comparison = tag + ".value IN (" + comparison + ")"; } @@ -148,11 +176,11 @@ case ConstraintType_Wildcard: { - const std::string value = constraint.GetSingleValue(); + const std::string value = values[0]; if (value == "*") { - if (!constraint.IsMandatory()) + if (!isMandatory) { // Universal constraint on an optional tag, ignore it return false; @@ -201,7 +229,7 @@ std::string parameter = formatter.GenerateParameter(escaped); - if (constraint.IsCaseSensitive()) + if (isCaseSensitive) { comparison = (tag + ".value LIKE " + parameter + " " + formatter.FormatWildcardEscape()); @@ -220,7 +248,7 @@ return false; } - if (constraint.IsMandatory()) + if (isMandatory) { target = comparison; } @@ -237,6 +265,91 @@ } +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + static bool FormatComparison(std::string& target, + ISqlLookupFormatter& formatter, + const Orthanc::DatabasePluginMessages::DatabaseMetadataConstraint& constraint, + size_t index, + bool escapeBrackets) + { + std::vector<std::string> values; + OrthancDatabases::ConstraintType constraintType; + switch (constraint.type()) + { + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_EQUAL: + constraintType = OrthancDatabases::ConstraintType_Equal; + break; + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_GREATER_OR_EQUAL: + constraintType = OrthancDatabases::ConstraintType_GreaterOrEqual; + break; + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_LIST: + constraintType = OrthancDatabases::ConstraintType_List; + break; + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_SMALLER_OR_EQUAL: + constraintType = OrthancDatabases::ConstraintType_SmallerOrEqual; + break; + case Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_WILDCARD: + constraintType = OrthancDatabases::ConstraintType_Wildcard; + break; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + + if (constraint.type() == Orthanc::DatabasePluginMessages::ConstraintType::CONSTRAINT_LIST) + { + for (int i = 0; i < constraint.values_size(); ++i) + { + values.push_back(constraint.values(i)); + } + } + else + { + assert(constraint.values_size() == 1); + values.push_back(constraint.values(0)); + } + + return FormatComparison(target, + formatter, + constraintType, + values, + constraint.is_case_sensitive(), + constraint.is_mandatory(), + index, + escapeBrackets); + + } +#endif + + + static bool FormatComparison(std::string& target, + ISqlLookupFormatter& formatter, + const DatabaseConstraint& constraint, + size_t index, + bool escapeBrackets) + { + std::vector<std::string> values; + if (constraint.GetConstraintType() == OrthancDatabases::ConstraintType_List) + { + for (size_t i = 0; i < constraint.GetValuesCount(); ++i) + { + values.push_back(constraint.GetValue(i)); + } + } + else + { + values.push_back(constraint.GetSingleValue()); + } + + return FormatComparison(target, + formatter, + constraint.GetConstraintType(), + values, + constraint.IsCaseSensitive(), + constraint.IsMandatory(), + index, + escapeBrackets); + } + static void FormatJoin(std::string& target, const DatabaseConstraint& constraint, size_t index) @@ -269,6 +382,104 @@ } +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + static void FormatJoin(std::string& target, + const Orthanc::DatabasePluginMessages::DatabaseMetadataConstraint& constraint, + Orthanc::ResourceType level, + size_t index) + { + std::string tag = "t" + boost::lexical_cast<std::string>(index); + + if (constraint.is_mandatory()) + { + target = " INNER JOIN "; + } + else + { + target = " LEFT JOIN "; + } + + target += "Metadata "; + + target += tag + " ON " + tag + ".id = " + FormatLevel(level) + + ".internalId AND " + tag + ".type = " + + boost::lexical_cast<std::string>(constraint.metadata()); + } +#endif + + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + static void FormatJoinForOrdering(std::string& target, + uint32_t tagGroup, + uint32_t tagElement, + Orthanc::ResourceType tagLevel, + bool isIdentifierTag, + size_t index, + Orthanc::ResourceType requestLevel) + { + std::string orderArg = "order" + boost::lexical_cast<std::string>(index); + + target.clear(); + + if (tagLevel == Orthanc::ResourceType_Patient && requestLevel == Orthanc::ResourceType_Study) + { // Patient tags are copied at study level + tagLevel = Orthanc::ResourceType_Study; + } + + std::string tagTable; + if (isIdentifierTag) + { + tagTable = "DicomIdentifiers "; + } + else + { + tagTable = "MainDicomTags "; + } + + std::string tagFilter = orderArg + ".tagGroup = " + boost::lexical_cast<std::string>(tagGroup) + " AND " + orderArg + ".tagElement = " + boost::lexical_cast<std::string>(tagElement); + + if (tagLevel == requestLevel) + { + target = " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + FormatLevel(requestLevel) + + ".internalId AND " + tagFilter; + } + else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 1) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "parent.internalId AND " + tagFilter; + } + else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 2) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandparent.internalId AND " + tagFilter; + } + else if (static_cast<int32_t>(requestLevel) - static_cast<int32_t>(tagLevel) == 3) + { + target = " INNER JOIN Resources " + orderArg + "parent ON " + orderArg + "parent.internalId = " + FormatLevel(requestLevel) + ".parentId " + " INNER JOIN Resources " + orderArg + "grandparent ON " + orderArg + "grandparent.internalId = " + orderArg + "parent.parentId " + " INNER JOIN Resources " + orderArg + "grandgrandparent ON " + orderArg + "grandgrandparent.internalId = " + orderArg + "grandparent.parentId " + " LEFT JOIN " + tagTable + " " + orderArg + " ON " + orderArg + ".id = " + orderArg + "grandgrandparent.internalId AND " + tagFilter; + } + } +#endif + + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + static void FormatJoinForOrdering(std::string& target, + int32_t metadata, + size_t index, + Orthanc::ResourceType requestLevel) + { + std::string arg = "order" + boost::lexical_cast<std::string>(index); + + target = " INNER JOIN Metadata " + arg + " ON " + arg + ".id = " + FormatLevel(requestLevel) + + ".internalId AND " + arg + ".type = " + + boost::lexical_cast<std::string>(metadata); + } +#endif + + static std::string Join(const std::list<std::string>& values, const std::string& prefix, const std::string& separator) @@ -365,8 +576,7 @@ } } - std::string values; - Orthanc::Toolbox::JoinStrings(values, comparisonValues, ", "); + std::string values = boost::algorithm::join(comparisonValues, ", "); if (constraint.IsCaseSensitive()) { @@ -606,9 +816,299 @@ if (limit != 0) { - sql += " LIMIT " + boost::lexical_cast<std::string>(limit); + sql += " ORDER BY " + FormatLevel(queryLevel) + ".publicId "; // we need an "order by" to use limits + sql += formatter.FormatLimits(0, limit); + } + } + + +#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1 + static Orthanc::ResourceType DetectLevel(const Orthanc::DatabasePluginMessages::Find_Request& request) + { + // This corresponds to "Orthanc::OrthancIdentifiers()::DetectLevel()" in the Orthanc core + if (!request.orthanc_id_patient().empty() && + request.orthanc_id_study().empty() && + request.orthanc_id_series().empty() && + request.orthanc_id_instance().empty()) + { + return Orthanc::ResourceType_Patient; + } + else if (!request.orthanc_id_study().empty() && + request.orthanc_id_series().empty() && + request.orthanc_id_instance().empty()) + { + return Orthanc::ResourceType_Study; + } + else if (!request.orthanc_id_series().empty() && + request.orthanc_id_instance().empty()) + { + return Orthanc::ResourceType_Series; + } + else if (!request.orthanc_id_instance().empty()) + { + return Orthanc::ResourceType_Instance; + } + else + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + } + + static const std::string& GetOrthancIdentifier(const Orthanc::DatabasePluginMessages::Find_Request& request, Orthanc::ResourceType level) + { + switch (level) + { + case Orthanc::ResourceType::ResourceType_Patient: + return request.orthanc_id_patient(); + case Orthanc::ResourceType::ResourceType_Study: + return request.orthanc_id_study(); + case Orthanc::ResourceType::ResourceType_Series: + return request.orthanc_id_series(); + case Orthanc::ResourceType::ResourceType_Instance: + return request.orthanc_id_instance(); + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); } } + + + void ISqlLookupFormatter::Apply(std::string& sql, + ISqlLookupFormatter& formatter, + const Orthanc::DatabasePluginMessages::Find_Request& request) + { + const bool escapeBrackets = formatter.IsEscapeBrackets(); + Orthanc::ResourceType queryLevel = MessagesToolbox::Convert(request.level()); + const std::string& strQueryLevel = FormatLevel(queryLevel); + + DatabaseConstraints constraints; + + for (int i = 0; i < request.dicom_tag_constraints().size(); i++) + { + constraints.AddConstraint(new DatabaseConstraint(request.dicom_tag_constraints(i))); + } + + Orthanc::ResourceType lowerLevel, upperLevel; + GetLookupLevels(lowerLevel, upperLevel, queryLevel, constraints); + + assert(upperLevel <= queryLevel && + queryLevel <= lowerLevel); + + std::string ordering; + std::string orderingJoins; + + if (request.ordering_size() > 0) + { + std::vector<std::string> orderByFields; + + for (int i = 0; i < request.ordering_size(); ++i) + { + std::string orderingJoin; + const Orthanc::DatabasePluginMessages::Find_Request_Ordering& ordering = request.ordering(i); + + switch (ordering.key_type()) + { + case Orthanc::DatabasePluginMessages::OrderingKeyType::ORDERING_KEY_TYPE_DICOM_TAG: + FormatJoinForOrdering(orderingJoin, ordering.tag_group(), ordering.tag_element(), MessagesToolbox::Convert(ordering.tag_level()), ordering.is_identifier_tag(), i, queryLevel); + break; + case Orthanc::DatabasePluginMessages::OrderingKeyType::ORDERING_KEY_TYPE_METADATA: + FormatJoinForOrdering(orderingJoin, ordering.metadata(), i, queryLevel); + break; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + + orderingJoins += orderingJoin; + + std::string orderByField; + if (!formatter.SupportsNullsLast()) + { + orderByField = "CASE WHEN order" + boost::lexical_cast<std::string>(i) + ".value IS NULL THEN 1 ELSE 0 END, "; + } + orderByField += "order" + boost::lexical_cast<std::string>(i) + ".value"; + + if (ordering.direction() == Orthanc::DatabasePluginMessages::OrderingDirection::ORDERING_DIRECTION_ASC) + { + orderByField += " ASC"; + } + else + { + orderByField += " DESC"; + } + + orderByFields.push_back(orderByField); + } + + std::string orderByFieldsString = boost::algorithm::join(orderByFields, ", "); + + if (formatter.SupportsNullsLast()) + { + ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + " NULLS LAST) AS rowNumber"; + } + else + { + ordering = "ROW_NUMBER() OVER (ORDER BY " + orderByFieldsString + ") AS rowNumber"; + } + } + else + { + ordering = "ROW_NUMBER() OVER (ORDER BY " + strQueryLevel + ".publicId) AS rowNumber"; // we need a default ordering in order to make default queries repeatable when using since&limit + } + + sql = ("SELECT " + + strQueryLevel + ".publicId, " + + strQueryLevel + ".internalId, " + + ordering + + " FROM Resources AS " + strQueryLevel); + + + std::string joins, comparisons; + + const bool isOrthancIdentifiersDefined = (!request.orthanc_id_patient().empty() || + !request.orthanc_id_study().empty() || + !request.orthanc_id_series().empty() || + !request.orthanc_id_instance().empty()); + + // handle parent constraints + if (isOrthancIdentifiersDefined && Orthanc::IsResourceLevelAboveOrEqual(DetectLevel(request), queryLevel)) + { + Orthanc::ResourceType topParentLevel = DetectLevel(request); + + if (topParentLevel == queryLevel) + { + comparisons += " AND " + FormatLevel(topParentLevel) + ".publicId = " + formatter.GenerateParameter(GetOrthancIdentifier(request, topParentLevel)); + } + else + { + comparisons += " AND " + FormatLevel("parent", topParentLevel) + ".publicId = " + formatter.GenerateParameter(GetOrthancIdentifier(request, topParentLevel)); + + for (int level = queryLevel; level > topParentLevel; level--) + { + joins += " INNER JOIN Resources " + + FormatLevel("parent", static_cast<Orthanc::ResourceType>(level - 1)) + " ON " + + FormatLevel("parent", static_cast<Orthanc::ResourceType>(level - 1)) + ".internalId = "; + if (level == queryLevel) + { + joins += FormatLevel(static_cast<Orthanc::ResourceType>(level)) + ".parentId"; + } + else + { + joins += FormatLevel("parent", static_cast<Orthanc::ResourceType>(level)) + ".parentId"; + } + } + } + } + + size_t count = 0; + + for (size_t i = 0; i < constraints.GetSize(); i++) + { + const DatabaseConstraint& constraint = constraints.GetConstraint(i); + + std::string comparison; + + if (FormatComparison(comparison, formatter, constraint, count, escapeBrackets)) + { + std::string join; + FormatJoin(join, constraint, count); + joins += join; + + if (!comparison.empty()) + { + comparisons += " AND " + comparison; + } + + count ++; + } + } + + for (int i = 0; i < request.metadata_constraints_size(); i++) + { + std::string comparison; + + if (FormatComparison(comparison, formatter, request.metadata_constraints(i), count, escapeBrackets)) + { + std::string join; + FormatJoin(join, request.metadata_constraints(i), queryLevel, count); + joins += join; + + if (!comparison.empty()) + { + comparisons += " AND " + comparison; + } + + count ++; + } + } + + + for (int level = queryLevel - 1; level >= upperLevel; level--) + { + sql += (" INNER JOIN Resources " + + FormatLevel(static_cast<Orthanc::ResourceType>(level)) + " ON " + + FormatLevel(static_cast<Orthanc::ResourceType>(level)) + ".internalId=" + + FormatLevel(static_cast<Orthanc::ResourceType>(level + 1)) + ".parentId"); + } + + for (int level = queryLevel + 1; level <= lowerLevel; level++) + { + sql += (" INNER JOIN Resources " + + FormatLevel(static_cast<Orthanc::ResourceType>(level)) + " ON " + + FormatLevel(static_cast<Orthanc::ResourceType>(level - 1)) + ".internalId=" + + FormatLevel(static_cast<Orthanc::ResourceType>(level)) + ".parentId"); + } + + std::list<std::string> where; + where.push_back(strQueryLevel + ".resourceType = " + + formatter.FormatResourceType(queryLevel) + comparisons); + + + if (!request.labels().empty()) + { + /** + * "In SQL Server, NOT EXISTS and NOT IN predicates are the best + * way to search for missing values, as long as both columns in + * question are NOT NULL." + * https://explainextended.com/2009/09/15/not-in-vs-not-exists-vs-left-join-is-null-sql-server/ + **/ + + std::list<std::string> formattedLabels; + for (int i = 0; i < request.labels().size(); i++) + { + formattedLabels.push_back(formatter.GenerateParameter(request.labels(i))); + } + + std::string condition; + switch (request.labels_constraint()) + { + case Orthanc::DatabasePluginMessages::LABELS_CONSTRAINT_ANY: + condition = "> 0"; + break; + + case Orthanc::DatabasePluginMessages::LABELS_CONSTRAINT_ALL: + condition = "= " + boost::lexical_cast<std::string>(request.labels().size()); + break; + + case Orthanc::DatabasePluginMessages::LABELS_CONSTRAINT_NONE: + condition = "= 0"; + break; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + + where.push_back("(SELECT COUNT(1) FROM Labels AS selectedLabels WHERE selectedLabels.id = " + strQueryLevel + + ".internalId AND selectedLabels.label IN (" + Join(formattedLabels, "", ", ") + ")) " + condition); + } + + sql += joins + orderingJoins + Join(where, " WHERE ", " AND "); + + if (request.has_limits()) + { + sql += formatter.FormatLimits(request.limits().since(), request.limits().count()); + } + + } +#endif void ISqlLookupFormatter::ApplySingleLevel(std::string& sql, @@ -721,8 +1221,8 @@ if (limit != 0) { - sql += " LIMIT " + boost::lexical_cast<std::string>(limit); + sql += " ORDER BY publicId "; // we need an "order by" to use limits + sql += formatter.FormatLimits(0, limit); } } - }
--- a/Framework/Plugins/ISqlLookupFormatter.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/ISqlLookupFormatter.h Mon Dec 16 16:36:12 2024 +0100 @@ -30,7 +30,7 @@ #pragma once -#include <Enumerations.h> +#include "MessagesToolbox.h" #include <boost/noncopyable.hpp> #include <vector> @@ -38,6 +38,7 @@ namespace OrthancDatabases { class DatabaseConstraints; + class FindRequest; enum LabelsConstraint { @@ -59,6 +60,10 @@ virtual std::string FormatWildcardEscape() = 0; + virtual std::string FormatLimits(uint64_t since, uint64_t count) = 0; + + virtual std::string FormatNull(const char* type) = 0; + /** * Whether to escape '[' and ']', which is only needed for * MSSQL. New in Orthanc 1.10.0, from the following changeset: @@ -66,6 +71,8 @@ **/ virtual bool IsEscapeBrackets() const = 0; + virtual bool SupportsNullsLast() const = 0; + static void GetLookupLevels(Orthanc::ResourceType& lowerLevel, Orthanc::ResourceType& upperLevel, const Orthanc::ResourceType& queryLevel, @@ -86,5 +93,11 @@ const std::set<std::string>& labels, // New in Orthanc 1.12.0 LabelsConstraint labelsConstraint, // New in Orthanc 1.12.0 size_t limit); + +#if ORTHANC_PLUGINS_HAS_INTEGRATED_FIND == 1 + static void Apply(std::string& sql, + ISqlLookupFormatter& formatter, + const Orthanc::DatabasePluginMessages::Find_Request& request); +#endif }; }
--- a/Framework/Plugins/IndexBackend.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/IndexBackend.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -36,6 +36,8 @@ #include <OrthancException.h> #include <Toolbox.h> +#include <boost/algorithm/string/join.hpp> + namespace OrthancDatabases { @@ -60,7 +62,24 @@ return s; } - + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + static std::string JoinChanges(const std::set<uint32_t>& changeTypes) + { + std::set<std::string> changeTypesString; + for (std::set<uint32_t>::const_iterator it = changeTypes.begin(); it != changeTypes.end(); ++it) + { + changeTypesString.insert(boost::lexical_cast<std::string>(*it)); + } + + std::string joinedChangesTypes; + Orthanc::Toolbox::JoinStrings(joinedChangesTypes, changeTypesString, ", "); + + return joinedChangesTypes; + } +#endif + + template <typename T> static void ReadListOfIntegers(std::list<T>& target, DatabaseManager::CachedStatement& statement, @@ -112,33 +131,69 @@ } + namespace // Anonymous namespace to avoid clashes between compilation modules + { + struct Change + { + int64_t seq_; + int32_t changeType_; + OrthancPluginResourceType resourceType_; + std::string publicId_; + std::string changeDate_; + + Change(int64_t seq, int32_t changeType, OrthancPluginResourceType resourceType, const std::string& publicId, const std::string& changeDate) + : seq_(seq), changeType_(changeType), resourceType_(resourceType), publicId_(publicId), changeDate_(changeDate) + { + } + }; + } + + void IndexBackend::ReadChangesInternal(IDatabaseBackendOutput& output, bool& done, DatabaseManager& manager, DatabaseManager::CachedStatement& statement, const Dictionary& args, - uint32_t limit) + uint32_t limit, + bool returnFirstResults) { statement.Execute(args); - uint32_t count = 0; - - while (count < limit && - !statement.IsDone()) + std::list<Change> changes; + while (!statement.IsDone()) { - output.AnswerChange( + changes.push_back(Change( statement.ReadInteger64(0), statement.ReadInteger32(1), static_cast<OrthancPluginResourceType>(statement.ReadInteger32(2)), statement.ReadString(3), - statement.ReadString(4)); + statement.ReadString(4) + )); statement.Next(); - count++; } - - done = (count < limit || - statement.IsDone()); + + done = changes.size() <= limit; // 'done' means we have returned all requested changes + + // if we have retrieved more changes than requested -> cleanup + if (changes.size() > limit) + { + assert(changes.size() == limit+1); // the statement should only request 1 element more + + if (returnFirstResults) + { + changes.pop_back(); + } + else + { + changes.pop_front(); + } + } + + for (std::list<Change>::const_iterator it = changes.begin(); it != changes.end(); ++it) + { + output.AnswerChange(it->seq_, it->changeType_, it->resourceType_, it->publicId_, it->changeDate_); + } } @@ -256,8 +311,10 @@ } - IndexBackend::IndexBackend(OrthancPluginContext* context) : - context_(context) + IndexBackend::IndexBackend(OrthancPluginContext* context, + bool readOnly) : + context_(context), + readOnly_(readOnly) { } @@ -531,62 +588,131 @@ { suffix = "OFFSET ${since} ROWS FETCH FIRST ${limit} ROWS ONLY"; } - else + else if (limit > 0) { suffix = "LIMIT ${limit} OFFSET ${since}"; } - DatabaseManager::CachedStatement statement( - STATEMENT_FROM_HERE, manager, - "SELECT publicId FROM (SELECT publicId FROM Resources " - "WHERE resourceType=${type}) AS tmp ORDER BY tmp.publicId " + suffix); + std::string sql = "SELECT publicId FROM (SELECT publicId FROM Resources " + "WHERE resourceType=${type}) AS tmp ORDER BY tmp.publicId " + suffix; + + DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql); statement.SetReadOnly(true); - statement.SetParameterType("type", ValueType_Integer64); - statement.SetParameterType("limit", ValueType_Integer64); - statement.SetParameterType("since", ValueType_Integer64); Dictionary args; + + statement.SetParameterType("type", ValueType_Integer64); args.SetIntegerValue("type", static_cast<int>(resourceType)); - args.SetIntegerValue("limit", limit); - args.SetIntegerValue("since", since); + + if (limit > 0) + { + statement.SetParameterType("limit", ValueType_Integer64); + statement.SetParameterType("since", ValueType_Integer64); + args.SetIntegerValue("limit", limit); + args.SetIntegerValue("since", since); + } ReadListOfStrings(target, statement, args); } - - /* Use GetOutput().AnswerChange() */ void IndexBackend::GetChanges(IDatabaseBackendOutput& output, bool& done /*out*/, DatabaseManager& manager, int64_t since, uint32_t limit) { - std::string suffix; + std::set<uint32_t> changeTypes; + GetChangesExtended(output, done, manager, since, -1, changeTypes, limit); + } + + /* Use GetOutput().AnswerChange() */ + void IndexBackend::GetChangesExtended(IDatabaseBackendOutput& output, + bool& done /*out*/, + DatabaseManager& manager, + int64_t since, + int64_t to, + const std::set<uint32_t>& changeTypes, + uint32_t limit) + { + std::string limitSuffix; if (manager.GetDialect() == Dialect_MSSQL) { - suffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY"; + limitSuffix = "OFFSET 0 ROWS FETCH FIRST ${limit} ROWS ONLY"; } else { - suffix = "LIMIT ${limit}"; + limitSuffix = "LIMIT ${limit}"; } - DatabaseManager::CachedStatement statement( - STATEMENT_FROM_HERE, manager, - "SELECT Changes.seq, Changes.changeType, Changes.resourceType, Resources.publicId, " - "Changes.date FROM Changes INNER JOIN Resources " - "ON Changes.internalId = Resources.internalId WHERE seq>${since} ORDER BY seq " + suffix); - + std::vector<std::string> filters; + bool hasSince = false; + bool hasTo = false; + + if (since > 0) + { + hasSince = true; + filters.push_back("seq>${since}"); + } + if (to != -1) + { + hasTo = true; + filters.push_back("seq<=${to}"); + } +#if ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED == 1 + if (changeTypes.size() > 0) + { + filters.push_back("changeType IN (" + JoinChanges(changeTypes) + ") "); + } +#endif + + std::string filtersString; + if (filters.size() > 0) + { + filtersString = "WHERE " + boost::algorithm::join(filters, " AND "); + } + + std::string sql; + bool returnFirstResults; + if (hasTo && !hasSince) + { + // in this case, we want the largest values but we want them ordered in ascending order + sql = "SELECT * FROM (SELECT Changes.seq, Changes.changeType, Changes.resourceType, Resources.publicId, Changes.date " + "FROM Changes INNER JOIN Resources " + "ON Changes.internalId = Resources.internalId " + filtersString + " ORDER BY seq DESC " + limitSuffix + + ") AS FilteredChanges ORDER BY seq ASC"; + + returnFirstResults = false; + } + else + { + // default query: we want the smallest values ordered in ascending order + sql = "SELECT Changes.seq, Changes.changeType, Changes.resourceType, Resources.publicId, " + "Changes.date FROM Changes INNER JOIN Resources " + "ON Changes.internalId = Resources.internalId " + filtersString + " ORDER BY seq ASC " + limitSuffix; + returnFirstResults = true; + } + + DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql); statement.SetReadOnly(true); + Dictionary args; + statement.SetParameterType("limit", ValueType_Integer64); - statement.SetParameterType("since", ValueType_Integer64); - - Dictionary args; - args.SetIntegerValue("limit", limit + 1); - args.SetIntegerValue("since", since); - - ReadChangesInternal(output, done, manager, statement, args, limit); + args.SetIntegerValue("limit", limit + 1); // we take limit+1 because we use the +1 to know if "Done" must be set to true + + if (hasSince) + { + statement.SetParameterType("since", ValueType_Integer64); + args.SetIntegerValue("since", since); + } + + if (hasTo) + { + statement.SetParameterType("to", ValueType_Integer64); + args.SetIntegerValue("to", to); + } + + ReadChangesInternal(output, done, manager, statement, args, limit, returnFirstResults); } @@ -686,7 +812,7 @@ Dictionary args; bool done; // Ignored - ReadChangesInternal(output, done, manager, statement, args, 1); + ReadChangesInternal(output, done, manager, statement, args, 1, true); } @@ -1240,7 +1366,11 @@ bool IndexBackend::HasMeasureLatency() { +#if ORTHANC_FRAMEWORK_VERSION_IS_ABOVE(1, 12, 2) return true; +#else + return false; +#endif } @@ -2052,7 +2182,7 @@ virtual std::string FormatResourceType(Orthanc::ResourceType level) { - return boost::lexical_cast<std::string>(Plugins::Convert(level)); + return boost::lexical_cast<std::string>(MessagesToolbox::ConvertToPlainC(level)); } virtual std::string FormatWildcardEscape() @@ -2072,6 +2202,74 @@ } } + virtual std::string FormatNull(const char* type) + { + switch (dialect_) + { + case Dialect_PostgreSQL: + return std::string("NULL::") + type; + case Dialect_MSSQL: + case Dialect_SQLite: + case Dialect_MySQL: + return "NULL"; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + } + + + virtual std::string FormatLimits(uint64_t since, uint64_t count) + { + std::string sql; + + switch (dialect_) + { + case Dialect_MSSQL: + { + if (count > 0 || since > 0) + { + sql += " OFFSET " + boost::lexical_cast<std::string>(since) + " ROWS "; + } + if (count > 0) + { + sql += " FETCH NEXT " + boost::lexical_cast<std::string>(count) + " ROWS ONLY "; + } + }; break; + case Dialect_SQLite: + case Dialect_PostgreSQL: + { + if (count > 0) + { + sql += " LIMIT " + boost::lexical_cast<std::string>(count); + } + if (since > 0) + { + sql += " OFFSET " + boost::lexical_cast<std::string>(since); + } + }; break; + case Dialect_MySQL: + { + if (count > 0 && since > 0) + { + sql += " LIMIT " + boost::lexical_cast<std::string>(since) + ", " + boost::lexical_cast<std::string>(count); + } + else if (count > 0) + { + sql += " LIMIT " + boost::lexical_cast<std::string>(count); + } + else if (since > 0) + { + sql += " LIMIT " + boost::lexical_cast<std::string>(since) + ", 18446744073709551615"; // max uint64 value when you don't want any limit + } + }; break; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + + return sql; + } + virtual bool IsEscapeBrackets() const { // This was initially done at a bad location by the following changeset: @@ -2079,6 +2277,11 @@ return (dialect_ == Dialect_MSSQL); } + virtual bool SupportsNullsLast() const + { + return (dialect_ == Dialect_PostgreSQL); + } + void PrepareStatement(DatabaseManager::StandaloneStatement& statement) const { statement.SetReadOnly(true); @@ -2109,7 +2312,7 @@ bool requestSomeInstance) { LookupFormatter formatter(manager.GetDialect()); - Orthanc::ResourceType queryLevel = Plugins::Convert(queryLevel_); + Orthanc::ResourceType queryLevel = MessagesToolbox::Convert(queryLevel_); Orthanc::ResourceType lowerLevel, upperLevel; ISqlLookupFormatter::GetLookupLevels(lowerLevel, upperLevel, queryLevel, lookup); @@ -2788,7 +2991,8 @@ void IndexBackend::Register(IndexBackend* backend, size_t countConnections, - unsigned int maxDatabaseRetries) + unsigned int maxDatabaseRetries, + unsigned int housekeepingDelaySeconds) { if (backend == NULL) { @@ -2802,7 +3006,7 @@ # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) if (OrthancPluginCheckVersionAdvanced(backend->GetContext(), 1, 12, 0) == 1) { - DatabaseBackendAdapterV4::Register(backend, countConnections, maxDatabaseRetries); + DatabaseBackendAdapterV4::Register(backend, countConnections, maxDatabaseRetries, housekeepingDelaySeconds); return; } # endif @@ -2812,7 +3016,7 @@ # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 9, 2) if (OrthancPluginCheckVersionAdvanced(backend->GetContext(), 1, 9, 2) == 1) { - DatabaseBackendAdapterV3::Register(backend, countConnections, maxDatabaseRetries); + DatabaseBackendAdapterV3::Register(backend, countConnections, maxDatabaseRetries, housekeepingDelaySeconds); return; } # endif @@ -2880,6 +3084,7 @@ uint64_t IndexBackend::MeasureLatency(DatabaseManager& manager) { +#if ORTHANC_FRAMEWORK_VERSION_IS_ABOVE(1, 12, 2) // execute 11x the simplest statement and return the median value std::vector<uint64_t> measures; @@ -2897,6 +3102,9 @@ std::sort(measures.begin(), measures.end()); return measures[measures.size() / 2]; +#else + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); +#endif } @@ -2908,4 +3116,1160 @@ backend.ConfigureDatabase(*manager, hasIdentifierTags, identifierTags); return manager.release(); } + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + bool IndexBackend::HasFindSupport() const + { + // TODO-FIND move to child plugins ? + return true; + } +#endif + + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* GetResourceContent( + Orthanc::DatabasePluginMessages::Find_Response* response, + Orthanc::DatabasePluginMessages::ResourceType level) + { + Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = NULL; // the protobuf response will be the owner + + switch (level) + { + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT: + content = response->mutable_patient_content(); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY: + content = response->mutable_study_content(); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES: + content =response->mutable_series_content(); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE: + content = response->mutable_instance_content(); + break; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + return content; + } + + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* GetChildrenContent( + Orthanc::DatabasePluginMessages::Find_Response* response, + Orthanc::DatabasePluginMessages::ResourceType childrenLevel) + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = NULL; // the protobuf response will be the owner + + switch (childrenLevel) + { + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY: + content = response->mutable_children_studies_content(); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES: + content =response->mutable_children_series_content(); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE: + content = response->mutable_children_instances_content(); + break; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + return content; + } + + std::string JoinRequestedMetadata(const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec) + { + std::set<std::string> metadataTypes; + for (int i = 0; i < childrenSpec->retrieve_metadata_size(); ++i) + { + metadataTypes.insert(boost::lexical_cast<std::string>(childrenSpec->retrieve_metadata(i))); + } + std::string joinedMetadataTypes; + Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ", "); + + return joinedMetadataTypes; + } + + std::string JoinRequestedTags(const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec) + { + std::set<std::string> tags; + for (int i = 0; i < childrenSpec->retrieve_main_dicom_tags_size(); ++i) + { + tags.insert("(" + boost::lexical_cast<std::string>(childrenSpec->retrieve_main_dicom_tags(i).group()) + + ", " + boost::lexical_cast<std::string>(childrenSpec->retrieve_main_dicom_tags(i).element()) + ")"); + } + std::string joinedTags; + Orthanc::Toolbox::JoinStrings(joinedTags, tags, ", "); + + return joinedTags; + } + + +#define C0_QUERY_ID 0 +#define C1_INTERNAL_ID 1 +#define C2_ROW_NUMBER 2 +#define C3_STRING_1 3 +#define C4_STRING_2 4 +#define C5_STRING_3 5 +#define C6_INT_1 6 +#define C7_INT_2 7 +#define C8_INT_3 8 +#define C9_BIG_INT_1 9 +#define C10_BIG_INT_2 10 + +#define QUERY_LOOKUP 1 +#define QUERY_MAIN_DICOM_TAGS 2 +#define QUERY_ATTACHMENTS 3 +#define QUERY_METADATA 4 +#define QUERY_LABELS 5 +#define QUERY_PARENT_MAIN_DICOM_TAGS 10 +#define QUERY_PARENT_IDENTIFIER 11 +#define QUERY_PARENT_METADATA 12 +#define QUERY_GRAND_PARENT_MAIN_DICOM_TAGS 15 +#define QUERY_GRAND_PARENT_METADATA 16 +#define QUERY_CHILDREN_IDENTIFIERS 20 +#define QUERY_CHILDREN_MAIN_DICOM_TAGS 21 +#define QUERY_CHILDREN_METADATA 22 +#define QUERY_CHILDREN_COUNT 23 +#define QUERY_GRAND_CHILDREN_IDENTIFIERS 30 +#define QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS 31 +#define QUERY_GRAND_CHILDREN_METADATA 32 +#define QUERY_GRAND_CHILDREN_COUNT 33 +#define QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS 40 +#define QUERY_GRAND_GRAND_CHILDREN_COUNT 41 +#define QUERY_ONE_INSTANCE_IDENTIFIER 50 +#define QUERY_ONE_INSTANCE_METADATA 51 +#define QUERY_ONE_INSTANCE_ATTACHMENTS 52 + +#define STRINGIFY(x) #x +#define TOSTRING(x) STRINGIFY(x) + + void IndexBackend::ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) + { + std::string sql; + + LookupFormatter formatter(manager.GetDialect()); + std::string lookupSql; + ISqlLookupFormatter::Apply(lookupSql, formatter, request); + + sql = "WITH Lookup AS (" + lookupSql + ") SELECT COUNT(*) FROM Lookup"; + + DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql); + statement.Execute(formatter.GetDictionary()); + response.mutable_count_resources()->set_count(statement.ReadInteger64(0)); + } + + void IndexBackend::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) + { + // TODO-FIND move to child plugins ? + + + // If we want the Find to use a read-only transaction, we can not create temporary tables with + // the lookup results. So we must use a CTE (Common Table Expression). + // However, a CTE can only be used in a single query -> we must unionize all the following + // queries to retrieve values from various tables. + // However, to use UNION, all tables must have the same columns (numbers and types). That's + // why we have generic column names. + // So, at the end we'll have only one very big query ! + + std::string sql; + + // extract the resource id of interest by executing the lookup in a CTE + LookupFormatter formatter(manager.GetDialect()); + std::string lookupSqlCTE; + ISqlLookupFormatter::Apply(lookupSqlCTE, formatter, request); + + // base query, retrieve the ordered internalId and publicId of the selected resources + sql = "WITH Lookup AS (" + lookupSqlCTE + ") "; + + std::string oneInstanceSqlCTE; + + if (request.level() != Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE && + request.retrieve_one_instance_metadata_and_attachments()) + { + switch (request.level()) + { + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES: + { + oneInstanceSqlCTE = "SELECT Lookup.internalId AS parentInternalId, childLevel.publicId AS instancePublicId, childLevel.internalId AS instanceInternalId, ROW_NUMBER() OVER (PARTITION BY Lookup.internalId ORDER BY childLevel.publicId) AS rowNum" + " FROM Resources AS childLevel " + " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId"; + }; break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY: + { + oneInstanceSqlCTE = "SELECT Lookup.internalId AS parentInternalId, grandChildLevel.publicId AS instancePublicId, grandChildLevel.internalId AS instanceInternalId, ROW_NUMBER() OVER (PARTITION BY Lookup.internalId ORDER BY grandChildLevel.publicId) AS rowNum" + " FROM Resources AS grandChildLevel " + " INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " + " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId"; + }; break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT: + { + oneInstanceSqlCTE = "SELECT Lookup.internalId AS parentInternalId, grandGrandChildLevel.publicId AS instancePublicId, grandGrandChildLevel.internalId AS instanceInternalId, ROW_NUMBER() OVER (PARTITION BY Lookup.internalId ORDER BY grandGrandChildLevel.publicId) AS rowNum" + " FROM Resources AS grandGrandChildLevel " + " INNER JOIN Resources grandChildLevel ON grandGrandChildLevel.parentId = grandChildLevel.internalId " + " INNER JOIN Resources childLevel ON grandChildLevel.parentId = childLevel.internalId " + " INNER JOIN Lookup ON childLevel.parentId = Lookup.internalId"; + }; break; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + } + sql += ", _OneInstance AS (" + oneInstanceSqlCTE + ") "; + sql += ", OneInstance AS (SELECT parentInternalId, instancePublicId, instanceInternalId FROM _OneInstance WHERE rowNum = 1) "; // this is a generic way to implement DISTINCT ON + } + + // if (!oneInstanceSqlCTE.empty() && (manager.GetDialect() == Dialect_MySQL || manager.GetDialect() == Dialect_SQLite)) + // { // all CTEs must be declared first in some dialects + // } + + std::string revisionInC7; + if (HasRevisionsSupport()) + { + revisionInC7 = " revision AS c7_int2, "; + } + else + { + revisionInC7 = " 0 AS C7_int2, "; + } + + + sql += " SELECT " + " " TOSTRING(QUERY_LOOKUP) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " Lookup.rowNumber AS c2_rowNumber, " + " Lookup.publicId AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + " FROM Lookup "; + + // need MainDicomTags from resource ? + if (request.retrieve_main_dicom_tags()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN MainDicomTags ON MainDicomTags.id = Lookup.internalId "; + } + + // need resource metadata ? + if (request.retrieve_metadata()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " type AS c6_int1, " + + revisionInC7 + + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Metadata ON Metadata.id = Lookup.internalId "; + } + + // need resource attachments ? + if (request.retrieve_attachments()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_ATTACHMENTS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " uuid AS c3_string1, " + " uncompressedHash AS c4_string2, " + " compressedHash AS c5_string3, " + " fileType AS c6_int1, " + + revisionInC7 + + " compressionType AS c8_int3, " + " compressedSize AS c9_big_int1, " + " uncompressedSize AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN AttachedFiles ON AttachedFiles.id = Lookup.internalId "; + } + + // need resource labels ? + if (request.retrieve_labels()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_LABELS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " label AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Labels ON Labels.id = Lookup.internalId "; + } + + // need MainDicomTags from parent ? + if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT) + { + const Orthanc::DatabasePluginMessages::Find_Request_ParentSpecification* parentSpec = NULL; + switch (request.level()) + { + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY: + parentSpec = &(request.parent_patient()); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES: + parentSpec = &(request.parent_study()); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE: + parentSpec = &(request.parent_series()); + break; + + default: + break; + } + + if (parentSpec->retrieve_main_dicom_tags()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN MainDicomTags ON MainDicomTags.id = currentLevel.parentId "; + } + + if (parentSpec->retrieve_metadata()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_PARENT_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " type AS c6_int1, " + + revisionInC7 + + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN Metadata ON Metadata.id = currentLevel.parentId "; + } + + // need MainDicomTags from grandparent ? + if (request.level() > Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY) + { + const Orthanc::DatabasePluginMessages::Find_Request_ParentSpecification* grandparentSpec = NULL; + switch (request.level()) + { + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES: + grandparentSpec = &(request.parent_patient()); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE: + grandparentSpec = &(request.parent_study()); + break; + + default: + break; + } + + if (grandparentSpec->retrieve_main_dicom_tags()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_PARENT_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId " + "INNER JOIN MainDicomTags ON MainDicomTags.id = parentLevel.parentId "; + } + + if (grandparentSpec->retrieve_metadata()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_PARENT_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " type AS c6_int1, " + + revisionInC7 + + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Resources currentLevel ON Lookup.internalId = currentLevel.internalId " + "INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId " + "INNER JOIN Metadata ON Metadata.id = parentLevel.parentId "; + } + } + } + + // need MainDicomTags from children ? + if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES) + { + const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* childrenSpec = NULL; + switch (request.level()) + { + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT: + childrenSpec = &(request.children_studies()); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY: + childrenSpec = &(request.children_series()); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_SERIES: + childrenSpec = &(request.children_instances()); + break; + + default: + break; + } + + if (childrenSpec->retrieve_main_dicom_tags_size() > 0) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN MainDicomTags ON MainDicomTags.id = childLevel.internalId AND (tagGroup, tagElement) IN (" + JoinRequestedTags(childrenSpec) + ")"; + } + + // need children identifiers ? + if (childrenSpec->retrieve_identifiers()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_CHILDREN_IDENTIFIERS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " childLevel.publicId AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId "; + } + else if (childrenSpec->retrieve_count()) // no need to count if we have retrieved the list of identifiers + { + if (HasChildCountTable()) // TODO: rename in HasChildCountColumn ? + { + // // we get the count value either from the childCount table if it has been computed or from the Resources table + // sql += "UNION ALL SELECT " + // " " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, " + // " Lookup.internalId AS c1_internalId, " + // " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + // " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + // " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + // " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + // " " + formatter.FormatNull("INT") + " AS c6_int1, " + // " " + formatter.FormatNull("INT") + " AS c7_int2, " + // " " + formatter.FormatNull("INT") + " AS c8_int3, " + // " COALESCE(" + // " (ChildCount.childCount)," + // " (SELECT COUNT(childLevel.internalId)" + // " FROM Resources AS childLevel" + // " WHERE Lookup.internalId = childLevel.parentId" + // " )) AS c9_big_int1, " + // " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + // "FROM Lookup " + // "LEFT JOIN ChildCount ON Lookup.internalId = ChildCount.parentId "; + + // we get the count value either from the childCount column if it has been computed or from the Resources table + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COALESCE(" + " (Resources.childCount)," + " (SELECT COUNT(childLevel.internalId)" + " FROM Resources AS childLevel" + " WHERE Lookup.internalId = childLevel.parentId" + " )) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "LEFT JOIN Resources ON Lookup.internalId = Resources.internalId "; + } + else + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COUNT(childLevel.internalId) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId GROUP BY Lookup.internalId "; + } + } + + if (childrenSpec->retrieve_metadata_size() > 0) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_CHILDREN_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " type AS c6_int1, " + + revisionInC7 + + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN Metadata ON Metadata.id = childLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(childrenSpec) + ") "; + } + + if (request.level() <= Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY) + { + const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandchildrenSpec = NULL; + switch (request.level()) + { + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT: + grandchildrenSpec = &(request.children_series()); + break; + case Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_STUDY: + grandchildrenSpec = &(request.children_instances()); + break; + + default: + break; + } + + // need grand children identifiers ? + if (grandchildrenSpec->retrieve_identifiers()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " grandChildLevel.publicId AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId " + "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId "; + } + else if (grandchildrenSpec->retrieve_count()) // no need to count if we have retrieved the list of identifiers + { + if (HasChildCountTable()) + { + // // we get the count value either from the childCount table if it has been computed or from the Resources table + // sql += "UNION ALL SELECT " + // " " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + // " Lookup.internalId AS c1_internalId, " + // " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + // " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + // " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + // " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + // " " + formatter.FormatNull("INT") + " AS c6_int1, " + // " " + formatter.FormatNull("INT") + " AS c7_int2, " + // " " + formatter.FormatNull("INT") + " AS c8_int3, " + // " COALESCE(" + // " (SELECT SUM(ChildCount.childCount)" + // " FROM ChildCount" + // " INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId" + // " WHERE ChildCount.parentId = childLevel.internalId)," + // " (SELECT COUNT(grandChildLevel.internalId)" + // " FROM Resources AS childLevel" + // " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId" + // " WHERE Lookup.internalId = childLevel.parentId" + // " )) AS c9_big_int1, " + // " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + // "FROM Lookup "; + + // we get the count value either from the childCount column if it has been computed or from the Resources table + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COALESCE(" + " (SELECT SUM(childLevel.childCount)" + " FROM Resources AS childLevel" + " WHERE childLevel.parentId = Lookup.internalId)," + " (SELECT COUNT(grandChildLevel.internalId)" + " FROM Resources AS childLevel" + " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId" + " WHERE Lookup.internalId = childLevel.parentId" + " )) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup "; + } + else + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COUNT(grandChildLevel.internalId) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId " + " INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId GROUP BY Lookup.internalId "; + } + } + + if (grandchildrenSpec->retrieve_main_dicom_tags_size() > 0) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " tagGroup AS c6_int1, " + " tagElement AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId " + " INNER JOIN MainDicomTags ON MainDicomTags.id = grandChildLevel.internalId AND (tagGroup, tagElement) IN (" + JoinRequestedTags(grandchildrenSpec) + ")"; + } + + if (grandchildrenSpec->retrieve_metadata_size() > 0) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_CHILDREN_METADATA) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " type AS c6_int1, " + + revisionInC7 + + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources childLevel ON childLevel.parentId = Lookup.internalId " + " INNER JOIN Resources grandChildLevel ON grandChildLevel.parentId = childLevel.internalId " + " INNER JOIN Metadata ON Metadata.id = grandChildLevel.internalId AND Metadata.type IN (" + JoinRequestedMetadata(grandchildrenSpec) + ") "; + } + + if (request.level() == Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_PATIENT) + { + const Orthanc::DatabasePluginMessages::Find_Request_ChildrenSpecification* grandgrandchildrenSpec = &(request.children_instances()); + + // need grand children identifiers ? + if (grandgrandchildrenSpec->retrieve_identifiers()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " grandGrandChildLevel.publicId AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId " + "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId " + "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId "; + } + else if (grandgrandchildrenSpec->retrieve_count()) // no need to count if we have retrieved the list of identifiers + { + if (HasChildCountTable()) + { + // // we get the count value either from the childCount table if it has been computed or from the Resources table + // sql += "UNION ALL SELECT " + // " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + // " Lookup.internalId AS c1_internalId, " + // " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + // " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + // " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + // " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + // " " + formatter.FormatNull("INT") + " AS c6_int1, " + // " " + formatter.FormatNull("INT") + " AS c7_int2, " + // " " + formatter.FormatNull("INT") + " AS c8_int3, " + // " COALESCE(" + // " (SELECT SUM(ChildCount.childCount)" + // " FROM ChildCount" + // " INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId" + // " INNER JOIN Resources AS grandChildLevel ON grandChildLevel.parentId = childLevel.internalId" + // " WHERE ChildCount.parentId = grandChildLevel.internalId)," + // " (SELECT COUNT(grandGrandChildLevel.internalId)" + // " FROM Resources AS childLevel" + // " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId" + // " INNER JOIN Resources AS grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId" + // " WHERE Lookup.internalId = childLevel.parentId" + // " )) AS c9_big_int1, " + // " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + // "FROM Lookup "; + + // we get the count value either from the childCount column if it has been computed or from the Resources table + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COALESCE(" + " (SELECT SUM(grandChildLevel.childCount)" + " FROM Resources AS grandChildLevel" + " INNER JOIN Resources AS childLevel ON childLevel.parentId = Lookup.internalId" + " WHERE grandChildLevel.parentId = childLevel.internalId)," + " (SELECT COUNT(grandGrandChildLevel.internalId)" + " FROM Resources AS childLevel" + " INNER JOIN Resources AS grandChildLevel ON childLevel.internalId = grandChildLevel.parentId" + " INNER JOIN Resources AS grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId" + " WHERE Lookup.internalId = childLevel.parentId" + " )) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup "; + } + else + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_GRAND_GRAND_CHILDREN_COUNT) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " " + formatter.FormatNull("TEXT") + " AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " COUNT(grandChildLevel.internalId) AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + "INNER JOIN Resources childLevel ON Lookup.internalId = childLevel.parentId " + "INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId " + "INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId GROUP BY Lookup.internalId "; + } + } + } + } + } + + // need parent identifier ? + if (request.retrieve_parent_identifier()) + { + sql += "UNION ALL SELECT " + " " TOSTRING(QUERY_PARENT_IDENTIFIER) " AS c0_queryId, " + " Lookup.internalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " parentLevel.publicId AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + "FROM Lookup " + " INNER JOIN Resources currentLevel ON currentLevel.internalId = Lookup.internalId " + " INNER JOIN Resources parentLevel ON currentLevel.parentId = parentLevel.internalId "; + } + + // need one instance info ? + if (request.level() != Orthanc::DatabasePluginMessages::ResourceType::RESOURCE_INSTANCE && + request.retrieve_one_instance_metadata_and_attachments()) + { + sql += " UNION ALL SELECT" + " " TOSTRING(QUERY_ONE_INSTANCE_IDENTIFIER) " AS c0_queryId, " + " parentInternalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " instancePublicId AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " " + formatter.FormatNull("INT") + " AS c6_int1, " + " " + formatter.FormatNull("INT") + " AS c7_int2, " + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " instanceInternalId AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + " FROM OneInstance "; + + sql += " UNION ALL SELECT" + " " TOSTRING(QUERY_ONE_INSTANCE_METADATA) " AS c0_queryId, " + " parentInternalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " Metadata.value AS c3_string1, " + " " + formatter.FormatNull("TEXT") + " AS c4_string2, " + " " + formatter.FormatNull("TEXT") + " AS c5_string3, " + " Metadata.type AS c6_int1, " + + revisionInC7 + + " " + formatter.FormatNull("INT") + " AS c8_int3, " + " " + formatter.FormatNull("BIGINT") + " AS c9_big_int1, " + " " + formatter.FormatNull("BIGINT") + " AS c10_big_int2 " + " FROM Metadata " + " INNER JOIN OneInstance ON Metadata.id = OneInstance.instanceInternalId"; + + sql += " UNION ALL SELECT" + " " TOSTRING(QUERY_ONE_INSTANCE_ATTACHMENTS) " AS c0_queryId, " + " parentInternalId AS c1_internalId, " + " " + formatter.FormatNull("BIGINT") + " AS c2_rowNumber, " + " uuid AS c3_string1, " + " uncompressedHash AS c4_string2, " + " compressedHash AS c5_string3, " + " fileType AS c6_int1, " + + revisionInC7 + + " compressionType AS c8_int3, " + " compressedSize AS c9_big_int1, " + " uncompressedSize AS c10_big_int2 " + " FROM AttachedFiles " + " INNER JOIN OneInstance ON AttachedFiles.id = OneInstance.instanceInternalId"; + + // sql += " ) "; + + } + + sql += " ORDER BY c0_queryId, c2_rowNumber"; // this is really important to make sure that the Lookup query is the first one to provide results since we use it to create the responses element ! + + std::unique_ptr<DatabaseManager::StatementBase> statement; + if (manager.GetDialect() == Dialect_MySQL) + { // TODO: investigate why "complex" cached statement do not seem to work properly in MySQL + statement.reset(new DatabaseManager::StandaloneStatement(manager, sql)); + } + else + { + statement.reset(new DatabaseManager::CachedStatement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql)); + } + + statement->Execute(formatter.GetDictionary()); + + // LOG(INFO) << sql; + + std::map<int64_t, Orthanc::DatabasePluginMessages::Find_Response*> responses; + + while (!statement->IsDone()) + { + int32_t queryId = statement->ReadInteger32(C0_QUERY_ID); + int64_t internalId = statement->ReadInteger64(C1_INTERNAL_ID); + + assert(queryId == QUERY_LOOKUP || responses.find(internalId) != responses.end()); // the QUERY_LOOKUP must be read first and must create the response before any other query tries to populate the fields + + // LOG(INFO) << queryId << " " << statement->ReadString(C3_STRING_1); + + switch (queryId) + { + case QUERY_LOOKUP: + responses[internalId] = response.add_find(); + responses[internalId]->set_public_id(statement->ReadString(C3_STRING_1)); + responses[internalId]->set_internal_id(internalId); + break; + + case QUERY_LABELS: + responses[internalId]->add_labels(statement->ReadString(C3_STRING_1)); + break; + + case QUERY_MAIN_DICOM_TAGS: + { + Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level()); + Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags(); + + tag->set_value(statement->ReadString(C3_STRING_1)); + tag->set_group(statement->ReadInteger32(C6_INT_1)); + tag->set_element(statement->ReadInteger32(C7_INT_2)); + }; break; + + case QUERY_PARENT_MAIN_DICOM_TAGS: + { + Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 1)); + Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags(); + + tag->set_value(statement->ReadString(C3_STRING_1)); + tag->set_group(statement->ReadInteger32(C6_INT_1)); + tag->set_element(statement->ReadInteger32(C7_INT_2)); + }; break; + + case QUERY_GRAND_PARENT_MAIN_DICOM_TAGS: + { + Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 2)); + Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags(); + + tag->set_value(statement->ReadString(C3_STRING_1)); + tag->set_group(statement->ReadInteger32(C6_INT_1)); + tag->set_element(statement->ReadInteger32(C7_INT_2)); + }; break; + + case QUERY_CHILDREN_IDENTIFIERS: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1)); + content->add_identifiers(statement->ReadString(C3_STRING_1)); + content->set_count(content->identifiers_size()); + }; break; + + case QUERY_CHILDREN_COUNT: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1)); + content->set_count(statement->ReadInteger64(C9_BIG_INT_1)); + }; break; + + case QUERY_CHILDREN_MAIN_DICOM_TAGS: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1)); + Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags(); + tag->set_value(statement->ReadString(C3_STRING_1)); // TODO: handle sequences ?? + tag->set_group(statement->ReadInteger32(C6_INT_1)); + tag->set_element(statement->ReadInteger32(C7_INT_2)); + }; break; + + case QUERY_CHILDREN_METADATA: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 1)); + Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata(); + + metadata->set_value(statement->ReadString(C3_STRING_1)); + metadata->set_key(statement->ReadInteger32(C6_INT_1)); + metadata->set_revision(0); // Setting a revision is not required in this case, as of Orthanc 1.12.5 + }; break; + + case QUERY_GRAND_CHILDREN_IDENTIFIERS: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2)); + content->add_identifiers(statement->ReadString(C3_STRING_1)); + content->set_count(content->identifiers_size()); + }; break; + + case QUERY_GRAND_CHILDREN_COUNT: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2)); + content->set_count(statement->ReadInteger64(C9_BIG_INT_1)); + }; break; + + case QUERY_GRAND_CHILDREN_MAIN_DICOM_TAGS: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2)); + Orthanc::DatabasePluginMessages::Find_Response_Tag* tag = content->add_main_dicom_tags(); + + tag->set_value(statement->ReadString(C3_STRING_1)); // TODO: handle sequences ?? + tag->set_group(statement->ReadInteger32(C6_INT_1)); + tag->set_element(statement->ReadInteger32(C7_INT_2)); + }; break; + + case QUERY_GRAND_CHILDREN_METADATA: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 2)); + Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata(); + + metadata->set_value(statement->ReadString(C3_STRING_1)); + metadata->set_key(statement->ReadInteger32(C6_INT_1)); + metadata->set_revision(0); // Setting a revision is not required in this case, as of Orthanc 1.12.5 + }; break; + + case QUERY_GRAND_GRAND_CHILDREN_IDENTIFIERS: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 3)); + content->add_identifiers(statement->ReadString(C3_STRING_1)); + content->set_count(content->identifiers_size()); + }; break; + + case QUERY_GRAND_GRAND_CHILDREN_COUNT: + { + Orthanc::DatabasePluginMessages::Find_Response_ChildrenContent* content = GetChildrenContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() + 3)); + content->set_count(statement->ReadInteger64(C9_BIG_INT_1)); + }; break; + + case QUERY_ATTACHMENTS: + { + Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_attachments(); + + attachment->set_uuid(statement->ReadString(C3_STRING_1)); + attachment->set_uncompressed_hash(statement->ReadString(C4_STRING_2)); + attachment->set_compressed_hash(statement->ReadString(C5_STRING_3)); + attachment->set_content_type(statement->ReadInteger32(C6_INT_1)); + attachment->set_compression_type(statement->ReadInteger32(C8_INT_3)); + attachment->set_compressed_size(statement->ReadInteger64(C9_BIG_INT_1)); + attachment->set_uncompressed_size(statement->ReadInteger64(C10_BIG_INT_2)); + + if (!statement->IsNull(C7_INT_2)) // revision can be null for files that have been atttached by older Orthanc versions + { + responses[internalId]->add_attachments_revisions(statement->ReadInteger32(C7_INT_2)); + } + else + { + responses[internalId]->add_attachments_revisions(0); + } + }; break; + + case QUERY_METADATA: + { + Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], request.level()); + Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata(); + + metadata->set_value(statement->ReadString(C3_STRING_1)); + metadata->set_key(statement->ReadInteger32(C6_INT_1)); + + if (!statement->IsNull(C7_INT_2)) // revision can be null for metadata that have been created by older Orthanc versions + { + metadata->set_revision(statement->ReadInteger32(C7_INT_2)); + } + else + { + metadata->set_revision(0); + } + }; break; + + case QUERY_PARENT_METADATA: + { + Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 1)); + Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata(); + + metadata->set_value(statement->ReadString(C3_STRING_1)); + metadata->set_key(statement->ReadInteger32(C6_INT_1)); + + if (!statement->IsNull(C7_INT_2)) // revision can be null for metadata that have been created by older Orthanc versions + { + metadata->set_revision(statement->ReadInteger32(C7_INT_2)); + } + else + { + metadata->set_revision(0); + } + }; break; + + case QUERY_GRAND_PARENT_METADATA: + { + Orthanc::DatabasePluginMessages::Find_Response_ResourceContent* content = GetResourceContent(responses[internalId], static_cast<Orthanc::DatabasePluginMessages::ResourceType>(request.level() - 2)); + Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = content->add_metadata(); + + metadata->set_value(statement->ReadString(C3_STRING_1)); + metadata->set_key(statement->ReadInteger32(C6_INT_1)); + + if (!statement->IsNull(C7_INT_2)) // revision can be null for metadata that have been created by older Orthanc versions + { + metadata->set_revision(statement->ReadInteger32(C7_INT_2)); + } + else + { + metadata->set_revision(0); + } + }; break; + + case QUERY_PARENT_IDENTIFIER: + { + responses[internalId]->set_parent_public_id(statement->ReadString(C3_STRING_1)); + }; break; + + case QUERY_ONE_INSTANCE_IDENTIFIER: + { + responses[internalId]->set_one_instance_public_id(statement->ReadString(C3_STRING_1)); + }; break; + case QUERY_ONE_INSTANCE_METADATA: + { + Orthanc::DatabasePluginMessages::Find_Response_Metadata* metadata = responses[internalId]->add_one_instance_metadata(); + + metadata->set_value(statement->ReadString(C3_STRING_1)); + metadata->set_key(statement->ReadInteger32(C6_INT_1)); + + if (!statement->IsNull(C7_INT_2)) // revision can be null for metadata that have been created by older Orthanc versions + { + metadata->set_revision(statement->ReadInteger32(C7_INT_2)); + } + else + { + metadata->set_revision(0); + } + }; break; + case QUERY_ONE_INSTANCE_ATTACHMENTS: + { + Orthanc::DatabasePluginMessages::FileInfo* attachment = responses[internalId]->add_one_instance_attachments(); + + attachment->set_uuid(statement->ReadString(C3_STRING_1)); + attachment->set_uncompressed_hash(statement->ReadString(C4_STRING_2)); + attachment->set_compressed_hash(statement->ReadString(C5_STRING_3)); + attachment->set_content_type(statement->ReadInteger32(C6_INT_1)); + attachment->set_compression_type(statement->ReadInteger32(C8_INT_3)); + attachment->set_compressed_size(statement->ReadInteger64(C9_BIG_INT_1)); + attachment->set_uncompressed_size(statement->ReadInteger64(C10_BIG_INT_2)); + }; break; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + statement->Next(); + } + } +#endif }
--- a/Framework/Plugins/IndexBackend.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/IndexBackend.h Mon Dec 16 16:36:12 2024 +0100 @@ -42,6 +42,7 @@ class LookupFormatter; OrthancPluginContext* context_; + bool readOnly_; boost::shared_mutex outputFactoryMutex_; std::unique_ptr<IDatabaseBackendOutput::IFactory> outputFactory_; @@ -53,19 +54,27 @@ virtual void ClearRemainingAncestor(DatabaseManager& manager); + virtual bool HasChildCountTable() const = 0; + void SignalDeletedFiles(IDatabaseBackendOutput& output, DatabaseManager& manager); void SignalDeletedResources(IDatabaseBackendOutput& output, DatabaseManager& manager); + bool IsReadOnly() + { + return readOnly_; + } + private: void ReadChangesInternal(IDatabaseBackendOutput& output, bool& done, DatabaseManager& manager, DatabaseManager::CachedStatement& statement, const Dictionary& args, - uint32_t limit); + uint32_t limit, + bool returnFirstResults); void ReadExportedResourcesInternal(IDatabaseBackendOutput& output, bool& done, @@ -74,7 +83,8 @@ uint32_t limit); public: - explicit IndexBackend(OrthancPluginContext* context); + explicit IndexBackend(OrthancPluginContext* context, + bool readOnly); virtual OrthancPluginContext* GetContext() ORTHANC_OVERRIDE { @@ -130,7 +140,15 @@ DatabaseManager& manager, int64_t since, uint32_t limit) ORTHANC_OVERRIDE; - + + virtual void GetChangesExtended(IDatabaseBackendOutput& output, + bool& done /*out*/, + DatabaseManager& manager, + int64_t since, + int64_t to, + const std::set<uint32_t>& changeTypes, + uint32_t limit) ORTHANC_OVERRIDE; + virtual void GetChildrenInternalId(std::list<int64_t>& target /*out*/, DatabaseManager& manager, int64_t id) ORTHANC_OVERRIDE; @@ -420,6 +438,34 @@ virtual uint64_t MeasureLatency(DatabaseManager& manager) ORTHANC_OVERRIDE; +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + // New primitives since Orthanc 1.12.5 + virtual bool HasExtendedChanges() const ORTHANC_OVERRIDE + { + return true; + } + + virtual bool HasFindSupport() const ORTHANC_OVERRIDE; + + virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE; + + virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE; +#endif + + virtual bool HasPerformDbHousekeeping() ORTHANC_OVERRIDE + { + return false; + } + + virtual void PerformDbHousekeeping(DatabaseManager& manager) ORTHANC_OVERRIDE + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + /** * "maxDatabaseRetries" is to handle * "OrthancPluginErrorCode_DatabaseCannotSerialize" if there is a @@ -428,7 +474,8 @@ **/ static void Register(IndexBackend* backend, size_t countConnections, - unsigned int maxDatabaseRetries); + unsigned int maxDatabaseRetries, + unsigned int housekeepingDelaySeconds); static void Finalize();
--- a/Framework/Plugins/IndexConnectionsPool.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/IndexConnectionsPool.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -23,6 +23,9 @@ #include "IndexConnectionsPool.h" +#include <Logging.h> + + namespace OrthancDatabases { class IndexConnectionsPool::ManagerReference : public Orthanc::IDynamicObject @@ -44,10 +47,44 @@ }; + void IndexConnectionsPool::HousekeepingThread(IndexConnectionsPool* that) + { + 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_) + { + Accessor accessor(*that); + + try + { + 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) : + size_t countConnections, + unsigned int houseKeepingDelaySeconds) : backend_(backend), - countConnections_(countConnections) + countConnections_(countConnections), + housekeepingContinue_(true), + housekeepingDelay_(boost::posix_time::seconds(houseKeepingDelaySeconds)) { if (countConnections == 0) { @@ -58,6 +95,12 @@ { 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(); @@ -104,7 +147,15 @@ { assert(*it != NULL); availableConnections_.Enqueue(new ManagerReference(**it)); - } + } + + // Start the housekeeping thread + housekeepingContinue_ = true; + + if (backend_->HasPerformDbHousekeeping()) + { + housekeepingThread_ = boost::thread(HousekeepingThread, this); + } } else { @@ -115,6 +166,15 @@ void IndexConnectionsPool::CloseConnections() { + { + // Stop the housekeeping thread + housekeepingContinue_ = false; + if (housekeepingThread_.joinable()) + { + housekeepingThread_.join(); + } + } + boost::unique_lock<boost::shared_mutex> lock(connectionsMutex_); if (connections_.size() != countConnections_)
--- a/Framework/Plugins/IndexConnectionsPool.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/IndexConnectionsPool.h Mon Dec 16 16:36:12 2024 +0100 @@ -29,6 +29,7 @@ #include <MultiThreading/SharedMessageQueue.h> #include <list> +#include <boost/thread.hpp> namespace OrthancDatabases { @@ -43,10 +44,16 @@ 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); public: IndexConnectionsPool(IndexBackend* backend /* takes ownership */, - size_t countConnections); + size_t countConnections, + unsigned int houseKeepingDelaySeconds); ~IndexConnectionsPool();
--- a/Framework/Plugins/IndexUnitTests.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/Plugins/IndexUnitTests.h Mon Dec 16 16:36:12 2024 +0100 @@ -29,8 +29,6 @@ #include <Compatibility.h> // For std::unique_ptr<> -#include <orthanc/OrthancCDatabasePlugin.h> - #include <gtest/gtest.h> #include <list> @@ -232,13 +230,13 @@ ImplicitTransaction::SetErrorOnDoubleExecution(true); #if ORTHANC_ENABLE_POSTGRESQL == 1 - PostgreSQLIndex db(&context, globalParameters_); + PostgreSQLIndex db(&context, globalParameters_, false); db.SetClearAll(true); #elif ORTHANC_ENABLE_MYSQL == 1 - MySQLIndex db(&context, globalParameters_); + MySQLIndex db(&context, globalParameters_, false); db.SetClearAll(true); #elif ORTHANC_ENABLE_ODBC == 1 - OdbcIndex db(&context, connectionString_); + OdbcIndex db(&context, connectionString_, false); #elif ORTHANC_ENABLE_SQLITE == 1 // Must be the last one SQLiteIndex db(&context); // Open in memory #else
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Plugins/MessagesToolbox.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -0,0 +1,172 @@ +/** + * 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-2024 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2024 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 "MessagesToolbox.h" + + +namespace OrthancDatabases +{ + namespace MessagesToolbox + { + Orthanc::ResourceType Convert(Orthanc::DatabasePluginMessages::ResourceType resourceType) + { + switch (resourceType) + { + case Orthanc::DatabasePluginMessages::RESOURCE_PATIENT: + return Orthanc::ResourceType_Patient; + + case Orthanc::DatabasePluginMessages::RESOURCE_STUDY: + return Orthanc::ResourceType_Study; + + case Orthanc::DatabasePluginMessages::RESOURCE_SERIES: + return Orthanc::ResourceType_Series; + + case Orthanc::DatabasePluginMessages::RESOURCE_INSTANCE: + return Orthanc::ResourceType_Instance; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + } + + + OrthancPluginResourceType ConvertToPlainC(Orthanc::ResourceType type) + { + switch (type) + { + case Orthanc::ResourceType_Patient: + return OrthancPluginResourceType_Patient; + + case Orthanc::ResourceType_Study: + return OrthancPluginResourceType_Study; + + case Orthanc::ResourceType_Series: + return OrthancPluginResourceType_Series; + + case Orthanc::ResourceType_Instance: + return OrthancPluginResourceType_Instance; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + } + + + Orthanc::ResourceType Convert(OrthancPluginResourceType type) + { + switch (type) + { + case OrthancPluginResourceType_Patient: + return Orthanc::ResourceType_Patient; + + case OrthancPluginResourceType_Study: + return Orthanc::ResourceType_Study; + + case OrthancPluginResourceType_Series: + return Orthanc::ResourceType_Series; + + case OrthancPluginResourceType_Instance: + return Orthanc::ResourceType_Instance; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + } + + +#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 + OrthancPluginConstraintType ConvertToPlainC(ConstraintType constraint) + { + switch (constraint) + { + case ConstraintType_Equal: + return OrthancPluginConstraintType_Equal; + + case ConstraintType_GreaterOrEqual: + return OrthancPluginConstraintType_GreaterOrEqual; + + case ConstraintType_SmallerOrEqual: + return OrthancPluginConstraintType_SmallerOrEqual; + + case ConstraintType_Wildcard: + return OrthancPluginConstraintType_Wildcard; + + case ConstraintType_List: + return OrthancPluginConstraintType_List; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + } +#endif + + +#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 + ConstraintType Convert(OrthancPluginConstraintType constraint) + { + switch (constraint) + { + case OrthancPluginConstraintType_Equal: + return ConstraintType_Equal; + + case OrthancPluginConstraintType_GreaterOrEqual: + return ConstraintType_GreaterOrEqual; + + case OrthancPluginConstraintType_SmallerOrEqual: + return ConstraintType_SmallerOrEqual; + + case OrthancPluginConstraintType_Wildcard: + return ConstraintType_Wildcard; + + case OrthancPluginConstraintType_List: + return ConstraintType_List; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + } +#endif + + + Orthanc::DatabasePluginMessages::ResourceType ConvertToProtobuf(OrthancPluginResourceType resourceType) + { + switch (resourceType) + { + case OrthancPluginResourceType_Patient: + return Orthanc::DatabasePluginMessages::RESOURCE_PATIENT; + + case OrthancPluginResourceType_Study: + return Orthanc::DatabasePluginMessages::RESOURCE_STUDY; + + case OrthancPluginResourceType_Series: + return Orthanc::DatabasePluginMessages::RESOURCE_SERIES; + + case OrthancPluginResourceType_Instance: + return Orthanc::DatabasePluginMessages::RESOURCE_INSTANCE; + + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); + } + } + } +}
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/Framework/Plugins/MessagesToolbox.h Mon Dec 16 16:36:12 2024 +0100 @@ -0,0 +1,92 @@ +/** + * 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-2024 Orthanc Team SRL, Belgium + * Copyright (C) 2021-2024 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 <orthanc/OrthancCDatabasePlugin.h> +#include <OrthancDatabasePlugin.pb.h> + +// Ensure that "ORTHANC_PLUGINS_VERSION_IS_ABOVE" is defined +#include "../../Resources/Orthanc/Plugins/OrthancPluginCppWrapper.h" + +#define ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT 0 + +#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) +# if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 5, 2) +# undef ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT +# define ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT 1 +# endif +#endif + + +#define ORTHANC_PLUGINS_HAS_INTEGRATED_FIND 0 + +#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) +# if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) +# undef ORTHANC_PLUGINS_HAS_INTEGRATED_FIND +# define ORTHANC_PLUGINS_HAS_INTEGRATED_FIND 1 +# endif +#endif + + +#define ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED 0 + +#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) +# if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) +# undef ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED +# define ORTHANC_PLUGINS_HAS_CHANGES_EXTENDED 1 +# endif +#endif + + +#include <Enumerations.h> + + +namespace OrthancDatabases +{ + enum ConstraintType + { + ConstraintType_Equal, + ConstraintType_SmallerOrEqual, + ConstraintType_GreaterOrEqual, + ConstraintType_Wildcard, + ConstraintType_List + }; + + namespace MessagesToolbox + { + Orthanc::ResourceType Convert(Orthanc::DatabasePluginMessages::ResourceType resourceType); + + OrthancPluginResourceType ConvertToPlainC(Orthanc::ResourceType type); + + Orthanc::ResourceType Convert(OrthancPluginResourceType type); + +#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 + OrthancPluginConstraintType ConvertToPlainC(ConstraintType constraint); +#endif + +#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 + ConstraintType Convert(OrthancPluginConstraintType constraint); +#endif + } +}
--- a/Framework/PostgreSQL/PostgreSQLDatabase.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -210,6 +210,26 @@ return !result.IsDone(); } + bool PostgreSQLDatabase::DoesIndexExist(const std::string& name) + { + std::string lower; + Orthanc::Toolbox::ToLowerCase(lower, name); + + // http://stackoverflow.com/a/24089729/881731 + + PostgreSQLStatement statement(*this, + "SELECT 1 FROM pg_catalog.pg_class c " + "JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace " + "WHERE n.nspname = 'public' AND c.relkind='i' " + "AND c.relname=$1"); + + statement.DeclareInputString(0); + statement.BindString(0, lower); + + PostgreSQLResult result(statement); + return !result.IsDone(); + } + bool PostgreSQLDatabase::DoesColumnExist(const std::string& tableName, const std::string& columnName) @@ -290,6 +310,11 @@ return db_.DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return db_.DoesIndexExist(name.c_str()); + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false;
--- a/Framework/PostgreSQL/PostgreSQLDatabase.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.h Mon Dec 16 16:36:12 2024 +0100 @@ -74,6 +74,8 @@ void ExecuteMultiLines(const std::string& sql); + bool DoesIndexExist(const std::string& name); + bool DoesTableExist(const std::string& name); bool DoesColumnExist(const std::string& tableName,
--- a/Framework/PostgreSQL/PostgreSQLParameters.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/PostgreSQL/PostgreSQLParameters.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -102,7 +102,7 @@ maxConnectionRetries_ = configuration.GetUnsignedIntegerValue("MaximumConnectionRetries", 10); connectionRetryInterval_ = configuration.GetUnsignedIntegerValue("ConnectionRetryInterval", 5); - std::string transactionMode = configuration.GetStringValue("TransactionMode", "Serializable"); + std::string transactionMode = configuration.GetStringValue("TransactionMode", "ReadCommitted"); if (transactionMode == "ReadCommitted") { LOG(WARNING) << "PostgreSQL: using READ COMMITTED transaction mode";
--- a/Framework/PostgreSQL/PostgreSQLStatement.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/PostgreSQL/PostgreSQLStatement.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -26,6 +26,7 @@ #include "../Common/BinaryStringValue.h" #include "../Common/InputFileValue.h" +#include "../Common/Integer32Value.h" #include "../Common/Integer64Value.h" #include "../Common/NullValue.h" #include "../Common/ResultBase.h" @@ -338,6 +339,10 @@ DeclareInputInteger64(i); break; + case ValueType_Integer32: + DeclareInputInteger(i); + break; + case ValueType_Utf8String: DeclareInputString(i); break; @@ -529,6 +534,10 @@ BindInteger64(i, dynamic_cast<const Integer64Value&>(parameters.GetValue(name)).GetValue()); break; + case ValueType_Integer32: + BindInteger(i, dynamic_cast<const Integer32Value&>(parameters.GetValue(name)).GetValue()); + break; + case ValueType_Null: BindNull(i); break;
--- a/Framework/PostgreSQL/PostgreSQLTransaction.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/PostgreSQL/PostgreSQLTransaction.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -65,37 +65,28 @@ LOG(ERROR) << "PostgreSQL: Beginning a transaction twice!"; throw Orthanc::OrthancException(Orthanc::ErrorCode_BadSequenceOfCalls); } - - database_.ExecuteMultiLines("BEGIN"); + std::string transactionStatement; // if not defined, will use the default DB transaction isolation level switch (type) { case TransactionType_ReadWrite: { - std::string statement = database_.GetReadWriteTransactionStatement(); - if (!statement.empty()) // if not defined, will use the default DB transaction isolation level - { - database_.ExecuteMultiLines(statement); - } - + transactionStatement = database_.GetReadWriteTransactionStatement(); break; } case TransactionType_ReadOnly: { - std::string statement = database_.GetReadOnlyTransactionStatement(); - if (!statement.empty()) // if not defined, will use the default DB transaction isolation level - { - database_.ExecuteMultiLines(statement); - } - + transactionStatement = database_.GetReadOnlyTransactionStatement(); break; } default: throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); } - + + database_.ExecuteMultiLines("BEGIN; " + transactionStatement); + isOpen_ = true; }
--- a/Framework/PostgreSQL/PostgreSQLTransaction.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/PostgreSQL/PostgreSQLTransaction.h Mon Dec 16 16:36:12 2024 +0100 @@ -67,6 +67,11 @@ return database_.DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return database_.DoesIndexExist(name.c_str()); + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false;
--- a/Framework/SQLite/SQLiteDatabase.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/SQLite/SQLiteDatabase.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -77,6 +77,11 @@ return db_.GetObject().DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return false; + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false;
--- a/Framework/SQLite/SQLiteTransaction.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Framework/SQLite/SQLiteTransaction.h Mon Dec 16 16:36:12 2024 +0100 @@ -69,6 +69,11 @@ return database_.GetObject().DoesTableExist(name.c_str()); } + virtual bool DoesIndexExist(const std::string& name) ORTHANC_OVERRIDE + { + return false; // Not implemented yet + } + virtual bool DoesTriggerExist(const std::string& name) ORTHANC_OVERRIDE { return false;
--- a/MySQL/NEWS Mon Sep 09 15:21:14 2024 +0200 +++ b/MySQL/NEWS Mon Dec 16 16:36:12 2024 +0100 @@ -1,3 +1,9 @@ +Pending changes in the mainline +=============================== + +* Added support for ExtendedChanges: + - changes?type=...&to=... +* Added support for ExtendedFind * Fixed a memory leak when executing non cached SQL statements (rarely used)
--- a/MySQL/Plugins/IndexPlugin.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/MySQL/Plugins/IndexPlugin.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -29,7 +29,9 @@ #include <Logging.h> #include <Toolbox.h> -#include <google/protobuf/any.h> +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) +# include <google/protobuf/any.h> +#endif #define ORTHANC_PLUGIN_NAME "mysql-index" @@ -37,7 +39,9 @@ { ORTHANC_PLUGINS_API int32_t OrthancPluginInitialize(OrthancPluginContext* context) { +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) GOOGLE_PROTOBUF_VERIFY_VERSION; +#endif if (!OrthancDatabases::InitializePlugin(context, ORTHANC_PLUGIN_NAME, "MySQL", true)) { @@ -67,14 +71,22 @@ return 0; } + bool readOnly = configuration.GetBooleanValue("ReadOnly", false); + + if (readOnly) + { + LOG(WARNING) << "READ-ONLY SYSTEM: the Database plugin is working in read-only mode"; + } + try { const size_t countConnections = mysql.GetUnsignedIntegerValue("IndexConnectionsCount", 1); + const unsigned int housekeepingDelaySeconds = 5; // TODO - PARAMETER OrthancDatabases::MySQLParameters parameters(mysql, configuration); OrthancDatabases::IndexBackend::Register( - new OrthancDatabases::MySQLIndex(context, parameters), countConnections, - parameters.GetMaxConnectionRetries()); + new OrthancDatabases::MySQLIndex(context, parameters, readOnly), countConnections, + parameters.GetMaxConnectionRetries(), housekeepingDelaySeconds); } catch (Orthanc::OrthancException& e) { @@ -99,7 +111,10 @@ OrthancDatabases::MySQLDatabase::GlobalFinalization(); Orthanc::HttpClient::GlobalFinalize(); Orthanc::Toolbox::FinalizeOpenSsl(); + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) google::protobuf::ShutdownProtobufLibrary(); +#endif }
--- a/MySQL/Plugins/MySQLIndex.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/MySQL/Plugins/MySQLIndex.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -39,8 +39,9 @@ namespace OrthancDatabases { MySQLIndex::MySQLIndex(OrthancPluginContext* context, - const MySQLParameters& parameters) : - IndexBackend(context), + const MySQLParameters& parameters, + bool readOnly) : + IndexBackend(context, readOnly), parameters_(parameters), clearAll_(false) { @@ -593,4 +594,32 @@ } } #endif + + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + bool MySQLIndex::HasFindSupport() const + { + // TODO-FIND + return true; + } +#endif + + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + // void MySQLIndex::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, + // DatabaseManager& manager, + // const Orthanc::DatabasePluginMessages::Find_Request& request) + // { + // // TODO-FIND + // throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + // } + + // void MySQLIndex::ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response, + // DatabaseManager& manager, + // const Orthanc::DatabasePluginMessages::Find_Request& request) + // { + // // TODO-FIND + // throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + // } +#endif }
--- a/MySQL/Plugins/MySQLIndex.h Mon Sep 09 15:21:14 2024 +0200 +++ b/MySQL/Plugins/MySQLIndex.h Mon Dec 16 16:36:12 2024 +0100 @@ -34,9 +34,16 @@ MySQLParameters parameters_; bool clearAll_; + protected: + virtual bool HasChildCountTable() const + { + return false; + } + public: MySQLIndex(OrthancPluginContext* context, - const MySQLParameters& parameters); + const MySQLParameters& parameters, + bool readOnly); void SetClearAll(bool clear) { @@ -85,5 +92,19 @@ { return true; } + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + virtual bool HasFindSupport() const ORTHANC_OVERRIDE; +#endif + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + // virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, + // DatabaseManager& manager, + // const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE; + + // virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response, + // DatabaseManager& manager, + // const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE; +#endif }; }
--- a/MySQL/UnitTests/UnitTestsMain.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/MySQL/UnitTests/UnitTestsMain.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -49,7 +49,7 @@ OrthancDatabases::MySQLParameters lock = globalParameters_; lock.SetLock(true); - OrthancDatabases::MySQLIndex db1(NULL, noLock); + OrthancDatabases::MySQLIndex db1(NULL, noLock, false); db1.SetClearAll(true); std::list<OrthancDatabases::IdentifierTag> identifierTags; @@ -57,15 +57,15 @@ std::unique_ptr<OrthancDatabases::DatabaseManager> manager1(OrthancDatabases::IndexBackend::CreateSingleDatabaseManager(db1, false, identifierTags)); { - OrthancDatabases::MySQLIndex db2(NULL, lock); + OrthancDatabases::MySQLIndex db2(NULL, lock, false); std::unique_ptr<OrthancDatabases::DatabaseManager> manager2(OrthancDatabases::IndexBackend::CreateSingleDatabaseManager(db2, false, identifierTags)); - OrthancDatabases::MySQLIndex db3(NULL, lock); + OrthancDatabases::MySQLIndex db3(NULL, lock, false); ASSERT_THROW(OrthancDatabases::IndexBackend::CreateSingleDatabaseManager(db3, false, identifierTags), Orthanc::OrthancException); } - OrthancDatabases::MySQLIndex db4(NULL, lock); + OrthancDatabases::MySQLIndex db4(NULL, lock, false); std::unique_ptr<OrthancDatabases::DatabaseManager> manager4(OrthancDatabases::IndexBackend::CreateSingleDatabaseManager(db4, false, identifierTags)); }
--- a/Odbc/NEWS Mon Sep 09 15:21:14 2024 +0200 +++ b/Odbc/NEWS Mon Dec 16 16:36:12 2024 +0100 @@ -7,6 +7,10 @@ Optimal Orthanc runtime: 1.12.0+ * Fix check of Orthanc runtime version +* Added support for ExtendedChanges: + - changes?type=...&to=... +* Fix bug 224, error when using LIMIT with MSSQLServer + https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=224 * Fixed a memory leak when executing non cached SQL statements (rarely used)
--- a/Odbc/Plugins/IndexPlugin.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Odbc/Plugins/IndexPlugin.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -45,7 +45,9 @@ #endif -#include <google/protobuf/any.h> +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) +# include <google/protobuf/any.h> +#endif static const char* const KEY_ODBC = "Odbc"; @@ -60,7 +62,9 @@ ORTHANC_PLUGINS_API int32_t OrthancPluginInitialize(OrthancPluginContext* context) { +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) GOOGLE_PROTOBUF_VERIFY_VERSION; +#endif if (!OrthancDatabases::InitializePlugin(context, ORTHANC_PLUGIN_NAME, "ODBC", true)) { @@ -106,6 +110,7 @@ const unsigned int countConnections = odbc.GetUnsignedIntegerValue("IndexConnectionsCount", 1); const unsigned int maxConnectionRetries = odbc.GetUnsignedIntegerValue("MaximumConnectionRetries", 10); const unsigned int connectionRetryInterval = odbc.GetUnsignedIntegerValue("ConnectionRetryInterval", 5); + const unsigned int housekeepingDelaySeconds = 5; // TODO - PARAMETER if (connectionString.empty()) { @@ -113,11 +118,18 @@ "No connection string provided for the ODBC index"); } - std::unique_ptr<OrthancDatabases::OdbcIndex> index(new OrthancDatabases::OdbcIndex(context, connectionString)); + bool readOnly = configuration.GetBooleanValue("ReadOnly", false); + + if (readOnly) + { + LOG(WARNING) << "READ-ONLY SYSTEM: the Database plugin is working in read-only mode"; + } + + std::unique_ptr<OrthancDatabases::OdbcIndex> index(new OrthancDatabases::OdbcIndex(context, connectionString, readOnly)); index->SetMaxConnectionRetries(maxConnectionRetries); index->SetConnectionRetryInterval(connectionRetryInterval); - OrthancDatabases::IndexBackend::Register(index.release(), countConnections, maxConnectionRetries); + OrthancDatabases::IndexBackend::Register(index.release(), countConnections, maxConnectionRetries, housekeepingDelaySeconds); } catch (Orthanc::OrthancException& e) { @@ -138,7 +150,10 @@ { LOG(WARNING) << "ODBC index is finalizing"; OrthancDatabases::IndexBackend::Finalize(); + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) google::protobuf::ShutdownProtobufLibrary(); +#endif }
--- a/Odbc/Plugins/OdbcIndex.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/Odbc/Plugins/OdbcIndex.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -135,8 +135,9 @@ OdbcIndex::OdbcIndex(OrthancPluginContext* context, - const std::string& connectionString) : - IndexBackend(context), + const std::string& connectionString, + bool readOnly) : + IndexBackend(context, readOnly), maxConnectionRetries_(10), connectionRetryInterval_(5), connectionString_(connectionString) @@ -695,4 +696,32 @@ SignalDeletedFiles(output, manager); } + + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + bool OdbcIndex::HasFindSupport() const + { + // TODO-FIND + return false; + } +#endif + + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + // void OdbcIndex::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, + // DatabaseManager& manager, + // const Orthanc::DatabasePluginMessages::Find_Request& request) + // { + // // TODO-FIND + // throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + // } + + // void OdbcIndex::ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response, + // DatabaseManager& manager, + // const Orthanc::DatabasePluginMessages::Find_Request& request) + // { + // // TODO-FIND + // throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + // } +#endif }
--- a/Odbc/Plugins/OdbcIndex.h Mon Sep 09 15:21:14 2024 +0200 +++ b/Odbc/Plugins/OdbcIndex.h Mon Dec 16 16:36:12 2024 +0100 @@ -34,9 +34,16 @@ unsigned int connectionRetryInterval_; std::string connectionString_; + protected: + virtual bool HasChildCountTable() const + { + return false; + } + public: OdbcIndex(OrthancPluginContext* context, - const std::string& connectionString); + const std::string& connectionString, + bool readOnly); unsigned int GetMaxConnectionRetries() const { @@ -92,5 +99,19 @@ { return false; } + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + virtual bool HasFindSupport() const ORTHANC_OVERRIDE; +#endif + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + // virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, + // DatabaseManager& manager, + // const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE; + + // virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response, + // DatabaseManager& manager, + // const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE; +#endif }; }
--- a/PostgreSQL/CMakeLists.txt Mon Sep 09 15:21:14 2024 +0200 +++ b/PostgreSQL/CMakeLists.txt Mon Dec 16 16:36:12 2024 +0100 @@ -25,11 +25,11 @@ set(ORTHANC_PLUGIN_VERSION "mainline") # This is the preferred version of the Orthanc SDK for this plugin -set(ORTHANC_SDK_DEFAULT_VERSION "1.12.3") +set(ORTHANC_SDK_DEFAULT_VERSION "1.12.3") # TODO: 1.12.5 # This is the list of the versions of the Orthanc SDK against which # this plugin will compile -set(ORTHANC_SDK_COMPATIBLE_VERSIONS "1.12.3" "1.12.4") +set(ORTHANC_SDK_COMPATIBLE_VERSIONS "1.12.3" "1.12.4") # TODO: 1.12.5 # This is the minimal version of the Orthanc runtime that will provide # best performance. If the version of the Orthanc runtime is below @@ -37,13 +37,13 @@ # plugin will still start). set(ORTHANC_OPTIMAL_VERSION_MAJOR 1) set(ORTHANC_OPTIMAL_VERSION_MINOR 12) -set(ORTHANC_OPTIMAL_VERSION_REVISION 3) +set(ORTHANC_OPTIMAL_VERSION_REVISION 3) # TODO: 1.12.5 if (ORTHANC_PLUGIN_VERSION STREQUAL "mainline") set(ORTHANC_FRAMEWORK_VERSION "mainline") set(ORTHANC_FRAMEWORK_DEFAULT_SOURCE "hg") else() - set(ORTHANC_FRAMEWORK_VERSION "1.12.4") + set(ORTHANC_FRAMEWORK_VERSION "1.12.4") # TODO: 1.12.5 set(ORTHANC_FRAMEWORK_DEFAULT_SOURCE "web") endif() @@ -92,6 +92,7 @@ POSTGRESQL_PREPARE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndex.sql POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToRev1.sql POSTGRESQL_UPGRADE_REV1_TO_REV2 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev1ToRev2.sql + POSTGRESQL_UPGRADE_REV2_TO_REV3 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3b.sql )
--- a/PostgreSQL/NEWS Mon Sep 09 15:21:14 2024 +0200 +++ b/PostgreSQL/NEWS Mon Dec 16 16:36:12 2024 +0100 @@ -1,12 +1,44 @@ -Pending changes in the mainline +Pending changes in the mainline (future 7.0) =============================== -DB schema revision: 2 -Minimum plugin SDK (for build): 1.12.3 -Minimum Orthanc runtime: 1.12.3 +DB schema revision: 3 +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.5+ + +Minimal Postgresql Server version: 9 +Optimal Postgresql Server version: 11+ + +* WARNING: An Index is being replaced to improve performance. The creation + of the new index can take some time (we have observed 3 minutes on a + DB with 70M instances). Orthanc will not be available during the + creation of this index. If needed, you can create it manually before installing + the new plugin by executing these SQL commands: + + https://orthanc.uclouvain.be/hg/orthanc-databases/file/tip/PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_To_Rev3b.sql + +* WARNING: A new column has been added in the resources table when upgrading to this version. A new thread + is populating this new table and might consume DB bandwitdh and CPU. + * Fix updates from plugin version 3.3 to latest version +* Added support for ExtendedChanges: + - changes?type=...&to=... +* Performance optimizations (to be summarized before release): + - using more prepared SQL statements: + - InsertOrUpdateMetadata + - ExecuteSetResourcesContentTags + - merged BEGIN and SET TRANSACTION statements + - reduced the number of round-trips between Orthanc and the PostgreSQL server: + - e.g: when receiving an instance in an existing series, reduced the number of SQL queries from 13 to 9 * Fixed a memory leak when executing non cached SQL statements (rarely used) +* New default values for configurations: + - "IndexConnectionsCount": 50 + - "TransactionMode": "ReadCommitted" +* Introduced a new thread to perform DB Housekeeping at regular interval (5s) for the + DB plugins requiring it (currently only PostgreSQL). E.g: This avoids very long update + times in case you don't call /statistics for a long period. Release 6.2 (2024-03-25)
--- a/PostgreSQL/Plugins/IndexPlugin.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/PostgreSQL/Plugins/IndexPlugin.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -27,7 +27,9 @@ #include <Logging.h> #include <Toolbox.h> -#include <google/protobuf/any.h> +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) +# include <google/protobuf/any.h> +#endif #define ORTHANC_PLUGIN_NAME "postgresql-index" @@ -36,7 +38,9 @@ { ORTHANC_PLUGINS_API int32_t OrthancPluginInitialize(OrthancPluginContext* context) { +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) GOOGLE_PROTOBUF_VERIFY_VERSION; +#endif if (!OrthancDatabases::InitializePlugin(context, ORTHANC_PLUGIN_NAME, "PostgreSQL", true)) { @@ -65,14 +69,22 @@ return 0; } + bool readOnly = configuration.GetBooleanValue("ReadOnly", false); + + if (readOnly) + { + LOG(WARNING) << "READ-ONLY SYSTEM: the Database plugin is working in read-only mode"; + } + try { - const size_t countConnections = postgresql.GetUnsignedIntegerValue("IndexConnectionsCount", 1); + const size_t countConnections = postgresql.GetUnsignedIntegerValue("IndexConnectionsCount", 50); + const unsigned int housekeepingDelaySeconds = 5; // TODO - PARAMETER OrthancDatabases::PostgreSQLParameters parameters(postgresql); OrthancDatabases::IndexBackend::Register( - new OrthancDatabases::PostgreSQLIndex(context, parameters), countConnections, - parameters.GetMaxConnectionRetries()); + new OrthancDatabases::PostgreSQLIndex(context, parameters, readOnly), countConnections, + parameters.GetMaxConnectionRetries(), housekeepingDelaySeconds); } catch (Orthanc::OrthancException& e) { @@ -94,7 +106,10 @@ LOG(WARNING) << "PostgreSQL index is finalizing"; OrthancDatabases::IndexBackend::Finalize(); Orthanc::Toolbox::FinalizeOpenSsl(); + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) google::protobuf::ShutdownProtobufLibrary(); +#endif }
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -32,9 +32,12 @@ #include <Compatibility.h> // For std::unique_ptr<> #include <Toolbox.h> +#include <SystemToolbox.h> #include <Logging.h> #include <OrthancException.h> +#include <boost/algorithm/string/join.hpp> + namespace Orthanc { @@ -43,16 +46,19 @@ static const GlobalProperty GlobalProperty_HasCreateInstance = GlobalProperty_DatabaseInternal1; static const GlobalProperty GlobalProperty_HasFastCountResources = GlobalProperty_DatabaseInternal2; static const GlobalProperty GlobalProperty_GetLastChangeIndex = GlobalProperty_DatabaseInternal3; + static const GlobalProperty GlobalProperty_HasComputeStatisticsReadOnly = GlobalProperty_DatabaseInternal4; } namespace OrthancDatabases { PostgreSQLIndex::PostgreSQLIndex(OrthancPluginContext* context, - const PostgreSQLParameters& parameters) : - IndexBackend(context), + const PostgreSQLParameters& parameters, + bool readOnly) : + IndexBackend(context, readOnly), parameters_(parameters), - clearAll_(false) + clearAll_(false), + hkHasComputedAllMissingChildCount_(false) { } @@ -93,13 +99,20 @@ PostgreSQLDatabase& db = dynamic_cast<PostgreSQLDatabase&>(manager.GetDatabase()); - if (parameters_.HasLock()) + if (parameters_.HasLock()) { + if (IsReadOnly()) + { + LOG(ERROR) << "READ-ONLY SYSTEM: Unable to lock the database when working in ReadOnly mode."; + throw Orthanc::OrthancException(Orthanc::ErrorCode_Plugin); + } + db.AdvisoryLock(POSTGRESQL_LOCK_INDEX); } + if (!IsReadOnly()) { - // lock the full DB while checking if it needs to be create/ugraded + // lock the full DB while checking if it needs to be created/ugraded PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); if (clearAll_) @@ -134,22 +147,30 @@ throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); } - bool needToRunUpgradeFromUnknownToV1 = false; - bool needToRunUpgradeV1toV2 = false; + bool applyUpgradeFromUnknownToV1 = false; + bool applyUpgradeV1toV2 = false; + bool applyUpgradeV2toV3 = false; + bool applyPrepareIndex = false; int revision; if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) { LOG(WARNING) << "No DatabasePatchLevel found, assuming it's 1"; revision = 1; - needToRunUpgradeFromUnknownToV1 = true; - needToRunUpgradeV1toV2 = true; + applyUpgradeFromUnknownToV1 = true; + applyUpgradeV1toV2 = true; + applyUpgradeV2toV3 = true; } else if (revision == 1) { LOG(WARNING) << "DatabasePatchLevel is 1"; - needToRunUpgradeFromUnknownToV1 = true; - needToRunUpgradeV1toV2 = true; + applyUpgradeV1toV2 = true; + applyUpgradeV2toV3 = true; + } + else if (revision == 2) + { + LOG(WARNING) << "DatabasePatchLevel is 2"; + applyUpgradeV2toV3 = true; } int hasTrigram = 0; @@ -160,30 +181,24 @@ // We've observed 9 minutes on DB with 100000 studies LOG(WARNING) << "The DB schema update will try to enable trigram matching on the PostgreSQL database " << "to speed up wildcard searches. This may take several minutes"; - needToRunUpgradeV1toV2 = true; + applyUpgradeV1toV2 = true; + applyUpgradeV2toV3 = true; } int property = 0; if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_HasFastCountResources) || - property != 1) - { - needToRunUpgradeV1toV2 = true; - } - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_GetTotalSizeIsFast) || - property != 1) - { - needToRunUpgradeV1toV2 = true; - } - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex) || property != 1) { - needToRunUpgradeV1toV2 = true; + applyUpgradeV1toV2 = true; + applyUpgradeV2toV3 = true; } - if (needToRunUpgradeFromUnknownToV1) + // If you add new tests here, update the test in the "ReadOnly" code below + + applyPrepareIndex = applyUpgradeV2toV3; + + if (applyUpgradeFromUnknownToV1) { LOG(WARNING) << "Upgrading DB schema from unknown to revision 1"; std::string query; @@ -193,7 +208,7 @@ t.GetDatabaseTransaction().ExecuteMultiLines(query); } - if (needToRunUpgradeV1toV2) + if (applyUpgradeV1toV2) { LOG(WARNING) << "Upgrading DB schema from revision 1 to revision 2"; @@ -202,15 +217,45 @@ Orthanc::EmbeddedResources::GetFileResource (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV1_TO_REV2); t.GetDatabaseTransaction().ExecuteMultiLines(query); + } - // apply all idempotent changes that are in the PrepareIndexV2 + if (applyUpgradeV2toV3) + { + LOG(WARNING) << "Upgrading DB schema from revision 2 to revision 3"; + + std::string query; + + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV2_TO_REV3); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + } + + if (applyPrepareIndex) + { + // apply all idempotent changes that are in the PrepareIndex.sql ApplyPrepareIndex(t, manager); } + } t.Commit(); } } + else + { + LOG(WARNING) << "READ-ONLY SYSTEM: checking if the DB already exists and has the right schema"; + + DatabaseManager::Transaction t(manager, TransactionType_ReadOnly); + + // test if the latest "extension" has been installed + int revision; + if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel) + || revision != 3) + { + LOG(ERROR) << "READ-ONLY SYSTEM: the DB does not have the correct schema to run with this version of the plugin"; + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); + } + } } @@ -237,17 +282,23 @@ uint64_t PostgreSQLIndex::GetTotalCompressedSize(DatabaseManager& manager) { - // Fast version if extension "./FastTotalSize.sql" is installed uint64_t result; { DatabaseManager::CachedStatement statement( STATEMENT_FROM_HERE, manager, - "SELECT * FROM UpdateSingleStatistic(0)"); + "SELECT * FROM ComputeStatisticsReadOnly(0)"); statement.Execute(); - result = static_cast<uint64_t>(statement.ReadInteger64(0)); + if (statement.IsNull(0)) + { + return 0; + } + else + { + result = static_cast<uint64_t>(statement.ReadInteger64(0)); + } } // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION. This is however true when no files are being delete/added @@ -258,17 +309,23 @@ uint64_t PostgreSQLIndex::GetTotalUncompressedSize(DatabaseManager& manager) { - // Fast version if extension "./FastTotalSize.sql" is installed uint64_t result; { DatabaseManager::CachedStatement statement( STATEMENT_FROM_HERE, manager, - "SELECT * FROM UpdateSingleStatistic(1)"); + "SELECT * FROM ComputeStatisticsReadOnly(1)"); statement.Execute(); - result = static_cast<uint64_t>(statement.ReadInteger64(0)); + if (statement.IsNull(0)) + { + return 0; + } + else + { + result = static_cast<uint64_t>(statement.ReadInteger64(0)); + } } // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION. This is however true when no files are being delete/added @@ -487,23 +544,34 @@ static void ExecuteSetResourcesContentTags( DatabaseManager& manager, const std::string& table, - const std::string& variablePrefix, uint32_t count, const OrthancPluginResourcesContentTags* tags) { std::string sql; + + std::vector<std::string> resourceIds; + std::vector<std::string> groups; + std::vector<std::string> elements; + std::vector<std::string> values; + Dictionary args; for (uint32_t i = 0; i < count; i++) { - std::string name = variablePrefix + boost::lexical_cast<std::string>(i); + std::string resourceArgName = "r" + boost::lexical_cast<std::string>(i); + std::string groupArgName = "g" + boost::lexical_cast<std::string>(i); + std::string elementArgName = "e" + boost::lexical_cast<std::string>(i); + std::string valueArgName = "v" + boost::lexical_cast<std::string>(i); - args.SetUtf8Value(name, tags[i].value); - - std::string insert = ("(" + boost::lexical_cast<std::string>(tags[i].resource) + ", " + - boost::lexical_cast<std::string>(tags[i].group) + ", " + - boost::lexical_cast<std::string>(tags[i].element) + ", " + - "${" + name + "})"); + args.SetIntegerValue(resourceArgName, tags[i].resource); + args.SetInteger32Value(elementArgName, tags[i].element); + args.SetInteger32Value(groupArgName, tags[i].group); + args.SetUtf8Value(valueArgName, tags[i].value); + + std::string insert = ("(${" + resourceArgName + "}, ${" + + groupArgName + "}, ${" + + elementArgName + "}, " + + "${" + valueArgName + "})"); if (sql.empty()) { @@ -517,11 +585,17 @@ if (!sql.empty()) { - DatabaseManager::StandaloneStatement statement(manager, sql); - + DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql); + for (uint32_t i = 0; i < count; i++) { - statement.SetParameterType(variablePrefix + boost::lexical_cast<std::string>(i), + statement.SetParameterType("r" + boost::lexical_cast<std::string>(i), + ValueType_Integer64); + statement.SetParameterType("g" + boost::lexical_cast<std::string>(i), + ValueType_Integer32); + statement.SetParameterType("e" + boost::lexical_cast<std::string>(i), + ValueType_Integer32); + statement.SetParameterType("v" + boost::lexical_cast<std::string>(i), ValueType_Utf8String); } @@ -552,25 +626,24 @@ for (uint32_t i = 0; i < count; i++) { - std::string argName = "m" + boost::lexical_cast<std::string>(i); - - args.SetUtf8Value(argName, metadata[i].value); + std::string resourceArgName = "r" + boost::lexical_cast<std::string>(i); + std::string typeArgName = "t" + boost::lexical_cast<std::string>(i); + std::string valueArgName = "v" + boost::lexical_cast<std::string>(i); - resourceIds.push_back(boost::lexical_cast<std::string>(metadata[i].resource)); - metadataTypes.push_back(boost::lexical_cast<std::string>(metadata[i].metadata)); - metadataValues.push_back("${" + argName + "}"); + args.SetIntegerValue(resourceArgName, metadata[i].resource); + args.SetInteger32Value(typeArgName, metadata[i].metadata); + args.SetUtf8Value(valueArgName, metadata[i].value); + + resourceIds.push_back("${" + resourceArgName + "}"); + metadataTypes.push_back("${" + typeArgName + "}"); + metadataValues.push_back("${" + valueArgName + "}"); revisions.push_back("0"); } - std::string joinedResourceIds; - std::string joinedMetadataTypes; - std::string joinedMetadataValues; - std::string joinedRevisions; - - Orthanc::Toolbox::JoinStrings(joinedResourceIds, resourceIds, ","); - Orthanc::Toolbox::JoinStrings(joinedMetadataTypes, metadataTypes, ","); - Orthanc::Toolbox::JoinStrings(joinedMetadataValues, metadataValues, ","); - Orthanc::Toolbox::JoinStrings(joinedRevisions, revisions, ","); + std::string joinedResourceIds = boost::algorithm::join(resourceIds, ","); + std::string joinedMetadataTypes = boost::algorithm::join(metadataTypes, ","); + std::string joinedMetadataValues = boost::algorithm::join(metadataValues, ","); + std::string joinedRevisions = boost::algorithm::join(revisions, ","); std::string sql = std::string("SELECT InsertOrUpdateMetadata(ARRAY[") + joinedResourceIds + "], ARRAY[" + @@ -578,12 +651,16 @@ joinedMetadataValues + "], ARRAY[" + joinedRevisions + "])"; - DatabaseManager::StandaloneStatement statement(manager, sql); + DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE_DYNAMIC(sql), manager, sql); for (uint32_t i = 0; i < count; i++) { - statement.SetParameterType("m" + boost::lexical_cast<std::string>(i), + statement.SetParameterType("v" + boost::lexical_cast<std::string>(i), ValueType_Utf8String); + statement.SetParameterType("r" + boost::lexical_cast<std::string>(i), + ValueType_Integer64); + statement.SetParameterType("t" + boost::lexical_cast<std::string>(i), + ValueType_Integer32); } statement.Execute(args); @@ -599,11 +676,9 @@ uint32_t countMetadata, const OrthancPluginResourcesContentMetadata* metadata) { - ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i", - countIdentifierTags, identifierTags); + ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", countIdentifierTags, identifierTags); - ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t", - countMainDicomTags, mainDicomTags); + ExecuteSetResourcesContentTags(manager, "MainDicomTags", countMainDicomTags, mainDicomTags); ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata); @@ -613,8 +688,6 @@ uint64_t PostgreSQLIndex::GetResourcesCount(DatabaseManager& manager, OrthancPluginResourceType resourceType) { - // Optimized version thanks to the "FastCountResources.sql" extension - assert(OrthancPluginResourceType_Patient == 0 && OrthancPluginResourceType_Study == 1 && OrthancPluginResourceType_Series == 2 && @@ -625,15 +698,22 @@ { DatabaseManager::StandaloneStatement statement( manager, - std::string("SELECT * FROM UpdateSingleStatistic(") + boost::lexical_cast<std::string>(resourceType + 2) + ")"); // For an explanation of the "+ 2" below, check out "PrepareIndex.sql" + std::string("SELECT * FROM ComputeStatisticsReadOnly(") + boost::lexical_cast<std::string>(resourceType + 2) + ")"); // For an explanation of the "+ 2" below, check out "PrepareIndex.sql" statement.Execute(); - result = static_cast<uint64_t>(statement.ReadInteger64(0)); + if (statement.IsNull(0)) + { + return 0; + } + else + { + result = static_cast<uint64_t>(statement.ReadInteger64(0)); + } } // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION. This is however true when no files are being delete/added - assert(result == IndexBackend::GetResourcesCount(manager, resourceType)); + // assert(result == IndexBackend::GetResourcesCount(manager, resourceType)); return result; } @@ -659,4 +739,39 @@ // backward compatibility is necessary throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); } + + bool PostgreSQLIndex::HasPerformDbHousekeeping() + { + return true; + } + + void PostgreSQLIndex::PerformDbHousekeeping(DatabaseManager& manager) + { + // Compute the missing child count (table introduced in rev3) + if (!hkHasComputedAllMissingChildCount_) + { + DatabaseManager::CachedStatement statement(STATEMENT_FROM_HERE, manager, + "SELECT ComputeMissingChildCount(50)"); + + statement.Execute(); + + int64_t updatedCount = statement.ReadInteger64(0); + hkHasComputedAllMissingChildCount_ = updatedCount == 0; + + if (updatedCount > 0) + { + LOG(INFO) << "Computed " << updatedCount << " missing ChildCount entries"; + } + else + { + LOG(INFO) << "No missing ChildCount entries"; + } + } + + // Consume the statistics delta to minimize computation when calling ComputeStatisticsReadOnly + { + int64_t patientsCount, studiesCount, seriesCount, instancesCount, compressedSize, uncompressedSize; + UpdateAndGetStatistics(manager, patientsCount, studiesCount, seriesCount, instancesCount, compressedSize, uncompressedSize); + } + } }
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h Mon Sep 09 15:21:14 2024 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.h Mon Dec 16 16:36:12 2024 +0100 @@ -25,6 +25,8 @@ #include "../../Framework/Plugins/IndexBackend.h" #include "../../Framework/PostgreSQL/PostgreSQLParameters.h" +#include <boost/thread.hpp> + namespace OrthancDatabases { @@ -33,6 +35,7 @@ private: PostgreSQLParameters parameters_; bool clearAll_; + bool hkHasComputedAllMissingChildCount_; protected: virtual void ClearDeletedFiles(DatabaseManager& manager) ORTHANC_OVERRIDE; @@ -41,11 +44,17 @@ virtual void ClearRemainingAncestor(DatabaseManager& manager) ORTHANC_OVERRIDE; + virtual bool HasChildCountTable() const ORTHANC_OVERRIDE + { + return true; + } + void ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager); public: PostgreSQLIndex(OrthancPluginContext* context, - const PostgreSQLParameters& parameters); + const PostgreSQLParameters& parameters, + bool readOnly = false); void SetClearAll(bool clear) { @@ -135,5 +144,9 @@ int64_t& compressedSize, int64_t& uncompressedSize) ORTHANC_OVERRIDE; + virtual bool HasPerformDbHousekeeping() ORTHANC_OVERRIDE; + + virtual void PerformDbHousekeeping(DatabaseManager& manager) ORTHANC_OVERRIDE; + }; }
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev3ToRev2.sql Mon Dec 16 16:36:12 2024 +0100 @@ -0,0 +1,15 @@ +-- This file contains an SQL procedure to downgrade from schema Rev3 to Rev2 (version = 6, revision = 1). + -- It actually deletes the ChildCount table and triggers + -- It actually does not uninstall ChildrenIndex2 because it is anyway more efficient than + -- ChildrenIndex and is not incompatible with previous revisions. + +DROP TRIGGER IF EXISTS DecrementChildCount ON Resources; +DROP TRIGGER IF EXISTS IncrementChildCount ON Resources; +DROP TABLE ChildCount; +DROP FUNCTION UpdateChildCount; + + +-- 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, 2); -- GlobalProperty_DatabasePatchLevel
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev3bToRev2.sql Mon Dec 16 16:36:12 2024 +0100 @@ -0,0 +1,84 @@ +-- This file contains an SQL procedure to downgrade from schema Rev3 to Rev2 (version = 6, revision = 1). + -- It actually deletes the ChildCount table and triggers + -- It actually does not uninstall ChildrenIndex2 because it is anyway more efficient than + -- ChildrenIndex and is not incompatible with previous revisions. + +-- remove the childCount column in resources +DO $body$ +BEGIN + IF EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN + ALTER TABLE Resources DROP COLUMN childcount; + ELSE + raise notice 'the resources.childcount column does not exists'; + END IF; + +END $body$; + + +------------------- re-install old CreateInstance function ------------------- +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) 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) 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) 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) 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; + + + + +-- 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, 2); -- GlobalProperty_DatabasePatchLevel
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Mon Sep 09 15:21:14 2024 +0200 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Mon Dec 16 16:36:12 2024 +0100 @@ -15,6 +15,7 @@ resourceType INTEGER NOT NULL, publicId VARCHAR(64) NOT NULL, parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + childCount INTEGER, CONSTRAINT UniquePublicId UNIQUE (publicId) ); @@ -106,7 +107,23 @@ PRIMARY KEY(server, property) ); -CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId); +DO $$ +DECLARE + pg_version text; +BEGIN + SELECT version() INTO pg_version; + + IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN + -- PostgreSQL 11 or later + + -- new ChildrenIndex2 introduced in Rev3 (replacing previous ChildrenIndex) + EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)'; + ELSE + EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)'; + END IF; +END $$; + + 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); @@ -114,6 +131,7 @@ CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id); CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id); CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex3 ON DicomIdentifiers(tagGroup, tagElement, value); CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value); CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId); @@ -174,6 +192,7 @@ END; $body$ LANGUAGE plpgsql; +DROP TRIGGER IF EXISTS PatientAdded on Resources; CREATE TRIGGER PatientAdded AFTER INSERT ON Resources FOR EACH ROW @@ -203,6 +222,7 @@ END; $body$ LANGUAGE plpgsql; +DROP TRIGGER IF EXISTS ResourceDeleted on Resources; CREATE TRIGGER ResourceDeleted AFTER DELETE ON Resources FOR EACH ROW @@ -481,6 +501,7 @@ END; $body$ LANGUAGE plpgsql; +DROP TRIGGER IF EXISTS InsertedChange on Changes; CREATE TRIGGER InsertedChange AFTER INSERT ON Changes FOR EACH ROW @@ -509,7 +530,7 @@ is_new_instance := 1; BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL) RETURNING internalid INTO patient_internal_id; + 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; @@ -517,7 +538,7 @@ END; BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id) RETURNING internalid INTO study_internal_id; + 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; @@ -525,7 +546,7 @@ END; BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id) RETURNING internalid INTO series_internal_id; + 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; @@ -533,7 +554,7 @@ END; BEGIN - INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id) RETURNING internalid INTO instance_internal_id; + 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; @@ -545,17 +566,128 @@ PERFORM PatientAddedOrUpdated(patient_internal_id, 1); END IF; END; +$body$ LANGUAGE plpgsql; +-- function to compute a statistic in a ReadOnly transaction +CREATE OR REPLACE FUNCTION ComputeStatisticsReadOnly( + IN statistics_key INTEGER, + OUT accumulated_value BIGINT +) RETURNS BIGINT AS $body$ + +DECLARE + current_value BIGINT; + +BEGIN + + SELECT VALUE FROM GlobalIntegers + INTO current_value + WHERE key = statistics_key; + + SELECT COALESCE(SUM(value), 0) + current_value FROM GlobalIntegersChanges + INTO accumulated_value + WHERE key = statistics_key; + +END; +$body$ LANGUAGE plpgsql; + + +-- -- new in Rev3b + +-- Computes the childCount for a number of resources for which it has not been computed yet. +-- This is actually used only after an update to Rev3. A thread will call this function +-- at regular interval to update all missing values and stop once all values have been processed. +CREATE OR REPLACE FUNCTION ComputeMissingChildCount( + IN batch_size BIGINT, + OUT updated_rows_count BIGINT +) RETURNS BIGINT AS $body$ +BEGIN + UPDATE Resources AS r + SET childCount = (SELECT COUNT(childLevel.internalId) + FROM Resources AS childLevel + WHERE childLevel.parentId = r.internalId) + WHERE internalId IN ( + SELECT internalId FROM Resources + WHERE resourceType < 3 AND childCount IS NULL + LIMIT batch_size); + + -- Get the number of rows affected + GET DIAGNOSTICS updated_rows_count = ROW_COUNT; +END; $body$ LANGUAGE plpgsql; +DROP TRIGGER IF EXISTS IncrementChildCount on Resources; +DROP TRIGGER IF EXISTS DecrementChildCount on Resources; + +CREATE OR REPLACE FUNCTION UpdateChildCount() +RETURNS TRIGGER AS $body$ +BEGIN + IF TG_OP = 'INSERT' THEN + IF new.parentId IS NOT NULL THEN + -- try to increment the childcount from the parent + -- note that we already have the lock on this row because the parent is locked in CreateInstance + UPDATE Resources + SET childCount = childCount + 1 + WHERE internalId = new.parentId AND childCount IS NOT NULL; + + -- this should only happen for old studies whose childCount has not yet been computed + -- note: this child has already been added so it will be counted + IF NOT FOUND THEN + UPDATE Resources + SET childCount = (SELECT COUNT(*) + FROM Resources + WHERE internalId = new.parentId) + WHERE internalId = new.parentId; + END IF; + END IF; + + ELSIF TG_OP = 'DELETE' THEN + + IF old.parentId IS NOT NULL THEN + + -- Decrement the child count for the parent + -- note that we already have the lock on this row because the parent is locked in DeleteResource + UPDATE Resources + SET childCount = childCount - 1 + WHERE internalId = old.parentId AND childCount IS NOT NULL; + + -- this should only happen for old studies whose childCount has not yet been computed + -- note: this child has already been removed so it will not be counted + IF NOT FOUND THEN + UPDATE Resources + SET childCount = (SELECT COUNT(*) + FROM Resources + WHERE internalId = new.parentId) + WHERE internalId = new.parentId; + END IF; + END IF; + + END IF; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER IncrementChildCount +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE UpdateChildCount(); + +CREATE TRIGGER DecrementChildCount +AFTER DELETE ON Resources +FOR EACH ROW +WHEN (OLD.parentId IS NOT NULL) +EXECUTE PROCEDURE UpdateChildCount(); + + + -- 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); +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, 2); -- GlobalProperty_DatabasePatchLevel +INSERT INTO GlobalProperties VALUES (4, 3); -- 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 INSERT INTO GlobalProperties VALUES (12, 1); -- GlobalProperty_HasFastCountResources INSERT INTO GlobalProperties VALUES (13, 1); -- GlobalProperty_GetLastChangeIndex +INSERT INTO GlobalProperties VALUES (14, 1); -- GlobalProperty_HasComputeStatisticsReadOnly
--- a/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql Mon Sep 09 15:21:14 2024 +0200 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql Mon Dec 16 16:36:12 2024 +0100 @@ -1,4 +1,4 @@ --- This file contains part of the changes required to upgrade from Revision 1 to Revision 2 (DB version 6 and revision 1 or 2) +-- This file contains part of the changes required to upgrade from Revision 1 to Revision 2 (DB version 6 and revision 1) -- It actually contains only the changes that: -- can not be executed with an idempotent statement in SQL -- or would polute the PrepareIndex.sql
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql Mon Dec 16 16:36:12 2024 +0100 @@ -0,0 +1,31 @@ +-- This file contains part of the changes required to upgrade from Revision 2 to Revision 3 (DB version 6 and revision 2) +-- It actually contains only the changes that: + -- can not be executed with an idempotent statement in SQL + -- or would polute the PrepareIndex.sql + -- do facilite an up-time upgrade +-- This file is executed only if the current schema is in revision 2 and it is executed +-- before PrepareIndex.sql that is idempotent. + + +-- create a new ChildrenIndex2 that is replacing ChildrenIndex. +-- We create it in this partial update so it can be created while the system is up ! +DO $$ +DECLARE + pg_version text; +BEGIN + SELECT version() INTO pg_version; + + IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN + -- PostgreSQL 11 or later + EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)'; + ELSE + EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)'; + END IF; +END $$; + +DROP INDEX IF EXISTS ChildrenIndex; -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading + + +-- other changes performed in PrepareIndex.sql: + -- add ChildCount tables and triggers +
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3b.sql Mon Dec 16 16:36:12 2024 +0100 @@ -0,0 +1,44 @@ +-- This file contains part of the changes required to upgrade from Revision 2 to Revision 3 (DB version 6 and revision 2) +-- It actually contains only the changes that: + -- can not be executed with an idempotent statement in SQL + -- or would polute the PrepareIndex.sql + -- do facilite an up-time upgrade +-- This file is executed only if the current schema is in revision 2 and it is executed +-- before PrepareIndex.sql that is idempotent. + + +-- create a new ChildrenIndex2 that is replacing ChildrenIndex. +-- We create it in this partial update so it can be created while the system is up ! +DO $$ +DECLARE + pg_version text; +BEGIN + SELECT version() INTO pg_version; + + IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN + -- PostgreSQL 11 or later + EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)'; + ELSE + EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)'; + END IF; +END $$; + +DROP INDEX IF EXISTS ChildrenIndex; -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading + +-- add the childCount columns in Resources if not yet done + +DO $body$ +BEGIN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN + ALTER TABLE Resources ADD COLUMN childcount INTEGER; + ELSE + raise notice 'the resources.childcount column already exists'; + END IF; + +END $body$; + + + +-- other changes performed in PrepareIndex.sql: + -- add ChildCount tables and triggers +
--- a/Resources/CMake/DatabasesFrameworkConfiguration.cmake Mon Sep 09 15:21:14 2024 +0200 +++ b/Resources/CMake/DatabasesFrameworkConfiguration.cmake Mon Dec 16 16:36:12 2024 +0100 @@ -113,6 +113,7 @@ ${ORTHANC_DATABASES_ROOT}/Framework/Common/IResult.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Common/ImplicitTransaction.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Common/InputFileValue.cpp + ${ORTHANC_DATABASES_ROOT}/Framework/Common/Integer32Value.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Common/Integer64Value.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Common/NullValue.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Common/Query.cpp @@ -120,7 +121,7 @@ ${ORTHANC_DATABASES_ROOT}/Framework/Common/ResultFileValue.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Common/RetryDatabaseFactory.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Common/RetryDatabaseFactory.cpp - ${ORTHANC_DATABASES_ROOT}/Framework/Common/StatementLocation.cpp + ${ORTHANC_DATABASES_ROOT}/Framework/Common/StatementId.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Common/Utf8StringValue.cpp )
--- a/Resources/CMake/DatabasesPluginConfiguration.cmake Mon Sep 09 15:21:14 2024 +0200 +++ b/Resources/CMake/DatabasesPluginConfiguration.cmake Mon Dec 16 16:36:12 2024 +0100 @@ -116,6 +116,7 @@ ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/ISqlLookupFormatter.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/IndexBackend.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/IndexConnectionsPool.cpp + ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/MessagesToolbox.cpp ${ORTHANC_DATABASES_ROOT}/Framework/Plugins/StorageBackend.cpp ${ORTHANC_DATABASES_ROOT}/Resources/Orthanc/Plugins/OrthancPluginCppWrapper.cpp )
--- a/SQLite/Plugins/IndexPlugin.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/SQLite/Plugins/IndexPlugin.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -26,7 +26,9 @@ #include <Logging.h> -#include <google/protobuf/any.h> +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) +# include <google/protobuf/any.h> +#endif #define ORTHANC_PLUGIN_NAME "sqlite-index" @@ -35,7 +37,9 @@ { ORTHANC_PLUGINS_API int32_t OrthancPluginInitialize(OrthancPluginContext* context) { +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) GOOGLE_PROTOBUF_VERIFY_VERSION; +#endif if (!OrthancDatabases::InitializePlugin(context, ORTHANC_PLUGIN_NAME, "SQLite", true)) { @@ -70,7 +74,8 @@ OrthancDatabases::IndexBackend::Register( new OrthancDatabases::SQLiteIndex(context, "index.db"), // TODO parameter 1 /* only 1 connection is possible with SQLite */, - 0 /* no collision is possible, as SQLite has a global lock */); + 0 /* no collision is possible, as SQLite has a global lock */, + 0 /* housekeeping delay, unused for SQLite */); } catch (Orthanc::OrthancException& e) { @@ -91,7 +96,10 @@ { LOG(WARNING) << "SQLite index is finalizing"; OrthancDatabases::IndexBackend::Finalize(); + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 0) google::protobuf::ShutdownProtobufLibrary(); +#endif }
--- a/SQLite/Plugins/SQLiteIndex.cpp Mon Sep 09 15:21:14 2024 +0200 +++ b/SQLite/Plugins/SQLiteIndex.cpp Mon Dec 16 16:36:12 2024 +0100 @@ -191,7 +191,7 @@ SQLiteIndex::SQLiteIndex(OrthancPluginContext* context, const std::string& path) : - IndexBackend(context), + IndexBackend(context, false /* not read-only */), path_(path), fast_(true) { @@ -203,7 +203,7 @@ SQLiteIndex::SQLiteIndex(OrthancPluginContext* context) : - IndexBackend(context), + IndexBackend(context, false /* not read-only */), fast_(true) { } @@ -259,4 +259,32 @@ } } } + + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + bool SQLiteIndex::HasFindSupport() const + { + // TODO-FIND + return false; + } +#endif + + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + void SQLiteIndex::ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) + { + // TODO-FIND + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + + void SQLiteIndex::ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) + { + // TODO-FIND + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } +#endif }
--- a/SQLite/Plugins/SQLiteIndex.h Mon Sep 09 15:21:14 2024 +0200 +++ b/SQLite/Plugins/SQLiteIndex.h Mon Dec 16 16:36:12 2024 +0100 @@ -67,5 +67,26 @@ { return true; } + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + virtual bool HasFindSupport() const ORTHANC_OVERRIDE; +#endif + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + virtual void ExecuteFind(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE; +#endif + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 5) + virtual void ExecuteCount(Orthanc::DatabasePluginMessages::TransactionResponse& response, + DatabaseManager& manager, + const Orthanc::DatabasePluginMessages::Find_Request& request) ORTHANC_OVERRIDE; +#endif + + virtual bool HasChildCountTable() const ORTHANC_OVERRIDE + { + return false; + } }; }
--- a/TODO Mon Sep 09 15:21:14 2024 +0200 +++ b/TODO Mon Dec 16 16:36:12 2024 +0100 @@ -49,6 +49,10 @@ https://groups.google.com/g/orthanc-users/c/xQelEcKqL9U/m/HsvxwlkvAQAJ https://groups.google.com/g/orthanc-users/c/1bkClfZ0KBA/m/s4AlwVh3CQAJ +* use ReadCommited mode by default +* use 40 index connection count by default (not 50 because, by default, PG has 100 connections and we want at least + a few connections to remain available for sys admins if e.g, 2 Orthanc instances are running on the same server) + ----- MySQL ----- @@ -72,4 +76,4 @@ ODBC ---- -* https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=224 \ No newline at end of file +* Implement labels \ No newline at end of file