1 defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do
5 create_if_not_exists table(:following_relationships) do
6 add(:follower_id, references(:users, type: :uuid, on_delete: :delete_all), null: false)
7 add(:following_id, references(:users, type: :uuid, on_delete: :delete_all), null: false)
8 add(:state, :string, null: false)
13 create_if_not_exists(index(:following_relationships, :follower_id))
14 create_if_not_exists(unique_index(:following_relationships, [:follower_id, :following_id]))
16 execute(update_thread_visibility(), restore_thread_visibility())
19 # The only difference between the original version: `actor_user` replaced with `actor_user_following`
20 def update_thread_visibility do
22 CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
24 public varchar := 'https://www.w3.org/ns/activitystreams#Public';
25 child objects%ROWTYPE;
26 activity activities%ROWTYPE;
28 valid_recipients varchar[];
29 actor_user_following varchar[];
31 --- Fetch actor following
32 SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
33 JOIN users ON users.id = following_relationships.follower_id
34 JOIN users AS following ON following.id = following_relationships.following_id
35 WHERE users.ap_id = actor;
37 --- Fetch our initial activity.
38 SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
41 --- Ensure that we have an activity before continuing.
42 --- If we don't, the thread is not satisfiable.
43 IF activity IS NULL THEN
47 --- We only care about Create activities.
48 IF activity.data->>'type' != 'Create' THEN
52 --- Normalize the child object into child.
53 SELECT * INTO child FROM objects
54 INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
55 WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
57 --- Fetch the author's AS2 following collection.
58 SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
60 --- Prepare valid recipients array.
61 valid_recipients := ARRAY[actor, 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 COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
76 WHERE child.data->>'inReplyTo' = objects.data->>'id';
82 $$ LANGUAGE plpgsql IMMUTABLE;
86 # priv/repo/migrations/20190515222404_add_thread_visibility_function.exs
87 def restore_thread_visibility do
89 CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
91 public varchar := 'https://www.w3.org/ns/activitystreams#Public';
92 child objects%ROWTYPE;
93 activity activities%ROWTYPE;
94 actor_user users%ROWTYPE;
96 valid_recipients varchar[];
99 SELECT * INTO actor_user FROM users WHERE users.ap_id = actor;
101 --- Fetch our initial activity.
102 SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
105 --- Ensure that we have an activity before continuing.
106 --- If we don't, the thread is not satisfiable.
107 IF activity IS NULL THEN
111 --- We only care about Create activities.
112 IF activity.data->>'type' != 'Create' THEN
116 --- Normalize the child object into child.
117 SELECT * INTO child FROM objects
118 INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
119 WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
121 --- Fetch the author's AS2 following collection.
122 SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
124 --- Prepare valid recipients array.
125 valid_recipients := ARRAY[actor, public];
126 IF ARRAY[author_fa] && actor_user.following THEN
127 valid_recipients := valid_recipients || author_fa;
130 --- Check visibility.
131 IF NOT valid_recipients && activity.recipients THEN
132 --- activity not visible, break out of the loop
136 --- If there's a parent, load it and do this all over again.
137 IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
138 SELECT * INTO activity FROM activities
139 INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
140 WHERE child.data->>'inReplyTo' = objects.data->>'id';
146 $$ LANGUAGE plpgsql IMMUTABLE;