--- a/PostgreSQL/CMakeLists.txt	Mon Jan 15 18:27:20 2024 +0100
+++ b/PostgreSQL/CMakeLists.txt	Wed Jan 17 16:22:08 2024 +0100
@@ -77,9 +77,9 @@
 
 
 EmbedResources(
-  POSTGRESQL_PREPARE_INDEX_V2       ${CMAKE_SOURCE_DIR}/Plugins/SQL/PrepareIndexV2.sql
-  POSTGRESQL_UPGRADE_UNKNOWN_TO_V1  ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/UnknownToV1.sql
-  POSTGRESQL_UPGRADE_V1_TO_V2       ${CMAKE_SOURCE_DIR}/Plugins/SQL/Upgrades/V1ToV2.sql
+  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
   )
 
 

--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Mon Jan 15 18:27:20 2024 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Wed Jan 17 16:22:08 2024 +0100
@@ -66,7 +66,7 @@
     std::string query;
 
     Orthanc::EmbeddedResources::GetFileResource
-      (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX_V2);
+      (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX);
     t.GetDatabaseTransaction().ExecuteMultiLines(query);
 
     SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion, 6);
@@ -194,7 +194,7 @@
             std::string query;
 
             Orthanc::EmbeddedResources::GetFileResource
-              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_UNKNOWN_TO_V1);
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_UNKNOWN_TO_V6_1);
             t.GetDatabaseTransaction().ExecuteMultiLines(query);
           }
           
@@ -205,7 +205,7 @@
             std::string query;
 
             Orthanc::EmbeddedResources::GetFileResource
-              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_V1_TO_V2);
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_V6_1_TO_V6_2);
             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/V6.2ToV6.1.sql	Wed Jan 17 16:22:08 2024 +0100
