Mercurial > hg > orthanc-databases

changeset 610:c2497f8bf5b6

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
cleanup upgrade/downgrade procedures
author Alain Mazy <am@orthanc.team>
date Mon, 16 Dec 2024 19:06:43 +0100
parents 591ce1e2d367
children d77323cdc55d
files PostgreSQL/CMakeLists.txt PostgreSQL/NEWS PostgreSQL/Plugins/SQL/Downgrades/Rev3ToRev2.sql PostgreSQL/Plugins/SQL/Downgrades/Rev3bToRev2.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3b.sql
diffstat 7 files changed, 94 insertions(+), 132 deletions(-) [+]
line wrap: on
line diff
--- a/PostgreSQL/CMakeLists.txt	Mon Dec 16 18:09:40 2024 +0100
+++ b/PostgreSQL/CMakeLists.txt	Mon Dec 16 19:06:43 2024 +0100
@@ -92,7 +92,7 @@
   POSTGRESQL_PREPARE_INDEX           ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndex.sql
   POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToRev1.sql
   POSTGRESQL_UPGRADE_REV1_TO_REV2    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev1ToRev2.sql
-  POSTGRESQL_UPGRADE_REV2_TO_REV3    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3b.sql
+  POSTGRESQL_UPGRADE_REV2_TO_REV3    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3.sql
   )
 
 
--- a/PostgreSQL/NEWS	Mon Dec 16 18:09:40 2024 +0100
+++ b/PostgreSQL/NEWS	Mon Dec 16 19:06:43 2024 +0100
@@ -16,7 +16,7 @@
   creation of this index.  If needed, you can create it manually before installing
   the new plugin by executing these SQL commands:
 
-    https://orthanc.uclouvain.be/hg/orthanc-databases/file/tip/PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_To_Rev3b.sql
+    https://orthanc.uclouvain.be/hg/orthanc-databases/file/tip/PostgreSQL/Plugins/SQL/Upgrades/Partial/Rev2_To_Rev3.sql
 
 * WARNING: A new column has been added in the resources table when upgrading to this version.  A new thread
   is populating this new table and might consume DB bandwitdh and CPU.
--- a/PostgreSQL/Plugins/SQL/Downgrades/Rev3ToRev2.sql	Mon Dec 16 18:09:40 2024 +0100
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev3ToRev2.sql	Mon Dec 16 19:06:43 2024 +0100
@@ -3,12 +3,89 @@
   -- It actually does not uninstall ChildrenIndex2 because it is anyway more efficient than 
      -- ChildrenIndex and is not incompatible with previous revisions.
 
+-- remove the childCount column in resources
+DO $body$
+BEGIN
+	IF EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN
+		ALTER TABLE Resources DROP COLUMN childcount;
+	ELSE
+		raise notice 'the resources.childcount column does not exists';
+	END IF;
+
+END $body$;
+
+
+------------------- re-install old CreateInstance function -------------------
+CREATE OR REPLACE FUNCTION CreateInstance(
+  IN patient_public_id TEXT,
+  IN study_public_id TEXT,
+  IN series_public_id TEXT,
+  IN instance_public_id TEXT,
+  OUT is_new_patient BIGINT,
+  OUT is_new_study BIGINT,
+  OUT is_new_series BIGINT,
+  OUT is_new_instance BIGINT,
+  OUT patient_internal_id BIGINT,
+  OUT study_internal_id BIGINT,
+  OUT series_internal_id BIGINT,
+  OUT instance_internal_id BIGINT) AS $body$
+
+BEGIN
+	is_new_patient := 1;
+	is_new_study := 1;
+	is_new_series := 1;
+	is_new_instance := 1;
+
+	BEGIN
+        INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL) RETURNING internalid INTO patient_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_patient := 0;
+            SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
+    END;
+
+	BEGIN
+        INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id) RETURNING internalid INTO study_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_study := 0;
+            SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
+    END;
+
+	BEGIN
+	    INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id) RETURNING internalid INTO series_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_series := 0;
+            SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
+    END;
+
+  	BEGIN
+		INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id) RETURNING internalid INTO instance_internal_id;
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_instance := 0;
+            SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
+    END;
+
+    IF is_new_instance > 0 THEN
+        -- Move the patient to the end of the recycling order.
+        PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
+    END IF;  
+END;
+$body$ LANGUAGE plpgsql;
+
+
+
+
+
 DROP TRIGGER IF EXISTS DecrementChildCount ON Resources;
 DROP TRIGGER IF EXISTS IncrementChildCount ON Resources;
-DROP TABLE ChildCount;
+DROP FUNCTION ComputeMissingChildCount;
 DROP FUNCTION UpdateChildCount;
 
 
+
 -- 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, 11);
