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;