Merge branch 'main' into glitch-soc/merge-upstream

Conflicts:
- `README.md`:
  Upstream added a link to the roadmap, but we have a completely different README.
  Kept ours.
- `app/models/media_attachment.rb`:
  Upstream upped media attachment limits.
  Updated the default according to upstream's.
- `db/migrate/20180831171112_create_bookmarks.rb`:
  Upstream changed the migration compatibility level.
  Did so too.
- `config/initializers/content_security_policy.rb`:
  Upstream refactored this file but we have a different version.
  Kept our version.
- `app/controllers/settings/preferences_controller.rb`:
  Upstream completely refactored user settings storage, and glitch-soc has a
  different set of settings.
  The file does not directly references individual settings anymore.
  Applied upstream changes.
- `app/lib/user_settings_decorator.rb`:
  Upstream completely refactored user settings storage, and glitch-soc has a
  different set of settings.
  The file got removed entirely.
  Removed it as well.
- `app/models/user.rb`:
  Upstream completely refactored user settings storage, and glitch-soc has a
  different set of settings.
  References to individual settings have been removed from the file.
  Removed them as well.
- `app/views/settings/preferences/appearance/show.html.haml`:
  Upstream completely refactored user settings storage, and glitch-soc has a
  different set of settings.
  Applied upstream's changes and ported ours back.
- `app/views/settings/preferences/notifications/show.html.haml`:
  Upstream completely refactored user settings storage, and glitch-soc has a
  different set of settings.
  Applied upstream's changes and ported ours back.
- `app/views/settings/preferences/other/show.html.haml`:
  Upstream completely refactored user settings storage, and glitch-soc has a
  different set of settings.
  Applied upstream's changes and ported ours back.
- `config/settings.yml`:
  Upstream completely refactored user settings storage, and glitch-soc has a
  different set of settings.
  In particular, upstream removed user-specific and unused settings.
  Did the same in glitch-soc.
- `spec/controllers/application_controller_spec.rb`:
  Conflicts due to glitch-soc's theming system.
  Mostly kept our version, as upstream messed up the tests.

1 files changed, 33 insertions, 23 deletions

diff --git a/app/lib/admin/metrics/retention.rb b/app/lib/admin/metrics/retention.rb
index f6135ac1e..9bd47c58e 100644
--- a/app/lib/admin/metrics/retention.rb
+++ b/app/lib/admin/metrics/retention.rb
@@ -42,25 +42,54 @@ class Admin::Metrics::Retention
   end
 
   def perform_query
-    sql = <<-SQL.squish
+    report_rows.each_with_object([]) do |row, arr|
+      current_cohort = arr.last
+
+      if current_cohort.nil? || current_cohort.period != row['cohort_period']
+        current_cohort = Cohort.new(period: row['cohort_period'], frequency: @frequency, data: [])
+        arr << current_cohort
+      end
+
+      value, rate = row['retention_value_and_rate'].delete('{}').split(',')
+
+      current_cohort.data << CohortData.new(
+        date: row['retention_period'],
+        rate: rate.to_f,
+        value: value.to_s
+      )
+    end
+  end
+
+  def report_rows
+    ActiveRecord::Base.connection.select_all(sanitized_sql_string)
+  end
+
+  def sanitized_sql_string
+    ActiveRecord::Base.sanitize_sql_array(
+      [sql_query_string, { start_at: @start_at, end_at: @end_at, frequency: @frequency }]
+    )
+  end
+
+  def sql_query_string
+    <<~SQL.squish
       SELECT axis.*, (
         WITH new_users AS (
           SELECT users.id
           FROM users
-          WHERE date_trunc($3, users.created_at)::date = axis.cohort_period
+          WHERE date_trunc(:frequency, users.created_at)::date = axis.cohort_period
         ),
         retained_users AS (
           SELECT users.id
           FROM users
           INNER JOIN new_users on new_users.id = users.id
-          WHERE date_trunc($3, users.current_sign_in_at) >= axis.retention_period
+          WHERE date_trunc(:frequency, users.current_sign_in_at) >= axis.retention_period
         )
         SELECT ARRAY[count(*), (count(*))::float / (SELECT GREATEST(count(*), 1) FROM new_users)] AS retention_value_and_rate
         FROM retained_users
       )
       FROM (
         WITH cohort_periods AS (
-          SELECT generate_series(date_trunc($3, $1::timestamp)::date, date_trunc($3, $2::timestamp)::date, ('1 ' || $3)::interval) AS cohort_period
+          SELECT generate_series(date_trunc(:frequency, :start_at::timestamp)::date, date_trunc(:frequency, :end_at::timestamp)::date, ('1 ' || :frequency)::interval) AS cohort_period
         ),
         retention_periods AS (
           SELECT cohort_period AS retention_period FROM cohort_periods
@@ -70,24 +99,5 @@ class Admin::Metrics::Retention
         WHERE retention_period >= cohort_period
       ) as axis
     SQL
-
-    rows = ActiveRecord::Base.connection.select_all(sql, nil, [[nil, @start_at], [nil, @end_at], [nil, @frequency]])
-
-    rows.each_with_object([]) do |row, arr|
-      current_cohort = arr.last
-
-      if current_cohort.nil? || current_cohort.period != row['cohort_period']
-        current_cohort = Cohort.new(period: row['cohort_period'], frequency: @frequency, data: [])
-        arr << current_cohort
-      end
-
-      value, rate = row['retention_value_and_rate'].delete('{}').split(',')
-
-      current_cohort.data << CohortData.new(
-        date: row['retention_period'],
-        rate: rate.to_f,
-        value: value.to_s
-      )
-    end
   end
 end