Mercurial > hg > orthanc-databases
changeset 707:86e27eadc5cc sql-opti
added support for audit logs
author | Alain Mazy <am@orthanc.team> |
---|---|
date | Tue, 15 Jul 2025 10:19:23 +0200 (11 days ago) |
parents | 2bf70b3ef0d5 |
children | 43245004c8e2 |
files | Framework/Plugins/DatabaseBackendAdapterV4.cpp Framework/Plugins/IDatabaseBackend.h Framework/Plugins/IndexBackend.cpp Framework/Plugins/IndexBackend.h Framework/Plugins/MessagesToolbox.h MySQL/Plugins/MySQLIndex.h Odbc/Plugins/OdbcIndex.h PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.h PostgreSQL/Plugins/SQL/Downgrades/Rev599ToRev5.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql SQLite/Plugins/SQLiteIndex.h |
diffstat | 12 files changed, 124 insertions(+), 3 deletions(-) [+] |
line wrap: on
line diff
--- a/Framework/Plugins/DatabaseBackendAdapterV4.cpp Mon Jun 30 09:49:36 2025 +0200 +++ b/Framework/Plugins/DatabaseBackendAdapterV4.cpp Tue Jul 15 10:19:23 2025 +0200 @@ -467,6 +467,10 @@ response.mutable_get_system_information()->set_supports_key_value_stores(accessor.GetBackend().HasKeyValueStores()); #endif +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + response.mutable_get_system_information()->set_supports_audit_logs(accessor.GetBackend().HasAuditLogs()); +#endif + break; } @@ -1406,6 +1410,17 @@ break; #endif +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + case Orthanc::DatabasePluginMessages::OPERATION_RECORD_AUDIT_LOG: + backend.RecordAuditLog(manager, + request.record_audit_log().user_id(), + Convert(request.record_audit_log().resource_type()), + request.record_audit_log().resource_id(), + request.record_audit_log().action(), + request.record_audit_log().log_data()); + break; + +#endif default: LOG(ERROR) << "Not implemented transaction operation from protobuf: " << request.operation(); throw Orthanc::OrthancException(Orthanc::ErrorCode_ParameterOutOfRange);
--- a/Framework/Plugins/IDatabaseBackend.h Mon Jun 30 09:49:36 2025 +0200 +++ b/Framework/Plugins/IDatabaseBackend.h Tue Jul 15 10:19:23 2025 +0200 @@ -65,6 +65,8 @@ virtual bool HasQueues() const = 0; + virtual bool HasAuditLogs() const = 0; + virtual void AddAttachment(DatabaseManager& manager, int64_t id, const OrthancPluginAttachment& attachment, @@ -459,6 +461,15 @@ const std::string& customData) = 0; #endif +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + virtual void RecordAuditLog(DatabaseManager& manager, + const std::string& userId, + OrthancPluginResourceType type, + const std::string& resourceId, + const std::string& action, + const std::string& value) = 0; +#endif + virtual bool HasPerformDbHousekeeping() = 0; virtual void PerformDbHousekeeping(DatabaseManager& manager) = 0;
--- a/Framework/Plugins/IndexBackend.cpp Mon Jun 30 09:49:36 2025 +0200 +++ b/Framework/Plugins/IndexBackend.cpp Tue Jul 15 10:19:23 2025 +0200 @@ -4682,4 +4682,37 @@ statement.Execute(args); } #endif + +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + void IndexBackend::RecordAuditLog(DatabaseManager& manager, + const std::string& userId, + OrthancPluginResourceType resourceType, + const std::string& resourceId, + const std::string& action, + const std::string& value) + { + DatabaseManager::CachedStatement statement( + STATEMENT_FROM_HERE, manager, + "INSERT INTO AuditLogs (userId, resourceType, resourceId, action, logData) " + "VALUES(${userId}, ${resourceType}, ${resourceId}, ${action}, ${logData})"); + + statement.SetParameterType("userId", ValueType_Utf8String); + statement.SetParameterType("resourceId", ValueType_Utf8String); + statement.SetParameterType("resourceType", ValueType_Integer64); + statement.SetParameterType("action", ValueType_Utf8String); + statement.SetParameterType("userId", ValueType_Utf8String); + statement.SetParameterType("logData", ValueType_BinaryString); + + Dictionary args; + args.SetUtf8Value("userId", userId); + args.SetIntegerValue("resourceType", static_cast<int>(resourceType)); + args.SetUtf8Value("resourceId", resourceId); + args.SetUtf8Value("action", action); + args.SetUtf8Value("userId", userId); + args.SetBinaryValue("logData", value); + + statement.Execute(args); + } +#endif + }
--- a/Framework/Plugins/IndexBackend.h Mon Jun 30 09:49:36 2025 +0200 +++ b/Framework/Plugins/IndexBackend.h Tue Jul 15 10:19:23 2025 +0200 @@ -521,6 +521,16 @@ #endif +#if ORTHANC_PLUGINS_HAS_AUDIT_LOGS == 1 + virtual void RecordAuditLog(DatabaseManager& manager, + const std::string& userId, + OrthancPluginResourceType type, + const std::string& resourceId, + const std::string& action, + const std::string& value) ORTHANC_OVERRIDE; +#endif + + virtual bool HasPerformDbHousekeeping() ORTHANC_OVERRIDE { return false;
--- a/Framework/Plugins/MessagesToolbox.h Mon Jun 30 09:49:36 2025 +0200 +++ b/Framework/Plugins/MessagesToolbox.h Tue Jul 15 10:19:23 2025 +0200 @@ -54,6 +54,7 @@ #define ORTHANC_PLUGINS_HAS_ATTACHMENTS_CUSTOM_DATA 0 #define ORTHANC_PLUGINS_HAS_KEY_VALUE_STORES 0 #define ORTHANC_PLUGINS_HAS_QUEUES 0 +#define ORTHANC_PLUGINS_HAS_AUDIT_LOGS 0 #if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) # if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 8) @@ -66,6 +67,15 @@ # endif #endif + +#if defined(ORTHANC_PLUGINS_VERSION_IS_ABOVE) +# if ORTHANC_PLUGINS_VERSION_IS_ABOVE(1, 12, 9) +# undef ORTHANC_PLUGINS_HAS_AUDIT_LOGS +# define ORTHANC_PLUGINS_HAS_AUDIT_LOGS 1 +# endif +#endif + + #include <Enumerations.h>
--- a/MySQL/Plugins/MySQLIndex.h Mon Jun 30 09:49:36 2025 +0200 +++ b/MySQL/Plugins/MySQLIndex.h Tue Jul 15 10:19:23 2025 +0200 @@ -76,6 +76,11 @@ return false; } + virtual bool HasAuditLogs() const ORTHANC_OVERRIDE + { + return false; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type)
--- a/Odbc/Plugins/OdbcIndex.h Mon Jun 30 09:49:36 2025 +0200 +++ b/Odbc/Plugins/OdbcIndex.h Tue Jul 15 10:19:23 2025 +0200 @@ -88,6 +88,11 @@ return false; } + virtual bool HasAuditLogs() const ORTHANC_OVERRIDE + { + return false; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type) ORTHANC_OVERRIDE;
--- a/PostgreSQL/NEWS Mon Jun 30 09:49:36 2025 +0200 +++ b/PostgreSQL/NEWS Tue Jul 15 10:19:23 2025 +0200 @@ -20,6 +20,7 @@ of being idle or one hour after their creation. * New metrics "orthanc_index_active_connections_count" showing the current number of active connections. +* Added support for AuditLogs. Maintenance: * Optimized the CreateInstance SQL query.
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h Mon Jun 30 09:49:36 2025 +0200 +++ b/PostgreSQL/Plugins/PostgreSQLIndex.h Tue Jul 15 10:19:23 2025 +0200 @@ -87,6 +87,11 @@ return true; } + virtual bool HasAuditLogs() const ORTHANC_OVERRIDE + { + return true; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type) ORTHANC_OVERRIDE;
--- a/PostgreSQL/Plugins/SQL/Downgrades/Rev599ToRev5.sql Mon Jun 30 09:49:36 2025 +0200 +++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev599ToRev5.sql Tue Jul 15 10:19:23 2025 +0200 @@ -233,6 +233,14 @@ ADD CONSTRAINT resources_parentid_fkey FOREIGN KEY (parentId) REFERENCES Resources(internalId) ON DELETE CASCADE; +-- Remove the AuditLogs table +----------- + +DROP INDEX IF EXISTS AuditLogsUserId; +DROP INDEX IF EXISTS AuditLogsResourceId; +DROP TABLE IF EXISTS AuditLogs; + + ---------- -- set the global properties that actually documents the DB version, revision and some of the capabilities
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql Mon Jun 30 09:49:36 2025 +0200 +++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql Tue Jul 15 10:19:23 2025 +0200 @@ -782,20 +782,20 @@ -- new in 1.12.8 (rev 5) -CREATE TABLE KeyValueStores( +CREATE TABLE IF NOT EXISTS KeyValueStores( storeId TEXT NOT NULL, key TEXT NOT NULL, value BYTEA NOT NULL, PRIMARY KEY(storeId, key) -- Prevents duplicates ); -CREATE TABLE Queues ( +CREATE TABLE IF NOT EXISTS Queues ( id BIGSERIAL NOT NULL PRIMARY KEY, queueId TEXT NOT NULL, value BYTEA NOT NULL ); -CREATE INDEX QueuesIndex ON Queues (queueId, id); +CREATE INDEX IF NOT EXISTS QueuesIndex ON Queues (queueId, id); -- new in rev 599 @@ -824,6 +824,19 @@ END; $$ LANGUAGE plpgsql; +CREATE TABLE IF NOT EXISTS AuditLogs ( + ts TIMESTAMP DEFAULT NOW(), + userId TEXT NOT NULL, + resourceType INTEGER NOT NULL, + resourceId VARCHAR(64) NOT NULL, + action TEXT NOT NULL, + logData BYTEA +); + +CREATE INDEX IF NOT EXISTS AuditLogsUserId ON AuditLogs (userId); +CREATE INDEX IF NOT EXISTS AuditLogsResourceId ON AuditLogs (resourceId); + + -- set the global properties that actually documents the DB version, revision and some of the capabilities DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13, 14);
--- a/SQLite/Plugins/SQLiteIndex.h Mon Jun 30 09:49:36 2025 +0200 +++ b/SQLite/Plugins/SQLiteIndex.h Tue Jul 15 10:19:23 2025 +0200 @@ -70,6 +70,11 @@ return true; } + virtual bool HasAuditLogs() const ORTHANC_OVERRIDE + { + return false; + } + virtual int64_t CreateResource(DatabaseManager& manager, const char* publicId, OrthancPluginResourceType type) ORTHANC_OVERRIDE;