Mercurial > hg > orthanc-databases
changeset 436:f16faa1fdc46 pg-transactions
InsertOrUpdateMetadata function + UpdateAndGetStatistics
| author | Alain Mazy <am@osimis.io> |
|---|---|
| date | Fri, 15 Dec 2023 17:11:26 +0100 |
| parents | 326f8304daa1 |
| children | d979f25e60cf |
| files | Framework/Plugins/DatabaseBackendAdapterV4.cpp Framework/Plugins/IDatabaseBackend.h Framework/Plugins/IndexBackend.cpp Framework/Plugins/IndexBackend.h NOTES PostgreSQL/CMakeLists.txt PostgreSQL/Plugins/FastTotalStats2.sql PostgreSQL/Plugins/InsertOrUpdateMetadata.sql PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/PostgreSQLIndex.h |
| diffstat | 10 files changed, 473 insertions(+), 31 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp Thu Dec 14 09:25:45 2023 +0100 +++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp Fri Dec 15 17:11:26 2023 +0100 @@ -432,6 +432,7 @@ response.mutable_get_system_information()->set_supports_revisions(accessor.GetBackend().HasRevisionsSupport()); response.mutable_get_system_information()->set_supports_labels(accessor.GetBackend().HasLabelsSupport()); 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()); break; } @@ -943,6 +944,20 @@ break; } + 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; + } + case Orthanc::DatabasePluginMessages::OPERATION_INCREMENT_GLOBAL_PROPERTY: { int64_t value = backend.IncrementGlobalProperty(manager, request.increment_global_property().server_id().c_str(),
--- a/Framework/Plugins/IDatabaseBackend.h Thu Dec 14 09:25:45 2023 +0100 +++ b/Framework/Plugins/IDatabaseBackend.h Fri Dec 15 17:11:26 2023 +0100 @@ -355,5 +355,16 @@ const char* serverIdentifier, int32_t property, int64_t increment) = 0; + + virtual bool HasUpdateAndGetStatistics() = 0; + + virtual void UpdateAndGetStatistics(DatabaseManager& manager, + int64_t& patientsCount, + int64_t& studiesCount, + int64_t& seriesCount, + int64_t& instancesCount, + int64_t& compressedSize, + int64_t& uncompressedSize) = 0; + }; }
--- a/Framework/Plugins/IndexBackend.cpp Thu Dec 14 09:25:45 2023 +0100 +++ b/Framework/Plugins/IndexBackend.cpp Fri Dec 15 17:11:26 2023 +0100 @@ -1219,7 +1219,23 @@ { 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); + } + void IndexBackend::LookupIdentifier(std::list<int64_t>& target /*out*/, DatabaseManager& manager, OrthancPluginResourceType resourceType,
--- a/Framework/Plugins/IndexBackend.h Thu Dec 14 09:25:45 2023 +0100 +++ b/Framework/Plugins/IndexBackend.h Fri Dec 15 17:11:26 2023 +0100 @@ -405,6 +405,15 @@ 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; /** * "maxDatabaseRetries" is to handle
--- a/NOTES Thu Dec 14 09:25:45 2023 +0100 +++ b/NOTES Fri Dec 15 17:11:26 2023 +0100 @@ -169,32 +169,102 @@ In debug, no verbose logs 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 (serializable mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: XX s -Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 19.861 s -Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 20.027 s (with temporary tables) - test_concurrent_anonymize_same_study deletion took: 28.4 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 (serializable mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 23.010 s -Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 1.71 s (with RemainingAncestor.id) -Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 2.97 s (with temporary tables) +Orthanc mainline + PG mainline (read-committed mode) : test_concurrent_uploads_same_study with 20 workers and 1x repeat: 6.57 s (with temporary tables) Orthanc 1.12.1 + PG 5.1 (serializable mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 23.016 s -Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 8.788 s -Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 11.872 s (with temporary tables) +Orthanc mainline + PG mainline (read-committed mode) : test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 7.129 s + +With Docker with 10 connections SQL: +osimis/orthanc:23.11.0: TIMING test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 25.047 s +osimis/orthanc:current: TIMING test_concurrent_anonymize_same_study with 4 workers and 10x repeat: 8.649 s + +osimis/orthanc:23.11.0: TIMING test_concurrent_anonymize_same_study deletion took: 11.807 s +osimis/orthanc:current: TIMING test_concurrent_anonymize_same_study deletion took: 10.620 s + +osimis/orthanc:23.11.0: TIMING test_concurrent_uploads_same_study with 20 workers and 1x repeat: 10.736 s +osimis/orthanc:current: TIMING test_concurrent_uploads_same_study with 20 workers and 1x repeat: 3.465 s + +osimis/orthanc:23.11.0: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 11.966 s +osimis/orthanc:current: TIMING test_upload_delete_same_study_from_multiple_threads with 5 workers and 30x repeat: 5.092 s + TODO: -- assert((statement.Next(), statement.IsDone())) commented out -> create temporary tables ? - try again temporary tables (with different names ? Have a switch in the code ?) +- check RETURNS SET OF ! + CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$ + SELECT * FROM foo WHERE fooid = $1; + $$ LANGUAGE SQL; - have a separate "thread" to increment/decrement statistics because everybody is fighting to modify the GlobalIntegers rows + ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i", -> make unique + handle on conflicts + countIdentifierTags, identifierTags); + + ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t", -> make unique + handle on conflicts + countMainDicomTags, mainDicomTags); + + ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata); handle on conflicts + +CREATE OR REPLACE FUNCTION InsertOrReplaceMetadata(VARIADIC params SETOF metadata) +RETURNS VOID AS $$ +BEGIN + + FOR i IN 1..array_length(my_params, 1) LOOP + my_sum := my_sum + my_params[i].my_column; + END LOOP; + RETURN my_sum; +END; +$$ LANGUAGE plpgsql; + +#include <postgresql/libpq-fe.h> + +int main() { + PGconn *conn = PQconnectdb("dbname=mydb user=myuser password=mypassword"); + if (PQstatus(conn) != CONNECTION_OK) { + std::cerr << "Connection to database failed: " << PQerrorMessage(conn) << std::endl; + PQfinish(conn); + return 1; + } + + const char *function_name = "my_function"; + int num_params = 3; + int params[] = {1, 2, 3}; + + char *query = PQescapeLiteral(conn, function_name, strlen(function_name)); + for (int i = 0; i < num_params; i++) { + char *param = PQescapeLiteral(conn, (const char *)¶ms[i], sizeof(int)); + query = (char *)realloc(query, strlen(query) + strlen(param) + 1); + strcat(query, ","); + strcat(query, param); + PQfreemem(param); + } + strcat(query, ")"); + + PGresult *res = PQexec(conn, query); + if (PQresultStatus(res) != PGRES_TUPLES_OK) { + std::cerr << "Function call failed: " << PQerrorMessage(conn) << std::endl; + PQfreemem(query); + PQclear(res); + PQfinish(conn); + return 1; + } + + int result = atoi(PQgetvalue(res, 0, 0)); + std::cout << "Result: " << result << std::endl; + + PQfreemem(query); + PQclear(res); + PQfinish(conn); + + - test events generation StableSeries .... -- test RemainingAncestor response in integration tests: OK -- disable MySQL ODBC plugin - run tests with docker localy + in CI - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820 - PatientAddedFunc contains an IF - validate upgrade DB from previous Orthanc and from scratch - test with older version of PG +- In Docker images, re-enable MySQL & ODBC plugins + tests DONE: - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP)
--- a/PostgreSQL/CMakeLists.txt Thu Dec 14 09:25:45 2023 +0100 +++ b/PostgreSQL/CMakeLists.txt Fri Dec 15 17:11:26 2023 +0100 @@ -83,6 +83,8 @@ POSTGRESQL_FAST_COUNT_RESOURCES ${CMAKE_SOURCE_DIR}/Plugins/FastCountResources.sql POSTGRESQL_GET_LAST_CHANGE_INDEX ${CMAKE_SOURCE_DIR}/Plugins/GetLastChangeIndex.sql POSTGRESQL_RESOURCE_DELETED_FUNC ${CMAKE_SOURCE_DIR}/Plugins/ResourceDeletedFunc.sql + POSTGRESQL_INSERT_UPDATE_METADATA ${CMAKE_SOURCE_DIR}/Plugins/InsertOrUpdateMetadata.sql + POSTGRESQL_FAST_TOTAL_STATS_2 ${CMAKE_SOURCE_DIR}/Plugins/FastTotalStats2.sql )
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/FastTotalStats2.sql Fri Dec 15 17:11:26 2023 +0100 @@ -0,0 +1,116 @@ +-- uninstall FastTotalSize & FastCountResources +DROP TRIGGER IF EXISTS AttachedFileIncrementSize ON AttachedFiles; +DROP TRIGGER IF EXISTS AttachedFileDecrementSize ON AttachedFiles; +DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources; + +-- 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 statisticsKey INTEGER, + OUT newValue 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 = statisticsKey + RETURNING value + ) + UPDATE GlobalIntegers + SET value = value + ( + SELECT COALESCE(SUM(value), 0) + FROM deleted_rows + ) + WHERE GlobalIntegers.key = statisticsKey + RETURNING value INTO newValue; + +END; +$body$ LANGUAGE plpgsql; + + +CREATE OR REPLACE FUNCTION UpdateStatistics( + OUT patientsCount BIGINT, + OUT studiesCount BIGINT, + OUT seriesCount BIGINT, + OUT instancesCount BIGINT, + OUT totalCompressedSize BIGINT, + OUT totalUncompressedSize BIGINT +) AS $body$ +BEGIN + + SELECT UpdateSingleStatistic(0) INTO totalCompressedSize; + SELECT UpdateSingleStatistic(1) INTO totalUncompressedSize; + SELECT UpdateSingleStatistic(2) INTO patientsCount; + SELECT UpdateSingleStatistic(3) INTO studiesCount; + SELECT UpdateSingleStatistic(4) INTO seriesCount; + SELECT UpdateSingleStatistic(5) INTO instancesCount; + +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; + + + +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(); + +CREATE TRIGGER IncrementResourcesTracker +AFTER INSERT ON Resources +FOR EACH ROW +EXECUTE PROCEDURE IncrementResourcesTrackerFunc(); + +CREATE TRIGGER DecrementResourcesTracker +AFTER DELETE ON Resources +FOR EACH ROW +EXECUTE PROCEDURE DecrementResourcesTrackerFunc();
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/PostgreSQL/Plugins/InsertOrUpdateMetadata.sql Fri Dec 15 17:11:26 2023 +0100 @@ -0,0 +1,13 @@ +CREATE OR REPLACE FUNCTION InsertOrUpdateMetadata(resourceIds BIGINT[], + metadataTypes INTEGER[], + metadataValues TEXT[], + revisions INTEGER[]) +RETURNS VOID AS $body$ +BEGIN + FOR i IN 1 .. ARRAY_LENGTH(resourceIds, 1) LOOP + -- RAISE NOTICE 'Parameter %: % % %', i, resourceIds[i], metadataTypes[i], metadataValues[i]; + INSERT INTO Metadata VALUES(resourceIds[i], metadataTypes[i], metadataValues[i], revisions[i]) ON CONFLICT DO NOTHING; + END LOOP; + +END; +$body$ LANGUAGE plpgsql; \ No newline at end of file
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp Thu Dec 14 09:25:45 2023 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp Fri Dec 15 17:11:26 2023 +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> @@ -90,6 +91,7 @@ { PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP); + bool shouldInstallFastTotalStats2 = false; if (clearAll_) { @@ -135,27 +137,36 @@ if (revision == 1) { - LOG(WARNING) << "PostgreSQL plugin: adding UNIQUE(publicId) constraint to the 'Resources' table "; - t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Resources ADD UNIQUE (publicId);"); + { + LOG(WARNING) << "PostgreSQL plugin: adding UNIQUE(publicId) constraint to the 'Resources' table "; + t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Resources ADD UNIQUE (publicId);"); + } + + { + LOG(WARNING) << "PostgreSQL plugin: adding or replacing ResourceDeletedFunc"; + + std::string query; + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_RESOURCE_DELETED_FUNC); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + } + + { + LOG(WARNING) << "PostgreSQL plugin: adding or replacing InsertOrUpdateMetadata"; + + std::string query; + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_INSERT_UPDATE_METADATA); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + } + + shouldInstallFastTotalStats2 = true; revision = 2; SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); } - if (revision == 2) - { - LOG(WARNING) << "PostgreSQL plugin: adding or replacing ResourceDeletedFunc"; - - std::string query; - Orthanc::EmbeddedResources::GetFileResource - (query, Orthanc::EmbeddedResources::POSTGRESQL_RESOURCE_DELETED_FUNC); - t.GetDatabaseTransaction().ExecuteMultiLines(query); - - revision = 3; - SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision); - } - - if (revision != 3) + if (revision != 2) { LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision; throw Orthanc::OrthancException(Orthanc::ErrorCode_Database); @@ -240,7 +251,6 @@ SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 3); } - if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetTotalSizeIsFast) || property != 1) @@ -291,6 +301,16 @@ SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1); } + if (shouldInstallFastTotalStats2) + { + LOG(WARNING) << "PostgreSQL plugin: installing FastTotalStats2 to replace FastTotalSize and FastCountResources"; + + std::string query; + Orthanc::EmbeddedResources::GetFileResource + (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_STATS_2); + t.GetDatabaseTransaction().ExecuteMultiLines(query); + } + t.Commit(); } @@ -465,6 +485,28 @@ } } + 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 @@ -596,6 +638,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) {
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h Thu Dec 14 09:25:45 2023 +0100 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.h Fri Dec 15 17:11:26 2023 +0100 @@ -69,6 +69,14 @@ 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; virtual uint64_t GetTotalUncompressedSize(DatabaseManager& manager) ORTHANC_OVERRIDE; @@ -112,5 +120,18 @@ 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; + }; }