--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Downgrades/Rev3ToRev2.sql	Wed Sep 25 09:25:58 2024 +0200
@@ -0,0 +1,56 @@
+-- This file contains an SQL procedure to downgrade from schema Rev3 to Rev2 (version = 6).
+-- It removes the column that has been added in Rev3
+
+-- these constraints were introduced in Rev3
+ALTER TABLE AttachedFiles DROP COLUMN customData;
+
+-- reinstall previous triggers
+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;
+
+DROP TRIGGER IF EXISTS AttachedFileDeleted on AttachedFiles;
+CREATE TRIGGER AttachedFileDeleted
+AFTER DELETE ON AttachedFiles
+FOR EACH ROW
+EXECUTE PROCEDURE AttachedFileDeletedFunc();
+
+
+
+DELETE FROM GlobalProperties WHERE property IN (4);
+INSERT INTO GlobalProperties VALUES (4, 2); -- GlobalProperty_DatabasePatchLevel
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/Rev2ToRev3.sql	Wed Sep 25 09:25:58 2024 +0200
@@ -0,0 +1,19 @@
+-- This file contains part of the changes required to upgrade from Revision 2 to Revision 3 (DB version 6)
+-- 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 2 and it is executed 
+-- before PrepareIndex.sql that is idempotent.
+
+
+
+DO $body$
+BEGIN
+
+    BEGIN
+        ALTER TABLE AttachedFiles ADD COLUMN customData TEXT;
+    EXCEPTION
+        WHEN duplicate_column THEN RAISE NOTICE 'column customData already exists in AttachedFiles.';
+    END;
+
+END $body$ LANGUAGE plpgsql;