Merge branch 'develop' into remove-twitter-api

18 files changed, 454 insertions, 0 deletions

diff --git a/priv/repo/migrations/20200309123730_create_chats.exs b/priv/repo/migrations/20200309123730_create_chats.exs
new file mode 100644
index 000000000..715d798ea
--- /dev/null
+++ b/priv/repo/migrations/20200309123730_create_chats.exs
@@ -0,0 +1,16 @@
+defmodule Pleroma.Repo.Migrations.CreateChats do
+  use Ecto.Migration
+
+  def change do
+    create table(:chats) do
+      add(:user_id, references(:users, type: :uuid))
+      # Recipient is an ActivityPub id, to future-proof for group support.
+      add(:recipient, :string)
+      add(:unread, :integer, default: 0)
+      timestamps()
+    end
+
+    # There's only one chat between a user and a recipient.
+    create(index(:chats, [:user_id, :recipient], unique: true))
+  end
+end
diff --git a/priv/repo/migrations/20200322174133_user_raw_bio.exs b/priv/repo/migrations/20200322174133_user_raw_bio.exs
new file mode 100644
index 000000000..ddf9be4f5
--- /dev/null
+++ b/priv/repo/migrations/20200322174133_user_raw_bio.exs
@@ -0,0 +1,9 @@
+defmodule Pleroma.Repo.Migrations.UserRawBio do
+  use Ecto.Migration
+
+  def change do
+    alter table(:users) do
+      add_if_not_exists(:raw_bio, :text)
+    end
+  end
+end
diff --git a/priv/repo/migrations/20200323122421_mrf_config_move_from_instance_namespace.exs b/priv/repo/migrations/20200323122421_mrf_config_move_from_instance_namespace.exs
new file mode 100644
index 000000000..ef36c4eb7
--- /dev/null
+++ b/priv/repo/migrations/20200323122421_mrf_config_move_from_instance_namespace.exs
@@ -0,0 +1,39 @@
+defmodule Pleroma.Repo.Migrations.MrfConfigMoveFromInstanceNamespace do
+  use Ecto.Migration
+
+  alias Pleroma.ConfigDB
+
+  @old_keys [:rewrite_policy, :mrf_transparency, :mrf_transparency_exclusions]
+  def change do
+    config = ConfigDB.get_by_params(%{group: :pleroma, key: :instance})
+
+    if config do
+      mrf =
+        config.value
+        |> Keyword.take(@old_keys)
+        |> Keyword.new(fn
+          {:rewrite_policy, policies} -> {:policies, policies}
+          {:mrf_transparency, transparency} -> {:transparency, transparency}
+          {:mrf_transparency_exclusions, exclusions} -> {:transparency_exclusions, exclusions}
+        end)
+
+      if mrf != [] do
+        {:ok, _} =
+          %ConfigDB{}
+          |> ConfigDB.changeset(%{group: :pleroma, key: :mrf, value: mrf})
+          |> Pleroma.Repo.insert()
+
+        new_instance = Keyword.drop(config.value, @old_keys)
+
+        if new_instance != [] do
+          {:ok, _} =
+            config
+            |> ConfigDB.changeset(%{value: new_instance})
+            |> Pleroma.Repo.update()
+        else
+          {:ok, _} = ConfigDB.delete(config)
+        end
+      end
+    end
+  end
+end
diff --git a/priv/repo/migrations/20200328193433_populate_user_raw_bio.exs b/priv/repo/migrations/20200328193433_populate_user_raw_bio.exs
new file mode 100644
index 000000000..cb35db3f5
--- /dev/null
+++ b/priv/repo/migrations/20200328193433_populate_user_raw_bio.exs
@@ -0,0 +1,25 @@
+defmodule Pleroma.Repo.Migrations.PopulateUserRawBio do
+  use Ecto.Migration
+  import Ecto.Query
+  alias Pleroma.User
+  alias Pleroma.Repo
+
+  def change do
+    {:ok, _} = Application.ensure_all_started(:fast_sanitize)
+
+    User.Query.build(%{local: true})
+    |> select([u], struct(u, [:id, :ap_id, :bio]))
+    |> Repo.stream()
+    |> Enum.each(fn %{bio: bio} = user ->
+      if bio do
+        raw_bio =
+          bio
+          |> String.replace(~r(<br */?>), "\n")
+          |> Pleroma.HTML.strip_tags()
+
+        Ecto.Changeset.cast(user, %{raw_bio: raw_bio}, [:raw_bio])
+        |> Repo.update()
+      end
+    end)
+  end
+end
diff --git a/priv/repo/migrations/20200508092434_update_counter_cache_table.exs b/priv/repo/migrations/20200508092434_update_counter_cache_table.exs
new file mode 100644
index 000000000..738344868
--- /dev/null
+++ b/priv/repo/migrations/20200508092434_update_counter_cache_table.exs
@@ -0,0 +1,143 @@
+defmodule Pleroma.Repo.Migrations.UpdateCounterCacheTable do
+  use Ecto.Migration
+
+  @function_name "update_status_visibility_counter_cache"
+  @trigger_name "status_visibility_counter_cache_trigger"
+
+  def up do
+    execute("drop trigger if exists #{@trigger_name} on activities")
+    execute("drop function if exists #{@function_name}()")
+    drop_if_exists(unique_index(:counter_cache, [:name]))
+    drop_if_exists(table(:counter_cache))
+
+    create_if_not_exists table(:counter_cache) do
+      add(:instance, :string, null: false)
+      add(:direct, :bigint, null: false, default: 0)
+      add(:private, :bigint, null: false, default: 0)
+      add(:unlisted, :bigint, null: false, default: 0)
+      add(:public, :bigint, null: false, default: 0)
+    end
+
+    create_if_not_exists(unique_index(:counter_cache, [:instance]))
+
+    """
+    CREATE OR REPLACE FUNCTION #{@function_name}()
+    RETURNS TRIGGER AS
+    $$
+      DECLARE
+        hostname character varying(255);
+        visibility_new character varying(64);
+        visibility_old character varying(64);
+        actor character varying(255);
+      BEGIN
+      IF TG_OP = 'DELETE' THEN
+        actor := OLD.actor;
+      ELSE
+        actor := NEW.actor;
+      END IF;
+      hostname := split_part(actor, '/', 3);
+      IF TG_OP = 'INSERT' THEN
+        visibility_new := activity_visibility(NEW.actor, NEW.recipients, NEW.data);
+        IF NEW.data->>'type' = 'Create'
+            AND visibility_new IN ('public', 'unlisted', 'private', 'direct') THEN
+          EXECUTE format('INSERT INTO "counter_cache" ("instance", %1$I) VALUES ($1, 1)
+                          ON CONFLICT ("instance") DO
+                          UPDATE SET %1$I = "counter_cache".%1$I + 1', visibility_new)
+                          USING hostname;
+        END IF;
+        RETURN NEW;
+      ELSIF TG_OP = 'UPDATE' THEN
+        visibility_new := activity_visibility(NEW.actor, NEW.recipients, NEW.data);
+        visibility_old := activity_visibility(OLD.actor, OLD.recipients, OLD.data);
+        IF (NEW.data->>'type' = 'Create')
+            AND (OLD.data->>'type' = 'Create')
+            AND visibility_new != visibility_old
+            AND visibility_new IN ('public', 'unlisted', 'private', 'direct') THEN
+          EXECUTE format('UPDATE "counter_cache" SET
+                          %1$I = greatest("counter_cache".%1$I - 1, 0),
+                          %2$I = "counter_cache".%2$I + 1
+                          WHERE "instance" = $1', visibility_old, visibility_new)
+                          USING hostname;
+        END IF;
+        RETURN NEW;
+      ELSIF TG_OP = 'DELETE' THEN
+        IF OLD.data->>'type' = 'Create' THEN
+          visibility_old := activity_visibility(OLD.actor, OLD.recipients, OLD.data);
+          EXECUTE format('UPDATE "counter_cache" SET
+                          %1$I = greatest("counter_cache".%1$I - 1, 0)
+                          WHERE "instance" = $1', visibility_old)
+                          USING hostname;
+        END IF;
+        RETURN OLD;
+      END IF;
+      END;
+    $$
+    LANGUAGE 'plpgsql';
+    """
+    |> execute()
+
+    execute("DROP TRIGGER IF EXISTS #{@trigger_name} ON activities")
+
+    """
+    CREATE TRIGGER #{@trigger_name}
+    BEFORE
+      INSERT
+      OR UPDATE of recipients, data
+      OR DELETE
+    ON activities
+    FOR EACH ROW
+      EXECUTE PROCEDURE #{@function_name}();
+    """
+    |> execute()
+  end
+
+  def down do
+    execute("DROP TRIGGER IF EXISTS #{@trigger_name} ON activities")
+    execute("DROP FUNCTION IF EXISTS #{@function_name}()")
+    drop_if_exists(unique_index(:counter_cache, [:instance]))
+    drop_if_exists(table(:counter_cache))
+
+    create_if_not_exists table(:counter_cache) do
+      add(:name, :string, null: false)
+      add(:count, :bigint, null: false, default: 0)
+    end
+
+    create_if_not_exists(unique_index(:counter_cache, [:name]))
+
+    """
+    CREATE OR REPLACE FUNCTION #{@function_name}()
+    RETURNS TRIGGER AS
+    $$
+      DECLARE
+      BEGIN
+      IF TG_OP = 'INSERT' THEN
+          IF NEW.data->>'type' = 'Create' THEN
+            EXECUTE 'INSERT INTO counter_cache (name, count) VALUES (''status_visibility_' || activity_visibility(NEW.actor, NEW.recipients, NEW.data) || ''', 1) ON CONFLICT (name) DO UPDATE SET count = counter_cache.count + 1';
+          END IF;
+          RETURN NEW;
+      ELSIF TG_OP = 'UPDATE' THEN
+          IF (NEW.data->>'type' = 'Create') and (OLD.data->>'type' = 'Create') and activity_visibility(NEW.actor, NEW.recipients, NEW.data) != activity_visibility(OLD.actor, OLD.recipients, OLD.data) THEN
+             EXECUTE 'INSERT INTO counter_cache (name, count) VALUES (''status_visibility_' || activity_visibility(NEW.actor, NEW.recipients, NEW.data) || ''', 1) ON CONFLICT (name) DO UPDATE SET count = counter_cache.count + 1';
+             EXECUTE 'update counter_cache SET count = counter_cache.count - 1 where count > 0 and name = ''status_visibility_' || activity_visibility(OLD.actor, OLD.recipients, OLD.data) || ''';';
+          END IF;
+          RETURN NEW;
+      ELSIF TG_OP = 'DELETE' THEN
+          IF OLD.data->>'type' = 'Create' THEN
+            EXECUTE 'update counter_cache SET count = counter_cache.count - 1 where count > 0 and name = ''status_visibility_' || activity_visibility(OLD.actor, OLD.recipients, OLD.data) || ''';';
+          END IF;
+          RETURN OLD;
+      END IF;
+      END;
+    $$
+    LANGUAGE 'plpgsql';
+    """
+    |> execute()
+
+    """
+    CREATE TRIGGER #{@trigger_name} BEFORE INSERT OR UPDATE of recipients, data OR DELETE ON activities
+    FOR EACH ROW
+    EXECUTE PROCEDURE #{@function_name}();
+    """
+    |> execute()
+  end
+end
diff --git a/priv/repo/migrations/20200520155351_add_recipients_contain_blocked_domains_function.exs b/priv/repo/migrations/20200520155351_add_recipients_contain_blocked_domains_function.exs
new file mode 100644
index 000000000..14e873125
--- /dev/null
+++ b/priv/repo/migrations/20200520155351_add_recipients_contain_blocked_domains_function.exs
@@ -0,0 +1,33 @@
+defmodule Pleroma.Repo.Migrations.AddRecipientsContainBlockedDomainsFunction do
+  use Ecto.Migration
+  @disable_ddl_transaction true
+
+  def up do
+    statement = """
+    CREATE OR REPLACE FUNCTION recipients_contain_blocked_domains(recipients varchar[], blocked_domains varchar[]) RETURNS boolean AS $$
+    DECLARE
+      recipient_domain varchar;
+      recipient varchar;
+    BEGIN
+      FOREACH recipient IN ARRAY recipients LOOP
+        recipient_domain = split_part(recipient, '/', 3)::varchar;
+
+        IF recipient_domain = ANY(blocked_domains) THEN
+          RETURN TRUE;
+        END IF;
+      END LOOP;
+
+      RETURN FALSE;
+    END;
+    $$ LANGUAGE plpgsql;
+    """
+
+    execute(statement)
+  end
+
+  def down do
+    execute(
+      "drop function if exists recipients_contain_blocked_domains(recipients varchar[], blocked_domains varchar[])"
+    )
+  end
+end
diff --git a/priv/repo/migrations/20200526144426_add_apps_indexes.exs b/priv/repo/migrations/20200526144426_add_apps_indexes.exs
new file mode 100644
index 000000000..5cb6a0473
--- /dev/null
+++ b/priv/repo/migrations/20200526144426_add_apps_indexes.exs
@@ -0,0 +1,7 @@
+defmodule Pleroma.Repo.Migrations.AddAppsIndexes do
+  use Ecto.Migration
+
+  def change do
+    create(index(:apps, [:client_id, :client_secret]))
+  end
+end
diff --git a/priv/repo/migrations/20200527104138_change_notification_user_index.exs b/priv/repo/migrations/20200527104138_change_notification_user_index.exs
new file mode 100644
index 000000000..4dcfe6de9
--- /dev/null
+++ b/priv/repo/migrations/20200527104138_change_notification_user_index.exs
@@ -0,0 +1,8 @@
+defmodule Pleroma.Repo.Migrations.ChangeNotificationUserIndex do
+  use Ecto.Migration
+
+  def change do
+    drop_if_exists(index(:notifications, [:user_id]))
+    create_if_not_exists(index(:notifications, [:user_id, "id desc nulls last"]))
+  end
+end
diff --git a/priv/repo/migrations/20200527163635_delete_notifications_from_invisible_users.exs b/priv/repo/migrations/20200527163635_delete_notifications_from_invisible_users.exs
new file mode 100644
index 000000000..9e95a8111
--- /dev/null
+++ b/priv/repo/migrations/20200527163635_delete_notifications_from_invisible_users.exs
@@ -0,0 +1,18 @@
+defmodule Pleroma.Repo.Migrations.DeleteNotificationsFromInvisibleUsers do
+  use Ecto.Migration
+
+  import Ecto.Query
+  alias Pleroma.Repo
+
+  def up do
+    Pleroma.Notification
+    |> join(:inner, [n], activity in assoc(n, :activity))
+    |> where(
+      [n, a],
+      fragment("? in (SELECT ap_id FROM users WHERE invisible = true)", a.actor)
+    )
+    |> Repo.delete_all()
+  end
+
+  def down, do: :ok
+end
diff --git a/priv/repo/migrations/20200602094828_add_type_to_notifications.exs b/priv/repo/migrations/20200602094828_add_type_to_notifications.exs
new file mode 100644
index 000000000..19c733628
--- /dev/null
+++ b/priv/repo/migrations/20200602094828_add_type_to_notifications.exs
@@ -0,0 +1,9 @@
+defmodule Pleroma.Repo.Migrations.AddTypeToNotifications do
+  use Ecto.Migration
+
+  def change do
+    alter table(:notifications) do
+      add(:type, :string)
+    end
+  end
+end
diff --git a/priv/repo/migrations/20200602125218_backfill_notification_types.exs b/priv/repo/migrations/20200602125218_backfill_notification_types.exs
new file mode 100644
index 000000000..996d721ee
--- /dev/null
+++ b/priv/repo/migrations/20200602125218_backfill_notification_types.exs
@@ -0,0 +1,10 @@
+defmodule Pleroma.Repo.Migrations.BackfillNotificationTypes do
+  use Ecto.Migration
+
+  def up do
+    Pleroma.MigrationHelper.NotificationBackfill.fill_in_notification_types()
+  end
+
+  def down do
+  end
+end
diff --git a/priv/repo/migrations/20200602150528_create_chat_message_reference.exs b/priv/repo/migrations/20200602150528_create_chat_message_reference.exs
new file mode 100644
index 000000000..6f9148b7c
--- /dev/null
+++ b/priv/repo/migrations/20200602150528_create_chat_message_reference.exs
@@ -0,0 +1,20 @@
+# Pleroma: A lightweight social networking server
+# Copyright © 2017-2020 Pleroma Authors <https://pleroma.social/>
+# SPDX-License-Identifier: AGPL-3.0-only
+
+defmodule Pleroma.Repo.Migrations.CreateChatMessageReference do
+  use Ecto.Migration
+
+  def change do
+    create table(:chat_message_references, primary_key: false) do
+      add(:id, :uuid, primary_key: true)
+      add(:chat_id, references(:chats, on_delete: :delete_all), null: false)
+      add(:object_id, references(:objects, on_delete: :delete_all), null: false)
+      add(:seen, :boolean, default: false, null: false)
+
+      timestamps()
+    end
+
+    create(index(:chat_message_references, [:chat_id, "id desc"]))
+  end
+end
diff --git a/priv/repo/migrations/20200603105113_add_unique_index_to_chat_message_references.exs b/priv/repo/migrations/20200603105113_add_unique_index_to_chat_message_references.exs
new file mode 100644
index 000000000..fdf85132e
--- /dev/null
+++ b/priv/repo/migrations/20200603105113_add_unique_index_to_chat_message_references.exs
@@ -0,0 +1,7 @@
+defmodule Pleroma.Repo.Migrations.AddUniqueIndexToChatMessageReferences do
+  use Ecto.Migration
+
+  def change do
+    create(unique_index(:chat_message_references, [:object_id, :chat_id]))
+  end
+end
diff --git a/priv/repo/migrations/20200603120448_remove_unread_from_chats.exs b/priv/repo/migrations/20200603120448_remove_unread_from_chats.exs
new file mode 100644
index 000000000..6322137d5
--- /dev/null
+++ b/priv/repo/migrations/20200603120448_remove_unread_from_chats.exs
@@ -0,0 +1,9 @@
+defmodule Pleroma.Repo.Migrations.RemoveUnreadFromChats do
+  use Ecto.Migration
+
+  def change do
+    alter table(:chats) do
+      remove(:unread, :integer, default: 0)
+    end
+  end
+end
diff --git a/priv/repo/migrations/20200603122732_add_seen_index_to_chat_message_references.exs b/priv/repo/migrations/20200603122732_add_seen_index_to_chat_message_references.exs
new file mode 100644
index 000000000..a5065d612
--- /dev/null
+++ b/priv/repo/migrations/20200603122732_add_seen_index_to_chat_message_references.exs
@@ -0,0 +1,12 @@
+defmodule Pleroma.Repo.Migrations.AddSeenIndexToChatMessageReferences do
+  use Ecto.Migration
+
+  def change do
+    create(
+      index(:chat_message_references, [:chat_id],
+        where: "seen = false",
+        name: "unseen_messages_count_index"
+      )
+    )
+  end
+end
diff --git a/priv/repo/migrations/20200604150318_migrate_seen_to_unread_in_chat_message_references.exs b/priv/repo/migrations/20200604150318_migrate_seen_to_unread_in_chat_message_references.exs
new file mode 100644
index 000000000..fd6bc7bc7
--- /dev/null
+++ b/priv/repo/migrations/20200604150318_migrate_seen_to_unread_in_chat_message_references.exs
@@ -0,0 +1,30 @@
+defmodule Pleroma.Repo.Migrations.MigrateSeenToUnreadInChatMessageReferences do
+  use Ecto.Migration
+
+  def change do
+    drop(
+      index(:chat_message_references, [:chat_id],
+        where: "seen = false",
+        name: "unseen_messages_count_index"
+      )
+    )
+
+    alter table(:chat_message_references) do
+      add(:unread, :boolean, default: true)
+    end
+
+    execute("update chat_message_references set unread = not seen")
+
+    alter table(:chat_message_references) do
+      modify(:unread, :boolean, default: true, null: false)
+      remove(:seen, :boolean, default: false, null: false)
+    end
+
+    create(
+      index(:chat_message_references, [:chat_id],
+        where: "unread = true",
+        name: "unread_messages_count_index"
+      )
+    )
+  end
+end
diff --git a/priv/repo/migrations/20200606105430_change_type_to_enum_for_notifications.exs b/priv/repo/migrations/20200606105430_change_type_to_enum_for_notifications.exs
new file mode 100644
index 000000000..9ea34436b
--- /dev/null
+++ b/priv/repo/migrations/20200606105430_change_type_to_enum_for_notifications.exs
@@ -0,0 +1,36 @@
+defmodule Pleroma.Repo.Migrations.ChangeTypeToEnumForNotifications do
+  use Ecto.Migration
+
+  def up do
+    """
+    create type notification_type as enum (
+      'follow',
+      'follow_request',
+      'mention',
+      'move',
+      'pleroma:emoji_reaction',
+      'pleroma:chat_mention',
+      'reblog',
+      'favourite'
+    )
+    """
+    |> execute()
+
+    """
+    alter table notifications 
+    alter column type type notification_type using (type::notification_type)
+    """
+    |> execute()
+  end
+
+  def down do
+    alter table(:notifications) do
+      modify(:type, :string)
+    end
+
+    """
+    drop type notification_type
+    """
+    |> execute()
+  end
+end
diff --git a/priv/repo/migrations/20200607112923_change_chat_id_to_flake.exs b/priv/repo/migrations/20200607112923_change_chat_id_to_flake.exs
new file mode 100644
index 000000000..f14e269ca
--- /dev/null
+++ b/priv/repo/migrations/20200607112923_change_chat_id_to_flake.exs
@@ -0,0 +1,23 @@
+defmodule Pleroma.Repo.Migrations.ChangeChatIdToFlake do
+  use Ecto.Migration
+
+  def up do
+    execute("""
+    alter table chats
+    drop constraint chats_pkey cascade,
+    alter column id drop default,
+    alter column id set data type uuid using cast( lpad( to_hex(id), 32, '0') as uuid),
+    add primary key (id)
+    """)
+
+    execute("""
+    alter table chat_message_references
+    alter column chat_id set data type uuid using cast( lpad( to_hex(chat_id), 32, '0') as uuid),
+    add constraint chat_message_references_chat_id_fkey foreign key (chat_id) references chats(id) on delete cascade
+    """)
+  end
+
+  def down do
+    :ok
+  end
+end