Git of dcc - anni/blob - priv/repo/migrations/20220821004840_change_thread_visibility_to_use_new_object_id_index.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.ChangeThreadVisibilityToUseNewObjectIdIndex do
   6   use Ecto.Migration
   7
   8   def up do
   9     execute(update_thread_visibility())
  10   end
  11
  12   def down do
  13     execute(restore_thread_visibility())
  14   end
  15
  16   def update_thread_visibility do
  17     """
  18     CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar, local_public varchar default '') RETURNS boolean AS $$
  19     DECLARE
  20       public varchar := 'https://www.w3.org/ns/activitystreams#Public';
  21       child objects%ROWTYPE;
  22       activity activities%ROWTYPE;
  23       author_fa varchar;
  24       valid_recipients varchar[];
  25       actor_user_following varchar[];
  26     BEGIN
  27       --- Fetch actor following
  28       SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
  29       JOIN users ON users.id = following_relationships.follower_id
  30       JOIN users AS following ON following.id = following_relationships.following_id
  31       WHERE users.ap_id = actor;
  32
  33       --- Fetch our initial activity.
  34       SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
  35
  36       LOOP
  37         --- Ensure that we have an activity before continuing.
  38         --- If we don't, the thread is not satisfiable.
  39         IF activity IS NULL THEN
  40           RETURN false;
  41         END IF;
  42
  43         --- We only care about Create activities.
  44         IF activity.data->>'type' != 'Create' THEN
  45           RETURN true;
  46         END IF;
  47
  48         --- Normalize the child object into child.
  49         SELECT * INTO child FROM objects
  50         INNER JOIN activities ON associated_object_id(activities.data) = objects.data->>'id'
  51         WHERE associated_object_id(activity.data) = objects.data->>'id';
  52
  53         --- Fetch the author's AS2 following collection.
  54         SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
  55
  56         --- Prepare valid recipients array.
  57         valid_recipients := ARRAY[actor, public];
  58         --- If we specified local public, add it.
  59         IF local_public <> '' THEN
  60           valid_recipients := valid_recipients || local_public;
  61         END IF;
  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 associated_object_id(activities.data) = 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/20220509180452_change_thread_visibility_to_be_local_only_aware.exs
  87   def restore_thread_visibility do
  88     """
  89     CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar, local_public varchar default '') RETURNS boolean AS $$
  90     DECLARE
  91       public varchar := 'https://www.w3.org/ns/activitystreams#Public';
  92       child objects%ROWTYPE;
  93       activity activities%ROWTYPE;
  94       author_fa varchar;
  95       valid_recipients varchar[];
  96       actor_user_following varchar[];
  97     BEGIN
  98       --- Fetch actor following
  99       SELECT array_agg(following.follower_address) INTO actor_user_following FROM following_relationships
 100       JOIN users ON users.id = following_relationships.follower_id
 101       JOIN users AS following ON following.id = following_relationships.following_id
 102       WHERE users.ap_id = actor;
 103
 104       --- Fetch our initial activity.
 105       SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
 106
 107       LOOP
 108         --- Ensure that we have an activity before continuing.
 109         --- If we don't, the thread is not satisfiable.
 110         IF activity IS NULL THEN
 111           RETURN false;
 112         END IF;
 113
 114         --- We only care about Create activities.
 115         IF activity.data->>'type' != 'Create' THEN
 116           RETURN true;
 117         END IF;
 118
 119         --- Normalize the child object into child.
 120         SELECT * INTO child FROM objects
 121         INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
 122         WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
 123
 124         --- Fetch the author's AS2 following collection.
 125         SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
 126
 127         --- Prepare valid recipients array.
 128         valid_recipients := ARRAY[actor, public];
 129         --- If we specified local public, add it.
 130         IF local_public <> '' THEN
 131           valid_recipients := valid_recipients || local_public;
 132         END IF;
 133         IF ARRAY[author_fa] && actor_user_following THEN
 134           valid_recipients := valid_recipients || author_fa;
 135         END IF;
 136
 137         --- Check visibility.
 138         IF NOT valid_recipients && activity.recipients THEN
 139           --- activity not visible, break out of the loop
 140           RETURN false;
 141         END IF;
 142
 143         --- If there's a parent, load it and do this all over again.
 144         IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
 145           SELECT * INTO activity FROM activities
 146           INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
 147           WHERE child.data->>'inReplyTo' = objects.data->>'id';
 148         ELSE
 149           RETURN true;
 150         END IF;
 151       END LOOP;
 152     END;
 153     $$ LANGUAGE plpgsql IMMUTABLE;
 154     """
 155   end
 156 end