public void TestSqlLike()
{
var expression = DbFluent.Like(
DbFluent.Table("MyTable"),
DbFluent.Concat("%", DbFluent.Parameter("myText").AsExp(), "%"));
var log = new BdoLog();
string expectedResult = @"$sqlLike($sqlTable('MyTable'), $sqlConcat($sqlText('%'), $sqlParameter('myText'), $sqlText('%')))";
string result = (string)expression;
string xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
// scripted
result = _appHost.Interpreter.Evaluate <string>(
expression,
new ScriptVariableSet().SetDbBuilder(new DbQueryBuilder_PostgreSql()),
log: log);
expectedResult = @"(""MyTable"" like concat('%', |*|p:myText|*|, '%'))";
xml = "";
if (log.HasErrorsOrExceptions())
{
xml = ". Result was '" + log.ToXml();
}
Assert.That(result.Trim().Equals(expectedResult.Trim(), StringComparison.OrdinalIgnoreCase), "Bad script interpretation" + xml);
}
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
internal IDbQuery SelectEmployeeWithCode5(string code)
{
return(this.UseQuery("SelectEmployeeWithCode5", p =>
p.SelectQuery <DbEmployee>()
.WithFields(Tuple("Fields_SelectEmployee"))
.Where(q => DbFluent.Exists(
q.UseSubQuery(
DbFluent.SelectQuery(DbFluent.Table("Employee", "Mdm"))
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text))))))
)
.WithParameters(
ElementFactory.CreateScalar("code", code)));
}
public void AddFields()
{
var log = new BdoLog();
var tuple = DbFluent.Tuple(
DbFluent.Field("field1", DbFluent.Table("Table1")),
DbFluent.Field("field2", DbFluent.Table("Table1")),
DbFluent.Field("field3", DbFluent.Table("Table1")).WithAlias("f3"))
.AddFields(
DbFluent.FieldAsLiteral("field1", 1),
DbFluent.FieldAsLiteral("field2", DbFluent.Table("Table2"), 2).WithAlias("f3"));
Assert.That(tuple.Fields.Count == 3, "Bad script interpretation");
}
/// <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);
/// <summary>
///
/// </summary>
/// <param name="code"></param>
/// <returns></returns>
internal IDbQuery SelectEmployeeWithCode4(string code)
{
return(this.UseQuery("SelectEmployeeWithCode4", p =>
p.SelectQuery <DbEmployee>()
.From(
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
DbFluent.Table("RegionalDirectorate").WithAlias("directorate"),
JoinCondition("Employee_RegionalDirectorate", null, "directorate")))
.WithFields(Tuple("Fields_SelectEmployee"))
.WithLimit(100)
.AddIdField(q => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), q.UseParameter("code", DataValueTypes.Text)))
.WithCTE(
DbFluent.TableAsQuery(
DbFluent.SelectQuery(Table("RegionalDirectorate"))).WithAlias("directorate")
))
.WithParameters(
ElementFactory.CreateScalar("code", code)));
}
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);
}
/// <summary>
/// Updates the regional directorate of the specified Fidal employee.
/// </summary>
/// <param name="regionalDirectorateCode">The code of the regional directorate to consider.</param>
/// <returns>Returns the query.</returns>
internal IDbQuery UpdateEmployee2(string regionalDirectorateCode)
{
return(this.UseQuery("UpdateEmployee2", p =>
DbFluent.UpdateQuery(Table("DbEmployee", "employee"))
.AddField(q => DbFluent.Field <DbEmployee>(q => q.RegionalDirectorateId).AsNull())
.From(Table("RegionalDirectorate", "regionalDirectorate"))
.AddIdField(q =>
DbFluent.FieldAsOther <DbEmployee>(
t => t.RegionalDirectorateId, Table("DbEmployee", "employee"),
DbFluent.Field <DbRegionalDirectorate>(
t => t.RegionalDirectorateId, Table("RegionalDirectorate").WithAlias("regionalDirectorate"))))
.AddIdField(q =>
DbFluent.FieldAsParameter <DbEmployee>(
t => t.Code,
DbFluent.Table("employee"),
q.UseParameter("regionalDirectorateCode", DataValueTypes.Text)))
.WithReturnedIdFields(Field("Code", "DbEmployee", "employee"))
)
.WithParameters(
ElementFactory.CreateScalar("regionalDirectorateCode", regionalDirectorateCode)));
}
/// <summary>
///
/// </summary>
/// <param name="builder"></param>
public void OnCreating_Test1()
{
AddTable <DbEmployee>(DbFluent.Table(nameof(DbEmployee).Substring(2), "Mdm"))
.AddTable("RegionalDirectorate", DbFluent.Table(nameof(DbRegionalDirectorate).Substring(2), "Mdm"))
.AddTable <DbContact>(DbFluent.Table(nameof(DbContact).Substring(2), "Mdm"))
.AddRelationship("Employee_RegionalDirectorate",
Table <DbEmployee>(), Table("RegionalDirectorate"),
(nameof(DbEmployee.EmployeeId), nameof(DbRegionalDirectorate.RegionalDirectorateId)))
.AddRelationship <DbEmployee, DbContact>((p => p.MainContactId, q => q.ContactId))
.AddRelationship <DbEmployee, DbContact>("secondary", (p => p.MainContactId, q => q.ContactId))
.AddTuple("Fields_SelectEmployee",
DbFluent.FieldAsAll(Table <DbEmployee>()),
DbFluent.Field(nameof(DbRegionalDirectorate.RegionalDirectorateId), Table("RegionalDirectorate")),
DbFluent.Field(nameof(DbRegionalDirectorate.Code), Table("RegionalDirectorate")))
.AddTuple("Fields_SelectEmployee2",
DbFluent.FieldAsAll(Table <DbEmployee>("employee")),
DbFluent.Field <DbContact>(p => p.Code, Table <DbContact>("contact")).WithAlias("contactCode"),
DbFluent.Field(nameof(DbRegionalDirectorate.RegionalDirectorateId), Table("RegionalDirectorate", "regionalDirectorate")),
DbFluent.Field(nameof(DbRegionalDirectorate.Code), Table("RegionalDirectorate", "regionalDirectorate")))
.AddQuery(
DbFluent.StoredQuery(
DbFluent.SelectQuery(Table <DbEmployee>())
.From(
Table <DbEmployee>(),
DbFluent.TableAsJoin(
DbQueryJoinKind.Left,
Table("RegionalDirectorate"),
JoinCondition("Employee_RegionalDirectorate")))
.WithFields(Tuple("Fields_SelectEmployee"))
.AddIdField(p => DbFluent.FieldAsParameter(nameof(DbEmployee.Code), p.UseParameter("code")))));
}
/// <summary>
///
/// </summary>
public void OnCreating_Test2()
{
// Community
AddTable <DbCommunity>(
DbFluent.Table(nameof(DbCommunity).Substring(2), DbSchemas.Test2.ToString()),
q => q.CommunityId,
q => q.Code,
q => q.CreationDate,
q => q.LabelEN,
q => q.LabelFR,
q => q.LastModificationDate)
.AddTuple(
"SelectCommunity",
AllFields <DbCommunity>().ToArray()
)
.AddTuple(
"UpdateCommunity",
DbFluent.Field(nameof(DbCommunity.Code), Table <DbCommunity>()),
Field <DbCommunity>(q => q.LabelEN),
Field(nameof(DbCommunity.LabelFR), "DbCommunity"),
DbFluent.Field(nameof(DbCommunity.LastModificationDate), Table <DbCommunity>())
);
// Country
AddTable <DbCountry>(
DbFluent.Table(nameof(DbCountry).Substring(2), DbSchemas.Test2.ToString()),
q => q.CountryId,
q => q.Code,
q => q.CreationDate,
q => q.LabelEN,
q => q.LabelFR,
q => q.LastModificationDate)
.AddTuple(
"SelectCountry",
Field <DbCountry>(q => q.CountryId),
DbFluent.Field(nameof(DbCountry.Code), Table <DbCountry>()),
DbFluent.Field(nameof(DbCountry.CreationDate)),
DbFluent.Field(nameof(DbCountry.LabelEN)),
DbFluent.Field(nameof(DbCountry.LabelFR)),
DbFluent.Field(nameof(DbCountry.LastModificationDate)))
.AddTuple(
"UpdateCountry",
DbFluent.Field(nameof(DbCountry.Code)),
DbFluent.Field(nameof(DbCountry.LabelEN)),
DbFluent.Field(nameof(DbCountry.LabelFR)),
DbFluent.Field(nameof(DbCountry.LastModificationDate)));
// Country_Community
AddTable("Country_Community", DbFluent.Table(nameof(DbCountry_Community).Substring(2), DbSchemas.Test2.ToString()))
.AddTuple(
"SelectCountry_Community",
DbFluent.Field(nameof(DbCountry_Community.CountryId), Table("Country_Community")),
DbFluent.Field(nameof(DbCountry_Community.CommunityId), Table("Country_Community")))
.AddTuple(
"UpdateCountry",
DbFluent.Field(nameof(DbCountry_Community.CountryId), Table("Country_Community")),
DbFluent.Field(nameof(DbCountry_Community.CommunityId), Table("Country_Community")));
// CountryInformation
AddTable <DbCountryInformation>(
DbFluent.Table(nameof(DbCountryInformation).Substring(2), DbSchemas.Test2.ToString()))
.AddTuple(
"SelectCountryInformation",
DbFluent.Field(nameof(DbCountryInformation.CountryId)),
DbFluent.Field(nameof(DbCountryInformation.CreationDate)),
DbFluent.Field(nameof(DbCountryInformation.LastModificationDate)),
DbFluent.Field(nameof(DbCountryInformation.StartDate)))
.AddTuple(
"UpdateCountryInformation",
DbFluent.Field(nameof(DbCountryInformation.LastModificationDate)),
DbFluent.Field(nameof(DbCountryInformation.StartDate)));
// Relationships
AddRelationship(
"Community_Country_Community",
Table <DbCommunity>(),
Table("Country_Community"),
(nameof(DbCommunity.CommunityId), nameof(DbCountry_Community.CommunityId)))
.AddRelationship(
"Community_Country_Community",
Table <DbCountry>(),
Table("Country_Community"),
(nameof(DbCountry.CountryId), nameof(DbCountry_Community.CountryId)))
.AddRelationship <DbCountryInformation, DbCountry>(
(q => q.CountryId, q => q.CountryId));
}