CODEWITHSUNDEEP
ruby-on-railsactiverecordsqldatatypesmysql2
Touqeer
Touqeer

Reputation: 654

Data type for characters in another language

I have a Location table in activerecord which contains the string type column address. I want to store this Garden Village Restaurant-B مطعم حديقة فيلاج sub-B in address column, which datatype should I use? I have already tried text datatype but whenever i try to store it gives me an error:

ActiveRecord::StatementInvalid: Mysql2::Error: Incorrect string value: '\xD9\x85\xD8\xB7\xD8\xB9...' for column 'address'

Or if there is another way, I'm open to suggestions.

Upvotes: 1

Views: 85

Answers (2)

kiddorails
kiddorails

Reputation: 13014

Generate migration: (Why? - Do not run custom queries on your database, as they are not recorded in your schema)

rails g migration change_collation

Add code as below in migration generated. This will change the character set of database(so next migrations will automatically respect the new collation) and will change the character set of existing tables.

class ChangeCollation < ActiveRecord::Migration[5.0]
  def change_encoding(encoding,collation)
    connection = ActiveRecord::Base.connection
    tables = connection.tables
    dbname =connection.current_database
    execute <<-SQL
      ALTER DATABASE #{dbname} CHARACTER SET #{encoding} COLLATE #{collation};
    SQL
    tables.each do |tablename|
      execute <<-SQL
        ALTER TABLE #{dbname}.#{tablename} CONVERT TO CHARACTER SET #{encoding} COLLATE #{collation};
      SQL
    end
  end

  def change
    reversible do |dir|
      dir.up do
        change_encoding('utf8', 'utf8_unicode_ci')
      end
      dir.down do
        change_encoding('latin1', 'latin1_swedish_ci')
      end
    end
  end
end

Also, I think utf8_general_ci will also support storing the urdu characters. But based on this post, better to go ahead with utf8_unicode_ci

Another way: save address in encrypted manner:

config/initializers/encrypter.rb

encrypter_key = ActiveSupport::KeyGenerator.new('mypassword').generate_key('a..z', 32)
ENCRYPTER_CRYPT = ActiveSupport::MessageEncryptor.new(encrypter_key)

in model: 

class Location < ApplicationRecord
  before_save :encrypt_address
  def encrypt_address
    self.address = ENCRYPTER_CRYPT.encrypt_and_sign(self[:address]) if self[:address].present?
  end

  def address
    # override getter to decrypt and give original urdu string.
    ENCRYPTER_CRYPT.decrypt_and_verify(self[:address]) if self[:address].present?
  end
end

Upvotes: 2

Aniket Tiwari
Aniket Tiwari

Reputation: 3998

You could run the following command on your desired table(s):

ALTER TABLE <table_name> CONVERT TO CHARACTER SET utf8 COLLATE utf8_unicode_ci;

Also delete all the data first from that table Hope it helps!

Upvotes: 0

Related Questions