Fix database encoding

fix_encoding.rb

require 'mysql2'

SIMULATE = true
DB_HOST  = "localhost"
DB_NAME  = "database"
DB_USER  = "root"
DB_PASS  = "password"

@client = Mysql2::Client.new(
                  :host     => DB_HOST,
                  :username => DB_USER,
                  :password => DB_PASS,
                  :database => DB_NAME,
                  :encoding => "UTF8")

@binary_client = Mysql2::Client.new(
                  :host     => DB_HOST,
                  :username => DB_USER,
                  :password => DB_PASS,
                  :database => DB_NAME,
                  :encoding => "BINARY")

@inf_schema_client = Mysql2::Client.new(
                  :host     => DB_HOST,
                  :username => DB_USER,
                  :password => DB_PASS,
                  :database => "information_schema")

@row_count = 0
@column_count = 0
@table_count = 0
@failed_queries = []

def fix_column(table_name, column_name, column_type)
  results = @binary_client.query("SELECT id, `#{column_name}` FROM `#{table_name}`")
  new_row = {}
  results.each do |result|
    if result[column_name] != nil
      id = result["id"]
      unicode_version = result[column_name].force_encoding("UTF-8")
      if unicode_version.bytes.count > unicode_version.length
        new_row[id] = unicode_version
      end
    end
  end

  change_encoding = "ALTER TABLE `#{table_name}` CHANGE `#{column_name}` `#{column_name}` #{column_type} CHARACTER SET utf8  NULL  DEFAULT NULL"
  puts change_encoding
  @client.query(change_encoding) unless SIMULATE
  @column_count += 1

  new_row.each do |id, new_value|
    begin
      sql = "UPDATE `#{table_name}` SET `#{column_name}`='#{@client.escape(new_value)}' WHERE id=#{id}"
      puts sql
      @client.query(sql) unless SIMULATE
      @row_count += 1
    rescue Exception => e
      @failed_queries.push sql
    end
  end
end

def fix_columns
  columns = @inf_schema_client.query("SELECT * FROM `COLUMNS` WHERE `TABLE_SCHEMA` = '#{DB_NAME}' AND `character_set_name` = 'latin1'")

  columns.each do |column|
    fix_column(column["TABLE_NAME"], column["COLUMN_NAME"], column["COLUMN_TYPE"]) unless column["TABLE_NAME"] == "schema_migrations"
  end
end

def fix_tables
  results = @inf_schema_client.query("SELECT * FROM `TABLES` WHERE `TABLE_SCHEMA` = '#{DB_NAME}'")

  results.each do |result|
    sql = "ALTER TABLE `#{result["TABLE_NAME"]}` CHARACTER SET = utf8"
    puts sql
    @client.query(sql) unless SIMULATE
    @table_count += 1
  end
end

def fix_db
  sql = "ALTER DATABASE CHARACTER SET 'utf8'"
  puts sql
  @client.query(sql) unless SIMULATE
end

fix_columns
fix_tables
fix_db


puts 
puts "Only a simulation, relax" if SIMULATE
puts "Rows changed:    " + @row_count.to_s
puts "Columns changed: " + @column_count.to_s
puts "Tables changed:  " + @table_count.to_s
puts "Failed queries:" unless SIMULATE
puts @failed_queries