First

1 files changed, 45 insertions, 0 deletions

diff --git a/priv/repo/migrations/20190303120636_update_user_note_counters.exs b/priv/repo/migrations/20190303120636_update_user_note_counters.exs
new file mode 100644
index 0000000..f0a950d
--- /dev/null
+++ b/priv/repo/migrations/20190303120636_update_user_note_counters.exs
@@ -0,0 +1,45 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.UpdateUserNoteCounters do
+  use Ecto.Migration
+
+  @public "https://www.w3.org/ns/activitystreams#Public"
+
+  def up do
+    execute("""
+      WITH public_note_count AS (
+        SELECT
+          data->>'actor' AS actor,
+          count(id) AS count
+        FROM objects
+        WHERE data->>'type' = 'Note' AND (
+          data->'cc' ? '#{@public}' OR data->'to' ? '#{@public}'
+        )
+        GROUP BY data->>'actor'
+      )
+      UPDATE users AS u
+      SET "info" = jsonb_set(u.info, '{note_count}', o.count::varchar::jsonb, true)
+      FROM public_note_count AS o
+      WHERE u.ap_id = o.actor
+    """)
+  end
+
+  def down do
+    execute("""
+      WITH public_note_count AS (
+        SELECT
+          data->>'actor' AS actor,
+          count(id) AS count
+        FROM objects
+        WHERE data->>'type' = 'Note'
+        GROUP BY data->>'actor'
+      )
+      UPDATE users AS u
+      SET "info" = jsonb_set(u.info, '{note_count}', o.count::varchar::jsonb, true)
+      FROM public_note_count AS o
+      WHERE u.ap_id = o.actor
+    """)
+  end
+end