Mercurial > hg > orthanc-tests
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
