MySQL database that uses -1 for TRUE (legacy Microsoft Access)

I’m connecting to a legacy MySQL database that was originally created using Microsoft Access. It continues to be accessed by Access, in addition to Rails.

Boolean fields are defined as tinyint(1) and use 0 for false. But, it uses -1 for true. It seems that this is what Microsoft Access uses as the true value, and it needs to continue using -1 because there are still Access applications that use the database.

Is there any configuration parameter or hook that I can use in Rails to redefine what the MySQL adapter uses for the TRUE value?

I tried before_save hook to rewrite active = true to active = -1 but it seems to send the DB query using TRUE/FALSE. i.e.

UPDATE `imaster` SET `imaster`.`active` = TRUE WHERE `imaster`.`4Digit` = '1605'

Maybe this is something that has to be done on the MySQL end but that may be hard to get authorization to do. If I could find a way to do it from the Rails side it would be ideal.

You can alter this constant to add -1 as a possible truth value: ActiveRecord::Type::Boolean

Welcome to the RoR discussion, by the way!

Walter

Thanks. I looked at that class but it says that the -1 should work already as a ‘truthy’ value:

Coercion: Values set from user input will first be coerced into the appropriate ruby type. Coercion behavior is roughly mapped to Ruby’s boolean semantics.

  • “false”, “f” , “0”, 0 or any other value in FALSE_VALUES will be coerced to false
  • Empty strings are coerced to nil
  • All other values will be coerced to true

My problem comes when I’m trying to set the value to true. ActiveRecord simply creates the SQL statement as active = TRUE instead of passing a 1 value. I’d like for it to send the actual -1 value.

Ok, so I discovered this configuration option that I can add to my application.rb.

require 'active_record/connection_adapters/mysql2_adapter'
ActiveRecord::ConnectionAdapters::Mysql2Adapter.emulate_booleans = false

This overrides the default ActiveRecord behaviour of using active = TRUE in the SQL statement and forces it to send the actual value as such:

SELECT `imaster`.* FROM `imaster` WHERE `imaster`.`active` = -1

The only negative side-effect is that I now can’t use queries like:

Product.where(active: true)

So I solved that by defining a couple of constants to use instead:

module MyProject
  module Db
    TRUE = -1
    FALSE = 0
  end
end

Now I can do:

Product.where(active: MyProject::Db::TRUE)

A nice bonus is that if we ever do drop Microsoft Access support and change all boolean fields to use 1 instead of -1 for TRUE we can just adjust the MyProject::Db::TRUE constant.

Suggested improvements are welcome!