protected override DbCommand BuildDeleteCommand(SqlDeleteCommand deleteCommand)
{
NpgsqlCommand cmd = new NpgsqlCommand();
BuildQueryContext ctx = new BuildQueryContext(cmd, deleteCommand);
//设置上下文
ctx.BeginBuildQuery(deleteCommand);
EntityModel m = Runtime.RuntimeContext.Current.GetModelAsync <EntityModel>(deleteCommand.T.ModelID).Result;
ctx.AppendFormat("Delete From \"{0}\" t ", m.GetSqlTableName(false, null));
//构建Where
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildWhere;
if (!Expression.IsNull(deleteCommand.Filter))
{
ctx.Append(" Where ");
BuildExpression(ctx.CurrentQuery.Filter, ctx);
}
//构建Join
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildJoin;
var q1 = (SqlQueryBase)ctx.CurrentQuery;
if (q1.HasJoins) //先处理每个手工的联接及每个手工联接相应的自动联接
{
BuildJoins(q1.Joins, ctx);
}
ctx.BuildQueryAutoJoins(q1); //再处理自动联接
ctx.EndBuildQuery(deleteCommand);
return(cmd);
}
private void BuildPrimitiveExpression(PrimitiveExpression exp, BuildQueryContext ctx)
{
if (exp.Value == null)
{
ctx.Append("NULL");
return;
}
if (exp.Value is IEnumerable list && !(exp.Value is string)) //用于处理In及NotIn的参数
{
ctx.Append("(");
bool first = true;
foreach (var item in list)
{
if (first)
{
first = false;
}
else
{
ctx.Append(",");
}
ctx.AppendFormat("@{0}", ctx.GetParameterName(item));
}
ctx.Append(")");
}
private void BuildGroupBy(ISqlSelectQuery query, BuildQueryContext ctx)
{
if (query.GroupByKeys == null || query.GroupByKeys.Length == 0)
{
return;
}
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildGroupBy;
ctx.Append(" Group By ");
for (int i = 0; i < query.GroupByKeys.Length; i++)
{
if (i != 0)
{
ctx.Append(",");
}
BuildExpression(query.GroupByKeys[i], ctx);
}
if (!Expression.IsNull(query.HavingFilter))
{
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildHaving;
ctx.Append(" Having ");
BuildExpression(query.HavingFilter, ctx);
}
if (query.HasSortItems)
{
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildOrderBy;
BuildOrderBy(query, ctx);
}
}
/// <summary>
/// 处理手工联接及其对应的自动联接
/// </summary>
private void BuildJoins(List <SqlJoin> joins, BuildQueryContext ctx)
{
foreach (var item in joins)
{
//先处理当前的联接
ctx.Append(GetJoinString(item.JoinType));
if (item.Right is SqlQueryJoin)
{
var j = (SqlQueryJoin)item.Right;
var jModel = Runtime.RuntimeContext.Current.GetModelAsync <EntityModel>(j.T.ModelID).Result;
ctx.AppendFormat("\"{0}\" {1} On ", jModel.GetSqlTableName(false, null), j.AliasName);
BuildExpression(item.OnConditon, ctx);
//再处理手工联接的自动联接
ctx.BuildQueryAutoJoins(j);
}
else //否则表示联接对象是SubQuery，注意：子查询没有自动联接
{
SqlSubQuery sq = (SqlSubQuery)item.Right;
ctx.Append("(");
BuildNormalQuery(sq.Target, ctx);
ctx.AppendFormat(") As {0} On ", ((SqlQueryBase)sq.Target).AliasName);
BuildExpression(item.OnConditon, ctx);
}
//最后递归当前联接的右部是否还有手工的联接项
if (item.Right.HasJoins)
{
BuildJoins(item.Right.Joins, ctx);
}
}
}
private void BuildCTE_SelectItems(ISqlSelectQuery query, BuildQueryContext ctx, bool forTeeNodePath = false)
{
//ctx.IsBuildCTESelectItem = true;
foreach (var si in query.Selects.Values)
{
FieldExpression fsi = si.Expression as FieldExpression;
if (!Expression.IsNull(fsi))
{
if (Equals(fsi.Owner.Owner, null))
{
if (forTeeNodePath)
{
ctx.AppendFormat("t.\"{0}\" \"{1}\",", fsi.Name, si.AliasName);
}
else
{
ctx.AppendFormat("t.\"{0}\",", fsi.Name);
}
}
}
//else if (forTeeNodePath)
//{
// var aggRefField = si.Expression as AggregationRefFieldExpression;
// if (!object.Equals(null, aggRefField))
// {
// BuildAggregationRefFieldExpression(aggRefField, ctx);
// ctx.AppendFormat(" \"{0}\",", si.AliasName);
// }
//}
}
//ctx.IsBuildCTESelectItem = false;
}
protected override DbCommand BuidUpdateCommand(SqlUpdateCommand updateCommand)
{
NpgsqlCommand cmd = new NpgsqlCommand();
BuildQueryContext ctx = new BuildQueryContext(cmd, updateCommand);
//设置上下文
ctx.BeginBuildQuery(updateCommand);
EntityModel model = Runtime.RuntimeContext.Current.GetModelAsync <EntityModel>(updateCommand.T.ModelID).Result;
ctx.AppendFormat("Update \"{0}\" t Set ", model.GetSqlTableName(false, null));
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildUpdateSet;
for (int i = 0; i < updateCommand.UpdateItems.Count; i++)
{
BuildExpression(updateCommand.UpdateItems[i], ctx);
if (i < updateCommand.UpdateItems.Count - 1)
{
ctx.Append(",");
}
}
//构建Where
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildWhere;
if (!Expression.IsNull(updateCommand.Filter))
{
ctx.Append(" Where ");
BuildExpression(ctx.CurrentQuery.Filter, ctx);
}
//构建Join
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildJoin;
SqlQueryBase q1 = (SqlQueryBase)ctx.CurrentQuery;
if (q1.HasJoins) //先处理每个手工的联接及每个手工联接相应的自动联接
{
BuildJoins(q1.Joins, ctx);
}
ctx.BuildQueryAutoJoins(q1); //再处理自动联接
//最后处理返回值
if (updateCommand.OutputItems != null)
{
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildWhere; //TODO: fix this?
ctx.Append(" RETURNING ");
for (int i = 0; i < updateCommand.OutputItems.Length; i++)
{
var field = (FieldExpression)updateCommand.OutputItems[i];
ctx.AppendFormat("\"{0}\"", field.Name);
if (i != updateCommand.OutputItems.Length - 1)
{
ctx.Append(",");
}
}
}
//结束用于附加条件，注意：仅在Upsert时这样操作
ctx.EndBuildQuery(updateCommand);
return(cmd);
}
private void BuildSelectItem(SqlSelectItemExpression item, BuildQueryContext ctx)
{
//判断item.Expression是否是子Select项,是则表示外部查询（FromQuery）引用的Select项
if (item.Expression.Type == ExpressionType.SelectItemExpression)
{
SqlSelectItemExpression si = (SqlSelectItemExpression)item.Expression;
//判断当前查询是否等于Select项的所有者，否则表示Select项的所有者的外部查询引用该Select项
if (ReferenceEquals(ctx.CurrentQuery, item.Owner))
{
ctx.AppendFormat("{0}.\"{1}\"", ctx.GetQueryAliasName(si.Owner), si.AliasName);
}
else
{
ctx.AppendFormat("{0}.\"{1}\"", ctx.GetQueryAliasName(item.Owner), si.AliasName);
}
//处理选择项别名
if (ctx.CurrentQueryInfo.BuildStep == BuildQueryStep.BuildSelect)//&& !ctx.IsBuildCTESelectItem)
{
if (item.AliasName != si.AliasName)
{
ctx.AppendFormat(" \"{0}\"", item.AliasName);
}
}
}
else //----上面为FromQuery的Select项，下面为Query或SubQuery的Select项----
{
//判断当前查询是否等于Select项的所有者，否则表示Select项的所有者的外部查询引用该Select项
if (ReferenceEquals(ctx.CurrentQuery, item.Owner))
{
BuildExpression(item.Expression, ctx);
}
else
{
ctx.AppendFormat("{0}.\"{1}\"", ctx.GetQueryAliasName(item.Owner), item.AliasName);
}
//处理选择项别名
if (ctx.CurrentQueryInfo.BuildStep == BuildQueryStep.BuildSelect)//&& !ctx.IsBuildCTESelectItem)
{
MemberExpression memberExp = item.Expression as MemberExpression;
if (Expression.IsNull(memberExp)
/*|| memberExp.Type == ExpressionType.AggregationRefFieldExpression*/ //注意：聚合引用字段必须用别名
|| memberExp.Name != item.AliasName)
{
ctx.AppendFormat(" \"{0}\"", item.AliasName);
}
}
}
}
private void BuildExpression(Expression exp, BuildQueryContext ctx)
{
switch (exp.Type)
{
case ExpressionType.FieldExpression:
BuildFieldExpression((FieldExpression)exp, ctx);
break;
case ExpressionType.EntityExpression:
BuildEntityExpression((EntityExpression)exp, ctx);
break;
//case ExpressionType.AggregationRefFieldExpression:
// BuildAggregationRefFieldExpression((AggregationRefFieldExpression)exp, ctx);
// break;
case ExpressionType.BinaryExpression:
BuildBinaryExpression((BinaryExpression)exp, ctx);
break;
case ExpressionType.PrimitiveExpression:
BuildPrimitiveExpression((PrimitiveExpression)exp, ctx);
break;
case ExpressionType.SelectItemExpression:
BuildSelectItem((SqlSelectItemExpression)exp, ctx);
break;
case ExpressionType.SubQueryExpression:
BuildSubQuery((SqlSubQuery)exp, ctx);
break;
////case ExpressionType.DbParameterExpression:
//// BuildDbParameterExpression((DbParameterExpression)exp, ctx);
//// break;
case ExpressionType.DbFuncExpression:
BuidDbFuncExpression((DbFuncExpression)exp, ctx);
break;
case ExpressionType.DbParameterExpression:
BuildDbParameterExpression((DbParameterExpression)exp, ctx);
break;
//case ExpressionType.InvocationExpression:
// BuildInvocationExpression((InvocationExpression)exp, ctx);
// break;
default:
throw new NotSupportedException($"Not Supported Expression Type [{exp.Type.ToString()}] for Query.");
}
}
private void BuildOrderBy(ISqlSelectQuery query, BuildQueryContext ctx)
{
ctx.Append(" Order By ");
for (int i = 0; i < query.SortItems.Count; i++)
{
SqlSortItem si = query.SortItems[i];
BuildExpression(si.Expression, ctx);
if (si.SortType == SortType.DESC)
{
ctx.Append(" DESC");
}
if (i < query.SortItems.Count - 1)
{
ctx.Append(" ,");
}
}
}
protected override DbCommand BuildQuery(ISqlSelectQuery query)
{
var cmd = new NpgsqlCommand();
var ctx = new BuildQueryContext(cmd, query);
if (query.Purpose == QueryPurpose.ToEntityTreeList)
{
BuildTreeQuery(query, ctx);
}
else if (query.Purpose == QueryPurpose.ToTreeNodePath)
{
BuildTreeNodePathQuery(query, ctx);
}
else
{
BuildNormalQuery(query, ctx);
}
return(cmd);
}
private void BuildTreeNodePathQuery(ISqlSelectQuery query, BuildQueryContext ctx)
{
//设置上下文
ctx.BeginBuildQuery(query);
ctx.Append("With RECURSIVE cte (\"Id\",\"ParentId\",\"Text\",\"Level\") As (Select ");
//Select Anchor
ctx.SetBuildStep(BuildQueryStep.BuildSelect);
BuildCTE_SelectItems(query, ctx, true);
ctx.Append("0 From ");
//From Anchor
ctx.SetBuildStep(BuildQueryStep.BuildFrom);
SqlQuery q = (SqlQuery)query;
var model = Runtime.RuntimeContext.Current.GetModelAsync <EntityModel>(q.T.ModelID).Result;
ctx.AppendFormat("\"{0}\" As {1}", model.GetSqlTableName(false, null), q.AliasName);
//Where Anchor
ctx.SetBuildStep(BuildQueryStep.BuildWhere);
if (!Equals(null, query.Filter))
{
ctx.Append(" Where ");
BuildExpression(query.Filter, ctx);
}
//End 1
ctx.CurrentQueryInfo.EndBuidQuery(); //ctx.EndBuildQuery(query);
//Union all
ctx.SetBuildStep(BuildQueryStep.BuildSelect);
ctx.Append(" Union All Select ");
//Select 2
BuildCTE_SelectItems(query, ctx, true);
ctx.Append("\"Level\" + 1 From ");
//From 2
ctx.SetBuildStep(BuildQueryStep.BuildFrom);
ctx.AppendFormat("\"{0}\" As {1}", model.GetSqlTableName(false, null), q.AliasName);
//Inner Join
ctx.Append(" Inner Join cte as d On d.\"ParentId\"=t.\"Id\") Select * From cte");
//End 1
ctx.EndBuildQuery(query, true);
}
private void BuildSubQuery(SqlSubQuery exp, BuildQueryContext ctx)
{
ctx.Append("(");
BuildNormalQuery(exp.Target, ctx);
ctx.Append(")");
}
private void BuildNormalQuery(ISqlSelectQuery query, BuildQueryContext ctx)
{
//设置上下文
ctx.BeginBuildQuery(query);
//构建Select
ctx.Append("Select ");
if (query.Purpose == QueryPurpose.ToDataTable && query.Distinct)
{
ctx.Append("Distinct ");
}
//构建Select Items
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildSelect;
if (query.Purpose == QueryPurpose.Count)
{
ctx.Append("Count(*)");
}
else
{
foreach (var si in query.Selects.Values)
{
BuildSelectItem(si, ctx);
ctx.Append(",");
}
ctx.RemoveLastChar(); //移除最后多余的,号
}
//构建From
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildFrom;
ctx.Append(" From ");
//判断From源
if (query is SqlFromQuery)
{
SqlFromQuery q = (SqlFromQuery)ctx.CurrentQuery;
//开始构建From子查询
ctx.Append("(");
BuildNormalQuery(q.Target, ctx);
ctx.Append(")");
ctx.AppendFormat(" {0}", ((SqlQueryBase)q.Target).AliasName);// ((QueryBase)query).AliasName);
}
else
{
SqlQuery q = (SqlQuery)ctx.CurrentQuery;
var model = Runtime.RuntimeContext.Current.GetModelAsync <EntityModel>(q.T.ModelID).Result;
ctx.AppendFormat("\"{0}\" {1}", model.GetSqlTableName(false, null), q.AliasName);
}
//构建Where
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildWhere;
if (!Equals(null, ctx.CurrentQuery.Filter))
{
ctx.Append(" Where ");
BuildExpression(ctx.CurrentQuery.Filter, ctx);
}
//非分组的情况下构建Order By
if (query.Purpose != QueryPurpose.Count)
{
if (query.GroupByKeys == null && query.HasSortItems)
{
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildOrderBy;
BuildOrderBy(query, ctx);
}
}
//构建Join
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildJoin;
SqlQueryBase q1 = (SqlQueryBase)ctx.CurrentQuery;
if (q1.HasJoins) //先处理每个手工的联接及每个手工联接相应的自动联接
{
BuildJoins(q1.Joins, ctx);
}
ctx.BuildQueryAutoJoins(q1); //再处理自动联接
//处理Skip and Take
if (query.Purpose != QueryPurpose.Count)
{
ctx.CurrentQueryInfo.BuildStep = BuildQueryStep.BuildSkipAndTake;
if (query.SkipSize > 0)
{
ctx.AppendFormat(" Offset {0}", query.SkipSize);
}
if (query.Purpose == QueryPurpose.ToSingleEntity)
{
ctx.Append(" Limit 1 ");
}
else if (query.TakeSize > 0)
{
ctx.AppendFormat(" Limit {0} ", query.TakeSize);
}
}
//构建分组、Having及排序
BuildGroupBy(query, ctx);
//结束上下文
ctx.EndBuildQuery(query);
}
//With RECURSIVE cte
//("ID","BaseID","BaseType","ParentID","ManagerID","SortNumber","Disabled","TreeLevel")
//As
//(Select t."ID", t."BaseID", t."BaseType", t."ParentID", t."ManagerID", t."SortNumber", t."Disabled",0
//From "sys.OrgUnit" As t
//Where t."ParentID" ISNULL
//Union All
//Select t."ID", t."BaseID", t."BaseType", t."ParentID", t."ManagerID", t."SortNumber", t."Disabled","TreeLevel" + 1
//From "sys.OrgUnit" As t Inner Join cte as d On t."ParentID" = d."ID")
//Select t."ID" "t.ID", t."BaseID" "t.BaseID", t."BaseType" "t.BaseType", t."ParentID" "t.ParentID", t."ManagerID"
//"t.ManagerID", t."SortNumber" "t.SortNumber", t."Disabled" "t.Disabled" From cte t
//Order By t."TreeLevel", t."SortNumber"
private void BuildTreeQuery(ISqlSelectQuery query, BuildQueryContext ctx)
{
//注意目前实现仅支持单一主键
//设置上下文
ctx.BeginBuildQuery(query);
ctx.Append("With RECURSIVE cte (");
ctx.SetBuildStep(BuildQueryStep.BuildWithCTE);
foreach (var si in query.Selects.Values)
{
FieldExpression fsi = si.Expression as FieldExpression;
if (!Equals(null, fsi) && Equals(fsi.Owner.Owner, null))
{
ctx.AppendFormat("\"{0}\",", ((FieldExpression)si.Expression).Name);
}
}
ctx.Append("\"TreeLevel\") As (Select ");
//Select Anchor
ctx.SetBuildStep(BuildQueryStep.BuildSelect);
BuildCTE_SelectItems(query, ctx);
ctx.Append("0 From ");
//From Anchor
ctx.SetBuildStep(BuildQueryStep.BuildFrom);
SqlQuery q = (SqlQuery)query;
var model = Runtime.RuntimeContext.Current.GetModelAsync <EntityModel>(q.T.ModelID).Result;
ctx.AppendFormat("\"{0}\" As {1}", model.GetSqlTableName(false, null), q.AliasName);
//Where Anchor
ctx.SetBuildStep(BuildQueryStep.BuildWhere);
if (!object.Equals(null, query.Filter))
{
ctx.Append(" Where ");
BuildExpression(query.Filter, ctx);
}
//End 1
ctx.CurrentQueryInfo.EndBuidQuery();//ctx.EndBuildQuery(query);
//Union all
ctx.SetBuildStep(BuildQueryStep.BuildSelect);
ctx.Append(" Union All Select ");
//Select 2
//ctx.SetBuildStep(BuildQueryStep.BuildSelect);
BuildCTE_SelectItems(query, ctx);
ctx.Append("\"TreeLevel\" + 1 From ");
//From 2
ctx.SetBuildStep(BuildQueryStep.BuildFrom);
ctx.AppendFormat("\"{0}\" As {1}", model.GetSqlTableName(false, null), q.AliasName);
//Inner Join
ctx.Append(" Inner Join cte as d On ");
BuildFieldExpression(q.TreeParentIDMember, ctx);
ctx.Append(" = d.\"Id\") Select "); //TODO:fix pk
//重新处理SelectItem
ctx.SetBuildStep(BuildQueryStep.BuildSelect);
foreach (var si in query.Selects.Values)
{
BuildSelectItem(si, ctx);
ctx.Append(",");
}
ctx.RemoveLastChar();
ctx.Append(" From cte t");
//构建自动联接Join，主要用于继承的
ctx.SetBuildStep(BuildQueryStep.BuildJoin);
ctx.BuildQueryAutoJoins(q); //再处理自动联接
//最后处理Order By
ctx.SetBuildStep(BuildQueryStep.BuildOrderBy);
if (query.HasSortItems)
{
ctx.Append(" Order By t.\"TreeLevel\"");
for (int i = 0; i < query.SortItems.Count; i++)
{
ctx.Append(",");
SqlSortItem si = query.SortItems[i];
BuildExpression(si.Expression, ctx);
if (si.SortType == SortType.DESC)
{
ctx.Append(" DESC");
}
}
}
//End 1
ctx.EndBuildQuery(query, true);
}