total rebase
[anni] / 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