Create `f_normalize` function to transform filter queries. Also make the FTS migration an actual migration to save new Monsterfork admins the headache.

2 files changed, 30 insertions, 19 deletions

diff --git a/db/migrate/20191118084127_migrate_to_new_search_impl.rb b/db/migrate/20191118084127_migrate_to_new_search_impl.rb
new file mode 100644
index 000000000..b068443ca
--- /dev/null
+++ b/db/migrate/20191118084127_migrate_to_new_search_impl.rb
@@ -0,0 +1,28 @@
+class MigrateToNewSearchImpl < ActiveRecord::Migration[5.2]
+  disable_ddl_transaction!
+
+  def up
+    safety_assured {
+      execute 'DROP TRIGGER IF EXISTS tsvectorinsert ON statuses'
+      execute 'DROP TRIGGER IF EXISTS tsvectorupdate ON statuses'
+      execute 'DROP FUNCTION IF EXISTS tsv_update_trigger'
+      execute 'DROP INDEX IF EXISTS tsv_idx'
+      execute 'ALTER TABLE statuses DROP COLUMN IF EXISTS tsv'
+      execute 'DROP INDEX IF EXISTS index_statuses_on_text_trgm'
+      execute 'DROP INDEX IF EXISTS index_statuses_on_spoiler_text_trgm'
+      execute <<-SQL.squish
+        CREATE OR REPLACE FUNCTION public.f_normalize(text)
+          RETURNS text LANGUAGE sql PARALLEL SAFE STRICT AS
+            $func$
+              SELECT REGEXP_REPLACE(LOWER(unaccent($1)), '"(.*)"', '\\\\y\\1\\\\y')
+            $func$
+      SQL
+      execute 'CREATE INDEX CONCURRENTLY IF NOT EXISTS index_statuses_on_normalized_text_trgm ON statuses USING GIN (normalized_text gin_trgm_ops)'
+    }
+  end
+
+  def down
+    #raise ActiveRecord::IrreversibleMigration
+    true
+  end
+end
diff --git a/dist/search.sql b/dist/search.sql
index e68b3c9b3..f80ca049c 100644
--- a/dist/search.sql
+++ b/dist/search.sql
@@ -1,22 +1,5 @@
--- Run this section on Mastodon DB as Postgres superuser. --
+-- Before running db:migrate, run this on Mastodon DB as Postgres superuser. --
 -- sudo -sHu postgres -- psql mastodon_production
 
 CREATE EXTENSION pg_trgm;
-
--- Run this section on Mastodon DB as Mastodon user. --
--- sudo -sHu mastodon -- psql mastodon_production
-
--- Drop old FTS implementation --
-DROP TRIGGER IF EXISTS tsvectorinsert ON statuses;
-DROP TRIGGER IF EXISTS tsvectorupdate ON statuses;
-DROP FUNCTION IF EXISTS tsv_update_trigger;
-DROP INDEX IF EXISTS tsv_idx;
-ALTER TABLE statuses DROP COLUMN IF EXISTS tsv;
-DROP INDEX IF EXISTS index_statuses_on_text_trgm;
-DROP INDEX IF EXISTS index_statuses_on_spoiler_text_trgm;
-
--- Create new trigram indexes --
-CREATE INDEX CONCURRENTLY IF NOT EXISTS index_statuses_on_normalized_text_trgm ON statuses USING GIN (normalized_text gin_trgm_ops);
-
--- Compact tables ---
-VACUUM ANALYZE;
+CREATE EXTENSION unaccent;