public void SelectTestWithJoins_WithTableNameSet(SupportedDatabaseTypes databaseTypes, string schema, string schemaPrefixId, string expectedSql)
{
SqlAliasHelper.ClearAliases();
const string entityTableName = "EntityTable";
const string childTableName = "ChildEntityTable";
const string childChildTableName = "ChildChildEntityTable";
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId() ?? string.Empty);
var builder = dbConfig.SqlBuilder();
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 schemaPrefixId, string expectedSql)
{
SqlAliasHelper.ClearAliases();
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(null, schemaPrefixId, SupportedDatabaseTypes.Postgres);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId() ?? string.Empty);
var builder = dbConfig.SqlBuilder();
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 schemaPrefixId, string expectedSql)
{
var useSchema = !string.IsNullOrEmpty(schema);
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId() ?? string.Empty);
var builder = dbConfig.SqlBuilder();
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 UpdateTest_WithTableNameSet2(SupportedDatabaseTypes databaseTypes, string schema, string schemaPrefixId, string expectedSql)
{
const string tableName = "EntityTable";
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.SqlBuilder();
var update = builder.Update <Entity>(tableName);
var sql = update
.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);
}
public void UpdateTestWithWhereIfTest(SupportedDatabaseTypes databaseTypes, string schema, string schemaPrefixId, string expectedSql)
{
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
var ifStatementResult = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.SqlBuilder();
var update = builder.Update <Entity>();
var sql = update
.OnSchema(schema, () => schema != null)
.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(SupportedDatabaseTypes databaseTypes, string schema, string schemaPrefixId, string expectedSql)
{
var useSchema = !string.IsNullOrEmpty(schema);
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.SqlBuilder();
var insert = builder.Insert <Entity>();
var sql = insert
.OnSchema(setSchemaNameIfExpressionIsEvaluatedToTrue: () => useSchema)
.Fields(x => x.FP(f => f.Id, "IdParam"))
.Fields(x => x.FV(f => f.Name, "Arild"))
.Fields(x => x.FP(f => f.Description))
.Build();
sql.Should().Be(expectedSql);
}
public void InsertTest_WithEnumAndDirectProperty(SupportedDatabaseTypes databaseTypes, string schema, string schemaPrefixId, string expectedSql)
{
var useSchema = !string.IsNullOrEmpty(schema);
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId());
var builder = dbConfig.SqlBuilder();
var resolver = dbConfig.DatabaseParameterResolver();
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))
.Fields(x => x.FP(f => f.EntityEnum))
.Fields(x => x.FP("SomeProperty"))
.Build();
sql.Should().Be(expectedSql);
}
public void SelectCountTestOneTableOnly(SupportedDatabaseTypes databaseTypes, string schema, string schemaPrefixId, string expectedSql)
{
SqlAliasHelper.ClearAliases();
var dbConfig = SqlBuilderFactory.DbConfigSchemaTargets(schema, schemaPrefixId, databaseTypes);
var useSchema = !string.IsNullOrEmpty(schema);
expectedSql = string.Format(expectedSql, dbConfig.Schema, dbConfig.GetSchemaPrefixId() ?? string.Empty);
var builder = dbConfig.SqlBuilder();
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);
}
}