Mercurial > hg > orthanc-databases

changeset 467:ff84104f7842 pg-transactions

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
renamed v6.2 to REV2 + removed 'default' TransactionMode + renamed 'READ COMMITTED' into 'ReadCommitted'
author Alain Mazy <am@osimis.io>
date Mon, 05 Feb 2024 09:43:14 +0100
parents daaa35ddba54
children 8f1f26680527
files Framework/PostgreSQL/PostgreSQLParameters.cpp PostgreSQL/CMakeLists.txt PostgreSQL/NEWS PostgreSQL/Plugins/PostgreSQLIndex.cpp PostgreSQL/Plugins/SQL/Downgrades/Rev2ToRev1.sql PostgreSQL/Plugins/SQL/Downgrades/V6.2ToV6.1.sql PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql PostgreSQL/Plugins/SQL/Upgrades/UnknownToRev1.sql PostgreSQL/Plugins/SQL/Upgrades/UnknownToV6.1.sql PostgreSQL/Plugins/SQL/Upgrades/V6.1ToV6.2.sql
diffstat 10 files changed, 329 insertions(+), 335 deletions(-) [+]
line wrap: on
line diff
--- a/Framework/PostgreSQL/PostgreSQLParameters.cpp	Wed Jan 31 15:43:10 2024 +0100
+++ b/Framework/PostgreSQL/PostgreSQLParameters.cpp	Mon Feb 05 09:43:14 2024 +0100
@@ -100,18 +100,13 @@
     maxConnectionRetries_ = configuration.GetUnsignedIntegerValue("MaximumConnectionRetries", 10);
     connectionRetryInterval_ = configuration.GetUnsignedIntegerValue("ConnectionRetryInterval", 5);
 
-    std::string transactionMode = configuration.GetStringValue("TransactionMode", "SERIALIZABLE");
-    if (transactionMode == "DEFAULT")
-    {
-      LOG(WARNING) << "PostgreSQL: using DB default transaction mode";
-      SetIsolationMode(IsolationMode_DbDefault);
-    }
-    else if (transactionMode == "READ COMMITTED")
+    std::string transactionMode = configuration.GetStringValue("TransactionMode", "Serializable");
+    if (transactionMode == "ReadCommitted")
     {
       LOG(WARNING) << "PostgreSQL: using READ COMMITTED transaction mode";
       SetIsolationMode(IsolationMode_ReadCommited);
     }
-    else if (transactionMode == "SERIALIZABLE")
+    else if (transactionMode == "Serializable")
     {
       LOG(WARNING) << "PostgreSQL: using SERIALIZABLE transaction mode";
       SetIsolationMode(IsolationMode_Serializable);
--- a/PostgreSQL/CMakeLists.txt	Wed Jan 31 15:43:10 2024 +0100
+++ b/PostgreSQL/CMakeLists.txt	Mon Feb 05 09:43:14 2024 +0100
@@ -80,8 +80,8 @@
 
 EmbedResources(
   POSTGRESQL_PREPARE_INDEX           ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndex.sql
-  POSTGRESQL_UPGRADE_UNKNOWN_TO_V6_1 ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToV6.1.sql
-  POSTGRESQL_UPGRADE_V6_1_TO_V6_2    ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/V6.1ToV6.2.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
   )
 
 
--- a/PostgreSQL/NEWS	Wed Jan 31 15:43:10 2024 +0100
+++ b/PostgreSQL/NEWS	Mon Feb 05 09:43:14 2024 +0100
@@ -3,12 +3,11 @@
 
 * Experimental debug feature:
   Introduced a new configuration to select the transaction isolation level
-    "TransactionMode": "SERIALIZABLE"
-  Allowed values: "SERIALIZABLE", "READ COMMITTED", "DEFAULT".
-  The "SERIALIZABLE" mode was the only available value up to now.  It is still the default
+    "TransactionMode": "Serializable"
+  Allowed values: "Serializable", "ReadCommitted".
+  The "Serializable" mode was the only available value up to now.  It is still the default
   value now.
-  The "READ COMMITTED" is possible now due to rewrites of SQL queries.
-  The "DEFAULT" value uses the default transaction isolation level defined at the database level.
+  The "ReadCommitted" is possible now due to rewrites of SQL queries.
 * internals:
   - Added a UNIQUE constraint on Resources.publicId to detect DB inconsistencies
 * New "EnableVerboseLogs" configuration to show SQL statements being executed.
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Jan 31 15:43:10 2024 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Mon Feb 05 09:43:14 2024 +0100
@@ -186,7 +186,7 @@
             std::string query;
 
             Orthanc::EmbeddedResources::GetFileResource
-              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_UNKNOWN_TO_V6_1);
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_UNKNOWN_TO_REV1);
             t.GetDatabaseTransaction().ExecuteMultiLines(query);
           }
           
