コード例 #1
0
        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);
        }
コード例 #2
0
        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(")");
            }
コード例 #3
0
        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);
            }
        }
コード例 #4
0
        /// <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);
                }
            }
        }
コード例 #5
0
 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;
 }
コード例 #6
0
        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);
        }
コード例 #7
0
        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);
                    }
                }
            }
        }
コード例 #8
0
        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.");
            }
        }
コード例 #9
0
 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(" ,");
         }
     }
 }
コード例 #10
0
        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);
        }
コード例 #11
0
        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);
        }
コード例 #12
0
 private void BuildSubQuery(SqlSubQuery exp, BuildQueryContext ctx)
 {
     ctx.Append("(");
     BuildNormalQuery(exp.Target, ctx);
     ctx.Append(")");
 }
コード例 #13
0
        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);
        }
コード例 #14
0
        //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);
        }