@@ -0,0 +1,246 @@
+-- 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
+       );
+
+
+-- this trigger has been removed in 6.2
+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/PrepareIndex.sql	Wed Jan 17 16:22:08 2024 +0100
@@ -0,0 +1,560 @@
+-- This SQL file creates a DB in version.revision 6.2 directly
+-- It is also run after upgrade scripts to create new tables and or create/replace triggers and functions.
+-- This script is self contained, it contains everything that needs to be run to create an Orthanc DB.
+-- Note to developers: 
+--   - it is and must stay idempotent.
+--   - it is executed when the DB is "locked", only one Orthanc instance can execute it at a given time.                
+
+CREATE TABLE IF NOT EXISTS GlobalProperties(
+       property INTEGER PRIMARY KEY,
+       value TEXT
+       );
+
+CREATE TABLE IF NOT EXISTS Resources(
+       internalId BIGSERIAL NOT NULL PRIMARY KEY,
+       resourceType INTEGER NOT NULL,
+       publicId VARCHAR(64) NOT NULL,
+       parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+       CONSTRAINT UniquePublicId UNIQUE (publicId)
+       );
+
+CREATE TABLE IF NOT EXISTS MainDicomTags(
+       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+       tagGroup INTEGER,
+       tagElement INTEGER,
+       value TEXT,
+       PRIMARY KEY(id, tagGroup, tagElement)
+       );
+
+CREATE TABLE IF NOT EXISTS DicomIdentifiers(
+       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+       tagGroup INTEGER,
+       tagElement INTEGER,
+       value TEXT,
+       PRIMARY KEY(id, tagGroup, tagElement)
+       );
+
+CREATE TABLE IF NOT EXISTS Metadata(
+       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+       type INTEGER NOT NULL,
+       value TEXT,
+       revision INTEGER,
+       PRIMARY KEY(id, type)
+       );
+
+CREATE TABLE IF NOT EXISTS AttachedFiles(
+       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+       fileType INTEGER,
+       uuid VARCHAR(64) NOT NULL,
+       compressedSize BIGINT,
+       uncompressedSize BIGINT,
+       compressionType INTEGER,
+       uncompressedHash VARCHAR(40),
+       compressedHash VARCHAR(40),
+       revision INTEGER,
+       PRIMARY KEY(id, fileType)
+       );              
+
+CREATE TABLE IF NOT EXISTS Changes(
+       seq BIGSERIAL NOT NULL PRIMARY KEY,
+       changeType INTEGER,
+       internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+       resourceType INTEGER,
+       date VARCHAR(64)
+       );
+
+CREATE TABLE IF NOT EXISTS ExportedResources(
+       seq BIGSERIAL NOT NULL PRIMARY KEY,
+       resourceType INTEGER,
+       publicId VARCHAR(64),
+       remoteModality TEXT,
+       patientId VARCHAR(64),
+       studyInstanceUid TEXT,
+       seriesInstanceUid TEXT,
+       sopInstanceUid TEXT,
+       date VARCHAR(64)
+       ); 
+
+CREATE TABLE IF NOT EXISTS PatientRecyclingOrder(
+       seq BIGSERIAL NOT NULL PRIMARY KEY,
+       patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+       CONSTRAINT UniquePatientId UNIQUE (patientId)
+       );
+
+CREATE TABLE IF NOT EXISTS Labels(
+        id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
+        label TEXT, 
+        PRIMARY KEY(id, label)
+        );
+
+CREATE TABLE IF NOT EXISTS GlobalIntegers(
+       key INTEGER PRIMARY KEY,
+       value BIGINT);
+-- GlobalIntegers keys:
+-- 0: CompressedSize
+-- 1: UncompressedSize
+-- 2: PatientsCount
+-- 3: StudiesCount
+-- 4: SeriesCount
+-- 5: InstancesCount
+-- 6: ChangeSeq
+-- 7: PatientRecyclingOrderSeq
+
+CREATE TABLE IF NOT EXISTS ServerProperties(
+        server VARCHAR(64) NOT NULL,
+        property INTEGER, value TEXT, 
+        PRIMARY KEY(server, property)
+        );
+
+CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId);
+CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId);
+CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType);
+CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
+
+CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id);
+CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id);
+CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
+CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value);
+
+CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId);
+CREATE INDEX IF NOT EXISTS LabelsIndex1 ON LABELS(id);
+CREATE INDEX IF NOT EXISTS LabelsIndex2 ON LABELS(label);
+
+------------------- Trigram index creation -------------------
+
+
+-- Apply fix for performance issue (speed up wildcard search by using GIN trigrams). This implements the patch suggested
+-- in issue #47, BUT we also keep the original "DicomIdentifiersIndexValues", as it leads to better
+-- performance for "strict" searches (i.e. searches involving no wildcard).
+-- https://www.postgresql.org/docs/current/static/pgtrgm.html
+-- https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47
+
+DO $body$
+begin
+	IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_trgm') THEN
+		CREATE EXTENSION IF NOT EXISTS pg_trgm;
+        CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);
+	ELSE
+		RAISE NOTICE 'pg_trgm extension is not available on you system';
+	END IF;
+END $body$;
+
+
+------------------- PatientAdded trigger & PatientRecyclingOrder -------------------
+DROP TRIGGER IF EXISTS PatientAdded ON Resources;
+
+CREATE OR REPLACE FUNCTION PatientAddedOrUpdated(
+    IN patient_id BIGINT,
+    IN is_update BIGINT
+    )
+RETURNS VOID AS $body$
+BEGIN
+    DECLARE
+        newSeq BIGINT;
+    BEGIN
+        UPDATE GlobalIntegers SET value = value + 1 WHERE key = 7 RETURNING value INTO newSeq;
+        IF is_update > 0 THEN
+            -- Note: Protected patients are not listed in this table !  So, they won't be updated
+            UPDATE PatientRecyclingOrder SET seq = newSeq WHERE PatientRecyclingOrder.patientId = patient_id;
+        ELSE
+            INSERT INTO PatientRecyclingOrder VALUES (newSeq, patient_id);
+        END IF;
+    END;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION PatientAddedFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  -- The "0" corresponds to "OrthancPluginResourceType_Patient"
+  IF new.resourceType = 0 THEN
+    PERFORM PatientAddedOrUpdated(new.internalId, 0);
+  END IF;
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE TRIGGER PatientAdded
+AFTER INSERT ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE PatientAddedFunc();
+
+-- initial population of PatientRecyclingOrderSeq
+INSERT INTO GlobalIntegers
+    SELECT 7, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM PatientRecyclingOrder
+    ON CONFLICT DO NOTHING;
+
+
+------------------- ResourceDeleted trigger -------------------
+DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
+
+-- The following trigger combines 2 triggers from SQLite:
+-- ResourceDeleted + ResourceDeletedParentCleaning
+CREATE OR REPLACE FUNCTION ResourceDeletedFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId;
+  INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
+  
+  -- If this resource is the latest child, delete the parent
+  DELETE FROM Resources WHERE internalId = old.parentId
+                              AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE TRIGGER ResourceDeleted
+AFTER DELETE ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE ResourceDeletedFunc();
+
+
+------------------- 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_row RECORD;
+    locked_row RECORD;
+
+BEGIN
+
+    SET client_min_messages = warning;   -- suppress NOTICE:  relation "deletedresources" already exists, skipping
+    
+    -- note: temporary tables are created at session (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 session
+    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_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE;
+
+    -- delete the resource itself
+    DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row;
+    -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + 
+
+    -- 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_row.parentId
+            AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId);
+
+END;
+
+$body$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable(
+) RETURNS VOID AS $body$
+
+BEGIN
+
+    SET client_min_messages = warning;   -- suppress NOTICE:  relation "deletedresources" already exists, skipping
+    
+    -- note: temporary tables are created at session (connection) level -> they are likely to exist
+    CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles(
+        uuid VARCHAR(64) NOT NULL,
+        fileType INTEGER,
+        compressedSize BIGINT,
+        uncompressedSize BIGINT,
+        compressionType INTEGER,
+        uncompressedHash VARCHAR(40),
+        compressedHash VARCHAR(40)
+        );
+
+    RESET client_min_messages;
+
+    -- clear the temporary table in case it has been created earlier in the session
+    DELETE FROM DeletedFiles;
+END;
+
+$body$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  INSERT INTO DeletedFiles VALUES
+    (old.uuid, old.filetype, old.compressedSize,
+     old.uncompressedSize, old.compressionType,
+     old.uncompressedHash, old.compressedHash);
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE TRIGGER AttachedFileDeleted
+AFTER DELETE ON AttachedFiles
+FOR EACH ROW
+EXECUTE PROCEDURE AttachedFileDeletedFunc();
+
+
+------------------- Fast Statistics -------------------
+
+-- initial population of GlobalIntegers if not already there
+INSERT INTO GlobalIntegers
+    SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO GlobalIntegers
+    SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO GlobalIntegers
+    SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0  -- Count patients
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO GlobalIntegers
+    SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1  -- Count studies
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO GlobalIntegers
+    SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2  -- Count series
+    ON CONFLICT DO NOTHING;
+
+INSERT INTO GlobalIntegers
+    SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3  -- Count instances
+    ON CONFLICT DO NOTHING;
+
+
+-- this table stores all changes that needs to be performed to the GlobalIntegers table
+-- This way, each transaction can add row independently in this table without having to lock
+-- any row (which was the case with previous FastTotalSize).
+-- These changes will be applied at regular interval by an external thread or when someone
+-- requests the statistics
+CREATE TABLE IF NOT EXISTS GlobalIntegersChanges(
+    key INTEGER,
+    value BIGINT);
+
+CREATE OR REPLACE FUNCTION UpdateSingleStatistic(
+    IN statistics_key INTEGER,
+    OUT new_value BIGINT
+) AS $body$
+BEGIN
+
+  -- Delete the current changes, sum them and update the GlobalIntegers row.
+  -- New rows can be added in the meantime, they won't be deleted or summed.
+  WITH deleted_rows AS (
+      DELETE FROM GlobalIntegersChanges
+      WHERE GlobalIntegersChanges.key = statistics_key
+      RETURNING value
+  )
+  UPDATE GlobalIntegers
+  SET value = value + (
+      SELECT COALESCE(SUM(value), 0)
+      FROM deleted_rows
+  )
+  WHERE GlobalIntegers.key = statistics_key
+  RETURNING value INTO new_value;
+
+END;
+$body$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION UpdateStatistics(
+  OUT patients_cunt BIGINT,
+  OUT studies_count BIGINT,
+  OUT series_count BIGINT,
+  OUT instances_count BIGINT,
+  OUT total_compressed_size BIGINT,
+  OUT total_uncompressed_size BIGINT
+) AS $body$
+BEGIN
+
+  SELECT UpdateSingleStatistic(0) INTO total_compressed_size;
+  SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size;
+  SELECT UpdateSingleStatistic(2) INTO patients_cunt;
+  SELECT UpdateSingleStatistic(3) INTO studies_count;
+  SELECT UpdateSingleStatistic(4) INTO series_count;
+  SELECT UpdateSingleStatistic(5) INTO instances_count;
+
+END;
+$body$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc()
+RETURNS TRIGGER AS $$
+BEGIN
+  INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1);
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc()
+RETURNS TRIGGER AS $$
+BEGIN
+  INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1);
+  RETURN NULL;
+END;
+$$ LANGUAGE plpgsql;
+
+
+CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
+RETURNS TRIGGER AS $body$
+BEGIN
+  INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize);
+  INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize);
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+  INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize);
+  INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize);
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles;
+CREATE TRIGGER AttachedFileIncrementSize
+AFTER INSERT ON AttachedFiles
+FOR EACH ROW
+EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
+
+DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles;
+CREATE TRIGGER AttachedFileDecrementSize
+AFTER DELETE ON AttachedFiles
+FOR EACH ROW
+EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
+
+DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources;
+CREATE TRIGGER IncrementResourcesTracker
+AFTER INSERT ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE IncrementResourcesTrackerFunc();
+
+DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources;
+CREATE TRIGGER DecrementResourcesTracker
+AFTER DELETE ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE DecrementResourcesTrackerFunc();
+
+
+------------------- InsertOrUpdateMetadata function -------------------
+CREATE OR REPLACE FUNCTION InsertOrUpdateMetadata(resource_ids BIGINT[],
+                                                  metadata_types INTEGER[], 
+                                                  metadata_values TEXT[],
+                                                  revisions INTEGER[])
+RETURNS VOID AS $body$
+BEGIN
+  	FOR i IN 1 .. ARRAY_LENGTH(resource_ids, 1) LOOP
+		-- RAISE NOTICE 'Parameter %: % % %', i, resource_ids[i], metadata_types[i], metadata_values[i];
+		INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i]) 
+          ON CONFLICT (id, type) DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision;
+	END LOOP;
+  
+END;
+$body$ LANGUAGE plpgsql;
+
+
+------------------- GetLastChange function -------------------
+DROP TRIGGER IF EXISTS InsertedChange ON Changes;
+
+-- insert the value if not already there
+INSERT INTO GlobalIntegers
+    SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes
+    ON CONFLICT DO NOTHING;
+
+CREATE OR REPLACE FUNCTION InsertedChangeFunc() 
+RETURNS TRIGGER AS $body$
+BEGIN
+    UPDATE GlobalIntegers SET value = new.seq WHERE key = 6;
+    RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE TRIGGER InsertedChange
+AFTER INSERT ON Changes
+FOR EACH ROW
+EXECUTE PROCEDURE InsertedChangeFunc();
+
+
+------------------- 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
+DELETE FROM GlobalProperties WHERE property IN (1, 4, 6, 10, 11, 12, 13);
+INSERT INTO GlobalProperties VALUES (1, 6); -- GlobalProperty_DatabaseSchemaVersion
+INSERT INTO GlobalProperties VALUES (4, 2); -- 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
+INSERT INTO GlobalProperties VALUES (12, 1); -- GlobalProperty_HasFastCountResources
+INSERT INTO GlobalProperties VALUES (13, 1); -- GlobalProperty_GetLastChangeIndex

