/// <summary> /// Devuelve el numero del parametro si es que es un parametro, si no, devuelve null /// </summary> static SqlParamItem AddParam(MemberExpression mem, SqlParamDic dic) { var path = IsParam(mem, dic); if (path != null) { return(dic.AddParam(path.Value.target, path.Value.members)); } return(null); }
/// <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 SqlExprParams(ParameterExpression param, ParameterExpression window, bool fromListNamed, string fromListAlias, IReadOnlyList <ExprStrRawSql> replace, ParamMode paramMode, SqlParamDic paramDic) { if (replace.Count > 0) { ; } if (fromListNamed && fromListAlias != null) { throw new ArgumentException($"'{nameof(fromListAlias)}' debe de ser null cuando '{nameof(fromListNamed)}' = true"); } if (fromListAlias == "") { throw new ArgumentException($"'{nameof(fromListAlias)}' no debe de ser una cadena vacía"); } Param = param; Window = window; FromListNamed = fromListNamed; FromListAlias = fromListAlias; Replace = replace; ParamMode = paramMode; ParamDic = paramDic; }
public static SqlExprParams Empty(ParamMode mode, SqlParamDic paramDic) => new SqlExprParams(null, null, false, null, new ExprStrRawSql[0], mode, paramDic);
/// <summary> /// Devuelve el el target y el path si es que la expresión es un parámetro, si no, devuelve null /// </summary> public static (object target, IReadOnlyList <MemberInfo> members)? IsParam(MemberExpression mem, SqlParamDic dic) { var first = mem; var members = new List <MemberInfo>(); while (first.Expression is MemberExpression leftMem) { members.Add(first.Member); first = leftMem; } members.Add(first.Member); //Poner primero los miembros de hasta la izquierda: members.Reverse(); if (first.Expression is ConstantExpression cons) { if (!cons.Type.Name.StartsWith("<>c__DisplayClass")) { return(null); } var target = cons.Value; if (typeof(IEnumerable).IsAssignableFrom(mem.Type) && mem.Type != typeof(string)) { throw new ArgumentException($"No se pueden parametrizar la expresión '{mem}' ya que es una colección"); } return(target, members); } return(null); }
/// <summary> /// Convierte una lista de FROM a SQL. /// Devuelve si la lista de FROMs tiene aliases /// </summary> /// <param name="item">Elemento que representa ya sea a un FROM o a una lista de JOINS</param> /// <param name="toSql">Convierte una expresión a SQL</param> /// <param name="paramSql">Alias del parámetro en SQL</param> /// <returns></returns> static (string sql, bool named) JoinToStrAlias(IFromListItem item, Func<Expression, string> toSql, IReadOnlyList<ExprStrRawSql> replaceMembers, string paramSql, bool forceUpperAlias, ParamMode paramMode, SqlParamDic paramDic) { if (item is SqlJoin join) { var currentAlias = toSql(join.Map.Parameters[1]); var currentOnStr = toSql(join.On.Body); var leftParam = join.Map.Parameters[0]; var leftAlias = ReplaceStringAliasMembers(leftParam, replaceMembers); var rightSubsNoRep = ReplaceSubqueryLambda(join.Right, leftParam, replaceMembers); //Visitar el lado derecho del JOIN: //TODO: Hacer una función para visitar a las expresiones de lado derecho del JOIN var rightSubs = SqlRewriteVisitor.VisitFromItem(rightSubsNoRep); var rightFunc = Expression.Lambda(rightSubs).Compile(); var rightExec = (IFromListItemTarget)rightFunc.DynamicInvoke(new object[0]); var latStr = join.Lateral ? "LATERAL " : ""; var typeStr = join.Type == JoinType.Inner ? "" : join.Type == JoinType.Left ? "LEFT " : join.Type == JoinType.Right ? "RIGHT " : join.Type == JoinType.Outter ? "OUTTER " : join.Type == JoinType.Cross ? "CROSS " : throw new ArgumentException("Join type " + join.Type + " invalido"); var right = $"{typeStr}JOIN {latStr}{SubqueryParenthesis(StatementStr.StatementToString(rightExec, paramMode, paramDic))} {currentAlias} ON {currentOnStr}"; var leftStr = JoinToStrAlias(join.Left, toSql, replaceMembers, leftAlias, true, paramMode, paramDic); return (leftStr.sql + Environment.NewLine + right, true); } else if (item is ISqlFrom from) { var fromIt = StatementStr.StatementToString(from.Target, paramMode, paramDic); return ($"FROM {SubqueryParenthesis(fromIt)} {(((fromIt is QueryToStrResult) || forceUpperAlias) ? paramSql : "")}", false); } else if (item is FromListAlias alias) { return JoinToStrAlias(alias.From, toSql, replaceMembers, paramSql, forceUpperAlias, paramMode, paramDic); } throw new ArgumentException("El from-item debe de ser un JOIN, FROM o Alias()"); }
static (string sql, bool named) JoinToStr(IFromListItem item, Func<Expression, string> toSql, IReadOnlyList<ExprStrRawSql> replaceMembers, string paramName, bool forceUpperAlias, ParamMode paramMode, SqlParamDic paramDic) { var paramAlias = SqlSelect.TableNameToStr(paramName); return JoinToStrAlias(item, toSql, replaceMembers, paramAlias, forceUpperAlias, paramMode, paramDic); }
/// <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); }
/// <summary> /// Convierte un <see cref="IFromListItemTarget"/> a string, devuelve true si el elemento requiered de un alias /// </summary> static StatementToStrResult FromListTargetToStr(IFromListItemTarget item, ParamMode paramMode, SqlParamDic paramDic) { if (item is ISqlQuery query) { return(QueryToStr(query, paramMode, paramDic)); } else if (item is SqlTable table) { return(new TableToStrResult(TableToString(table))); } else if (item is ISqlTableRefRaw raw) { return(new TableToStrResult(raw.Raw)); } throw new ArgumentException("El from item target debe de ser una tabla o un select"); }
/// <summary> /// Converts an <see cref="ISqlQuery"/> to string /// </summary> public static QueryToStrResult QueryToStr(ISqlQuery item, ParamMode paramMode, SqlParamDic paramDic) { if (item is ISqlSelectHasClause select) { var str = SqlSelect.SelectToStringScalar(select.Clause, paramMode, paramDic); return(new QueryColsToStrResult(str.Sql, str.Columns)); } else if (item is ISqlWithSelect withSelect) { var withSql = SqlWith.WithToSql(withSelect.With.With, withSelect.With.Param, paramMode, paramDic); var subquerySql = FromListTargetToStr(withSelect.Query, paramMode, paramDic); var ret = $"{withSql}{Environment.NewLine}{subquerySql.Sql}"; if (subquerySql is QueryColsToStrResult subCols) { return(new QueryColsToStrResult(ret, subCols.Columns)); } return(new QueryToStrResult(ret)); } else if (item is ISqlSelectRaw subq) { return(new QueryToStrResult(SqlSelect.DetabStr(subq.Raw))); } throw new ArgumentException("El from item target debe de ser una tabla o un select"); }
/// <summary> /// Convierte un INSERT a string /// </summary> static StatementToStrResult InsertToStr(ISqlInsert item, ParamMode paramMode, SqlParamDic paramDic) { if (item is ISqlInsertHasClause clau) { return(SqlInsertConverter.InsertToString(clau.Clause, paramMode, paramDic)); } throw new ArgumentException(nameof(item)); }
/// <summary> /// Convierte una sentencia de SQL a string. /// Puede convertir SELECT (fluent o RAW), INSERT y TABLE /// </summary> public static StatementToStrResult StatementToString(ISqlStatement item, ParamMode paramMode, SqlParamDic paramDic) { switch (item) { case ISqlInsert insert: return(InsertToStr(insert, paramMode, paramDic)); case ISqlQuery query: return(QueryToStr(query, paramMode, paramDic)); case IFromListItemTarget fromItem: return(FromListTargetToStr(fromItem, paramMode, paramDic)); default: throw new ArgumentException(nameof(item)); } }
/// <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)); }
/// <summary> /// Convierte una cláusula de SELECT a string /// </summary> public static string SelectToString(SelectClause clause, ParamMode paramMode, SqlParamDic paramDic) { return(SelectToStringScalar(clause, paramMode, paramDic).Sql); }