@@ -197,7 +197,7 @@
             std::string query;
 
             Orthanc::EmbeddedResources::GetFileResource
-              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_V6_1_TO_V6_2);
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_REV1_TO_REV2);
             t.GetDatabaseTransaction().ExecuteMultiLines(query);
 
             // apply all idempotent changes that are in the PrepareIndexV2
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev2ToRev1.sql	Mon Feb 05 09:43:14 2024 +0100
@@ -0,0 +1,252 @@
+-- This file contains an SQL procedure to downgrade from schema 6.2 to 6.1 (version = 6, revision = 1).
+-- It reinstalls all triggers and temporary tables that have been removed or replaced in 6.2
+
+-- note: we don't not remove the unique constraints that have been added - they should not 
+--       create any issues.
+
+-- these constraints were introduced in 6.2
+ALTER TABLE Resources DROP CONSTRAINT UniquePublicId;
+ALTER TABLE PatientRecyclingOrder DROP CONSTRAINT UniquePatientId;
+
+-- the CreateInstance has been replaced in 6.2, reinstall the 6.1
+DROP FUNCTION CreateInstance;
+CREATE FUNCTION CreateInstance(
+  IN patient TEXT,
+  IN study TEXT,
+  IN series TEXT,
+  IN instance TEXT,
+  OUT isNewPatient BIGINT,
+  OUT isNewStudy BIGINT,
+  OUT isNewSeries BIGINT,
+  OUT isNewInstance BIGINT,
+  OUT patientKey BIGINT,
+  OUT studyKey BIGINT,
+  OUT seriesKey BIGINT,
+  OUT instanceKey BIGINT) AS $body$
+
+DECLARE
+  patientSeq BIGINT;
+  countRecycling BIGINT;
+
+BEGIN
+  SELECT internalId FROM Resources INTO instanceKey WHERE publicId = instance AND resourceType = 3;
+
+  IF NOT (instanceKey IS NULL) THEN
+    -- This instance already exists, stop here
+    isNewInstance := 0;
+  ELSE
+    SELECT internalId FROM Resources INTO patientKey WHERE publicId = patient AND resourceType = 0;
+    SELECT internalId FROM Resources INTO studyKey WHERE publicId = study AND resourceType = 1;
+    SELECT internalId FROM Resources INTO seriesKey WHERE publicId = series AND resourceType = 2;
+
+    IF patientKey IS NULL THEN
+      -- Must create a new patient
+      IF NOT (studyKey IS NULL AND seriesKey IS NULL AND instanceKey IS NULL) THEN
+         RAISE EXCEPTION 'Broken invariant';
+      END IF;
+
+      INSERT INTO Resources VALUES (DEFAULT, 0, patient, NULL) RETURNING internalId INTO patientKey;
+      isNewPatient := 1;
+    ELSE
+      isNewPatient := 0;
+    END IF;
+  
+    IF (patientKey IS NULL) THEN
+       RAISE EXCEPTION 'Broken invariant';
+    END IF;
+
+    IF studyKey IS NULL THEN
+      -- Must create a new study
+      IF NOT (seriesKey IS NULL AND instanceKey IS NULL) THEN
+         RAISE EXCEPTION 'Broken invariant';
+      END IF;
+
+      INSERT INTO Resources VALUES (DEFAULT, 1, study, patientKey) RETURNING internalId INTO studyKey;
+      isNewStudy := 1;
+    ELSE
+      isNewStudy := 0;
+    END IF;
+
+    IF (studyKey IS NULL) THEN
+       RAISE EXCEPTION 'Broken invariant';
+    END IF;
+
+    IF seriesKey IS NULL THEN
+      -- Must create a new series
+      IF NOT (instanceKey IS NULL) THEN
+         RAISE EXCEPTION 'Broken invariant';
+      END IF;
+
+      INSERT INTO Resources VALUES (DEFAULT, 2, series, studyKey) RETURNING internalId INTO seriesKey;
+      isNewSeries := 1;
+    ELSE
+      isNewSeries := 0;
+    END IF;
+  
+    IF (seriesKey IS NULL OR NOT instanceKey IS NULL) THEN
+       RAISE EXCEPTION 'Broken invariant';
+    END IF;
+
+    INSERT INTO Resources VALUES (DEFAULT, 3, instance, seriesKey) RETURNING internalId INTO instanceKey;
+    isNewInstance := 1;
+
+    -- Move the patient to the end of the recycling order
+    SELECT seq FROM PatientRecyclingOrder WHERE patientId = patientKey INTO patientSeq;
+
+    IF NOT (patientSeq IS NULL) THEN
+       -- The patient is not protected
+       SELECT COUNT(*) FROM (SELECT * FROM PatientRecyclingOrder WHERE seq >= patientSeq LIMIT 2) AS tmp INTO countRecycling;
+       IF countRecycling = 2 THEN
+          -- The patient was not at the end of the recycling order
+          DELETE FROM PatientRecyclingOrder WHERE seq = patientSeq;
+          INSERT INTO PatientRecyclingOrder VALUES(DEFAULT, patientKey);
+       END IF;
+    END IF;
+  END IF;  
+END;
+
+$body$ LANGUAGE plpgsql;
+
+
+-- these tables have been deleted in 6.2:
+CREATE TABLE DeletedFiles(
+       uuid VARCHAR(64) NOT NULL,      -- 0
+       fileType INTEGER,               -- 1
+       compressedSize BIGINT,          -- 2
+       uncompressedSize BIGINT,        -- 3
+       compressionType INTEGER,        -- 4
+       uncompressedHash VARCHAR(40),   -- 5
+       compressedHash VARCHAR(40)      -- 6
+       );
+
+CREATE TABLE RemainingAncestor(
+       resourceType INTEGER NOT NULL,
+       publicId VARCHAR(64) NOT NULL
+       );
+
+CREATE TABLE DeletedResources(
+       resourceType INTEGER NOT NULL,
+       publicId VARCHAR(64) NOT NULL
+       );
+
+
+-- these triggers have been introduced in 6.2, remove them
+DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources;
+DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources;
+DROP FUNCTION IF EXISTS IncrementResourcesTrackerFunc;
+DROP FUNCTION IF EXISTS DecrementResourcesTrackerFunc;
+
+-- this trigger has been removed in 6.2, reinstall it
+CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc()
+RETURNS TRIGGER AS $$
+BEGIN
+  IF TG_OP = 'INSERT' THEN
+    UPDATE GlobalIntegers SET value = value + 1 WHERE key = new.resourceType + 2;
+    RETURN new;
+  ELSIF TG_OP = 'DELETE' THEN
+    UPDATE GlobalIntegers SET value = value - 1 WHERE key = old.resourceType + 2;
+    RETURN old;
+  END IF;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE TRIGGER CountResourcesTracker
+AFTER INSERT OR DELETE ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE CountResourcesTrackerFunc();
+
+-- this trigger was introduced in 6.2, remove it:
+DROP FUNCTION IF EXISTS InsertOrUpdateMetadata;
+
+-- reinstall old triggers:
+CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0;
+  UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1;
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0;
+  UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1;
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+DROP TRIGGER AttachedFileIncrementSize ON AttachedFiles;
+CREATE TRIGGER AttachedFileIncrementSize
+AFTER INSERT ON AttachedFiles
+FOR EACH ROW
+EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
+
+DROP TRIGGER AttachedFileDecrementSize ON AttachedFiles;
+CREATE TRIGGER AttachedFileDecrementSize
+AFTER DELETE ON AttachedFiles
+FOR EACH ROW
+EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
+
+-- these functions have been introduced in 6.2:
+DROP FUNCTION IF EXISTS UpdateStatistics;
+DROP FUNCTION IF EXISTS UpdateSingleStatistic;
+
+-- this table has been introduced in 6.2:
+DROP TABLE IF EXISTS GlobalIntegersChanges;
+
+-- these functions have been introduced in 6.2:
+DROP FUNCTION IF EXISTS CreateDeletedFilesTemporaryTable;
+DROP FUNCTION IF EXISTS DeleteResource;
+
+-- reinstall this old trigger:
+CREATE OR REPLACE FUNCTION ResourceDeletedFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  --RAISE NOTICE 'Delete resource %', old.parentId;
+  INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
+  
+  -- http://stackoverflow.com/a/11299968/881731
+  IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN
+    -- Signal that the deleted resource has a remaining parent
+    INSERT INTO RemainingAncestor
+      SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId;
+  ELSE
+    -- Delete a parent resource when its unique child is deleted 
+    DELETE FROM Resources WHERE internalId = old.parentId;
+  END IF;
+  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();
+
+-- reinstall this old trigger:
+CREATE OR REPLACE FUNCTION PatientAddedFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  -- The "0" corresponds to "OrthancPluginResourceType_Patient"
+  IF new.resourceType = 0 THEN
+    INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId);
+  END IF;
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS PatientAdded ON Resources;
+CREATE TRIGGER PatientAdded
+AFTER INSERT ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE PatientAddedFunc();
+
+
+-- 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, 1); -- GlobalProperty_DatabasePatchLevel
+INSERT INTO GlobalProperties VALUES (11, 2); -- GlobalProperty_HasCreateInstance
\ No newline at end of file
--- a/PostgreSQL/Plugins/SQL/Downgrades/V6.2ToV6.1.sql	Wed Jan 31 15:43:10 2024 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,252 +0,0 @@
--- This file contains an SQL procedure to downgrade from schema 6.2 to 6.1 (version = 6, revision = 1).
--- It reinstalls all triggers and temporary tables that have been removed or replaced in 6.2
-
--- note: we don't not remove the unique constraints that have been added - they should not 
---       create any issues.
-
--- these constraints were introduced in 6.2
-ALTER TABLE Resources DROP CONSTRAINT UniquePublicId;
-ALTER TABLE PatientRecyclingOrder DROP CONSTRAINT UniquePatientId;
-
--- the CreateInstance has been replaced in 6.2, reinstall the 6.1
-DROP FUNCTION CreateInstance;
-CREATE FUNCTION CreateInstance(
-  IN patient TEXT,
-  IN study TEXT,
-  IN series TEXT,
-  IN instance TEXT,
-  OUT isNewPatient BIGINT,
-  OUT isNewStudy BIGINT,
-  OUT isNewSeries BIGINT,
-  OUT isNewInstance BIGINT,
-  OUT patientKey BIGINT,
-  OUT studyKey BIGINT,
-  OUT seriesKey BIGINT,
-  OUT instanceKey BIGINT) AS $body$
-
-DECLARE
-  patientSeq BIGINT;
-  countRecycling BIGINT;
-
-BEGIN
-  SELECT internalId FROM Resources INTO instanceKey WHERE publicId = instance AND resourceType = 3;
-
-  IF NOT (instanceKey IS NULL) THEN
-    -- This instance already exists, stop here
-    isNewInstance := 0;
-  ELSE
-    SELECT internalId FROM Resources INTO patientKey WHERE publicId = patient AND resourceType = 0;
-    SELECT internalId FROM Resources INTO studyKey WHERE publicId = study AND resourceType = 1;
-    SELECT internalId FROM Resources INTO seriesKey WHERE publicId = series AND resourceType = 2;
-
-    IF patientKey IS NULL THEN
-      -- Must create a new patient
-      IF NOT (studyKey IS NULL AND seriesKey IS NULL AND instanceKey IS NULL) THEN
-         RAISE EXCEPTION 'Broken invariant';
-      END IF;
-
-      INSERT INTO Resources VALUES (DEFAULT, 0, patient, NULL) RETURNING internalId INTO patientKey;
-      isNewPatient := 1;
-    ELSE
-      isNewPatient := 0;
-    END IF;
-  
-    IF (patientKey IS NULL) THEN
-       RAISE EXCEPTION 'Broken invariant';
-    END IF;
-
-    IF studyKey IS NULL THEN
-      -- Must create a new study
-      IF NOT (seriesKey IS NULL AND instanceKey IS NULL) THEN
-         RAISE EXCEPTION 'Broken invariant';
-      END IF;
-
-      INSERT INTO Resources VALUES (DEFAULT, 1, study, patientKey) RETURNING internalId INTO studyKey;
-      isNewStudy := 1;
-    ELSE
-      isNewStudy := 0;
-    END IF;
-
-    IF (studyKey IS NULL) THEN
-       RAISE EXCEPTION 'Broken invariant';
-    END IF;
-
-    IF seriesKey IS NULL THEN
-      -- Must create a new series
-      IF NOT (instanceKey IS NULL) THEN
-         RAISE EXCEPTION 'Broken invariant';
-      END IF;
-
-      INSERT INTO Resources VALUES (DEFAULT, 2, series, studyKey) RETURNING internalId INTO seriesKey;
-      isNewSeries := 1;
-    ELSE
-      isNewSeries := 0;
-    END IF;
-  
-    IF (seriesKey IS NULL OR NOT instanceKey IS NULL) THEN
-       RAISE EXCEPTION 'Broken invariant';
-    END IF;
-
-    INSERT INTO Resources VALUES (DEFAULT, 3, instance, seriesKey) RETURNING internalId INTO instanceKey;
-    isNewInstance := 1;
-
-    -- Move the patient to the end of the recycling order
-    SELECT seq FROM PatientRecyclingOrder WHERE patientId = patientKey INTO patientSeq;
-
-    IF NOT (patientSeq IS NULL) THEN
-       -- The patient is not protected
-       SELECT COUNT(*) FROM (SELECT * FROM PatientRecyclingOrder WHERE seq >= patientSeq LIMIT 2) AS tmp INTO countRecycling;
-       IF countRecycling = 2 THEN
-          -- The patient was not at the end of the recycling order
-          DELETE FROM PatientRecyclingOrder WHERE seq = patientSeq;
-          INSERT INTO PatientRecyclingOrder VALUES(DEFAULT, patientKey);
-       END IF;
-    END IF;
-  END IF;  
-END;
-
-$body$ LANGUAGE plpgsql;
-
-
--- these tables have been deleted in 6.2:
-CREATE TABLE DeletedFiles(
-       uuid VARCHAR(64) NOT NULL,      -- 0
-       fileType INTEGER,               -- 1
-       compressedSize BIGINT,          -- 2
-       uncompressedSize BIGINT,        -- 3
-       compressionType INTEGER,        -- 4
-       uncompressedHash VARCHAR(40),   -- 5
-       compressedHash VARCHAR(40)      -- 6
-       );
-
-CREATE TABLE RemainingAncestor(
-       resourceType INTEGER NOT NULL,
-       publicId VARCHAR(64) NOT NULL
-       );
-
-CREATE TABLE DeletedResources(
-       resourceType INTEGER NOT NULL,
-       publicId VARCHAR(64) NOT NULL
-       );
-
-
--- these triggers have been introduced in 6.2, remove them
-DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources;
-DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources;
-DROP FUNCTION IF EXISTS IncrementResourcesTrackerFunc;
-DROP FUNCTION IF EXISTS DecrementResourcesTrackerFunc;
-
--- this trigger has been removed in 6.2, reinstall it
-CREATE OR REPLACE FUNCTION CountResourcesTrackerFunc()
-RETURNS TRIGGER AS $$
-BEGIN
-  IF TG_OP = 'INSERT' THEN
-    UPDATE GlobalIntegers SET value = value + 1 WHERE key = new.resourceType + 2;
-    RETURN new;
-  ELSIF TG_OP = 'DELETE' THEN
-    UPDATE GlobalIntegers SET value = value - 1 WHERE key = old.resourceType + 2;
-    RETURN old;
-  END IF;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE TRIGGER CountResourcesTracker
-AFTER INSERT OR DELETE ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE CountResourcesTrackerFunc();
-
--- this trigger was introduced in 6.2, remove it:
-DROP FUNCTION IF EXISTS InsertOrUpdateMetadata;
-
--- reinstall old triggers:
-CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  UPDATE GlobalIntegers SET value = value + new.compressedSize WHERE key = 0;
-  UPDATE GlobalIntegers SET value = value + new.uncompressedSize WHERE key = 1;
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  UPDATE GlobalIntegers SET value = value - old.compressedSize WHERE key = 0;
-  UPDATE GlobalIntegers SET value = value - old.uncompressedSize WHERE key = 1;
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-DROP TRIGGER AttachedFileIncrementSize ON AttachedFiles;
-CREATE TRIGGER AttachedFileIncrementSize
-AFTER INSERT ON AttachedFiles
-FOR EACH ROW
-EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
-
-DROP TRIGGER AttachedFileDecrementSize ON AttachedFiles;
-CREATE TRIGGER AttachedFileDecrementSize
-AFTER DELETE ON AttachedFiles
-FOR EACH ROW
-EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
-
--- these functions have been introduced in 6.2:
-DROP FUNCTION IF EXISTS UpdateStatistics;
-DROP FUNCTION IF EXISTS UpdateSingleStatistic;
-
--- this table has been introduced in 6.2:
-DROP TABLE IF EXISTS GlobalIntegersChanges;
-
--- these functions have been introduced in 6.2:
-DROP FUNCTION IF EXISTS CreateDeletedFilesTemporaryTable;
-DROP FUNCTION IF EXISTS DeleteResource;
-
--- reinstall this old trigger:
-CREATE OR REPLACE FUNCTION ResourceDeletedFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  --RAISE NOTICE 'Delete resource %', old.parentId;
-  INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
-  
-  -- http://stackoverflow.com/a/11299968/881731
-  IF EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId) THEN
-    -- Signal that the deleted resource has a remaining parent
-    INSERT INTO RemainingAncestor
-      SELECT resourceType, publicId FROM Resources WHERE internalId = old.parentId;
-  ELSE
-    -- Delete a parent resource when its unique child is deleted 
-    DELETE FROM Resources WHERE internalId = old.parentId;
-  END IF;
-  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();
-
--- reinstall this old trigger:
-CREATE OR REPLACE FUNCTION PatientAddedFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  -- The "0" corresponds to "OrthancPluginResourceType_Patient"
-  IF new.resourceType = 0 THEN
-    INSERT INTO PatientRecyclingOrder VALUES (DEFAULT, new.internalId);
-  END IF;
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-DROP TRIGGER IF EXISTS PatientAdded ON Resources;
-CREATE TRIGGER PatientAdded
-AFTER INSERT ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE PatientAddedFunc();
-
-
--- 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, 1); -- GlobalProperty_DatabasePatchLevel
-INSERT INTO GlobalProperties VALUES (11, 2); -- GlobalProperty_HasCreateInstance
\ No newline at end of file
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev1ToRev2.sql	Mon Feb 05 09:43:14 2024 +0100
@@ -0,0 +1,48 @@
+-- This file contains part of the changes required to upgrade from 6.1 to 6.2 (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
+-- This file is executed only if the current schema is in revision 1 and it is executed 
+-- before PrepareIndex.sql that is idempotent.
+
+
+-- add unique constraints if they do not exists
+DO $body$
+BEGIN
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM information_schema.table_constraints
+        WHERE table_schema = 'public' 
+            AND table_name = 'resources'
+            AND constraint_name = 'uniquepublicid')
+    THEN
+        ALTER TABLE Resources ADD CONSTRAINT UniquePublicId UNIQUE (publicId);
+        RAISE NOTICE 'UniquePublicId constraint added to Resources.';
+    END IF;
+
+    IF NOT EXISTS (
+        SELECT 1
+        FROM information_schema.table_constraints
+        WHERE table_schema = 'public' 
+            AND table_name = 'patientrecyclingorder'
+            AND constraint_name = 'uniquepatientid')
+    THEN
+        ALTER TABLE PatientRecyclingOrder ADD CONSTRAINT UniquePatientId UNIQUE (patientId);
+        RAISE NOTICE 'UniquePatientId constraint added to PatientRecyclingOrder.';
+    END IF;
+
+END $body$ LANGUAGE plpgsql;
+
+
+-- In V6.2, we'll now use temporary tables so we need to remove the old tables that might have been used in previous revisions !
+-- these statements, although idempotent, are not part of PrepareIndexV2.sql to keep it clean
+DROP TABLE IF EXISTS DeletedFiles;
+DROP TABLE IF EXISTS RemainingAncestor;
+DROP TABLE IF EXISTS DeletedResources;
+
+-- These triggers disappears and are not replaced in V6.2
+DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources;
+
+-- The signature has changed so we must delete the function before replacing it.
+DROP FUNCTION IF EXISTS CreateInstance;
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/UnknownToRev1.sql	Mon Feb 05 09:43:14 2024 +0100
@@ -0,0 +1,18 @@
+-- add the revision columns if not yet done
+
+DO $body$
+BEGIN
+	IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='metadata' AND column_name='revision') THEN
+		ALTER TABLE Metadata ADD COLUMN revision INTEGER;
+	ELSE
+		raise notice 'the metadata.revision column already exists';
+	END IF;
+
+	IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='attachedfiles' AND column_name='revision') THEN
+		ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER;
+	ELSE
+		raise notice 'the attachedfiles.revision column already exists';
+	END IF;
+
+END $body$;
+
--- a/PostgreSQL/Plugins/SQL/Upgrades/UnknownToV6.1.sql	Wed Jan 31 15:43:10 2024 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,18 +0,0 @@
--- add the revision columns if not yet done
-
-DO $body$
-BEGIN
-	IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='metadata' AND column_name='revision') THEN
-		ALTER TABLE Metadata ADD COLUMN revision INTEGER;
-	ELSE
-		raise notice 'the metadata.revision column already exists';
-	END IF;
-
-	IF NOT EXISTS (SELECT * FROM information_schema.columns WHERE table_schema='public' AND table_name='attachedfiles' AND column_name='revision') THEN
-		ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER;
-	ELSE
-		raise notice 'the attachedfiles.revision column already exists';
-	END IF;
-
-END $body$;
-
--- a/PostgreSQL/Plugins/SQL/Upgrades/V6.1ToV6.2.sql	Wed Jan 31 15:43:10 2024 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,48 +0,0 @@
--- This file contains part of the changes required to upgrade from 6.1 to 6.2 (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
--- This file is executed only if the current schema is in revision 1 and it is executed 
--- before PrepareIndex.sql that is idempotent.
-
-
--- add unique constraints if they do not exists
-DO $body$
-BEGIN
-
-    IF NOT EXISTS (
-        SELECT 1
-        FROM information_schema.table_constraints
-        WHERE table_schema = 'public' 
-            AND table_name = 'resources'
-            AND constraint_name = 'uniquepublicid')
-    THEN
-        ALTER TABLE Resources ADD CONSTRAINT UniquePublicId UNIQUE (publicId);
-        RAISE NOTICE 'UniquePublicId constraint added to Resources.';
-    END IF;
-
-    IF NOT EXISTS (
-        SELECT 1
-        FROM information_schema.table_constraints
-        WHERE table_schema = 'public' 
-            AND table_name = 'patientrecyclingorder'
-            AND constraint_name = 'uniquepatientid')
-    THEN
-        ALTER TABLE PatientRecyclingOrder ADD CONSTRAINT UniquePatientId UNIQUE (patientId);
-        RAISE NOTICE 'UniquePatientId constraint added to PatientRecyclingOrder.';
-    END IF;
-
-END $body$ LANGUAGE plpgsql;
-
-
--- In V6.2, we'll now use temporary tables so we need to remove the old tables that might have been used in previous revisions !
--- these statements, although idempotent, are not part of PrepareIndexV2.sql to keep it clean
-DROP TABLE IF EXISTS DeletedFiles;
-DROP TABLE IF EXISTS RemainingAncestor;
-DROP TABLE IF EXISTS DeletedResources;
-
--- These triggers disappears and are not replaced in V6.2
-DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources;
-
--- The signature has changed so we must delete the function before replacing it.
-DROP FUNCTION IF EXISTS CreateInstance;