Skip to content

Instantly share code, notes, and snippets.

@todddickerson

todddickerson/character_set_and_collation.rb

Forked from tjh/character_set_and_collation.rb
Created June 13, 2014 15:49
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save todddickerson/bcf24c352d9584a65fd3 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save todddickerson/bcf24c352d9584a65fd3 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @tjh tjh revised this gist Feb 6, 2012. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions character_set_and_collation.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -21,9 +21,9 @@
    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}` varchar(255) CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
    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}` text CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
    puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` CHANGE COLUMN `#{field_name}` `#{field_name}` CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
    end
    end
  2. @tjh tjh revised this gist Jan 31, 2012. No changes.
  3. @tjh tjh revised this gist Jan 31, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion character_set_and_collation.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -18,7 +18,7 @@
    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};"
    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}` varchar(255) CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
  4. @tjh tjh revised this gist Jan 31, 2012. 1 changed file with 6 additions and 3 deletions.
    9 changes: 6 additions & 3 deletions character_set_and_collation.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -18,9 +18,12 @@
    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|text)/
    #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}` varchar(255) 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}` text CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
    end
    end
    end
  5. @tjh tjh revised this gist Jan 31, 2012. No changes.
  6. @tjh tjh revised this gist Jan 31, 2012. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion character_set_and_collation.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -21,6 +21,6 @@
    puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` DEFAULT CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
    elsif row =~ /t\.(string|text)/
    field_name = row.match(/"(.+)"/)[1]
    puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` CHANGE COLUMN `#{field_name}` `#{field_name}` text CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION} DEFAULT NULL;"
    puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` CHANGE COLUMN `#{field_name}` `#{field_name}` text CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION};"
    end
    end
  7. @tjh tjh revised this gist Jan 31, 2012. 1 changed file with 7 additions and 0 deletions.
    7 changes: 7 additions & 0 deletions character_set_and_collation.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -1,5 +1,12 @@
    #!/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'
  8. @tjh tjh created this gist Jan 31, 2012.
    19 changes: 19 additions & 0 deletions character_set_and_collation.rb
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,19 @@
    #!/usr/bin/env ruby

    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|text)/
    field_name = row.match(/"(.+)"/)[1]
    puts "ALTER TABLE `#{DATABASE}`.`#{table_name}` CHANGE COLUMN `#{field_name}` `#{field_name}` text CHARACTER SET #{CHARACTER_SET} COLLATE #{COLLATION} DEFAULT NULL;"
    end
    end