switch (back) to postgres fts engine for fast search & timeline filters

1 files changed, 42 insertions, 0 deletions

diff --git a/db/migrate/20200109191740_migrate_back_to_fts.rb b/db/migrate/20200109191740_migrate_back_to_fts.rb
new file mode 100644
index 000000000..dd98f7649
--- /dev/null
+++ b/db/migrate/20200109191740_migrate_back_to_fts.rb
@@ -0,0 +1,42 @@
+class MigrateBackToFts < ActiveRecord::Migration[5.2]
+  def up
+    if table_exists? :normalized_statuses
+      remove_index :normalized_statuses, name: 'index_statuses_on_normalized_text_trgm'
+      drop_table :normalized_statuses
+    end
+
+    safety_assured do
+      execute <<-SQL.squish
+        DROP FUNCTION IF EXISTS public.f_normalize;
+        DROP FUNCTION IF EXISTS public.f_unaccent;
+
+        CREATE OR REPLACE FUNCTION public.f_strip_mentions(text)
+          RETURNS text LANGUAGE sql IMMUTABLE PARALLEL SAFE STRICT AS
+          $func$
+            SELECT regexp_replace(
+              regexp_replace($1, '</?span>', '', 'g'),
+              '>@[^[:space:]]+<', '><', 'g'
+            )
+          $func$;
+
+        CREATE OR REPLACE AGGREGATE tsquery_union(tsquery) (
+          SFUNC = tsquery_or,
+          STYPE = tsquery,
+          PARALLEL = SAFE
+        );
+
+        CREATE TEXT SEARCH CONFIGURATION fedi ( COPY = simple );
+
+        ALTER TEXT SEARCH CONFIGURATION fedi
+          ALTER MAPPING FOR hword, hword_part, word
+            WITH unaccent, simple;
+
+        ALTER TABLE statuses
+          ADD COLUMN tsv tsvector
+          GENERATED ALWAYS AS (
+            to_tsvector('fedi', f_strip_mentions(spoiler_text || ' ' || text))
+          ) STORED;
+      SQL
+    end
+  end
+end