Git of dcc - anni/blob - priv/repo/migrations/20191008132217_migrate_following_relationships.exs

   1 # Pleroma: A lightweight social networking server
   2 # Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
   3 # SPDX-License-Identifier: AGPL-3.0-only
   4
   5 defmodule Pleroma.Repo.Migrations.MigrateFollowingRelationships do
   6   use Ecto.Migration
   7
   8   def change do
   9     execute(import_following_from_users(), "")
  10     execute(import_following_from_activities(), restore_following_column())
  11   end
  12
  13   defp import_following_from_users do
  14     """
  15     INSERT INTO following_relationships (follower_id, following_id, state, inserted_at, updated_at)
  16     SELECT
  17         relations.follower_id,
  18         following.id,
  19         'accept',
  20         now(),
  21         now()
  22     FROM (
  23         SELECT
  24             users.id AS follower_id,
  25             unnest(users.following) AS following_ap_id
  26         FROM
  27             users
  28         WHERE
  29             users.following != '{}'
  30             AND users.local = false OR users.local = true AND users.email IS NOT NULL -- Exclude `internal/fetch` and `relay`
  31     ) AS relations
  32         JOIN users AS "following" ON "following".follower_address = relations.following_ap_id
  33
  34         WHERE relations.follower_id != following.id
  35     ON CONFLICT DO NOTHING
  36     """
  37   end
  38
  39   defp import_following_from_activities do
  40     """
  41     INSERT INTO
  42         following_relationships (
  43             follower_id,
  44             following_id,
  45             state,
  46             inserted_at,
  47             updated_at
  48         )
  49     SELECT
  50         followers.id,
  51         following.id,
  52         activities.data ->> 'state',
  53         (activities.data ->> 'published') :: timestamp,
  54         now()
  55     FROM
  56         activities
  57         JOIN users AS followers ON (activities.actor = followers.ap_id)
  58         JOIN users AS following ON (activities.data ->> 'object' = following.ap_id)
  59     WHERE
  60         activities.data ->> 'type' = 'Follow'
  61         AND activities.data ->> 'state' IN ('accept', 'pending', 'reject')
  62     ORDER BY activities.updated_at DESC
  63     ON CONFLICT DO NOTHING
  64     """
  65   end
  66
  67   defp restore_following_column do
  68     """
  69     UPDATE
  70         users
  71     SET
  72         following = following_query.following_array,
  73         updated_at = now()
  74     FROM (
  75         SELECT
  76             follower.id AS follower_id,
  77             CASE follower.local
  78             WHEN TRUE THEN
  79                 array_prepend(follower.follower_address, array_agg(following.follower_address))
  80             ELSE
  81                 array_agg(following.follower_address)
  82             END AS following_array
  83         FROM
  84             following_relationships
  85             JOIN users AS follower ON follower.id = following_relationships.follower_id
  86             JOIN users AS following ON following.id = following_relationships.following_id
  87         GROUP BY
  88             follower.id) AS following_query
  89     WHERE
  90         following_query.follower_id = users.id
  91     """
  92   end
  93 end