diff options
author | Dennis Schubert <mail@dennis-schubert.de> | 2021-02-13 05:53:04 +0300 |
---|---|---|
committer | Dennis Schubert <mail@dennis-schubert.de> | 2021-02-13 06:30:23 +0300 |
commit | a178e609079b4c7e98bfbc900acc5aa3fa057020 (patch) | |
tree | 3256a7092a54a4158bf70abe510f060d1fbc9f86 /db | |
parent | 5bb079831142ea3855f0858b813d75b35f09c865 (diff) |
Enforce lowercase tags and migrate existing uppercase tags for Postgres.
Diffstat (limited to 'db')
-rw-r--r-- | db/migrate/20210213000000_lowercaseify_tags.rb | 81 |
1 files changed, 81 insertions, 0 deletions
diff --git a/db/migrate/20210213000000_lowercaseify_tags.rb b/db/migrate/20210213000000_lowercaseify_tags.rb new file mode 100644 index 000000000..1df0354a9 --- /dev/null +++ b/db/migrate/20210213000000_lowercaseify_tags.rb @@ -0,0 +1,81 @@ +# frozen_string_literal: true + +class LowercaseifyTags < ActiveRecord::Migration[5.2] + # We have to disable the global transaction for migrations, otherwise we + # can't rescue from duplicates below. Checking if there are duplicates before + # running the update would make this migration even more complicated, so... + disable_ddl_transaction! + + # rubocop:disable Metrics/AbcSize, Metrics/MethodLength + def up + # MySQL is case-insensitive by default. However, using utf8mb4_bin, it is + # not. So, in theory, MySQL is affected by the same issue. Unfortunately, + # MySQL does not support function indexes, so we can't simply run an index + # on lower(name). The "solution" for MySQL is to add a generated column + # and triggers to update that column. + # + # Since MySQL is deprecated from our POV anyway, and no new pods should + # run MySQL - let's ignore this for now. + return unless AppConfig.postgres? + + # Get all tag names for tags that are currently not completely lowercase + affected_tags = query_first_field("SELECT DISTINCT lower(name) FROM tags WHERE lower(name) != name") + + Rails.logger.info "Merging duplicate tag entities for #{affected_tags.length} tags..." + affected_tags.each do |tag_name| + duplicate_tag_ids = query_first_field("SELECT id FROM tags WHERE lower(name) = '#{tag_name}' ORDER BY id ASC") + + # Take the lowest ID tag as the target + target_tag_id = duplicate_tag_ids.shift + + duplicate_tag_ids.each do |duplicate_tag| + # Note: Because things could be tagged with multiple cases of the same + # tag, we can't simply update all taggings - it might create a + # duplicate tagging violation... + # Instead, query each tagging, try to update, and if it's a duplicate, + # simply drop the tagging. + + # Do the aforementioned for taggings (posts, profiles, ...) + query_first_field("SELECT id FROM taggings WHERE tag_id = #{duplicate_tag}").each do |tagging_id| + begin + execute("UPDATE taggings SET tag_id = #{target_tag_id} WHERE id = #{tagging_id}") + rescue ActiveRecord::RecordNotUnique + execute("DELETE FROM taggings WHERE id = #{tagging_id}") + end + end + + # Do the aforementioned for tag_followings (i.e. tag streams) + query_first_field("SELECT id FROM tag_followings WHERE tag_id = #{duplicate_tag}").each do |tag_following_id| + begin + execute("UPDATE tag_followings SET tag_id = #{target_tag_id} WHERE id = #{tag_following_id}") + rescue ActiveRecord::RecordNotUnique + execute("DELETE FROM tag_followings WHERE id = #{tag_following_id}") + end + end + + # Delete the duplicate tag + execute("DELETE FROM tags WHERE id = #{duplicate_tag}") + end + + # Make sure the target tag is actually lowercase + execute("UPDATE tags SET name = '#{tag_name}' WHERE id = #{target_tag_id}") + + # Re-count the taggings. I don't think this matters for us, but... + ActsAsTaggableOn::Tag.reset_counters(target_tag_id, :taggings) + end + + # Finally, create the new, now unique, index on the lowercase'd tag name + add_index :tags, "lower(name)", name: "index_tags_on_lower_name", unique: true + end + # rubocop:enable Metrics/AbcSize, Metrics/MethodLength + + def down + remove_index :tags, name: "index_tags_on_lower_name" + end + + private + + def query_first_field(query) + execute(query).values.map {|row| row[0] } + end +end |