Convert any MySQL 5.5.3+ database into real utf8 (utf8mb4) in Rails - RUN=1 rake db:convert_to_utf8mb4

db_convert_to_utf8mb4.rake

# The tables that should be ignored
IGNORED_TABLES = %w()
# The tables should have their charset changed,
# but with no asociated AR model, thus columns will not be modified
TABLES_WITHOUT_MODELS = %w(
  oauth_access_grants
  oauth_access_tokens
  oauth_applications
  schema_migrations
)
# The tables that don't follow rails for their model names
NON_STANDARD_TABLES_MODELS = {
  # :table_name => ModelName
}.with_indifferent_access

namespace :db do
  desc 'Convert any MySQL database into general utf8'
  task convert_to_utf8: :environment do
    convert('utf8')
  end

  desc 'Convert any MySQL 5.5.3+ database into real utf8 (utf8mb4)'
  task convert_to_utf8mb4: :environment do
    convert('utf8mb4')
  end
end

def convert(collate = 'utf8')
  puts Time.now
  dryrun = ENV['RUN'] != '1'
  conn = ActiveRecord::Base.connection
  database_name = Rails.configuration.database_configuration[Rails.env]['database']

  tables = ENV['CONVERT_TABLES'] ? ENV['CONVERT_TABLES'].split(',') : (conn.tables - IGNORED_TABLES)

  if dryrun
    def conn.run_sql(sql)
      puts(sql)
    end
  else
    def conn.run_sql(sql)
      # puts(sql)
      ActiveRecord::Base.transaction do
        execute(sql)
      end
    end
  end

  # convert database
  puts("Converting Database: #{database_name} to #{collate}")
  conn.run_sql "ALTER DATABASE #{database_name} CHARACTER SET = #{collate} collate = #{collate}_bin"
  # convert tables
  table_sqls(conn, tables, collate).each do |tbl, sqls|
    puts("Converting table: #{tbl}")
    sqls.each do |sql|
      conn.run_sql sql
    end
  end

  puts Time.now
  puts 'Done!'
end

def table_sqls(conn, tables = [], collate = 'utf8')
  return {} if tables.empty?
  results = {}

  tables.each do |tbl|
    a = ["CONVERT TO CHARACTER SET #{collate} COLLATE #{collate}_bin"]
    b = []

    unless TABLES_WITHOUT_MODELS.include? tbl
      model = NON_STANDARD_TABLES_MODELS[tbl] || tbl.to_s.classify.constantize
      model.connection.schema_cache.clear!
      model.reset_column_information

      model.columns.each do |col|
        type = col.sql_type

        nullable = col.null ? '' : ' NOT NULL'
        default = col.default ? " DEFAULT '#{col.default}'" : ''
        limit = col.limit
        if type =~ /varchar/ || type =~ /varbinary/
          limit = limit <= 191 ? limit : 191 if collate == 'utf8mb4'
        end

        case type
        when /varchar/
          a << "CHANGE #{conn.quote_column_name(col.name)} #{conn.quote_column_name(col.name)} VARBINARY(#{limit})"
          b << "CHANGE #{conn.quote_column_name(col.name)} #{conn.quote_column_name(col.name)} VARCHAR(#{limit}) CHARACTER SET #{collate} COLLATE #{collate}_bin#{nullable}#{default}"
        when /text/
          a << "CHANGE #{conn.quote_column_name(col.name)} #{conn.quote_column_name(col.name)} BLOB"
          b << "CHANGE #{conn.quote_column_name(col.name)} #{conn.quote_column_name(col.name)} TEXT CHARACTER SET #{collate} COLLATE #{collate}_bin#{nullable}#{default}"
        when /varbinary/
          b << "CHANGE #{conn.quote_column_name(col.name)} #{conn.quote_column_name(col.name)} VARCHAR(#{limit}) CHARACTER SET #{collate} COLLATE #{collate}_bin#{nullable}#{default}"
        when /blob/
          b << "CHANGE #{conn.quote_column_name(col.name)} #{conn.quote_column_name(col.name)} TEXT CHARACTER SET #{collate} COLLATE #{collate}_bin#{nullable}#{default}"
        end
      end
    end

    # convert table and columns
    results[tbl] = [] if results[tbl].nil?
    results[tbl] << "ALTER TABLE #{tbl} #{a.join(', ')}" if a.present?
    results[tbl] << "ALTER TABLE #{tbl} #{b.join(', ')}" if b.present?
  end

  results
end