1 # Pleroma: A lightweight social networking server
2 # Copyright © 2017-2022 Pleroma Authors <https://pleroma.social/>
3 # SPDX-License-Identifier: AGPL-3.0-only
5 defmodule Pleroma.Repo.Migrations.ChangeThreadVisibilityToUseNewObjectIdIndex do
9 execute(update_thread_visibility())
13 execute(restore_thread_visibility())
16 def update_thread_visibility do
18 CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar, local_public varchar default '') RETURNS boolean AS $$
20 public varchar := 'https://www.w3.org/ns/activitystreams#Public';
21 child objects%ROWTYPE;
22 activity activities%ROWTYPE;
24 valid_recipients varchar[];
25 actor_user_following varchar[];
27 --- Fetch actor following
28 SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
29 JOIN users ON users.id = following_relationships.follower_id
30 JOIN users AS following ON following.id = following_relationships.following_id
31 WHERE users.ap_id = actor;
33 --- Fetch our initial activity.
34 SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
37 --- Ensure that we have an activity before continuing.
38 --- If we don't, the thread is not satisfiable.
39 IF activity IS NULL THEN
43 --- We only care about Create activities.
44 IF activity.data->>'type' != 'Create' THEN
48 --- Normalize the child object into child.
49 SELECT * INTO child FROM objects
50 INNER JOIN activities ON associated_object_id(activities.data) = objects.data->>'id'
51 WHERE associated_object_id(activity.data) = objects.data->>'id';
53 --- Fetch the author's AS2 following collection.
54 SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
56 --- Prepare valid recipients array.
57 valid_recipients := ARRAY[actor, public];
58 --- If we specified local public, add it.
59 IF local_public <> '' THEN
60 valid_recipients := valid_recipients || local_public;
62 IF ARRAY[author_fa] && actor_user_following THEN
63 valid_recipients := valid_recipients || author_fa;
67 IF NOT valid_recipients && activity.recipients THEN
68 --- activity not visible, break out of the loop
72 --- If there's a parent, load it and do this all over again.
73 IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
74 SELECT * INTO activity FROM activities
75 INNER JOIN objects ON associated_object_id(activities.data) = objects.data->>'id'
76 WHERE child.data->>'inReplyTo' = objects.data->>'id';
82 $$ LANGUAGE plpgsql IMMUTABLE;
86 # priv/repo/migrations/20220509180452_change_thread_visibility_to_be_local_only_aware.exs
87 def restore_thread_visibility do
89 CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar, local_public varchar default '') RETURNS boolean AS $$
91 public varchar := 'https://www.w3.org/ns/activitystreams#Public';
92 child objects%ROWTYPE;
93 activity activities%ROWTYPE;
95 valid_recipients varchar[];
96 actor_user_following varchar[];
98 --- Fetch actor following
99 SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
100 JOIN users ON users.id = following_relationships.follower_id
101 JOIN users AS following ON following.id = following_relationships.following_id
102 WHERE users.ap_id = actor;
104 --- Fetch our initial activity.
105 SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
108 --- Ensure that we have an activity before continuing.
109 --- If we don't, the thread is not satisfiable.
110 IF activity IS NULL THEN
114 --- We only care about Create activities.
115 IF activity.data->>'type' != 'Create' THEN
119 --- Normalize the child object into child.
120 SELECT * INTO child FROM objects
121 INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
122 WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
124 --- Fetch the author's AS2 following collection.
125 SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
127 --- Prepare valid recipients array.
128 valid_recipients := ARRAY[actor, public];
129 --- If we specified local public, add it.
130 IF local_public <> '' THEN
131 valid_recipients := valid_recipients || local_public;
133 IF ARRAY[author_fa] && actor_user_following THEN
134 valid_recipients := valid_recipients || author_fa;
137 --- Check visibility.
138 IF NOT valid_recipients && activity.recipients THEN
139 --- activity not visible, break out of the loop
143 --- If there's a parent, load it and do this all over again.
144 IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
145 SELECT * INTO activity FROM activities
146 INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
147 WHERE child.data->>'inReplyTo' = objects.data->>'id';
153 $$ LANGUAGE plpgsql IMMUTABLE;