convert_db_to_utf8.rake

# Why 'utf8_unicode_ci'? Read http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
THE_ONE_TRUE_COLLATION = 'utf8_unicode_ci'

task convert_db_to_utf8: :environment do
  c = ActiveRecord::Base.connection

  database_name = Rails.configuration.database_configuration[Rails.env]['database']

  puts "Setting default for new tables for #{database_name.inspect}..."
  c.execute("ALTER DATABASE `#{database_name}` CHARACTER SET `utf8` COLLATE `#{THE_ONE_TRUE_COLLATION}`")

  table_status = c.execute("SHOW TABLE STATUS")
  table_status.each(as: :hash) do |t|
    next if t['Name'].start_with?('lhm')

    needs_correct_default = false
    need_conversion = false

    if t['Collation'] != THE_ONE_TRUE_COLLATION
      needs_correct_default = true
    end

    c.execute("SHOW FULL COLUMNS FROM `#{t['Name']}`").each(as: hash) do |c|
      next if c['Collation'].nil?
      if c['Collation'] != THE_ONE_TRUE_COLLATION
        need_conversion = true
        break
      end
    end

    next if !needs_correct_default && !need_conversion

    puts "Altering #{t['Name']}..."
    Lhm.change_table t['Name'] do |m|
      if needs_correct_default
        puts "Setting default new columns for for #{m.name.inspect} from #{t['Collation'].inspect} to #{THE_ONE_TRUE_COLLATION.inspect}..."
        m.ddl "ALTER TABLE `#{m.name}` CHARACTER SET `utf8` COLLATE `#{THE_ONE_TRUE_COLLATION}`"
      end
      if need_conversion
        puts "Converting char set and collation for #{m.name.inspect}..."
        m.ddl "ALTER TABLE `#{m.name}` CONVERT TO CHARACTER SET `utf8` COLLATE `#{THE_ONE_TRUE_COLLATION}`"
      end
    end
  end
end

task check_all_columns: :environment do
  c = ActiveRecord::Base.connection

  c.tables.each do |table_name|
    next if table_name.start_with?('lhm')

    bad_columns = []
    c.execute("SHOW FULL COLUMNS FROM `#{table_name}`").each(as: hash) do |c|
      next if c['Collation'].nil?
      bad_columns << c['Collation'] if c['Collation'] != THE_ONE_TRUE_COLLATION
    end
    unless bad_columns.empty?
      puts "#{table_name.inspect} has columns with wrong collation #{bad_columns.uniq.inspect}"
    end
  end
end