Skip to content

Instantly share code, notes, and snippets.

@salimane

salimane/db_convert_to_utf8mb4.rake

Last active August 29, 2015 14:12
Show Gist options
  • Save salimane/3307b962ac1036dd5a2e to your computer and use it in GitHub Desktop.
Save salimane/3307b962ac1036dd5a2e to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. salimane revised this gist Dec 30, 2014. 1 changed file with 12 additions and 9 deletions.
    21 changes: 12 additions & 9 deletions db_convert_to_utf8mb4.rake
    View file
    Original file line number Diff line number Diff line change
    @@ -14,13 +14,13 @@ NON_STANDARD_TABLES_MODELS = {
    }.with_indifferent_access

    namespace :db do
    desc "Convert any MySQL database into general utf8"
    task :convert_to_utf8 => :environment 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
    desc 'Convert any MySQL 5.5.3+ database into real utf8 (utf8mb4)'
    task convert_to_utf8mb4: :environment do
    convert('utf8mb4')
    end
    end
    @@ -31,7 +31,7 @@ def convert(collate = 'utf8')
    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
    tables = ENV['CONVERT_TABLES'] ? ENV['CONVERT_TABLES'].split(',') : (conn.tables - IGNORED_TABLES)

    if dryrun
    def conn.run_sql(sql)
    @@ -66,7 +66,6 @@ def table_sqls(conn, tables = [], collate = 'utf8')
    results = {}

    tables.each do |tbl|

    a = ["CONVERT TO CHARACTER SET #{collate} COLLATE #{collate}_bin"]
    b = []

    @@ -80,16 +79,20 @@ def table_sqls(conn, tables = [], collate = 'utf8')

    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(#{col.limit})"
    b << "CHANGE #{conn.quote_column_name(col.name)} #{conn.quote_column_name(col.name)} VARCHAR(#{col.limit}) CHARACTER SET #{collate} COLLATE #{collate}_bin#{nullable}#{default}"
    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(#{col.limit}) CHARACTER SET #{collate} COLLATE #{collate}_bin#{nullable}#{default}"
    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
  2. salimane revised this gist Dec 29, 2014. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions db_convert_to_utf8mb4.rake
    View file
    Original file line number Diff line number Diff line change
    @@ -14,12 +14,12 @@ NON_STANDARD_TABLES_MODELS = {
    }.with_indifferent_access

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

    desc "convert any mysql5.5.3+ database into proper 'mysql utf8' which is utf8mb4"
    desc "Convert any MySQL 5.5.3+ database into real utf8 (utf8mb4)"
    task :convert_to_utf8mb4 => :environment do
    convert('utf8mb4')
    end
  3. salimane created this gist Dec 29, 2014.
    106 changes: 106 additions & 0 deletions db_convert_to_utf8mb4.rake
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,106 @@
    # 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 mysql5.5.3+ database into proper 'mysql utf8' which is 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}'" : ''

    case type
    when /varchar/
    a << "CHANGE #{conn.quote_column_name(col.name)} #{conn.quote_column_name(col.name)} VARBINARY(#{col.limit})"
    b << "CHANGE #{conn.quote_column_name(col.name)} #{conn.quote_column_name(col.name)} VARCHAR(#{col.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(#{col.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