Mercurial > hg > orthanc-databases

changeset 655:bfa993553ed9 sql-opti

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Optimized the CreateInstance SQL query
author Alain Mazy <am@orthanc.team>
date Tue, 22 Apr 2025 18:10:38 +0200 (3 months ago)
parents f9e43680c480
children 5459bbd74cc6
files PostgreSQL/CMakeLists.txt PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/Downgrades/Rev5ToRev4.sql PostgreSQL/Plugins/SQL/PrepareIndex.sql PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev5.sql
diffstat 6 files changed, 132 insertions(+), 33 deletions(-) [+]
line wrap: on
line diff
--- a/PostgreSQL/CMakeLists.txt	Thu Feb 27 09:14:30 2025 +0100
+++ b/PostgreSQL/CMakeLists.txt	Tue Apr 22 18:10:38 2025 +0200
@@ -94,6 +94,7 @@
   POSTGRESQL_UPGRADE_REV1_TO_REV2    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev1ToRev2.sql
   POSTGRESQL_UPGRADE_REV2_TO_REV3    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev2ToRev3.sql
   POSTGRESQL_UPGRADE_REV3_TO_REV4    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev3ToRev4.sql
+  POSTGRESQL_UPGRADE_REV4_TO_REV5    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/Rev4ToRev5.sql
   )
 
 
--- a/PostgreSQL/NEWS	Thu Feb 27 09:14:30 2025 +0100
+++ b/PostgreSQL/NEWS	Tue Apr 22 18:10:38 2025 +0200
@@ -1,6 +1,18 @@
 Pending changes in the mainline
 ===============================
 
+DB schema revision: 5
+Minimum plugin SDK (for build): 1.12.5
+Optimal plugin SDK (for build): 1.12.5
+Minimum Orthanc runtime: 1.12.5
+Optimal Orthanc runtime: 1.12.6
+
+Minimal Postgresql Server version: 9
+Optimal Postgresql Server version: 11+
+
+
+Maintenance:
+* Optimized the CreateInstance SQL query.
 
 
 Release 7.2 (2025-02-27)
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Thu Feb 27 09:14:30 2025 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Tue Apr 22 18:10:38 2025 +0200
@@ -49,7 +49,7 @@
   static const GlobalProperty GlobalProperty_HasComputeStatisticsReadOnly = GlobalProperty_DatabaseInternal4;
 }
 
-#define CURRENT_DB_REVISION 4
+#define CURRENT_DB_REVISION 5
 
 namespace OrthancDatabases
 {
@@ -235,6 +235,19 @@
             currentRevision = 4;
           }
 
+          if (currentRevision == 4)
+          {
+            LOG(WARNING) << "Upgrading DB schema from revision 4 to revision 5";
+
+            std::string query;
+
+            Orthanc::EmbeddedResources::GetFileResource
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV4_TO_REV5);
+            t.GetDatabaseTransaction().ExecuteMultiLines(query);
+            hasAppliedAnUpgrade = true;
+            currentRevision = 5;
+          }
+
           if (hasAppliedAnUpgrade)
           {
             LOG(WARNING) << "Upgrading DB schema by applying PrepareIndex.sql";
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev5ToRev4.sql	Tue Apr 22 18:10:38 2025 +0200
@@ -0,0 +1,71 @@
+-- This file contains an SQL procedure to downgrade from schema Rev5 to Rev4 (version = 6).
+  -- It re-installs the old CreateInstance method
+
+
+-----------
+
+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, 0) 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, 0) 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, 0) 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, 0) 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, 4); -- GlobalProperty_DatabasePatchLevel
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Thu Feb 27 09:14:30 2025 +0100
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Tue Apr 22 18:10:38 2025 +0200
@@ -539,42 +539,42 @@
 	is_new_series := 1;
 	is_new_instance := 1;
 
-	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL, 0) 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;
+	-- Check if patient exists
+	SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id;
+	IF NOT FOUND THEN
+		INSERT INTO "resources" VALUES (DEFAULT, 0, patient_public_id, NULL, 0) RETURNING internalid INTO patient_internal_id;
+	ELSE
+		is_new_patient := 0;
+	END IF;
 
-	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id, 0) 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;
+	-- Check if study exists
+	SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id;
+	IF NOT FOUND THEN
+		INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id, 0) RETURNING internalid INTO study_internal_id;
+	ELSE
+		is_new_study := 0;
+	END IF;
 
-	BEGIN
-	    INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id, 0) 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;
+	-- Check if series exists
+	SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id;
+	IF NOT FOUND THEN
+		INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id, 0) RETURNING internalid INTO series_internal_id;
+	ELSE
+		is_new_series := 0;
+	END IF;
 
-  	BEGIN
+	-- Check if instance exists
+	SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id;
+	IF NOT FOUND THEN
 		INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id, 0) 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;
+	ELSE
+		is_new_instance := 0;
+	END IF;
 
-    IF is_new_instance > 0 THEN
-        -- Move the patient to the end of the recycling order.
-        PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
-    END IF;  
+	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;
 
@@ -721,7 +721,7 @@
 -- set the global properties that actually documents the DB version, revision and some of the capabilities
 DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13, 14);
 INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion
-INSERT INTO GlobalProperties VALUES (4, 4); -- GlobalProperty_DatabasePatchLevel
+INSERT INTO GlobalProperties VALUES (4, 5); -- GlobalProperty_DatabasePatchLevel
 INSERT INTO GlobalProperties VALUES (6, 1); -- GlobalProperty_GetTotalSizeIsFast
 INSERT INTO GlobalProperties VALUES (10, 1); -- GlobalProperty_HasTrigramIndex
 INSERT INTO GlobalProperties VALUES (11, 3); -- GlobalProperty_HasCreateInstance  -- this is actually the 3rd version of HasCreateInstance
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev4ToRev5.sql	Tue Apr 22 18:10:38 2025 +0200
@@ -0,0 +1,2 @@
+-- everything is performed in PrepareIndex.sql
+SELECT 1;
\ No newline at end of file