Change sqlite3 boolean serialization to use 1 and 0

Abstract boolean serialization has been using 't' and 'f', with MySQL
overriding that to use 1 and 0.

This has the advantage that SQLite natively recognizes 1 and 0 as true
and false, but does not natively recognize 't' and 'f'.

This change in serialization requires a migration of stored boolean data
for SQLite databases, so it's implemented behind a configuration flag
whose default false value is deprecated. The flag itself can be
deprecated in a future version of Rails.  While loaded models will give
the correct result for boolean columns without migrating old data,
where() clauses will interact incorrectly with old data.

While working in this area, also change the abstract adapter to use
`"TRUE"` and `"FALSE"` as quoted values and `true` and `false` for
unquoted.  These are supported by PostreSQL, and MySQL remains
overriden.

12 files changed, 148 insertions, 8 deletions

diff --git a/activerecord/CHANGELOG.md b/activerecord/CHANGELOG.md
index 786bef7359..5ffbed28f6 100644
--- a/activerecord/CHANGELOG.md
+++ b/activerecord/CHANGELOG.md
@@ -1,3 +1,14 @@
+*   Change sqlite3 boolean serialization to use 1 and 0
+
+    SQLite natively recognizes 1 and 0 as true and false, but does not natively
+    recognize 't' and 'f' as was previously serialized.
+
+    This change in serialization requires a migration of stored boolean data
+    for SQLite databases, so it's implemented behind a configuration flag
+    whose default false value is deprecated.
+
+    *Lisa Ugray*
+
 *   Skip query caching when working with batches of records (`find_each`, `find_in_batches`,
     `in_batches`).
 
diff --git a/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb b/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb
index 61233dcc51..de78c6db44 100644
--- a/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb
+++ b/activerecord/lib/active_record/connection_adapters/abstract/quoting.rb
@@ -106,19 +106,19 @@ module ActiveRecord
       end
 
       def quoted_true
-        "'t'".freeze
+        "TRUE".freeze
       end
 
       def unquoted_true
-        "t".freeze
+        true
       end
 
       def quoted_false
-        "'f'".freeze
+        "FALSE".freeze
       end
 
       def unquoted_false
-        "f".freeze
+        false
       end
 
       # Quote date/time values for use in SQL input. Includes microseconds
diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3/quoting.rb b/activerecord/lib/active_record/connection_adapters/sqlite3/quoting.rb
index 7276a65098..04dd7fd357 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite3/quoting.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite3/quoting.rb
@@ -22,6 +22,22 @@ module ActiveRecord
           "x'#{value.hex}'"
         end
 
+        def quoted_true
+          ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer ? "1".freeze : "'t'".freeze
+        end
+
+        def unquoted_true
+          ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer ? 1 : "t".freeze
+        end
+
+        def quoted_false
+          ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer ? "0".freeze : "'f'".freeze
+        end
+
+        def unquoted_false
+          ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer ? 0 : "f".freeze
+        end
+
         private
 
           def _type_cast(value)
diff --git a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
index 04129841e4..b79dbe0733 100644
--- a/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
+++ b/activerecord/lib/active_record/connection_adapters/sqlite3_adapter.rb
@@ -72,6 +72,23 @@ module ActiveRecord
         boolean:      { name: "boolean" }
       }
 
+      ##
+      # :singleton-method:
+      # Indicates whether boolean values are stored in sqlite3 databases as 1
+      # and 0 or 't' and 'f'. Leaving `ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer`
+      # set to false is deprecated. SQLite databases have used 't' and 'f' to
+      # serialize boolean values and must have old data converted to 1 and 0
+      # (its native boolean serialization) before setting this flag to true.
+      # Conversion can be accomplished by setting up a rake task which runs
+      #
+      #   ExampleModel.where("boolean_column = 't'").update_all(boolean_column: 1)
+      #   ExampleModel.where("boolean_column = 't'").update_all(boolean_column: 0)
+      # for all models and all boolean columns, after which the flag must be set
+      # to true by adding the following to your application.rb file:
+      #
+      #   ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = true
+      class_attribute :represent_boolean_as_integer, default: false
+
       class StatementPool < ConnectionAdapters::StatementPool
         private
 
@@ -512,5 +529,6 @@ module ActiveRecord
           execute("PRAGMA foreign_keys = ON", "SCHEMA")
         end
     end
