// 创建 SELECT 命令 RawCommand ResolveSelectCommandImpl(IDbQueryableInfo_Select dbQuery, int indent, bool isOuter, ResolveToken token) { // 说明: // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句 // 2.在有聚合函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询 // 3.'Any' 子句将翻译成 IF EXISTS... // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要 // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询 // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题 // 导航属性中有1:n关系,只统计主表 // 例:AccountList = a.Client.AccountList, var subQuery = dbQuery.Subquery as IDbQueryableInfo_Select; if (dbQuery.HasMany && subQuery != null && subQuery.Aggregate != null) { dbQuery = subQuery; } bool useStatis = dbQuery.Aggregate != null; bool useNesting = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0; string alias0 = token != null && !string.IsNullOrEmpty(token.AliasPrefix) ? (token.AliasPrefix + "0") : "t0"; // 没有聚合函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY bool useOrderBy = (!useStatis || dbQuery.Skip > 0) && !dbQuery.HasAny && (!dbQuery.IsParsedByMany || (dbQuery.Skip > 0 || dbQuery.Take > 0)); TableAlias aliases = this.PrepareTableAlias(dbQuery, token); var result = new MappingCommand(this, aliases, token) { HasMany = dbQuery.HasMany }; ISqlBuilder jf = result.JoinFragment; ISqlBuilder wf = result.WhereFragment; jf.Indent = indent; #region 嵌套查询 if (useStatis && useNesting) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy, alias0); visitor_.Write(jf); result.AddNavMembers(visitor_.NavMembers); // SELECT COUNT(1) FROM jf.AppendNewLine(); jf.Append("FROM ( "); indent += 1; jf.Indent = indent; } #endregion #region 择子句 // SELECT 子句 if (jf.Indent > 0) { jf.AppendNewLine(); } jf.Append("SELECT "); if (dbQuery.HasAny) { jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM ("); indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT 1 "); } if (useStatis && !useNesting) { // 如果有聚合函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段 jf.AppendNewLine(); var visitor_ = new AggregateExpressionVisitor(this, aliases, dbQuery.Aggregate, dbQuery.GroupBy); visitor_.Write(jf); result.AddNavMembers(visitor_.NavMembers); } else { // DISTINCT 子句 if (dbQuery.HasDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!dbQuery.HasAny) { // SELECT 范围 var visitor2 = new SQLiteColumnExpressionVisitor(this, aliases, dbQuery); visitor2.Write(jf); result.PickColumns = visitor2.PickColumns; result.PickColumnText = visitor2.PickColumnText; result.PickNavDescriptors = visitor2.PickNavDescriptors; result.AddNavMembers(visitor2.NavMembers); } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (dbQuery.Subquery != null) { // 子查询 jf.Append('('); RawCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Subquery, indent + 1, false, token); jf.Append(cmd.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(dbQuery.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias0); jf.Append(' '); } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, dbQuery.Joins); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, dbQuery.Where); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, dbQuery.Having, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (dbQuery.OrderBys.Count > 0 && useOrderBy) { visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 if (dbQuery.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(dbQuery.Take, token)); } else if (dbQuery.Take == 0 && dbQuery.Skip > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(-1, token)); } if (dbQuery.Skip > 0) { wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(dbQuery.Skip, token)); } #endregion #region 嵌套查询 if (useStatis && useNesting) { result.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (dbQuery.HasMany && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.Aggregate == null && !(subQuery.Skip > 0 || subQuery.Take > 0)) { result.CombineFragments(); visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (dbQuery.Unions != null && dbQuery.Unions.Count > 0) { result.CombineFragments(); for (int index = 0; index < dbQuery.Unions.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } RawCommand cmd = this.ResolveSelectCommand(dbQuery.Unions[index], indent, isOuter, token); jf.Append(cmd.CommandText); } } #endregion #region Any 子句 // 'Any' 子句 if (dbQuery.HasAny) { // 产生 WHERE 子句 result.CombineFragments(); // 如果没有分页,则显式指定只查一笔记录 if (dbQuery.Take == 0 && dbQuery.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion return(result); }
// 创建 SELECT 命令 private DbRawCommand TranslateSelectCommandImpl(DbQuerySelectTree tree, int indent, bool isOutermost, ITranslateContext context) { // 说明: // 1.OFFSET 前必须要有 'ORDER BY',即 'Skip' 子句前必须使用 'OrderBy' 子句 // 2.在有聚合函数的<MAX,MIN...>情况下,如果有 'Distinct' 'GroupBy' 'Skip' 'Take' 子句,则需要使用嵌套查询 // 3.'Any' 子句将翻译成 IF EXISTS... // 4.分组再分页时需要使用嵌套查询,此时子查询不需要 'OrderBy' 子句,但最外层则需要 // 5.'Skip' 'Take' 子句视为语义结束符,在其之后的子句将使用嵌套查询 // 6.导航属性中有 1:n 关系的,需要使用嵌套查询,否则分页查询会有问题 // 导航属性中有1:n关系,只统计主表 // 例:AccountList = a.Client.AccountList, var subquery = tree.Subquery as DbQuerySelectTree; if (tree.SelectHasMany && subquery != null && subquery.Aggregate != null) { tree = subquery; } var srcDbExpressionType = context.CurrentExpressionType; var srcIsOutermost = context.CurrentIsOutermost; if (srcDbExpressionType == null) { context.CurrentExpressionType = DbExpressionType.Select; } if (srcIsOutermost == null || !isOutermost) { context.CurrentIsOutermost = isOutermost; } bool useAggregate = tree.Aggregate != null; // 没有聚合函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY // 第一层的表别名 string alias = context != null && !string.IsNullOrEmpty(context.AliasPrefix) ? (context.AliasPrefix + "0") : "t0"; bool useSubquery = tree.HasDistinct || tree.GroupBy != null || tree.Skip > 0 || tree.Take > 0; bool useOrderBy = (!useAggregate || tree.Skip > 0) && !tree.HasAny && (!tree.SelectHasMany || (tree.Skip > 0 || tree.Take > 0)); AliasGenerator ag = this.PrepareTableAlias(tree, context != null ? context.AliasPrefix : null); var result = new DbSelectCommand(context, ag, tree.SelectHasMany); ISqlBuilder jf = result.JoinFragment; ISqlBuilder wf = result.WhereFragment; jf.Indent = indent; #region 嵌套查询 if (useAggregate && useSubquery) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, alias); visitor.Visit(tree.Aggregate); result.AddNavMembers(visitor.NavMembers); // SELECT COUNT(1) FROM jf.AppendNewLine(); jf.Append("FROM ( "); indent += 1; jf.Indent = indent; context.CurrentIsOutermost = false; } #endregion #region 择子句 // SELECT 子句 if (jf.Indent > 0) { jf.AppendNewLine(); } jf.Append("SELECT "); if (tree.HasAny) { jf.Append("CASE WHEN COUNT(1) = 1 THEN 1 ELSE 0 END FROM ("); indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT 1 "); } if (useAggregate && !useSubquery) { // 如果有聚合函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段 jf.AppendNewLine(); var visitor = new AggregateExpressionVisitor(ag, jf, tree.GroupBy, null); visitor.Visit(tree.Aggregate); result.AddNavMembers(visitor.NavMembers); } else { // DISTINCT 子句 if (tree.HasDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!tree.HasAny) { // SELECT 范围 var visitor = new SQLiteColumnExpressionVisitor(ag, jf, tree); visitor.Visit(tree.Select); result.SelectedColumns = visitor.SelectedColumns; result.SelectedColumnText = visitor.SelectedColumnText; result.SelectedNavs = visitor.SelectedNavDescriptors; result.AddNavMembers(visitor.NavMembers); } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (tree.Subquery != null) { // 子查询 jf.Append('('); DbRawCommand cmd = this.TranslateSelectCommandImpl(tree.Subquery, indent + 1, false, context); jf.Append(cmd.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias); jf.Append(' '); } else if (tree.FromSql != null) { if (tree.FromSql.DbContext == null) { tree.FromSql.DbContext = context.DbContext; } DbRawSql rawSql = tree.FromSql; // 解析参数 object[] args = null; if (rawSql.Parameters != null) { args = rawSql.Parameters.Select(x => this.Constor.GetSqlValue(x, context)).ToArray(); } string sql = rawSql.CommandText; if (args != null && args.Length > 0) { sql = string.Format(sql, args); } // 子查询 jf.Append('('); var cmd = new DbRawCommand(sql, context.Parameters, CommandType.Text); jf.Append(cmd.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias); jf.Append(' '); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(tree.From); jf.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias); jf.Append(' '); } // LEFT<INNER> JOIN 子句 if (tree.Joins != null) { var visitor = new JoinExpressionVisitor(ag, jf); visitor.Visit(tree.Joins); } wf.Indent = jf.Indent; // WHERE 子句 if (tree.Wheres != null) { var visitor = new WhereExpressionVisitor(ag, wf); visitor.Visit(tree.Wheres); result.AddNavMembers(visitor.NavMembers); } // GROUP BY 子句 if (tree.GroupBy != null) { var visitor = new GroupByExpressionVisitor(ag, wf); visitor.Visit(tree.GroupBy); result.AddNavMembers(visitor.NavMembers); } // HAVING 子句 if (tree.Havings != null) { var visitor = new HavingExpressionVisitor(ag, wf, tree.GroupBy); visitor.Visit(tree.Havings); result.AddNavMembers(visitor.NavMembers); } // ORDER 子句 if (tree.OrderBys != null && tree.OrderBys.Count > 0 && useOrderBy) { var visitor = new OrderByExpressionVisitor(ag, wf, tree.GroupBy, null); visitor.Visit(tree.OrderBys); result.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 if (tree.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.Constor.GetSqlValue(tree.Take, context)); } else if (tree.Take == 0 && tree.Skip > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.Constor.GetSqlValue(-1, context)); } if (tree.Skip > 0) { wf.AppendFormat(" OFFSET {0}", this.Constor.GetSqlValue(tree.Skip, context)); } #endregion #region 嵌套查询 if (useAggregate && useSubquery) { result.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (tree.SelectHasMany && subquery.Aggregate == null && subquery != null && subquery.OrderBys != null && subquery.OrderBys.Count > 0 && !(subquery.Skip > 0 || subquery.Take > 0)) { result.CombineFragments(); var visitor = new OrderByExpressionVisitor(ag, jf, null, null); visitor.Visit(subquery.OrderBys); } #endregion #region 并集查询 // UNION 子句 if (tree.Unions != null && tree.Unions.Count > 0) { result.CombineFragments(); for (int index = 0; index < tree.Unions.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } DbRawCommand cmd = this.TranslateSelectCommand(tree.Unions[index], indent, isOutermost, context); jf.Append(cmd.CommandText); } } #endregion #region Any 子句 // 'Any' 子句 if (tree.HasAny) { // 产生 WHERE 子句 result.CombineFragments(); // 如果没有分页,则显式指定只查一笔记录 if (tree.Take == 0 && tree.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias); } #endregion #region 还原状态 context.CurrentExpressionType = srcDbExpressionType; context.CurrentIsOutermost = srcIsOutermost; #endregion return(result); }