# HG changeset patch
# User Sebastien Jodogne <s.jodogne@gmail.com>
# Date 1546954661 -3600
# Node ID cb0aac9bbada783e24bdc1ed5a82d2fdd2e2fd88
# Parent  2ee166f77501b1ff9c2320ee8ced8cf7f0fce337
optimization for /statistics URI

diff -r 2ee166f77501 -r cb0aac9bbada PostgreSQL/CMakeLists.txt
--- a/PostgreSQL/CMakeLists.txt	Sun Jan 06 12:48:18 2019 +0100
+++ b/PostgreSQL/CMakeLists.txt	Tue Jan 08 14:37:41 2019 +0100
@@ -52,9 +52,10 @@
 
 
 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_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
   )
 
 add_library(OrthancPostgreSQLIndex SHARED
diff -r 2ee166f77501 -r cb0aac9bbada PostgreSQL/Plugins/FastCountResources.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/PostgreSQL/Plugins/FastCountResources.sql	Tue Jan 08 14:37:41 2019 +0100
@@ -0,0 +1,33 @@
+-- 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 2ee166f77501 -r cb0aac9bbada PostgreSQL/Plugins/PostgreSQLIndex.cpp
--- a/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Sun Jan 06 12:48:18 2019 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.cpp	Tue Jan 08 14:37:41 2019 +0100
@@ -36,6 +36,7 @@
   // Some aliases for internal properties
   static const GlobalProperty GlobalProperty_HasTrigramIndex = GlobalProperty_DatabaseInternal0;
   static const GlobalProperty GlobalProperty_HasCreateInstance = GlobalProperty_DatabaseInternal1;
+  static const GlobalProperty GlobalProperty_HasFastCountResources = GlobalProperty_DatabaseInternal2;
 }
 
 
@@ -212,6 +213,29 @@
       t.Commit();
     }
 
+    {
+      PostgreSQLTransaction t(*db);
+
+      // Installing this extension requires the "GlobalIntegers" table
+      // created by the "FastTotalSize" extension
+      int property = 0;
+      if (!LookupGlobalIntegerProperty(property, *db, t,
+                                       Orthanc::GlobalProperty_HasFastCountResources) ||
+          property != 1)
+      {
+        LOG(INFO) << "Installing the FastCountResources extension";
+
+        std::string query;
+        Orthanc::EmbeddedResources::GetFileResource
+          (query, Orthanc::EmbeddedResources::POSTGRESQL_FAST_COUNT_RESOURCES);
+        db->Execute(query);
+
+        SetGlobalIntegerProperty(*db, t, Orthanc::GlobalProperty_HasFastCountResources, 1);
+      }
+
+      t.Commit();
+    }
+
     return db.release();
   }
 
@@ -248,28 +272,42 @@
   uint64_t PostgreSQLIndex::GetTotalCompressedSize()
   {
     // Fast version if extension "./FastTotalSize.sql" is installed
-    DatabaseManager::CachedStatement statement(
-      STATEMENT_FROM_HERE, GetManager(),
-      "SELECT value FROM GlobalIntegers WHERE key = 0");
+    uint64_t result;
+
+    {
+      DatabaseManager::CachedStatement statement(
+        STATEMENT_FROM_HERE, GetManager(),
+        "SELECT value FROM GlobalIntegers WHERE key = 0");
 
-    statement.SetReadOnly(true);
-    statement.Execute();
+      statement.SetReadOnly(true);
+      statement.Execute();
 
-    return static_cast<uint64_t>(ReadInteger64(statement, 0));
+      result = static_cast<uint64_t>(ReadInteger64(statement, 0));
+    }
+    
+    assert(result == IndexBackend::GetTotalCompressedSize());
+    return result;
   }
 
   
   uint64_t PostgreSQLIndex::GetTotalUncompressedSize()
   {
     // Fast version if extension "./FastTotalSize.sql" is installed
-    DatabaseManager::CachedStatement statement(
-      STATEMENT_FROM_HERE, GetManager(),
-      "SELECT value FROM GlobalIntegers WHERE key = 1");
+    uint64_t result;
+
+    {
+      DatabaseManager::CachedStatement statement(
+        STATEMENT_FROM_HERE, GetManager(),
+        "SELECT value FROM GlobalIntegers WHERE key = 1");
 
-    statement.SetReadOnly(true);
-    statement.Execute();
+      statement.SetReadOnly(true);
+      statement.Execute();
 
-    return static_cast<uint64_t>(ReadInteger64(statement, 0));
+      result = static_cast<uint64_t>(ReadInteger64(statement, 0));
+    }
+    
+    assert(result == IndexBackend::GetTotalUncompressedSize());
+    return result;
   }
 
 
@@ -322,4 +360,38 @@
     }
   }
 #endif
+
+
+  uint64_t PostgreSQLIndex::GetResourceCount(OrthancPluginResourceType resourceType)
+  {
+    // Optimized version thanks to the "FastCountResources.sql" extension
+
+    assert(OrthancPluginResourceType_Patient == 0 &&
+           OrthancPluginResourceType_Study == 1 &&
+           OrthancPluginResourceType_Series == 2 &&
+           OrthancPluginResourceType_Instance == 3);
+
+    uint64_t result;
+    
+    {
+      DatabaseManager::CachedStatement statement(
+        STATEMENT_FROM_HERE, GetManager(),
+        "SELECT value FROM GlobalIntegers WHERE key = ${key}");
+
+      statement.SetParameterType("key", ValueType_Integer64);
+
+      Dictionary args;
+
+      // For an explanation of the "+ 2" below, check out "FastCountResources.sql"
+      args.SetIntegerValue("key", static_cast<int>(resourceType + 2));
+
+      statement.SetReadOnly(true);
+      statement.Execute(args);
+
+      result = static_cast<uint64_t>(ReadInteger64(statement, 0));
+    }
+      
+    assert(result == IndexBackend::GetResourceCount(resourceType));
+    return result;
+  }
 }
diff -r 2ee166f77501 -r cb0aac9bbada PostgreSQL/Plugins/PostgreSQLIndex.h
--- a/PostgreSQL/Plugins/PostgreSQLIndex.h	Sun Jan 06 12:48:18 2019 +0100
+++ b/PostgreSQL/Plugins/PostgreSQLIndex.h	Tue Jan 08 14:37:41 2019 +0100
@@ -91,5 +91,8 @@
                                 const char* hashInstance)
       ORTHANC_OVERRIDE;
 #endif
+
+    virtual uint64_t GetResourceCount(OrthancPluginResourceType resourceType)
+      ORTHANC_OVERRIDE;
   };
 }