1 defmodule Pleroma.Repo.Migrations.ChangeThreadVisibilityToBeLocalOnlyAware do
5 execute("DROP FUNCTION IF EXISTS thread_visibility(actor varchar, activity_id varchar)")
6 execute(update_thread_visibility())
11 "DROP FUNCTION IF EXISTS thread_visibility(actor varchar, activity_id varchar, local_public varchar)"
14 execute(restore_thread_visibility())
17 def update_thread_visibility do
19 CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar, local_public varchar default '') RETURNS boolean AS $$
21 public varchar := 'https://www.w3.org/ns/activitystreams#Public';
22 child objects%ROWTYPE;
23 activity activities%ROWTYPE;
25 valid_recipients varchar[];
26 actor_user_following varchar[];
28 --- Fetch actor following
29 SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
30 JOIN users ON users.id = following_relationships.follower_id
31 JOIN users AS following ON following.id = following_relationships.following_id
32 WHERE users.ap_id = actor;
34 --- Fetch our initial activity.
35 SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
38 --- Ensure that we have an activity before continuing.
39 --- If we don't, the thread is not satisfiable.
40 IF activity IS NULL THEN
44 --- We only care about Create activities.
45 IF activity.data->>'type' != 'Create' THEN
49 --- Normalize the child object into child.
50 SELECT * INTO child FROM objects
51 INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
52 WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
54 --- Fetch the author's AS2 following collection.
55 SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
57 --- Prepare valid recipients array.
58 valid_recipients := ARRAY[actor, public];
59 --- If we specified local public, add it.
60 IF local_public <> '' THEN
61 valid_recipients := valid_recipients || local_public;
63 IF ARRAY[author_fa] && actor_user_following THEN
64 valid_recipients := valid_recipients || author_fa;
68 IF NOT valid_recipients && activity.recipients THEN
69 --- activity not visible, break out of the loop
73 --- If there's a parent, load it and do this all over again.
74 IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
75 SELECT * INTO activity FROM activities
76 INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
77 WHERE child.data->>'inReplyTo' = objects.data->>'id';
83 $$ LANGUAGE plpgsql IMMUTABLE;
87 # priv/repo/migrations/20191007073319_create_following_relationships.exs
88 def restore_thread_visibility do
90 CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
92 public varchar := 'https://www.w3.org/ns/activitystreams#Public';
93 child objects%ROWTYPE;
94 activity activities%ROWTYPE;
96 valid_recipients varchar[];
97 actor_user_following varchar[];
99 --- Fetch actor following
100 SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
101 JOIN users ON users.id = following_relationships.follower_id
102 JOIN users AS following ON following.id = following_relationships.following_id
103 WHERE users.ap_id = actor;
105 --- Fetch our initial activity.
106 SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
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
115 --- We only care about Create activities.
116 IF activity.data->>'type' != 'Create' THEN
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';
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;
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;
134 --- Check visibility.
135 IF NOT valid_recipients && activity.recipients THEN
136 --- activity not visible, break out of the loop
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';
150 $$ LANGUAGE plpgsql IMMUTABLE;