public void Can_get_results_from_nonreadonly_statements()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var result = connection.ExecuteScalar <string>("PRAGMA journal_mode;");
Assert.NotNull(result);
}
}
public void Ctor_unsets_read_uncommitted_when_serializable()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (connection.BeginTransaction(IsolationLevel.Serializable))
{
Assert.Equal(0L, connection.ExecuteScalar<long>("PRAGMA read_uncommitted;"));
}
}
}
public void Ctor_unsets_read_uncommitted_when_serializable()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
using (connection.BeginTransaction(IsolationLevel.Serializable))
{
Assert.Equal(0L, connection.ExecuteScalar <long>("PRAGMA read_uncommitted;"));
}
}
}
public void Ctor_sets_read_uncommitted()
{
using (var connection = new SqliteConnection("Data Source=:memory:;Cache=Shared"))
{
connection.Open();
using (connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
Assert.Equal(1L, connection.ExecuteScalar <long>("PRAGMA read_uncommitted;"));
}
}
}
public void CreateFunction_works_when_parameter_null_and_type_byteArray()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", (byte[] x) => x == null);
var result = connection.ExecuteScalar <long>("SELECT test(NULL);");
Assert.Equal(1L, result);
}
}
public void Ctor_sets_read_uncommitted()
{
using (var connection = new SqliteConnection("Data Source=:memory:;Cache=Shared"))
{
connection.Open();
using (connection.BeginTransaction(IsolationLevel.ReadUncommitted))
{
Assert.Equal(1L, connection.ExecuteScalar<long>("PRAGMA read_uncommitted;"));
}
}
}
public void CreateFunction_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", 1L, (long state, long x, int y) => $"{state} {x} {y}");
var result = connection.ExecuteScalar <string>("SELECT test(2, 3);");
Assert.Equal("1 2 3", result);
}
}
public void CreateFunction_works_when_parameter_empty_blob()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", (byte[] x) => x?.Length == 0);
var result = connection.ExecuteScalar <long>("SELECT test(X'');");
Assert.Equal(1L, result);
}
}
public void CreateFunction_works_when_result_DBNull()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction <object>("test", () => DBNull.Value);
var result = connection.ExecuteScalar <object>("SELECT test();");
Assert.Equal(DBNull.Value, result);
}
}
public void CreateFunction_works_when_sqlite_exception()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction <long>("test", () => throw new SqliteException("Test", 200));
var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <long>("SELECT test();"));
Assert.Equal(Resources.SqliteNativeError(200, "Test"), ex.Message);
}
}
public void CreateFunction_works_when_null()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", () => 1L);
connection.CreateFunction("test", default(Func <long>));
var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <long>("SELECT test();"));
Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such function: test"), ex.Message);
}
}
public void CreateAggregate_works_when_params()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
connection.CreateAggregate("test", (string a, object[] args) => a + string.Join(", ", args) + "; ");
var result = connection.ExecuteScalar <string>("SELECT test(dummy) FROM dual;");
Assert.Equal("X; ", result);
}
}
public void CreateFunction_works_when_params()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction(
"test",
args => string.Join(", ", args.Select(a => a?.GetType().FullName ?? "(null)")));
var result = connection.ExecuteScalar <string>("SELECT test(1, 3.1, 'A', X'7E57', NULL);");
Assert.Equal("System.Int64, System.Double, System.String, System.Byte[], (null)", result);
}
}
public void CreateCollation_with_null_comparer_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateCollation("MY_NOCASE", (s1, s2) => string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));
connection.CreateCollation("MY_NOCASE", null);
var ex = Assert.Throws <SqliteException>(
() => connection.ExecuteScalar <long>("SELECT 'Νικοσ' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE;"));
Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such collation sequence: MY_NOCASE"), ex.Message);
}
}
public void CreateFunction_works_when_parameter_null_but_type_long()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", (long x) => x);
var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <long>("SELECT test(NULL);"));
Assert.Equal(
Resources.SqliteNativeError(SQLITE_ERROR, Resources.UDFCalledWithNull("test", 0)),
ex.Message);
}
}
public void CreateAggregate_works_when_sqlite_exception()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
connection.CreateAggregate("test", (string a) => throw new SqliteException("Test", 200));
var ex = Assert.Throws <SqliteException>(
() => connection.ExecuteScalar <string>("SELECT test() FROM dual;"));
Assert.Equal(Resources.SqliteNativeError(200, "Test"), ex.Message);
}
}
public void CreateAggregate_works_when_exception_during_final()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
connection.CreateAggregate <string, string>("test", "A", a => "B", a => throw new Exception("Test"));
var ex = Assert.Throws <SqliteException>(
() => connection.ExecuteScalar <string>("SELECT test() FROM dual;"));
Assert.Equal(Resources.SqliteNativeError(raw.SQLITE_ERROR, "Test"), ex.Message);
Assert.Equal(raw.SQLITE_ERROR, ex.SqliteErrorCode);
}
}
public void CreateAggregate_works_when_null()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE dual (dummy); INSERT INTO dual (dummy) VALUES ('X');");
connection.CreateAggregate("test", (string a) => "A");
connection.CreateAggregate("test", default(Func <string, string>));
var ex = Assert.Throws <SqliteException>(
() => connection.ExecuteScalar <long>("SELECT test() FROM dual;"));
Assert.Equal(Resources.SqliteNativeError(SQLITE_ERROR, "no such function: test"), ex.Message);
}
}
public void CreateFunction_works_when_parameter_null_but_type_double()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.CreateFunction("test", (double x) => x);
var ex = Assert.Throws <SqliteException>(() => connection.ExecuteScalar <double>("SELECT test(NULL);"));
Assert.Equal(
Resources.SqliteNativeError(raw.SQLITE_ERROR, new InvalidCastException().Message),
ex.Message);
Assert.Equal(raw.SQLITE_ERROR, ex.SqliteErrorCode);
}
}
public void CreateAggregate_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
connection.ExecuteNonQuery("CREATE TABLE dual2 (dummy1, dummy2); INSERT INTO dual2 (dummy1, dummy2) VALUES ('X', 1);");
connection.CreateAggregate(
"test",
"A",
(string a, string x, int y) => a + x + y,
a => a + "Z");
var result = connection.ExecuteScalar <string>("SELECT test(dummy1, dummy2) FROM dual2;");
Assert.Equal("AX1Z", result);
}
}
public void BackupDatabase_works()
{
using (var connection1 = new SqliteConnection("Data Source=:memory:"))
{
connection1.Open();
connection1.ExecuteNonQuery(
"CREATE TABLE Person (Name TEXT);" + "INSERT INTO Person VALUES ('Waldo');");
using (var connection2 = new SqliteConnection("Data Source=:memory:"))
{
connection2.Open();
connection1.BackupDatabase(connection2);
var name = connection2.ExecuteScalar <string>("SELECT Name FROM Person;");
Assert.Equal("Waldo", name);
}
}
}
public void Open_works_when_memory_shared()
{
var connectionString = "Data Source=people;Mode=Memory;Cache=Shared";
using (var connection1 = new SqliteConnection(connectionString))
{
connection1.Open();
connection1.ExecuteNonQuery(
"CREATE TABLE Person (Name TEXT);" + "INSERT INTO Person VALUES ('Waldo');");
using (var connection2 = new SqliteConnection(connectionString))
{
connection2.Open();
var name = connection2.ExecuteScalar <string>("SELECT Name FROM Person;");
Assert.Equal("Waldo", name);
}
}
}
public void Collations_dont_bleed_across_connections()
{
sqlite3 db;
using (var connection1 = new SqliteConnection(ConnectionString))
{
connection1.CreateCollation("COLLATION1", string.CompareOrdinal);
connection1.Open();
db = connection1.Handle !;
}
using var connection2 = new SqliteConnection(ConnectionString);
connection2.Open();
Assert.Same(db, connection2.Handle);
var collations = connection2.ExecuteScalar <string>("SELECT group_concat(name) FROM pragma_collation_list;")
.Split(",");
Assert.DoesNotContain("COLLATION1", collations);
}
public void Write_works(
byte[] expected,
long initialPosition,
byte[] buffer,
int offset,
int count)
{
using (var stream = CreateStream())
{
stream.Position = initialPosition;
stream.Write(buffer, offset, count);
Assert.Equal(initialPosition + count, stream.Position);
}
Assert.Equal(
expected,
_connection.ExecuteScalar <byte[]>(
$"SELECT {Column} FROM {Table} WHERE rowid = {Rowid}"));
}
public void CreateCollation_works_with_state()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
var list = new List <string>();
connection.CreateCollation(
"MY_NOCASE",
list,
(l, s1, s2) =>
{
l.Add("Invoked");
return(string.Compare(s1, s2, StringComparison.OrdinalIgnoreCase));
});
Assert.Equal(1L, connection.ExecuteScalar <long>("SELECT 'Νικοσ' = 'ΝΙΚΟΣ' COLLATE MY_NOCASE;"));
var item = Assert.Single(list);
Assert.Equal("Invoked", item);
}
}
public void Dispose_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
CreateTestTable(connection);
using (var transaction = connection.BeginTransaction())
{
connection.ExecuteNonQuery("INSERT INTO TestTable VALUES (1);");
transaction.Dispose();
Assert.Null(connection.Transaction);
Assert.Null(transaction.Connection);
}
Assert.Equal(0L, connection.ExecuteScalar <long>("SELECT COUNT(*) FROM TestTable;"));
}
}
public void Open_works_when_readonly()
{
using (var connection = new SqliteConnection("Data Source=readonly.db"))
{
connection.Open();
if (connection.ExecuteScalar <long>("SELECT COUNT(*) FROM sqlite_master WHERE name = 'Idomic';") == 0)
{
(connection).ExecuteNonQuery("CREATE TABLE Idomic (Word TEXT);");
}
}
using (var connection = new SqliteConnection("Data Source=readonly.db;Mode=ReadOnly"))
{
connection.Open();
var ex = Assert.Throws <SqliteException>(
() => connection.ExecuteNonQuery("INSERT INTO Idomic VALUES ('arimfexendrapuse');"));
Assert.AreEqual(SQLITE_READONLY, ex.SqliteErrorCode);
}
}
public void ReadUncommitted_doesnt_bleed_across_connections()
{
sqlite3 db;
using (var connection1 = new SqliteConnection(ConnectionString))
{
connection1.Open();
db = connection1.Handle !;
using (connection1.BeginTransaction(IsolationLevel.ReadUncommitted))
{
}
}
using var connection2 = new SqliteConnection(ConnectionString);
connection2.Open();
Assert.Same(db, connection2.Handle);
var readUncommitted = connection2.ExecuteScalar <long>("PRAGMA read_uncommitted;");
Assert.Equal(0L, readUncommitted);
}
public void ReadUncommitted_allows_dirty_reads()
{
const string connectionString = "Data Source=read-uncommitted;Mode=Memory;Cache=Shared";
using (var connection1 = new SqliteConnection(connectionString))
using (var connection2 = new SqliteConnection(connectionString))
{
connection1.Open();
connection2.Open();
connection1.ExecuteNonQuery("CREATE TABLE Data (Value); INSERT INTO Data VALUES (0);");
using (connection1.BeginTransaction())
using (connection2.BeginTransaction(IsolationLevel.ReadUncommitted))
{
connection1.ExecuteNonQuery("UPDATE Data SET Value = 1;");
var value = connection2.ExecuteScalar <long>("SELECT * FROM Data;");
Assert.Equal(1, value);
}
}
}
public void Open_decrypts_lazily_when_no_password()
{
try
{
using var connection1 = new SqliteConnection("Data Source=encrypted2.db;Password=password");
connection1.Open();
// NB: The file is only encrypted after writing
connection1.ExecuteNonQuery(
"CREATE TABLE IF NOT EXISTS data (value); INSERT INTO data (value) VALUES (1);");
using var connection2 = new SqliteConnection("Data Source=encrypted2.db");
connection2.Open();
connection2.ExecuteNonQuery("PRAGMA key = 'password';");
var value = connection2.ExecuteScalar <long>("SELECT value FROM data;");
Assert.Equal(1L, value);
}
finally
{
File.Delete("encrypted2.db");
}
}
public void Dispose_works()
{
using (var connection = new SqliteConnection("Data Source=:memory:"))
{
connection.Open();
CreateTestTable(connection);
using (var transaction = connection.BeginTransaction())
{
connection.ExecuteNonQuery("INSERT INTO TestTable VALUES (1);");
transaction.Dispose();
Assert.Null(connection.Transaction);
Assert.Null(transaction.Connection);
}
Assert.Equal(0L, connection.ExecuteScalar<long>("SELECT COUNT(*) FROM TestTable;"));
}
}
public void Open_works_when_readonly()
{
using (var connection = new SqliteConnection("Data Source=readonly.db"))
{
connection.Open();
if (connection.ExecuteScalar<long>("SELECT COUNT(*) FROM sqlite_master WHERE name = 'Idomic';") == 0)
{
connection.ExecuteNonQuery("CREATE TABLE Idomic (Word TEXT);");
}
}
using (var connection = new SqliteConnection("Data Source=readonly.db;Mode=ReadOnly"))
{
connection.Open();
var ex = Assert.Throws<SqliteException>(
() => connection.ExecuteNonQuery("INSERT INTO Idomic VALUES ('arimfexendrapuse');"));
Assert.Equal(SQLITE_READONLY, ex.SqliteErrorCode);
}
}
public void Open_works_when_memory_shared()
{
var connectionString = "Data Source=people;Mode=Memory;Cache=Shared";
using (var connection1 = new SqliteConnection(connectionString))
{
connection1.Open();
connection1.ExecuteNonQuery(
"CREATE TABLE Person (Name TEXT);" +
"INSERT INTO Person VALUES ('Waldo');");
using (var connection2 = new SqliteConnection(connectionString))
{
connection2.Open();
var name = connection2.ExecuteScalar<string>("SELECT Name FROM Person;");
Assert.Equal("Waldo", name);
}
}
}
