First
[anni] / priv / repo / migrations / 20191007073319_create_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.CreateFollowingRelationships do
6   use Ecto.Migration
7
8   def change do
9     create_if_not_exists table(:following_relationships) do
10       add(:follower_id, references(:users, type: :uuid, on_delete: :delete_all), null: false)
11       add(:following_id, references(:users, type: :uuid, on_delete: :delete_all), null: false)
12       add(:state, :string, null: false)
13
14       timestamps()
15     end
16
17     create_if_not_exists(index(:following_relationships, :follower_id))
18     create_if_not_exists(unique_index(:following_relationships, [:follower_id, :following_id]))
19
20     execute(update_thread_visibility(), restore_thread_visibility())
21   end
22
23   # The only difference between the original version: `actor_user` replaced with `actor_user_following`
24   def update_thread_visibility do
25     """
26     CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
27     DECLARE
28       public varchar := 'https://www.w3.org/ns/activitystreams#Public';
29       child objects%ROWTYPE;
30       activity activities%ROWTYPE;
31       author_fa varchar;
32       valid_recipients varchar[];
33       actor_user_following varchar[];
34     BEGIN
35       --- Fetch actor following
36       SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
37       JOIN users ON users.id = following_relationships.follower_id
38       JOIN users AS following ON following.id = following_relationships.following_id
39       WHERE users.ap_id = actor;
40
41       --- Fetch our initial activity.
42       SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
43
44       LOOP
45         --- Ensure that we have an activity before continuing.
46         --- If we don't, the thread is not satisfiable.
47         IF activity IS NULL THEN
48           RETURN false;
49         END IF;
50
51         --- We only care about Create activities.
52         IF activity.data->>'type' != 'Create' THEN
53           RETURN true;
54         END IF;
55
56         --- Normalize the child object into child.
57         SELECT * INTO child FROM objects
58         INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
59         WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
60
61         --- Fetch the author's AS2 following collection.
62         SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
63
64         --- Prepare valid recipients array.
65         valid_recipients := ARRAY[actor, public];
66         IF ARRAY[author_fa] && actor_user_following THEN
67           valid_recipients := valid_recipients || author_fa;
68         END IF;
69
70         --- Check visibility.
71         IF NOT valid_recipients && activity.recipients THEN
72           --- activity not visible, break out of the loop
73           RETURN false;
74         END IF;
75
76         --- If there's a parent, load it and do this all over again.
77         IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
78           SELECT * INTO activity FROM activities
79           INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
80           WHERE child.data->>'inReplyTo' = objects.data->>'id';
81         ELSE
82           RETURN true;
83         END IF;
84       END LOOP;
85     END;
86     $$ LANGUAGE plpgsql IMMUTABLE;
87     """
88   end
89
90   # priv/repo/migrations/20190515222404_add_thread_visibility_function.exs
91   def restore_thread_visibility do
92     """
93     CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
94     DECLARE
95       public varchar := 'https://www.w3.org/ns/activitystreams#Public';
96       child objects%ROWTYPE;
97       activity activities%ROWTYPE;
98       actor_user users%ROWTYPE;
99       author_fa varchar;
100       valid_recipients varchar[];
101     BEGIN
102       --- Fetch our actor.
103       SELECT * INTO actor_user FROM users WHERE users.ap_id = actor;
104
105       --- Fetch our initial activity.
106       SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
107
108       LOOP
109         --- Ensure that we have an activity before continuing.
110         --- If we don't, the thread is not satisfiable.
111         IF activity IS NULL THEN
112           RETURN false;
113         END IF;
114
115         --- We only care about Create activities.
116         IF activity.data->>'type' != 'Create' THEN
117           RETURN true;
118         END IF;
119
120         --- Normalize the child object into child.
121         SELECT * INTO child FROM objects
122         INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
123         WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
124
125         --- Fetch the author's AS2 following collection.
126         SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
127
128         --- Prepare valid recipients array.
129         valid_recipients := ARRAY[actor, public];
130         IF ARRAY[author_fa] && actor_user.following THEN
131           valid_recipients := valid_recipients || author_fa;
132         END IF;
133
134         --- Check visibility.
135         IF NOT valid_recipients && activity.recipients THEN
136           --- activity not visible, break out of the loop
137           RETURN false;
138         END IF;
139
140         --- If there's a parent, load it and do this all over again.
141         IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
142           SELECT * INTO activity FROM activities
143           INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
144           WHERE child.data->>'inReplyTo' = objects.data->>'id';
145         ELSE
146           RETURN true;
147         END IF;
148       END LOOP;
149     END;
150     $$ LANGUAGE plpgsql IMMUTABLE;
151     """
152   end
153 end