public void Render(ISqlBuilder ctx, params Expression[] args) { var flag = args.Length == 3 ? "DATE_ADD" : "DATE_SUB"; var value = (TimeSpan)(args[2] as ConstantExpression).Value; ctx.AppendFormat("{0}({0}({0}({0}(", flag); ctx.Visit(args[1]); ctx.AppendFormat(",INTERVAL {0} SECOND),INTERVAL {1} MINUTE),INTERVAL {2} HOUR),INTERVAL {3} DAY)" , value.Seconds , value.Minutes , value.Hours , value.Days ); }
public void Render(ISqlBuilder ctx, params Expression[] args) { if (args.Length != 3) { throw new NotSupportedException(string.Format(Res.ArgumentCountError, "Insert", "", "3")); } var secondStartIndex = args[1];// Expression.Constant((int)(args[1] as ConstantExpression).Value + 1); ctx.Append("(CASE WHEN "); ctx.Visit(secondStartIndex); ctx.Append(" > LENGTH("); ctx.Visit(args[0]); ctx.Append(") THEN null ELSE (SUBSTR("); ctx.Visit(args[0]); ctx.Append(",1,"); ctx.Visit(Expression.Subtract(Expression.Property(args[1], "Value"), Expression.Constant(1, Types.Int32))); ctx.Append(") || "); ctx.Visit(args[2]); ctx.Append(" || SUBSTR("); ctx.Visit(args[0]); ctx.AppendFormat(","); ctx.Visit(secondStartIndex); ctx.Append(",LENGTH("); ctx.Visit(args[0]); ctx.Append(")))END)"); }
public void Render(ISqlBuilder builder, params Expression[] args) { var flag = args.Length == 3 ? "" : "-"; var value = (TimeSpan)(args[2] as ConstantExpression).Value; builder.AppendFormat("DATEADD('d',{4}{0},DATEADD('h',{4}{1},DATEADD('n',{4}{2},DATEADD('s',{4}{3}," , value.Days , value.Hours , value.Minutes , value.Seconds , flag); builder.Visit(args[1]); builder.Append("))))"); }
public void Render(ISqlBuilder builder, params Expression[] args) { var flag = args.Length == 3 ? "" : "-"; var value = (TimeSpan)(args[2] as ConstantExpression).Value; builder.Append("("); builder.Visit(args[1]); builder.AppendFormat(" + INTERVAL '{4}{0}' DAY + INTERVAL '{4}{1}' HOUR + INTERVAL '{4}{2}' MINUTE + INTERVAL '{4}{3}' SECOND" , value.Days , value.Hours , value.Minutes , value.Seconds , flag); builder.Append(")"); }
public void Render(ISqlBuilder builder, params Expression[] args) { var flag = args.Length == 3 ? "" : "-"; var value = (TimeSpan)(args[2] as ConstantExpression).Value; builder.AppendFormat("DATEADD(DAY,{4}{0},DATEADD(HOUR,{4}{1},DATEADD(MINUTE,{4}{2},DATEADD(SECOND,{4}{3}," , value.Days , value.Hours , value.Minutes , value.Seconds , flag); builder.Visit(args[1]); builder.Append("))))"); }
public void Render(ISqlBuilder ctx, params Expression[] args) { //datetime(?1, '?2 year','?3 month','?4 day','?5 hour','?6 minute','?7 second') var flag = args.Length == 3 ? "" : "-"; var value = (TimeSpan)((args[2] as NamedValueExpression).Value as ConstantExpression).Value; ctx.Append("DATETIME("); ctx.Visit(args[1]); ctx.AppendFormat(", '{4}{0} day','{4}{1} hour','{4}{2} minute','{4}{3} second')" , value.Days , value.Hours , value.Minutes , value.Seconds , flag); }
// 创建 SELECT 命令 DbRawCommand ResolveSelectCommandImpl(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 as NpgSqlBuilder).UseQuote = isOutermost; 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; (jf as NpgSqlBuilder).UseQuote = false; 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 ColumnExpressionVisitor(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 cmd2 = this.ResolveSelectCommandImpl(tree.Subquery, indent + 1, false, context); jf.Append(cmd2.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)); } 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 cmd2 = this.ResolveSelectCommandImpl(tree.Unions[index], indent, isOutermost, context); jf.Append(cmd2.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); }
// 解译 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 关系的,需要使用嵌套查询,否则分页查询会有问题 // 8.如果只有 Skip 没有 Take,则使用 Row_Number() Over()分页语法,其它使用 LIMIT ## OFFSET 语法 // 导航属性中有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 srcIsOutmost = context.CurrentIsOutermost; if (srcDbExpressionType == null) { context.CurrentExpressionType = DbExpressionType.Select; } if (srcIsOutmost == 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; ISqlBuilder sf = null; 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 范围 ColumnExpressionVisitor visitor = null; if (tree.Skip > 0 && tree.Take == 0) { sf = this.CreateSqlBuilder(context); sf.Indent = jf.Indent + 1; visitor = new ColumnExpressionVisitor(ag, sf, tree); visitor.Visit(tree.Select); } else { visitor = new ColumnExpressionVisitor(ag, jf, tree); visitor.Visit(tree.Select); } result.SelectedColumns = visitor.SelectedColumns; result.SelectedColumnText = visitor.SelectedColumnText; result.SelectedNavs = visitor.SelectedNavDescriptors; result.AddNavMembers(visitor.NavMembers); if (sf != null) { // 第一层嵌套 int index = 0; jf.AppendNewLine(); foreach (var column in result.SelectedColumns) { jf.AppendMember(alias, column.Name); jf.AppendAs(column.NewName); index += 1; if (index < result.SelectedColumns.Count) { jf.Append(','); jf.AppendNewLine(); } } jf.AppendNewLine(); jf.Append("FROM("); // 第二层嵌套 indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT"); jf.Append(sf); jf.Append(','); jf.AppendNewLine(); if (tree.OrderBys == null || tree.OrderBys.Count == 0) { throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'."); } jf.Append("ROW_NUMBER() OVER("); var visitor3 = new OrderByExpressionVisitor(ag, jf, tree.GroupBy, null); visitor3.Visit(tree.OrderBys, false); result.AddNavMembers(visitor3.NavMembers); jf.Append(") Row_Number0"); } } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (tree.Subquery != null) { // 子查询 jf.Append("("); var 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)// && !groupByPaging) { var visitor = new OrderByExpressionVisitor(ag, wf, tree.GroupBy, null); visitor.Visit(tree.OrderBys); result.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 // LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。 // LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。 // 初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。 // Limit n,-1 语法不支持,使用ROW_Number()语法代替 if (tree.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.Constor.GetSqlValue(tree.Take, context)); 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.HasMany && subquery != null && subquery.OrderBys.Count > 0 && subquery.Aggregate == null && !(subquery.Skip > 0 || subquery.Take > 0)) 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.TranslateSelectCommandImpl(tree.Unions[index], indent, isOutermost, context); jf.Append(cmd.CommandText); } } #endregion #region 分页查询 if (sf != null) { // 合并 WHERE result.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias); jf.AppendNewLine(); jf.Append("WHERE "); if (tree.Skip > 0) { jf.AppendMember(alias, "Row_Number0"); jf.Append(" > "); jf.Append(this.Constor.GetSqlValue(tree.Skip, context)); } } #endregion #region Any 子句 // 'Any' 子句 if (tree.HasAny) { // 产生 WHERE 子句 result.CombineFragments(); // 如果没有分页,则显式指定只查一笔记录 if (tree.Take == 0 && tree.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 0,1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias); } #endregion #region 还原状态 context.CurrentExpressionType = srcDbExpressionType; context.CurrentIsOutermost = srcIsOutmost; #endregion return(result); }
/// <summary> /// 创建 INSRT 命令 /// </summary> /// <param name="dbQuery">查询语义</param> /// <param name="token">解析上下文</param> /// <returns></returns> protected override RawCommand ResolveInsertCommand <T>(IDbQueryableInfo_Insert dbQuery, ResolveToken token) { TableAlias aliases = new TableAlias(); ISqlBuilder builder = this.CreateSqlBuilder(token); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); if (dbQuery.Entity != null) { object entity = dbQuery.Entity; ISqlBuilder seg_Columns = this.CreateSqlBuilder(token); ISqlBuilder seg_Values = this.CreateSqlBuilder(token); // 指定插入列 MemberAccessorCollection memberAccessors = typeRuntime.Members; if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0) { memberAccessors = new MemberAccessorCollection(); for (int i = 0; i < dbQuery.EntityColumns.Count; i++) { Expression curExpr = dbQuery.EntityColumns[i]; if (curExpr.NodeType == ExpressionType.Lambda) { curExpr = (curExpr as LambdaExpression).Body.ReduceUnary(); } if (curExpr.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("Can't read field name from expression {0}", dbQuery.EntityColumns[i]); } MemberExpression member = curExpr as MemberExpression; string name = member.Member.Name; memberAccessors[name] = typeRuntime.Members[name]; } } foreach (var m in memberAccessors) { var column = m.Column; if (column != null && column.NoMapped) { continue; } if (m.ForeignKey != null) { continue; } if (m.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } if (m != typeRuntime.Identity) { seg_Columns.AppendMember(m.Member.Name); seg_Columns.Append(','); var value = m.Invoke(entity); string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); seg_Values.Append(seg); seg_Values.Append(','); } } seg_Columns.Length -= 1; seg_Values.Length -= 1; if (dbQuery.Bulk == null || !dbQuery.Bulk.OnlyValue) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); builder.Append(seg_Columns); builder.Append(')'); builder.AppendNewLine(); builder.AppendTab(); builder.Append("VALUES"); } builder.Append('('); builder.Append(seg_Values); builder.Append(')'); if (dbQuery.Bulk != null && !dbQuery.Bulk.IsEndPos) { builder.Append(","); } if (dbQuery.Bulk == null && typeRuntime.Identity != null) { builder.Append(';'); builder.AppendNewLine(); builder.AppendFormat("SELECT LAST_INSERT_ROWID()"); builder.Append(" AS "); builder.Append(this.QuotePrefix); builder.Append(Constant.AUTO_INCREMENT_NAME); builder.Append(this.QuoteSuffix); } } else if (dbQuery.Query != null) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); int i = 0; MappingCommand cmd = this.ResolveSelectCommandImpl(dbQuery.Query, 0, false, token) as MappingCommand; //for (int i = 0; i < seg.Columns.Count; i++) foreach (var column in cmd.PickColumns) { builder.AppendMember(column.Name); if (i < cmd.PickColumns.Count - 1) { builder.Append(','); } i++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd.CommandText); } if (dbQuery.Bulk == null || dbQuery.Bulk.IsEndPos) { builder.Append(';'); } return(new RawCommand(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }
// 创建 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 命令 protected override Command ParseSelectCommand <T>(DbQueryableInfo_Select <T> sQueryInfo, int indent, bool isOuter, ParserToken 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 subQueryInfo = sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>; if (sQueryInfo.HasManyNavigation && subQueryInfo != null && subQueryInfo.StatisExpression != null) { sQueryInfo = subQueryInfo; } bool useStatis = sQueryInfo.StatisExpression != null; // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0"; bool useSubQuery = sQueryInfo.HaveDistinct || sQueryInfo.GroupByExpression != null || sQueryInfo.Skip > 0 || sQueryInfo.Take > 0; bool useOrderBy = (!useStatis || sQueryInfo.Skip > 0) && !sQueryInfo.HaveAny && (!sQueryInfo.ResultByManyNavigation || (sQueryInfo.Skip > 0 || sQueryInfo.Take > 0)); IDbQueryable dbQueryable = sQueryInfo.SourceQuery; TableAliasCache aliases = this.PrepareAlias <T>(sQueryInfo, token); SelectCommand cmd = new SelectCommand(this, aliases, token) { HasManyNavigation = sQueryInfo.HasManyNavigation }; ISqlBuilder jf = cmd.JoinFragment; ISqlBuilder wf = cmd.WhereFragment; jf.Indent = indent; #region 嵌套查询 if (useStatis && useSubQuery) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression, alias0); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); // SELECT COUNT(1) FROM jf.AppendNewLine(); jf.Append("FROM ( "); indent += 1; jf.Indent = indent; } #endregion #region 择子句 // SELECT 子句 if (jf.Indent > 0) { jf.AppendNewLine(); } if (sQueryInfo.HaveAny) { jf.Append("IF EXISTS("); indent += 1; jf.Indent = indent; jf.AppendNewLine(); } jf.Append("SELECT "); if (useStatis && !useSubQuery) { // 如果有统计函数,并且不是嵌套的话,则直接使用SELECT <MAX,MIN...>,不需要解析选择的字段 jf.AppendNewLine(); var visitor2 = new StatisExpressionVisitor(this, aliases, sQueryInfo.StatisExpression, sQueryInfo.GroupByExpression); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); } else { // DISTINCT 子句 if (sQueryInfo.HaveDistinct) { jf.Append("DISTINCT "); } // TOP 子句 if (sQueryInfo.Take > 0 && sQueryInfo.Skip == 0) { jf.AppendFormat("TOP({0})", jf.GetSqlValue(sQueryInfo.Take)); } // Any if (sQueryInfo.HaveAny) { jf.Append("TOP 1 1"); } #region 字段 if (!sQueryInfo.HaveAny) { // SELECT 范围 var visitor2 = new ColumnExpressionVisitor(this, aliases, sQueryInfo); visitor2.Write(jf); cmd.Columns = visitor2.Columns; cmd.Navigations = visitor2.Navigations; cmd.AddNavMembers(visitor2.NavMembers); } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (sQueryInfo.SubQueryInfo != null) { // 子查询 jf.Append('('); Command cmd2 = this.ParseSelectCommand <T>(sQueryInfo.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token); jf.Append(cmd2.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQueryInfo.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias0); jf.Append(' '); SqlDbContext context = (SqlDbContext)dbQueryable.DbContext; if (context.NoLock && !string.IsNullOrEmpty(this._widthNoLock)) { jf.Append(this._widthNoLock); } } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQueryInfo.Joins); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, sQueryInfo.WhereExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, sQueryInfo.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, sQueryInfo.HavingExpression, sQueryInfo.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (sQueryInfo.OrderBys.Count > 0 && useOrderBy) { visitor = new OrderByExpressionVisitor(this, aliases, sQueryInfo.OrderBys, sQueryInfo.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 if (sQueryInfo.Skip > 0) { if (sQueryInfo.OrderBys.Count == 0) { throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'."); } wf.AppendNewLine(); wf.Append("OFFSET "); wf.Append(wf.GetSqlValue(sQueryInfo.Skip)); wf.Append(" ROWS"); if (sQueryInfo.Take > 0) { wf.Append(" FETCH NEXT "); wf.Append(wf.GetSqlValue(sQueryInfo.Take)); wf.Append(" ROWS ONLY "); } } #endregion #region 嵌套查询 if (useStatis && useSubQuery) { cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } #endregion #region 嵌套导航 if (sQueryInfo.HasManyNavigation && subQueryInfo.StatisExpression == null && subQueryInfo != null && subQueryInfo.OrderBys.Count > 0 && !(subQueryInfo.Skip > 0 || subQueryInfo.Take > 0)) { // TODO Include 从表,没分页,OrderBy 报错 cmd.Convergence(); visitor = new OrderByExpressionVisitor(this, aliases, subQueryInfo.OrderBys); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (sQueryInfo.Unions != null && sQueryInfo.Unions.Count > 0) { cmd.Convergence(); for (int index = 0; index < sQueryInfo.Unions.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } Command cmd2 = this.ParseSelectCommand <T>(sQueryInfo.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token); jf.Append(cmd2.CommandText); } } #endregion #region Any 子句 // 'Any' 子句 if (sQueryInfo.HaveAny) { cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") SELECT 1 ELSE SELECT 0"); } #endregion return(cmd); }
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 关系的,需要使用嵌套查询,否则分页查询会有问题 // 8.如果只有 Skip 没有 Take,则使用 Row_Number() Over()分页语法,其它使用 LIMIT ## OFFSET 语法 // 导航属性中有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; // 没有聚合函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY string alias0 = token != null && !string.IsNullOrEmpty(token.AliasPrefix) ? (token.AliasPrefix + "0") : "t0"; bool useSubquery = dbQuery.HasDistinct || dbQuery.GroupBy != null || dbQuery.Skip > 0 || dbQuery.Take > 0; 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; ISqlBuilder sf = null; jf.Indent = indent; #region 嵌套查询 if (useStatis && useSubquery) { // 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 && !useSubquery) { // 如果有聚合函数,并且不是嵌套的话,则直接使用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 visitor_ = new ColumnExpressionVisitor(this, aliases, dbQuery); if (dbQuery.Skip > 0 && dbQuery.Take == 0) { sf = this.CreateSqlBuilder(token); sf.Indent = jf.Indent + 1; visitor_.Write(sf); } else { visitor_.Write(jf); } result.PickColumns = visitor_.PickColumns; result.PickColumnText = visitor_.PickColumnText; result.PickNavDescriptors = visitor_.PickNavDescriptors; result.AddNavMembers(visitor_.NavMembers); if (sf != null) { // 第一层嵌套 int index = 0; jf.AppendNewLine(); foreach (var column in result.PickColumns) { jf.AppendMember(alias0, column.Name); jf.AppendAs(column.NewName); index += 1; if (index < result.PickColumns.Count) { jf.Append(','); jf.AppendNewLine(); } } jf.AppendNewLine(); jf.Append("FROM("); // 第二层嵌套 indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT"); jf.Append(sf); jf.Append(','); jf.AppendNewLine(); if (dbQuery.OrderBys.Count == 0) { throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'."); } jf.Append("ROW_NUMBER() OVER("); var visitor3 = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy); visitor3.Write(jf, false); result.AddNavMembers(visitor3.NavMembers); jf.Append(") Row_Number0"); } } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (dbQuery.Subquery != null) { // 子查询 jf.Append("("); var 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)// && !groupByPaging) { visitor = new OrderByExpressionVisitor(this, aliases, dbQuery.OrderBys, dbQuery.GroupBy); visitor.Write(wf); result.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 // LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。 // LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。 // 初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。 // Limit n,-1 语法不支持,使用ROW_Number()语法代替 if (dbQuery.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", this.DbValue.GetSqlValue(dbQuery.Take, token)); wf.AppendFormat(" OFFSET {0}", this.DbValue.GetSqlValue(dbQuery.Skip, token)); } #endregion #region 嵌套查询 if (useStatis && useSubquery) { 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.ResolveSelectCommandImpl(dbQuery.Unions[index], indent, isOuter, token); jf.Append(cmd.CommandText); } } #endregion #region 分页查询 if (sf != null) { // 合并 WHERE result.CombineFragments(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.AppendNewLine(); jf.Append("WHERE "); if (dbQuery.Skip > 0) { jf.AppendMember(alias0, "Row_Number0"); jf.Append(" > "); jf.Append(this.DbValue.GetSqlValue(dbQuery.Skip, token)); } } #endregion #region Any 子句 // 'Any' 子句 if (dbQuery.HasAny) { // 产生 WHERE 子句 result.CombineFragments(); // 如果没有分页,则显式指定只查一笔记录 if (dbQuery.Take == 0 && dbQuery.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 0,1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion return(result); }
// 创建 SELECT 命令 DbCommandDefinition ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQuery, int indent, bool isOuter, List <IDbDataParameter> parameters = null) { // 说明: // 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 关系的,需要使用嵌套查询,否则分页查询会有问题 // 8.如果只有 Skip 没有 Take,则使用 Row_Number() Over()分页语法,其它使用 LIMIT ## OFFSET 语法 // 导航属性中有1:n关系,只统计主表 // 例:AccountList = a.Client.AccountList, DbQueryableInfo_Select <T> innerQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>; if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.Statis != null) { sQuery = innerQuery; } bool useNesting = sQuery.HaveDistinct || sQuery.GroupBy != null || sQuery.Skip > 0 || sQuery.Take > 0; bool useStatis = sQuery.Statis != null; // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.GenByListNavigation || (sQuery.Skip > 0 || sQuery.Take > 0)); IDbQueryable dbQueryable = sQuery.SourceQuery; TableAliasCache aliases = this.PrepareAlias <T>(sQuery); SelectDbCommandDefinition cmd = new SelectDbCommandDefinition(this, aliases, parameters) { HaveListNavigation = sQuery.HaveListNavigation }; ISqlBuilder jf = cmd.JoinFragment; ISqlBuilder wf = cmd.WhereFragment; ISqlBuilder sf = null; jf.Indent = indent; #region 嵌套查询 if (useStatis && useNesting) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy, "t0"); visitor2.Write(jf); cmd.AddNavMembers(visitor2.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 (sQuery.HaveAny) { 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 visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.Statis, sQuery.GroupBy); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); } else { // DISTINCT 子句 if (sQuery.HaveDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!sQuery.HaveAny) { // SELECT 范围 var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery); if (sQuery.Skip > 0 && sQuery.Take == 0) { sf = this.CreateSqlBuilder(parameters); sf.Indent = jf.Indent + 1; visitor2.Write(sf); } else { visitor2.Write(jf); } cmd.Columns = visitor2.Columns; cmd.Navigations = visitor2.Navigations; cmd.AddNavMembers(visitor2.NavMembers); if (sf != null) { // 第一层嵌套 int index = 0; jf.AppendNewLine(); foreach (var entry in cmd.Columns) { jf.AppendMember("t0", entry.Key); jf.AppendAs(entry.Key); index += 1; if (index < cmd.Columns.Count) { jf.Append(','); jf.AppendNewLine(); } } jf.AppendNewLine(); jf.Append("FROM("); // 第二层嵌套 indent += 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append("SELECT"); jf.Append(sf); jf.Append(','); jf.AppendNewLine(); if (sQuery.OrderBy.Count == 0) { throw new XFrameworkException("The method 'OrderBy' must be called before 'Skip'."); } jf.Append("ROW_NUMBER() OVER("); var visitor3 = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy); visitor3.Write(jf, false); cmd.AddNavMembers(visitor3.NavMembers); jf.Append(") Row_Number0"); } } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (sQuery.SubQueryInfo != null) { // 子查询 jf.Append("("); var cmd2 = this.ParseSelectCommandImpl <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, jf.Parameters); jf.Append(cmd2.CommandText); jf.AppendNewLine(); jf.Append(") t0 "); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(" t0 "); //if (dbQueryable.DbContext.NoLock && !string.IsNullOrEmpty(this.WidthNoLock)) jf.Append(this.WidthNoLock); } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Join); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, sQuery.Where); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, sQuery.Having, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (sQuery.OrderBy.Count > 0 && useOrderBy)// && !groupByPaging) { visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBy, sQuery.GroupBy); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 // LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。 // LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。 // 初始记录行的偏移量是 0(而不是 1): 为了与 PostgreSQL 兼容,MySQL 也支持句法: LIMIT # OFFSET #。 // Limit n,-1 语法不支持,使用ROW_Number()语法代替 if (sQuery.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", wf.GetSqlValue(sQuery.Take)); wf.AppendFormat(" OFFSET {0}", wf.GetSqlValue(sQuery.Skip)); } #endregion #region 嵌套查询 if (useStatis && useNesting) { cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(" ) t0"); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (sQuery.HaveListNavigation && innerQuery != null && innerQuery.OrderBy.Count > 0 && innerQuery.Statis == null && !(innerQuery.Skip > 0 || innerQuery.Take > 0)) { cmd.Convergence(); visitor = new OrderByExpressionVisitor(this, aliases, innerQuery.OrderBy);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (sQuery.Union != null && sQuery.Union.Count > 0) { cmd.Convergence(); for (int index = 0; index < sQuery.Union.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } DbCommandDefinition cmd2 = this.ParseSelectCommandImpl <T>(sQuery.Union[index] as DbQueryableInfo_Select <T>, indent, isOuter, jf.Parameters); jf.Append(cmd2.CommandText); } } #endregion #region 分页查询 if (sf != null) { // 合并 WHERE cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") t0"); jf.AppendNewLine(); jf.Append("WHERE "); if (sQuery.Skip > 0) { jf.Append("t0.Row_Number0 > "); jf.Append(jf.GetSqlValue(sQuery.Skip)); } } #endregion #region Any 子句 // 'Any' 子句 if (sQuery.HaveAny) { // 产生 WHERE 子句 cmd.Convergence(); // 如果没有分页,则显式指定只查一笔记录 if (sQuery.Take == 0 && sQuery.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 0,1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") t0"); } #endregion return(cmd); }
// 创建 SELECT 命令 Command ParseSelectCommandImpl <T>(DbQueryableInfo_Select <T> sQuery, int indent, bool isOuter, ParserToken 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, DbQueryableInfo_Select <T> subQuery = sQuery.SubQueryInfo as DbQueryableInfo_Select <T>; if (sQuery.HasManyNavigation && subQuery != null && subQuery.StatisExpression != null) { sQuery = subQuery; } bool useStatis = sQuery.StatisExpression != null; bool useNesting = sQuery.HaveDistinct || sQuery.GroupByExpression != null || sQuery.Skip > 0 || sQuery.Take > 0; string alias0 = token != null && !string.IsNullOrEmpty(token.TableAliasName) ? (token.TableAliasName + "0") : "t0"; // 没有统计函数或者使用 'Skip' 子句,则解析OrderBy // 导航属性如果使用嵌套,除非有 TOP 或者 OFFSET 子句,否则不能用ORDER BY bool useOrderBy = (!useStatis || sQuery.Skip > 0) && !sQuery.HaveAny && (!sQuery.ResultByManyNavigation || (sQuery.Skip > 0 || sQuery.Take > 0)); IDbQueryable dbQueryable = sQuery.SourceQuery; TableAliasCache aliases = this.PrepareAlias <T>(sQuery, token); SelectCommand cmd = new SelectCommand(this, aliases, token) { HasManyNavigation = sQuery.HasManyNavigation }; ISqlBuilder jf = cmd.JoinFragment; ISqlBuilder wf = cmd.WhereFragment; (jf as NpgSqlBuilder).IsOuter = isOuter; jf.Indent = indent; #region 嵌套查询 if (useStatis && useNesting) { // SELECT jf.Append("SELECT "); jf.AppendNewLine(); // SELECT COUNT(1) var visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.StatisExpression, sQuery.GroupByExpression, alias0); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); // SELECT COUNT(1) FROM jf.AppendNewLine(); jf.Append("FROM ( "); indent += 1; jf.Indent = indent; (jf as NpgSqlBuilder).IsOuter = false; } #endregion #region 择子句 // SELECT 子句 if (jf.Indent > 0) { jf.AppendNewLine(); } jf.Append("SELECT "); if (sQuery.HaveAny) { 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 visitor2 = new StatisExpressionVisitor(this, aliases, sQuery.StatisExpression, sQuery.GroupByExpression); visitor2.Write(jf); cmd.AddNavMembers(visitor2.NavMembers); } else { // DISTINCT 子句 if (sQuery.HaveDistinct) { jf.Append("DISTINCT "); } #region 择字段 if (!sQuery.HaveAny) { // SELECT 范围 var visitor2 = new ColumnExpressionVisitor(this, aliases, sQuery); visitor2.Write(jf); cmd.Columns = visitor2.Columns; cmd.Navigations = visitor2.Navigations; cmd.AddNavMembers(visitor2.NavMembers); } #endregion } #endregion #region 顺序解析 // FROM 子句 jf.AppendNewLine(); jf.Append("FROM "); if (sQuery.SubQueryInfo != null) { // 子查询 jf.Append('('); Command cmd2 = this.ParseSelectCommandImpl <T>(sQuery.SubQueryInfo as DbQueryableInfo_Select <T>, indent + 1, false, token); jf.Append(cmd2.CommandText); jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); jf.Append(' '); } else { var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo(sQuery.FromType); jf.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); jf.Append(' '); jf.Append(alias0); jf.Append(' '); } // LEFT<INNER> JOIN 子句 ExpressionVisitorBase visitor = new JoinExpressionVisitor(this, aliases, sQuery.Joins); visitor.Write(jf); wf.Indent = jf.Indent; // WHERE 子句 visitor = new WhereExpressionVisitor(this, aliases, sQuery.WhereExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // GROUP BY 子句 visitor = new GroupByExpressionVisitor(this, aliases, sQuery.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // HAVING 子句 visitor = new HavingExpressionVisitor(this, aliases, sQuery.HavingExpression, sQuery.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); // ORDER 子句 if (sQuery.OrderBys.Count > 0 && useOrderBy) { visitor = new OrderByExpressionVisitor(this, aliases, sQuery.OrderBys, sQuery.GroupByExpression); visitor.Write(wf); cmd.AddNavMembers(visitor.NavMembers); } #endregion #region 分页查询 if (sQuery.Take > 0) { wf.AppendNewLine().AppendFormat("LIMIT {0}", wf.GetSqlValue(sQuery.Take)); } if (sQuery.Skip > 0) { wf.AppendFormat(" OFFSET {0}", wf.GetSqlValue(sQuery.Skip)); } #endregion #region 嵌套查询 if (useStatis && useNesting) { cmd.Convergence(); indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion #region 嵌套导航 // TODO Include 从表,没分页,OrderBy 报错 if (sQuery.HasManyNavigation && subQuery != null && subQuery.OrderBys.Count > 0 && subQuery.StatisExpression == null && !(subQuery.Skip > 0 || subQuery.Take > 0)) { cmd.Convergence(); visitor = new OrderByExpressionVisitor(this, aliases, subQuery.OrderBys);//, null, "t0"); visitor.Write(jf); } #endregion #region 并集查询 // UNION 子句 if (sQuery.Unions != null && sQuery.Unions.Count > 0) { cmd.Convergence(); for (int index = 0; index < sQuery.Unions.Count; index++) { jf.AppendNewLine(); jf.Append("UNION ALL"); if (indent == 0) { jf.AppendNewLine(); } Command cmd2 = this.ParseSelectCommandImpl <T>(sQuery.Unions[index] as DbQueryableInfo_Select <T>, indent, isOuter, token); jf.Append(cmd2.CommandText); } } #endregion #region Any 子句 // 'Any' 子句 if (sQuery.HaveAny) { // 产生 WHERE 子句 cmd.Convergence(); // 如果没有分页,则显式指定只查一笔记录 if (sQuery.Take == 0 && sQuery.Skip == 0) { jf.AppendNewLine(); jf.Append("LIMIT 1"); } indent -= 1; jf.Indent = indent; jf.AppendNewLine(); jf.Append(") "); jf.Append(alias0); } #endregion return(cmd); }
/// <summary> /// 创建 INSRT 命令 /// </summary> /// <param name="tree">查询语义</param> /// <param name="context">解析SQL命令上下文</param> /// <returns></returns> protected override DbRawCommand TranslateInsertCommand <T>(DbQueryInsertTree tree, ITranslateContext context) { ISqlBuilder builder = this.CreateSqlBuilder(context); var typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); var caseSensitive = context != null && context.DbContext != null ? ((NpgDbContext)context.DbContext).CaseSensitive : false; if (tree.Entity != null) { object entity = tree.Entity; ISqlBuilder columnsBuilder = this.CreateSqlBuilder(context); ISqlBuilder valuesBuilder = this.CreateSqlBuilder(context); // 指定插入列 var members = typeRuntime.Members; if (tree.EntityColumns != null && tree.EntityColumns.Count > 0) { members = new MemberAccessorCollection(); for (int i = 0; i < tree.EntityColumns.Count; i++) { Expression curExpr = tree.EntityColumns[i]; if (curExpr.NodeType == ExpressionType.Lambda) { curExpr = (curExpr as LambdaExpression).Body.ReduceUnary(); } if (curExpr.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("Can't read field name from expression {0}", tree.EntityColumns[i]); } var member = curExpr as MemberExpression; string name = member.Member.Name; members[name] = typeRuntime.Members[name]; } } foreach (var item in members) { var m = item as FieldAccessorBase; if (m == null || !m.IsDbField) { continue; } if (m != typeRuntime.Identity) { columnsBuilder.AppendMember(null, m.Member, typeRuntime.Type); columnsBuilder.Append(','); var value = m.Invoke(entity); string sqlExpression = this.Constor.GetSqlValueWidthDefault(value, context, m.Column); valuesBuilder.Append(sqlExpression); valuesBuilder.Append(','); } } columnsBuilder.Length -= 1; valuesBuilder.Length -= 1; if (tree.Bulk == null || !tree.Bulk.OnlyValue) { builder.Append("INSERT INTO "); builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); builder.Append('('); builder.Append(columnsBuilder); builder.Append(')'); builder.AppendNewLine(); builder.AppendTab(); builder.Append("VALUES"); } builder.Append('('); builder.Append(valuesBuilder); builder.Append(')'); if (tree.Bulk != null && !tree.Bulk.IsEndPos) { builder.Append(","); } if (tree.Bulk == null && typeRuntime.Identity != null) { builder.Append(';'); builder.AppendNewLine(); // sequence,命名原则是 tablename_columnname_seq. builder.AppendFormat("SELECT CURRVAL('{2}{0}_{1}_seq{2}')", typeRuntime.TableName, TypeUtils.GetFieldName(typeRuntime.Identity.Member, typeRuntime.Type), caseSensitive ? "\"" : string.Empty); builder.Append(" AS "); builder.Append(this.QuotePrefix); builder.Append(AppConst.AUTO_INCREMENT_NAME); builder.Append(this.QuoteSuffix); } } else if (tree.Select != null) { builder.Append("INSERT INTO "); builder.AppendTable(typeRuntime.TableSchema, typeRuntime.TableName, typeRuntime.IsTemporary); builder.Append('('); var srcDbExpressionType = context.CurrentExpressionType; var srcIsOutermost = context.CurrentIsOutermost; context.CurrentExpressionType = DbExpressionType.Insert; context.CurrentIsOutermost = false; var cmd = this.ResolveSelectCommandImpl(tree.Select, 0, false, context) as DbSelectCommand; context.CurrentExpressionType = srcDbExpressionType; context.CurrentIsOutermost = srcIsOutermost; int index = 0; foreach (var column in cmd.SelectedColumns) { builder.AppendMember(column.Name); if (index < cmd.SelectedColumns.Count - 1) { builder.Append(','); } index++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd.CommandText); } if (tree.Bulk == null || tree.Bulk.IsEndPos) { builder.Append(';'); } return(new DbRawCommand(builder.ToString(), builder.TranslateContext != null ? builder.TranslateContext.Parameters : null, System.Data.CommandType.Text)); }
// 创建 INSRT 命令 protected override Command ResolveInsertCommand <T>(DbQueryableInfo_Insert <T> dbQuery, ResolveToken token) { ISqlBuilder builder = this.CreateSqlBuilder(token); TypeRuntimeInfo typeRuntime = TypeRuntimeInfoCache.GetRuntimeInfo <T>(); TableAliasCache aliases = new TableAliasCache(); if (dbQuery.Entity != null) { object entity = dbQuery.Entity; ISqlBuilder columnsBuilder = this.CreateSqlBuilder(token); ISqlBuilder valuesBuilder = this.CreateSqlBuilder(token); // 指定插入列 MemberInvokerCollection invokers = typeRuntime.Invokers; if (dbQuery.EntityColumns != null && dbQuery.EntityColumns.Count > 0) { invokers = new MemberInvokerCollection(); for (int i = 0; i < dbQuery.EntityColumns.Count; i++) { Expression curExpr = dbQuery.EntityColumns[i]; if (curExpr.NodeType == ExpressionType.Lambda) { curExpr = (curExpr as LambdaExpression).Body.ReduceUnary(); } if (curExpr.NodeType != ExpressionType.MemberAccess) { throw new XFrameworkException("Can't read field name from expression {0}", dbQuery.EntityColumns[i]); } MemberExpression member = curExpr as MemberExpression; string name = member.Member.Name; invokers[name] = typeRuntime.Invokers[name]; } } foreach (var invoker in invokers) { var column = invoker.Column; if (column != null && column.NoMapped) { continue; } if (invoker.ForeignKey != null) { continue; } if (invoker.Member.MemberType == System.Reflection.MemberTypes.Method) { continue; } if (invoker != dbQuery.AutoIncrement) { columnsBuilder.AppendMember(invoker.Member.Name); columnsBuilder.Append(','); var value = invoker.Invoke(entity); string seg = this.DbValue.GetSqlValueWidthDefault(value, token, column); valuesBuilder.Append(seg); valuesBuilder.Append(','); } } columnsBuilder.Length -= 1; valuesBuilder.Length -= 1; if (dbQuery.Bulk == null || !dbQuery.Bulk.OnlyValue) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); builder.Append(columnsBuilder); builder.Append(')'); builder.AppendNewLine(); builder.AppendNewTab(); builder.Append("VALUES"); } builder.Append('('); builder.Append(valuesBuilder); builder.Append(')'); if (dbQuery.Bulk != null && !dbQuery.Bulk.IsEndPos) { builder.Append(","); } if (dbQuery.Bulk == null && dbQuery.AutoIncrement != null) { builder.Append(';'); builder.AppendNewLine(); // sequence,命名原则是 tablename_columnname_seq. builder.AppendFormat("SELECT CURRVAL('{0}_{1}_seq')", typeRuntime.TableName, dbQuery.AutoIncrement.Member.Name); builder.Append(" AS "); builder.Append(this.QuotePrefix); builder.Append(Constant.AUTOINCREMENTNAME); builder.Append(this.QuoteSuffix); } } else if (dbQuery.SelectInfo != null) { builder.Append("INSERT INTO "); builder.AppendMember(typeRuntime.TableName, !typeRuntime.IsTemporary); builder.Append('('); int i = 0; MapperCommand cmd2 = this.ResolveSelectCommandImpl(dbQuery.SelectInfo, 0, false, token) as MapperCommand; //for (int i = 0; i < seg.Columns.Count; i++) foreach (var column in cmd2.PickColumns) { builder.AppendMember(column.Name); if (i < cmd2.PickColumns.Count - 1) { builder.Append(','); } i++; } builder.Append(')'); builder.AppendNewLine(); builder.Append(cmd2.CommandText); } if (dbQuery.Bulk == null || dbQuery.Bulk.IsEndPos) { builder.Append(';'); } return(new Command(builder.ToString(), builder.Token != null ? builder.Token.Parameters : null, System.Data.CommandType.Text)); }