CODEWITHSUNDEEP
mysqlruby-on-railsactiverecordbooleandbmigrate
Fajarmf
Fajarmf

Reputation: 2273

How to change active record column type from boolean to integer without db:migrate?

I have a model like this:

  create_table :settings do |t|
    t.integer :user_id
    t.boolean :send_notification, :default => true
  end

It was running well until I need to specify multiple types of notification (email and/or sms) and I want the user to be able to specify which notification that he needs. So I thought it was doable when I look at my settting table in database:

+----------------------+------------+------+-----+---------+----------------+
| Field                | Type       | Null | Key | Default | Extra          |
+----------------------+------------+------+-----+---------+----------------+
| id                   | int(11)    | NO   | PRI | NULL    | auto_increment |
| user_id              | int(11)    | YES  | MUL | NULL    |                |
| send_notification    | tinyint(1) | YES  |     | 1       |                |
+----------------------+------------+------+-----+---------+----------------+

So I was thinking to reuse send_notification column with bit-masking, e.g. 0 means nothing, 1 means email only, 2 means sms only, and 3 means both sms and email. It was all working well in database, but when I tried it in script/console. I realized that it's not possible to do so (bit-masking on boolean field).

ree > setting = Setting.first
 => #<Setting id: 1, user_id: 1, send_notification: false> 
ree > setting.send_notification = 2
 => 2 
ree > setting
 => #<Setting id: 1, user_id: 1, send_notification: false>

So I need to alter the column type, but it's a bit costly as my table is very big. Is there any better solution other than creating migration file and rake db:migrating?

Upvotes: 0

Views: 1877

Answers (1)

Devart
Devart

Reputation: 121982

Boolean data type is represented as TINYINT(1), so it is a byte. If the field send_notification was used as bool, there should be '0' - false, '1' - 'true' or NULL values. If there are valuse > 1, they can be changed with '1' - 

UPDATE settings SET send_notification = 1 WHERE send_notification > 1;

Now, you can use this field for your flags (NULL, 0, 1, 2...). If you want, you can alter table to change TINYINT to other integer type.

Also, MySQL has useful BIT functions.

Upvotes: 1

Related Questions