+    ActiveSupport.run_load_hooks(:active_record_sqlite3adapter, SQLite3Adapter)
   end
 end
diff --git a/activerecord/lib/active_record/railtie.rb b/activerecord/lib/active_record/railtie.rb
index 9cca103a18..962ed880b9 100644
--- a/activerecord/lib/active_record/railtie.rb
+++ b/activerecord/lib/active_record/railtie.rb
@@ -174,5 +174,29 @@ end_warning
         end
       end
     end
+
+    initializer "active_record.check_represent_sqlite3_boolean_as_integer" do
+      config.after_initialize do
+        ActiveSupport.on_load(:active_record_sqlite3adapter) do
+          unless ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer
+            ActiveSupport::Deprecation.warn <<-MSG
+Leaving `ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer`
+set to false is deprecated. SQLite databases have used 't' and 'f' to serialize
+boolean values and must have old data converted to 1 and 0 (its native boolean
+serialization) before setting this flag to true. Conversion can be accomplished
+by setting up a rake task which runs
+
+  ExampleModel.where("boolean_column = 't'").update_all(boolean_column: 1)
+  ExampleModel.where("boolean_column = 't'").update_all(boolean_column: 0)
+
+for all models and all boolean columns, after which the flag must be set to
+true by adding the following to your application.rb file:
+
+  ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = true
+MSG
+          end
+        end
+      end
+    end
   end
 end
diff --git a/activerecord/test/cases/adapters/postgresql/quoting_test.rb b/activerecord/test/cases/adapters/postgresql/quoting_test.rb
index a1e966b915..d7b23ad41d 100644
--- a/activerecord/test/cases/adapters/postgresql/quoting_test.rb
+++ b/activerecord/test/cases/adapters/postgresql/quoting_test.rb
@@ -9,11 +9,11 @@ module ActiveRecord
         end
 
         def test_type_cast_true
-          assert_equal "t", @conn.type_cast(true)
+          assert_equal true, @conn.type_cast(true)
         end
 
         def test_type_cast_false
-          assert_equal "f", @conn.type_cast(false)
+          assert_equal false, @conn.type_cast(false)
         end
 
         def test_quote_float_nan
diff --git a/activerecord/test/cases/adapters/sqlite3/quoting_test.rb b/activerecord/test/cases/adapters/sqlite3/quoting_test.rb
index aefbb309e6..815453ad50 100644
--- a/activerecord/test/cases/adapters/sqlite3/quoting_test.rb
+++ b/activerecord/test/cases/adapters/sqlite3/quoting_test.rb
@@ -5,6 +5,11 @@ require "securerandom"
 class SQLite3QuotingTest < ActiveRecord::SQLite3TestCase
   def setup
     @conn = ActiveRecord::Base.connection
+    @initial_represent_boolean_as_integer = ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer
+  end
+
+  def teardown
+    ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = @initial_represent_boolean_as_integer
   end
 
   def test_type_cast_binary_encoding_without_logger
@@ -15,11 +20,19 @@ class SQLite3QuotingTest < ActiveRecord::SQLite3TestCase
   end
 
   def test_type_cast_true
+    ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = false
     assert_equal "t", @conn.type_cast(true)
+
+    ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = true
+    assert_equal 1, @conn.type_cast(true)
   end
 
   def test_type_cast_false
+    ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = false
     assert_equal "f", @conn.type_cast(false)
+
+    ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = true
+    assert_equal 0, @conn.type_cast(false)
   end
 
   def test_type_cast_bigdecimal
diff --git a/activerecord/test/cases/quoting_test.rb b/activerecord/test/cases/quoting_test.rb
index 0819776fbf..b21adccc4b 100644
--- a/activerecord/test/cases/quoting_test.rb
+++ b/activerecord/test/cases/quoting_test.rb
@@ -8,11 +8,11 @@ module ActiveRecord
       end
 
       def test_quoted_true
-        assert_equal "'t'", @quoter.quoted_true
+        assert_equal "TRUE", @quoter.quoted_true
       end
 
       def test_quoted_false
-        assert_equal "'f'", @quoter.quoted_false
+        assert_equal "FALSE", @quoter.quoted_false
       end
 
       def test_quote_column_name
