From 158369f367a4a29eb960a7489939c5e13c33dc0b Mon Sep 17 00:00:00 2001
From: multiple creatures <dev@multiple-creature.party>
Date: Mon, 18 Nov 2019 03:30:52 -0600
Subject: Create `f_normalize` function to transform filter queries.  Also make
 the FTS migration an actual migration to save new Monsterfork admins the
 headache.

---
 .../20191118084127_migrate_to_new_search_impl.rb   | 28 ++++++++++++++++++++++
 1 file changed, 28 insertions(+)
 create mode 100644 db/migrate/20191118084127_migrate_to_new_search_impl.rb

(limited to 'db')

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
-- 
cgit