--- a/PostgreSQL/Plugins/SQL/PrepareIndexV2.sql	Mon Jan 15 18:27:20 2024 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,548 +0,0 @@
--- This SQL file creates a DB in revision 2 directly (version is being used from v 6.0 of the PostgreSQL plugin)
--- It is also run after upgrade scripts to create new tables and or create/replace triggers and functions.
--- This script is self contained, it contains everything that needs to be run to create an Orthanc DB.
--- Note: it is and must be idempotent.
-
-CREATE TABLE IF NOT EXISTS GlobalProperties(
-       property INTEGER PRIMARY KEY,
-       value TEXT
-       );
-
-CREATE TABLE IF NOT EXISTS Resources(
-       internalId BIGSERIAL NOT NULL PRIMARY KEY,
-       resourceType INTEGER NOT NULL,
-       publicId VARCHAR(64) NOT NULL,
-       parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       CONSTRAINT UniquePublicId UNIQUE (publicId)
-       );
-
-CREATE TABLE IF NOT EXISTS MainDicomTags(
-       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       tagGroup INTEGER,
-       tagElement INTEGER,
-       value TEXT,
-       PRIMARY KEY(id, tagGroup, tagElement)
-       );
-
-CREATE TABLE IF NOT EXISTS DicomIdentifiers(
-       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       tagGroup INTEGER,
-       tagElement INTEGER,
-       value TEXT,
-       PRIMARY KEY(id, tagGroup, tagElement)
-       );
-
-CREATE TABLE IF NOT EXISTS Metadata(
-       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       type INTEGER NOT NULL,
-       value TEXT,
-       revision INTEGER,
-       PRIMARY KEY(id, type)
-       );
-
-CREATE TABLE IF NOT EXISTS AttachedFiles(
-       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       fileType INTEGER,
-       uuid VARCHAR(64) NOT NULL,
-       compressedSize BIGINT,
-       uncompressedSize BIGINT,
-       compressionType INTEGER,
-       uncompressedHash VARCHAR(40),
-       compressedHash VARCHAR(40),
-       revision INTEGER,
-       PRIMARY KEY(id, fileType)
-       );              
-
-CREATE TABLE IF NOT EXISTS Changes(
-       seq BIGSERIAL NOT NULL PRIMARY KEY,
-       changeType INTEGER,
-       internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       resourceType INTEGER,
-       date VARCHAR(64)
-       );
-
-CREATE TABLE IF NOT EXISTS ExportedResources(
-       seq BIGSERIAL NOT NULL PRIMARY KEY,
-       resourceType INTEGER,
-       publicId VARCHAR(64),
-       remoteModality TEXT,
-       patientId VARCHAR(64),
-       studyInstanceUid TEXT,
-       seriesInstanceUid TEXT,
-       sopInstanceUid TEXT,
-       date VARCHAR(64)
-       ); 
-
-CREATE TABLE IF NOT EXISTS PatientRecyclingOrder(
-       seq BIGSERIAL NOT NULL PRIMARY KEY,
-       patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       CONSTRAINT UniquePatientId UNIQUE (patientId)
-       );
-
-CREATE TABLE IF NOT EXISTS Labels(
-        id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-        label TEXT, 
-        PRIMARY KEY(id, label)
-        );
-
-CREATE TABLE IF NOT EXISTS GlobalIntegers(
-       key INTEGER PRIMARY KEY,
-       value BIGINT);
--- GlobalIntegers keys:
--- 0: CompressedSize
--- 1: UncompressedSize
--- 2: PatientsCount
--- 3: StudiesCount
--- 4: SeriesCount
--- 5: InstancesCount
--- 6: ChangeSeq
--- 7: PatientRecyclingOrderSeq
-
-CREATE TABLE IF NOT EXISTS ServerProperties(
-        server VARCHAR(64) NOT NULL,
-        property INTEGER, value TEXT, 
-        PRIMARY KEY(server, property)
-        );
-
-CREATE INDEX IF NOT EXISTS ChildrenIndex ON Resources(parentId);
-CREATE INDEX IF NOT EXISTS PublicIndex ON Resources(publicId);
-CREATE INDEX IF NOT EXISTS ResourceTypeIndex ON Resources(resourceType);
-CREATE INDEX IF NOT EXISTS PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
-
-CREATE INDEX IF NOT EXISTS MainDicomTagsIndex ON MainDicomTags(id);
-CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex1 ON DicomIdentifiers(id);
-CREATE INDEX IF NOT EXISTS DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
-CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues ON DicomIdentifiers(value);
-
-CREATE INDEX IF NOT EXISTS ChangesIndex ON Changes(internalId);
-CREATE INDEX IF NOT EXISTS LabelsIndex1 ON LABELS(id);
-CREATE INDEX IF NOT EXISTS LabelsIndex2 ON LABELS(label);
-
-------------------- Trigram index creation -------------------
-
-
--- Apply fix for performance issue (speed up wildcard search by using GIN trigrams). This implements the patch suggested
--- in issue #47, BUT we also keep the original "DicomIdentifiersIndexValues", as it leads to better
--- performance for "strict" searches (i.e. searches involving no wildcard).
--- https://www.postgresql.org/docs/current/static/pgtrgm.html
--- https://orthanc.uclouvain.be/bugs/show_bug.cgi?id=47
-
-DO $body$
-begin
-	IF EXISTS (SELECT 1 FROM pg_available_extensions WHERE name='pg_trgm') THEN
-		CREATE EXTENSION IF NOT EXISTS pg_trgm;
-        CREATE INDEX IF NOT EXISTS DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);
-	ELSE
-		RAISE NOTICE 'pg_trgm extension is not available on you system';
-	END IF;
-END $body$;
-
-
-------------------- PatientAdded trigger & PatientRecyclingOrder -------------------
-DROP TRIGGER IF EXISTS PatientAdded ON Resources;
-
-CREATE OR REPLACE FUNCTION PatientAddedOrUpdated(
-    IN patient_id BIGINT,
-    IN is_update BIGINT
-    )
-RETURNS VOID AS $body$
-BEGIN
-    DECLARE
-        newSeq BIGINT;
-    BEGIN
-        UPDATE GlobalIntegers SET value = value + 1 WHERE key = 7 RETURNING value INTO newSeq;
-        IF is_update > 0 THEN
-            -- Note: Protected patients are not listed in this table !  So, they won't be updated
-            UPDATE PatientRecyclingOrder SET seq = newSeq WHERE PatientRecyclingOrder.patientId = patient_id;
-        ELSE
-            INSERT INTO PatientRecyclingOrder VALUES (newSeq, patient_id);
-        END IF;
-    END;
-END;
-$body$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION PatientAddedFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  -- The "0" corresponds to "OrthancPluginResourceType_Patient"
-  IF new.resourceType = 0 THEN
-    PERFORM PatientAddedOrUpdated(new.internalId, 0);
-  END IF;
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-CREATE TRIGGER PatientAdded
-AFTER INSERT ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE PatientAddedFunc();
-
--- initial population of PatientRecyclingOrderSeq
-INSERT INTO GlobalIntegers
-    SELECT 7, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM PatientRecyclingOrder
-    ON CONFLICT DO NOTHING;
-
-
-------------------- ResourceDeleted trigger -------------------
-DROP TRIGGER IF EXISTS ResourceDeleted ON Resources;
-
--- The following trigger combines 2 triggers from SQLite:
--- ResourceDeleted + ResourceDeletedParentCleaning
-CREATE OR REPLACE FUNCTION ResourceDeletedFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  -- RAISE NOTICE 'ResourceDeletedFunc %', old.publicId;
-  INSERT INTO DeletedResources VALUES (old.resourceType, old.publicId);
-  
-  -- If this resource is the latest child, delete the parent
-  DELETE FROM Resources WHERE internalId = old.parentId
-                              AND NOT EXISTS (SELECT 1 FROM Resources WHERE parentId = old.parentId);
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-CREATE TRIGGER ResourceDeleted
-AFTER DELETE ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE ResourceDeletedFunc();
-
-
-------------------- 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_row RECORD;
-    locked_row RECORD;
-
-BEGIN
-
-    SET client_min_messages = warning;   -- suppress NOTICE:  relation "deletedresources" already exists, skipping
-    
-    -- note: temporary tables are created at session (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 session
-    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_row FROM resources WHERE internalid = (SELECT parentid FROM resources WHERE internalid = id) FOR UPDATE;
-
-    -- delete the resource itself
-    DELETE FROM Resources WHERE internalId=id RETURNING * INTO deleted_row;
-    -- note: there is a ResourceDeletedFunc trigger that will execute here and delete the parents if there are no remaining children + 
-
-    -- 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_row.parentId
-            AND EXISTS (SELECT 1 FROM Resources WHERE parentId = deleted_row.parentId);
-
-END;
-
-$body$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION CreateDeletedFilesTemporaryTable(
-) RETURNS VOID AS $body$
-
-BEGIN
-
-    SET client_min_messages = warning;   -- suppress NOTICE:  relation "deletedresources" already exists, skipping
-    
-    -- note: temporary tables are created at session (connection) level -> they are likely to exist
-    CREATE TEMPORARY TABLE IF NOT EXISTS DeletedFiles(
-        uuid VARCHAR(64) NOT NULL,
-        fileType INTEGER,
-        compressedSize BIGINT,
-        uncompressedSize BIGINT,
-        compressionType INTEGER,
-        uncompressedHash VARCHAR(40),
-        compressedHash VARCHAR(40)
-        );
-
-    RESET client_min_messages;
-
-    -- clear the temporary table in case it has been created earlier in the session
-    DELETE FROM DeletedFiles;
-END;
-
-$body$ LANGUAGE plpgsql;
-
-
-DROP TRIGGER IF EXISTS AttachedFileDeleted ON AttachedFiles;
-
-CREATE OR REPLACE FUNCTION AttachedFileDeletedFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  INSERT INTO DeletedFiles VALUES
-    (old.uuid, old.filetype, old.compressedSize,
-     old.uncompressedSize, old.compressionType,
-     old.uncompressedHash, old.compressedHash);
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-CREATE TRIGGER AttachedFileDeleted
-AFTER DELETE ON AttachedFiles
-FOR EACH ROW
-EXECUTE PROCEDURE AttachedFileDeletedFunc();
-
-
-------------------- Fast Statistics -------------------
-
--- initial population of GlobalIntegers if not already there
-INSERT INTO GlobalIntegers
-    SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles
-    ON CONFLICT DO NOTHING;
-
-INSERT INTO GlobalIntegers
-    SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles
-    ON CONFLICT DO NOTHING;
-
-INSERT INTO GlobalIntegers
-    SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0  -- Count patients
-    ON CONFLICT DO NOTHING;
-
-INSERT INTO GlobalIntegers
-    SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1  -- Count studies
-    ON CONFLICT DO NOTHING;
-
-INSERT INTO GlobalIntegers
-    SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2  -- Count series
-    ON CONFLICT DO NOTHING;
-
-INSERT INTO GlobalIntegers
-    SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3  -- Count instances
-    ON CONFLICT DO NOTHING;
-
-
--- this table stores all changes that needs to be performed to the GlobalIntegers table
--- This way, each transaction can add row independently in this table without having to lock
--- any row (which was the case with previous FastTotalSize).
--- These changes will be applied at regular interval by an external thread or when someone
--- requests the statistics
-CREATE TABLE IF NOT EXISTS GlobalIntegersChanges(
-    key INTEGER,
-    value BIGINT);
-
-CREATE OR REPLACE FUNCTION UpdateSingleStatistic(
-    IN statistics_key INTEGER,
-    OUT new_value BIGINT
-) AS $body$
-BEGIN
-
-  -- Delete the current changes, sum them and update the GlobalIntegers row.
-  -- New rows can be added in the meantime, they won't be deleted or summed.
-  WITH deleted_rows AS (
-      DELETE FROM GlobalIntegersChanges
-      WHERE GlobalIntegersChanges.key = statistics_key
-      RETURNING value
-  )
-  UPDATE GlobalIntegers
-  SET value = value + (
-      SELECT COALESCE(SUM(value), 0)
-      FROM deleted_rows
-  )
-  WHERE GlobalIntegers.key = statistics_key
-  RETURNING value INTO new_value;
-
-END;
-$body$ LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION UpdateStatistics(
-  OUT patients_cunt BIGINT,
-  OUT studies_count BIGINT,
-  OUT series_count BIGINT,
-  OUT instances_count BIGINT,
-  OUT total_compressed_size BIGINT,
-  OUT total_uncompressed_size BIGINT
-) AS $body$
-BEGIN
-
-  SELECT UpdateSingleStatistic(0) INTO total_compressed_size;
-  SELECT UpdateSingleStatistic(1) INTO total_uncompressed_size;
-  SELECT UpdateSingleStatistic(2) INTO patients_cunt;
-  SELECT UpdateSingleStatistic(3) INTO studies_count;
-  SELECT UpdateSingleStatistic(4) INTO series_count;
-  SELECT UpdateSingleStatistic(5) INTO instances_count;
-
-END;
-$body$ LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION IncrementResourcesTrackerFunc()
-RETURNS TRIGGER AS $$
-BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(new.resourceType + 2, 1);
-  RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION DecrementResourcesTrackerFunc()
-RETURNS TRIGGER AS $$
-BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(old.resourceType + 2, -1);
-  RETURN NULL;
-END;
-$$ LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION AttachedFileIncrementSizeFunc()
-RETURNS TRIGGER AS $body$
-BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(0, new.compressedSize);
-  INSERT INTO GlobalIntegersChanges VALUES(1, new.uncompressedSize);
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-CREATE OR REPLACE FUNCTION AttachedFileDecrementSizeFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-  INSERT INTO GlobalIntegersChanges VALUES(0, -old.compressedSize);
-  INSERT INTO GlobalIntegersChanges VALUES(1, -old.uncompressedSize);
-  RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-DROP TRIGGER IF EXISTS AttachedFileIncrementSize on AttachedFiles;
-CREATE TRIGGER AttachedFileIncrementSize
-AFTER INSERT ON AttachedFiles
-FOR EACH ROW
-EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
-
-DROP TRIGGER IF EXISTS AttachedFileDecrementSize on AttachedFiles;
-CREATE TRIGGER AttachedFileDecrementSize
-AFTER DELETE ON AttachedFiles
-FOR EACH ROW
-EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
-
-DROP TRIGGER IF EXISTS IncrementResourcesTracker on Resources;
-CREATE TRIGGER IncrementResourcesTracker
-AFTER INSERT ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE IncrementResourcesTrackerFunc();
-
-DROP TRIGGER IF EXISTS DecrementResourcesTracker on Resources;
-CREATE TRIGGER DecrementResourcesTracker
-AFTER DELETE ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE DecrementResourcesTrackerFunc();
-
-
-------------------- InsertOrUpdateMetadata function -------------------
-CREATE OR REPLACE FUNCTION InsertOrUpdateMetadata(resource_ids BIGINT[],
-                                                  metadata_types INTEGER[], 
-                                                  metadata_values TEXT[],
-                                                  revisions INTEGER[])
-RETURNS VOID AS $body$
-BEGIN
-  	FOR i IN 1 .. ARRAY_LENGTH(resource_ids, 1) LOOP
-		-- RAISE NOTICE 'Parameter %: % % %', i, resource_ids[i], metadata_types[i], metadata_values[i];
-		INSERT INTO Metadata VALUES(resource_ids[i], metadata_types[i], metadata_values[i], revisions[i]) 
-          ON CONFLICT (id, type) DO UPDATE SET value = EXCLUDED.value, revision = EXCLUDED.revision;
-	END LOOP;
-  
-END;
-$body$ LANGUAGE plpgsql;
-
-
-------------------- GetLastChange function -------------------
-DROP TRIGGER IF EXISTS InsertedChange ON Changes;
-
--- insert the value if not already there
-INSERT INTO GlobalIntegers
-    SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes
-    ON CONFLICT DO NOTHING;
-
-CREATE OR REPLACE FUNCTION InsertedChangeFunc() 
-RETURNS TRIGGER AS $body$
-BEGIN
-    UPDATE GlobalIntegers SET value = new.seq WHERE key = 6;
-    RETURN NULL;
-END;
-$body$ LANGUAGE plpgsql;
-
-CREATE TRIGGER InsertedChange
-AFTER INSERT ON Changes
-FOR EACH ROW
-EXECUTE PROCEDURE InsertedChangeFunc();
-
-
-------------------- 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;

--- a/PostgreSQL/Plugins/SQL/Upgrades/UnknownToV1.sql	Mon Jan 15 18:27:20 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$;
-

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/UnknownToV6.1.sql	Wed Jan 17 16:22:08 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/V1ToV2.sql	Mon Jan 15 18:27:20 2024 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,45 +0,0 @@
--- This file contains part of the changes required to upgrade from revision 1 to revision 2 (v 6.0)
--- It actually contains only the changes that:
-   -- can not be executed with an idempotent statement in SQL
-   -- or would polute the PrepareIndexV2.sql
--- This file is executed only if the current schema is in revision 1 and it is executed before PrepareIndexV2.sql
--- that is idempotent.
-
--- add unique constraints if they donot 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 V2, 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 V2
-DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources;
-

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/V6.1ToV6.2.sql	Wed Jan 17 16:22:08 2024 +0100
@@ -0,0 +1,46 @@
+-- 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;
+