diff --git a/guides/source/configuring.md b/guides/source/configuring.md
index 28ceef9740..d7fa8813b2 100644
--- a/guides/source/configuring.md
+++ b/guides/source/configuring.md
@@ -375,6 +375,28 @@ The MySQL adapter adds one additional configuration option:
 
 * `ActiveRecord::ConnectionAdapters::Mysql2Adapter.emulate_booleans` controls whether Active Record will consider all `tinyint(1)` columns as booleans. Defaults to `true`.
 
+The SQLite3Adapter adapter adds one additional configuration option:
+
+* `ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer`
+indicates whether boolean values are stored in sqlite3 databases as 1 and 0 or
+'t' and 'f'. Leaving `ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer`
+set to false is deprecated. SQLite databases have used 't' and 'f' to serialize
+boolean values and must have old data converted to 1 and 0 (its native boolean
+serialization) before setting this flag to true. Conversion can be accomplished
+by setting up a rake task which runs
+
+    ```ruby
+    ExampleModel.where("boolean_column = 't'").update_all(boolean_column: 1)
+    ExampleModel.where("boolean_column = 't'").update_all(boolean_column: 0)
+    ```
+
+  for all models and all boolean columns, after which the flag must be set to true
+by adding the following to your application.rb file:
+
+    ```ruby
+    ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = true
+    ```
+
 The schema dumper adds one additional configuration option:
 
 * `ActiveRecord::SchemaDumper.ignore_tables` accepts an array of tables that should _not_ be included in any generated schema file. This setting is ignored unless `config.active_record.schema_format == :ruby`.
diff --git a/railties/lib/rails/application/configuration.rb b/railties/lib/rails/application/configuration.rb
index d403c4fa7c..4797223380 100644
--- a/railties/lib/rails/application/configuration.rb
+++ b/railties/lib/rails/application/configuration.rb
@@ -86,6 +86,10 @@ module Rails
 
           if respond_to?(:active_record)
             active_record.cache_versioning = true
+            # Remove the temporary load hook from SQLite3Adapter when this is removed
+            ActiveSupport.on_load(:active_record_sqlite3adapter) do
+              ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = true
+            end
           end
 
           if respond_to?(:action_dispatch)
diff --git a/railties/lib/rails/generators/rails/app/templates/config/initializers/new_framework_defaults_5_2.rb.tt b/railties/lib/rails/generators/rails/app/templates/config/initializers/new_framework_defaults_5_2.rb.tt
index 900e18251c..f2df0d1e9f 100644
--- a/railties/lib/rails/generators/rails/app/templates/config/initializers/new_framework_defaults_5_2.rb.tt
+++ b/railties/lib/rails/generators/rails/app/templates/config/initializers/new_framework_defaults_5_2.rb.tt
@@ -21,3 +21,7 @@
 # Add default protection from forgery to ActionController::Base instead of in
 # ApplicationController.
 # Rails.application.config.action_controller.default_protect_from_forgery = true
+
+# Store boolean values are in sqlite3 databases as 1 and 0 instead of 't' and
+# 'f' after migrating old data.
+# ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer = true
diff --git a/railties/test/application/configuration_test.rb b/railties/test/application/configuration_test.rb
index 6c0c087331..b8a19379e0 100644
--- a/railties/test/application/configuration_test.rb
+++ b/railties/test/application/configuration_test.rb
@@ -1585,6 +1585,34 @@ module ApplicationTests
       assert_equal({}, Rails.application.config.my_custom_config)
     end
 
+    test "default SQLite3Adapter.represent_boolean_as_integer for 5.1 is false" do
+      remove_from_config '.*config\.load_defaults.*\n'
+      add_to_top_of_config <<-RUBY
+        config.load_defaults 5.1
+      RUBY
+      app_file "app/models/post.rb", <<-RUBY
+        class Post < ActiveRecord::Base
+        end
+      RUBY
+
+      app "development"
+      Post.object_id # force lazy load hooks to run
+
+      assert_not ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer
+    end
+
+    test "default SQLite3Adapter.represent_boolean_as_integer for new installs is true" do
+      app_file "app/models/post.rb", <<-RUBY
+        class Post < ActiveRecord::Base
+        end
+      RUBY
+
+      app "development"
+      Post.object_id # force lazy load hooks to run
+
+      assert ActiveRecord::ConnectionAdapters::SQLite3Adapter.represent_boolean_as_integer
+    end
+
     test "config_for containing ERB tags should evaluate" do
       app_file "config/custom.yml", <<-RUBY
       development: