Rails 3 SQLite3 Boolean false

by

SQLite uses 1 for true and 0 for false:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

But SQLite also has a loose type system and automatically casts things so your 'f' is probably being interpreted as having a truthiness of “true” simply because it isn’t zero.

A bit of digging indicates that you have found a bug in the Rails 3.0.7 SQLiteAdapter. In active_record/connection_adapters/abstract/quoting.rb, we find these:

def quoted_true
  "'t'"
end

def quoted_false
  "'f'"
end

So, by default, ActiveRecord assumes that the database understands 't' and 'f' for boolean columns. The MySQL adaptor overrides these to work with its tinyint implementation of boolean columns:

QUOTED_TRUE, QUOTED_FALSE = '1'.freeze, '0'.freeze

#...

def quoted_true
  QUOTED_TRUE
end

def quoted_false
  QUOTED_FALSE
end

But the SQLite adapter does not provide its own implementations of quoted_true or quoted_false so it gets the defaults which don’t work with SQLite’s booleans.

The 't' and 'f' booleans work in PostgreSQL so maybe everyone is using PostgreSQL with Rails 3 or they’re just not noticing that their queries aren’t working properly.

I’m a little surprised by this and hopefully someone can point out where I’ve gone wrong, you can’t be the first person to use a boolean column in SQLite with Rails 3.

Try monkey patching def quoted_true;'1';end and def quoted_false;'0';end into ActiveRecord::ConnectionAdapters::SQLiteAdapter (or temporarily hand-edit them into active_record/connection_adapters/sqlite_adapter.rb) and see if you get sensible SQL.

More Related Contents:

  1. ActiveRecord OR query
  2. ActiveRecord: size vs count
  3. Connecting Rails 3.1 with Multiple Databases
  4. How to chain scope queries with OR instead of AND?
  5. Clean way to find ActiveRecord objects by id in the order specified
  6. ActiveRecord Find By Year, Day or Month on a Date field
  7. Rails update_attributes without save?
  8. Validate uniqueness of multiple columns
  9. cannot load such file — sqlite3/sqlite3_native (LoadError) on ruby on rails
  10. Can’t find the PostgreSQL client library (libpq)
  11. do..end vs curly braces for blocks in Ruby
  12. In Ruby on Rails, to extend the String class, where should the code be put in?
  13. Rails: How to run `rails generate scaffold` when the model already exists?
  14. Combine two ActiveRecord::Relation objects
  15. incompatible character encodings: ASCII-8BIT and UTF-8
  16. Alias for column names in Rails
  17. Rails 3 devise, current_user is not accessible in a Model ?
  18. STI, one controller
  19. find vs find_by vs where
  20. What is the replacement for ActionController::Base.relative_url_root?
  21. Cannot add a NOT NULL column with default value NULL in Sqlite3
  22. Delete link sends “Get” instead of “Delete” in Rails 3 view
  23. Rails Routes based on condition
  24. Rails – link_to helper with data-* attribute [duplicate]
  25. How to use the I18n fallback features in Rails 3
  26. Is there a “phpmyadmin” for Ruby on Rails? [closed]
  27. Rails 2 to Rails 3 : using link_to instead of link_to_remote (including remote and update)
  28. Specifying column name in a “references” migration
  29. Ruby 1.9.2 and Rails 3 cannot open rails console
  30. Configuring Warden for use in RSpec controller specs

Leave a Comment