Booleans in Rails with SQLite

Rails 3 SQLite3 Boolean false

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.

Regression in ActiveRecord 4 + SQLite3 boolean query support?

I have debugged the guts of both ActiveRecord 3.2.14 and 4.0.2. Here is the bug from start to finish:

SQLite allows you to insert any arbitrary string/number as a column value for boolean columns. Thus, you can insert 1 or 't' for a boolean column value.

ActiveRecord maps boolean values to string types 't' or 'f' when interacting with SQLite, not 1 or 0. The reasons behind that decision may stem from Postgres, but that is how it is for now.

When ActiveRecord creates a record for the first time in 3.2.14, at line 365 in persistence.rb, it creates a mapping of all model fields and inserts all fields, whether or not they have been changed. Here is the create method:

def create
  attributes_values = arel_attributes_values(!id.nil?)

  new_id = self.class.unscoped.insert attributes_values

  self.id ||= new_id if self.class.primary_key

  IdentityMap.add(self) if IdentityMap.enabled?
  @new_record = false
  id
end

This causes ActiveRecord to generate an insert statement like the following (note the presence of enabled, our boolean column):

INSERT INTO "store_items" ("created_at", "enabled", "other_columns....") VALUES (?, ?, ?)  [["created_at", 2014-01-11 21:47:24 UTC], ["enabled", true], ["other_colummns", ...]]

In ActiveRecord 4.0.2, the file dirty.rb, line 78, now calls the create_record method of persistence.rb (at line 507). Create record looks like this:

def create_record(attribute_names = @attributes.keys)
  attributes_values = arel_attributes_with_values_for_create(attribute_names)

  new_id = self.class.unscoped.insert attributes_values
  self.id ||= new_id if self.class.primary_key

  @new_record = false
  id
end

Because create_record now accepts an argument that lists only those columns that have changed, it generates an insert statement that does NOT include columns whose default values match what you are inserting. Thus, an insert statement that used a boolean value that matched a default would look like this:

INSERT INTO "store_items" ("created_at", "other_columns....") VALUES (?, ?, ?)  [["created_at", 2014-01-11 21:47:24 UTC], ["other_colummns", ...]]

Note that the boolean column "enabled" is missing, because in this case, our default value of true/1 matched what we were inserting the first time we created the record.

Because enabled is NOT specified by the insert statement generated by ActiveRecord, SQLite gives it the value of 1, instead of the value of 't', which is what ActiveRecord 3.1.14 used to give it.

Ultimately, to work around this bug, don't include a default value on boolean columns or ensure that you are changing it to something that is not the default to force ActiveRecord to actually set it to a 't' or 'f' value on create.

Thus, change this:

class CreateStoreItems < ActiveRecord::Migration
  def change
    create_table :store_items do |t|
      t.boolean :enabled, :null => false, :default => 1
    end
  end
end

to this

class CreateStoreItems < ActiveRecord::Migration
  def change
    create_table :store_items do |t|
      t.boolean :enabled, :null => false
    end
  end
end

Alternatively, if you 'twiddle' the boolean value instead of relying on the default, you can correct the bug as well.

Taking the average of a boolean column in both postgres and sqlite3

Boolean values in databases are difficult to work with portably:

1. MySQL and SQLite use C-style booleans natively.
2. The ActiveRecord driver for SQLite sometimes mistakenly uses 't' and 'f' strings for booleans. This might be fixed in newer versions.
3. PostgreSQL has a native boolean type.
4. Other databases do other things.
5. Sometimes there are automatic casts to numeric values and sometimes there aren't.
6. ?
7. Profit.

If you want to do this portably, then you should convert the booleans by hand. In your case, you are lucky to have an expression that yields a native boolean value so you don't have to worry about AR's SQLite brain damage. A simple CASE should make things work the same everywhere:

self.average(%q{
  case
    when undergrad_college_name != '' then 1.0
    else 0.0
  end
})

That will give you a floating point result (probably a Float or BigDecimal) and then you can decide how you want deal with it from there.

Correct format for a query on a Boolean within a Rails app -- Targetting SQLite / PostgreSQL

To generate a boolean value, use a boolean expression with a known result:

Category.where("user_accessible = (1 = 1)")

If you want an actual value to be used as a parameter, you can read it from the database:

 ... = ActiveRecord::Base.connection.execute("SELECT 1=1")

Rails SQlite boolean problem

It probably is some sort of encoding issue.
Use the rails console and do:

photo.update_attribute(:is_approved, false)

Then it will go through active record.

Rails 3 and SQLite communications concerning boolians

I solved this by using a integer migration to my users table, i have no idea as of why @user.update_attribute(:approved, true) didnt save to the database it must have had something to do with my setup

i made the migration add_column :users, :is_approved, :integer, :default => 0 and when i want to activate a user i simply flip the integer value to 1

---

Related Topics