Created
January 12, 2015 15:08
-
-
Save dimaursu/312714276d86b4e1fe18 to your computer and use it in GitHub Desktop.
Crazy mysql migration
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| class SetMysqlToUnicodeMb4 < ActiveRecord::Migration | |
| # Converts the tables and strings columns to utf8mb4, which is the true, full | |
| # unicode support in MySQl | |
| UTF8_PAIRS = Hash.new { |h,k| h[k] = [] } | |
| tables = ActiveRecord::Base.connection.tables | |
| tables.each do |table| | |
| ActiveRecord::Base.connection.columns(table).each do |column| | |
| # build a hash with all the columns that contain characters | |
| if (column.type == :string) || (column.type == :text) | |
| UTF8_PAIRS[table] << { :name => column.name, :type => column.sql_type } | |
| end | |
| end | |
| end | |
| def self.up | |
| # shorten indexes regardless of the RDBMS provider - for consitency | |
| shorten_indexes(); | |
| change_encoding('utf8mb4') if AppConfig.mysql? | |
| end | |
| def self.down | |
| change_encoding('utf8') if AppConfig.mysql? | |
| end | |
| def change_encoding(encoding) | |
| execute "ALTER DATABASE `#{ActiveRecord::Base.connection.current_database}` CHARACTER SET #{encoding};" | |
| tables.each do |table| | |
| execute "ALTER TABLE `#{table}` CHARACTER SET = #{encoding}" | |
| end | |
| UTF8_PAIRS.each do |table, columns| | |
| columns.each do |column| | |
| name = column[:name] | |
| type = column[:type] | |
| execute "ALTER TABLE `#{table}` CHANGE `#{name}` `#{name}` #{type} CHARACTER SET #{encoding} NULL;" | |
| end | |
| end | |
| end | |
| def shorten_indexes | |
| require 'pry' | |
| indexes = UTF8_PAIRS.collect do |table, columns| | |
| ActiveRecord::Base.connection.indexes(table).collect do |index| | |
| # compute the length of the new index < 767 bytes, which is a MySQL | |
| # limitation on index column length, or 191 chars, 4byte each | |
| width = 191 | |
| # index.columns -> ["invitation_service", "invitation_identifier"] | |
| non_char_columns = index.columns.select do |icolumn| | |
| columns.select { |column| icolumn == column[:name]} | |
| end | |
| width -= non_char_columns.length | |
| # column width | |
| width /= columns.length | |
| col_lengths = {} | |
| columns.each {|column| col_lengths[column[:name]] = width } | |
| { | |
| :name => index.name, | |
| :table => index.table, | |
| :columns => index.columns, | |
| :col_lengths => col_lengths | |
| } | |
| end | |
| end | |
| indexes.flatten.each do |idx| | |
| remove_index idx[:table], idx[:name] | |
| add_index idx[:table], idx[:columns], :name => idx[:name], :length => idx[:col_lengths], using: :btree | |
| end | |
| end | |
| end |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment