move normalized text into own table

4 files changed, 46 insertions, 5 deletions

diff --git a/db/migrate/20191118084127_migrate_to_new_search_impl.rb b/db/migrate/20191118084127_migrate_to_new_search_impl.rb
index b068443ca..52a50af36 100644
--- a/db/migrate/20191118084127_migrate_to_new_search_impl.rb
+++ b/db/migrate/20191118084127_migrate_to_new_search_impl.rb
@@ -22,7 +22,6 @@ class MigrateToNewSearchImpl < ActiveRecord::Migration[5.2]
   end
 
   def down
-    #raise ActiveRecord::IrreversibleMigration
-    true
+    raise ActiveRecord::IrreversibleMigration
   end
 end
diff --git a/db/migrate/20191211235208_create_normalized_statuses.rb b/db/migrate/20191211235208_create_normalized_statuses.rb
new file mode 100644
index 000000000..9baaa3f62
--- /dev/null
+++ b/db/migrate/20191211235208_create_normalized_statuses.rb
@@ -0,0 +1,23 @@
+class CreateNormalizedStatuses < ActiveRecord::Migration[5.2]
+  def up
+    create_table :normalized_statuses do |t|
+      t.references :status, foreign_key: true
+      t.text :text
+    end
+
+    safety_assured do
+      remove_index :statuses, name: 'index_statuses_on_normalized_text_trgm'
+      execute 'INSERT INTO normalized_statuses (status_id, text) SELECT id, normalized_text FROM statuses'
+      remove_column :statuses, :normalized_text
+    end
+  end
+
+  def down
+    safety_assured do
+      execute 'UPDATE statuses SET normalized_text = s.text FROM (SELECT status_id, text FROM normalized_statuses) AS s WHERE statuses.id = s.id'
+      remove_index :normalized_statuses, name: 'index_statuses_on_normalized_text_trgm'
+      drop_table :normalized_statuses
+      add_column :statuses, :normalized_text, :text, null: false, default: ''
+    end
+  end
+end
diff --git a/db/migrate/20191212002705_add_index_to_normalized_statuses.rb b/db/migrate/20191212002705_add_index_to_normalized_statuses.rb
new file mode 100644
index 000000000..7a9dce39a
--- /dev/null
+++ b/db/migrate/20191212002705_add_index_to_normalized_statuses.rb
@@ -0,0 +1,13 @@
+class AddIndexToNormalizedStatuses < ActiveRecord::Migration[5.2]
+  disable_ddl_transaction!
+
+  def up
+    safety_assured do
+      execute 'CREATE INDEX CONCURRENTLY IF NOT EXISTS index_statuses_on_normalized_text_trgm ON normalized_statuses USING GIN (text gin_trgm_ops)'
+    end
+  end
+
+  def down
+    remove_index :normalized_statuses, name: 'index_statuses_on_normalized_text_trgm'
+  end
+end
diff --git a/db/schema.rb b/db/schema.rb
index 63897a0e7..96e9148ed 100644
--- a/db/schema.rb
+++ b/db/schema.rb
@@ -10,7 +10,7 @@
 #
 # It's strongly recommended that you check this file into your version control system.
 
-ActiveRecord::Schema.define(version: 2019_11_18_102858) do
+ActiveRecord::Schema.define(version: 2019_12_12_002705) do
 
   # These are extensions that must be enabled in order to support this database
   enable_extension "pg_trgm"
@@ -432,6 +432,13 @@ ActiveRecord::Schema.define(version: 2019_11_18_102858) do
     t.index ["target_account_id"], name: "index_mutes_on_target_account_id"
   end
 
+  create_table "normalized_statuses", force: :cascade do |t|
+    t.bigint "status_id"
+    t.text "text"
+    t.index ["status_id"], name: "index_normalized_statuses_on_status_id"
+    t.index ["text"], name: "index_statuses_on_normalized_text_trgm", opclass: :gin_trgm_ops, using: :gin
+  end
+
   create_table "notifications", force: :cascade do |t|
     t.bigint "activity_id", null: false
     t.string "activity_type", null: false
@@ -689,13 +696,11 @@ ActiveRecord::Schema.define(version: 2019_11_18_102858) do
     t.string "origin"
     t.boolean "boostable"
     t.boolean "reject_replies"
-    t.text "normalized_text", default: "", null: false
     t.index ["account_id", "id", "visibility", "updated_at"], name: "index_statuses_20180106", order: { id: :desc }
     t.index ["account_id", "id", "visibility"], name: "index_statuses_on_account_id_and_id_and_visibility", order: { id: :desc }, where: "(visibility = ANY (ARRAY[0, 1, 2, 4]))"
     t.index ["in_reply_to_account_id"], name: "index_statuses_on_in_reply_to_account_id"
     t.index ["in_reply_to_id"], name: "index_statuses_on_in_reply_to_id"
     t.index ["network"], name: "index_statuses_on_network", where: "network"
-    t.index ["normalized_text"], name: "index_statuses_on_normalized_text_trgm", opclass: :gin_trgm_ops, using: :gin
     t.index ["origin"], name: "index_statuses_on_origin", unique: true
     t.index ["reblog_of_id", "account_id"], name: "index_statuses_on_reblog_of_id_and_account_id"
     t.index ["uri"], name: "index_statuses_on_uri", unique: true
@@ -860,6 +865,7 @@ ActiveRecord::Schema.define(version: 2019_11_18_102858) do
   add_foreign_key "mentions", "statuses", on_delete: :cascade
   add_foreign_key "mutes", "accounts", column: "target_account_id", name: "fk_eecff219ea", on_delete: :cascade
   add_foreign_key "mutes", "accounts", name: "fk_b8d8daf315", on_delete: :cascade
+  add_foreign_key "normalized_statuses", "statuses"
   add_foreign_key "notifications", "accounts", column: "from_account_id", name: "fk_fbd6b0bf9e", on_delete: :cascade
   add_foreign_key "notifications", "accounts", name: "fk_c141c8ee55", on_delete: :cascade
   add_foreign_key "oauth_access_grants", "oauth_applications", column: "application_id", name: "fk_34d54b0a33", on_delete: :cascade