git.squeep.com Git - akkoma/blob - priv/repo/migrations/20191007073319_create_following_relationships.exs

   1 defmodule Pleroma.Repo.Migrations.CreateFollowingRelationships do
   2   use Ecto.Migration
   3
   4   def change 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)
   9
  10       timestamps()
  11     end
  12
  13     create_if_not_exists(index(:following_relationships, :follower_id))
  14     create_if_not_exists(unique_index(:following_relationships, [:follower_id, :following_id]))
  15
  16     execute(update_thread_visibility(), restore_thread_visibility())
  17   end
  18
  19   # The only difference between the original version: `actor_user` replaced with `actor_user_following`
  20   def update_thread_visibility do
  21     """
  22     CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
  23     DECLARE
  24       public varchar := 'https://www.w3.org/ns/activitystreams#Public';
  25       child objects%ROWTYPE;
  26       activity activities%ROWTYPE;
  27       author_fa varchar;
  28       valid_recipients varchar[];
  29       actor_user_following varchar[];
  30     BEGIN
  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;
  36
  37       --- Fetch our initial activity.
  38       SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
  39
  40       LOOP
  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
  44           RETURN false;
  45         END IF;
  46
  47         --- We only care about Create activities.
  48         IF activity.data->>'type' != 'Create' THEN
  49           RETURN true;
  50         END IF;
  51
  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';
  56
  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;
  59
  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;
  64         END IF;
  65
  66         --- Check visibility.
  67         IF NOT valid_recipients && activity.recipients THEN
  68           --- activity not visible, break out of the loop
  69           RETURN false;
  70         END IF;
  71
  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';
  77         ELSE
  78           RETURN true;
  79         END IF;
  80       END LOOP;
  81     END;
  82     $$ LANGUAGE plpgsql IMMUTABLE;
  83     """
  84   end
  85
  86   # priv/repo/migrations/20190515222404_add_thread_visibility_function.exs
  87   def restore_thread_visibility do
  88     """
  89     CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
  90     DECLARE
  91       public varchar := 'https://www.w3.org/ns/activitystreams#Public';
  92       child objects%ROWTYPE;
  93       activity activities%ROWTYPE;
  94       actor_user users%ROWTYPE;
  95       author_fa varchar;
  96       valid_recipients varchar[];
  97     BEGIN
  98       --- Fetch our actor.
  99       SELECT * INTO actor_user FROM users WHERE users.ap_id = actor;
 100
 101       --- Fetch our initial activity.
 102       SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
 103
 104       LOOP
 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
 108           RETURN false;
 109         END IF;
 110
 111         --- We only care about Create activities.
 112         IF activity.data->>'type' != 'Create' THEN
 113           RETURN true;
 114         END IF;
 115
 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';
 120
 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;
 123
 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;
 128         END IF;
 129
 130         --- Check visibility.
 131         IF NOT valid_recipients && activity.recipients THEN
 132           --- activity not visible, break out of the loop
 133           RETURN false;
 134         END IF;
 135
 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';
 141         ELSE
 142           RETURN true;
 143         END IF;
 144       END LOOP;
 145     END;
 146     $$ LANGUAGE plpgsql IMMUTABLE;
 147     """
 148   end
 149 end