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     drop_if_exists index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts)
   7     alter table(:objects) do
   8       add(:fts_content, :tsvector)
   9     end
  10
  11     execute("CREATE FUNCTION objects_fts_update() RETURNS trigger AS $$
  12     begin
  13       new.fts_content := to_tsvector('english', new.data->>'content');
  14       return new;
  15     end
  16     $$ LANGUAGE plpgsql")
  17     execute("create index objects_fts on objects using RUM (fts_content rum_tsvector_addon_ops, inserted_at) with (attach = 'inserted_at', to = 'fts_content');")
  18
  19     execute("CREATE TRIGGER tsvectorupdate BEFORE INSERT OR UPDATE ON objects
  20     FOR EACH ROW EXECUTE PROCEDURE objects_fts_update()")
  21
  22     execute("UPDATE objects SET updated_at = NOW()")
  23   end
  24
  25   def down do
  26     execute "drop index objects_fts"
  27     execute "drop trigger tsvectorupdate on objects"
  28     execute "drop function objects_fts_update()"
  29     alter table(:objects) do
  30       remove(:fts_content, :tsvector)
  31     end
  32     create index(:objects, ["(to_tsvector('english', data->>'content'))"], using: :gin, name: :objects_fts)
  33   end
  34 end