git.squeep.com Git - akkoma/blob - priv/repo/migrations/20191007073319_create_following_relationships.exs

   1 defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do
   2   use Ecto.Migration
   3
   4   # had to disable these to be able to restore `following` index concurrently
   5   # https://hexdocs.pm/ecto_sql/Ecto.Migration.html#index/3-adding-dropping-indexes-concurrently
   6   @disable_ddl_transaction true
   7   @disable_migration_lock true
   8
   9   def change do
  10     create_if_not_exists table(:following_relationships) do
  11       add(:follower_id, references(:users, type: :uuid, on_delete: :delete_all), null: false)
  12       add(:following_id, references(:users, type: :uuid, on_delete: :delete_all), null: false)
  13       add(:state, :string, null: false)
  14
  15       timestamps()
  16     end
  17
  18     create_if_not_exists(index(:following_relationships, :follower_id))
  19     create_if_not_exists(unique_index(:following_relationships, [:follower_id, :following_id]))
  20
  21     execute(import_following_from_users(), "")
  22     execute(import_following_from_activities(), restore_following_column())
  23
  24     drop(index(:users, [:following], concurrently: true, using: :gin))
  25
  26     alter table(:users) do
  27       remove(:following, {:array, :string}, default: [])
  28     end
  29   end
  30
  31   defp import_following_from_users do
  32     """
  33     INSERT INTO following_relationships (follower_id, following_id, state, inserted_at, updated_at)
  34     SELECT
  35         relations.follower_id,
  36         following.id,
  37         'accept',
  38         now(),
  39         now()
  40     FROM (
  41         SELECT
  42             users.id AS follower_id,
  43             unnest(users.following) AS following_ap_id
  44         FROM
  45             users
  46         WHERE
  47             users.following != '{}'
  48             AND users.local = false OR users.local = true AND users.email IS NOT NULL -- Exclude `internal/fetch` and `relay`
  49     ) AS relations
  50         JOIN users AS "following" ON "following".follower_address = relations.following_ap_id
  51
  52         WHERE relations.follower_id != following.id
  53     ON CONFLICT DO NOTHING
  54     """
  55   end
  56
  57   defp import_following_from_activities do
  58     """
  59     INSERT INTO
  60         following_relationships (
  61             follower_id,
  62             following_id,
  63             state,
  64             inserted_at,
  65             updated_at
  66         )
  67     SELECT
  68         followers.id,
  69         following.id,
  70         activities.data ->> 'state',
  71         (activities.data ->> 'published') :: timestamp,
  72         now()
  73     FROM
  74         activities
  75         JOIN users AS followers ON (activities.actor = followers.ap_id)
  76         JOIN users AS following ON (activities.data ->> 'object' = following.ap_id)
  77     WHERE
  78         activities.data ->> 'type' = 'Follow'
  79         AND activities.data ->> 'state' IN ('accept', 'pending', 'reject')
  80     ORDER BY activities.updated_at DESC
  81     ON CONFLICT DO NOTHING
  82     """
  83   end
  84
  85   defp restore_following_column do
  86     """
  87     UPDATE
  88         users
  89     SET
  90         following = following_query.following_array,
  91         updated_at = now()
  92     FROM (
  93         SELECT
  94             follwer.id AS follower_id,
  95             CASE follwer.local
  96             WHEN TRUE THEN
  97                 array_prepend(follwer.follower_address, array_agg(following.follower_address))
  98             ELSE
  99                 array_agg(following.follower_address)
 100             END AS following_array
 101         FROM
 102             following_relationships
 103             JOIN users AS follwer ON follwer.id = following_relationships.follower_id
 104             JOIN users AS FOLLOWING ON following.id = following_relationships.following_id
 105         GROUP BY
 106             follwer.id) AS following_query
 107     WHERE
 108         following_query.follower_id = users.id
 109     """
 110   end
 111 end