--- a/NOTES	Fri Dec 15 17:11:26 2023 +0100
+++ b/NOTES	Mon Dec 18 18:50:01 2023 +0100
@@ -193,78 +193,16 @@
 
 
 TODO:
-- check RETURNS SET OF !
-  CREATE FUNCTION getfoo(int) RETURNS SETOF foo AS $$
-      SELECT * FROM foo WHERE fooid = $1;
-  $$ LANGUAGE SQL;
-- have a separate "thread" to increment/decrement statistics because everybody is fighting to modify the GlobalIntegers rows
-    ExecuteSetResourcesContentTags(manager, "DicomIdentifiers", "i",      -> make unique + handle on conflicts
-                                   countIdentifierTags, identifierTags);
-
-    ExecuteSetResourcesContentTags(manager, "MainDicomTags", "t",         -> make unique + handle on conflicts
-                                   countMainDicomTags, mainDicomTags);
-    
-    ExecuteSetResourcesContentMetadata(manager, HasRevisionsSupport(), countMetadata, metadata);  handle on conflicts
-
-CREATE OR REPLACE FUNCTION InsertOrReplaceMetadata(VARIADIC params SETOF metadata)
-RETURNS VOID AS $$
-BEGIN
-
-  FOR i IN 1..array_length(my_params, 1) LOOP
-    my_sum := my_sum + my_params[i].my_column;
-  END LOOP;
-  RETURN my_sum;
-END;
-$$ LANGUAGE plpgsql;
-
-#include <postgresql/libpq-fe.h>
-
-int main() {
-  PGconn *conn = PQconnectdb("dbname=mydb user=myuser password=mypassword");
-  if (PQstatus(conn) != CONNECTION_OK) {
-    std::cerr << "Connection to database failed: " << PQerrorMessage(conn) << std::endl;
-    PQfinish(conn);
-    return 1;
-  }
-
-  const char *function_name = "my_function";
-  int num_params = 3;
-  int params[] = {1, 2, 3};
-
-  char *query = PQescapeLiteral(conn, function_name, strlen(function_name));
-  for (int i = 0; i < num_params; i++) {
-    char *param = PQescapeLiteral(conn, (const char *)&params[i], sizeof(int));
-    query = (char *)realloc(query, strlen(query) + strlen(param) + 1);
-    strcat(query, ",");
-    strcat(query, param);
-    PQfreemem(param);
-  }
-  strcat(query, ")");
-
-  PGresult *res = PQexec(conn, query);
-  if (PQresultStatus(res) != PGRES_TUPLES_OK) {
-    std::cerr << "Function call failed: " << PQerrorMessage(conn) << std::endl;
-    PQfreemem(query);
-    PQclear(res);
-    PQfinish(conn);
-    return 1;
-  }
-
-  int result = atoi(PQgetvalue(res, 0, 0));
-  std::cout << "Result: " << result << std::endl;
-
-  PQfreemem(query);
-  PQclear(res);
-  PQfinish(conn);
-
-
+- reenable PatientRecyclingOrder
+- have a separate "thread" to UpdateStatistics ?
 - test events generation StableSeries ....
-- run tests with docker localy + in CI
 - check https://discourse.orthanc-server.org/t/image-insert-are-too-slow-databse-performance-too-poor-when-using-mysql-mariadb/3820
-- PatientAddedFunc contains an IF
 - validate upgrade DB from previous Orthanc and from scratch
+- check minimal version of PG (9.5 - 9.6 ? for create index if not exists)
+- implement a downgrade script ?
 - test with older version of PG
 - In Docker images, re-enable MySQL & ODBC plugins + tests
 
 DONE:
 - force the create/update DB transaction to be serializable (not needed: this is handled by POSTGRESQL_LOCK_DATABASE_SETUP)
+- PatientAddedFunc contains an IF  (check if other IF/THEN/ELSE pattern remains)

--- a/PostgreSQL/CMakeLists.txt	Fri Dec 15 17:11:26 2023 +0100
+++ b/PostgreSQL/CMakeLists.txt	Mon Dec 18 18:50:01 2023 +0100
@@ -77,14 +77,9 @@
 
 
 EmbedResources(
-  POSTGRESQL_PREPARE_INDEX          ${CMAKE_SOURCE_DIR}/Plugins/PrepareIndex.sql
-  POSTGRESQL_CREATE_INSTANCE        ${CMAKE_SOURCE_DIR}/Plugins/CreateInstance.sql
-  POSTGRESQL_FAST_TOTAL_SIZE        ${CMAKE_SOURCE_DIR}/Plugins/FastTotalSize.sql
-  POSTGRESQL_FAST_COUNT_RESOURCES   ${CMAKE_SOURCE_DIR}/Plugins/FastCountResources.sql
-  POSTGRESQL_GET_LAST_CHANGE_INDEX  ${CMAKE_SOURCE_DIR}/Plugins/GetLastChangeIndex.sql
-  POSTGRESQL_RESOURCE_DELETED_FUNC  ${CMAKE_SOURCE_DIR}/Plugins/ResourceDeletedFunc.sql
-  POSTGRESQL_INSERT_UPDATE_METADATA ${CMAKE_SOURCE_DIR}/Plugins/InsertOrUpdateMetadata.sql
-  POSTGRESQL_FAST_TOTAL_STATS_2     ${CMAKE_SOURCE_DIR}/Plugins/FastTotalStats2.sql
+  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
   )
 
 

--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Fri Dec 15 17:11:26 2023 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Mon Dec 18 18:50:01 2023 +0100
@@ -61,6 +61,22 @@
     return PostgreSQLDatabase::CreateDatabaseFactory(parameters_);
   }
 
