myers · July 24, 2014 18:19 · Jul 24, 2014
diff --git a/convert_db_to_utf8.rake b/convert_db_to_utf8.rake
@@ -0,0 +1,62 @@
+# Why 'utf8_unicode_ci'? Read http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci
+THE_ONE_TRUE_COLLATION = 'utf8_unicode_ci'
+
+task convert_db_to_utf8: :environment do
+  c = ActiveRecord::Base.connection
+
+  database_name = Rails.configuration.database_configuration[Rails.env]['database']
+
+  puts "Setting default for new tables for #{database_name.inspect}..."
+  c.execute("ALTER DATABASE `#{database_name}` CHARACTER SET `utf8` COLLATE `#{THE_ONE_TRUE_COLLATION}`")
+
+  table_status = c.execute("SHOW TABLE STATUS")
+  table_status.each(as: :hash) do |t|
+    next if t['Name'].start_with?('lhm')
+
+    needs_correct_default = false
+    need_conversion = false
+
+    if t['Collation'] != THE_ONE_TRUE_COLLATION
+      needs_correct_default = true
+    end
+
+    c.execute("SHOW FULL COLUMNS FROM `#{t['Name']}`").each(as: hash) do |c|
+      next if c['Collation'].nil?
+      if c['Collation'] != THE_ONE_TRUE_COLLATION
+        need_conversion = true
+        break
+      end
+    end
+
+    next if !needs_correct_default && !need_conversion
+
+    puts "Altering #{t['Name']}..."
+    Lhm.change_table t['Name'] do |m|
+      if needs_correct_default
+        puts "Setting default new columns for for #{m.name.inspect} from #{t['Collation'].inspect} to #{THE_ONE_TRUE_COLLATION.inspect}..."
+        m.ddl "ALTER TABLE `#{m.name}` CHARACTER SET `utf8` COLLATE `#{THE_ONE_TRUE_COLLATION}`"
+      end
+      if need_conversion
+        puts "Converting char set and collation for #{m.name.inspect}..."
+        m.ddl "ALTER TABLE `#{m.name}` CONVERT TO CHARACTER SET `utf8` COLLATE `#{THE_ONE_TRUE_COLLATION}`"
+      end
+    end
+  end
+end
+
+task check_all_columns: :environment do
+  c = ActiveRecord::Base.connection
+
+  c.tables.each do |table_name|
+    next if table_name.start_with?('lhm')
+
+    bad_columns = []
+    c.execute("SHOW FULL COLUMNS FROM `#{table_name}`").each(as: hash) do |c|
+      next if c['Collation'].nil?
+      bad_columns << c['Collation'] if c['Collation'] != THE_ONE_TRUE_COLLATION
+    end
+    unless bad_columns.empty?
+      puts "#{table_name.inspect} has columns with wrong collation #{bad_columns.uniq.inspect}"
+    end
+  end
+end