# HG changeset patch
# User Alain Mazy <am@osimis.io>
# Date 1702482726 -3600
# Node ID 23c7af6f671a0cbb8b8b54c6ff4bc3e0bfccfdaf
# Parent  5964ce6385a5e5173efe3da2aca1f14692710b35
DeleteResource does not need the RemainingAncestor table anymore

diff -r 5964ce6385a5 -r 23c7af6f671a Framework/Common/DatabaseManager.cpp
--- a/Framework/Common/DatabaseManager.cpp	Wed Dec 13 15:48:56 2023 +0100
+++ b/Framework/Common/DatabaseManager.cpp	Wed Dec 13 16:52:06 2023 +0100
@@ -492,7 +492,7 @@
           return dynamic_cast<const Integer64Value&>(value).GetValue();
 
         default:
-          //LOG(ERROR) << value.Format();
+          // LOG(ERROR) << value.GetType();
           throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);
       }
     }
@@ -521,7 +521,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);
diff -r 5964ce6385a5 -r 23c7af6f671a Framework/Common/DatabaseManager.h
--- a/Framework/Common/DatabaseManager.h	Wed Dec 13 15:48:56 2023 +0100
+++ b/Framework/Common/DatabaseManager.h	Wed Dec 13 16:52:06 2023 +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());
diff -r 5964ce6385a5 -r 23c7af6f671a NOTES
--- a/NOTES	Wed Dec 13 15:48:56 2023 +0100
+++ b/NOTES	Wed Dec 13 16:52:06 2023 +0100
@@ -172,7 +172,8 @@
 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: 22.500 s (with temporary tables)
-
+                                                       test_concurrent_anonymize_same_study deletion took: 32.265 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)
diff -r 5964ce6385a5 -r 23c7af6f671a PostgreSQL/Plugins/CreateInstance.sql
--- a/PostgreSQL/Plugins/CreateInstance.sql	Wed Dec 13 15:48:56 2023 +0100
+++ b/PostgreSQL/Plugins/CreateInstance.sql	Wed Dec 13 16:52:06 2023 +0100
@@ -24,35 +24,35 @@
 
 	BEGIN
         INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL);
-    exception
-        when unique_violation then
+    EXCEPTION
+        WHEN unique_violation THEN
             isNewPatient := 0;
-    end;
-    select internalid into patientKey from "resources" where publicId=patient and resourcetype = 0;
+    END;
+    SELECT internalid INTO patientKey FROM "resources" WHERE publicId=patient AND resourcetype = 0;
 
 	BEGIN
         INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey);
-    exception
-        when unique_violation then
+    EXCEPTION
+        WHEN unique_violation THEN
             isNewStudy := 0;
-    end;
-    select internalid into studyKey from "resources" where publicId=study and resourcetype = 1;
+    END;
+    SELECT internalid INTO studyKey FROM "resources" WHERE publicId=study AND resourcetype = 1;
 
 	BEGIN
 	    INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey);
-    exception
-        when unique_violation then
+    EXCEPTION
+        WHEN unique_violation THEN
             isNewSeries := 0;
-    end;
-	select internalid into seriesKey from "resources" where publicId=series and resourcetype = 2;
+    END;
+	SELECT internalid INTO seriesKey FROM "resources" WHERE publicId=series AND resourcetype = 2;
 
   	BEGIN
 		INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey);
-    exception
-        when unique_violation then
+    EXCEPTION
+        WHEN unique_violation THEN
             isNewInstance := 0;
-    end;
-    select internalid into instanceKey from "resources" where publicId=instance and resourcetype = 3;   
+    END;
+    SELECT internalid INTO instanceKey FROM "resources" WHERE publicId=instance AND resourcetype = 3;   
 
   IF isNewInstance > 0 THEN
     -- Move the patient to the end of the recycling order
diff -r 5964ce6385a5 -r 23c7af6f671a PostgreSQL/Plugins/PostgreSQLIndex.cpp
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Dec 13 15:48:56 2023 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Dec 13 16:52:06 2023 +0100
@@ -539,6 +539,45 @@
 
   }
 
+  void PostgreSQLIndex::DeleteResource(IDatabaseBackendOutput& output,
+                                       DatabaseManager& manager,
+                                       int64_t id)
+  {
+    ClearDeletedFiles(manager);
+    ClearDeletedResources(manager);
+
+    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
diff -r 5964ce6385a5 -r 23c7af6f671a PostgreSQL/Plugins/PostgreSQLIndex.h
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h	Wed Dec 13 15:48:56 2023 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.h	Wed Dec 13 16:52:06 2023 +0100
@@ -63,8 +63,11 @@
     
     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 uint64_t GetTotalCompressedSize(DatabaseManager& manager) ORTHANC_OVERRIDE;
 
diff -r 5964ce6385a5 -r 23c7af6f671a PostgreSQL/Plugins/ResourceDeletedFunc.sql
--- a/PostgreSQL/Plugins/ResourceDeletedFunc.sql	Wed Dec 13 15:48:56 2023 +0100
+++ b/PostgreSQL/Plugins/ResourceDeletedFunc.sql	Wed Dec 13 16:52:06 2023 +0100
@@ -9,12 +9,6 @@
   --RAISE NOTICE 'Delete resource %', old.parentId;
   INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
   
-  -- 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)
-  INSERT INTO RemainingAncestor SELECT resourceType, publicId
-                                FROM Resources 
-                                WHERE internalId = old.parentId
-                                      AND EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
   -- 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);
@@ -31,3 +25,28 @@
 DROP TABLE IF EXISTS DeletedFiles;
 DROP TABLE IF EXISTS RemainingAncestor;
 DROP TABLE IF EXISTS DeletedResources;
+
+
+CREATE OR REPLACE FUNCTION DeleteResource(
+    IN id BIGINT,
+    OUT remainingAncestorResourceType INTEGER,
+    OUT remainingAncestorPublicId TEXT) AS $body$
+
+DECLARE
+    deleted_row RECORD;
+
+BEGIN
+
+    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 remainingAncestorResourceType, remainingAncestorPublicId
+        FROM Resources 
+        WHERE internalId = deleted_row.parentId
+            AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId);
+
+END;
+
+$body$ LANGUAGE plpgsql;