require "cases/helper"
require "models/owner"
require "tempfile"
require "support/ddl_helper"
module ActiveRecord
module ConnectionAdapters
class SQLite3AdapterTest < ActiveRecord::SQLite3TestCase
include DdlHelper
self.use_transactional_tests = 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.drop_table "ex", if_exists: true
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
# sqlite3 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
vals = [Relation::QueryAttribute.new("number", 10, Type::Value.new)]
@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_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, [Relation::QueryAttribute.new(nil, 1, Type::Value.new)])
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")')
result = @conn.exec_query(
"SELECT id, data FROM ex WHERE id = ?", nil, [Relation::QueryAttribute.new("id", "1-fuu", Type::Integer.new)])
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 IF NOT EXISTS 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", if_exists: true
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_logged
with_example_table do
sql = "INSERT INTO ex (number) VALUES (10)"
name = "foo"
assert_logged [[sql, name, []]] do
@conn.insert(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, 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
ActiveSupport::Deprecation.silence { assert_equal %w{ ex }, @conn.tables }
with_example_table "id integer PRIMARY KEY AUTOINCREMENT, number integer", "people" do
ActiveSupport::Deprecation.silence { 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 IN ('table','view') AND name <> 'sqlite_sequence'
SQL
assert_logged [[sql.squish, "SCHEMA", []]] do
ActiveSupport::Deprecation.silence do
@conn.tables("hello")
end
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 IN ('table','view') AND name <> 'sqlite_sequence' AND name = 'ex'
SQL
assert_logged [[sql.squish, "SCHEMA", []]] do
ActiveSupport::Deprecation.silence do
assert @conn.table_exists?("ex")
end
end
end
end
def test_columns
with_example_table do
columns = @conn.columns("ex").sort_by(&:name)
assert_equal 2, columns.length
assert_equal %w{ id number }.sort, columns.map(&:name)
assert_equal [nil, nil], columns.map(&:default)
assert_equal [true, true], columns.map(&: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
def test_statement_closed
db = ::SQLite3::Database.new(ActiveRecord::Base.
configurations["arunit"]["database"])
statement = ::SQLite3::Statement.new(db,
"CREATE TABLE statement_test (number integer not null)")
statement.stub(:step, -> { raise ::SQLite3::BusyException.new("busy") }) do
assert_called(statement, :columns, returns: []) do
assert_called(statement, :close) do
::SQLite3::Statement.stub(:new, statement) do
assert_raises ActiveRecord::StatementInvalid do
@conn.exec_query "select * from statement_test"
end
end
end
end
end
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