Skip to content

Instantly share code, notes, and snippets.

@salimane
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.
Convert any MySQL 5.5.3+ database into real utf8 (utf8mb4) in Rails - RUN=1 rake db:convert_to_utf8mb4
# 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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment