changeset 989:599b328e5a75

avoid downloading downgrade file from uclouvain
author Alain Mazy <am@orthanc.team>
date Mon, 28 Sep 2026 11:05:22 +0200
parents 99e19529a7e0
children bf0201d555a1
files NewTests/PostgresUpgrades/downgrade.sh NewTests/PostgresUpgrades/downgrade.sql
diffstat 2 files changed, 119 insertions(+), 20 deletions(-) [+]
line wrap: on
line diff
--- a/NewTests/PostgresUpgrades/downgrade.sh	Mon Sep 28 10:01:08 2026 +0200
+++ b/NewTests/PostgresUpgrades/downgrade.sh	Mon Sep 28 11:05:22 2026 +0200
@@ -4,23 +4,5 @@
 
 pushd /scripts
 
-apt-get update && apt-get install -y wget
-
-    # # hg clone is often rejected from Azure runners, let's try a download from http
-    # wget https://orthanc.uclouvain.be/downloads/sources/orthanc-postgresql/OrthancPostgreSQL-10.0.tar.gz --output-document /tmp/pg.tar.gz
-    # # ex for an intermediate release (or if download from uclouvain is rejected)
-    # # wget https://public-files.orthanc.team/tmp-builds/hg-repos/orthanc-databases-81d837d7d20d.tar.gz --output-document /tmp/pg.tar.gz
-
-    # mkdir -p /scripts/orthanc-databases/
-    # pushd /scripts/orthanc-databases/
-    # tar xvf /tmp/pg.tar.gz --strip-components=1
-
-    # psql -U postgres -f /scripts/orthanc-databases/PostgreSQL/Plugins/SQL/Downgrades/Rev10ToRev6.sql
-
-wget https://orthanc.uclouvain.be/hg/orthanc-databases/raw-file/default/PostgreSQL/Plugins/SQL/Downgrades/Rev11ToRev10.sql --output-document /tmp/downgrade.sql
-
-psql -v ON_ERROR_STOP=1 -U postgres -f /tmp/downgrade.sql
-
-# if you want to test a downgrade procedure, you may use this code ...
-# psql -v ON_ERROR_STOP=1  -U postgres -f downgrade.sql
+psql -v ON_ERROR_STOP=1  -U postgres -f downgrade.sql
 popd
--- a/NewTests/PostgresUpgrades/downgrade.sql	Mon Sep 28 10:01:08 2026 +0200
+++ b/NewTests/PostgresUpgrades/downgrade.sql	Mon Sep 28 11:05:22 2026 +0200
@@ -1,1 +1,118 @@
--- you may paste here a test downgrade procedure to avoid rebuilding the docker image ...  check downgrade.sh
\ No newline at end of file
+-- this file is identical to https://orthanc.uclouvain.be/hg/orthanc-databases/raw-file/default/PostgreSQL/Plugins/SQL/Downgrades/Rev11ToRev10.sql
+
+-- restore the old DeleteResource function
+
+CREATE OR REPLACE FUNCTION DeleteResource(
+    IN id BIGINT,
+    OUT remaining_ancestor_resource_type INTEGER,
+    OUT remaining_anncestor_public_id TEXT) AS $body$
+DECLARE
+    deleted_resource_row RECORD;
+    deleted_parent_row RECORD;
+    deleted_grand_parent_row RECORD;
+    deleted_grand_grand_parent_row RECORD;
+    locked_parent_row RECORD;
+    locked_resource_row RECORD;
+BEGIN
+    SET client_min_messages = warning;   -- suppress NOTICE:  relation "deletedresources" already exists, skipping
+    -- note: temporary tables are created at connection level -> they are likely to exist.
+    -- These tables are used by the triggers
+    CREATE TEMPORARY TABLE IF NOT EXISTS DeletedResources(
+        resourceType INTEGER NOT NULL,
+        publicId VARCHAR(64) NOT NULL
+        );
+    RESET client_min_messages;
+    -- clear the temporary table in case it has been created earlier in the connection
+    DELETE FROM DeletedResources;
+    -- create/clear the DeletedFiles temporary table
+    PERFORM CreateDeletedFilesTemporaryTable();
+    -- Before deleting an object, we need to lock its parent until the end of the transaction to avoid that
+    -- 2 threads deletes the last 2 instances of a series at the same time -> none of them would realize
+    -- that they are deleting the last instance and the parent resources would not be deleted.
+    -- Locking only the immediate parent is sufficient to prevent from this.
+    SELECT * INTO locked_parent_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE;
+    -- Before deleting the resource itself, we lock it to retrieve the resourceType and to make sure not 2 connections try to
+    -- delete it at the same time
+    SELECT * INTO locked_resource_row FROM resources WHERE internalid = id FOR UPDATE;
+    -- before delete the resource itself, we must delete its grand-grand-children, the grand-children and its children no to violate 
+    -- the parentId referencing an existing primary key constrain.  This is actually implementing the ON DELETE CASCADE that was on the parentId in previous revisions.
+    
+    -- If this resource has grand-grand-children, delete them
+    if locked_resource_row.resourceType < 1 THEN
+        WITH grand_grand_children_to_delete AS (SELECT grandGrandChildLevel.internalId, grandGrandChildLevel.resourceType, grandGrandChildLevel.publicId
+                                                FROM Resources childLevel
+                                                INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId
+                                                INNER JOIN Resources grandGrandChildLevel ON grandChildLevel.internalId = grandGrandChildLevel.parentId
+                                                WHERE childLevel.parentId = id),
+        
+        deleted_grand_grand_children_rows AS (DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM grand_grand_children_to_delete)
+                                              RETURNING resourceType, publicId)
+        INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_grand_grand_children_rows; 
+    END IF;
+    -- If this resource has grand-children, delete them
+    if locked_resource_row.resourceType < 2 THEN
+        WITH grand_children_to_delete AS (SELECT grandChildLevel.internalId, grandChildLevel.resourceType, grandChildLevel.publicId
+                                          FROM Resources childLevel
+                                          INNER JOIN Resources grandChildLevel ON childLevel.internalId = grandChildLevel.parentId
+                                          WHERE childLevel.parentId = id),
+        
+        deleted_grand_children_rows AS (DELETE FROM Resources WHERE internalId IN (SELECT internalId FROM grand_children_to_delete)
+                                        RETURNING resourceType, publicId)
+        INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_grand_children_rows; 
+    END IF;
+    -- If this resource has children, delete them
+    if locked_resource_row.resourceType < 3 THEN
+        WITH deleted_children AS (DELETE FROM Resources 
+                                  WHERE parentId = id
+                                  RETURNING resourceType, publicId)
+        INSERT INTO DeletedResources SELECT resourceType, publicId FROM deleted_children; 
+    END IF;
+    -- delete the resource itself
+    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_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;
+
+DROP INDEX IF EXISTS AttachedFilesUuid;
+
+-- set the global properties that actually documents the DB version, revision and some of the capabilities
+-- modify only the ones that have changed
+DELETE FROM GlobalProperties WHERE property IN (4);
+INSERT INTO GlobalProperties VALUES (4, 10); -- GlobalProperty_DatabasePatchLevel