Git of dcc - anni/blob - priv/repo/migrations/20190515222404_add_thread_visibility_function.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.AddThreadVisibilityFunction do
   6   use Ecto.Migration
   7   @disable_ddl_transaction true
   8
   9   def up do
  10     statement = """
  11     CREATE OR REPLACE FUNCTION thread_visibility(actor varchar, activity_id varchar) RETURNS boolean AS $$
  12     DECLARE
  13       public varchar := 'https://www.w3.org/ns/activitystreams#Public';
  14       child objects%ROWTYPE;
  15       activity activities%ROWTYPE;
  16       actor_user users%ROWTYPE;
  17       author_fa varchar;
  18       valid_recipients varchar[];
  19     BEGIN
  20       --- Fetch our actor.
  21       SELECT * INTO actor_user FROM users WHERE users.ap_id = actor;
  22
  23       --- Fetch our initial activity.
  24       SELECT * INTO activity FROM activities WHERE activities.data->>'id' = activity_id;
  25
  26       LOOP
  27         --- Ensure that we have an activity before continuing.
  28         --- If we don't, the thread is not satisfiable.
  29         IF activity IS NULL THEN
  30           RETURN false;
  31         END IF;
  32
  33         --- We only care about Create activities.
  34         IF activity.data->>'type' != 'Create' THEN
  35           RETURN true;
  36         END IF;
  37
  38         --- Normalize the child object into child.
  39         SELECT * INTO child FROM objects
  40         INNER JOIN activities ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
  41         WHERE COALESCE(activity.data->'object'->>'id', activity.data->>'object') = objects.data->>'id';
  42
  43         --- Fetch the author's AS2 following collection.
  44         SELECT COALESCE(users.follower_address, '') INTO author_fa FROM users WHERE users.ap_id = activity.actor;
  45
  46         --- Prepare valid recipients array.
  47         valid_recipients := ARRAY[actor, public];
  48         IF ARRAY[author_fa] && actor_user.following THEN
  49           valid_recipients := valid_recipients || author_fa;
  50         END IF;
  51
  52         --- Check visibility.
  53         IF NOT valid_recipients && activity.recipients THEN
  54           --- activity not visible, break out of the loop
  55           RETURN false;
  56         END IF;
  57
  58         --- If there's a parent, load it and do this all over again.
  59         IF (child.data->'inReplyTo' IS NOT NULL) AND (child.data->'inReplyTo' != 'null'::jsonb) THEN
  60           SELECT * INTO activity FROM activities
  61           INNER JOIN objects ON COALESCE(activities.data->'object'->>'id', activities.data->>'object') = objects.data->>'id'
  62           WHERE child.data->>'inReplyTo' = objects.data->>'id';
  63         ELSE
  64           RETURN true;
  65         END IF;
  66       END LOOP;
  67     END;
  68     $$ LANGUAGE plpgsql IMMUTABLE;
  69     """
  70
  71     execute(statement)
  72   end
  73
  74   def down do
  75     execute("drop function if exists thread_visibility(actor varchar, activity_id varchar)")
  76   end
  77 end