Rails database boolean values

Question

Code first:

create_table :users do |t|
  ...
  t.boolean :is_active, :default => true
  ...
end

Now, here is my issue - I am creating a rake task to import a LARGE number of records (10,000+). I've done extensive testing and benchmarking and determined that the fastest and most efficient way to perform this task is to create one giant raw SQL statement. (I'm reading data from CSV). As an example:

inserts = Array.new
FasterCSV.foreach(...) do |row|
  inserts.push "(row[0], row[1]...)"
end
User.connection.execute "INSERT INTO users (...) VALUES #{inserts.join(", ")}"

Everything works great. The entire process completes in (literally) seconds instead of the 1.5 hours using ActiveRecord. However, my problem lies with the boolean field. I develop locally on SQLite, but MySQL on production. When using ActiveRecord, Rails determines what to put in the "boolean" field (since almost all databases are different). I'm writing custom SQL and I want to know if there is a way I can do something like...

INSERT INTO users(..., is_active, ...) VALUES (..., ActiveRecord::Base.connection.boolean.true, ...)

...that correctly returns the database-specific boolean value.

Anyone who answers "just using ActiveRecord" will be down-voted. It's simply NOT feasible in this situation. I'm also not willing to use a tinyint(1) field and use 1's or 0's.

In summary, the value for is_active needs to change based on the current database connection...

Is this even possible?

Why don't you develop locally with MySQL? That would solve this problem, along with other incompatibilities. They way I usually deal with booleans is to use a prepared statement, but won't work for you. — Paul Schreiber
– Paul Schreiber, Commented Dec 27, 2010 at 22:23
That doesn't answer my question though. What if I want to use POSTgres or MSSQL at some point? — sethvargo
– sethvargo, Commented Dec 27, 2010 at 22:24

Scott · Accepted Answer · 2010-12-27 23:44:44Z

31

I believe that you might be looking for ActiveRecord::Base.connection.quoted_true

This returns native boolean values in quotes, e.g. '1' for SQL Server or MySQL, and 't' for PostgreSQL or SQLite

edited Dec 27, 2010 at 23:44

answered Dec 27, 2010 at 23:18

Scott

17.3k5 gold badges56 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

sethvargo Over a year ago

Thank you so much! How/where did you find this?! Also, it may be worth editing your answer (just for other people who may have this problem) to mention that it automatically includes the quotes ('). For example, if the connection is sqlite, it returns 't' not just t.

Scott Over a year ago

Heh, to find it, I had some fun looking through the codes for ActiveRecord connection adapters.

Phillip Koebbe Over a year ago

Just for clarity: You can use self.connection.quoted_* in class methods and self.class.connection.quoted_* in instance methods.

Collectives™ on Stack Overflow

Rails database boolean values

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related