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