/// <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);
}