+  void PostgreSQLIndex::ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager)
+  {
+    std::string query;
+
+    Orthanc::EmbeddedResources::GetFileResource
+      (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX_V2);
+    t.GetDatabaseTransaction().ExecuteMultiLines(query);
+
+    SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion, 6);
+    SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 2);
+    SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 3);  // this is the 3rd version !
+    SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 1);
+    SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1);
+    SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasFastCountResources, 1);
+    SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1);
+  }
   
   void PostgreSQLIndex::ConfigureDatabase(DatabaseManager& manager,
                                           bool hasIdentifierTags,
@@ -77,7 +93,7 @@
     if (expectedVersion != 6)
     {
       LOG(ERROR) << "This database plugin is incompatible with your version of Orthanc "
-                 << "expecting the DB schema version " << expectedVersion 
+                 << "expecting the Orthanc DB schema version " << expectedVersion 
                  << ", but this plugin is only compatible with version 6";
       throw Orthanc::OrthancException(Orthanc::ErrorCode_Plugin);
     }
@@ -90,8 +106,8 @@
     }
 
     {
+      // lock the full DB while checking if it needs to be create/ugraded
       PostgreSQLDatabase::TransientAdvisoryLock lock(db, POSTGRESQL_LOCK_DATABASE_SETUP);
-      bool shouldInstallFastTotalStats2 = false;
 
       if (clearAll_)
       {
@@ -103,262 +119,98 @@
 
         if (!t.GetDatabaseTransaction().DoesTableExist("Resources"))
         {
-          std::string query;
-
-          Orthanc::EmbeddedResources::GetFileResource
-            (query, Orthanc::EmbeddedResources::POSTGRESQL_PREPARE_INDEX);
-          t.GetDatabaseTransaction().ExecuteMultiLines(query);
-
-          SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion, expectedVersion);
-          SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, 1);
-          SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 0);
-        }
-          
-        if (!t.GetDatabaseTransaction().DoesTableExist("Resources"))
-        {
-          LOG(ERROR) << "Corrupted PostgreSQL database";
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);        
-        }
-
-        int version = 0;
-        if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) ||
-            version != 6)
-        {
-          LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema version: " << version;
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);        
-        }
+          LOG(WARNING) << "PostgreSQL is creating the database schema";
 
