# HG changeset patch
# User Alain Mazy <am@osimis.io>
# Date 1702923484 -3600
# Node ID 2bc65c678b1bc990339cdfb5453166477b261bf1
# Parent  f790232b77ef234beb09b925c523ec0624eca4c0
cont cleanup

diff -r f790232b77ef -r 2bc65c678b1b PostgreSQL/Plugins/CreateInstance.sql
--- a/PostgreSQL/Plugins/CreateInstance.sql	Mon Dec 18 19:17:09 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,73 +0,0 @@
-CREATE OR REPLACE 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
-	isNewPatient := 1;
-	isNewStudy := 1;
-	isNewSeries := 1;
-	isNewInstance := 1;
-
-	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 0, patient, NULL);
-    EXCEPTION
-        WHEN unique_violation THEN
-            isNewPatient := 0;
-    END;
-    SELECT internalid INTO patientKey FROM "resources" WHERE publicId=patient AND resourcetype = 0;
-
-	BEGIN
-        INSERT INTO "resources" VALUES (DEFAULT, 1, study, patientKey);
-    EXCEPTION
-        WHEN unique_violation THEN
-            isNewStudy := 0;
-    END;
-    SELECT internalid INTO studyKey FROM "resources" WHERE publicId=study AND resourcetype = 1;
-
-	BEGIN
-	    INSERT INTO "resources" VALUES (DEFAULT, 2, series, studyKey);
-    EXCEPTION
-        WHEN unique_violation THEN
-            isNewSeries := 0;
-    END;
-	SELECT internalid INTO seriesKey FROM "resources" WHERE publicId=series AND resourcetype = 2;
-
-  	BEGIN
-		INSERT INTO "resources" VALUES (DEFAULT, 3, instance, seriesKey);
-    EXCEPTION
-        WHEN unique_violation THEN
-            isNewInstance := 0;
-    END;
-    SELECT internalid INTO instanceKey FROM "resources" WHERE publicId=instance AND resourcetype = 3;   
-
-  IF isNewInstance > 0 THEN
-    -- 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;
diff -r f790232b77ef -r 2bc65c678b1b PostgreSQL/Plugins/FastCountResources.sql
--- a/PostgreSQL/Plugins/FastCountResources.sql	Mon Dec 18 19:17:09 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,33 +0,0 @@
--- https://wiki.postgresql.org/wiki/Count_estimate
-
-INSERT INTO GlobalIntegers
-SELECT 2, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 0;  -- Count patients
-
-INSERT INTO GlobalIntegers
-SELECT 3, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 1;  -- Count studies
-
-INSERT INTO GlobalIntegers
-SELECT 4, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 2;  -- Count series
-
-INSERT INTO GlobalIntegers
-SELECT 5, CAST(COALESCE(COUNT(*), 0) AS BIGINT) FROM Resources WHERE resourceType = 3;  -- Count instances
-
-
-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();
diff -r f790232b77ef -r 2bc65c678b1b PostgreSQL/Plugins/FastTotalSize.sql
--- a/PostgreSQL/Plugins/FastTotalSize.sql	Mon Dec 18 19:17:09 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,41 +0,0 @@
-CREATE TABLE GlobalIntegers(
-       key INTEGER PRIMARY KEY,
-       value BIGINT);
-
-INSERT INTO GlobalIntegers
-SELECT 0, CAST(COALESCE(SUM(compressedSize), 0) AS BIGINT) FROM AttachedFiles;
-
-INSERT INTO GlobalIntegers
-SELECT 1, CAST(COALESCE(SUM(uncompressedSize), 0) AS BIGINT) FROM AttachedFiles;
-
-
-
-CREATE 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 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;
-
-
-
-CREATE TRIGGER AttachedFileIncrementSize
-AFTER INSERT ON AttachedFiles
-FOR EACH ROW
-EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
-
-CREATE TRIGGER AttachedFileDecrementSize
-AFTER DELETE ON AttachedFiles
-FOR EACH ROW
-EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
diff -r f790232b77ef -r 2bc65c678b1b PostgreSQL/Plugins/FastTotalStats2.sql
--- a/PostgreSQL/Plugins/FastTotalStats2.sql	Mon Dec 18 19:17:09 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,116 +0,0 @@
--- uninstall FastTotalSize & FastCountResources
-DROP TRIGGER IF EXISTS AttachedFileIncrementSize ON AttachedFiles;
-DROP TRIGGER IF EXISTS AttachedFileDecrementSize ON AttachedFiles;
-DROP TRIGGER IF EXISTS CountResourcesTracker ON Resources;
-
--- 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 statisticsKey INTEGER,
-    OUT newValue 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 = statisticsKey
-      RETURNING value
-  )
-  UPDATE GlobalIntegers
-  SET value = value + (
-      SELECT COALESCE(SUM(value), 0)
-      FROM deleted_rows
-  )
-  WHERE GlobalIntegers.key = statisticsKey
-  RETURNING value INTO newValue;
-
-END;
-$body$ LANGUAGE plpgsql;
-
-
-CREATE OR REPLACE FUNCTION UpdateStatistics(
-  OUT patientsCount BIGINT,
-  OUT studiesCount BIGINT,
-  OUT seriesCount BIGINT,
-  OUT instancesCount BIGINT,
-  OUT totalCompressedSize BIGINT,
-  OUT totalUncompressedSize BIGINT
-) AS $body$
-BEGIN
-
-  SELECT UpdateSingleStatistic(0) INTO totalCompressedSize;
-  SELECT UpdateSingleStatistic(1) INTO totalUncompressedSize;
-  SELECT UpdateSingleStatistic(2) INTO patientsCount;
-  SELECT UpdateSingleStatistic(3) INTO studiesCount;
-  SELECT UpdateSingleStatistic(4) INTO seriesCount;
-  SELECT UpdateSingleStatistic(5) INTO instancesCount;
-
-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;
-
-
-
-CREATE TRIGGER AttachedFileIncrementSize
-AFTER INSERT ON AttachedFiles
-FOR EACH ROW
-EXECUTE PROCEDURE AttachedFileIncrementSizeFunc();
-
-CREATE TRIGGER AttachedFileDecrementSize
-AFTER DELETE ON AttachedFiles
-FOR EACH ROW
-EXECUTE PROCEDURE AttachedFileDecrementSizeFunc();
-
-CREATE TRIGGER IncrementResourcesTracker
-AFTER INSERT ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE IncrementResourcesTrackerFunc();
-
-CREATE TRIGGER DecrementResourcesTracker
-AFTER DELETE ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE DecrementResourcesTrackerFunc();
diff -r f790232b77ef -r 2bc65c678b1b PostgreSQL/Plugins/GetLastChangeIndex.sql
--- a/PostgreSQL/Plugins/GetLastChangeIndex.sql	Mon Dec 18 19:17:09 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,27 +0,0 @@
--- In PostgreSQL, the most straightforward query would be to run:
-
---   SELECT currval(pg_get_serial_sequence('Changes', 'seq'))".
-
--- Unfortunately, this raises the error message "currval of sequence
--- "changes_seq_seq" is not yet defined in this session" if no change
--- has been inserted before the SELECT. We thus track the sequence
--- index with a trigger.
--- http://www.neilconway.org/docs/sequences/
-
-INSERT INTO GlobalIntegers
-SELECT 6, CAST(COALESCE(MAX(seq), 0) AS BIGINT) FROM Changes;
-
-
-CREATE 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();
diff -r f790232b77ef -r 2bc65c678b1b PostgreSQL/Plugins/InsertOrUpdateMetadata.sql
--- a/PostgreSQL/Plugins/InsertOrUpdateMetadata.sql	Mon Dec 18 19:17:09 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,13 +0,0 @@
-CREATE OR REPLACE FUNCTION InsertOrUpdateMetadata(resourceIds BIGINT[],
-                                                  metadataTypes INTEGER[], 
-                                                  metadataValues TEXT[],
-                                                  revisions INTEGER[])
-RETURNS VOID AS $body$
-BEGIN
-  	FOR i IN 1 .. ARRAY_LENGTH(resourceIds, 1) LOOP
-		-- RAISE NOTICE 'Parameter %: % % %', i, resourceIds[i], metadataTypes[i], metadataValues[i];
-		INSERT INTO Metadata VALUES(resourceIds[i], metadataTypes[i], metadataValues[i], revisions[i]) ON CONFLICT DO NOTHING;
-	END LOOP;
-  
-END;
-$body$ LANGUAGE plpgsql;
\ No newline at end of file
diff -r f790232b77ef -r 2bc65c678b1b PostgreSQL/Plugins/PrepareIndex.sql
--- a/PostgreSQL/Plugins/PrepareIndex.sql	Mon Dec 18 19:17:09 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,172 +0,0 @@
-CREATE TABLE GlobalProperties(
-       property INTEGER PRIMARY KEY,
-       value TEXT
-       );
-
-CREATE TABLE Resources(
-       internalId BIGSERIAL NOT NULL PRIMARY KEY,
-       resourceType INTEGER NOT NULL,
-       publicId VARCHAR(64) NOT NULL,
-       parentId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE
-       -- UNIQUE (publicId)  -- this is made unique in C++ code (new in plugin v X.Y.Z)
-       );
-
-CREATE TABLE MainDicomTags(
-       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       tagGroup INTEGER,
-       tagElement INTEGER,
-       value TEXT,
-       PRIMARY KEY(id, tagGroup, tagElement)
-       );
-
-CREATE TABLE DicomIdentifiers(
-       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       tagGroup INTEGER,
-       tagElement INTEGER,
-       value TEXT,
-       PRIMARY KEY(id, tagGroup, tagElement)
-       );
-
-CREATE TABLE Metadata(
-       id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       type INTEGER NOT NULL,
-       value TEXT,
-       PRIMARY KEY(id, type)
-       );
-
-CREATE TABLE 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),
-       PRIMARY KEY(id, fileType)
-       );              
-
-CREATE TABLE Changes(
-       seq BIGSERIAL NOT NULL PRIMARY KEY,
-       changeType INTEGER,
-       internalId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,
-       resourceType INTEGER,
-       date VARCHAR(64)
-       );
-
-CREATE TABLE 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 PatientRecyclingOrder(
-       seq BIGSERIAL NOT NULL PRIMARY KEY,
-       patientId BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE
-       );
-
-CREATE INDEX ChildrenIndex ON Resources(parentId);
-CREATE INDEX PublicIndex ON Resources(publicId);
-CREATE INDEX ResourceTypeIndex ON Resources(resourceType);
-CREATE INDEX PatientRecyclingIndex ON PatientRecyclingOrder(patientId);
-
-CREATE INDEX MainDicomTagsIndex ON MainDicomTags(id);
-CREATE INDEX DicomIdentifiersIndex1 ON DicomIdentifiers(id);
-CREATE INDEX DicomIdentifiersIndex2 ON DicomIdentifiers(tagGroup, tagElement);
-CREATE INDEX DicomIdentifiersIndexValues ON DicomIdentifiers(value);
-
-CREATE INDEX ChangesIndex ON Changes(internalId);
-
-
--- New tables wrt. Orthanc core
-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
-       );
--- End of differences
-
-
-CREATE 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();
-
-
--- previous version of the trigger, moved into ResourceDeletedFunc.sql
--- 
--- -- The following trigger combines 2 triggers from SQLite:
--- -- ResourceDeleted + ResourceDeletedParentCleaning
--- CREATE 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 
---     -- (a parent that must not be deleted but whose LastUpdate must be updated)
---     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;
-
--- CREATE TRIGGER ResourceDeleted
--- AFTER DELETE ON Resources
--- FOR EACH ROW
--- EXECUTE PROCEDURE ResourceDeletedFunc();
-
-
-
-CREATE 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;
-
-CREATE TRIGGER PatientAdded
-AFTER INSERT ON Resources
-FOR EACH ROW
-EXECUTE PROCEDURE PatientAddedFunc();
diff -r f790232b77ef -r 2bc65c678b1b PostgreSQL/Plugins/ResourceDeletedFunc.sql
--- a/PostgreSQL/Plugins/ResourceDeletedFunc.sql	Mon Dec 18 19:17:09 2023 +0100
+++ /dev/null	Thu Jan 01 00:00:00 1970 +0000
@@ -1,98 +0,0 @@
--- this script can be used either the first time we create the DB or during an upgrade
-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 'Delete resource %', old.parentId;
-  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();
-
--- we'll now use temporary tables so we need to remove the old tables !
-DROP TABLE IF EXISTS DeletedFiles;
-DROP TABLE IF EXISTS RemainingAncestor;
-DROP TABLE IF EXISTS DeletedResources;
-
-
-CREATE OR REPLACE FUNCTION DeleteResource(
-    IN id BIGINT,
-    OUT remainingAncestorResourceType INTEGER,
-    OUT remainingAncestorPublicId TEXT) AS $body$
-
-DECLARE
-    deleted_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();
-
-
-    -- 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 remainingAncestorResourceType, remainingAncestorPublicId
-        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;