FIX: Add migrations to fix index on category slugs

FIX: Add migrations to fix index on category slugs

Slugs can be the empty string, but the added index didn’t account for that. This commit changes the migration, stopping it from being unique so that it can be applied everywhere and adds another migration that recreates the index properly.

diff --git a/app/models/category.rb b/app/models/category.rb
index db500e6ff6..2dcdd7d1e8 100644
--- a/app/models/category.rb
+++ b/app/models/category.rb
@@ -889,6 +889,6 @@ end
 #  index_categories_on_reviewable_by_group_id  (reviewable_by_group_id)
 #  index_categories_on_search_priority         (search_priority)
 #  index_categories_on_topic_count             (topic_count)
-#  unique_index_categories_on_name             (COALESCE(parent_category_id, '-1'::integer), name) UNIQUE
-#  unique_index_categories_on_slug             (COALESCE(parent_category_id, '-1'::integer), slug) UNIQUE
+#  unique_index_categories_on_name             ((COALESCE(parent_category_id, '-1'::integer)), name) UNIQUE
+#  unique_index_categories_on_slug             ((COALESCE(parent_category_id, '-1'::integer)), slug) UNIQUE WHERE ((slug)::text <> ''::text)
 #
diff --git a/db/migrate/20191008124357_add_unique_index_categories_on_slug.rb b/db/migrate/20191008124357_add_unique_index_categories_on_slug.rb
index 5ee97c6482..7c9dad67e3 100644
--- a/db/migrate/20191008124357_add_unique_index_categories_on_slug.rb
+++ b/db/migrate/20191008124357_add_unique_index_categories_on_slug.rb
@@ -5,8 +5,7 @@ class AddUniqueIndexCategoriesOnSlug < ActiveRecord::Migration[6.0]
     add_index(
       :categories,
       'COALESCE(parent_category_id, -1), slug',
-      name: 'unique_index_categories_on_slug',
-      unique: true
+      name: 'unique_index_categories_on_slug'
     )
   end
 end
diff --git a/db/migrate/20191016124059_fix_category_slugs_index.rb b/db/migrate/20191016124059_fix_category_slugs_index.rb
new file mode 100644
index 0000000000..58b67b6fe6
--- /dev/null
+++ b/db/migrate/20191016124059_fix_category_slugs_index.rb
@@ -0,0 +1,15 @@
+# frozen_string_literal: true
+
+class FixCategorySlugsIndex < ActiveRecord::Migration[6.0]
+  def change
+    remove_index(:categories, name: 'unique_index_categories_on_slug')
+
+    add_index(
+      :categories,
+      'COALESCE(parent_category_id, -1), slug',
+      name: 'unique_index_categories_on_slug',
+      where: "slug != ''",
+      unique: true
+    )
+  end
+end

GitHub sha: 7ba914f1

This commit has been mentioned on Discourse Meta. There might be relevant details there: