Move DB queries related to statistics in a separate module

2 files changed, 55 insertions, 4 deletions

diff --git a/src/invidious/database/statistics.cr b/src/invidious/database/statistics.cr
new file mode 100644
index 00000000..1df549e2
--- /dev/null
+++ b/src/invidious/database/statistics.cr
@@ -0,0 +1,49 @@
+require "./base.cr"
+
+module Invidious::Database::Statistics
+  extend self
+
+  # -------------------
+  #  User stats
+  # -------------------
+
+  def count_users_total : Int64
+    request = <<-SQL
+      SELECT count(*) FROM users
+    SQL
+
+    PG_DB.query_one(request, as: Int64)
+  end
+
+  def count_users_active_1m : Int64
+    request = <<-SQL
+      SELECT count(*) FROM users
+      WHERE CURRENT_TIMESTAMP - updated < '6 months'
+    SQL
+
+    PG_DB.query_one(request, as: Int64)
+  end
+
+  def count_users_active_6m : Int64
+    request = <<-SQL
+      SELECT count(*) FROM users
+      WHERE CURRENT_TIMESTAMP - updated < '1 month'
+    SQL
+
+    PG_DB.query_one(request, as: Int64)
+  end
+
+  # -------------------
+  #  Channel stats
+  # -------------------
+
+  def channel_last_update : Time?
+    request = <<-SQL
+      SELECT updated FROM channels
+      ORDER BY updated DESC
+      LIMIT 1
+    SQL
+
+    PG_DB.query_one?(request, as: Time)
+  end
+end
diff --git a/src/invidious/jobs/statistics_refresh_job.cr b/src/invidious/jobs/statistics_refresh_job.cr
index 6569c0a1..a113bd77 100644
--- a/src/invidious/jobs/statistics_refresh_job.cr
+++ b/src/invidious/jobs/statistics_refresh_job.cr
@@ -47,12 +47,14 @@ class Invidious::Jobs::StatisticsRefreshJob < Invidious::Jobs::BaseJob
 
   private def refresh_stats
     users = STATISTICS.dig("usage", "users").as(Hash(String, Int64))
-    users["total"] = db.query_one("SELECT count(*) FROM users", as: Int64)
-    users["activeHalfyear"] = db.query_one("SELECT count(*) FROM users WHERE CURRENT_TIMESTAMP - updated < '6 months'", as: Int64)
-    users["activeMonth"] = db.query_one("SELECT count(*) FROM users WHERE CURRENT_TIMESTAMP - updated < '1 month'", as: Int64)
+
+    users["total"] = Invidious::Database::Statistics.count_users_total
+    users["activeHalfyear"] = Invidious::Database::Statistics.count_users_active_1m
+    users["activeMonth"] = Invidious::Database::Statistics.count_users_active_6m
+
     STATISTICS["metadata"] = {
       "updatedAt"              => Time.utc.to_unix,
-      "lastChannelRefreshedAt" => db.query_one?("SELECT updated FROM channels ORDER BY updated DESC LIMIT 1", as: Time).try &.to_unix || 0_i64,
+      "lastChannelRefreshedAt" => Invidious::Database::Statistics.channel_last_update.try &.to_unix || 0_i64,
     }
   end
 end