FIX: CF to table migration was incorrect (#171)

FIX: CF to table migration was incorrect (#171)

The order of values in the INSERT query was invalid (assigned_to_id and topic_id were swapped)

Co-authored-by: David Taylor david@taylorhq.com

diff --git a/db/post_migrate/20210709101534_move_assignments_from_custom_fields_to_a_table.rb b/db/post_migrate/20210709101534_move_assignments_from_custom_fields_to_a_table.rb
index d2db7ae..a7a58db 100644
--- a/db/post_migrate/20210709101534_move_assignments_from_custom_fields_to_a_table.rb
+++ b/db/post_migrate/20210709101534_move_assignments_from_custom_fields_to_a_table.rb
@@ -2,18 +2,7 @@
 
 class MoveAssignmentsFromCustomFieldsToATable < ActiveRecord::Migration[6.1]
   def up
-    execute <<~SQL
-      INSERT INTO assignments (topic_id, assigned_by_user_id, assigned_to_id, created_at, updated_at)
-      SELECT (
-        SELECT value::integer assigned_to_id
-        FROM topic_custom_fields tcf1
-        WHERE tcf1.name = 'assigned_to_id' AND tcf1.topic_id = tcf2.topic_id
-      ), value::integer assgined_by_id, topic_id, created_at, updated_at
-      FROM topic_custom_fields tcf2
-      WHERE name = 'assigned_by_id'
-      ORDER BY created_at DESC
-      ON CONFLICT DO NOTHING
-    SQL
+    # No-op, this migration was invalid
   end
 
   def down
diff --git a/db/post_migrate/20210714173022_correctly_move_assignments_from_custom_fields_to_a_table.rb b/db/post_migrate/20210714173022_correctly_move_assignments_from_custom_fields_to_a_table.rb
new file mode 100644
index 0000000..b44226f
--- /dev/null
+++ b/db/post_migrate/20210714173022_correctly_move_assignments_from_custom_fields_to_a_table.rb
@@ -0,0 +1,32 @@
+# frozen_string_literal: true
+
+class CorrectlyMoveAssignmentsFromCustomFieldsToATable < ActiveRecord::Migration[6.1]
+  def up
+    # An old version of 20210709101534 incorrectly imported `assignments` with
+    # the topic_id and assigned_to_id columns flipped. This query deletes those invalid records.
+    execute <<~SQL
+      DELETE FROM assignments USING topic_custom_fields
+      WHERE
+        assignments.assigned_to_id = topic_custom_fields.topic_id
+        AND assignments.topic_id = topic_custom_fields.value::integer
+        AND topic_custom_fields.name = 'assigned_to_id'
+    SQL
+
+    execute <<~SQL
+      INSERT INTO assignments (assigned_to_id, assigned_by_user_id, topic_id, created_at, updated_at)
+      SELECT (
+        SELECT value::integer assigned_to_id
+        FROM topic_custom_fields tcf1
+        WHERE tcf1.name = 'assigned_to_id' AND tcf1.topic_id = tcf2.topic_id
+      ), value::integer assigned_by_id, topic_id, created_at, updated_at
+      FROM topic_custom_fields tcf2
+      WHERE name = 'assigned_by_id'
+      ORDER BY created_at DESC
+      ON CONFLICT DO NOTHING
+    SQL
+  end
+
+  def down
+    raise ActiveRecord::IrreversibleMigration
+  end
+end

GitHub sha: b0a480fd357590656da05707115b4408bab7e791

This commit appears in #171 which was approved by davidtaylorhq. It was merged by CvX.