--- a/PostgreSQL/Plugins/SQL/Downgrades/Rev3bToRev2.sql	Mon Dec 16 18:09:40 2024 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,84 +0,0 @@
--- This file contains an SQL procedure to downgrade from schema Rev3 to Rev2 (version = 6, revision = 1).
-  -- It actually deletes the ChildCount table and triggers
-  -- It actually does not uninstall ChildrenIndex2 because it is anyway more efficient than 
-     -- ChildrenIndex and is not incompatible with previous revisions.
-
--- remove the childCount column in resources
-DO $body$
-BEGIN
-	IF EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN
-		ALTER TABLE Resources DROP COLUMN childcount;
-	ELSE
-		raise notice 'the resources.childcount column does not exists';
-	END IF;
-
-END $body$;
-
-
-------------------- re-install old CreateInstance function -------------------
-CREATE OR REPLACE FUNCTION CreateInstance(
-  IN patient_public_id TEXT,
-  IN study_public_id TEXT,
-  IN series_public_id TEXT,
-  IN instance_public_id TEXT,
-  OUT is_new_patient BIGINT,
-  OUT is_new_study BIGINT,
-  OUT is_new_series BIGINT,
-  OUT is_new_instance BIGINT,
-  OUT patient_internal_id BIGINT,
-  OUT study_internal_id BIGINT,
-  OUT series_internal_id BIGINT,
-  OUT instance_internal_id BIGINT) AS $body$
-
-BEGIN
-	is_new_patient := 1;
-	is_new_study := 1;
-	is_new_series := 1;
-	is_new_instance := 1;
-
-	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL) RETURNING internalid INTO patient_internal_id;
-    EXCEPTION
-        WHEN unique_violation THEN
-            is_new_patient := 0;
-            SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
-    END;
-
-	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id) RETURNING internalid INTO study_internal_id;
-    EXCEPTION
-        WHEN unique_violation THEN
-            is_new_study := 0;
-            SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
-    END;
-
-	BEGIN
-	    INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id) RETURNING internalid INTO series_internal_id;
-    EXCEPTION
-        WHEN unique_violation THEN
-            is_new_series := 0;
-            SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction    END;
-    END;
-
-  	BEGIN
-		INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id) RETURNING internalid INTO instance_internal_id;
-    EXCEPTION
-        WHEN unique_violation THEN
-            is_new_instance := 0;
-            SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id FOR UPDATE;  -- also locks the resource and its parent to prevent from deletion while we complete this transaction
-    END;
-
-    IF is_new_instance > 0 THEN
-        -- Move the patient to the end of the recycling order.
-        PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
-    END IF;  
-END;
-$body$ LANGUAGE plpgsql;
-
-
-
-
--- 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, 11);
-INSERT INTO GlobalProperties VALUES (4, 2); -- GlobalProperty_DatabasePatchLevel
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Mon Dec 16 18:09:40 2024 +0100
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Mon Dec 16 19:06:43 2024 +0100
@@ -591,7 +591,7 @@
 $body$ LANGUAGE plpgsql;
 
 
--- -- new in Rev3b
+-- -- new in Rev3
 
 -- Computes the childCount for a number of resources for which it has not been computed yet.
 -- This is actually used only after an update to Rev3.  A thread will call this function
--- a/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql	Mon Dec 16 18:09:40 2024 +0100
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql	Mon Dec 16 19:06:43 2024 +0100
@@ -25,6 +25,19 @@
 
 DROP INDEX IF EXISTS ChildrenIndex;  -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading
 
+-- add the childCount columns in Resources if not yet done
+
+DO $body$
+BEGIN
+	IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN
+		ALTER TABLE Resources ADD COLUMN childcount INTEGER;
+	ELSE
+		raise notice 'the resources.childcount column already exists';
+	END IF;
+
+END $body$;
+
+
 
 -- other changes performed in PrepareIndex.sql:
   -- add ChildCount tables and triggers
--- a/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3b.sql	Mon Dec 16 18:09:40 2024 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,44 +0,0 @@
--- This file contains part of the changes required to upgrade from Revision 2 to Revision 3 (DB version 6 and revision 2)
--- It actually contains only the changes that:
-   -- can not be executed with an idempotent statement in SQL
-   -- or would polute the PrepareIndex.sql
-   -- do facilite an up-time upgrade
--- This file is executed only if the current schema is in revision 2 and it is executed 
--- before PrepareIndex.sql that is idempotent.
-
-
--- create a new ChildrenIndex2 that is replacing ChildrenIndex.
--- We create it in this partial update so it can be created while the system is up !
-DO $$
-DECLARE
-    pg_version text;
-BEGIN
-    SELECT version() INTO pg_version;
-
-    IF substring(pg_version from 'PostgreSQL (\d+)\.')::int >= 11 THEN
-        -- PostgreSQL 11 or later
-        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST) INCLUDE (publicId, internalId)';
-    ELSE
-        EXECUTE 'CREATE INDEX IF NOT EXISTS ChildrenIndex2 ON Resources USING btree (parentId ASC NULLS LAST, publicId, internalId)';
-    END IF;
-END $$;
-
-DROP INDEX IF EXISTS ChildrenIndex;  -- replaced by ChildrenIndex2 but no need to uninstall ChildrenIndex2 when downgrading
-
--- add the childCount columns in Resources if not yet done
-
-DO $body$
-BEGIN
-	IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='resources' AND column_name='childcount') THEN
-		ALTER TABLE Resources ADD COLUMN childcount INTEGER;
-	ELSE
-		raise notice 'the resources.childcount column already exists';
-	END IF;
-
-END $body$;
-
-
-
--- other changes performed in PrepareIndex.sql:
-  -- add ChildCount tables and triggers
-