public async Task When_searching_for_all_records_in_a_table_not_matching_any_of_the_given_keywords()
{
using (var conn = new SQLiteInMemoryConnection())
{
await Given_a_logTtable_and_an_ftsTable(conn);
var logs = new[]
{
new FTSContext.Log {
Level = FTSContext.Level.Debug, Message = "There is a Cat"
},
new FTSContext.Log {
Level = FTSContext.Level.Info, Message = "There is a Dog"
},
new FTSContext.Log {
Level = FTSContext.Level.Warn, Message = "There is a Cat and a Dog"
},
new FTSContext.Log {
Level = FTSContext.Level.Warn, Message = "There is a Parrot"
}
};
var repo = conn.GetDBContext <FTSContext.Log>(SQLiteDialect.Instance);
await repo.Insert(logs);
var term = Term <FTSContext.Log> .All
.AndNot(Match.Any, l => l.Message, "Cat", "Dog");
var result = (await conn.Search(term)).ToArray();
result.Length.ShouldBe(1);
result[0].Level.ShouldBe(logs[3].Level);
result[0].Message.ShouldBe(logs[3].Message);
}
}
public void When_searching_for_non_existing_table()
{
using (var conn = new SQLiteInMemoryConnection())
{
var selectAllTerm = Term <FTSContext.Log> .All;
Should.Throw <SQLiteException>(async() => await conn.Search(selectAllTerm))
.Message.ShouldBe("SQL logic error\r\nno such table: Log");
}
}
public void When_binding_function_to_a_closed_connection()
{
using (var conn = new SQLiteInMemoryConnection())
{
var func = new SQLiteScalarFunction("Foo", 1, args => null);
Should.Throw <InvalidOperationException>(() => conn.BindFunction(func))
.Message.ShouldBe("Cannot bind a function to a closed.");
}
}
public async Task When_checking_if_table_exists_overriding_model_table()
{
using (var conn = new SQLiteInMemoryConnection())
{
(await conn.Exists("Person")).ShouldBeFalse();
await conn.ExecuteAsync(TableQuery);
(await conn.Exists("Person")).ShouldBeTrue();
}
}
public async Task When_checking_if_table_exists_aliased_models()
{
using (var conn = new SQLiteInMemoryConnection())
{
(await conn.Exists <MyPerson>()).ShouldBeFalse();
await conn.ExecuteAsync(TableQuery);
(await conn.Exists <MyPerson>()).ShouldBeTrue();
}
}
public async Task When_searching_for_records_in_a_table_for_all_of_the_supplied_keywords()
{
using (var conn = new SQLiteInMemoryConnection())
{
await Given_a_logTtable_and_an_ftsTable(conn);
var logs = new[]
{
new FTSContext.Log {
Level = FTSContext.Level.Debug, Message = "John is playing football"
},
new FTSContext.Log {
Level = FTSContext.Level.Info, Message = "Mary is playing football"
},
new FTSContext.Log {
Level = FTSContext.Level.Warn, Message = "Someone is playing football"
},
new FTSContext.Log {
Level = FTSContext.Level.Warn, Message = "John with Mary are playing football"
},
new FTSContext.Log {
Level = FTSContext.Level.Warn, Message = "football is awesome"
}
};
var repo = conn.GetDBContext <FTSContext.Log>(SQLiteDialect.Instance);
await repo.Insert(logs);
var term = Term <FTSContext.Log> .All.And(Match.All, l => l.Message, "John", "Mary", "football");
var result1 = (await conn.Search(term)).ToArray();
result1.Length.ShouldBe(1);
result1[0].Level.ShouldBe(logs[3].Level);
result1[0].Message.ShouldBe(logs[3].Message);
term.Clear();
term.And(Match.All, l => l.Message, "play*", "football");
var result2 = (await conn.Search(term)).ToArray();
result2.Length.ShouldBe(4);
result2[0].Level.ShouldBe(logs[0].Level);
result2[0].Message.ShouldBe(logs[0].Message);
result2[1].Level.ShouldBe(logs[1].Level);
result2[1].Message.ShouldBe(logs[1].Message);
result2[2].Level.ShouldBe(logs[2].Level);
result2[2].Message.ShouldBe(logs[2].Message);
result2[3].Level.ShouldBe(logs[3].Level);
result2[3].Message.ShouldBe(logs[3].Message);
}
}
public async Task When_checking_table_exists()
{
using (var conn = new SQLiteInMemoryConnection())
{
(await conn.Exists <Person>()).ShouldBeFalse();
conn.State.ShouldBe(ConnectionState.Open);
await conn.ExecuteAsync(TableQuery);
(await conn.Exists <Person>()).ShouldBeTrue();
}
}
public void When_getting_table_info_of_a_non_existing_table()
{
using (var conn = new SQLiteInMemoryConnection())
{
var ex1 = Should.Throw <InvalidOperationException>(() => conn.GetTableInfo <Person>());
ex1.Message.ShouldBe("Table: [Person] does not exist.");
var ex2 = Should.Throw <InvalidOperationException>(() => conn.GetTableInfo("Bambolini"));
ex2.Message.ShouldBe("Table: Bambolini does not exist.");
}
}
public void When_creating_connection_with_valid_connection_string()
{
using (var conn = new SQLiteInMemoryConnection(SQLiteConnectionStringProvider.GetInMemoryConnectionString()))
{
conn.ConnectionString.ShouldBe("Data Source=:memory:");
conn.ConnectionTimeout.ShouldBe(15);
conn.DataSource.ShouldBeNull();
conn.Database.ShouldBe("main");
conn.ServerVersion.ShouldBe("3.21.0");
conn.State.ShouldBe(ConnectionState.Closed);
}
}
public async Task When_searching_for_all_records_in_empty_table()
{
using (var conn = new SQLiteInMemoryConnection())
{
await Given_a_logTtable_and_an_ftsTable(conn);
var selectAllTerm = Term <FTSContext.Log> .All;
var result = await conn.Search(selectAllTerm);
result.ShouldBeEmpty();
}
}
public async Task When_executing_some_sql()
{
using (var conn = new SQLiteInMemoryConnection())
{
await conn.ExecuteAsync(TableQuery);
(await conn.ExecuteScalarAsync <int>("SELECT COUNT(*) FROM Person")).ShouldBe(0);
var allRows = await conn.QueryAsync <dynamic>("SELECT * FROM Person");
allRows.ShouldBeEmpty();
var ex = Should.Throw <SQLiteException>(async() => await conn.ExecuteAsync("SELECT * FROM SomeTable;"));
ex.Message.ShouldBe("SQL logic error\r\nno such table: SomeTable");
ex.InnerException.ShouldBeNull();
}
}
public void When_changing_connection_state()
{
var conn = new SQLiteInMemoryConnection();
conn.State.ShouldBe(ConnectionState.Closed);
conn.Open();
conn.State.ShouldBe(ConnectionState.Open);
conn.Close();
conn.State.ShouldBe(ConnectionState.Open);
conn.Dispose();
Should.Throw <ObjectDisposedException>(() => conn.State.ShouldBe(ConnectionState.Closed))
.Message.ShouldBe("Cannot access a disposed object.\r\nObject name: 'SQLiteConnection'.");
}
public async Task When_binding_a_collation_function_to_an_opened_connection_table()
{
using (var conn = new SQLiteInMemoryConnection())
{
conn.Open();
Func <string, string, int> compare = (one, two) => 1;
conn.BindFunction(new SQLiteCollationFunction("FunkyCompare", compare));
await conn.ExecuteAsync("CREATE TABLE Numbers(No TEXT NOT NULL COLLATE FunkyCompare);");
await conn.ExecuteAsync("INSERT INTO Numbers VALUES (1), (2), (3), (4);");
var defaultCollation = await conn.QueryAsync <string>("SELECT No FROM Numbers ORDER BY No ASC;");
defaultCollation.ShouldBe(new[] { "4", "3", "2", "1" });
}
}
public async Task When_binding_a_scalar_function_to_an_opened_connection()
{
using (var conn = new SQLiteInMemoryConnection())
{
string receivedArg = null;
var func = new SQLiteScalarFunction("Funky", 1, args =>
{
receivedArg = args[0].ToString();
return("bar");
});
conn.Open();
conn.BindFunction(func);
var foo = (await conn.QueryAsync <string>("SELECT \"foo\"")).First();
foo.ShouldBe("foo");
var bar = (await conn.QueryAsync <string>("SELECT Funky(\"foo\")")).First();
bar.ShouldBe("bar");
receivedArg.ShouldBe("foo");
}
}
public async Task When_searching_for_all_records_in_a_table()
{
using (var conn = new SQLiteInMemoryConnection())
{
await Given_a_logTtable_and_an_ftsTable(conn);
var logs = new[]
{
new FTSContext.Log {
Level = FTSContext.Level.Debug, Message = "There is a Cat"
},
new FTSContext.Log {
Level = FTSContext.Level.Debug, Message = "There is a Dog"
},
new FTSContext.Log {
Level = FTSContext.Level.Debug, Message = "There is a Cat and a Dog"
}
};
var repo = conn.GetDBContext <FTSContext.Log>(SQLiteDialect.Instance);
await repo.Insert(logs);
var selectAllTerm = Term <FTSContext.Log> .All;
var result = (await conn.Search(selectAllTerm)).ToArray();
result.Length.ShouldBe(3);
result[0].Level.ShouldBe(logs[0].Level);
result[0].Message.ShouldBe(logs[0].Message);
result[1].Level.ShouldBe(logs[1].Level);
result[1].Message.ShouldBe(logs[1].Message);
result[1].Level.ShouldBe(logs[1].Level);
result[1].Message.ShouldBe(logs[1].Message);
result[2].Level.ShouldBe(logs[2].Level);
result[2].Message.ShouldBe(logs[2].Message);
}
}
public async Task When_querying_multiple_single_rows()
{
using (var conn = new SQLiteInMemoryConnection())
using (var reader = await conn.QueryMultipleAsync("SELECT 'Foo'; SELECT 666; SELECT 3.43;"))
{
reader.IsConsumed.ShouldBeFalse();
var first = await reader.ReadFirstOrDefaultAsync <string>();
reader.IsConsumed.ShouldBeFalse();
first.ShouldBe("Foo");
var second = await reader.ReadFirstOrDefaultAsync <dynamic>();
reader.IsConsumed.ShouldBeFalse();
second.ShouldBe(100);
var three = await reader.ReadFirstOrDefaultAsync <double>();
reader.IsConsumed.ShouldBeTrue();
three.ShouldBe(3.43);
}
}
public async Task When_getting_table_info_of_aliased_model()
{
using (var conn = new SQLiteInMemoryConnection())
{
await conn.ExecuteAsync(TableQuery);
var tableInfo = await conn.GetTableInfo <MyPerson>();
tableInfo.ShouldNotBeNull();
tableInfo.TableName.ShouldBe("Person");
tableInfo.SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
tableInfo.Columns.Length.ShouldBe(3);
Array.TrueForAll(tableInfo.Columns, i => i.TableName == "Person").ShouldBeTrue();
tableInfo.Columns[0].Id.ShouldBe(0);
tableInfo.Columns[0].Name.ShouldBe("Id");
tableInfo.Columns[0].Type.ShouldBe(SQLiteDataType.INTEGER);
tableInfo.Columns[0].DefaultValue.ShouldBeNull();
tableInfo.Columns[0].IsPrimaryKey.ShouldBeTrue();
tableInfo.Columns[0].NotNull.ShouldBeTrue();
tableInfo.Columns[1].Id.ShouldBe(1);
tableInfo.Columns[1].Name.ShouldBe("Name");
tableInfo.Columns[1].Type.ShouldBe(SQLiteDataType.TEXT);
tableInfo.Columns[1].DefaultValue.ShouldBeNull();
tableInfo.Columns[1].IsPrimaryKey.ShouldBeFalse();
tableInfo.Columns[1].NotNull.ShouldBeTrue();
tableInfo.Columns[2].Id.ShouldBe(2);
tableInfo.Columns[2].Name.ShouldBe("Age");
tableInfo.Columns[2].Type.ShouldBe(SQLiteDataType.INTEGER);
tableInfo.Columns[2].DefaultValue.ShouldBeNull();
tableInfo.Columns[2].IsPrimaryKey.ShouldBeFalse();
tableInfo.Columns[2].NotNull.ShouldBeTrue();
}
}
public async Task When_binding_an_aggregate_function_to_an_opened_connection()
{
using (var conn = new SQLiteInMemoryConnection())
{
conn.Open();
await conn.ExecuteAsync("CREATE TABLE Numbers(No INTEGER NOT NULL);");
await conn.ExecuteAsync("INSERT INTO Numbers VALUES (1), (2), (3), (4);");
const int InitState = 0;
Func <object[], int, object, object> step = (objects, i, state) =>
{
var newNo = Convert.ToInt32(objects[0]);
return((int)state + newNo);
};
Func <object, object> final = finalState => finalState;
conn.BindFunction(new SQLiteAggregateFunction("FunkySum", 1, InitState, step, final));
var funkySum = await conn.ExecuteScalarAsync <long>("SELECT FunkySum(No) FROM Numbers;");
funkySum.ShouldBe(10);
}
}
public async Task When_querying_multiple_multiple_rows()
{
using (var conn = new SQLiteInMemoryConnection())
{
await conn.ExecuteAsync(SQLiteSQLGenerator.Table <ModelOne>());
await conn.ExecuteAsync(SQLiteSQLGenerator.Table <ModelTwo>());
var repoOne = conn.GetDBContext <ModelOne>(SQLiteDialect.Instance);
var repoTwo = conn.GetDBContext <ModelTwo>(SQLiteDialect.Instance, "ModelTwo");
await repoOne.Insert(new[]
{
new ModelOne {
Name = "M1-A"
},
new ModelOne {
Name = "M1-B"
},
new ModelOne {
Name = "M1-C"
}
});
await repoTwo.Insert(new[]
{
new ModelTwo {
Category = "M2-C-A", Number = 1
},
new ModelTwo {
Category = "M2-C-B", Number = 2
},
new ModelTwo {
Category = "M2-C-C", Number = 3
},
new ModelTwo {
Category = "M2-C-D", Number = 4
}
});
using (var reader = await conn.QueryMultipleAsync("SELECT Id, Name FROM ModelOne; SELECT Id, Number, Category FROM ModelTwo;"))
{
reader.IsConsumed.ShouldBeFalse();
var modelOnes = (await reader.ReadAsync <ModelOne>(false)).ToArray();
reader.IsConsumed.ShouldBeFalse();
modelOnes.Length.ShouldBe(3);
modelOnes[0].Name.ShouldBe("M1-A");
modelOnes[1].Name.ShouldBe("M1-B");
modelOnes[2].Name.ShouldBe("M1-C");
var modelTwos = (await reader.ReadAsync <ModelTwo>()).ToArray();
reader.IsConsumed.ShouldBeTrue();
modelTwos.Length.ShouldBe(4);
modelTwos[0].Number.ShouldBe(1);
modelTwos[0].Category.ShouldBe("M2-C-A");
modelTwos[1].Number.ShouldBe(2);
modelTwos[1].Category.ShouldBe("M2-C-B");
modelTwos[2].Number.ShouldBe(3);
modelTwos[2].Category.ShouldBe("M2-C-C");
modelTwos[3].Number.ShouldBe(4);
modelTwos[3].Category.ShouldBe("M2-C-D");
}
}
}
public async Task When_getting_objects_of_a_table()
{
using (var conn = new SQLiteInMemoryConnection())
{
var snapshot1 = (await conn.GetDatabaseObjects()).ToArray();
snapshot1.ShouldNotBeNull();
snapshot1.ShouldBeEmpty();
await conn.ExecuteAsync(TableQuery);
var snapshot2 = (await conn.GetDatabaseObjects()).ToArray();
snapshot2.ShouldNotBeNull();
snapshot2.Length.ShouldBe(1);
snapshot2[0].Type.ShouldBe(SQLiteObjectType.Table);
snapshot2[0].Name.ShouldBe("Person");
snapshot2[0].SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
await conn.ExecuteAsync(ViewQuery);
var snapshot3 = (await conn.GetDatabaseObjects()).ToArray();
snapshot3.ShouldNotBeNull();
snapshot3.Length.ShouldBe(2);
snapshot3[0].Type.ShouldBe(SQLiteObjectType.Table);
snapshot3[0].Name.ShouldBe("Person");
snapshot3[0].SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
snapshot3[1].Type.ShouldBe(SQLiteObjectType.View);
snapshot3[1].Name.ShouldBe("Person_view");
snapshot3[1].SQL.ShouldBe(ViewQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
await conn.ExecuteAsync(TriggerQuery);
var snapshot4 = (await conn.GetDatabaseObjects()).ToArray();
snapshot4.ShouldNotBeNull();
snapshot4.Length.ShouldBe(3);
snapshot4[0].Type.ShouldBe(SQLiteObjectType.Table);
snapshot4[0].Name.ShouldBe("Person");
snapshot4[0].SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
snapshot4[1].Type.ShouldBe(SQLiteObjectType.View);
snapshot4[1].Name.ShouldBe("Person_view");
snapshot4[1].SQL.ShouldBe(ViewQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
snapshot4[2].Type.ShouldBe(SQLiteObjectType.Trigger);
snapshot4[2].Name.ShouldBe("Person_bu");
snapshot4[2].SQL.ShouldBe(TriggerQuery.Replace("IF NOT EXISTS ", string.Empty));
await conn.ExecuteAsync(IndexQuery);
var snapshot5 = (await conn.GetDatabaseObjects()).ToArray();
snapshot5.ShouldNotBeNull();
snapshot5.Length.ShouldBe(4);
snapshot5[0].Type.ShouldBe(SQLiteObjectType.Table);
snapshot5[0].Name.ShouldBe("Person");
snapshot5[0].SQL.ShouldBe(TableQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
snapshot5[1].Type.ShouldBe(SQLiteObjectType.View);
snapshot5[1].Name.ShouldBe("Person_view");
snapshot5[1].SQL.ShouldBe(ViewQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
snapshot5[2].Type.ShouldBe(SQLiteObjectType.Trigger);
snapshot5[2].Name.ShouldBe("Person_bu");
snapshot5[2].SQL.ShouldBe(TriggerQuery.Replace("IF NOT EXISTS ", string.Empty));
snapshot5[3].Type.ShouldBe(SQLiteObjectType.Index);
snapshot5[3].Name.ShouldBe("Person_idx");
snapshot5[3].SQL.ShouldBe(IndexQuery.Replace("IF NOT EXISTS ", string.Empty).Replace(";", string.Empty));
}
}
public async Task When_searching_for_all_records_in_a_table_matching_multiple_keywords()
{
using (var conn = new SQLiteInMemoryConnection())
{
await Given_a_logTtable_and_an_ftsTable(conn);
var logs = new[]
{
new FTSContext.Log {
Level = FTSContext.Level.Debug, Message = "Linux can be fun"
},
new FTSContext.Log {
Level = FTSContext.Level.Info, Message = "Windows and linux are two operating systems"
},
new FTSContext.Log {
Level = FTSContext.Level.Warn, Message = "linux is a Unix like operating system"
},
new FTSContext.Log {
Level = FTSContext.Level.Fatal, Message = "software development in Mac OS operating system may be fun!"
}
};
var repo = conn.GetDBContext <FTSContext.Log>(SQLiteDialect.Instance);
await repo.Insert(logs);
var term = Term <FTSContext.Log> .All;
var result = (await conn.Search(term)).ToArray();
result.Length.ShouldBe(4);
term.AndNot(Match.Any, l => l.Message, "Mac Os");
result = (await conn.Search(term)).ToArray();
result.Length.ShouldBe(3);
result[0].Level.ShouldBe(logs[0].Level);
result[0].Message.ShouldBe(logs[0].Message);
result[1].Level.ShouldBe(logs[1].Level);
result[1].Message.ShouldBe(logs[1].Message);
result[2].Level.ShouldBe(logs[2].Level);
result[2].Message.ShouldBe(logs[2].Message);
term.Clear();
term
.AndNot(Match.Any, l => l.Message, "Mac Os")
.And(Match.Any, l => l.Message, "operating system", "fun");
result = (await conn.Search(term)).ToArray();
result.Length.ShouldBe(2);
result[0].Level.ShouldBe(logs[0].Level);
result[0].Message.ShouldBe(logs[0].Message);
result[1].Level.ShouldBe(logs[2].Level);
result[1].Message.ShouldBe(logs[2].Message);
term.Clear();
term
.AndNot(Match.Any, l => l.Message, "Mac Os")
.And(Match.Any, l => l.Message, "operating system*", "fun");
result = (await conn.Search(term)).ToArray();
result.Length.ShouldBe(3);
result[0].Level.ShouldBe(logs[0].Level);
result[0].Message.ShouldBe(logs[0].Message);
result[1].Level.ShouldBe(logs[1].Level);
result[1].Message.ShouldBe(logs[1].Message);
result[2].Level.ShouldBe(logs[2].Level);
result[2].Message.ShouldBe(logs[2].Message);
}
}