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_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 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_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 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_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_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_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");
}
}
}
