static string OrderByItemStr(OrderByExpr orderBy, SqlExprParams pars) { return ($"{SqlExpression.ExprToSql(orderBy.Expr.Body, pars.ReplaceSelectParams(orderBy.Expr.Parameters[0], null), true)} " + $"{(orderBy.Order == OrderByOrder.Asc ? "ASC" : orderBy.Order == OrderByOrder.Desc ? "DESC" : throw new ArgumentException())}" + $"{(orderBy.Nulls == OrderByNulls.NullsFirst ? " NULLS FIRST" : orderBy.Nulls == OrderByNulls.NullsLast ? " NULLS LAST" : "")}"); }
static string CallToSql(MethodCallExpression call, SqlExprParams pars) { var funcAtt = call.Method.GetCustomAttribute <SqlNameAttribute>(); if (funcAtt != null) { var args = string.Join(", ", call.Arguments.Select(x => ExprToSql(x, pars, false))); return($"{funcAtt.SqlName}({args})"); } else if (call.Method.DeclaringType == typeof(Sql)) { switch (call.Method.Name) { case nameof(Sql.Raw): case nameof(Sql.RawRowRef): return(SqlCalls.RawToSql(call, pars)); } } else if (call.Method.DeclaringType == typeof(SqlSelectExtensions)) { switch (call.Method.Name) { case nameof(SqlSelectExtensions.Scalar): return(SqlCalls.ScalarToSql(call, pars)); default: //Si es una llamada a las extensiones y no es la llamada a Scalar entonces es un subquery: //Por ejemplo, uno dentro de una expresión EXISTS o IN return(SqlCalls.SubqueryToSql(call, pars)); } } throw new ArgumentException("No se pudo convertir a SQL la llamada a la función " + call); }
/// <summary> /// Convierte un <see cref="MemberExpression"/> a SQL, tomando en cuenta los aliases de la lista de froms /// y la lógica descrita en <see cref="SqlExprParams"/> /// </summary> static string SingleMemberToSql(SqlExprParams pars, string baseMemberName, string subpath, MemberExpression mem) { var memberName = baseMemberName + subpath; if (pars.FromListNamed) { //Si la lista de FROM tiene aliases, el parametro del select no hace referencia a una tabla, //si no a un objeto de aliases donde cada propiedad es una tabla o un elemento de un JOIN MemberExpression firstExpr = mem; while (firstExpr is MemberExpression sm1 && sm1.Expression is MemberExpression sm2) { firstExpr = sm2; } if (IsFromParam(mem.Expression)) { throw new ArgumentException("No esta soportado obtener una expresión de * en el SingleMemberSql"); } else if (IsFromParam(firstExpr.Expression)) { return(TableRefToSql(firstExpr.Member.Name, memberName)); } else if (IsRawTableRef(firstExpr.Expression, out var raw)) { return(RawTableRefToSql(raw, SqlSelect.ColNameToStr(memberName))); } } else { //Si la lista de FROM no tiene aliases, el parámetro del SELECT hace referencia a la tabla del SELECT Expression firstExpr = mem; while (firstExpr is MemberExpression sm) { firstExpr = sm.Expression; } if (IsFromParam(firstExpr)) { return(TableRefToSql(pars.FromListAlias, memberName)); } else if (IsRawTableRef(firstExpr, out var raw)) { return(RawTableRefToSql(raw, SqlSelect.ColNameToStr(memberName))); } } //Intentamos convertir al Expr a string con el replace: var exprRep = SqlFromList.ReplaceStringAliasMembers(mem.Expression, pars.Replace); if (exprRep != null) { return($"{exprRep}.\"{memberName}\""); } var exprStr = ExprToSql(mem.Expression, pars, false); return($"{exprStr}.\"{memberName}\""); }
/// <summary> /// Convierte un from-list a SQL /// </summary> /// <param name="item"></param> /// <param name="paramName">El nombre del parámetro del SELECT, en caso de que el FROM list no tenga alias, este será el alias del from list</param> /// <returns></returns> public static FromListToStrResult FromListToStr(IFromListItem item, string paramName, bool forceUpperAlias, ParamMode paramMode, SqlParamDic paramDic) { var alias = ExtractJoinAliases(item).SelectMany(x => x).Select(x => new ExprStrRawSql(x.Find, x.Alias)).ToList(); var pars = new SqlExprParams(null, null, false, null, alias, paramMode, paramDic); Func<Expression, string> toSql = ex => SqlExpression.ExprToSql(ex, pars, true); var join = JoinToStr(item, toSql, alias, paramName, forceUpperAlias, paramMode, paramDic); return new FromListToStrResult(join.sql, join.named, !join.named ? paramName : null, alias); }
public static string SubqueryToSql(Expression expr, SqlExprParams pars) { var selectCall = expr; var callSub = SqlFromList.ReplaceSubqueryBody(selectCall, pars.Replace); var subqueryFunc = Expression.Lambda(callSub).Compile(); var subqueryExec = (ISqlSelectHasClause)subqueryFunc.DynamicInvoke(new object[0]); var selectStr = SqlSelect.TabStr(SqlSelect.SelectToString(subqueryExec.Clause, pars.ParamMode, pars.ParamDic)); return($"({Environment.NewLine}{selectStr}{Environment.NewLine})"); }
public static string RawToSql(MethodCallExpression call, SqlExprParams pars) { var arg = call.Arguments[0]; if (!ExprRewrite.ExprEval.TryEvalExpr(arg, out object result)) { throw new ArgumentException($"No se pudo evaluar el contenido del Sql.Raw '{arg}'"); } return((string)result); }
/// <summary> /// Convierte la expresión de proyección de un SELECT a sql, devuelve si la proyección es escalar /// </summary> public static SelectExprToStrResult SelectBodyToStr(Expression body, SqlExprParams pars) { var visitor = new SqlRewriteVisitor(pars); body = visitor.Visit(body); IEnumerable <ValueCol> MemberAssigToSql(Expression expr, MemberInfo prop) { var exprSql = SqlExpression.ExprToSqlStar(expr, pars, false); if (exprSql.star) { return(new[] { new ValueCol(SqlExpression.SqlSubpath.Single(exprSql.sql), null) }); } var asList = exprSql.sql.Select(subpath => new ValueCol(subpath.Sql, MemberToColumnName(prop, subpath)) ); return(asList); } if (body is MemberInitExpression || body is NewExpression) { var exprs = ExtractInitExpr(body) .SelectMany(x => MemberAssigToSql(x.expr, x.mem)) .ToList() ; return(new SelectExprToStrResult(exprs, false)); } var bodySql = SqlExpression.ExprToSqlStar(body, pars, false); if (bodySql.sql.Count > 1) { throw new ArgumentException("Por ahora no esta permitido devolver un ComplexType como el resultado de un SELECT"); } return(new SelectExprToStrResult( new[] { new ValueCol(bodySql.sql.First().Sql, null) }, !bodySql.star)); }
static string WindowToStr(WindowClauses windows, SqlExprParams pars) { var obj = windows.Windows; var props = obj.GetType().GetTypeInfo().DeclaredProperties.Select(x => new NamedWindow(x.Name, (x.GetValue(obj) as ISqlWindow))).ToList(); var noSonWin = props.Where(x => x.Window == null); if (noSonWin.Any()) { var names = string.Join(", ", noSonWin.Select(x => x.Name)); throw new ArgumentException($"The following WINDOW definitions are invalid: '{names }'"); } var ret = props.Select(x => $"\"{x.Name}\" AS ({Environment.NewLine}{TabStr(WindowDefToStr(x.Window, props, pars))}{Environment.NewLine})"); return($"WINDOW {Environment.NewLine}" + TabStr(string.Join($", {Environment.NewLine}", ret))); }
public static string ConditionalToSql(ConditionalExpression expr, SqlExprParams pars) { var b = new StringBuilder(); Expression curr = expr; while (curr is ConditionalExpression cond) { b.Append("WHEN "); b.Append(ExprToSql(cond.Test, pars, false)); b.Append(" THEN "); b.Append(ExprToSql(cond.IfTrue, pars, false)); b.AppendLine(); curr = cond.IfFalse; } b.Append("ELSE "); b.Append(ExprToSql(curr, pars, false)); return(SqlSelect.TabStr($"{Environment.NewLine}CASE{Environment.NewLine}{SqlSelect.TabStr(b.ToString())}{Environment.NewLine}END")); }
/// <summary> /// Converts an UNION clause to string /// </summary> static string UnionToStr(UnionClause clause, SqlExprParams pars) { var b = new StringBuilder(); var typeStr = clause.Type == UnionType.Union ? ( clause.Uniqueness == UnionUniqueness.All ? "UNION ALL" : "UNION" ) : clause.Type == UnionType.Intersect ? "INTERSECT" : clause.Type == UnionType.Except ? "EXCEPT" : throw new ArgumentException(); var queryStr = StatementStr.QueryToStr(clause.Query, pars.ParamMode, pars.ParamDic); b.AppendLine(typeStr); b.AppendLine("("); b.AppendLine(TabStr(queryStr.Sql)); b.Append(")"); return(b.ToString()); }
/// <summary> /// Convierte el cuerpo de la expresión SET a SQL /// </summary> public static string SetToSql(Expression body, ParamMode paramMode, SqlParamDic paramDic, IEnumerable <SqlFromList.ExprStrRawSql> exprAlias ) { var b = new StringBuilder(); var pars = new SqlExprParams(null, null, false, null, exprAlias.ToList(), paramMode, paramDic); //Hacer el rewrite en todo el body: var visitor = new SqlRewriteVisitor(pars); body = visitor.Visit(body); var exprs = SqlSelect .ExtractInitExpr(body) .Select(x => (x.mem, sql: SqlExpression.ExprToSqlStar(x.expr, pars, false))) ; if (exprs.Any(y => y.sql.star)) { throw new ArgumentException("No esta soportado una expresión star '*' en la asignación de los valores de un INSERT"); } var subpaths = exprs.SelectMany(x => x.sql.sql, (parent, child) => (member: parent.mem, subpath: child)); var sets = subpaths.Select(x => ( column: SqlSelect.MemberToColumnName(x.member, x.subpath), value: x.subpath.Sql)) ; var setSql = sets .Select(x => $"{SqlSelect.ColNameToStr(x.column)} = {x.value}" ); var sql = string.Join($", {Environment.NewLine}", setSql); return(sql); }
public static string BetweenToSql(MethodCallExpression call, SqlExprParams pars) { return($"({SqlExpression.ExprToSql(call.Arguments[0], pars, false)} BETWEEN {SqlExpression.ExprToSql(call.Arguments[1], pars, false)} AND {SqlExpression.ExprToSql(call.Arguments[2], pars, false)})"); }
public static string FilterToSql(MethodCallExpression call, SqlExprParams pars) { return($"{SqlExpression.ExprToSql(call.Arguments[0], pars, false)} FILTER (WHERE {SqlExpression.ExprToSql(call.Arguments[1], pars, false)})"); }
public static string LikeToSql(MethodCallExpression call, SqlExprParams pars) { return($"{SqlExpression.ExprToSql(call.Arguments[0], pars, false)} LIKE {SqlExpression.ExprToSql(call.Arguments[1], pars, false)}"); }
public static string OverToSql(MethodCallExpression call, SqlExprParams pars) { return($"{SqlExpression.ExprToSql(call.Arguments[0], pars, false)} OVER {WindowToSql(call.Arguments[1])}"); }
public static string CastToSql(MethodCallExpression call, SqlExprParams pars) { var type = Expression.Lambda <Func <SqlType> >(call.Arguments[1]).Compile()(); return($"CAST ({SqlExpression.ExprToSql(call.Arguments[0], pars, false)} AS {type.Sql})"); }
static string OrderByStr(IReadOnlyList <OrderByExpr> orderBy, SqlExprParams pars) { return($"ORDER BY {string.Join(", ", orderBy.Select(x => OrderByItemStr(x, pars)))}"); }
/// <summary> /// Convierte una expresión a SQL /// </summary> public static (IReadOnlyList <SqlSubpath> sql, bool star) ExprToSqlStar(Expression expr, SqlExprParams pars, bool rewrite) { if (rewrite) { var visitor = new SqlRewriteVisitor(pars); expr = visitor.Visit(expr); } //Es importante primero comprobar la igualdad del parametro, ya que el replace list tiene una entrada para el parametro tambien if (IsFromParam(expr)) { if (pars.FromListNamed) { return(SqlSubpath.FromString($"*"), true); } return(SqlSubpath.FromString(SqlExpression.TableRefToSql(pars.FromListAlias, "*")), true); } var replace = SqlFromList.ReplaceStringAliasMembers(expr, pars.Replace); if (replace != null) { return(SqlSubpath.FromString(replace), false); } if (expr is MemberExpression mem) { return(MemberToSql(mem, pars)); } else if (expr is ConditionalExpression cond) { return(SqlSubpath.FromString(ConditionalToSql(cond, pars)), false); } else if (expr is MethodCallExpression call) { return(SqlSubpath.FromString(CallToSql(call, pars)), false); } else if (expr is MemberInitExpression || expr is NewExpression) { //TODO: Note la similaridad de este código, del InsertToString y del SelectStr //Puede ser que estas 3 funciones sean lógicamente equivalentes y que se puedan unificar var exprs = SqlSelect .ExtractInitExpr(expr) .Select(x => (x.mem, sql: ExprToSqlStar(x.expr, pars, false))); ; if (exprs.Any(y => y.sql.star)) { throw new ArgumentException("No esta soportado una expresión star '*' en una subexpresión"); } var subpaths = exprs .SelectMany(x => x.sql.sql, (parent, child) => (member: parent.mem, subpath: child)) .Select(x => new SqlSubpath(x.subpath.Sql, "_" + x.member.Name + x.subpath.Subpath)) .ToList() ; return(subpaths, false); } throw new ArgumentException("No se pudo convertir a SQL la expresión " + expr.ToString()); }
static (IReadOnlyList <SqlSubpath> sql, bool star) MemberToSql(MemberExpression mem, SqlExprParams pars) { //Si es un parametro: if (AddParam(mem, pars.ParamDic) is var param && param != null) { return(SqlSubpath.FromString(ParamToSql(param, pars.ParamMode)), false); } //Si el tipo es un complex type: var subpaths = SubPaths(mem.Type); string memberName = mem.Member.Name; if (Sql2Sql.Mapper.PathAccessor.IsComplexType(mem.Expression.Type) && mem.Expression is MemberExpression) { var complexName = new List <string>(); MemberExpression curr = mem; complexName.Add(mem.Member.Name); while (Sql2Sql.Mapper.PathAccessor.IsComplexType(curr.Expression.Type) && curr.Expression is MemberExpression m2) { curr = m2; complexName.Add(curr.Member.Name); } complexName.Reverse(); var name = string.Join("_", complexName); mem = curr; memberName = name; } //Miembros de string: if (mem.Expression.Type == typeof(string)) { throw new ArgumentException($"No se pudo convertir a SQL el miembro de 'string' '{mem.Member.Name}'"); } //Estrella: if (pars.FromListNamed && IsFromParam(mem.Expression)) { return(SqlSubpath.FromString($"\"{mem.Member.Name}\".*"), true); } var items = subpaths.Select(x => new SqlSubpath(SingleMemberToSql(pars, memberName, x, mem), x)).ToList(); if (subpaths.Count > 1) { ; } return(items, false); }
static string DistinctOnStr(IReadOnlyList <LambdaExpression> expr, SqlExprParams pars) { return($"DISTINCT ON ({string.Join(", ", expr.Select(x => SqlExpression.ExprToSql(x.Body, pars.ReplaceSelectParams(x.Parameters[0], null), true)))})"); }
public static string ScalarToSql(MethodCallExpression call, SqlExprParams pars) { var selectCall = call.Arguments[0]; return(SubqueryToSql(selectCall, pars)); }
public static string ExprToSql(Expression expr, SqlExprParams pars, bool rewrite) { return(SqlSubpath.Single(ExprToSqlStar(expr, pars, rewrite).sql)); }
/// <summary> /// Convert a window definition to string /// </summary> static string WindowDefToStr(ISqlWindow window, IEnumerable <NamedWindow> others, SqlExprParams pars) { return(string.Join(Environment.NewLine, WindowDefToStrList(window, others, pars))); }
static string WhereStr(LambdaExpression where, SqlExprParams p) { var pars = p.ReplaceSelectParams(where.Parameters[0], where.Parameters[1]); return($"WHERE {SqlExpression.ExprToSql(where.Body, pars, true)}"); }
static string PartitionByStr(IReadOnlyList <PartitionByExpr> groups, SqlExprParams pars) { var exprs = string.Join(", ", groups.Select(x => SqlExpression.ExprToSql(x.Expr.Body, pars.ReplaceSelectParams(x.Expr.Parameters[0], null), true))); return($"PARTITION BY {exprs}"); }
/// <summary> /// Conver a window definition to an string list /// </summary> static IReadOnlyList <string> WindowDefToStrList(ISqlWindow window, IEnumerable <NamedWindow> others, SqlExprParams pars) { var current = window.Current; var previous = window.Previous; List <string> retItems = new List <string>();; if (previous != null) { var existingWindow = others.Where(x => x.Window == previous).FirstOrDefault(); //The previous window is an existing one: if (existingWindow != null) { retItems.Add(existingWindow.Name); } else { retItems.AddRange(WindowDefToStrList(window.Previous, others, pars)); } } if (current.PartitionBy?.Any() == true) { retItems.Add(PartitionByStr(current.PartitionBy, pars)); } if (current.OrderBy?.Any() == true) { retItems.Add(OrderByStr(current.OrderBy, pars)); } if (current.Frame != null) { retItems.Add(WindowFrameClauseStr(current.Frame)); } return(retItems); }
/// <summary> /// Convierte una cláusula de SELECT a string /// </summary> public static SelectToStrResult SelectToStringScalar(SelectClause clause, ParamMode paramMode, SqlParamDic paramDic) { var selectExpr = clause.Select; var paramName = selectExpr.Parameters[0].Name; var from = SqlFromList.FromListToStr(clause.From, paramName, true, paramMode, paramDic); var selectParam = selectExpr.Parameters[0]; var aliases = from.Aliases.ToList(); if (!from.Named) { //Agregar el parametro del select como el nombre del fromList, esto para que se sustituya correctamente en los subqueries aliases.Add(new SqlFromList.ExprStrRawSql(selectParam, TableNameToStr(from.Alias))); } var pars = new SqlExprParams(selectParam, selectExpr.Parameters[1], from.Named, from.Alias, aliases, paramMode, paramDic); var select = SelectBodyToStr(selectExpr.Body, pars); //The query converted to string, before the PostUnion var query = new StringBuilder(); query.Append("SELECT"); if (clause.DistinctType != SelectType.All) { query.Append(" "); switch (clause.DistinctType) { case SelectType.Distinct: query.Append("DISTINCT"); break; case SelectType.DistinctOn: query.Append(DistinctOnStr(clause.DistinctOn, pars)); break; } } query.AppendLine(); query.AppendLine($"{TabStr(SelectExprToStr(select.Values))}"); query.AppendLine(from.Sql); if (clause.Where != null) { query.AppendLine(WhereStr(clause.Where, pars)); } if (clause.GroupBy?.Any() == true) { query.AppendLine(GroupByStr(clause.GroupBy, pars)); } if (clause.Window != null) { query.AppendLine(WindowToStr(clause.Window, pars)); } if (clause.OrderBy?.Any() == true) { query.AppendLine(OrderByStr(clause.OrderBy, pars)); } if (clause.Limit != null) { query.AppendLine("LIMIT " + clause.Limit); } //Delete the last line jump, note that the lenght of the line-jump //depends on the operating system query.Length = query.Length - Environment.NewLine.Length; StringBuilder ret; if (clause.Unions?.Any() == true) { ret = new StringBuilder(); //Put the query whole inside parenthesis ret.AppendLine("("); ret.AppendLine(TabStr(query.ToString())); ret.AppendLine(")"); foreach (var union in clause.Unions) { ret.AppendLine(UnionToStr(union, pars)); } //Remove the last lineJump: ret.Length = ret.Length - Environment.NewLine.Length; } else { ret = query; } return(new SelectToStrResult(ret.ToString(), select.Values.Select(x => x.Column).ToList(), select.Scalar)); }