Git of dcc - anni/blob - priv/repo/migrations/20220509180452_change_thread_visibility_to_be_local_only_aware.exs

   1 defmodule Pleroma.Repo.Migrations.ChangeThreadVisibilityToBeLocalOnlyAware do
   2   use Ecto.Migration
   3
   4   def up do
   5     execute("DROP FUNCTION IF EXISTS thread_visibility(actor varchar, activity_id varchar)")
   6     execute(update_thread_visibility())
   7   end
   8
   9   def down do
  10     execute(
  11       "DROP FUNCTION IF EXISTS thread_visibility(actor varchar, activity_id varchar, local_public varchar)"
  12     )
  13
  14     execute(restore_thread_visibility())
  15   end
  16
  17   def update_thread_visibility do
  18     """
  19     CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar, local_public varchar default '') RETURNS boolean AS $$
  20     DECLARE
  21       public varchar := 'https://www.w3.org/ns/activitystreams#Public';
  22       child objects%ROWTYPE;
  23       activity activities%ROWTYPE;
  24       author_fa varchar;
  25       valid_recipients varchar[];
  26       actor_user_following varchar[];
  27     BEGIN
  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;
  33
  34       --- Fetch our initial activity.
  35       SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
  36
  37       LOOP
  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
  41           RETURN false;
  42         END IF;
  43
  44         --- We only care about Create activities.
  45         IF activity.data->>'type' != 'Create' THEN
  46           RETURN true;
  47         END IF;
  48
  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';
  53
  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;
  56
  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;
  62         END IF;
  63         IF ARRAY[author_fa] && actor_user_following THEN
  64           valid_recipients := valid_recipients || author_fa;
  65         END IF;
  66
  67         --- Check visibility.
  68         IF NOT valid_recipients && activity.recipients THEN
  69           --- activity not visible, break out of the loop
  70           RETURN false;
  71         END IF;
  72
  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';
  78         ELSE
  79           RETURN true;
  80         END IF;
  81       END LOOP;
  82     END;
  83     $$ LANGUAGE plpgsql IMMUTABLE;
  84     """
  85   end
  86
  87   # priv/repo/migrations/20191007073319_create_following_relationships.exs
  88   def restore_thread_visibility do
  89     """
  90     CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
  91     DECLARE
  92       public varchar := 'https://www.w3.org/ns/activitystreams#Public';
  93       child objects%ROWTYPE;
  94       activity activities%ROWTYPE;
  95       author_fa varchar;
  96       valid_recipients varchar[];
  97       actor_user_following varchar[];
  98     BEGIN
  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;
 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