sqlite3_adapter_test.rb

# encoding: utf-8
require "cases/helper"
require 'models/owner'
require 'tempfile'
require 'support/ddl_helper'

module ActiveRecord
  module ConnectionAdapters
    class SQLite3AdapterTest < ActiveRecord::TestCase
      include DdlHelper

      self.use_transactional_fixtures = false

      class DualEncoding < ActiveRecord::Base
      end

      def setup
        @conn = Base.sqlite3_connection database: ':memory:',
                                        adapter: 'sqlite3',
                                        timeout: 100
      end

      def test_bad_connection
        assert_raise ActiveRecord::NoDatabaseError do
          connection = ActiveRecord::Base.sqlite3_connection(adapter: "sqlite3", database: "/tmp/should/_not/_exist/-cinco-dog.db")
          connection.exec_query('drop table if exists ex')
        end
      end

      unless in_memory_db?
        def test_connect_with_url
          original_connection = ActiveRecord::Base.remove_connection
          tf = Tempfile.open 'whatever'
          url = "sqlite3://#{tf.path}"
          ActiveRecord::Base.establish_connection(url)
          assert ActiveRecord::Base.connection
        ensure
          tf.close
          tf.unlink
          ActiveRecord::Base.establish_connection(original_connection)
        end

        def test_connect_memory_with_url
          original_connection = ActiveRecord::Base.remove_connection
          url = "sqlite3:///:memory:"
          ActiveRecord::Base.establish_connection(url)
          assert ActiveRecord::Base.connection
        ensure
          ActiveRecord::Base.establish_connection(original_connection)
        end
      end

      def test_valid_column
        with_example_table do
          column = @conn.columns('ex').find { |col| col.name == 'id' }
          assert @conn.valid_type?(column.type)
        end
      end

      # sqlite databases should be able to support any type and not
      # just the ones mentioned in the native_database_types.
      # Therefore test_invalid column should always return true
      # even if the type is not valid.
      def test_invalid_column
        assert @conn.valid_type?(:foobar)
      end

      def test_column_types
        owner = Owner.create!(name: "hello".encode('ascii-8bit'))
        owner.reload
        select = Owner.columns.map { |c| "typeof(#{c.name})" }.join ', '
        result = Owner.connection.exec_query <<-esql
          SELECT #{select}
          FROM   #{Owner.table_name}
          WHERE  #{Owner.primary_key} = #{owner.id}
        esql

        assert(!result.rows.first.include?("blob"), "should not store blobs")
      ensure
        owner.delete
      end

      def test_exec_insert
        with_example_table do
          column = @conn.columns('ex').find { |col| col.name == 'number' }
          vals   = [[column, 10]]
          @conn.exec_insert('insert into ex (number) VALUES (?)', 'SQL', vals)

          result = @conn.exec_query(
            'select number from ex where number = ?', 'SQL', vals)

          assert_equal 1, result.rows.length
          assert_equal 10, result.rows.first.first
        end
      end

      def test_primary_key_returns_nil_for_no_pk
        with_example_table 'id int, data string' do
          assert_nil @conn.primary_key('ex')
        end
      end

      def test_connection_no_db
        assert_raises(ArgumentError) do
          Base.sqlite3_connection {}
        end
      end

      def test_bad_timeout
        assert_raises(TypeError) do
          Base.sqlite3_connection database: ':memory:',
                                  adapter: 'sqlite3',
                                  timeout: 'usa'
        end
      end

      # connection is OK with a nil timeout
      def test_nil_timeout
        conn = Base.sqlite3_connection database: ':memory:',
                                       adapter: 'sqlite3',
                                       timeout: nil
        assert conn, 'made a connection'
      end

      def test_connect
        assert @conn, 'should have connection'
      end

      # sqlite3 defaults to UTF-8 encoding
      def test_encoding
        assert_equal 'UTF-8', @conn.encoding
      end

      def test_bind_value_substitute
        bind_param = @conn.substitute_at('foo', 0)
        assert_equal Arel.sql('?'), bind_param
      end

      def test_exec_no_binds
        with_example_table 'id int, data string' do
          result = @conn.exec_query('SELECT id, data FROM ex')
          assert_equal 0, result.rows.length
          assert_equal 2, result.columns.length
          assert_equal %w{ id data }, result.columns

          @conn.exec_query('INSERT INTO ex (id, data) VALUES (1, "foo")')
          result = @conn.exec_query('SELECT id, data FROM ex')
          assert_equal 1, result.rows.length
          assert_equal 2, result.columns.length

          assert_equal [[1, 'foo']], result.rows
        end
      end

      def test_exec_query_with_binds
        with_example_table 'id int, data string' do
          @conn.exec_query('INSERT INTO ex (id, data) VALUES (1, "foo")')
          result = @conn.exec_query(
            'SELECT id, data FROM ex WHERE id = ?', nil, [[nil, 1]])

          assert_equal 1, result.rows.length
          assert_equal 2, result.columns.length

          assert_equal [[1, 'foo']], result.rows
        end
      end

      def test_exec_query_typecasts_bind_vals
        with_example_table 'id int, data string' do
          @conn.exec_query('INSERT INTO ex (id, data) VALUES (1, "foo")')
          column = @conn.columns('ex').find { |col| col.name == 'id' }

          result = @conn.exec_query(
            'SELECT id, data FROM ex WHERE id = ?', nil, [[column, '1-fuu']])

          assert_equal 1, result.rows.length
          assert_equal 2, result.columns.length

          assert_equal [[1, 'foo']], result.rows
        end
      end

      def test_quote_binary_column_escapes_it
        DualEncoding.connection.execute(<<-eosql)
          CREATE TABLE dual_encodings (
            id integer PRIMARY KEY AUTOINCREMENT,
            name varchar(255),
            data binary
          )
        eosql
        str = "\x80".force_encoding("ASCII-8BIT")
        binary = DualEncoding.new name: 'いただきます！', data: str
        binary.save!
        assert_equal str, binary.data

      ensure
        DualEncoding.connection.drop_table('dual_encodings')
      end

      def test_type_cast_should_not_mutate_encoding
        name  = 'hello'.force_encoding(Encoding::ASCII_8BIT)
        Owner.create(name: name)
        assert_equal Encoding::ASCII_8BIT, name.encoding
      ensure
        Owner.delete_all
      end

      def test_execute
        with_example_table do
          @conn.execute "INSERT INTO ex (number) VALUES (10)"
          records = @conn.execute "SELECT * FROM ex"
          assert_equal 1, records.length

          record = records.first
          assert_equal 10, record['number']
          assert_equal 1, record['id']
        end
      end

      def test_quote_string
        assert_equal "''", @conn.quote_string("'")
      end

      def test_insert_sql
        with_example_table do
          2.times do |i|
            rv = @conn.insert_sql "INSERT INTO ex (number) VALUES (#{i})"
            assert_equal(i + 1, rv)
          end

          records = @conn.execute "SELECT * FROM ex"
          assert_equal 2, records.length
        end
      end

      def test_insert_sql_logged
        with_example_table do
          sql = "INSERT INTO ex (number) VALUES (10)"
          name = "foo"
          assert_logged [[sql, name, []]] do
            @conn.insert_sql sql, name
          end
        end
      end

      def test_insert_id_value_returned
        with_example_table do
          sql = "INSERT INTO ex (number) VALUES (10)"
          idval = 'vuvuzela'
          id = @conn.insert_sql sql, nil, nil, idval
          assert_equal idval, id
        end
      end

      def test_select_rows
        with_example_table do
          2.times do |i|
            @conn.create "INSERT INTO ex (number) VALUES (#{i})"
          end
          rows = @conn.select_rows 'select number, id from ex'
          assert_equal [[0, 1], [1, 2]], rows
        end
      end

      def test_select_rows_logged
        with_example_table do
          sql = "select * from ex"
          name = "foo"
          assert_logged [[sql, name, []]] do
            @conn.select_rows sql, name
          end
        end
      end

      def test_transaction
        with_example_table do
          count_sql = 'select count(*) from ex'

          @conn.begin_db_transaction
          @conn.create "INSERT INTO ex (number) VALUES (10)"

          assert_equal 1, @conn.select_rows(count_sql).first.first
          @conn.rollback_db_transaction
          assert_equal 0, @conn.select_rows(count_sql).first.first
        end
      end

      def test_tables
        with_example_table do
          assert_equal %w{ ex }, @conn.tables
          with_example_table 'id integer PRIMARY KEY AUTOINCREMENT, number integer', 'people' do
            assert_equal %w{ ex people }.sort, @conn.tables.sort
          end
        end
      end

      def test_tables_logs_name
        sql = <<-SQL
          SELECT name FROM sqlite_master
          WHERE type = 'table' AND NOT name = 'sqlite_sequence'
        SQL
        assert_logged [[sql.squish, 'SCHEMA', []]] do
          @conn.tables('hello')
        end
      end

      def test_indexes_logs_name
        with_example_table do
          assert_logged [["PRAGMA index_list(\"ex\")", 'SCHEMA', []]] do
            @conn.indexes('ex', 'hello')
          end
        end
      end

      def test_table_exists_logs_name
        with_example_table do
          sql = <<-SQL
            SELECT name FROM sqlite_master
            WHERE type = 'table'
            AND NOT name = 'sqlite_sequence' AND name = \"ex\"
          SQL
          assert_logged [[sql.squish, 'SCHEMA', []]] do
            assert @conn.table_exists?('ex')
          end
        end
      end

      def test_columns
        with_example_table do
          columns = @conn.columns('ex').sort_by { |x| x.name }
          assert_equal 2, columns.length
          assert_equal %w{ id number }.sort, columns.map { |x| x.name }
          assert_equal [nil, nil], columns.map { |x| x.default }
          assert_equal [true, true], columns.map { |x| x.null }
        end
      end

      def test_columns_with_default
        with_example_table 'id integer PRIMARY KEY AUTOINCREMENT, number integer default 10' do
          column = @conn.columns('ex').find { |x|
            x.name == 'number'
          }
          assert_equal 10, column.default
        end
      end

      def test_columns_with_not_null
        with_example_table 'id integer PRIMARY KEY AUTOINCREMENT, number integer not null' do
          column = @conn.columns('ex').find { |x| x.name == 'number' }
          assert_not column.null, "column should not be null"
        end
      end

      def test_indexes_logs
        with_example_table do
          assert_logged [["PRAGMA index_list(\"ex\")", "SCHEMA", []]] do
            @conn.indexes('ex')
          end
        end
      end

      def test_no_indexes
        assert_equal [], @conn.indexes('items')
      end

      def test_index
        with_example_table do
          @conn.add_index 'ex', 'id', unique: true, name: 'fun'
          index = @conn.indexes('ex').find { |idx| idx.name == 'fun' }

          assert_equal 'ex', index.table
          assert index.unique, 'index is unique'
          assert_equal ['id'], index.columns
        end
      end

      def test_non_unique_index
        with_example_table do
          @conn.add_index 'ex', 'id', name: 'fun'
          index = @conn.indexes('ex').find { |idx| idx.name == 'fun' }
          assert_not index.unique, 'index is not unique'
        end
      end

      def test_compound_index
        with_example_table do
          @conn.add_index 'ex', %w{ id number }, name: 'fun'
          index = @conn.indexes('ex').find { |idx| idx.name == 'fun' }
          assert_equal %w{ id number }.sort, index.columns.sort
        end
      end

      def test_primary_key
        with_example_table do
          assert_equal 'id', @conn.primary_key('ex')
          with_example_table 'internet integer PRIMARY KEY AUTOINCREMENT, number integer not null', 'foos' do
            assert_equal 'internet', @conn.primary_key('foos')
          end
        end
      end

      def test_no_primary_key
        with_example_table 'number integer not null' do
          assert_nil @conn.primary_key('ex')
        end
      end

      def test_supports_extensions
        assert_not @conn.supports_extensions?, 'does not support extensions'
      end

      def test_respond_to_enable_extension
        assert @conn.respond_to?(:enable_extension)
      end

      def test_respond_to_disable_extension
        assert @conn.respond_to?(:disable_extension)
      end

      private

      def assert_logged logs
        subscriber = SQLSubscriber.new
        subscription = ActiveSupport::Notifications.subscribe('sql.active_record', subscriber)
        yield
        assert_equal logs, subscriber.logged
      ensure
        ActiveSupport::Notifications.unsubscribe(subscription)
      end

      def with_example_table(definition = nil, table_name = 'ex', &block)
        definition ||= <<-SQL
          id integer PRIMARY KEY AUTOINCREMENT,
          number integer
        SQL
        super(@conn, table_name, definition, &block)
      end
    end
  end
end