# HG changeset patch
# User Alain Mazy <am@orthanc.team>
# Date 1727791368 -7200
# Node ID b0533b703c2c7c03aa45e4d7d100ab2219a9ebb0
# Parent  7453fc5bef1a1877cd4e04f6bccf78c78a9369dd# Parent  4c84d908e89123ef5976cd47442b02a0f0d8a433
merged find-refactoring -> attach-custom-data

diff -r 7453fc5bef1a -r b0533b703c2c PostgreSQL/Plugins/PostgreSQLIndex.cpp
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Thu Sep 26 08:55:00 2024 +0200
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Tue Oct 01 16:02:48 2024 +0200
@@ -43,6 +43,7 @@
   static const GlobalProperty GlobalProperty_HasCreateInstance = GlobalProperty_DatabaseInternal1;
   static const GlobalProperty GlobalProperty_HasFastCountResources = GlobalProperty_DatabaseInternal2;
   static const GlobalProperty GlobalProperty_GetLastChangeIndex = GlobalProperty_DatabaseInternal3;
+  static const GlobalProperty GlobalProperty_HasComputeStatisticsReadOnly = GlobalProperty_DatabaseInternal4;
 }
 
 
@@ -172,18 +173,19 @@
           }
 
           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;
-          }
+          // these extensions are not installed anymore from v6.0 of the plugin (but the plugin is fast to compute the size and count the resources)
+          // 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)
@@ -228,6 +230,15 @@
             // apply all idempotent changes that are in the PrepareIndex (update triggers + set Patch level to 3)
             ApplyPrepareIndex(t, manager);
           }
+
+          if (!LookupGlobalIntegerProperty(property, manager, MISSING_SERVER_IDENTIFIER,
+                                           Orthanc::GlobalProperty_HasComputeStatisticsReadOnly) ||
+              property != 1)
+          {
+            // apply all idempotent changes that are in the PrepareIndex.  In this case, we are just interested by
+            // ComputeStatisticsReadOnly() that does not need to be uninstalled in case of downgrade.
+            ApplyPrepareIndex(t, manager);
+          }
         }
 
         t.Commit();
@@ -259,17 +270,23 @@
 
   uint64_t PostgreSQLIndex::GetTotalCompressedSize(DatabaseManager& manager)
   {
-    // Fast version if extension "./FastTotalSize.sql" is installed
     uint64_t result;
 
     {
       DatabaseManager::CachedStatement statement(
         STATEMENT_FROM_HERE, manager,
-        "SELECT * FROM UpdateSingleStatistic(0)");
+        "SELECT * FROM ComputeStatisticsReadOnly(0)");
 
       statement.Execute();
 
-      result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      if (statement.IsNull(0))
+      {
+        return 0;
+      }
+      else
+      {
+        result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      }
     }
     
     // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION.  This is however true when no files are being delete/added
@@ -280,17 +297,23 @@
   
   uint64_t PostgreSQLIndex::GetTotalUncompressedSize(DatabaseManager& manager)
   {
-    // Fast version if extension "./FastTotalSize.sql" is installed
     uint64_t result;
 
     {
       DatabaseManager::CachedStatement statement(
         STATEMENT_FROM_HERE, manager,
-        "SELECT * FROM UpdateSingleStatistic(1)");
+        "SELECT * FROM ComputeStatisticsReadOnly(1)");
 
       statement.Execute();
 
-      result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      if (statement.IsNull(0))
+      {
+        return 0;
+      }
+      else
+      {
+        result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      }
     }
     
     // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION.  This is however true when no files are being delete/added
@@ -658,8 +681,6 @@
   uint64_t PostgreSQLIndex::GetResourcesCount(DatabaseManager& manager,
                                               OrthancPluginResourceType resourceType)
   {
-    // Optimized version thanks to the "FastCountResources.sql" extension
-
     assert(OrthancPluginResourceType_Patient == 0 &&
            OrthancPluginResourceType_Study == 1 &&
            OrthancPluginResourceType_Series == 2 &&
@@ -670,15 +691,22 @@
     {
       DatabaseManager::StandaloneStatement statement(
         manager,
-        std::string("SELECT * FROM UpdateSingleStatistic(") + boost::lexical_cast<std::string>(resourceType + 2) + ")");  // For an explanation of the "+ 2" below, check out "PrepareIndex.sql"
+        std::string("SELECT * FROM ComputeStatisticsReadOnly(") + boost::lexical_cast<std::string>(resourceType + 2) + ")");  // For an explanation of the "+ 2" below, check out "PrepareIndex.sql"
 
       statement.Execute();
 
-      result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      if (statement.IsNull(0))
+      {
+        return 0;
+      }
+      else
+      {
+        result = static_cast<uint64_t>(statement.ReadInteger64(0));
+      }
     }
       
     // disabled because this is not alway true while transactions are being executed in READ COMITTED TRANSACTION.  This is however true when no files are being delete/added
-    assert(result == IndexBackend::GetResourcesCount(manager, resourceType));
+    // assert(result == IndexBackend::GetResourcesCount(manager, resourceType));
 
     return result;
   }
diff -r 7453fc5bef1a -r b0533b703c2c PostgreSQL/Plugins/SQL/PrepareIndex.sql
--- a/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Thu Sep 26 08:55:00 2024 +0200
+++ b/PostgreSQL/Plugins/SQL/PrepareIndex.sql	Tue Oct 01 16:02:48 2024 +0200
@@ -549,7 +549,28 @@
         PERFORM PatientAddedOrUpdated(patient_internal_id, 1);
     END IF;  
 END;
+$body$ LANGUAGE plpgsql;
 
+-- function to compute a statistic in a ReadOnly transaction
+CREATE OR REPLACE FUNCTION ComputeStatisticsReadOnly(
+    IN statistics_key INTEGER,
+    OUT accumulated_value BIGINT
+) RETURNS BIGINT AS $body$
+
+DECLARE
+    current_value BIGINT;
+    
+BEGIN
+
+    SELECT VALUE FROM GlobalIntegers
+    INTO current_value
+    WHERE key = statistics_key;
+
+    SELECT COALESCE(SUM(value), 0) + current_value FROM GlobalIntegersChanges
+    INTO accumulated_value
+    WHERE key = statistics_key;
+
+END;
 $body$ LANGUAGE plpgsql;
 
 
@@ -563,3 +584,4 @@
 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
+INSERT INTO GlobalProperties VALUES (14, 1); -- GlobalProperty_HasComputeStatisticsReadOnly