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; }
/// <summary> /// Returns string representation of SQL Server quoted columns /// </summary> /// <param name="sqlQuery">SQL query model</param> /// <returns>Joined string of SQL SELECT columns</returns> private (string columns, string aliasColumns) GetColumns(ISqlSelectQuery sqlQuery) { IEnumerable <ISqlColumn> cols = sqlQuery.Columns.Where(col => col.Include); string columns = String.Join(", ", cols.Select(col => { string columnExpression = col.Expression; if (!sqlQuery.ExcludeAliases || !col.Visible) { columnExpression += " as " + QuoteIdentifier(String.IsNullOrEmpty(col.TrimmedAlias) ? col.Expression : col.TrimmedAlias); } return(columnExpression); })); string aliasColumns = String.Join(", ", cols.Select(col => { string columnExpression = QuoteIdentifier("tbl") + "." + QuoteIdentifier(col.Expression); if (!sqlQuery.ExcludeAliases || !col.Visible) { columnExpression = QuoteIdentifier("tbl") + "." + QuoteIdentifier(String.IsNullOrEmpty(col.TrimmedAlias) ? col.Expression : col.TrimmedAlias); } return(columnExpression); })); return(columns, aliasColumns); }
/// <summary> /// Returns string representation of SQL Server quoted columns /// </summary> /// <param name="sqlQuery">SQL query model</param> /// <returns>Joined string of SQL SELECT columns</returns> private (string columns, string aliasColumns) GetColumns(ISqlSelectQuery sqlQuery) { IEnumerable <ISqlColumn> cols = sqlQuery.Columns.Where(col => col.Include); string columns = String.Join(", ", cols.Select(col => { string columnExpression = QuoteIdentifier(col.Expression); if (!sqlQuery.ExcludeAliases && !String.IsNullOrEmpty(col.ExpressionAlias)) { columnExpression += " " + col.ExpressionAlias; } return(columnExpression); })); string aliasColumns = String.Join(", ", cols.Select(col => { string columnExpression = QuoteIdentifier("tbl") + "." + QuoteIdentifier(col.Expression); if (!sqlQuery.ExcludeAliases && col.ExpressionAlias != null) { columnExpression = QuoteIdentifier("tbl") + "." + QuoteIdentifier(col.ExpressionAlias); } return(columnExpression); })); return(columns, aliasColumns); }
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> /// Returns string representation of Oracle quoted columns /// </summary> /// <param name="sqlQuery">SQL query model</param> /// <returns>Joined string of SQL SELECT columns</returns> private string GetColumns(ISqlSelectQuery sqlQuery) { IEnumerable <ISqlColumn> cols = sqlQuery.Columns.Where(col => col.Include); string columns = String.Join(", ", cols.Select(col => { string columnExpression = col.Unquoted ? col.Expression : QuoteIdentifier(col.Expression); if (!sqlQuery.ExcludeAliases && !String.IsNullOrEmpty(col.ExpressionAlias)) { columnExpression += " " + QuoteIdentifier(col.ExpressionAlias); } return(columnExpression); })); return(columns); }
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); }
/// <summary> /// Creates an SQL Server parameterized query with an SQL text from given <see cref="ISqlSelectQuery"/> /// </summary> /// <param name="sqlQuery">SQL query model</param> /// <returns>Parameterized query model</returns> public override IParameterizedQuery BuildQuery(ISqlSelectQuery sqlQuery) { (string columns, string aliasColumns) = GetColumns(sqlQuery); string table = QuoteIdentifier(sqlQuery.TableIdentifier.Table); string conditions; IEnumerable <ISqlParameter> parameters; if (sqlQuery.Condition != null) { IUncheckedSqlCondition uncheckedCondition = ConditionStringBuilder.Build(sqlQuery.Condition); conditions = uncheckedCondition.SqlConditionString; parameters = sqlQuery.Parameters.Union(uncheckedCondition.Parameters); if (!Connection.CheckIfTableExists(sqlQuery.TableIdentifier)) { throw new InvalidOperationException($"Table {sqlQuery.TableIdentifier.Table} does not exist or is not accessible."); } foreach (string column in uncheckedCondition.UncheckedIdentifiers) { if (!Connection.CheckIfColumnExists(sqlQuery.TableIdentifier, column)) { throw new InvalidOperationException($"Column {sqlQuery.TableIdentifier.Table}.{column} does not exist or is not accessible."); } } } else { parameters = sqlQuery.Parameters; conditions = "1 = 1"; } if (sqlQuery.NoPagination) { string noPaginationQuery = $"SELECT {columns} FROM {table} WHERE({conditions})"; return(new SqlServerDbQuery(noPaginationQuery, parameters)); } var orderByColumns = sqlQuery.Columns.Where(col => col.SortOrder != Sql.SortOrder.Unspecified) .Select(col => QuoteIdentifier(col.Expression) + " " + (col.SortOrder == Sql.SortOrder.Ascending ? "ASC" : "DESC")) .ToList(); orderByColumns.Reverse(); if (!orderByColumns.Any()) { throw new InvalidOperationException("At least one column for sorting must be specified for pagination to work"); } string orderby = String.Join(", ", orderByColumns); int offset, limit; if (sqlQuery.Page == 1) { offset = 1; limit = sqlQuery.PageSize; } else { offset = sqlQuery.Page * sqlQuery.PageSize; limit = offset + sqlQuery.PageSize - 1; } string query = $@"SELECT {aliasColumns} FROM (SELECT {columns}, ROW_NUMBER() OVER (ORDER BY {orderby}) rn FROM {table} WHERE ({conditions})) tbl WHERE tbl.rn BETWEEN {offset} AND {limit} ORDER BY tbl.rn"; return(new SqlServerDbQuery(query, parameters)); }
internal SqlFromQuery(ISqlSelectQuery target) { Target = target; }
/// <summary> /// Builds a COUNT query for checking the number of rows from a given SQL SELECT query model. /// </summary> /// <param name="sqlQuery">SQL SELECT query model</param> /// <returns>Parameterized SQL Server query</returns> public override IParameterizedQuery BuildCountQuery(ISqlSelectQuery sqlQuery) => sqlBuilder.BuildCountQuery(sqlQuery.TableIdentifier, sqlQuery.Condition);
/// <summary> /// Converts SQL SELECT query model into the SQL Server parameterized query /// </summary> /// <param name="sqlQuery">SQL SELECT query model</param> /// <returns>Parameterized SQL Server query</returns> public override IParameterizedQuery BuildQuery(ISqlSelectQuery sqlQuery) => sqlBuilder.BuildQuery(sqlQuery);
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); }
/// <summary> /// Returns number of records for given table identifier. May be RDBMS-specific. /// </summary> /// <param name="sqlQuery">The SQL QUERY model</param> /// <returns>Number of records</returns> public virtual int GetTotalRecords(ISqlSelectQuery sqlQuery) { IParameterizedQuery countQuery = BuildCountQuery(sqlQuery); return(GetScalar <int>(countQuery)); }
/// <summary> /// Returns number of records for given table identifier. May be RDBMS-specific. /// </summary> /// <param name="sqlQuery">The SQL QUERY model</param> /// <returns>Number of records</returns> public virtual async Task <int> GetTotalRecordsAsync(ISqlSelectQuery sqlQuery) { IParameterizedQuery countQuery = BuildCountQuery(sqlQuery); return(await GetScalarAsync <int>(countQuery)); }
/// <summary> /// Constructs a parameterized query model out of the SQL SELECT query model. /// </summary> /// <param name="sqlQuery">SQL SELECT query model</param> /// <returns>Parameterized query model</returns> public abstract IParameterizedQuery BuildQuery(ISqlSelectQuery sqlQuery);
/// <summary> /// Returns a total number of records in a given table. /// </summary> /// <param name="sqlQuery">SQL SELECT query model</param> /// <returns>Number of total records</returns> public override int GetTotalRecords(ISqlSelectQuery sqlQuery) { IParameterizedQuery countQuery = BuildCountQuery(sqlQuery); return((int)GetScalar <decimal>(countQuery)); }