amuntasim · June 2, 2022 21:06 · Aug 9, 2015 · Feb 12, 2015 · Feb 12, 2015
diff --git a/change_mysql_character_set_and_collation b/change_mysql_character_set_and_collation
@@ -1,34 +1,46 @@
-#!/usr/bin/env ruby
-
-# Put this file in the root of your Rails project,
-# then run it to output the SQL needed to change all
-# your tables and columns to the same character set
-# and collation.
-#
-# > ruby character_set_and_collation.rb 
+##Create a migration
+### rails g migration make_unicode_friendly
 
-CHARACTER_SET = 'utf8'
-COLLATION = 'utf8_general_ci'
+class MakeUnicodeFriendly < ActiveRecord::Migration
+  def change
+    alter_database_and_tables_charsets "utf8", "utf8_general_ci"
+  end
 
-schema = File.open('db/schema.rb', 'r').read
-rows = schema.split("\n")
+  private
+  def alter_database_and_tables_charsets charset = default_charset, collation = default_collation
+    case connection.adapter_name
+      when 'Mysql2'
+        execute "ALTER DATABASE #{connection.current_database} CHARACTER SET #{charset} COLLATE #{collation}"
 
-table_name = nil
-rows.each do |row|
-  if row =~ /create_table/
-    table_name = row.match(/create_table "(\w+)"/)[1]
-    puts "ALTER TABLE `#{table_name}` DEFAULT CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
-  elsif row =~ /t\.string/
-    field_name = row.match(/"(\w+)"/)[1]
-    limit = row =~ /limit:\s*(\d*)/ ? row.match(/limit:\s*(\d*)/)[1] : 255
-    puts "ALTER TABLE `#{table_name}` CHANGE  `#{field_name}` `#{field_name}` VARCHAR(#{limit})  CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
-  elsif row =~ /t\.text/
-    field_name = row.match(/"(\w+)"/)[1]
-    if row =~ /limit:\s*(\d*)/
-      limit = row.match(/limit:\s*(\d*)/)[1]
-      puts "ALTER TABLE `#{table_name}` CHANGE  `#{field_name}` `#{field_name}` TEXT(#{limit}) CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
-    else
-      puts "ALTER TABLE `#{table_name}` CHANGE  `#{field_name}` `#{field_name}` TEXT CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
+        connection.tables.each do |table|
+          execute "ALTER TABLE #{table} CONVERT TO CHARACTER SET #{charset} COLLATE #{collation}"
+        end
+      else
+        # OK, not quite irreversible but can't be done if there's not
+        # the code here to support it...
+        raise ActiveRecord::IrreversibleMigration.new("Migration error: Unsupported database for migration to UTF-8 support")
     end
   end
-end
+
+  def default_charset
+    case connection.adapter_name
+      when 'Mysql2'
+        execute("show variables like 'character_set_server'").fetch_hash['Value']
+      else
+        nil
+    end
+  end
+
+  def default_collation
+    case connection.adapter_name
+      when 'Mysql2'
+        execute("show variables like 'collation_server'").fetch_hash['Value']
+      else
+        nil
+    end
+  end
+
+  def connection
+    ActiveRecord::Base.connection
+  end
+end
diff --git a/...e mysql table character set and collation → change_mysql_character_set_and_collation b/...e mysql table character set and collation → change_mysql_character_set_and_collation
diff --git a/change mysql table character set and collation b/change mysql table character set and collation
@@ -0,0 +1,34 @@
+#!/usr/bin/env ruby
+
+# Put this file in the root of your Rails project,
+# then run it to output the SQL needed to change all
+# your tables and columns to the same character set
+# and collation.
+#
+# > ruby character_set_and_collation.rb 
+
+CHARACTER_SET = 'utf8'
+COLLATION = 'utf8_general_ci'
+
+schema = File.open('db/schema.rb', 'r').read
+rows = schema.split("\n")
+
+table_name = nil
+rows.each do |row|
+  if row =~ /create_table/
+    table_name = row.match(/create_table "(\w+)"/)[1]
+    puts "ALTER TABLE `#{table_name}` DEFAULT CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
+  elsif row =~ /t\.string/
+    field_name = row.match(/"(\w+)"/)[1]
+    limit = row =~ /limit:\s*(\d*)/ ? row.match(/limit:\s*(\d*)/)[1] : 255
+    puts "ALTER TABLE `#{table_name}` CHANGE  `#{field_name}` `#{field_name}` VARCHAR(#{limit})  CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
+  elsif row =~ /t\.text/
+    field_name = row.match(/"(\w+)"/)[1]
+    if row =~ /limit:\s*(\d*)/
+      limit = row.match(/limit:\s*(\d*)/)[1]
+      puts "ALTER TABLE `#{table_name}` CHANGE  `#{field_name}` `#{field_name}` TEXT(#{limit}) CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
+    else
+      puts "ALTER TABLE `#{table_name}` CHANGE  `#{field_name}` `#{field_name}` TEXT CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
+    end
+  end
+end