public void TestInterprete_Fun_SqlEq()
{
var log = new BdoLog();
string value = null;
string fluentScript = DbFluent.Eq(
DbFluent.Field("RegionalDirectorateId"), DbFluent.IfNull(value, DbFluent.Field("RegionalDirectorateId")));
string expectedScript = "$sqlEq($sqlField('RegionalDirectorateId'), $sqlIfNull($sqlNull(), $sqlField('RegionalDirectorateId')))";
string xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(expectedScript.Equals(fluentScript, StringComparison.OrdinalIgnoreCase), "Bad fluent interpretation. Result was '" + xml);
var scriptVariableSet = new ScriptVariableSet();
scriptVariableSet.SetValue(VarSetDb.__DbBuilder,
DbQueryFactory.CreateQueryBuilder <DbQueryBuilder_PostgreSql>(GlobalVariables.AppHost));
string result = GlobalVariables.AppHost.Scope.Interpreter.Evaluate(fluentScript, DataExpressionKind.Script, scriptVariableSet, log: log)?.ToString();
string expectedResult = @"""RegionalDirectorateId""=COALESCE(NULL, ""RegionalDirectorateId"")";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee5(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.Where(q => DbFluent.Eq(DbFluent.Field("code"), code));
return(query);
}
// Join conditions ---------------------------------------
/// <summary>
///
/// </summary>
/// <param name="name"></param>
/// <param name="table1Alias"></param>
/// <param name="table2Alias"></param>
/// <returns></returns>
public DataExpression JoinCondition(
string name,
string table1Alias = null,
string table2Alias = null)
{
DbTableRelationship relationship = Relationship(name);
if (!string.IsNullOrEmpty(table1Alias))
{
relationship.Table1.Alias = table1Alias;
}
if (!string.IsNullOrEmpty(table2Alias))
{
relationship.Table2.Alias = table2Alias;
}
List <object> queryConditions = new List <object>();
foreach (var mapping in relationship.FieldMappingDictionary.Values)
{
queryConditions.Add(
DbFluent.Eq(
DbFluent.Field(mapping.Key, relationship.Table1),
DbFluent.Field(mapping.Content, relationship.Table2)));
}
return(DbFluent.And(queryConditions.ToArray()));
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee4(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.Where(q => DbFluent.Eq(DbFluent.Field("code"), q.UseParameter("code", DataValueTypes.Text)))
.WithParameters(
ElementFactory.CreateScalar("code", code));
return(query);
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee6(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.From(
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
Table("RegionalDirectorate").WithAlias("directorate"),
JoinCondition("Employee_RegionalDirectorate", null, "directorate")))
.Where(q => DbFluent.Eq(DbFluent.Field("code"), code));
return(query);
}
/// <summary>
/// Delete the specified employee.
/// </summary>
/// <param name="code">The code to consider.</param>
/// <returns>Returns the generated query.</returns>
internal IDbQuery DeleteEmployee9(string code)
{
var query = DbFluent.DeleteQuery(Table <DbEmployee>())
.From(
DbFluent.TableAsJoin(DbQueryJoinKind.Left,
Table("RegionalDirectorate").WithAlias("directorate"),
JoinCondition("Employee_RegionalDirectorate")))
.Where(q => DbFluent.Eq(DbFluent.Field("code"), q.UseParameter("code", DataValueTypes.Text)))
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)))
.WithParameters(
ElementFactory.CreateScalar("code", code));
return(query);
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
internal IDbQuery SelectEmployeeWithCode2(string code)
{
return(this.UseQuery("SelectEmployeeWithCode2", p =>
DbFluent.SelectQuery(Table <DbEmployee>())
.From(
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
Table("RegionalDirectorate"),
JoinCondition("Employee_RegionalDirectorate")))
.WithFields(Tuple("Fields_SelectEmployee"))
.WithLimit(100)
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)))
.Where(DbFluent.Eq(DbFluent.Field("field1"), DbFluent.Field("field2"))))
.WithParameters(
ElementFactory.CreateScalar("code", code)));
}
/// <summary>
///
/// </summary>
/// <param name="q"></param>
/// <param name="orderBy"></param>
/// <param name="pageSize"></param>
/// <param name="pageToken"></param>
/// <param name="log"></param>
/// <returns></returns>
internal IDbQuery ListEmployees1(
string q, string orderBy, int?pageSize = null, string pageToken = null,
IBdoLog log = null)
=> DbFluent.SelectQuery("GetMyTables", DbFluent.Table())
.WithLimit(100)
.AsDistinct()
.WithFields(
DbFluent.FieldAsAll(DbFluent.Table("table")),
DbFluent.Field("Field1", DbFluent.Table("table")),
DbFluent.Field("Field2", DbFluent.Table("table")))
.From(
DbFluent.Table(nameof(DbRegionalDirectorate).Substring(2), "schema1").WithAlias("table"),
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
DbFluent.Table("DbTable1".Substring(2), "schema2").WithAlias("table1"),
DbFluent.And(
DbFluent.Eq(
DbFluent.Field("table1key", DbFluent.Table("table1")),
DbFluent.Field(nameof(DbRegionalDirectorate.Code), DbFluent.Table("table"))),
DbFluent.Eq(
DbFluent.Field("table2key", DbFluent.Table("table2")),
DbFluent.Field(nameof(DbRegionalDirectorate.LabelEN), DbFluent.Table("table"))))),
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
DbFluent.Table("DbTable1".Substring(2), "schema2").WithAlias("table2"),
DbFluent.Eq(
DbFluent.Field("table1key", DbFluent.Table("table2")),
DbFluent.Field("Field1", DbFluent.Table("table"))))
)
.Filter(
q,
DbApiFluent.CreateFilterDefinition(
DbApiFluent.CreateFilterClause("startDate", DbFluent.Field("CreationDate", DbFluent.Table("table")), DataOperators.GreaterOrEqual),
DbApiFluent.CreateFilterClause("endDate", DbFluent.Field("LastModificationDate", DbFluent.Table("table")), DataOperators.LesserOrEqual),
DbApiFluent.CreateFilterClause("code", DbFluent.Field("Code", DbFluent.Table("table")), DataOperators.Equal)),
log)
.Sort(
orderBy,
DbApiFluent.CreateSortDefinition(
DbApiFluent.CreateSortClause("startDate", DbFluent.Field("CreationDate", DbFluent.Table("table"))),
DbApiFluent.CreateSortClause("endDate", DbFluent.Field("LastModificationDate", DbFluent.Table("table"))),
DbApiFluent.CreateSortClause("code", DbFluent.Field("Code", DbFluent.Table("table")))),
log);
public void TestInterprete_Fun_SqlEq_Null()
{
var log = new BdoLog();
// Case: value, null
string value = null;
string fluentScript1 = DbFluent.Eq(
null, DbFluent.IfNull(value, DbFluent.Field("RegionalDirectorateId", DbFluent.Table("Table1", "Schema1"))));
var scriptVariableSet = new ScriptVariableSet();
scriptVariableSet.SetValue(VarSetDb.__DbBuilder,
DbQueryFactory.CreateQueryBuilder <DbQueryBuilder_PostgreSql>(GlobalVariables.AppHost));
string result = GlobalVariables.AppHost.Scope.Interpreter.Evaluate(fluentScript1, DataExpressionKind.Script, scriptVariableSet, log: log)?.ToString();
string expectedResult = @"COALESCE(NULL, ""Schema1"".""Table1"".""RegionalDirectorateId"") is null";
string xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
// Case: null, value
string fluentScript2 = DbFluent.Eq(
DbFluent.IfNull(value, DbFluent.Field("RegionalDirectorateId", DbFluent.Table("Table1", "Schema1"))), null);
result = GlobalVariables.AppHost.Scope.Interpreter.Evaluate(fluentScript2, DataExpressionKind.Script, scriptVariableSet, log: log)?.ToString();
expectedResult = @"COALESCE(NULL, ""Schema1"".""Table1"".""RegionalDirectorateId"") is null";
xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
}
public void TestSqlIfNull()
{
var expression = DbFluent.Eq(
DbFluent.Field("fieldA"),
DbFluent.IfNull(
DbFluent.Parameter("myText"),
DbFluent.Field("fieldA")));
var log = new BdoLog();
string expectedResult = @"""fieldA""=coalesce(|*|p:myText|*|, ""fieldA"")";
var result = _appHost.Interpreter.Evaluate <string>(
expression,
new ScriptVariableSet().SetDbBuilder(new DbQueryBuilder_PostgreSql()),
log: log);
var xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
}