-        int revision;
-        if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel))
-        {
-          revision = 1;
-          SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
-        }
-
-        if (revision == 1)
-        {
-          {
-            LOG(WARNING) << "PostgreSQL plugin: adding UNIQUE(publicId) constraint to the 'Resources' table ";
-            t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Resources ADD UNIQUE (publicId);");
-          }
-
-          {
-            LOG(WARNING) << "PostgreSQL plugin: adding or replacing ResourceDeletedFunc";
-
-            std::string query;
-            Orthanc::EmbeddedResources::GetFileResource
-              (query, Orthanc::EmbeddedResources::POSTGRESQL_RESOURCE_DELETED_FUNC);
-            t.GetDatabaseTransaction().ExecuteMultiLines(query);
-          }
-
-          {
-            LOG(WARNING) << "PostgreSQL plugin: adding or replacing InsertOrUpdateMetadata";
+          ApplyPrepareIndex(t, manager);
 
-            std::string query;
-            Orthanc::EmbeddedResources::GetFileResource
-              (query, Orthanc::EmbeddedResources::POSTGRESQL_INSERT_UPDATE_METADATA);
-            t.GetDatabaseTransaction().ExecuteMultiLines(query);
-          }
-
-          shouldInstallFastTotalStats2 = true;
-
-          revision = 2;
-          SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel, revision);
-        }
-
-        if (revision != 2)
-        {
-          LOG(ERROR) << "PostgreSQL plugin is incompatible with database schema revision: " << revision;
-          throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);        
-        }
-
-        t.Commit();
-      }
-
-      {
-        DatabaseManager::Transaction t(manager, TransactionType_ReadWrite);
-
-        int hasTrigram = 0;
-        if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER,
-                                         Orthanc::GlobalProperty_HasTrigramIndex) ||
-            hasTrigram != 1)
-        {
-          /**
-           * 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
-           **/
-          try
+          if (!t.GetDatabaseTransaction().DoesTableExist("Resources"))
           {
-            // We've observed 9 minutes on DB with 100000 studies
-            LOG(WARNING) << "Trying to enable trigram matching on the PostgreSQL database "
-                         << "to speed up wildcard searches. This may take several minutes";
-
-            t.GetDatabaseTransaction().ExecuteMultiLines(
-              "CREATE EXTENSION IF NOT EXISTS pg_trgm; "
-              "CREATE INDEX DicomIdentifiersIndexValues2 ON DicomIdentifiers USING gin(value gin_trgm_ops);");
-
-            SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasTrigramIndex, 1);
-            LOG(WARNING) << "Trigram index has been created";
-
-            t.Commit();
-          }
-          catch (Orthanc::OrthancException&)
-          {
-            LOG(WARNING) << "Performance warning: Your PostgreSQL server does "
-                         << "not support trigram matching";
-            LOG(WARNING) << "-> Consider installing the \"pg_trgm\" extension on the "
-                         << "PostgreSQL server, e.g. on Debian: sudo apt install postgresql-contrib";
+            LOG(ERROR) << "Corrupted PostgreSQL database or failed to create the database schema";
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_InternalError);        
           }
         }
         else
         {
-          t.Commit();
-        }
-      }
-
-      {
-        DatabaseManager::Transaction t(manager, TransactionType_ReadWrite);
+          LOG(WARNING) << "The database schema already exists, checking if it needs to be updated";
 
-        int property = 0;
-        if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
-                                         Orthanc::GlobalProperty_HasCreateInstance) ||
-            property != 3)
-        {
-          LOG(INFO) << "Installing the CreateInstance extension";
-
-          if (property == 1)
+          int version = 0;
+          if (!LookupGlobalIntegerProperty(version, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabaseSchemaVersion) ||
+              version != 6)
           {
-            // Drop older, experimental versions of this extension
-            t.GetDatabaseTransaction().ExecuteMultiLines("DROP FUNCTION CreateInstance("
-                                                         "IN patient TEXT, IN study TEXT, IN series TEXT, in instance TEXT)");
+            LOG(ERROR) << "PostgreSQL plugin is incompatible with Orthanc database schema version: " << version;
+            throw Orthanc::OrthancException(Orthanc::ErrorCode_Database);        
           }
-        
-          // property == 2 was a first version of the CreateInstance -> we need to replace it by the new one
-          // property == 3 is a new version (in v5.2) with same signature and CREATE OR UPDATE 
-          //  -> we can replace the previous one without deleting it
-          //     and we can create it if it has never been created.
-          std::string query;
-          Orthanc::EmbeddedResources::GetFileResource
-            (query, Orthanc::EmbeddedResources::POSTGRESQL_CREATE_INSTANCE);
-          t.GetDatabaseTransaction().ExecuteMultiLines(query);
 
-          SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasCreateInstance, 3);
-        }
+          bool needToRunUpgradeFromUnknownToV1 = false;
+          bool needToRunUpgradeV1toV2 = false;
 
-        if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
-                                         Orthanc::GlobalProperty_GetTotalSizeIsFast) ||
-            property != 1)
-        {
-          LOG(INFO) << "Installing the FastTotalSize extension";
-
-          std::string query;
-          Orthanc::EmbeddedResources::GetFileResource
-            (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_SIZE);
-          t.GetDatabaseTransaction().ExecuteMultiLines(query);
-
-          SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetTotalSizeIsFast, 1);
-        }
-
+          int revision;
+          if (!LookupGlobalIntegerProperty(revision, manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_DatabasePatchLevel))
+          {
+            LOG(WARNING) << "No DatabasePatchLevel found, assuming it's 1";
+            revision = 1;
+            needToRunUpgradeFromUnknownToV1 = true;
+            needToRunUpgradeV1toV2 = true;
+          }
+          else if (revision == 1)
+          {
+            needToRunUpgradeV1toV2 = true;
+          }
 
