Rails 4.1 - Write to MySQL database without typecasting

9
votes

I have a column in my MySQL database which is of type TINYINT(1). I need to store actual integers in this column. The problem is, because of the column type, Rails 4.1 assumes this column contains only boolean values, so it typecasts all values besides 0 or 1 to be 0 when it writes to the database.

I don't want to simply disable boolean emulation since we have a number of columns in our database where we use TINYINT(1) to actually represent a boolean value. And I am currently not able to change the column types in MySQL.

How can I force Rails 4.1 to bypass the typecasting step and write directly to the database instead?

(This excerpt from the Rails 4.1 source may be of some use: https://github.com/rails/rails/blob/4-1-stable/activerecord/lib/active_record/attribute_methods/write.rb)

4
mysqlruby-on-railsrubyactiverecordruby-on-rails-4.1
Additional context for other readers, TINYINT(1) can store signed integer values -127..+127 stackoverflow.com/questions/4401673/… but MySQL uses BOOLEAN as a synonym for it. - Michael Berkowski
Would it be the end of the world if you opened up that column to a regular INT using a migration? - tadman
@tadman Believe me, the thought crossed my mind, but for reasons outside my control I'm not able to change the structure of this database. - Ben Visness
You could try looking at this answer to see if that helps. - tadman
@BenVisness have you tried overwriting the setter like this: api.rubyonrails.org/classes/ActiveRecord/… - d34n5

4 Answers

3
votes

Could you use raw SQL to do the insert?

Something like:

sql = "INSERT INTO my_table (smallnumber) VALUES (100)"
ActiveRecord::Base.connection.execute(sql)
1
votes

I don't know if it works but you can try to overwrite the setter using the method :raw_write_attribute or :write_attribute. The :raw_write_attribute and :write_attribute methods disable/enable the type casting before writing.

Let's say the attribute/column is called :the_boolean_column_who_wanted_to_be_an_integer, you can probably do something like:

def the_boolean_column_who_wanted_to_be_an_integer=(value)
  raw_write_attribute(:the_boolean_column_who_wanted_to_be_an_integer, value) # or write_attribute(...
end

Does it work?

0
votes

Maybe you should overwrite the setter completely, using rails 4.1 source code:

def the_field=(value)
    attr_name = 'the_field'
    attr_name = self.class.primary_key if attr_name == 'id' && self.class.primary_key
    @attributes_cache.delete(attr_name)
    column = column_for_attribute(attr_name)

    # If we're dealing with a binary column, write the data to the cache
    # so we don't attempt to typecast multiple times.
    if column && column.binary?
      @attributes_cache[attr_name] = value
    end

    if column || @attributes.has_key?(attr_name)
      @attributes[attr_name] = value
    else
      raise ActiveModel::MissingAttributeError, "can't write unknown attribute `#{attr_name}'"
    end
end

Note that @attributes[attr_name] = send(type_cast_method, column, value) has been changed to @attributes[attr_name] = value . You can probably simplify it for your use case. Also note that I haven't tried this, and even if it works, you should be careful whenever you want to upgrade rails.

0
votes

Plan A: Change to SMALLINT (2 bytes) as a compromise.

Plan B: See if TINYINT(3) will fool Rails into not thinking it is Boolean.

Plan C: See if TINYINT UNSIGNED will fool Rails into not thinking it is Boolean. (This assumes your number are non-negative: 0..255.)