Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save wittawasw/3936749d899dd2039e67b274e94670df to your computer and use it in GitHub Desktop.
Save wittawasw/3936749d899dd2039e67b274e94670df to your computer and use it in GitHub Desktop.
Convert all Rails table column collation and character set
#!/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
DATABASE = ''
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 "(.+)"/)[1]
puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` DEFAULT CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
elsif row =~ /t\.string/
field_name = row.match(/"(.+)"/)[1]
puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` CHANGE COLUMN `#{field_name}` `#{field_name}` CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
elsif row =~ /t\.text/
field_name = row.match(/"(.+)"/)[1]
puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` CHANGE COLUMN `#{field_name}` `#{field_name}` CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
end
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment