private SqlProvider <T> FormatGetDo(IWhereExpression whereParams, IFieldAnyExpression fieldAnyExpression)
{
var selectSql = ResolveExpression.Instance(Dialect).ResolveSelect(typeof(T).GetPropertiesInDb(true), Context.QuerySet.SelectExpression, 1, Context.QuerySet.IsDistinct);
var fromTableSql = FormatTableName();
var whereSql = whereParams.SqlCmd;
Params = whereParams.Param;
var orderbySql = ResolveExpression.Instance(Dialect).ResolveOrderBy(Context.QuerySet.OrderbyExpressionList);
var limitSql = ResolveExpression.Instance(Dialect).ResolveLimit(1);
if (fieldAnyExpression != null)
{
string selectDistinctSql = ResolveExpression.Instance(Dialect).ResolveSelect(typeof(T).GetPropertiesInDb(true), Context.QuerySet.SelectExpression, 1, true);
var di = fieldAnyExpression.WhereParam.ToDictionary();
foreach (var i in di)
{
Params.Add(i.Key, i.Value);
}
SqlString = $"{selectDistinctSql} from ({selectSql} ,jsonb_array_elements({fieldAnyExpression.ListFieldName}) as \"{ResolveExpression.FieldAnyColumnName}\" {fromTableSql} {whereSql} {orderbySql}) as {ResolveExpression.FieldAnyTableName} where {fieldAnyExpression.WhereClause} {limitSql}";
}
else
{
SqlString = $"{selectSql} {fromTableSql} {whereSql} {orderbySql} {limitSql}";
}
return(this);
}
public WhereExpression(IWhereExpression Expression, string fieldName, object value, CompareType type)
{
this.Expression = Expression;
this.FieldName = fieldName;
this.Value = value;
this.CompareType = type;
}
public IWhere <T> Where <Key>(Expression <Func <T, Key> > keySelector, object value, CompareType type = CompareType.Equal)
{
string fieldName = TryGetName(keySelector.Body);
if (!string.IsNullOrEmpty(fieldName))
{
this.Expression = new WhereExpression(this.Expression, fieldName, value, CompareType.GreaterThan);
}
return(this);
}
protected override void VisitWhere(IWhereExpression expression)
{
if (!((expression is WhereInExpression) || (expression is WhereNotInExpression)))
{
if (whereClause.Length > 0)
{
whereClause.AppendFormat(" AND ");
}
}
base.VisitWhere(expression);
}
public IContentQuery <T> Or(IWhereExpression expression)
{
IExpression exp = null;
if (this.Expression is IWhereExpression)
{
exp = new OrElseExpression((IWhereExpression)this.Expression, expression);
}
else
{
exp = new OrElseExpression(new FalseExpression(), expression);
}
return(this.Create(exp));
}
protected override void VisitWhere(IWhereExpression expression)
{
if (!((expression is WhereInExpression) || (expression is WhereNotInExpression)))
{
if (whereClause.Length > 0)
{
whereClause.Append(" && ");
this.viewNameBuilder.AppendFormat("_AND_");
}
}
base.VisitWhere(expression);
}
public IContentQuery <T> Where(IWhereExpression expression)
{
IExpression exp = null;
if (this.Expression is IWhereExpression)
{
exp = new AndAlsoExpression((IWhereExpression)Expression, expression);
}
else
{
exp = new AndAlsoExpression(new TrueExpression(), expression);
}
return(this.Create(exp));
}
/// <summary>
/// Build And/Or Expression
/// </summary>
/// <param name="logical"></param>
/// <param name="left"></param>
/// <param name="right"></param>
/// <returns></returns>
private static IWhereExpression BuildLogicalExpression(Logical logical, IWhereExpression left, IWhereExpression right)
{
//And/Or
switch (logical)
{
case Logical.And:
return(new AndAlsoExpression(left, right));
case Logical.Or:
return(new OrElseExpression(left, right));
default:
throw new NotSupportedException();
}
}
public static void SetOperand(this LogicalExpression expression, IWhereExpression operand)
{
if (expression.Operand1 == null)
{
expression.Operand1 = operand;
}
else if (expression.Operand2 == null)
{
expression.Operand2 = operand;
}
else
{
throw new InvalidOperationException("All operands are alreadey set!");
}
}
private static IWhereExpression GetExpression(IWhereExpression inner, WhereClause clause, Schema schema, IValueProvider valueProvider)
{
var binder = EngineContext.Current.Resolve <ITextContentBinder>();
var value = ParameterizedFieldValue.GetFieldValue(clause.Value1, valueProvider);
var fieldValue = binder.ConvertToColumnType(schema, clause.FieldName, value);
if (clause.Logical == Logical.Or && string.IsNullOrEmpty(value))
{
return(null);
}
switch (clause.Operator)
{
case Operator.Equals:
return(new WhereEqualsExpression(inner, clause.FieldName, fieldValue));
case Operator.NotEquals:
return(new WhereNotEqualsExpression(inner, clause.FieldName, fieldValue));
case Operator.GreaterThan:
return(new WhereGreaterThanExpression(inner, clause.FieldName, fieldValue));
case Operator.LessThan:
return(new WhereLessThanExpression(inner, clause.FieldName, fieldValue));
case Operator.Contains:
return(new WhereContainsExpression(inner, clause.FieldName, fieldValue));
case Operator.StartsWith:
return(new WhereStartsWithExpression(inner, clause.FieldName, fieldValue));
case Operator.EndsWith:
return(new WhereEndsWithExpression(inner, clause.FieldName, fieldValue));
case Operator.Between:
var value2 = ParameterizedFieldValue.GetFieldValue(clause.Value2, valueProvider);
var fieldValue2 = binder.ConvertToColumnType(schema, clause.Value2, value2);
return(new WhereBetweenExpression(inner, clause.FieldName, fieldValue, fieldValue2));
case Operator.NotNull:
return(new AndAlsoExpression(new WhereNotEqualsExpression(inner, clause.FieldName, null), new WhereNotEqualsExpression(inner, clause.FieldName, "")));
case Operator.IsNull:
return(new OrElseExpression(new WhereEqualsExpression(inner, clause.FieldName, null), new WhereEqualsExpression(inner, clause.FieldName, "")));
default:
return(null);
}
}
/// <summary>
/// Or表达式。
/// </summary>
/// <param name="action"></param>
/// <returns></returns>
public virtual IWhereExpression Or(WhereExpressionAction action)
{
if (action == null)
{
return(this);
}
using (IWhereExpression expression = CreateInstance()) {
action(expression);
string commandText = expression.CommandText;
if (!string.IsNullOrEmpty(commandText))
{
commandText = $"(\r\n{commandText}{_layerLeft})";
}
Where(commandText, "or");
}
return(this);
}
private string GetFilter(IWhereExpression query)
{
StringBuilder filter = new StringBuilder();
if (query != null)
{
if (query is WhereExpression)
{
filter.AppendLine(GetAndFilter((WhereExpression)query));
}
else
{
filter.AppendLine(GetOrFilter((OrAlsoExpression)query));
}
}
return(filter.ToString());
}
/// <summary>
/// 创建SelectCommandBuilder实例。
/// </summary>
/// <param name="dataContext">数据上下文接口。</param>
/// <param name="tableName">表名，如果commandText有值，将忽略此参数。</param>
/// <param name="commandText">命令脚本。</param>
public SelectCommandBuilder(IDataContext dataContext, string tableName, string commandText)
{
_dataContext = dataContext;
dataContext.DisposableObjects?.Add(this);
_tableName = tableName;
_fields = new Collections.Generic.HashSet <string>(System.StringComparer.OrdinalIgnoreCase);
_whereBefores = new Collections.Generic.HashSet <string>(System.StringComparer.OrdinalIgnoreCase);
_orderbys = new Collections.Generic.HashSet <string>(System.StringComparer.OrdinalIgnoreCase);
TakeCount = -1;
_parameters = new System.Collections.Generic.List <object>();
_dialect = dataContext.Provider.CreateDialect();
_whereExpression = CreateWhereExpression();
_whereExpression.AddCommandParameter = AddCommandParameterDefault;
if (!string.IsNullOrEmpty(commandText))
{
Parse(commandText);
}
_dialect.Keywords.Add("$self", _tableName);
}
private Func <T, bool> GetFilter(IWhereExpression where)
{
Func <T, bool> filter = null;
if (where is WhereExpression)
{
filter = GetAndFilter((WhereExpression) where);
}
else
{
filter = GetOrFilter((OrAlsoExpression) where);
}
if (filter == null)
{
filter = new Func <T, bool>(m => { return(true); });
}
return(filter);
}
private static IWhereExpression GetExpression(IWhereExpression inner, WhereClause clause, Schema schema, IValueProvider valueProvider)
{
var value = ParameterizedFieldValue.GetFieldValue(clause.Value1, valueProvider);
var fieldValue = Kooboo.CMS.Content.Models.Binder.TextContentBinder.DefaultBinder.ConvertToColumnType(schema, clause.FieldName, value);
if (clause.Logical == Logical.Or && string.IsNullOrEmpty(value))
{
return(null);
}
switch (clause.Operator)
{
case Operator.Equals:
return(new WhereEqualsExpression(inner, clause.FieldName, fieldValue));
case Operator.NotEquals:
return(new WhereNotEqualsExpression(inner, clause.FieldName, fieldValue));
case Operator.GreaterThan:
return(new WhereGreaterThanExpression(inner, clause.FieldName, fieldValue));
case Operator.LessThan:
return(new WhereLessThanExpression(inner, clause.FieldName, fieldValue));
case Operator.Contains:
return(new WhereContainsExpression(inner, clause.FieldName, fieldValue));
case Operator.StartsWith:
return(new WhereStartsWithExpression(inner, clause.FieldName, fieldValue));
case Operator.EndsWith:
return(new WhereEndsWithExpression(inner, clause.FieldName, fieldValue));
case Operator.Between:
var value2 = ParameterizedFieldValue.GetFieldValue(clause.Value2, valueProvider);
var fieldValue2 = Kooboo.CMS.Content.Models.Binder.TextContentBinder.DefaultBinder.ConvertToColumnType(schema, clause.Value2, value2);
return(new WhereBetweenExpression(inner, clause.FieldName, fieldValue, fieldValue2));
default:
return(null);
}
}
/// <summary>
/// 释放对象占用的所有资源。
/// </summary>
public virtual void Dispose()
{
if (AutoEnd)
{
OnEnd();
}
_tableName = null;
_fields?.Clear();
_fields = null;
_whereBefores?.Clear();
_whereBefores = null;
_whereExpression?.Clear();
_whereExpression?.Dispose();
_whereExpression = null;
_orderbys?.Clear();
_orderbys = null;
_dataContext = null;
_parameters?.Clear();
_parameters = null;
_dialect = null;
}
public OrElseExpression(IWhereExpression left, IWhereExpression right)
{
this.Left = left;
this.Right = right;
}
public static IWhereExpression Parse(this IEnumerable <WhereClause> clauses, Schema schema, IValueProvider valueProvider)
{
IWhereExpression expression = null;
IWhereExpression thenRight = null;
Logical defaultValue = Logical.And;
Logical? thenLogical = null;
Logical lastLogical = defaultValue;
foreach (var clause in clauses)
{
var exp = GetExpression(null, clause, schema, valueProvider);
if (exp == null)
{
continue;
}
thenRight = BuildLogicalExpression(lastLogical, thenRight, exp);
switch (clause.Logical)
{
case Logical.And:
case Logical.Or:
lastLogical = clause.Logical;
break;
case Logical.ThenAnd:
if (thenLogical.HasValue)
{
expression = BuildLogicalExpression(thenLogical.Value, expression, thenRight);
}
else
{
expression = thenRight;
}
thenRight = null;
thenLogical = Logical.And;
lastLogical = defaultValue;
break;
case Logical.ThenOr:
if (thenLogical.HasValue)
{
expression = BuildLogicalExpression(thenLogical.Value, expression, thenRight);
}
else
{
expression = thenRight;
}
thenRight = null;
thenLogical = Logical.Or;
lastLogical = defaultValue;
break;
default:
throw new NotSupportedException();
}
}
if (thenLogical.HasValue)
{
expression = BuildLogicalExpression(thenLogical.Value, expression, thenRight);
}
else
{
expression = thenRight;
}
return(expression);
}
public AndAlsoExpression(IWhereExpression left, IWhereExpression right)
{
this.Left = left;
this.Right = right;
}
public IWhere <T> Or(IWhere <T> query)
{
this.Expression = new OrAlsoExpression(this.Expression, query.Expression);
return(this);
}
public IWhere <T> Where(string fieldName, object value, CompareType type = CompareType.Equal)
{
this.Expression = new WhereExpression(this.Expression, fieldName, value, type);
return(this);
}
public IContentQuery <T> WhereNot(IWhereExpression expression)
{
return(this.Create(new NotExpression(expression)));
}
protected virtual void VisitWhere(IWhereExpression expression)
{
if (expression is NotExpression)
{
VisitNot((NotExpression)expression);
}
else if (expression is TrueExpression)
{
VisitTrue((TrueExpression)expression);
}
else if (expression is FalseExpression)
{
VisitFalse((FalseExpression)expression);
}
else if (expression is WhereBetweenOrEqualExpression)
{
var whereBetweenExpression = (WhereBetweenOrEqualExpression)expression;
VisitWhereBetweenOrEqual(whereBetweenExpression);
}
else if (expression is WhereBetweenExpression)
{
var whereBetweenExpression = (WhereBetweenExpression)expression;
VisitWhereBetween(whereBetweenExpression);
}
else if (expression is WhereContainsExpression)
{
var whereContainsExpression = (WhereContainsExpression)expression;
VisitWhereContains(whereContainsExpression);
}
else if (expression is WhereEndsWithExpression)
{
var whereEndsWithExpression = (WhereEndsWithExpression)expression;
VisitWhereEndsWith(whereEndsWithExpression);
}
else if (expression is WhereEqualsExpression)
{
var whereEqualsExpression = (WhereEqualsExpression)expression;
VisitWhereEquals(whereEqualsExpression);
}
else if (expression is WhereClauseExpression)
{
var whereExpression = (WhereClauseExpression)expression;
VisitWhereClause(whereExpression);
}
else if (expression is WhereGreaterThanExpression)
{
var whereGreaterThan = (WhereGreaterThanExpression)expression;
VisitWhereGreaterThan(whereGreaterThan);
}
else if (expression is WhereGreaterThanOrEqualExpression)
{
var whereGreaterThenOrEquals = (WhereGreaterThanOrEqualExpression)expression;
VisitWhereGreaterThanOrEqual(whereGreaterThenOrEquals);
}
else if (expression is WhereLessThanExpression)
{
var whereLessThan = (WhereLessThanExpression)expression;
VisitWhereLessThan(whereLessThan);
}
else if (expression is WhereLessThanOrEqualExpression)
{
var whereLessThanOrEqual = (WhereLessThanOrEqualExpression)expression;
VisitWhereLessThanOrEqual(whereLessThanOrEqual);
}
else if (expression is WhereNotEqualsExpression)
{
VisitWhereNotEquals((WhereNotEqualsExpression)expression);
}
else if (expression is WhereStartsWithExpression)
{
var whereStartWith = (WhereStartsWithExpression)expression;
VisitWhereStartsWith(whereStartWith);
}
else if (expression is WhereInExpression)
{
var whereInExpression = (WhereInExpression)expression;
VisitWhereIn(whereInExpression);
}
else if (expression is WhereNotInExpression)
{
var whereNotInExpression = (WhereNotInExpression)expression;
VisitWhereNotIn(whereNotInExpression);
}
else if (expression is OrElseExpression)
{
VisitOrElse((OrElseExpression)expression);
}
else if (expression is AndAlsoExpression)
{
VisitAndAlso((AndAlsoExpression)expression);
}
}
public OpenUpdate(IRepository <T> repository, IWhereExpression where)
{
this.Repository = repository;
this.WhereExpression = where;
}
protected virtual void VisitWhere(IWhereExpression expression)
{
if (expression is TrueExpression)
{
VisitTrue((TrueExpression)expression);
}
else if (expression is FalseExpression)
{
VisitFalse((FalseExpression)expression);
}
if (expression is WhereBetweenOrEqualExpression)
{
var whereBetweenExpression = (WhereBetweenOrEqualExpression)expression;
VisitWhereBetweenOrEqual(whereBetweenExpression);
}
else if (expression is WhereBetweenExpression)
{
var whereBetweenExpression = (WhereBetweenExpression)expression;
VisitWhereBetween(whereBetweenExpression);
}
else if (expression is WhereContainsExpression)
{
var whereContainsExpression = (WhereContainsExpression)expression;
VisitWhereContains(whereContainsExpression);
}
else if (expression is WhereEndsWithExpression)
{
var whereEndsWithExpression = (WhereEndsWithExpression)expression;
VisitWhereEndsWith(whereEndsWithExpression);
}
else if (expression is WhereEqualsExpression)
{
var whereEqualsExpression = (WhereEqualsExpression)expression;
VisitWhereEquals(whereEqualsExpression);
}
else if (expression is WhereClauseExpression)
{
var whereExpression = (WhereClauseExpression)expression;
VisitWhereClause(whereExpression);
}
else if (expression is WhereGreaterThanExpression)
{
var whereGreaterThan = (WhereGreaterThanExpression)expression;
VisitWhereGreaterThan(whereGreaterThan);
}
else if (expression is WhereGreaterThanOrEqualExpression)
{
var whereGreaterThenOrEquals = (WhereGreaterThanOrEqualExpression)expression;
VisitWhereGreaterThanOrEqual(whereGreaterThenOrEquals);
}
else if (expression is WhereLessThanExpression)
{
var whereLessThan = (WhereLessThanExpression)expression;
VisitWhereLessThan(whereLessThan);
}
else if (expression is WhereLessThanOrEqualExpression)
{
var whereLessThanOrEqual = (WhereLessThanOrEqualExpression)expression;
VisitWhereLessThanOrEqual(whereLessThanOrEqual);
}
else if (expression is WhereNotEqualsExpression)
{
VisitWhereNotEquals((WhereNotEqualsExpression)expression);
}
else if (expression is WhereStartsWithExpression)
{
var whereStartWith = (WhereStartsWithExpression)expression;
VisitWhereStartsWith(whereStartWith);
}
else if (expression is WhereInExpression)
{
var whereInExpression = (WhereInExpression)expression;
VisitWhereIn(whereInExpression);
}
else if (expression is WhereNotInExpression)
{
var whereNotInExpression = (WhereNotInExpression)expression;
VisitWhereNotIn(whereNotInExpression);
}
else if (expression is OrElseExpression)
{
VisitOrElse((OrElseExpression)expression);
}
else if (expression is AndAlsoExpression)
{
VisitAndAlso((AndAlsoExpression)expression);
}
}
public IContentQuery <T> Where(IWhereExpression expression)
{
var newContentQuery = this.innerContentQuery.Where(expression);
return(this.CreateDuck(newContentQuery));
}
public NotExpression(IWhereExpression expression)
{
InnerExpression = expression;
}
/// <summary>
/// Build And/Or Expression
/// </summary>
/// <param name="logical"></param>
/// <param name="left"></param>
/// <param name="right"></param>
/// <returns></returns>
private static IWhereExpression BuildLogicalExpression(Logical logical, IWhereExpression left, IWhereExpression right)
{
//And/Or
switch (logical)
{
case Logical.And:
return new AndAlsoExpression(left, right);
case Logical.Or:
return new OrElseExpression(left, right);
default:
throw new NotSupportedException();
}
}
public AndAlsoExpression(IWhereExpression left, IWhereExpression right)
{
this.Left = left;
this.Right = right;
}
public OrElseExpression(IWhereExpression left, IWhereExpression right)
{
this.Left = left;
this.Right = right;
}
private static IWhereExpression GetExpression(IWhereExpression inner, WhereClause clause, Schema schema, IValueProvider valueProvider)
{
var binder = EngineContext.Current.Resolve<ITextContentBinder>();
var value = ParameterizedFieldValue.GetFieldValue(clause.Value1, valueProvider);
var fieldValue = binder.ConvertToColumnType(schema, clause.FieldName, value);
if (clause.Logical == Logical.Or && string.IsNullOrEmpty(value))
{
return null;
}
switch (clause.Operator)
{
case Operator.Equals:
return new WhereEqualsExpression(inner, clause.FieldName, fieldValue);
case Operator.NotEquals:
return new WhereNotEqualsExpression(inner, clause.FieldName, fieldValue);
case Operator.GreaterThan:
return new WhereGreaterThanExpression(inner, clause.FieldName, fieldValue);
case Operator.LessThan:
return new WhereLessThanExpression(inner, clause.FieldName, fieldValue);
case Operator.Contains:
return new WhereContainsExpression(inner, clause.FieldName, fieldValue);
case Operator.StartsWith:
return new WhereStartsWithExpression(inner, clause.FieldName, fieldValue);
case Operator.EndsWith:
return new WhereEndsWithExpression(inner, clause.FieldName, fieldValue);
case Operator.Between:
var value2 = ParameterizedFieldValue.GetFieldValue(clause.Value2, valueProvider);
var fieldValue2 = binder.ConvertToColumnType(schema, clause.Value2, value2);
return new WhereBetweenExpression(inner, clause.FieldName, fieldValue, fieldValue2);
case Operator.NotNull:
return new AndAlsoExpression(new WhereNotEqualsExpression(inner, clause.FieldName, null), new WhereNotEqualsExpression(inner, clause.FieldName, ""));
case Operator.IsNull:
return new OrElseExpression(new WhereEqualsExpression(inner, clause.FieldName, null), new WhereEqualsExpression(inner, clause.FieldName, ""));
default:
return null;
}
}
public void Delete(IWhereExpression expression)
{
return;
}
public NotExpression(IWhereExpression expression)
{
InnerExpression = expression;
}
protected override void VisitWhere(IWhereExpression expression)
{
if (!((expression is WhereInExpression) || (expression is WhereNotInExpression)))
{
if (whereClause.Length > 0)
{
whereClause.Append(" && ");
this.viewNameBuilder.AppendFormat("_AND_");
}
}
base.VisitWhere(expression);
}
private static IWhereExpression GetExpression(IWhereExpression inner, WhereClause clause, Schema schema, IValueProvider valueProvider)
{
var value = ParameterizedFieldValue.GetFieldValue(clause.Value1, valueProvider);
var fieldValue = Kooboo.CMS.Content.Models.Binder.TextContentBinder.DefaultBinder.ConvertToColumnType(schema, clause.FieldName, value);
if (clause.Logical == Logical.Or && string.IsNullOrEmpty(value))
{
return null;
}
switch (clause.Operator)
{
case Operator.Equals:
return new WhereEqualsExpression(inner, clause.FieldName, fieldValue);
case Operator.NotEquals:
return new WhereNotEqualsExpression(inner, clause.FieldName, fieldValue);
case Operator.GreaterThan:
return new WhereGreaterThanExpression(inner, clause.FieldName, fieldValue);
case Operator.LessThan:
return new WhereLessThanExpression(inner, clause.FieldName, fieldValue);
case Operator.Contains:
return new WhereContainsExpression(inner, clause.FieldName, fieldValue);
case Operator.StartsWith:
return new WhereStartsWithExpression(inner, clause.FieldName, fieldValue);
case Operator.EndsWith:
return new WhereEndsWithExpression(inner, clause.FieldName, fieldValue);
case Operator.Between:
var value2 = ParameterizedFieldValue.GetFieldValue(clause.Value2, valueProvider);
var fieldValue2 = Kooboo.CMS.Content.Models.Binder.TextContentBinder.DefaultBinder.ConvertToColumnType(schema, clause.Value2, value2);
return new WhereBetweenExpression(inner, clause.FieldName, fieldValue, fieldValue2);
default:
return null;
}
}
public IUpdate <T> Update(IWhereExpression where)
{
return(new OpenUpdate <T>(this, where));
}