-        // Installing this extension requires the "GlobalIntegers" table
-        // created by the "FastTotalSize" extension
-        property = 0;
-        if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
-                                         Orthanc::GlobalProperty_HasFastCountResources) ||
-            property != 1)
-        {
-          LOG(INFO) << "Installing the FastCountResources extension";
-
-          std::string query;
-          Orthanc::EmbeddedResources::GetFileResource
-            (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES);
-          t.GetDatabaseTransaction().ExecuteMultiLines(query);
-
-          SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_HasFastCountResources, 1);
-        }
-
+          int hasTrigram = 0;
+          if (!LookupGlobalIntegerProperty(hasTrigram, manager, MISSING_SERVER_IDENTIFIER,
+                                           Orthanc::GlobalProperty_HasTrigramIndex) || 
+              hasTrigram != 1)
+          {
+            // We've observed 9 minutes on DB with 100000 studies
+            LOG(WARNING) << "The DB schema update will try to enable trigram matching on the PostgreSQL database "
+                         << "to speed up wildcard searches. This may take several minutes";
+            needToRunUpgradeV1toV2 = true;
+          }
 
-        // Installing this extension requires the "GlobalIntegers" table
-        // created by the "GetLastChangeIndex" extension
-        property = 0;
-        if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
-                                         Orthanc::GlobalProperty_GetLastChangeIndex) ||
-            property != 1)
-        {
-          LOG(INFO) << "Installing the GetLastChangeIndex extension";
-
-          std::string query;
-          Orthanc::EmbeddedResources::GetFileResource
-            (query, Orthanc::EmbeddedResources::POSTGRESQL_GET_LAST_CHANGE_INDEX);
-          t.GetDatabaseTransaction().ExecuteMultiLines(query);
-
-          SetGlobalIntegerProperty(manager, MISSING_SERVER_IDENTIFIER, Orthanc::GlobalProperty_GetLastChangeIndex, 1);
-        }
-
-        if (shouldInstallFastTotalStats2)
-        {
-          LOG(WARNING) << "PostgreSQL plugin: installing FastTotalStats2 to replace FastTotalSize and FastCountResources";
-
-          std::string query;
-          Orthanc::EmbeddedResources::GetFileResource
-            (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_TOTAL_STATS_2);
-          t.GetDatabaseTransaction().ExecuteMultiLines(query);
-        }
-
-        t.Commit();
-      }
- 
-
-      {
-        // New in release 4.0 to deal with multiple writers
-        DatabaseManager::Transaction t(manager, TransactionType_ReadWrite);
+          int property = 0;
+          if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
+                                           Orthanc::GlobalProperty_HasFastCountResources) ||
+              property != 1)
+          {
+            needToRunUpgradeV1toV2 = true;
+          }
+          if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
+                                          Orthanc::GlobalProperty_GetTotalSizeIsFast) ||
+              property != 1)
+          {
+            needToRunUpgradeV1toV2 = true;
+          }
+          if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
+                                          Orthanc::GlobalProperty_GetLastChangeIndex) ||
+              property != 1)
+          {
+            needToRunUpgradeV1toV2 = true;
+          }
 
