/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="parameter">The parameter to consider.</param>
public static DbField FieldAsParameter <T>(
Expression <Func <T, object> > expr,
DbTable table,
ScalarElement parameter) where T : class
{
return(DbFluent.Field <T>(expr, table).AsParameter(parameter));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="otherField">The other field to consider.</param>
public static DbField FieldAsOther(
string name,
DbTable table,
DbField otherField)
{
return(DbFluent.Field(name, table).AsOther(otherField));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="query">The query to consider.</param>
public static DbField FieldAsQuery(
string name,
DbTable table,
IDbQuery query)
{
return(DbFluent.Field(name, table).AsQuery(query));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="query">The query to consider.</param>
public static DbField FieldAsQuery <T>(
Expression <Func <T, object> > expr,
DbTable table,
IDbQuery query) where T : class
{
return(DbFluent.Field <T>(expr, table).AsQuery(query));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="value">The value to consider.</param>
/// <param name="valueType">The value type to consider.</param>
public static DbField FieldAsLiteral(
string name,
object value,
DataValueTypes valueType = DataValueTypes.Any)
{
return(DbFluent.FieldAsLiteral(name, null, value, valueType));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="script">The script to consider.</param>
public static DbField FieldAsScript <T>(
Expression <Func <T, object> > expr,
DbTable table,
string script) where T : class
{
return(DbFluent.Field <T>(expr, table).AsScript(script));
}
/// <summary>
/// Evaluates the script word $SQLVALUE.
/// </summary>
/// <returns>The interpreted string value.</returns>
public override string GetSqlText_Value(object value)
{
if (value == null)
{
return(DbFluent.Null());
}
else if (value is DbQuery)
{
}
var valueType = value.GetValueType();
switch (valueType)
{
case DataValueTypes.Text:
var param1String = value as string;
return(GetSqlText_Text(param1String));
case DataValueTypes.Date:
if (value is DateTime param1DateTime)
{
return(GetSqlText_Text(param1DateTime.ToString(StringHelper.__DateFormat)));
}
break;
case DataValueTypes.Time:
if (value is TimeSpan param1TimeSpan)
{
return(GetSqlText_Text(param1TimeSpan.ToString(StringHelper.__TimeFormat)));
}
break;
}
return(value?.ToString() ?? "");
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="otherField">The other field to consider.</param>
public static DbField FieldAsOther <T>(
Expression <Func <T, object> > expr,
DbTable table,
DbField otherField) where T : class
{
return(DbFluent.Field <T>(expr, table).AsOther(otherField));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="parameterIndex">The parameter index to consider.</param>
public static DbField FieldAsParameter <T>(
Expression <Func <T, object> > expr,
DbTable table,
byte parameterIndex) where T : class
{
return(DbFluent.Field <T>(expr, table).AsParameter(parameterIndex));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="value">The value to consider.</param>
/// <param name="valueType">The value type to consider.</param>
public static DbField FieldAsLiteral(
string name,
DbTable table,
object value,
DataValueTypes valueType = DataValueTypes.Any)
{
return(DbFluent.Field(name, table).AsLiteral(value, valueType));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="value">The value to consider.</param>
public static DbField FieldAsLiteral <T>(
Expression <Func <T, object> > expr,
DbTable table,
object value) where T : class
{
var field = DbFluent.Field <T>(expr, table);
return(field.AsLiteral(value, field?.ValueType ?? DataValueTypes.None));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <param name="script">The script to consider.</param>
public static DbField FieldAsScript(
string name,
DbTable table,
string script)
{
var field = DbFluent.Field(name, table);
field.AsScript(script);
return(field);
}
private string GetSqlText_Table(
string tableDataModule,
string tableSchema,
string tableName,
string tableAlias,
IDbQuery query = null,
IDataElementSet parameterSet = null,
DbQueryFieldMode viewMode = DbQueryFieldMode.CompleteName,
string defaultDataModule = null,
string defaultSchema = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if ((viewMode == DbQueryFieldMode.CompleteName) && (!string.IsNullOrEmpty(tableAlias)))
{
queryString += GetSqlText_Table(tableAlias);
}
else if (!string.IsNullOrEmpty(tableName))
{
if ((viewMode == DbQueryFieldMode.CompleteName) || (viewMode == DbQueryFieldMode.CompleteNameOrValueAsAlias))
{
if (string.IsNullOrEmpty(tableDataModule))
{
tableDataModule = defaultDataModule;
}
if (!string.IsNullOrEmpty(tableDataModule))
{
tableDataModule = GetDatabaseName(tableDataModule);
}
if (string.IsNullOrEmpty(tableSchema))
{
tableSchema = defaultSchema;
}
string script = DbFluent.Table(tableName, tableSchema, tableDataModule);
queryString += Scope?.Interpreter.Evaluate(script, DataExpressionKind.Script, scriptVariableSet, log) ?? String.Empty;
}
else
{
queryString += GetSqlText_Table(tableName);
}
if (viewMode == DbQueryFieldMode.CompleteNameOrValueAsAlias)
{
queryString = queryString.ConcatenateIf(!string.IsNullOrEmpty(tableAlias), " as " + GetSqlText_Table(tableAlias));
}
}
return(queryString);
}
/// <summary>
/// Sorts the specified query considering the specified query script.
/// </summary>
/// <param name="query">The database query to consider.</param>
/// <param name="sortQuery">The sort query text to consider.</param>
/// <param name="definition">The definition to consider.</param>
/// <param name="log">The log to consider.</param>
/// <returns>The built query.</returns>
public static IDbSingleQuery Sort(
this IDbSingleQuery query,
string sortQuery,
DbApiSortDefinition definition = null,
IBdoLog log = null)
{
if (query != null && !string.IsNullOrEmpty(sortQuery))
{
query.OrderByClause = new DbQueryOrderByClause();
foreach (string fieldItem in sortQuery.Split(','))
{
var statement = new DbQueryOrderByStatement();
var fieldItemParams = fieldItem?.Trim().Split(' ');
if (fieldItemParams.Length > 0)
{
string fieldName = fieldItemParams[0]?.Trim();
if (!definition.ContainsKey(fieldName))
{
log?.AddError("Undefined field '" + fieldName + "' in order statement", resultCode: "user");
}
else
{
statement.Field = definition?[fieldName]?.Field ?? DbFluent.Field(fieldName);
statement.Sorting = DataSortingModes.Ascending;
if (fieldItemParams.Length > 1)
{
string direction = fieldItemParams[1]?.Trim();
if (string.Equals(direction, "desc"))
{
statement.Sorting = DataSortingModes.Descending;
query.OrderByClause.Statements.Add(statement);
}
else if (!string.Equals(direction, "asc"))
{
log?.AddError("Invalid order direction '" + direction + "'", resultCode: "user");
}
else
{
query.OrderByClause.Statements.Add(statement);
}
}
}
}
}
}
return(query);
}
private string GetSqlText_Table(
string tableDataModule,
string tableSchema,
string tableName,
string tableAlias,
DbQueryTableMode mode = DbQueryTableMode.CompleteName,
string defaultDataModule = null,
string defaultSchema = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
return(GetSqlText_Table(
DbFluent.Table(tableName, tableSchema, tableDataModule).WithAlias(tableAlias),
null, null, mode, defaultDataModule, defaultSchema, scriptVariableSet, log));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
/// <typeparam name="T">The class to consider.</typeparam>
public static DbField Field <T>(
Expression <Func <T, object> > expr,
DbTable table = null) where T : class
{
var propertyInfo = expr.GetProperty();
var name = propertyInfo?.Name;
var valueType = propertyInfo?.PropertyType.GetValueType() ?? DataValueTypes.None;
if (propertyInfo?.GetCustomAttribute(typeof(BdoDbFieldAttribute)) is BdoDbFieldAttribute fieldAttribute)
{
name = fieldAttribute.Name;
valueType = fieldAttribute.ValueType;
}
return(DbFluent.Field(name, table).WithValueType(valueType));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="table">The data table to consider.</param>
public static DbField FieldAsNull(
string name, DbTable table)
{
return(DbFluent.Field(name, table).AsNull());
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="parameterName">The parameter element to consider.</param>
public static DbField FieldAsParameter(
string name,
string parameterName)
{
return(DbFluent.FieldAsParameter(name, null, parameterName));
}
// From -------------------------------------
private string GetSqlText_FromClause(
IDbQueryFromClause clause,
IDbSingleQuery query = null,
DbQueryFromClauseKind kind = DbQueryFromClauseKind.FromPreffix,
IDataElementSet parameterSet = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
string queryString = "";
if (query != null)
{
if (query.Kind == DbQueryKind.Insert)
{
if ((clause?.Statements?.Count == 1 &&
clause?.Statements[0]?.Tables.Any(p => p is DbDerivedTable) != true) ||
(query.WhereClause != null))
{
var subQuery = DbFluent.SelectQuery(null)
.WithFields(query.Fields?.ToArray());
subQuery.FromClause = query.FromClause;
subQuery.WhereClause = query.WhereClause;
clause = new DbQueryFromClause
{
Statements = new List <DbQueryFromStatement>()
};
clause.Statements.Add(new DbQueryFromStatement()
{
Tables = new List <DbTable>()
{
DbFluent.TableAsQuery(subQuery)
}
});
}
}
if (clause == null)
{
if (query.Kind == DbQueryKind.Insert)
{
var table = DbFluent.TableAsTuples(
DbFluent.Tuple(query.Fields?.ToArray()));
queryString += GetSqlText_Table(
table, query, parameterSet,
DbQueryTableMode.CompleteName,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
else if (query.Kind != DbQueryKind.Update)
{
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
DbQueryTableMode.CompleteName,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
}
else
{
if (clause?.Expression != null)
{
string expression = Scope?.Interpreter.Evaluate(clause.Expression, scriptVariableSet, log)?.ToString() ?? "";
queryString += expression;
}
else if (!(clause?.Statements?.Count > 0))
{
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
DbQueryTableMode.CompleteNameAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
else
{
foreach (var statement in clause.Statements)
{
// if the first table is not a joined one then we add first the query's default table
if (statement.Tables == null || statement.Tables.Count == 0 || statement.Tables[0] is DbJoinedTable)
{
queryString += GetSqlText_Table(
query.DataModule, query.Schema, query.DataTable, query.DataTableAlias,
DbQueryTableMode.CompleteNameAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
if (statement.Tables?.Count > 0)
{
var existingTbale = false;
foreach (var table in statement.Tables)
{
if (query?.Kind == DbQueryKind.Delete && table is DbJoinedTable joinedTable)
{
queryString += !existingTbale ? " using " : ", ";
queryString += GetSqlText_Table(
joinedTable.Table,
query, parameterSet, DbQueryTableMode.CompleteNameAsAlias,
scriptVariableSet: scriptVariableSet, log: log);
existingTbale = true;
// we update the where clause to add relationships
if (query?.WhereClause == null)
{
query.WhereClause = new DbQueryWhereClause();
}
if (query.WhereClause.Expression == null)
{
query.WhereClause.Expression = joinedTable.Condition;
}
else
{
query.WhereClause.Expression = DbFluent.And(query.WhereClause.Expression, joinedTable.Condition);
}
}
else
{
queryString += GetSqlText_Table(
table,
query, parameterSet, DbQueryTableMode.CompleteNameAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
}
}
}
}
}
}
}
queryString = queryString.If(!string.IsNullOrEmpty(queryString),
(kind == DbQueryFromClauseKind.FromPreffix ? "from " : "") + queryString);
return(queryString);
}
// As parameter -----
/// <summary>
/// Creates a new instance of the DataElement class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="value">The data table to consider.</para>
public static IScalarElement Parameter(
string name,
object value = null)
{
return(DbFluent.Parameter(name, DataValueTypes.Any, value));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="parameterIndex">The parameter index to consider.</param>
public static DbField FieldAsParameter(
string name,
byte parameterIndex)
{
return(DbFluent.FieldAsParameter(name, null, parameterIndex));
}
/// <summary>
/// Converts the specifed search query into an extension script.
/// </summary>
/// <param name="searchQuery">The search query to consider.</param>
/// <param name="log">The </param>
/// <param name="definition">The clause statement to consider.</param>
/// <param name="i"></param>
/// <returns></returns>
internal static string ConvertToExtensionScript(
this string searchQuery,
IBdoLog log = null,
DbApiFilterDefinition definition = null,
int i = 0)
{
string script = searchQuery;
if (!string.IsNullOrEmpty(script))
{
// boolean instructions
foreach (string instruction in new string[] { "Or", "And", "Not" })
{
int j = i;
List <string> clauses = new List <string>();
script.IndexOfNextString(" " + instruction + " ", ref j);
while (j < script.Length - 1)
{
string clause = script.Substring(i, j - i + 1);
clause = clause.ConvertToExtensionScript(log, definition, 0);
clauses.Add(clause);
j = i = j + (" " + instruction + " ").Length;
script.IndexOfNextString(" " + instruction + " ", ref j);
if (j == script.Length)
{
clause = script.Substring(i);
clause = clause.ConvertToExtensionScript(log, definition, 0);
clauses.Add(clause);
}
}
if (clauses.Count > 0)
{
script = "$sql" + instruction + "(" + clauses.Aggregate((p, q) => p + "," + q) + ")";
}
}
if (i == 0)
{
DataOperators aOperator = DataOperators.None;
int k = script.Length;
string scriptOperator = null;
foreach (DataOperators currentOperator in new DataOperators[] {
DataOperators.Exists,
DataOperators.Contains,
DataOperators.Different, DataOperators.Equal,
DataOperators.GreaterOrEqual, DataOperators.Greater,
DataOperators.LesserOrEqual, DataOperators.Lesser,
DataOperators.Has, DataOperators.In
})
{
int k1 = 0;
string currentScriptOperator = DbQueryExtension.GetInstruction(currentOperator);
script.IndexOfNextString(currentScriptOperator, ref k1);
if (k1 < k)
{
scriptOperator = currentScriptOperator;
aOperator = currentOperator;
k = k1;
}
}
if (k == script.Length)
{
log.AddError("No operator found in clause '" + searchQuery + "'", resultCode: "user");
}
else
{
string scriptFunction = DbQueryExtension.GetScriptFunction(aOperator)?.Trim();
string fieldName = script.Substring(0, k)?.Trim();
string value = script.Substring(k + scriptOperator.Length)?.Trim();
if (value.Length > 2 && value.StartsWith("'") && value.EndsWith("'"))
{
value = "$sqlText('" + value.Substring(1, value.Length - 2) + "')";
}
// check that the field is in the dictionary
if (!definition.ContainsKey(fieldName))
{
log.AddError("Undefined field '" + fieldName + "' in clause '" + searchQuery + "''", resultCode: "user");
}
else
{
DbApiFilterClause clause = definition?[fieldName];
// check the instruction found corresponds to the definition in dictionary
if (!clause.Operators.Any(p => p == aOperator))
{
log.AddError("Undefined operator '" + aOperator.ToString() + "' for field '" + fieldName + "'", resultCode: "user");
}
else
{
if (clause.Field == null)
{
clause.Field = DbFluent.Field(fieldName);
}
if (aOperator == DataOperators.Has)
{
if (value.Length > 2 && value.StartsWith("{") && value.EndsWith("}"))
{
value = value.Substring(1, value.Length - 2);
}
value = value.ConvertToExtensionScript(log, clause.FilterDefinition, 0);
script = "(" + value + ")";
}
else
{
script = scriptFunction + clause.Field;
}
}
}
}
}
}
return(script);
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="query">The query to consider.</param>
public static DbField FieldAsQuery(
string name,
IDbQuery query)
{
return(DbFluent.FieldAsQuery(name, null, query));
}
// ------------------------------------------
// TABLES
// ------------------------------------------
#region Tables
private string GetSqlText_Table(
DbTable table,
IDbQuery query = null,
IDataElementSet parameterSet = null,
DbQueryTableMode mode = DbQueryTableMode.CompleteName,
string defaultDataModule = null,
string defaultSchema = null,
IScriptVariableSet scriptVariableSet = null,
IBdoLog log = null)
{
var queryString = "";
if (table?.Expression != null)
{
string expression = Scope?.Interpreter.Evaluate(table.Expression, scriptVariableSet, log)?.ToString() ?? "";
queryString += expression;
}
else if (mode == DbQueryTableMode.CompleteName && !string.IsNullOrEmpty(table?.Alias))
{
queryString += GetSqlText_Table(table.Alias);
}
else if (table is DbJoinedTable joinedTable)
{
switch (joinedTable.Kind)
{
case DbQueryJoinKind.Inner:
{
queryString += " inner join ";
break;
}
case DbQueryJoinKind.Left:
{
queryString += " left join ";
break;
}
case DbQueryJoinKind.Right:
{
queryString += " right join ";
break;
}
}
queryString += GetSqlText_Table(
joinedTable.Table,
query, parameterSet, DbQueryTableMode.CompleteNameAsAlias,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log);
if (joinedTable.Kind != DbQueryJoinKind.None)
{
queryString += " on ";
string expression = Scope?.Interpreter.Evaluate(joinedTable.Condition, scriptVariableSet, log)?.ToString() ?? string.Empty;
queryString += expression;
}
}
else
{
if (table is DbDerivedTable derivedTable)
{
string subQuery = BuildQuery(derivedTable.Query, DbQueryParameterMode.Scripted, parameterSet, scriptVariableSet, log);
UpdateParameterSet(query.ParameterSet, derivedTable.Query);
queryString = "(" + subQuery + ")";
}
else if (table is DbTupledTable tupledTable)
{
if (tupledTable?.Tuples?.Count > 0)
{
if (!string.IsNullOrEmpty(queryString))
{
queryString += ", ";
}
foreach (var tuple in tupledTable.Tuples)
{
var tupleString = string.Join(", ", tuple.Fields.Select(field =>
GetSqlText_Field(
field, query, parameterSet, DbQueryFieldMode.OnlyValue,
query.DataModule, query.Schema,
scriptVariableSet: scriptVariableSet, log: log)));
queryString += "(" + tupleString + ")";
}
}
queryString = queryString.If(!string.IsNullOrEmpty(queryString),
"(values " + queryString + ")");
}
else if (!string.IsNullOrEmpty(table?.Name))
{
var tableName = table.Name;
var tableSchema = table.Schema;
var tableDataModule = table.DataModule;
if (string.IsNullOrEmpty(tableDataModule))
{
tableDataModule = defaultDataModule;
}
if (string.IsNullOrEmpty(tableSchema))
{
tableSchema = defaultSchema;
}
if (!string.IsNullOrEmpty(tableDataModule))
{
tableDataModule = GetDatabaseName(tableDataModule);
}
string script = DbFluent.Table(tableName, tableSchema, tableDataModule);
queryString = Scope?.Interpreter.Evaluate(script, DataExpressionKind.Script, scriptVariableSet, log)?.ToString() ?? String.Empty;
}
if (!string.IsNullOrEmpty(table.Alias))
{
switch (mode)
{
case DbQueryTableMode.AliasAsCompleteName:
{
queryString = GetSqlText_Table(table.Alias) + " as " + queryString;
break;
}
case DbQueryTableMode.CompleteNameAsAlias:
{
queryString = queryString + " as " + GetSqlText_Table(table.Alias);
break;
}
default:
break;
}
}
}
return(queryString);
}
// As Null -----
/// <summary>
/// Updates the specified field as null.
/// </summary>
/// <param name="field">The field to consider.</param>
public static DbField AsNull(this DbField field)
{
return(field?.AsScript(DbFluent.Null()));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="otherField">The other field to consider.</param>
public static DbField FieldAsOther(
string name,
DbField otherField)
{
return(DbFluent.FieldAsOther(name, null, otherField));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="name">The name to consider.</param>
/// <param name="parameter">The parameter to consider.</param>
public static DbField FieldAsParameter(
string name,
ScalarElement parameter)
{
return(DbFluent.FieldAsParameter(name, null, parameter));
}
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="expr">The expression to consider.</param>
/// <param name="table">The data table to consider.</param>
public static DbField FieldAsNull <T>(
Expression <Func <T, object> > expr,
DbTable table) where T : class
{
return(DbFluent.Field <T>(expr, table).AsNull());
}
// As All ---------------------------------
/// <summary>
/// Creates a new instance of the DbField class.
/// </summary>
/// <param name="table">The data table to consider.</param>
public static DbField FieldAsAll(DbTable table)
{
return(DbFluent.Field(null, table).AsAll());
}
