git.squeep.com Git - akkoma/blob - priv/repo/optional_migrations/rum_indexing/20190510135645_add_fts_index_to_objects_two.exs

   1 defmodule Pleroma.Repo.Migrations.AddFtsIndexToObjectsTwo do
   2   use Ecto.Migration
   3
   4   def up do
   5     execute("create extension if not exists rum")
   6
   7     drop_if_exists(
   8       index(:objects, ["(to_tsvector('english', data->>'content'))"],
   9         using: :gin,
  10         name: :objects_fts
  11       )
  12     )
  13
  14     alter table(:objects) do
  15       add(:fts_content, :tsvector)
  16     end
  17
  18     execute("CREATE FUNCTION objects_fts_update() RETURNS trigger AS $$
  19     begin
  20     new.fts_content := to_tsvector(new.data->>'content');
  21     return new;
  22     end
  23     $$ LANGUAGE plpgsql")
  24
  25     execute(
  26       "create index if not exists objects_fts on objects using RUM (fts_content rum_tsvector_addon_ops, inserted_at) with (attach = 'inserted_at', to = 'fts_content');"
  27     )
  28
  29     execute("CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON objects
  30     FOR EACH ROW EXECUTE PROCEDURE objects_fts_update()")
  31
  32     execute("UPDATE objects SET updated_at = NOW()")
  33   end
  34
  35   def down do
  36     execute("drop index if exists objects_fts")
  37     execute("drop trigger if exists tsvectorupdate on objects")
  38     execute("drop function if exists objects_fts_update()")
  39
  40     alter table(:objects) do
  41       remove(:fts_content, :tsvector)
  42     end
  43
  44     create_if_not_exists(
  45       index(:objects, ["(to_tsvector('english', data->>'content'))"],
  46         using: :gin,
  47         name: :objects_fts
  48       )
  49     )
  50   end
  51 end