CODEWITHSUNDEEP
mysqlruby-on-rails
danikaze
danikaze

Reputation: 1654

How to specify a collation for a column when creating a model with rails

Quick question: How to specify a collation for a specific column when creating a model with rails?

I haven't found any option in the rails API doc but I think it's very important to explicit declare a collation for some attributes.

Let's say that the default collation is utf8_unicode_ci. But when creating an Users model the password field's collation should be utf8_bin.

So, when using rails like this

rails g model Users username:string password:string

you can't specify password to use utf8_bin collation.

Any way to do it?

EDIT: The generated SQL should be something like this:

CREATE TABLE `user` (
  `id`               INT(11) UNSIGNED NOT NULL AUTO_INCREMENT  COMMENT 'Unique ID',
  `username`         VARCHAR(32) NOT NULL                      COMMENT 'Nick/username',
  `password`         VARCHAR(32) COLLATE utf8mb4_bin           COMMENT 'Encoded password',

  PRIMARY KEY(`id`),
  INDEX(`username`)
);

I don't care if you can't do it directly via a command and you have to modify the db/timestamp_create_users.rb file. But the point is being able to use the rails migrations.

Upvotes: 11

Views: 11830

Answers (2)

kbrock
kbrock

Reputation: 1017

Good news, rails now has collation built into migrations

class CreateUsers < ActiveRecord::Migration
  def change
    create_table :users do |t|
      t.string   :password, :collation => :utf8mb4_bin, :comment => "Encoded password"
    end
  end
end

https://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/SchemaStatements.html#method-i-add_column

mysql can tell you the character sets via

show character set;

Upvotes: 2

Renra
Renra

Reputation: 5649

Sadly it seems that rails does not provide any high level way to set column-level collation so you have to fall back to SQL at least a little. Here's how I did it:

class CreateUsers < ActiveRecord::Migration                                     
  def change                                                                    
    create_table :users do |t|                                                  
      t.string   :password                                                      
    end                                                                         

    reversible do |dir|                                                         
      dir.up {                                                                  
        change_column :users, :password, "VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_bin"
      }                                                            
    end                                                                         
  end                                                                           
end

The reversible block is there just to enable rollbacks because rails would not know how to revert change_column without the original value.

Then you can check the collation on the mysql cli with:

SHOW TABLE STATUS;

you should see your default collation there - probably utf8_unicode_ci. Then do:

SHOW FULL COLUMNS FROM users;

And there you should see utf8_bin at the password field.

Upvotes: 11

Related Questions