Mercurial > hg > orthanc-databases
changeset 469:302f3c2b1c34
merge pg-transactions -> mainline
| author | Alain Mazy <am@osimis.io> |
|---|---|
| date | Mon, 05 Feb 2024 09:48:11 +0100 |
| parents | 042416783518 (current diff) 8f1f26680527 (diff) |
| children | 11c6bcc9d1f2 |
| files | |
| diffstat | 30 files changed, 1953 insertions(+), 623 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Common/DatabaseManager.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/Common/DatabaseManager.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -492,8 +492,8 @@ return dynamic_cast<const Integer64Value&>(value).GetValue(); default: - //LOG(ERROR) << value.Format(); - throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + // LOG(ERROR) << value.GetType(); + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "The returned field is not of the correct type (Integer64)"); } } } @@ -511,8 +511,7 @@ if (value != static_cast<int64_t>(static_cast<int32_t>(value))) { - LOG(ERROR) << "Integer overflow"; - throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "Integer overflow"); } else { @@ -521,7 +520,18 @@ } } - + bool DatabaseManager::StatementBase::IsNull(size_t field) const + { + if (IsDone()) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); + } + else + { + return GetResultField(field).GetType() == ValueType_Null; + } + } + std::string DatabaseManager::StatementBase::ReadString(size_t field) const { const IValue& value = GetResultField(field); @@ -535,8 +545,7 @@ return dynamic_cast<const Utf8StringValue&>(value).GetContent(); default: - //LOG(ERROR) << value.Format(); - throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError, "The returned field is not of the correct type (String)"); } } @@ -560,8 +569,7 @@ } } - - void DatabaseManager::CachedStatement::Execute(const Dictionary& parameters) + void DatabaseManager::CachedStatement::ExecuteInternal(const Dictionary& parameters, bool withResults) { try { @@ -587,7 +595,14 @@ #endif */ - SetResult(GetTransaction().Execute(*statement_, parameters)); + if (withResults) + { + SetResult(GetTransaction().Execute(*statement_, parameters)); + } + else + { + GetTransaction().ExecuteWithoutResult(*statement_, parameters); + } } catch (Orthanc::OrthancException& e) { @@ -595,7 +610,18 @@ throw; } } + + + void DatabaseManager::CachedStatement::Execute(const Dictionary& parameters) + { + ExecuteInternal(parameters, true); + } + void DatabaseManager::CachedStatement::ExecuteWithoutResult(const Dictionary& parameters) + { + ExecuteInternal(parameters, false); + } + DatabaseManager::StandaloneStatement::StandaloneStatement(DatabaseManager& manager, const std::string& sql) : @@ -615,6 +641,16 @@ void DatabaseManager::StandaloneStatement::Execute(const Dictionary& parameters) { + ExecuteInternal(parameters, true); + } + + void DatabaseManager::StandaloneStatement::ExecuteWithoutResult(const Dictionary& parameters) + { + ExecuteInternal(parameters, false); + } + + void DatabaseManager::StandaloneStatement::ExecuteInternal(const Dictionary& parameters, bool withResults) + { try { std::unique_ptr<Query> query(ReleaseQuery()); @@ -626,7 +662,14 @@ statement_.reset(GetManager().GetDatabase().Compile(*query)); assert(statement_.get() != NULL); - SetResult(GetTransaction().Execute(*statement_, parameters)); + if (withResults) + { + SetResult(GetTransaction().Execute(*statement_, parameters)); + } + else + { + GetTransaction().Execute(*statement_, parameters); + } } catch (Orthanc::OrthancException& e) {
--- a/Framework/Common/DatabaseManager.h Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/Common/DatabaseManager.h Mon Feb 05 09:48:11 2024 +0100 @@ -187,6 +187,8 @@ std::string ReadString(size_t field) const; + bool IsNull(size_t field) const; + void PrintResult(std::ostream& stream) { IResult::Print(stream, GetResult()); @@ -219,6 +221,17 @@ } void Execute(const Dictionary& parameters); + + void ExecuteWithoutResult() + { + Dictionary parameters; + ExecuteWithoutResult(parameters); + } + + void ExecuteWithoutResult(const Dictionary& parameters); + + private: + void ExecuteInternal(const Dictionary& parameters, bool withResults); }; @@ -240,6 +253,17 @@ } void Execute(const Dictionary& parameters); + + void ExecuteWithoutResult() + { + Dictionary parameters; + ExecuteWithoutResult(parameters); + } + + void ExecuteWithoutResult(const Dictionary& parameters); + + private: + void ExecuteInternal(const Dictionary& parameters, bool withResults); }; }; }
--- a/Framework/Common/ResultBase.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/Common/ResultBase.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -56,11 +56,6 @@ for (size_t i = 0; i < fields_.size(); i++) { - if (fields_[i] == NULL) - { - throw Orthanc::OrthancException(Orthanc::ErrorCode_NullPointer); - } - ValueType sourceType = fields_[i]->GetType(); ValueType targetType = expectedType_[i]; @@ -95,11 +90,11 @@ for (size_t i = 0; i < fields_.size(); i++) { fields_[i] = FetchField(i); + } - if (fields_[i] == NULL) - { - throw Orthanc::OrthancException(Orthanc::ErrorCode_NullPointer); - } + if (fields_.size() == 1 && fields_[0] == NULL) // this is a "void" result + { + return; } ConvertFields();
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -431,6 +431,13 @@ response.mutable_get_system_information()->set_supports_flush_to_disk(false); response.mutable_get_system_information()->set_supports_revisions(accessor.GetBackend().HasRevisionsSupport()); response.mutable_get_system_information()->set_supports_labels(accessor.GetBackend().HasLabelsSupport()); + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 3) + response.mutable_get_system_information()->set_supports_increment_global_property(accessor.GetBackend().HasAtomicIncrementGlobalProperty()); + response.mutable_get_system_information()->set_has_update_and_get_statistics(accessor.GetBackend().HasUpdateAndGetStatistics()); + response.mutable_get_system_information()->set_has_measure_latency(accessor.GetBackend().HasMeasureLatency()); +#endif + break; } @@ -514,7 +521,16 @@ break; } - + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 3) + case Orthanc::DatabasePluginMessages::OPERATION_MEASURE_LATENCY: + { + IndexConnectionsPool::Accessor accessor(pool); + response.mutable_measure_latency()->set_latency_us(accessor.GetBackend().MeasureLatency(accessor.GetManager())); + break; + } +#endif + default: LOG(ERROR) << "Not implemented database operation from protobuf: " << request.operation(); throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange); @@ -941,7 +957,34 @@ break; } - + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 3) + case Orthanc::DatabasePluginMessages::OPERATION_UPDATE_AND_GET_STATISTICS: + { + int64_t patientsCount, studiesCount, seriesCount, instancesCount, compressedSize, uncompressedSize; + backend.UpdateAndGetStatistics(manager, patientsCount, studiesCount, seriesCount, instancesCount, compressedSize, uncompressedSize); + + response.mutable_update_and_get_statistics()->set_patients_count(patientsCount); + response.mutable_update_and_get_statistics()->set_studies_count(studiesCount); + response.mutable_update_and_get_statistics()->set_series_count(seriesCount); + response.mutable_update_and_get_statistics()->set_instances_count(instancesCount); + response.mutable_update_and_get_statistics()->set_total_compressed_size(compressedSize); + response.mutable_update_and_get_statistics()->set_total_uncompressed_size(uncompressedSize); + break; + } +#endif + +#if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 3) + case Orthanc::DatabasePluginMessages::OPERATION_INCREMENT_GLOBAL_PROPERTY: + { + int64_t value = backend.IncrementGlobalProperty(manager, request.increment_global_property().server_id().c_str(), + request.increment_global_property().property(), + request.increment_global_property().increment()); + response.mutable_increment_global_property()->set_new_value(value); + break; + } +#endif + case Orthanc::DatabasePluginMessages::OPERATION_LOOKUP_METADATA: { std::string value; @@ -1325,7 +1368,14 @@ } catch (::Orthanc::OrthancException& e) { - LOG(ERROR) << "Exception in database back-end: " << e.What(); + if (e.GetErrorCode() == ::Orthanc::ErrorCode_DatabaseCannotSerialize) + { + LOG(WARNING) << "An SQL transaction failed and will likely be retried: " << e.GetDetails(); + } + else + { + LOG(ERROR) << "Exception in database back-end: " << e.What(); + } return static_cast<OrthancPluginErrorCode>(e.GetErrorCode()); } catch (::std::runtime_error& e)
--- a/Framework/Plugins/IDatabaseBackend.h Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/Plugins/IDatabaseBackend.h Mon Feb 05 09:48:11 2024 +0100 @@ -348,5 +348,34 @@ // New in Orthanc 1.12.0 virtual void ListAllLabels(std::list<std::string>& target, DatabaseManager& manager) = 0; + + // New in Orthanc 1.12.X + virtual bool HasAtomicIncrementGlobalProperty() = 0; + + // New in Orthanc 1.12.X + virtual int64_t IncrementGlobalProperty(DatabaseManager& manager, + const char* serverIdentifier, + int32_t property, + int64_t increment) = 0; + + // New in Orthanc 1.12.X + virtual bool HasUpdateAndGetStatistics() = 0; + + // New in Orthanc 1.12.X + virtual void UpdateAndGetStatistics(DatabaseManager& manager, + int64_t& patientsCount, + int64_t& studiesCount, + int64_t& seriesCount, + int64_t& instancesCount, + int64_t& compressedSize, + int64_t& uncompressedSize) = 0; + + // New in Orthanc 1.12.X + virtual bool HasMeasureLatency() = 0; + + // New in Orthanc 1.12.X + virtual uint64_t MeasureLatency(DatabaseManager& manager) = 0; + + }; }
--- a/Framework/Plugins/IndexBackend.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/Plugins/IndexBackend.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -33,6 +33,7 @@ #include <Compatibility.h> // For std::unique_ptr<> #include <Logging.h> #include <OrthancException.h> +#include <Toolbox.h> namespace OrthancDatabases @@ -176,6 +177,16 @@ statement.IsDone()); } + void IndexBackend::ClearRemainingAncestor(DatabaseManager& manager) + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "DELETE FROM RemainingAncestor"); + + statement.Execute(); + } + + void IndexBackend::ClearDeletedFiles(DatabaseManager& manager) { @@ -433,14 +444,7 @@ { ClearDeletedFiles(manager); ClearDeletedResources(manager); - - { - DatabaseManager::CachedStatement statement( - STATEMENT_FROM_HERE, manager, - "DELETE FROM RemainingAncestor"); - - statement.Execute(); - } + ClearRemainingAncestor(manager); { DatabaseManager::CachedStatement statement( @@ -460,7 +464,6 @@ DatabaseManager::CachedStatement statement( STATEMENT_FROM_HERE, manager, "SELECT * FROM RemainingAncestor"); - statement.Execute(); if (!statement.IsDone()) @@ -473,9 +476,10 @@ assert((statement.Next(), statement.IsDone())); } } - + SignalDeletedFiles(output, manager); SignalDeletedResources(output, manager); + } @@ -1204,7 +1208,41 @@ } } - + bool IndexBackend::HasAtomicIncrementGlobalProperty() + { + return false; // currently only implemented in Postgres + } + + int64_t IndexBackend::IncrementGlobalProperty(DatabaseManager& manager, + const char* serverIdentifier, + int32_t property, + int64_t increment) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + + bool IndexBackend::HasUpdateAndGetStatistics() + { + return false; // currently only implemented in Postgres + } + + void IndexBackend::UpdateAndGetStatistics(DatabaseManager& manager, + int64_t& patientsCount, + int64_t& studiesCount, + int64_t& seriesCount, + int64_t& instancesCount, + int64_t& compressedSize, + int64_t& uncompressedSize) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + + bool IndexBackend::HasMeasureLatency() + { + return true; + } + + void IndexBackend::LookupIdentifier(std::list<int64_t>& target /*out*/, DatabaseManager& manager, OrthancPluginResourceType resourceType, @@ -2839,6 +2877,28 @@ } + uint64_t IndexBackend::MeasureLatency(DatabaseManager& manager) + { + // execute 11x the simplest statement and return the median value + std::vector<uint64_t> measures; + + for (int i = 0; i < 11; i++) + { + DatabaseManager::StandaloneStatement statement(manager, "SELECT 1"); + + Orthanc::Toolbox::ElapsedTimer timer; + + statement.ExecuteWithoutResult(); + + measures.push_back(timer.GetElapsedMicroseconds()); + } + + std::sort(measures.begin(), measures.end()); + + return measures[measures.size() / 2]; + } + + DatabaseManager* IndexBackend::CreateSingleDatabaseManager(IDatabaseBackend& backend, bool hasIdentifierTags, const std::list<IdentifierTag>& identifierTags)
--- a/Framework/Plugins/IndexBackend.h Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/Plugins/IndexBackend.h Mon Feb 05 09:48:11 2024 +0100 @@ -46,9 +46,11 @@ std::unique_ptr<IDatabaseBackendOutput::IFactory> outputFactory_; protected: - void ClearDeletedFiles(DatabaseManager& manager); + virtual void ClearDeletedFiles(DatabaseManager& manager); - void ClearDeletedResources(DatabaseManager& manager); + virtual void ClearDeletedResources(DatabaseManager& manager); + + virtual void ClearRemainingAncestor(DatabaseManager& manager); void SignalDeletedFiles(IDatabaseBackendOutput& output, DatabaseManager& manager); @@ -396,6 +398,27 @@ virtual void ListAllLabels(std::list<std::string>& target, DatabaseManager& manager) ORTHANC_OVERRIDE; + virtual bool HasAtomicIncrementGlobalProperty() ORTHANC_OVERRIDE; + + virtual int64_t IncrementGlobalProperty(DatabaseManager& manager, + const char* serverIdentifier, + int32_t property, + int64_t increment) ORTHANC_OVERRIDE; + + virtual bool HasUpdateAndGetStatistics() ORTHANC_OVERRIDE; + + virtual void UpdateAndGetStatistics(DatabaseManager& manager, + int64_t& patientsCount, + int64_t& studiesCount, + int64_t& seriesCount, + int64_t& instancesCount, + int64_t& compressedSize, + int64_t& uncompressedSize) ORTHANC_OVERRIDE; + + virtual bool HasMeasureLatency() ORTHANC_OVERRIDE; + + virtual uint64_t MeasureLatency(DatabaseManager& manager) ORTHANC_OVERRIDE; + /** * "maxDatabaseRetries" is to handle * "OrthancPluginErrorCode_DatabaseCannotSerialize" if there is a
--- a/Framework/PostgreSQL/PostgreSQLDatabase.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -159,7 +159,10 @@ void PostgreSQLDatabase::ExecuteMultiLines(const std::string& sql) { - LOG(TRACE) << "PostgreSQL: " << sql; + if (IsVerboseEnabled()) + { + LOG(INFO) << "PostgreSQL: " << sql; + } Open(); PGresult* result = PQexec(reinterpret_cast<PGconn*>(pg_), sql.c_str()); @@ -318,13 +321,15 @@ PostgreSQLDatabase::TransientAdvisoryLock::TransientAdvisoryLock( PostgreSQLDatabase& database, - int32_t lock) : + int32_t lock, + unsigned int retries, + unsigned int retryInterval) : database_(database), lock_(lock) { bool locked = true; - for (unsigned int i = 0; i < 10; i++) + for (unsigned int i = 0; i < retries; i++) { if (database_.AcquireAdvisoryLock(lock_)) { @@ -333,7 +338,7 @@ } else { - boost::this_thread::sleep(boost::posix_time::milliseconds(500)); + boost::this_thread::sleep(boost::posix_time::milliseconds(retryInterval)); } }
--- a/Framework/PostgreSQL/PostgreSQLDatabase.h Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLDatabase.h Mon Feb 05 09:48:11 2024 +0100 @@ -36,6 +36,7 @@ private: friend class PostgreSQLStatement; friend class PostgreSQLLargeObject; + friend class PostgreSQLTransaction; class Factory; @@ -59,6 +60,11 @@ void Open(); + bool IsVerboseEnabled() const + { + return parameters_.IsVerboseEnabled(); + } + bool AcquireAdvisoryLock(int32_t lock); bool ReleaseAdvisoryLock(int32_t lock); @@ -91,7 +97,9 @@ public: TransientAdvisoryLock(PostgreSQLDatabase& database, - int32_t lock); + int32_t lock, + unsigned int retries = 10, + unsigned int retryInterval = 500); ~TransientAdvisoryLock(); }; @@ -99,5 +107,17 @@ static IDatabaseFactory* CreateDatabaseFactory(const PostgreSQLParameters& parameters); static PostgreSQLDatabase* CreateDatabaseConnection(const PostgreSQLParameters& parameters); + + protected: + const char* GetReadWriteTransactionStatement() const + { + return parameters_.GetReadWriteTransactionStatement(); + } + + const char* GetReadOnlyTransactionStatement() const + { + return parameters_.GetReadOnlyTransactionStatement(); + } + }; }
--- a/Framework/PostgreSQL/PostgreSQLParameters.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLParameters.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -42,6 +42,7 @@ lock_ = true; maxConnectionRetries_ = 10; connectionRetryInterval_ = 5; + isVerboseEnabled_ = false; } @@ -94,8 +95,26 @@ lock_ = configuration.GetBooleanValue("Lock", true); // Use locking by default + isVerboseEnabled_ = configuration.GetBooleanValue("EnableVerboseLogs", false); + maxConnectionRetries_ = configuration.GetUnsignedIntegerValue("MaximumConnectionRetries", 10); connectionRetryInterval_ = configuration.GetUnsignedIntegerValue("ConnectionRetryInterval", 5); + + std::string transactionMode = configuration.GetStringValue("TransactionMode", "Serializable"); + if (transactionMode == "ReadCommitted") + { + LOG(WARNING) << "PostgreSQL: using READ COMMITTED transaction mode"; + SetIsolationMode(IsolationMode_ReadCommited); + } + else if (transactionMode == "Serializable") + { + LOG(WARNING) << "PostgreSQL: using SERIALIZABLE transaction mode"; + SetIsolationMode(IsolationMode_Serializable); + } + else + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_BadParameterType, std::string("Invalid value for 'TransactionMode': ") + transactionMode); + } }
--- a/Framework/PostgreSQL/PostgreSQLParameters.h Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLParameters.h Mon Feb 05 09:48:11 2024 +0100 @@ -30,6 +30,13 @@ namespace OrthancDatabases { + enum IsolationMode + { + IsolationMode_DbDefault = 0, + IsolationMode_Serializable = 1, + IsolationMode_ReadCommited = 2 + }; + class PostgreSQLParameters { private: @@ -43,7 +50,8 @@ bool lock_; unsigned int maxConnectionRetries_; unsigned int connectionRetryInterval_; - + bool isVerboseEnabled_; + IsolationMode isolationMode_; void Reset(); public: @@ -125,6 +133,52 @@ return connectionRetryInterval_; } + void SetIsolationMode(IsolationMode isolationMode) + { + isolationMode_ = isolationMode; + } + + const char* GetReadWriteTransactionStatement() const + { + switch (isolationMode_) + { + case IsolationMode_DbDefault: + return ""; + case IsolationMode_ReadCommited: + return "SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ WRITE"; + case IsolationMode_Serializable: + return "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE"; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + } + + const char* GetReadOnlyTransactionStatement() const + { + switch (isolationMode_) + { + case IsolationMode_DbDefault: + return ""; + case IsolationMode_ReadCommited: + return "SET TRANSACTION ISOLATION LEVEL READ COMMITTED READ ONLY"; + case IsolationMode_Serializable: + return "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY"; + default: + throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); + } + } + + void SetVerboseEnabled(bool enabled) + { + isVerboseEnabled_ = enabled; + } + + bool IsVerboseEnabled() const + { + return isVerboseEnabled_; + } + + void Format(std::string& target) const; }; }
--- a/Framework/PostgreSQL/PostgreSQLResult.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLResult.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -84,6 +84,11 @@ position_(0), database_(statement.GetDatabase()) { + if (database_.IsVerboseEnabled()) + { + LOG(INFO) << "PostgreSQL: " << statement.sql_; + } + result_ = statement.Execute(); assert(result_ != NULL); // An exception would have been thrown otherwise @@ -255,6 +260,9 @@ case OIDOID: return new LargeObjectResult(database_, GetLargeObjectOid(column)); + case VOIDOID: + return NULL; + default: throw Orthanc::OrthancException(Orthanc::ErrorCode_NotImplemented); }
--- a/Framework/PostgreSQL/PostgreSQLStatement.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLStatement.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -287,7 +287,8 @@ } #if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 9, 2) - throw Orthanc::OrthancException(Orthanc::ErrorCode_DatabaseCannotSerialize); + std::string errorString(PQresultErrorMessage(result)); + throw Orthanc::OrthancException(Orthanc::ErrorCode_DatabaseCannotSerialize, errorString, false); // don't log here, it is handled at higher level #else throw Orthanc::OrthancException(Orthanc::ErrorCode_Database, "Collision between multiple writers"); #endif @@ -308,7 +309,10 @@ inputs_(new Inputs), formatter_(Dialect_PostgreSQL) { - LOG(TRACE) << "PostgreSQL: " << sql; + if (database.IsVerboseEnabled()) + { + LOG(TRACE) << "PostgreSQL: " << sql; + } } @@ -319,7 +323,11 @@ formatter_(Dialect_PostgreSQL) { query.Format(sql_, formatter_); - LOG(TRACE) << "PostgreSQL: " << sql_; + + if (database.IsVerboseEnabled()) + { + LOG(TRACE) << "PostgreSQL: " << sql_; + } for (size_t i = 0; i < formatter_.GetParametersCount(); i++) {
--- a/Framework/PostgreSQL/PostgreSQLTransaction.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/Framework/PostgreSQL/PostgreSQLTransaction.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -70,12 +70,22 @@ switch (type) { case TransactionType_ReadWrite: - database_.ExecuteMultiLines("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ WRITE"); - break; + { + const std::string& statement = database_.GetReadWriteTransactionStatement(); + if (!statement.empty()) // if not defined, will use the default DB transaction isolation level + { + database_.ExecuteMultiLines(statement); + } + }; break; case TransactionType_ReadOnly: - database_.ExecuteMultiLines("SET TRANSACTION ISOLATION LEVEL SERIALIZABLE READ ONLY"); - break; + { + const std::string& statement = database_.GetReadOnlyTransactionStatement(); + if (!statement.empty()) // if not defined, will use the default DB transaction isolation level + { + database_.ExecuteMultiLines(statement); + } + }; break; default: throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/NOTES Mon Feb 05 09:48:11 2024 +0100 @@ -0,0 +1,230 @@ +Resources: +********* +- PG transaction modes explained: https://www.postgresql.org/files/developer/concurrency.pdf +- Isolation level explained (PG + MySQL): https://amirsoleimani.medium.com/understanding-database-isolation-level-via-examples-mysql-and-postgres-a86b5502d404 +- Message queuing in PG: https://www.crunchydata.com/blog/message-queuing-using-native-postgresql + + +Create and delete instances Internals: +************************************* + +isNewInstance = CreateInstance(...) + +if (!isNewInstance && overwriteInstances) + DeleteResource(instance) + -> ClearDeletedFiles(manager); + DELETE FROM DeletedFiles ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction -> it is empty when taking a snapshot of the DB in READ COMMITTED mode!!! + ClearDeletedResources(manager); + DELETE FROM DeletedResources ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! + + DELETE FROM RemainingAncestor ------> this is not a temporary table in postgres but it is supposed to be empty before and after each transaction !!! + DELETE FROM Resources WHERE internalId=.. + -> cascades delete the MainDicomTags, the Metadata and the AttachedFiles + -> this triggers AttachedFileDeletedFunc + INSERT INTO DeletedFiles VALUES + (old.uuid, old.filetype, old.compressedSize, + old.uncompressedSize, old.compressionType, + old.uncompressedHash, old.compressedHash); + RETURN NULL; + -> this triggers a SQL trigger: ResourceDeletedFunc + INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); + IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN + -- Signal that the deleted resource has a remaining parent + -- (a parent that must not be deleted but whose LastUpdate must be updated) + INSERT INTO RemainingAncestor + SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; + ELSE + -- Delete a parent resource when its unique child is deleted + DELETE FROM Resources WHERE internalId = old.parentId; + END IF; + + SELECT * FROM RemainingAncestor + -> SignalRemainingAncestor() // There is at most 1 remaining ancestor + -> ServerIndex::TransactionContext::SignalRemainingAncestor() + -> stores remainingType and remainingPublicId (this is used in StatelessDatabaseOperations::DeleteResource to build the Rest Response of /delete + and to update the LastUpdate of all parent (only when deleted from /delete)) + + SignalDeletedFiles(output, manager); + SELECT * FROM DeletedFiles + -> SignalDeletedAttachment() + -> ServerIndex::TransactionContext::SignalAttachmentDeleted() + -> pendingFilesToRemove_.push_back(FileToRemove(info)) (files are deleted in CommitFilesToRemove in the ServerIndex::TransactionContext::Commit) + + SignalDeletedResources(output, manager); + SELECT resourceType, publicId FROM DeletedResources + -> SignalDeletedResource() + -> Emit DeletedResource event (lua) + + + if (!CreateInstance(...)) + Error: "No new instance while overwriting; this should not happen" + +if isNewInstance -> LogChange +if isNewSeries -> LogChange +.... + +Sample SQL code that you can execute in DBeaver to test new functions/procedures: + +CreateInstance +************************************************************************ + +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); + EXCEPTION + WHEN unique_violation THEN + is_new_patient := 0; + END; + SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id AND resourcetype = 0 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id); + EXCEPTION + WHEN unique_violation THEN + is_new_study := 0; + END; + SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id AND resourcetype = 1 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id); + EXCEPTION + WHEN unique_violation THEN + is_new_series := 0; + END; + SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id AND resourcetype = 2 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction + + BEGIN + INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id); + EXCEPTION + WHEN unique_violation THEN + is_new_instance := 0; + END; + SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id AND resourcetype = 3 FOR UPDATE; -- also locks the resource and its parent to prevent from deletion while we complete this transaction + + 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; + + +DO $$ +DECLARE + result record; +begin + delete from "resources"; + + SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance1'); + + RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patient_internal_id, result.is_new_patient; + RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.study_internal_id, result.is_new_study; + RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.series_internal_id, result.is_new_series; + RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instance_internal_id, result.is_new_instance; + RAISE NOTICE '--------------'; + + SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); + + RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patient_internal_id, result.is_new_patient; + RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.study_internal_id, result.is_new_study; + RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.series_internal_id, result.is_new_series; + RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instance_internal_id, result.is_new_instance; + RAISE NOTICE '--------------'; + + SELECT * INTO result from CreateInstance('patient1', 'study1', 'series1', 'instance2'); + + RAISE NOTICE 'Value patientInternalId: %, is_new: %', result.patient_internal_id, result.is_new_patient; + RAISE NOTICE 'Value studyInternalId: %, is_new: %', result.study_internal_id, result.is_new_study; + RAISE NOTICE 'Value seriesInternalId: %, is_new: %', result.series_internal_id, result.is_new_series; + RAISE NOTICE 'Value instanceInternalId: %, is_new: %', result.instance_internal_id, result.is_new_instance; + RAISE NOTICE '--------------'; + +END $$; + + +-- \set patient_key 'patient_key' +-- SELECT CreateInstance('patient', 'study', 'series', 'instance', patient_key) ; + +-- drop function CreateInstance +-- select * from "resources"; +-- delete from "resources"; +-- INSERT INTO "resources" VALUES (DEFAULT, 0, 'patient', NULL) + + + +************************************************************************ + +In debug, no verbose logs, 10 connections +Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 43.957 s +Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 15.744 s + test_concurrent_anonymize_same_study deletion took: 18.8 s + +Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 21.214 s +Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 9.514 s + +Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 23.016 s +Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 7.129 s + +Orthanc mainline + PG mainline (read-committed mode) : test_upload_multiple_studies_from_multiple_threads with 10 workers and 25 files and repeat 3x: 6.454 s + +With Docker with 10 connections SQL: +osimis/orthanc:24.1.2 : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 18.956 s FAIL !!! + test_concurrent_anonymize_same_study deletion took: NA +osimis/orthanc:current: test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 6.867 s + test_concurrent_anonymize_same_study deletion took: 9.095 s + +osimis/orthanc:24.1.2 : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 9.822 s +osimis/orthanc:current: test_concurrent_uploads_same_study with 20 workers and 1x repeat: 16.027 s up to 38s ! (slower but the test is not representative of a real life scenario !!!!!) + +osimis/orthanc:24.1.2 : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 12.966 s +osimis/orthanc:current: test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 4.196 s + +osimis/orthanc:24.1.2 : test_upload_multiple_studies_from_multiple_threads with 10 workers and 25 files and repeat 3x: 8.957 s +osimis/orthanc:current: test_upload_multiple_studies_from_multiple_threads with 10 workers and 25 files and repeat 3x: 2.671 s + +Testing the connecions (note: Orthanc and PG server running on the same server) +10 connections : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 15.744 s +1 connection : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 21.341 s +10 connections : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 6.57 s +1 connection : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 10.223 s +10 connections : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 7.129 s +1 connection : test_upload_delete_same_study_from_multiple_threads with 5 workers and 3x repeat (10x): 11.172 s + + +TODO: +- have a separate "thread" to UpdateStatistics ? + +- check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820 + +DONE: +- implement a downgrade script ? And test it in PotgresUpgrades integ tests +- test the transfer plugin +- perf tests: upload generated data (different studies) +- In Docker images, re-enable MySQL & ODBC plugins + tests +- reenable PatientRecyclingOrder +- force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP) +- PatientAddedFunc contains an IF (check if other IF/THEN/ELSE pattern remains) +- validate upgrade DB from previous Orthanc and from scratch +- check minimal version of PG (9.5 - 9.6 ? for create index if not exists): seems to work with 9.5 cfr PotgresUpgrades integ tests +- test events generation StableSeries .... (count the NewSeries, NewInstances event and make sure they match the numb)
--- a/PostgreSQL/CMakeLists.txt Wed Jan 31 14:54:41 2024 +0100 +++ b/PostgreSQL/CMakeLists.txt Mon Feb 05 09:48:11 2024 +0100 @@ -23,11 +23,11 @@ set(ORTHANC_PLUGIN_VERSION "mainline") -set(ORTHANC_SDK_DEFAULT_VERSION "1.12.0") +set(ORTHANC_SDK_DEFAULT_VERSION "1.12.3") set(ORTHANC_OPTIMAL_VERSION_MAJOR 1) set(ORTHANC_OPTIMAL_VERSION_MINOR 12) -set(ORTHANC_OPTIMAL_VERSION_REVISION 0) +set(ORTHANC_OPTIMAL_VERSION_REVISION 3) if (ORTHANC_PLUGIN_VERSION STREQUAL "mainline") set(ORTHANC_FRAMEWORK_VERSION "mainline") @@ -79,11 +79,9 @@ EmbedResources( - POSTGRESQL_PREPARE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/PrepareIndex.sql - POSTGRESQL_CREATE_INSTANCE ${CMAKE_SOURCE_DIR}/Plugins/CreateInstance.sql - POSTGRESQL_FAST_TOTAL_SIZE ${CMAKE_SOURCE_DIR}/Plugins/FastTotalSize.sql - POSTGRESQL_FAST_COUNT_RESOURCES ${CMAKE_SOURCE_DIR}/Plugins/FastCountResources.sql - POSTGRESQL_GET_LAST_CHANGE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/GetLastChangeIndex.sql + 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 )
--- a/PostgreSQL/NEWS Wed Jan 31 14:54:41 2024 +0100 +++ b/PostgreSQL/NEWS Mon Feb 05 09:48:11 2024 +0100 @@ -1,3 +1,19 @@ +Pending changes in the mainline +=============================== + +* Transaction Mode: + Introduced a new configuration to select the transaction isolation level + "TransactionMode": "Serializable" + Allowed values: "Serializable", "ReadCommitted". + The "Serializable" mode was the only available value up to now. It is still the default + value now. + The "ReadCommitted" is possible now due to rewrites of SQL queries and notably improves + the Orthanc ability to ingest data from multiple sources in parallel. +* internals: + - Added a UNIQUE constraint on Resources.publicId to detect DB inconsistencies +* New "EnableVerboseLogs" configuration to show SQL statements being executed. + + Release 5.1 (2023-06-27) ========================
--- a/PostgreSQL/Plugins/CreateInstance.sql Wed Jan 31 14:54:41 2024 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,96 +0,0 @@ -CREATE FUNCTION CreateInstance( - IN patient TEXT, - IN study TEXT, - IN series TEXT, - IN instance TEXT, - OUT isNewPatient BIGINT, - OUT isNewStudy BIGINT, - OUT isNewSeries BIGINT, - OUT isNewInstance BIGINT, - OUT patientKey BIGINT, - OUT studyKey BIGINT, - OUT seriesKey BIGINT, - OUT instanceKey BIGINT) AS $body$ - -DECLARE - patientSeq BIGINT; - countRecycling BIGINT; - -BEGIN - SELECT internalId FROM Resources INTO instanceKey WHERE publicId = instance AND resourceType = 3; - - IF NOT (instanceKey IS NULL) THEN - -- This instance already exists, stop here - isNewInstance := 0; - ELSE - SELECT internalId FROM Resources INTO patientKey WHERE publicId = patient AND resourceType = 0; - SELECT internalId FROM Resources INTO studyKey WHERE publicId = study AND resourceType = 1; - SELECT internalId FROM Resources INTO seriesKey WHERE publicId = series AND resourceType = 2; - - IF patientKey IS NULL THEN - -- Must create a new patient - IF NOT (studyKey IS NULL AND seriesKey IS NULL AND instanceKey IS NULL) THEN - RAISE EXCEPTION 'Broken invariant'; - END IF; - - INSERT INTO Resources VALUES (DEFAULT, 0, patient, NULL) RETURNING internalId INTO patientKey; - isNewPatient := 1; - ELSE - isNewPatient := 0; - END IF; - - IF (patientKey IS NULL) THEN - RAISE EXCEPTION 'Broken invariant'; - END IF; - - IF studyKey IS NULL THEN - -- Must create a new study - IF NOT (seriesKey IS NULL AND instanceKey IS NULL) THEN - RAISE EXCEPTION 'Broken invariant'; - END IF; - - INSERT INTO Resources VALUES (DEFAULT, 1, study, patientKey) RETURNING internalId INTO studyKey; - isNewStudy := 1; - ELSE - isNewStudy := 0; - END IF; - - IF (studyKey IS NULL) THEN - RAISE EXCEPTION 'Broken invariant'; - END IF; - - IF seriesKey IS NULL THEN - -- Must create a new series - IF NOT (instanceKey IS NULL) THEN - RAISE EXCEPTION 'Broken invariant'; - END IF; - - INSERT INTO Resources VALUES (DEFAULT, 2, series, studyKey) RETURNING internalId INTO seriesKey; - isNewSeries := 1; - ELSE - isNewSeries := 0; - END IF; - - IF (seriesKey IS NULL OR NOT instanceKey IS NULL) THEN - RAISE EXCEPTION 'Broken invariant'; - END IF; - - INSERT INTO Resources VALUES (DEFAULT, 3, instance, seriesKey) RETURNING internalId INTO instanceKey; - isNewInstance := 1; - - -- Move the patient to the end of the recycling order - SELECT seq FROM PatientRecyclingOrder WHERE patientId = patientKey INTO patientSeq; - - IF NOT (patientSeq IS NULL) THEN - -- The patient is not protected - SELECT COUNT(*) FROM (SELECT * FROM PatientRecyclingOrder WHERE seq >= patientSeq LIMIT 2) AS tmp INTO countRecycling; - IF countRecycling = 2 THEN - -- The patient was not at the end of the recycling order - DELETE FROM PatientRecyclingOrder WHERE seq = patientSeq; - INSERT INTO PatientRecyclingOrder VALUES(DEFAULT, patientKey); - END IF; - END IF; - END IF; -END; - -$body$ LANGUAGE plpgsql;
--- a/PostgreSQL/Plugins/FastCountResources.sql Wed Jan 31 14:54:41 2024 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,33 +0,0 @@ --- https://wiki.postgresql.org/wiki/Count_estimate - -INSERT INTO GlobalIntegers -SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0; -- Count patients - -INSERT INTO GlobalIntegers -SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1; -- Count studies - -INSERT INTO GlobalIntegers -SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2; -- Count series - -INSERT INTO GlobalIntegers -SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3; -- Count instances - - -CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc() -RETURNS TRIGGER AS $$ -BEGIN - IF TG_OP = 'INSERT' THEN - UPDATE GlobalIntegers SET value = value + 1 WHERE key = new.resourceType + 2; - RETURN new; - ELSIF TG_OP = 'DELETE' THEN - UPDATE GlobalIntegers SET value = value - 1 WHERE key = old.resourceType + 2; - RETURN old; - END IF; -END; -$$ LANGUAGE plpgsql; - - -CREATE TRIGGER CountResourcesTracker -AFTER INSERT OR DELETE ON Resources -FOR EACH ROW -EXECUTE PROCEDURE CountResourcesTrackerFunc();
--- a/PostgreSQL/Plugins/FastTotalSize.sql Wed Jan 31 14:54:41 2024 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,41 +0,0 @@ -CREATE TABLE GlobalIntegers( - key INTEGER PRIMARY KEY, - value BIGINT); - -INSERT INTO GlobalIntegers -SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles; - -INSERT INTO GlobalIntegers -SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles; - - - -CREATE FUNCTION AttachedFileIncrementSizeFunc() -RETURNS TRIGGER AS $body$ -BEGIN - UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0; - UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1; - RETURN NULL; -END; -$body$ LANGUAGE plpgsql; - -CREATE FUNCTION AttachedFileDecrementSizeFunc() -RETURNS TRIGGER AS $body$ -BEGIN - UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0; - UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1; - RETURN NULL; -END; -$body$ LANGUAGE plpgsql; - - - -CREATE TRIGGER AttachedFileIncrementSize -AFTER INSERT ON AttachedFiles -FOR EACH ROW -EXECUTE PROCEDURE AttachedFileIncrementSizeFunc(); - -CREATE TRIGGER AttachedFileDecrementSize -AFTER DELETE ON AttachedFiles -FOR EACH ROW -EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
--- a/PostgreSQL/Plugins/GetLastChangeIndex.sql Wed Jan 31 14:54:41 2024 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,27 +0,0 @@ --- In PostgreSQL, the most straightforward query would be to run: - --- SELECT currval(pg_get_serial_sequence('Changes', 'seq'))". - --- Unfortunately, this raises the error message "currval of sequence --- "changes_seq_seq" is not yet defined in this session" if no change --- has been inserted before the SELECT. We thus track the sequence --- index with a trigger. --- http://www.neilconway.org/docs/sequences/ - -INSERT INTO GlobalIntegers -SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes; - - -CREATE FUNCTION InsertedChangeFunc() -RETURNS TRIGGER AS $body$ -BEGIN - UPDATE GlobalIntegers SET value = new.seq WHERE key = 6; - RETURN NULL; -END; -$body$ LANGUAGE plpgsql; - - -CREATE TRIGGER InsertedChange -AFTER INSERT ON Changes -FOR EACH ROW -EXECUTE PROCEDURE InsertedChangeFunc();
--- a/PostgreSQL/Plugins/PostgreSQLDefinitions.h Wed Jan 31 14:54:41 2024 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLDefinitions.h Mon Feb 05 09:48:11 2024 +0100 @@ -48,3 +48,10 @@ * https://groups.google.com/d/msg/orthanc-users/yV3LSTh_TjI/h3PRApJFBAAJ **/ static const int32_t POSTGRESQL_LOCK_DATABASE_SETUP = 44; + +/** + * Transient advisory lock to protect the instance creation, + * because it is not 100% resilient to concurrency in, e.g, READ COMIITED + * transaction isolation level. + **/ +static const int32_t POSTGRESQL_LOCK_CREATE_INSTANCE = 45;
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Wed Jan 31 14:54:41 2024 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Mon Feb 05 09:48:11 2024 +0100 @@ -30,6 +30,7 @@ #include <EmbeddedResources.h> // Auto-generated file #include <Compatibility.h> // For std::unique_ptr<> +#include <Toolbox.h> #include <Logging.h> #include <OrthancException.h> @@ -60,6 +61,14 @@ return PostgreSQLDatabase::CreateDatabaseFactory(parameters_); } + void PostgreSQLIndex::ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager) + { + std::string query; + + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + } void PostgreSQLIndex::ConfigureDatabase(DatabaseManager& manager, bool hasIdentifierTags, @@ -76,7 +85,7 @@ if (expectedVersion != 6) { LOG(ERROR) << "This database plugin is incompatible with your version of Orthanc " - << "expecting the DB schema version " << expectedVersion + << "expecting the Orthanc DB schema version " << expectedVersion << ", but this plugin is only compatible with version 6"; throw Orthanc::OrthancException(Orthanc::ErrorCode_Plugin); } @@ -89,6 +98,7 @@ } { + // lock the full DB while checking if it needs to be create/ugraded PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); if (clearAll_) @@ -101,218 +111,98 @@ if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) { - std::string query; - - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion, expectedVersion); - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 1); - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 0); - } - - if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) - { - LOG(ERROR) << "Corrupted PostgreSQL database"; - throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); - } + LOG(WARNING) << "PostgreSQL is creating the database schema"; - int version = 0; - if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) || - version != 6) - { - LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema version: " << version; - throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); - } - - int revision; - if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel)) - { - revision = 1; - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); - } - - if (revision != 1) - { - LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision; - throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); - } + ApplyPrepareIndex(t, manager); - t.Commit(); - } - - { - DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); - - int hasTrigram = 0; - if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_HasTrigramIndex) || - hasTrigram != 1) - { - /** - * Apply fix for performance issue (speed up wildcard search - * by using GIN trigrams). This implements the patch suggested - * in issue #47, BUT we also keep the original - * "DicomIdentifiersIndexValues", as it leads to better - * performance for "strict" searches (i.e. searches involving - * no wildcard). - * https://www.postgresql.org/docs/current/static/pgtrgm.html - * https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47 - **/ - try + if (!t.GetDatabaseTransaction().DoesTableExist("Resources")) { - // We've observed 9 minutes on DB with 100000 studies - LOG(WARNING) << "Trying to enable trigram matching on the PostgreSQL database " - << "to speed up wildcard searches. This may take several minutes"; - - t.GetDatabaseTransaction().ExecuteMultiLines( - "CREATE EXTENSION IF NOT EXISTS pg_trgm; " - "CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);"); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 1); - LOG(WARNING) << "Trigram index has been created"; - - t.Commit(); - } - catch (Orthanc::OrthancException&) - { - LOG(WARNING) << "Performance warning: Your PostgreSQL server does " - << "not support trigram matching"; - LOG(WARNING) << "-> Consider installing the \"pg_trgm\" extension on the " - << "PostgreSQL server, e.g. on Debian: sudo apt install postgresql-contrib"; + LOG(ERROR) << "Corrupted PostgreSQL database or failed to create the database schema"; + throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError); } } else { - t.Commit(); - } - } - - { - DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); - - int property = 0; - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_HasCreateInstance) || - property != 2) - { - LOG(INFO) << "Installing the CreateInstance extension"; + LOG(WARNING) << "The database schema already exists, checking if it needs to be updated"; - if (property == 1) + int version = 0; + if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) || + version != 6) { - // Drop older, experimental versions of this extension - t.GetDatabaseTransaction().ExecuteMultiLines("DROP FUNCTION CreateInstance(" - "IN patient TEXT, IN study TEXT, IN series TEXT, in instance TEXT)"); + LOG(ERROR) << "PostgreSQL plugin is incompatible with Orthanc database schema version: " << version; + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); } - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_CREATE_INSTANCE); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 2); - } + bool needToRunUpgradeFromUnknownToV1 = false; + bool needToRunUpgradeV1toV2 = false; - - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_GetTotalSizeIsFast) || - property != 1) - { - LOG(INFO) << "Installing the FastTotalSize extension"; - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_SIZE); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1); - } - + 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; + } + else if (revision == 1) + { + needToRunUpgradeV1toV2 = true; + } - // Installing this extension requires the "GlobalIntegers" table - // created by the "FastTotalSize" extension - property = 0; - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_HasFastCountResources) || - property != 1) - { - LOG(INFO) << "Installing the FastCountResources extension"; - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasFastCountResources, 1); - } - + int hasTrigram = 0; + if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER, + Orthanc::GlobalProperty_HasTrigramIndex) || + hasTrigram != 1) + { + // 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; + } - // Installing this extension requires the "GlobalIntegers" table - // created by the "GetLastChangeIndex" extension - property = 0; - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, - Orthanc::GlobalProperty_GetLastChangeIndex) || - property != 1) - { - LOG(INFO) << "Installing the GetLastChangeIndex extension"; - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1); - } - - t.Commit(); - } - - - { - // New in release 4.0 to deal with multiple writers - DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); - - if (!t.GetDatabaseTransaction().DoesTableExist("ServerProperties")) - { - t.GetDatabaseTransaction().ExecuteMultiLines("CREATE TABLE ServerProperties(server VARCHAR(64) NOT NULL, " - "property INTEGER, value TEXT, PRIMARY KEY(server, property))"); - } + 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; + } - /** - * PostgreSQL 9.5: "Adding a column with a default requires - * updating each row of the table (to store the new column - * value). However, if no default is specified, PostgreSQL is - * able to avoid the physical update." => We set no default - * for performance (older entries will be NULL) - * https://www.postgresql.org/docs/9.5/ddl-alter.html - **/ - if (!db.DoesColumnExist("Metadata", "revision")) - { - t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Metadata ADD COLUMN revision INTEGER"); - } + if (needToRunUpgradeFromUnknownToV1) + { + LOG(WARNING) << "Upgrading DB schema from unknown to revision 1"; + std::string query; - if (!db.DoesColumnExist("AttachedFiles", "revision")) - { - t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER"); - } - - t.Commit(); - } - + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + } + + if (needToRunUpgradeV1toV2) + { + LOG(WARNING) << "Upgrading DB schema from revision 1 to revision 2"; - { - // New in release 5.0 to deal with labels - DatabaseManager::Transaction t(manager, TransactionType_ReadWrite); + std::string query; - if (!t.GetDatabaseTransaction().DoesTableExist("Labels")) - { - t.GetDatabaseTransaction().ExecuteMultiLines( - "CREATE TABLE Labels(" - "id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE," - "label TEXT, PRIMARY KEY(id, label));" - "CREATE INDEX LabelsIndex1 ON LABELS(id);" - "CREATE INDEX LabelsIndex2 ON LABELS(label);"); + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV1_TO_REV2); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + + // apply all idempotent changes that are in the PrepareIndexV2 + ApplyPrepareIndex(t, manager); + } } t.Commit(); @@ -358,7 +248,8 @@ result = static_cast<uint64_t>(statement.ReadInteger64(0)); } - assert(result == IndexBackend::GetTotalCompressedSize(manager)); + // 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::GetTotalCompressedSize(manager)); return result; } @@ -379,10 +270,163 @@ result = static_cast<uint64_t>(statement.ReadInteger64(0)); } - assert(result == IndexBackend::GetTotalUncompressedSize(manager)); + // 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::GetTotalUncompressedSize(manager)); return result; } + int64_t PostgreSQLIndex::IncrementGlobalProperty(DatabaseManager& manager, + const char* serverIdentifier, + int32_t property, + int64_t increment) + { + if (serverIdentifier == NULL) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_NullPointer); + } + else + { + if (strlen(serverIdentifier) == 0) + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "INSERT INTO GlobalProperties (property, value) VALUES(${property}, ${increment}) " + " ON CONFLICT (property) DO UPDATE SET value = CAST(GlobalProperties.value AS BIGINT) + ${increment}" + " RETURNING CAST(value AS BIGINT)"); + + statement.SetParameterType("property", ValueType_Integer64); + statement.SetParameterType("increment", ValueType_Integer64); + + Dictionary args; + args.SetIntegerValue("property", property); + args.SetIntegerValue("increment", increment); + + statement.Execute(args); + + return statement.ReadInteger64(0); + } + else + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "INSERT INTO ServerProperties (server, property, value) VALUES(${server}, ${property}, ${increment}) " + " ON CONFLICT (server, property) DO UPDATE SET value = CAST(ServerProperties.value AS BIGINT) + ${increment}" + " RETURNING CAST(value AS BIGINT)"); + + statement.SetParameterType("server", ValueType_Utf8String); + statement.SetParameterType("property", ValueType_Integer64); + statement.SetParameterType("increment", ValueType_Integer64); + + Dictionary args; + args.SetUtf8Value("server", serverIdentifier); + args.SetIntegerValue("property", property); + args.SetIntegerValue("increment", increment); + + statement.Execute(args); + + return statement.ReadInteger64(0); + } + } + } + + void PostgreSQLIndex::UpdateAndGetStatistics(DatabaseManager& manager, + int64_t& patientsCount, + int64_t& studiesCount, + int64_t& seriesCount, + int64_t& instancesCount, + int64_t& compressedSize, + int64_t& uncompressedSize) + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "SELECT * FROM UpdateStatistics()"); + + statement.Execute(); + + patientsCount = statement.ReadInteger64(0); + studiesCount = statement.ReadInteger64(1); + seriesCount = statement.ReadInteger64(2); + instancesCount = statement.ReadInteger64(3); + compressedSize = statement.ReadInteger64(4); + uncompressedSize = statement.ReadInteger64(5); + } + + void PostgreSQLIndex::ClearDeletedFiles(DatabaseManager& manager) + { + { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "SELECT CreateDeletedFilesTemporaryTable()" + ); + statement.ExecuteWithoutResult(); + } + + } + + void PostgreSQLIndex::ClearDeletedResources(DatabaseManager& manager) + { + { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(" + "resourceType INTEGER NOT NULL," + "publicId VARCHAR(64) NOT NULL" + ");" + ); + statement.Execute(); + } + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "DELETE FROM DeletedResources;" + ); + + statement.Execute(); + } + + } + + void PostgreSQLIndex::ClearRemainingAncestor(DatabaseManager& manager) + { + } + + void PostgreSQLIndex::DeleteResource(IDatabaseBackendOutput& output, + DatabaseManager& manager, + int64_t id) + { + // clearing of temporary table is now implemented in the funcion DeleteResource + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "SELECT * FROM DeleteResource(${id})"); + + statement.SetParameterType("id", ValueType_Integer64); + + Dictionary args; + args.SetIntegerValue("id", id); + + statement.Execute(args); + + if (statement.IsDone() || + statement.GetResultFieldsCount() != 2) + { + throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); + } + + statement.SetResultFieldType(0, ValueType_Integer64); + statement.SetResultFieldType(1, ValueType_Utf8String); + + if (!statement.IsNull(0)) + { + output.SignalRemainingAncestor( + statement.ReadString(1), + static_cast<OrthancPluginResourceType>(statement.ReadInteger32(0))); + } + + SignalDeletedFiles(output, manager); + SignalDeletedResources(output, manager); + } + + #if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 void PostgreSQLIndex::CreateInstance(OrthancPluginCreateInstanceResult& result, @@ -406,7 +450,7 @@ args.SetUtf8Value("study", hashStudy); args.SetUtf8Value("series", hashSeries); args.SetUtf8Value("instance", hashInstance); - + statement.Execute(args); if (statement.IsDone() || @@ -420,6 +464,8 @@ statement.SetResultFieldType(i, ValueType_Integer64); } + // LOG(INFO) << statement.ReadInteger64(0) << statement.ReadInteger64(1) << statement.ReadInteger64(2) << statement.ReadInteger64(3); + result.isNewInstance = (statement.ReadInteger64(3) == 1); result.instanceId = statement.ReadInteger64(7); @@ -436,6 +482,133 @@ #endif +#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 + static void ExecuteSetResourcesContentTags( + DatabaseManager& manager, + const std::string& table, + const std::string& variablePrefix, + uint32_t count, + const OrthancPluginResourcesContentTags* tags) + { + std::string sql; + Dictionary args; + + for (uint32_t i = 0; i < count; i++) + { + std::string name = variablePrefix + 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 + "})"); + + if (sql.empty()) + { + sql = "INSERT INTO " + table + " VALUES " + insert; + } + else + { + sql += ", " + insert; + } + } + + if (!sql.empty()) + { + DatabaseManager::StandaloneStatement statement(manager, sql); + + for (uint32_t i = 0; i < count; i++) + { + statement.SetParameterType(variablePrefix + boost::lexical_cast<std::string>(i), + ValueType_Utf8String); + } + + statement.Execute(args); + } + } +#endif + + +#if ORTHANC_PLUGINS_HAS_DATABASE_CONSTRAINT == 1 + static void ExecuteSetResourcesContentMetadata( + DatabaseManager& manager, + bool hasRevisionsSupport, + uint32_t count, + const OrthancPluginResourcesContentMetadata* metadata) + { + if (count < 1) + { + return; + } + + std::vector<std::string> resourceIds; + std::vector<std::string> metadataTypes; + std::vector<std::string> metadataValues; + std::vector<std::string> revisions; + + Dictionary args; + + for (uint32_t i = 0; i < count; i++) + { + std::string argName = "m" + boost::lexical_cast<std::string>(i); + + args.SetUtf8Value(argName, metadata[i].value); + + 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 + "}"); + 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 sql = std::string("SELECT InsertOrUpdateMetadata(ARRAY[") + + joinedResourceIds + "], ARRAY[" + + joinedMetadataTypes + "], ARRAY[" + + joinedMetadataValues + "], ARRAY[" + + joinedRevisions + "])"; + + DatabaseManager::StandaloneStatement statement(manager, sql); + + for (uint32_t i = 0; i < count; i++) + { + statement.SetParameterType("m" + boost::lexical_cast<std::string>(i), + ValueType_Utf8String); + } + + statement.Execute(args); + } +#endif + + + void PostgreSQLIndex::SetResourcesContent(DatabaseManager& manager, + uint32_t countIdentifierTags, + const OrthancPluginResourcesContentTags* identifierTags, + uint32_t countMainDicomTags, + const OrthancPluginResourcesContentTags* mainDicomTags, + uint32_t countMetadata, + const OrthancPluginResourcesContentMetadata* metadata) + { + ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i", + countIdentifierTags, identifierTags); + + ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t", + countMainDicomTags, mainDicomTags); + + ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata); + + } + + uint64_t PostgreSQLIndex::GetResourcesCount(DatabaseManager& manager, OrthancPluginResourceType resourceType) { @@ -466,7 +639,9 @@ 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)); + return result; }
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h Wed Jan 31 14:54:41 2024 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.h Mon Feb 05 09:48:11 2024 +0100 @@ -33,6 +33,15 @@ PostgreSQLParameters parameters_; bool clearAll_; + protected: + virtual void ClearDeletedFiles(DatabaseManager& manager); + + virtual void ClearDeletedResources(DatabaseManager& manager); + + virtual void ClearRemainingAncestor(DatabaseManager& manager); + + void ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager); + public: PostgreSQLIndex(OrthancPluginContext* context, const PostgreSQLParameters& parameters); @@ -55,8 +64,19 @@ virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, - OrthancPluginResourceType type) - ORTHANC_OVERRIDE; + OrthancPluginResourceType type) ORTHANC_OVERRIDE; + + virtual void DeleteResource(IDatabaseBackendOutput& output, + DatabaseManager& manager, + int64_t id) ORTHANC_OVERRIDE; + + virtual void SetResourcesContent(DatabaseManager& manager, + uint32_t countIdentifierTags, + const OrthancPluginResourcesContentTags* identifierTags, + uint32_t countMainDicomTags, + const OrthancPluginResourcesContentTags* mainDicomTags, + uint32_t countMetadata, + const OrthancPluginResourcesContentMetadata* metadata) ORTHANC_OVERRIDE; virtual uint64_t GetTotalCompressedSize(DatabaseManager& manager) ORTHANC_OVERRIDE; @@ -90,5 +110,29 @@ { return true; } + + virtual bool HasAtomicIncrementGlobalProperty() ORTHANC_OVERRIDE + { + return true; + } + + virtual int64_t IncrementGlobalProperty(DatabaseManager& manager, + const char* serverIdentifier, + int32_t property, + int64_t increment) ORTHANC_OVERRIDE; + + virtual bool HasUpdateAndGetStatistics() ORTHANC_OVERRIDE + { + return true; + } + + virtual void UpdateAndGetStatistics(DatabaseManager& manager, + int64_t& patientsCount, + int64_t& studiesCount, + int64_t& seriesCount, + int64_t& instancesCount, + int64_t& compressedSize, + int64_t& uncompressedSize) ORTHANC_OVERRIDE; + }; }
--- a/PostgreSQL/Plugins/PrepareIndex.sql Wed Jan 31 14:54:41 2024 +0100 +++ /dev/null Thu Jan 01 00:00:00 1970 +0000 @@ -1,168 +0,0 @@ -CREATE TABLE GlobalProperties( - property INTEGER PRIMARY KEY, - value TEXT - ); - -CREATE TABLE Resources( - internalId BIGSERIAL NOT NULL PRIMARY KEY, - resourceType INTEGER NOT NULL, - publicId VARCHAR(64) NOT NULL, - parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE - ); - -CREATE TABLE MainDicomTags( - id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, - tagGroup INTEGER, - tagElement INTEGER, - value TEXT, - PRIMARY KEY(id, tagGroup, tagElement) - ); - -CREATE TABLE DicomIdentifiers( - id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, - tagGroup INTEGER, - tagElement INTEGER, - value TEXT, - PRIMARY KEY(id, tagGroup, tagElement) - ); - -CREATE TABLE Metadata( - id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, - type INTEGER NOT NULL, - value TEXT, - PRIMARY KEY(id, type) - ); - -CREATE TABLE AttachedFiles( - id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, - fileType INTEGER, - uuid VARCHAR(64) NOT NULL, - compressedSize BIGINT, - uncompressedSize BIGINT, - compressionType INTEGER, - uncompressedHash VARCHAR(40), - compressedHash VARCHAR(40), - PRIMARY KEY(id, fileType) - ); - -CREATE TABLE Changes( - seq BIGSERIAL NOT NULL PRIMARY KEY, - changeType INTEGER, - internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, - resourceType INTEGER, - date VARCHAR(64) - ); - -CREATE TABLE ExportedResources( - seq BIGSERIAL NOT NULL PRIMARY KEY, - resourceType INTEGER, - publicId VARCHAR(64), - remoteModality TEXT, - patientId VARCHAR(64), - studyInstanceUid TEXT, - seriesInstanceUid TEXT, - sopInstanceUid TEXT, - date VARCHAR(64) - ); - -CREATE TABLE PatientRecyclingOrder( - seq BIGSERIAL NOT NULL PRIMARY KEY, - patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE - ); - -CREATE INDEX ChildrenIndex ON Resources(parentId); -CREATE INDEX PublicIndex ON Resources(publicId); -CREATE INDEX ResourceTypeIndex ON Resources(resourceType); -CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId); - -CREATE INDEX MainDicomTagsIndex ON MainDicomTags(id); -CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id); -CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); -CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value); - -CREATE INDEX ChangesIndex ON Changes(internalId); - - --- New tables wrt. Orthanc core -CREATE TABLE DeletedFiles( - uuid VARCHAR(64) NOT NULL, -- 0 - fileType INTEGER, -- 1 - compressedSize BIGINT, -- 2 - uncompressedSize BIGINT, -- 3 - compressionType INTEGER, -- 4 - uncompressedHash VARCHAR(40), -- 5 - compressedHash VARCHAR(40) -- 6 - ); - -CREATE TABLE RemainingAncestor( - resourceType INTEGER NOT NULL, - publicId VARCHAR(64) NOT NULL - ); - -CREATE TABLE DeletedResources( - resourceType INTEGER NOT NULL, - publicId VARCHAR(64) NOT NULL - ); --- End of differences - - -CREATE FUNCTION AttachedFileDeletedFunc() -RETURNS TRIGGER AS $body$ -BEGIN - INSERT INTO DeletedFiles VALUES - (old.uuid, old.filetype, old.compressedSize, - old.uncompressedSize, old.compressionType, - old.uncompressedHash, old.compressedHash); - RETURN NULL; -END; -$body$ LANGUAGE plpgsql; - -CREATE TRIGGER AttachedFileDeleted -AFTER DELETE ON AttachedFiles -FOR EACH ROW -EXECUTE PROCEDURE AttachedFileDeletedFunc(); - - --- The following trigger combines 2 triggers from SQLite: --- ResourceDeleted + ResourceDeletedParentCleaning -CREATE FUNCTION ResourceDeletedFunc() -RETURNS TRIGGER AS $body$ -BEGIN - --RAISE NOTICE 'Delete resource %', old.parentId; - INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); - - -- http://stackoverflow.com/a/11299968/881731 - IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN - -- Signal that the deleted resource has a remaining parent - INSERT INTO RemainingAncestor - SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; - ELSE - -- Delete a parent resource when its unique child is deleted - DELETE FROM Resources WHERE internalId = old.parentId; - END IF; - RETURN NULL; -END; -$body$ LANGUAGE plpgsql; - -CREATE TRIGGER ResourceDeleted -AFTER DELETE ON Resources -FOR EACH ROW -EXECUTE PROCEDURE ResourceDeletedFunc(); - - - -CREATE FUNCTION PatientAddedFunc() -RETURNS TRIGGER AS $body$ -BEGIN - -- The "0" corresponds to "OrthancPluginResourceType_Patient" - IF new.resourceType = 0 THEN - INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId); - END IF; - RETURN NULL; -END; -$body$ LANGUAGE plpgsql; - -CREATE TRIGGER PatientAdded -AFTER INSERT ON Resources -FOR EACH ROW -EXECUTE PROCEDURE PatientAddedFunc();
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev2ToRev1.sql Mon Feb 05 09:48:11 2024 +0100 @@ -0,0 +1,252 @@ +-- This file contains an SQL procedure to downgrade from schema 6.2 to 6.1 (version = 6, revision = 1). +-- It reinstalls all triggers and temporary tables that have been removed or replaced in 6.2 + +-- note: we don't not remove the unique constraints that have been added - they should not +-- create any issues. + +-- these constraints were introduced in 6.2 +ALTER TABLE Resources DROP CONSTRAINT UniquePublicId; +ALTER TABLE PatientRecyclingOrder DROP CONSTRAINT UniquePatientId; + +-- the CreateInstance has been replaced in 6.2, reinstall the 6.1 +DROP FUNCTION CreateInstance; +CREATE FUNCTION CreateInstance( + IN patient TEXT, + IN study TEXT, + IN series TEXT, + IN instance TEXT, + OUT isNewPatient BIGINT, + OUT isNewStudy BIGINT, + OUT isNewSeries BIGINT, + OUT isNewInstance BIGINT, + OUT patientKey BIGINT, + OUT studyKey BIGINT, + OUT seriesKey BIGINT, + OUT instanceKey BIGINT) AS $body$ + +DECLARE + patientSeq BIGINT; + countRecycling BIGINT; + +BEGIN + SELECT internalId FROM Resources INTO instanceKey WHERE publicId = instance AND resourceType = 3; + + IF NOT (instanceKey IS NULL) THEN + -- This instance already exists, stop here + isNewInstance := 0; + ELSE + SELECT internalId FROM Resources INTO patientKey WHERE publicId = patient AND resourceType = 0; + SELECT internalId FROM Resources INTO studyKey WHERE publicId = study AND resourceType = 1; + SELECT internalId FROM Resources INTO seriesKey WHERE publicId = series AND resourceType = 2; + + IF patientKey IS NULL THEN + -- Must create a new patient + IF NOT (studyKey IS NULL AND seriesKey IS NULL AND instanceKey IS NULL) THEN + RAISE EXCEPTION 'Broken invariant'; + END IF; + + INSERT INTO Resources VALUES (DEFAULT, 0, patient, NULL) RETURNING internalId INTO patientKey; + isNewPatient := 1; + ELSE + isNewPatient := 0; + END IF; + + IF (patientKey IS NULL) THEN + RAISE EXCEPTION 'Broken invariant'; + END IF; + + IF studyKey IS NULL THEN + -- Must create a new study + IF NOT (seriesKey IS NULL AND instanceKey IS NULL) THEN + RAISE EXCEPTION 'Broken invariant'; + END IF; + + INSERT INTO Resources VALUES (DEFAULT, 1, study, patientKey) RETURNING internalId INTO studyKey; + isNewStudy := 1; + ELSE + isNewStudy := 0; + END IF; + + IF (studyKey IS NULL) THEN + RAISE EXCEPTION 'Broken invariant'; + END IF; + + IF seriesKey IS NULL THEN + -- Must create a new series + IF NOT (instanceKey IS NULL) THEN + RAISE EXCEPTION 'Broken invariant'; + END IF; + + INSERT INTO Resources VALUES (DEFAULT, 2, series, studyKey) RETURNING internalId INTO seriesKey; + isNewSeries := 1; + ELSE + isNewSeries := 0; + END IF; + + IF (seriesKey IS NULL OR NOT instanceKey IS NULL) THEN + RAISE EXCEPTION 'Broken invariant'; + END IF; + + INSERT INTO Resources VALUES (DEFAULT, 3, instance, seriesKey) RETURNING internalId INTO instanceKey; + isNewInstance := 1; + + -- Move the patient to the end of the recycling order + SELECT seq FROM PatientRecyclingOrder WHERE patientId = patientKey INTO patientSeq; + + IF NOT (patientSeq IS NULL) THEN + -- The patient is not protected + SELECT COUNT(*) FROM (SELECT * FROM PatientRecyclingOrder WHERE seq >= patientSeq LIMIT 2) AS tmp INTO countRecycling; + IF countRecycling = 2 THEN + -- The patient was not at the end of the recycling order + DELETE FROM PatientRecyclingOrder WHERE seq = patientSeq; + INSERT INTO PatientRecyclingOrder VALUES(DEFAULT, patientKey); + END IF; + END IF; + END IF; +END; + +$body$ LANGUAGE plpgsql; + + +-- these tables have been deleted in 6.2: +CREATE TABLE DeletedFiles( + uuid VARCHAR(64) NOT NULL, -- 0 + fileType INTEGER, -- 1 + compressedSize BIGINT, -- 2 + uncompressedSize BIGINT, -- 3 + compressionType INTEGER, -- 4 + uncompressedHash VARCHAR(40), -- 5 + compressedHash VARCHAR(40) -- 6 + ); + +CREATE TABLE RemainingAncestor( + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL + ); + +CREATE TABLE DeletedResources( + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL + ); + + +-- these triggers have been introduced in 6.2, remove them +DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources; +DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources; +DROP FUNCTION IF EXISTS IncrementResourcesTrackerFunc; +DROP FUNCTION IF EXISTS DecrementResourcesTrackerFunc; + +-- this trigger has been removed in 6.2, reinstall it +CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc() +RETURNS TRIGGER AS $$ +BEGIN + IF TG_OP = 'INSERT' THEN + UPDATE GlobalIntegers SET value = value + 1 WHERE key = new.resourceType + 2; + RETURN new; + ELSIF TG_OP = 'DELETE' THEN + UPDATE GlobalIntegers SET value = value - 1 WHERE key = old.resourceType + 2; + RETURN old; + END IF; +END; +$$ LANGUAGE plpgsql; + +CREATE TRIGGER CountResourcesTracker +AFTER INSERT OR DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE CountResourcesTrackerFunc(); + +-- this trigger was introduced in 6.2, remove it: +DROP FUNCTION IF EXISTS InsertOrUpdateMetadata; + +-- reinstall old triggers: +CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0; + UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0; + UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER AttachedFileIncrementSize ON AttachedFiles; +CREATE TRIGGER AttachedFileIncrementSize +AFTER INSERT ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileIncrementSizeFunc(); + +DROP TRIGGER AttachedFileDecrementSize ON AttachedFiles; +CREATE TRIGGER AttachedFileDecrementSize +AFTER DELETE ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileDecrementSizeFunc(); + +-- these functions have been introduced in 6.2: +DROP FUNCTION IF EXISTS UpdateStatistics; +DROP FUNCTION IF EXISTS UpdateSingleStatistic; + +-- this table has been introduced in 6.2: +DROP TABLE IF EXISTS GlobalIntegersChanges; + +-- these functions have been introduced in 6.2: +DROP FUNCTION IF EXISTS CreateDeletedFilesTemporaryTable; +DROP FUNCTION IF EXISTS DeleteResource; + +-- reinstall this old trigger: +CREATE OR REPLACE FUNCTION ResourceDeletedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + --RAISE NOTICE 'Delete resource %', old.parentId; + INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); + + -- http://stackoverflow.com/a/11299968/881731 + IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN + -- Signal that the deleted resource has a remaining parent + INSERT INTO RemainingAncestor + SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId; + ELSE + -- Delete a parent resource when its unique child is deleted + DELETE FROM Resources WHERE internalId = old.parentId; + END IF; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; +CREATE TRIGGER ResourceDeleted +AFTER DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE ResourceDeletedFunc(); + +-- reinstall this old trigger: +CREATE OR REPLACE FUNCTION PatientAddedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + -- The "0" corresponds to "OrthancPluginResourceType_Patient" + IF new.resourceType = 0 THEN + INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId); + END IF; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS PatientAdded ON Resources; +CREATE TRIGGER PatientAdded +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE PatientAddedFunc(); + + +-- 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, 1); -- GlobalProperty_DatabasePatchLevel +INSERT INTO GlobalProperties VALUES (11, 2); -- GlobalProperty_HasCreateInstance \ No newline at end of file
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Mon Feb 05 09:48:11 2024 +0100 @@ -0,0 +1,561 @@ +-- This SQL file creates a DB in version.revision 6.2 directly +-- It is also run after upgrade scripts to create new tables and or create/replace triggers and functions. +-- This script is self contained, it contains everything that needs to be run to create an Orthanc DB. +-- Note to developers: +-- - it is and must stay idempotent. +-- - it is executed when the DB is "locked", only one Orthanc instance can execute it at a given time. + +CREATE TABLE IF NOT EXISTS GlobalProperties( + property INTEGER PRIMARY KEY, + value TEXT + ); + +CREATE TABLE IF NOT EXISTS Resources( + internalId BIGSERIAL NOT NULL PRIMARY KEY, + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL, + parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + CONSTRAINT UniquePublicId UNIQUE (publicId) + ); + +CREATE TABLE IF NOT EXISTS MainDicomTags( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE TABLE IF NOT EXISTS DicomIdentifiers( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + tagGroup INTEGER, + tagElement INTEGER, + value TEXT, + PRIMARY KEY(id, tagGroup, tagElement) + ); + +CREATE TABLE IF NOT EXISTS Metadata( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + type INTEGER NOT NULL, + value TEXT, + revision INTEGER, + PRIMARY KEY(id, type) + ); + +CREATE TABLE IF NOT EXISTS AttachedFiles( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + fileType INTEGER, + uuid VARCHAR(64) NOT NULL, + compressedSize BIGINT, + uncompressedSize BIGINT, + compressionType INTEGER, + uncompressedHash VARCHAR(40), + compressedHash VARCHAR(40), + revision INTEGER, + PRIMARY KEY(id, fileType) + ); + +CREATE TABLE IF NOT EXISTS Changes( + seq BIGSERIAL NOT NULL PRIMARY KEY, + changeType INTEGER, + internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + resourceType INTEGER, + date VARCHAR(64) + ); + +CREATE TABLE IF NOT EXISTS ExportedResources( + seq BIGSERIAL NOT NULL PRIMARY KEY, + resourceType INTEGER, + publicId VARCHAR(64), + remoteModality TEXT, + patientId VARCHAR(64), + studyInstanceUid TEXT, + seriesInstanceUid TEXT, + sopInstanceUid TEXT, + date VARCHAR(64) + ); + +CREATE TABLE IF NOT EXISTS PatientRecyclingOrder( + seq BIGSERIAL NOT NULL PRIMARY KEY, + patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + CONSTRAINT UniquePatientId UNIQUE (patientId) + ); + +CREATE TABLE IF NOT EXISTS Labels( + id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE, + label TEXT, + PRIMARY KEY(id, label) + ); + +CREATE TABLE IF NOT EXISTS GlobalIntegers( + key INTEGER PRIMARY KEY, + value BIGINT); +-- GlobalIntegers keys: +-- 0: CompressedSize +-- 1: UncompressedSize +-- 2: PatientsCount +-- 3: StudiesCount +-- 4: SeriesCount +-- 5: InstancesCount +-- 6: ChangeSeq +-- 7: PatientRecyclingOrderSeq + +CREATE TABLE IF NOT EXISTS ServerProperties( + server VARCHAR(64) NOT NULL, + property INTEGER, value TEXT, + PRIMARY KEY(server, property) + ); + +CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId); +CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId); +CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType); +CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId); + +CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement); +CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value); + +CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId); +CREATE INDEX IF NOT EXISTS LabelsIndex1 ON LABELS(id); +CREATE INDEX IF NOT EXISTS LabelsIndex2 ON LABELS(label); + +------------------- Trigram index creation ------------------- + + +-- Apply fix for performance issue (speed up wildcard search by using GIN trigrams). This implements the patch suggested +-- in issue #47, BUT we also keep the original "DicomIdentifiersIndexValues", as it leads to better +-- performance for "strict" searches (i.e. searches involving no wildcard). +-- https://www.postgresql.org/docs/current/static/pgtrgm.html +-- https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47 + +DO $body$ +begin + IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_trgm') THEN + CREATE EXTENSION IF NOT EXISTS pg_trgm; + CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops); + ELSE + RAISE NOTICE 'pg_trgm extension is not available on you system'; + END IF; +END $body$; + + +------------------- PatientAdded trigger & PatientRecyclingOrder ------------------- +DROP TRIGGER IF EXISTS PatientAdded ON Resources; + +CREATE OR REPLACE FUNCTION PatientAddedOrUpdated( + IN patient_id BIGINT, + IN is_update BIGINT + ) +RETURNS VOID AS $body$ +BEGIN + DECLARE + newSeq BIGINT; + BEGIN + UPDATE GlobalIntegers SET value = value + 1 WHERE key = 7 RETURNING value INTO newSeq; + IF is_update > 0 THEN + -- Note: Protected patients are not listed in this table ! So, they won't be updated + UPDATE PatientRecyclingOrder SET seq = newSeq WHERE PatientRecyclingOrder.patientId = patient_id; + ELSE + INSERT INTO PatientRecyclingOrder VALUES (newSeq, patient_id); + END IF; + END; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION PatientAddedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + -- The "0" corresponds to "OrthancPluginResourceType_Patient" + IF new.resourceType = 0 THEN + PERFORM PatientAddedOrUpdated(new.internalId, 0); + END IF; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER PatientAdded +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE PatientAddedFunc(); + +-- initial population of PatientRecyclingOrderSeq +INSERT INTO GlobalIntegers + SELECT 7, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM PatientRecyclingOrder + ON CONFLICT DO NOTHING; + + +------------------- ResourceDeleted trigger ------------------- +DROP TRIGGER IF EXISTS ResourceDeleted ON Resources; + +-- The following trigger combines 2 triggers from SQLite: +-- ResourceDeleted + ResourceDeletedParentCleaning +CREATE OR REPLACE FUNCTION ResourceDeletedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId; + INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId); + + -- If this resource is the latest child, delete the parent + DELETE FROM Resources WHERE internalId = old.parentId + AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER ResourceDeleted +AFTER DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE ResourceDeletedFunc(); + + +------------------- DeleteResource function ------------------- + +CREATE OR REPLACE FUNCTION DeleteResource( + IN id BIGINT, + OUT remaining_ancestor_resource_type INTEGER, + OUT remaining_anncestor_public_id TEXT) AS $body$ + +DECLARE + deleted_row RECORD; + locked_row RECORD; + +BEGIN + + SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping + + -- note: temporary tables are created at session (connection) level -> they are likely to exist + -- these tables are used by the triggers + CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources( + resourceType INTEGER NOT NULL, + publicId VARCHAR(64) NOT NULL + ); + + RESET client_min_messages; + + -- clear the temporary table in case it has been created earlier in the session + DELETE FROM DeletedResources; + + -- create/clear the DeletedFiles temporary table + PERFORM CreateDeletedFilesTemporaryTable(); + + -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that + -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize + -- that they are deleting the last instance and the parent resources would not be deleted. + -- Locking only the immediate parent is sufficient to prevent from this. + SELECT * INTO locked_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE; + + -- delete the resource itself + DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row; + -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + + + -- If this resource still has siblings, keep track of the remaining parent + -- (a parent that must not be deleted but whose LastUpdate must be updated) + SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id + FROM Resources + WHERE internalId = deleted_row.parentId + AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId); + +END; + +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable( +) RETURNS VOID AS $body$ + +BEGIN + + SET client_min_messages = warning; -- suppress NOTICE: relation "deletedresources" already exists, skipping + + -- note: temporary tables are created at session (connection) level -> they are likely to exist + CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles( + uuid VARCHAR(64) NOT NULL, + fileType INTEGER, + compressedSize BIGINT, + uncompressedSize BIGINT, + compressionType INTEGER, + uncompressedHash VARCHAR(40), + compressedHash VARCHAR(40) + ); + + RESET client_min_messages; + + -- clear the temporary table in case it has been created earlier in the session + DELETE FROM DeletedFiles; +END; + +$body$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc() +RETURNS TRIGGER AS $body$ +BEGIN + INSERT INTO DeletedFiles VALUES + (old.uuid, old.filetype, old.compressedSize, + old.uncompressedSize, old.compressionType, + old.uncompressedHash, old.compressedHash); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS AttachedFileDeleted on AttachedFiles; +CREATE TRIGGER AttachedFileDeleted +AFTER DELETE ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileDeletedFunc(); + + +------------------- Fast Statistics ------------------- + +-- initial population of GlobalIntegers if not already there +INSERT INTO GlobalIntegers + SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0 -- Count patients + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1 -- Count studies + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2 -- Count series + ON CONFLICT DO NOTHING; + +INSERT INTO GlobalIntegers + SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3 -- Count instances + ON CONFLICT DO NOTHING; + + +-- this table stores all changes that needs to be performed to the GlobalIntegers table +-- This way, each transaction can add row independently in this table without having to lock +-- any row (which was the case with previous FastTotalSize). +-- These changes will be applied at regular interval by an external thread or when someone +-- requests the statistics +CREATE TABLE IF NOT EXISTS GlobalIntegersChanges( + key INTEGER, + value BIGINT); + +CREATE OR REPLACE FUNCTION UpdateSingleStatistic( + IN statistics_key INTEGER, + OUT new_value BIGINT +) AS $body$ +BEGIN + + -- Delete the current changes, sum them and update the GlobalIntegers row. + -- New rows can be added in the meantime, they won't be deleted or summed. + WITH deleted_rows AS ( + DELETE FROM GlobalIntegersChanges + WHERE GlobalIntegersChanges.key = statistics_key + RETURNING value + ) + UPDATE GlobalIntegers + SET value = value + ( + SELECT COALESCE(SUM(value), 0) + FROM deleted_rows + ) + WHERE GlobalIntegers.key = statistics_key + RETURNING value INTO new_value; + +END; +$body$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION UpdateStatistics( + OUT patients_cunt BIGINT, + OUT studies_count BIGINT, + OUT series_count BIGINT, + OUT instances_count BIGINT, + OUT total_compressed_size BIGINT, + OUT total_uncompressed_size BIGINT +) AS $body$ +BEGIN + + SELECT UpdateSingleStatistic(0) INTO total_compressed_size; + SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size; + SELECT UpdateSingleStatistic(2) INTO patients_cunt; + SELECT UpdateSingleStatistic(3) INTO studies_count; + SELECT UpdateSingleStatistic(4) INTO series_count; + SELECT UpdateSingleStatistic(5) INTO instances_count; + +END; +$body$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc() +RETURNS TRIGGER AS $$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc() +RETURNS TRIGGER AS $$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1); + RETURN NULL; +END; +$$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize); + INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize); + INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize); + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles; +CREATE TRIGGER AttachedFileIncrementSize +AFTER INSERT ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileIncrementSizeFunc(); + +DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles; +CREATE TRIGGER AttachedFileDecrementSize +AFTER DELETE ON AttachedFiles +FOR EACH ROW +EXECUTE PROCEDURE AttachedFileDecrementSizeFunc(); + +DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources; +CREATE TRIGGER IncrementResourcesTracker +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE IncrementResourcesTrackerFunc(); + +DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources; +CREATE TRIGGER DecrementResourcesTracker +AFTER DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE DecrementResourcesTrackerFunc(); + + +------------------- InsertOrUpdateMetadata function ------------------- +CREATE OR REPLACE FUNCTION InsertOrUpdateMetadata(resource_ids BIGINT[], + metadata_types INTEGER[], + metadata_values TEXT[], + revisions INTEGER[]) +RETURNS VOID AS $body$ +BEGIN + FOR i IN 1 .. ARRAY_LENGTH(resource_ids, 1) LOOP + -- RAISE NOTICE 'Parameter %: % % %', i, resource_ids[i], metadata_types[i], metadata_values[i]; + INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i]) + ON CONFLICT (id, type) DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision; + END LOOP; + +END; +$body$ LANGUAGE plpgsql; + + +------------------- GetLastChange function ------------------- +DROP TRIGGER IF EXISTS InsertedChange ON Changes; + +-- insert the value if not already there +INSERT INTO GlobalIntegers + SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes + ON CONFLICT DO NOTHING; + +CREATE OR REPLACE FUNCTION InsertedChangeFunc() +RETURNS TRIGGER AS $body$ +BEGIN + UPDATE GlobalIntegers SET value = new.seq WHERE key = 6; + RETURN NULL; +END; +$body$ LANGUAGE plpgsql; + +CREATE TRIGGER InsertedChange +AFTER INSERT ON Changes +FOR EACH ROW +EXECUTE PROCEDURE InsertedChangeFunc(); + + +------------------- 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 +DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13); +INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion +INSERT INTO GlobalProperties VALUES (4, 2); -- 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
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql Mon Feb 05 09:48:11 2024 +0100 @@ -0,0 +1,48 @@ +-- This file contains part of the changes required to upgrade from 6.1 to 6.2 (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 +-- This file is executed only if the current schema is in revision 1 and it is executed +-- before PrepareIndex.sql that is idempotent. + + +-- add unique constraints if they do not exists +DO $body$ +BEGIN + + IF NOT EXISTS ( + SELECT 1 + FROM information_schema.table_constraints + WHERE table_schema = 'public' + AND table_name = 'resources' + AND constraint_name = 'uniquepublicid') + THEN + ALTER TABLE Resources ADD CONSTRAINT UniquePublicId UNIQUE (publicId); + RAISE NOTICE 'UniquePublicId constraint added to Resources.'; + END IF; + + IF NOT EXISTS ( + SELECT 1 + FROM information_schema.table_constraints + WHERE table_schema = 'public' + AND table_name = 'patientrecyclingorder' + AND constraint_name = 'uniquepatientid') + THEN + ALTER TABLE PatientRecyclingOrder ADD CONSTRAINT UniquePatientId UNIQUE (patientId); + RAISE NOTICE 'UniquePatientId constraint added to PatientRecyclingOrder.'; + END IF; + +END $body$ LANGUAGE plpgsql; + + +-- In V6.2, we'll now use temporary tables so we need to remove the old tables that might have been used in previous revisions ! +-- these statements, although idempotent, are not part of PrepareIndexV2.sql to keep it clean +DROP TABLE IF EXISTS DeletedFiles; +DROP TABLE IF EXISTS RemainingAncestor; +DROP TABLE IF EXISTS DeletedResources; + +-- These triggers disappears and are not replaced in V6.2 +DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources; + +-- The signature has changed so we must delete the function before replacing it. +DROP FUNCTION IF EXISTS CreateInstance;
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/SQL/Upgrades/UnknownToRev1.sql Mon Feb 05 09:48:11 2024 +0100 @@ -0,0 +1,18 @@ +-- add the revision columns if not yet done + +DO $body$ +BEGIN + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='metadata' AND column_name='revision') THEN + ALTER TABLE Metadata ADD COLUMN revision INTEGER; + ELSE + raise notice 'the metadata.revision column already exists'; + END IF; + + IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='attachedfiles' AND column_name='revision') THEN + ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER; + ELSE + raise notice 'the attachedfiles.revision column already exists'; + END IF; + +END $body$; +
--- a/TODO Wed Jan 31 14:54:41 2024 +0100 +++ b/TODO Mon Feb 05 09:48:11 2024 +0100 @@ -10,8 +10,6 @@ * Performance of joins in LookupResources: Create cached statement for LookupResources, that are grouped to search up to, say, 10 tags, instead of recompiling for each request -* Do not log "DatabaseCannotSerialize" errors in the plugin but only - in Orthanc after all retries have been made. --------------------- Common - Storage area