Git of dcc - anni/blob - 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