/// <summary>
/// 用于生成EntityRef的自动Join
/// </summary>
public void BuildQueryAutoJoins(SqlQueryBase target)
{
if (!AutoJoins.TryGetValue(target, out Dictionary <string, EntityExpression> ds))
{
return;
}
foreach (var rq in ds.Values)
{
//Left Join "City" c ON c."Code" = t."CityCode"
//eg: Customer.City的City
var rqModel = Runtime.RuntimeContext.Current.GetModelAsync <EntityModel>(rq.ModelID).Result;
//eg: Customer.City的Customer
var rqOwnerModel = Runtime.RuntimeContext.Current.GetModelAsync <EntityModel>(rq.Owner.ModelID).Result;
AppendFormat(" Left Join \"{0}\" {1} On ", rqModel.GetSqlTableName(false, null), rq.AliasName);
//Build ON Condition, other.pks == this.fks
var rm = (EntityRefModel)rqOwnerModel.GetMember(rq.Name, true);
for (int i = 0; i < rqModel.SqlStoreOptions.PrimaryKeys.Count; i++)
{
if (i != 0)
{
Append(" And ");
}
var pk = (DataFieldModel)rqModel.GetMember(rqModel.SqlStoreOptions.PrimaryKeys[i].MemberId, true);
var fk = (DataFieldModel)rqOwnerModel.GetMember(rm.FKMemberIds[i], true);
AppendFormat("{0}.\"{1}\"={2}.\"{3}\"", rq.AliasName, pk.SqlColName, rq.Owner.AliasName, fk.SqlColName);
}
}
}
private void LoopAddQueryJoins(SqlQueryBase query)
{
//判断是否已经生成别名
if (string.IsNullOrEmpty(query.AliasName))
{
_queryIndex += 1;
query.AliasName = $"t{_queryIndex.ToString()}";
}
//将当前查询加入自动联接字典表
AutoJoins.Add(query, new Dictionary <string, EntityExpression>());
if (query.HasJoins)
{
foreach (var item in query.Joins)
{
if (item.Right is SqlQueryJoin) //注意：子查询不具备自动联接
{
LoopAddQueryJoins((SqlQueryBase)item.Right);
}
else
{
LoopAddSubQueryJoins((SqlSubQuery)item.Right);
}
}
}
}
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);
}
public string GetEntityRefAliasName(EntityExpression exp, SqlQueryBase query)
{
string path = exp.ToString();
Dictionary <string, EntityExpression> ds = AutoJoins[query];
EntityExpression e = null;
if (!ds.TryGetValue(path, out e))
{
ds.Add(path, exp);
_queryIndex += 1;
exp.AliasName = $"j{_queryIndex}";
e = exp;
}
return(e.AliasName);
}
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);
}