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!