Mercurial > hg > orthanc-databases

changeset 680:0ce2725836c6 sql-opti

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
removed DeletedResource trigger that is now inlined in the DeleteResource function to improve observability and ease comprehension
author Alain Mazy <am@orthanc.team>
date Wed, 04 Jun 2025 12:03:56 +0200
parents 96a6640b8498
children 45393e54b142
files PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev99.sql
diffstat 4 files changed, 75 insertions(+), 51 deletions(-) [+]
line wrap: on
line diff
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Jun 04 11:01:07 2025 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Jun 04 12:03:56 2025 +0200
@@ -459,7 +459,7 @@
 
   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
+    {
       DatabaseManager::CachedStatement statement(
         STATEMENT_FROM_HERE, manager,
         "SELECT CreateDeletedFilesTemporaryTable()"
@@ -471,29 +471,12 @@
 
   void PostgreSQLIndex::ClearDeletedResources(DatabaseManager& manager)
   {
-    { // note: the temporary table lifespan is the session, not the transaction -> that's why we need the IF NOT EXISTS
-      DatabaseManager::CachedStatement statement(
-        STATEMENT_FROM_HERE, manager,
-        "CREATE TEMPORARY TABLE IF NOT EXISTS  DeletedResources("
-        "resourceType INTEGER NOT NULL,"
-        "publicId VARCHAR(64) NOT NULL"
-        ");"
-        );
-      statement.Execute();
-    }
-    {
-      DatabaseManager::CachedStatement statement(
-        STATEMENT_FROM_HERE, manager,
-        "DELETE FROM DeletedResources;"
-        );
-
-      statement.Execute();
-    }
-
+    // not used anymore in PostgreSQL
   }
 
   void PostgreSQLIndex::ClearRemainingAncestor(DatabaseManager& manager)
   {
+    // not used anymore in PostgreSQL
   }
 
   void PostgreSQLIndex::DeleteResource(IDatabaseBackendOutput& output,
--- a/PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql	Wed Jun 04 11:01:07 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev99ToRev4.sql	Wed Jun 04 12:03:56 2025 +0200
@@ -220,6 +220,31 @@
 
 $body$ LANGUAGE plpgsql;
 
+-- restore the DeletedResource trigger
+
+------------------- ResourceDeleted trigger -------------------
+DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
+
+-- The following trigger combines 2 triggers from SQLite:
+-- ResourceDeleted + ResourceDeletedParentCleaning
+CREATE OR REPLACE FUNCTION ResourceDeletedFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId;
+  INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
+  
+  -- 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);
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS ResourceDeleted on Resources;
+CREATE TRIGGER ResourceDeleted
+AFTER DELETE ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE ResourceDeletedFunc();
 
 
 ----------
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Wed Jun 04 11:01:07 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Wed Jun 04 12:03:56 2025 +0200
@@ -169,30 +169,6 @@
 END;
 $body$ LANGUAGE plpgsql;
 
-------------------- ResourceDeleted trigger -------------------
-DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
-
--- The following trigger combines 2 triggers from SQLite:
--- ResourceDeleted + ResourceDeletedParentCleaning
-CREATE OR REPLACE FUNCTION ResourceDeletedFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId;
-  INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
-  
-  -- 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);
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-DROP TRIGGER IF EXISTS ResourceDeleted on Resources;
-CREATE TRIGGER ResourceDeleted
-AFTER DELETE ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE ResourceDeletedFunc();
-
 
 ------------------- DeleteResource function -------------------
 
@@ -202,7 +178,10 @@
     OUT remaining_anncestor_public_id TEXT) AS $body$
 
 DECLARE
-    deleted_row RECORD;
+    deleted_resource_row RECORD;
+    deleted_parent_row RECORD;
+    deleted_grand_parent_row RECORD;
+    deleted_grand_grand_parent_row RECORD;
     locked_row RECORD;
 
 BEGIN
@@ -224,16 +203,47 @@
     SELECT * INTO locked_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE;
 
     -- delete the resource itself
-    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 + 
+    DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_resource_row;
 
+    -- keep track of the deleted resources for C++ code
+    INSERT INTO DeletedResources VALUES (deleted_resource_row.resourceType, deleted_resource_row.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)
     SELECT resourceType, publicId INTO remaining_ancestor_resource_type, remaining_anncestor_public_id
         FROM Resources 
-        WHERE internalId = deleted_row.parentId
-            AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId);
+        WHERE internalId = deleted_resource_row.parentId
+            AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId);
+
+	IF deleted_resource_row.resourceType > 0 THEN
+        -- If this resource is the latest child, delete the parent
+        DELETE FROM Resources WHERE internalId = deleted_resource_row.parentId
+                                    AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_resource_row.parentId)
+                                    RETURNING * INTO deleted_parent_row;
+        IF FOUND THEN
+            INSERT INTO DeletedResources VALUES (deleted_parent_row.resourceType, deleted_parent_row.publicId);
 
+            IF deleted_parent_row.resourceType > 0 THEN
+                -- If this resource is the latest child, delete the parent
+                DELETE FROM Resources WHERE internalId = deleted_parent_row.parentId
+                                    AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_parent_row.parentId)
+                                    RETURNING * INTO deleted_grand_parent_row;
+                IF FOUND THEN
+                    INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId);
+
+                    IF deleted_grand_parent_row.resourceType > 0 THEN
+                        -- If this resource is the latest child, delete the parent
+                        DELETE FROM Resources WHERE internalId = deleted_grand_parent_row.parentId
+                                            AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_grand_parent_row.parentId)
+                                            RETURNING * INTO deleted_grand_parent_row;
+                        IF FOUND THEN
+                            INSERT INTO DeletedResources VALUES (deleted_grand_parent_row.resourceType, deleted_grand_parent_row.publicId);
+                        END IF;
+                    END IF;
+                END IF;
+            END IF;
+        END IF;
+    END IF;
 END;
 
 $body$ LANGUAGE plpgsql;
@@ -258,7 +268,8 @@
 
 $body$ LANGUAGE plpgsql;
 
-
+-- Keep track of deleted files such that the C++ code knows which files have been deleted.
+-- Attached files are deleted by cascade when the related resource is deleted.
 CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc() 
 RETURNS TRIGGER AS $body$
 BEGIN
--- a/PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev99.sql	Wed Jun 04 11:01:07 2025 +0200
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev99.sql	Wed Jun 04 12:03:56 2025 +0200
@@ -44,4 +44,9 @@
 
 DROP TRIGGER IF EXISTS PatientAdded on Resources;
 DROP FUNCTION IF EXISTS PatientAddedFunc;
-DROP FUNCTION IF EXISTS PatientAddedOrUpdated;
\ No newline at end of file
+DROP FUNCTION IF EXISTS PatientAddedOrUpdated;
+
+-- The DeletedResources trigger is not used anymore
+
+DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
+DROP FUNCTION IF EXISTS ResourceDeletedFunc();
\ No newline at end of file