-        if (!t.GetDatabaseTransaction().DoesTableExist("ServerProperties"))
-        {
-          t.GetDatabaseTransaction().ExecuteMultiLines("CREATE TABLE ServerProperties(server VARCHAR(64) NOT NULL, "
-                                                       "property INTEGER, value TEXT, PRIMARY KEY(server, property))");
-        }
-
-        /**
-         * PostgreSQL 9.5: "Adding a column with a default requires
-         * updating each row of the table (to store the new column
-         * value). However, if no default is specified, PostgreSQL is
-         * able to avoid the physical update." => We set no default
-         * for performance (older entries will be NULL)
-         * https://www.postgresql.org/docs/9.5/ddl-alter.html
-         **/
-        if (!db.DoesColumnExist("Metadata", "revision"))
-        {
-          t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE Metadata ADD COLUMN revision INTEGER");
-        }
+          if (needToRunUpgradeFromUnknownToV1)
+          {
+            LOG(WARNING) << "Upgrading DB schema from unknown to revision 1";
+            std::string query;
 
-        if (!db.DoesColumnExist("AttachedFiles", "revision"))
-        {
-          t.GetDatabaseTransaction().ExecuteMultiLines("ALTER TABLE AttachedFiles ADD COLUMN revision INTEGER");
-        }
-
-        t.Commit();
-      }
- 
+            Orthanc::EmbeddedResources::GetFileResource
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_UNKNOWN_TO_V1);
+            t.GetDatabaseTransaction().ExecuteMultiLines(query);
+          }
+          
+          if (needToRunUpgradeV1toV2)
+          {
+            LOG(WARNING) << "Upgrading DB schema from revision 1 to revision 2";
 
-      {
-        // New in release 5.0 to deal with labels
-        DatabaseManager::Transaction t(manager, TransactionType_ReadWrite);
+            std::string query;
 
-        if (!t.GetDatabaseTransaction().DoesTableExist("Labels"))
-        {
-          t.GetDatabaseTransaction().ExecuteMultiLines(
-            "CREATE TABLE Labels("
-            "id BIGINT REFERENCES Resources(internalId) ON DELETE CASCADE,"
-            "label TEXT, PRIMARY KEY(id, label));"
-            "CREATE INDEX LabelsIndex1 ON LABELS(id);"
-            "CREATE INDEX LabelsIndex2 ON LABELS(label);");
+            Orthanc::EmbeddedResources::GetFileResource
+              (query, Orthanc::EmbeddedResources::POSTGRESQL_UPGRADE_V1_TO_V2);
+            t.GetDatabaseTransaction().ExecuteMultiLines(query);
+
+            // apply all idempotent changes that are in the PrepareIndexV2
+            ApplyPrepareIndex(t, manager);
+          }
         }
 
         t.Commit();

--- a/PostgreSQL/Plugins/PostgreSQLIndex.h	Fri Dec 15 17:11:26 2023 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.h	Mon Dec 18 18:50:01 2023 +0100
@@ -40,6 +40,7 @@
 
     virtual void ClearRemainingAncestor(DatabaseManager& manager);
 
+    void ApplyPrepareIndex(DatabaseManager::Transaction& t, DatabaseManager& manager);
 
   public:
     PostgreSQLIndex(OrthancPluginContext* context,

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/PrepareIndexV2.sql	Mon Dec 18 18:50:01 2023 +0100
@@ -0,0 +1,548 @@
+-- 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 patient 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);
+                -- ON CONFLICT (patientId) DO UPDATE SET seq = newSeq;
+        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);
+    -- UPDATE GlobalIntegers WHERE key = 7 SET value = value + 1 RETURNING value 
+    -- INSERT INTO PatientRecyclingOrder VALUES ((SELECT value FROM GlobalIntegers WHERE key = 7), new.internalId) 
+    --     ON CONFLICT ;
+  END IF;
+  RETURN NULL;
+END;
+$body$ LANGUAGE plpgsql;
+
+CREATE TRIGGER PatientAdded
+AFTER INSERT ON Resources
+FOR EACH ROW
+EXECUTE PROCEDURE PatientAddedFunc();
+
+-- initial value for 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;
+
+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 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 -------------------
+
+-- initialize values if not already theere
+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 DO NOTHING;
+	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$
+
+DECLARE
+  patientSeq BIGINT;
+  countRecycling BIGINT;
+
+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);
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_patient := 0;
+    END;
+    SELECT internalid INTO patient_internal_id FROM "resources" WHERE publicId = patient_public_id AND resourcetype = 0;
+
+	BEGIN
+        INSERT INTO "resources" VALUES (DEFAULT, 1, study_public_id, patient_internal_id);
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_study := 0;
+    END;
+    SELECT internalid INTO study_internal_id FROM "resources" WHERE publicId = study_public_id AND resourcetype = 1;
+
+	BEGIN
+	    INSERT INTO "resources" VALUES (DEFAULT, 2, series_public_id, study_internal_id);
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_series := 0;
+    END;
+	SELECT internalid INTO series_internal_id FROM "resources" WHERE publicId = series_public_id AND resourcetype = 2;
+
+  	BEGIN
+		INSERT INTO "resources" VALUES (DEFAULT, 3, instance_public_id, series_internal_id);
+    EXCEPTION
+        WHEN unique_violation THEN
+            is_new_instance := 0;
+    END;
+    SELECT internalid INTO instance_internal_id FROM "resources" WHERE publicId = instance_public_id AND resourcetype = 3;   
+
+    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;

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/UnknownToV1.sql	Mon Dec 18 18:50:01 2023 +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$;
+

--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/SQL/Upgrades/V1ToV2.sql	Mon Dec 18 18:50:01 2023 +0100
@@ -0,0 +1,45 @@
+-- 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;
+