Mercurial > hg > orthanc-databases

changeset 681:45393e54b142 sql-opti

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
refactored DeleteAttachment
author Alain Mazy <am@orthanc.team>
date Wed, 04 Jun 2025 15:28:35 +0200
parents 0ce2725836c6
children bcea50e40d6e
files PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/PostgreSQLIndex.h PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql
diffstat 4 files changed, 43 insertions(+), 4 deletions(-) [+]
line wrap: on
line diff
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Jun 04 12:03:56 2025 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Jun 04 15:28:35 2025 +0200
@@ -457,16 +457,33 @@
     uncompressedSize = statement.ReadInteger64(5);
   }
 
-  void PostgreSQLIndex::ClearDeletedFiles(DatabaseManager& manager)
+  void PostgreSQLIndex::DeleteAttachment(IDatabaseBackendOutput& output,
+                                         DatabaseManager& manager,
+                                         int64_t id,
+                                         int32_t attachment)
   {
     {
       DatabaseManager::CachedStatement statement(
         STATEMENT_FROM_HERE, manager,
-        "SELECT CreateDeletedFilesTemporaryTable()"
-        );
-      statement.ExecuteWithoutResult();
+        "SELECT DeleteAttachment(${id}, ${type})");
+
+      statement.SetParameterType("id", ValueType_Integer64);
+      statement.SetParameterType("type", ValueType_Integer32);
+
+      Dictionary args;
+      args.SetIntegerValue("id", id);
+      args.SetInteger32Value("type", attachment);
+    
+      statement.ExecuteWithoutResult(args);
     }
 
+    SignalDeletedFiles(output, manager);
+  }
+
+
+  void PostgreSQLIndex::ClearDeletedFiles(DatabaseManager& manager)
+  {
+    // not used anymore in PostgreSQL
   }
 
   void PostgreSQLIndex::ClearDeletedResources(DatabaseManager& manager)
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h	Wed Jun 04 12:03:56 2025 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.h	Wed Jun 04 15:28:35 2025 +0200
@@ -80,6 +80,11 @@
                                 DatabaseManager& manager,
                                 int64_t id) ORTHANC_OVERRIDE;
 
+    virtual void DeleteAttachment(IDatabaseBackendOutput& output,
+                                  DatabaseManager& manager,
+                                  int64_t id,
+                                  int32_t attachment) ORTHANC_OVERRIDE;
+
     virtual void SetResourcesContent(DatabaseManager& manager,
                                      uint32_t countIdentifierTags,
                                      const OrthancPluginResourcesContentTags* identifierTags,
--- a/PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql	Wed Jun 04 12:03:56 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql	Wed Jun 04 15:28:35 2025 +0200
@@ -247,6 +247,10 @@
 EXECUTE PROCEDURE ResourceDeletedFunc();
 
 
+-- remove the new DeleteAttachment function
+
+DROP FUNCTION IF EXISTS DeleteAttachment;
+
 ----------
 
 -- set the global properties that actually documents the DB version, revision and some of the capabilities
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Wed Jun 04 12:03:56 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Wed Jun 04 15:28:35 2025 +0200
@@ -288,6 +288,19 @@
 EXECUTE PROCEDURE AttachedFileDeletedFunc();
 
 
+CREATE OR REPLACE FUNCTION DeleteAttachment(
+    IN resource_id BIGINT,
+    IN file_type INTEGER) 
+RETURNS VOID AS $body$
+BEGIN
+    -- create/clear the DeletedFiles temporary table
+    PERFORM CreateDeletedFilesTemporaryTable();
+
+    DELETE FROM AttachedFiles WHERE id = resource_id AND fileType = file_type;
+END;
+$body$ LANGUAGE plpgsql;
+
+
 ------------------- Fast Statistics -------------------
 
 -- initial population of GlobalIntegers if not already there