public void SelectCountTestOneTableOnly(SupportedDatabaseTypes databaseTypes, string schema, string expectedSql)
{
var dbConfig = OverrideConfig.CreateTestDbConfig(databaseTypes, schema);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var select = builder.Select();
var sqls = new List <string>
{
select
.Count()
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
select
.Count()
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build()
};
foreach (var sql in sqls)
{
sql.Should().Be(expectedSql);
}
}
public void SelectTestWithJoins_WithTableNameSet(SupportedDatabaseTypes databaseTypes, string schema, string expectedSql)
{
const string entityTableName = "EntityTable";
const string childTableName = "ChildEntityTable";
const string childChildTableName = "ChildChildEntityTable";
var dbConfig = OverrideConfig.CreateTestDbConfig(databaseTypes, schema);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var select = builder.Select();
var sqls = new List <string>
{
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>(entityTableName)
.InnerJoin <Entity, ChildEntity>(fromTableName: entityTableName, toTableName: childTableName)
.LeftOuterJoin <ChildEntity, ChildChildEntity>(fromTableName: childTableName, toTableName: childChildTableName)
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.From <Entity>(entityTableName)
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.InnerJoin <Entity, ChildEntity>(fromTableName: entityTableName, toTableName: childTableName)
.LeftOuterJoin <ChildEntity, ChildChildEntity>(fromTableName: childTableName, toTableName: childChildTableName)
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
@select
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.InnerJoin <Entity, ChildEntity>(fromTableName: entityTableName, toTableName: childTableName)
.LeftOuterJoin <ChildEntity, ChildChildEntity>(fromTableName: childTableName, toTableName: childChildTableName)
.Fields <ChildEntity>(x => x.F(item => item.Description))
.Fields <ChildEntity>(x => x.F(item => item.Relation))
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.Where <Entity>(x => x.WV(item => item.Name, "Arild"))
.Build(),
};
foreach (var sql in sqls)
{
sql.Should().Be(expectedSql);
}
}
public void SelectWithIfStatementTest(bool ifStatementResult, string expectedSql)
{
var dbConfig = OverrideConfig.CreateTestDbConfig(SupportedDatabaseTypes.Postgres);
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var sql =
builder.Select()
.Fields <Entity>(x => x.F(item => item.Name))
.Fields <Entity>(x => x.F(item => item.Description))
.Where <Entity>(x => x.WP(item => item.Id, "IdParam"))
.WhereIf <Entity>(x => x.WV(item => item.Name, "Arild"), () => ifStatementResult)
.Build();
sql.Should().Be(expectedSql);
}
public void DeleteTest(SupportedDatabaseTypes databaseTypes, string schema, string expectedSql)
{
var useSchema = !string.IsNullOrEmpty(schema);
var dbConfig = OverrideConfig.CreateTestDbConfig(databaseTypes, schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var delete = builder.Delete <Entity>();
var sql = delete
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Where(x => x.WP(item => item.Id, "IdParam"))
.Where(x => x.WV(item => item.Name, "Arild"))
.Build();
sql.Should().Be(expectedSql);
}
public void UpdateTestWithWhereIfTest(SupportedDatabaseTypes databaseTypes, string schema, string expectedSql)
{
var dbConfig = OverrideConfig.CreateTestDbConfig(databaseTypes, schema);
var ifStatementResult = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var update = builder.Update <Entity>();
var sql = update
.Fields(x => x.FP(f => f.Description)
.FV(f => f.Name, "Arild"))
.Where(x => x.WP(item => item.Id, "IdParam"))
.WhereIf(x => x.WV(item => item.Name, "Arild", OP.DI), () => ifStatementResult)
.Build();
sql.Should().Be(expectedSql);
}
public void InsertTest_WithSequence(SupportedDatabaseTypes databaseTypes, string schema, string expectedSql)
{
var useSchema = !string.IsNullOrEmpty(schema);
var dbConfig = OverrideConfig.CreateTestDbConfig(databaseTypes, schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var resolver = dbConfig.CreateParameterResolver();
var insert = builder.Insert <Entity>();
var sql = insert
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields(x => x.FV(f => f.Id, resolver.WithNextSequence("seq"), true))
.Fields(x => x.FV(f => f.Name, "Arild"))
.Fields(x => x.FP(f => f.Description))
.Build();
sql.Should().Be(expectedSql);
}
public void UpdateTest_WithTableNameSet(SupportedDatabaseTypes databaseTypes, string schema, string expectedSql)
{
const string tableName = "EntityTable";
var dbConfig = OverrideConfig.CreateTestDbConfig(databaseTypes, schema);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema);
var builder = dbConfig.CreateSqlBuilder();
var update = builder.Update <Entity>();
var sql = update
.OnTable(tableName)
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields(x => x.FP(f => f.Description)
.FV(f => f.Name, "Arild"))
.Where(x => x.WP(item => item.Id, "IdParam"))
.Build();
sql.Should().Be(expectedSql);
}