public void ToSqlRuleContains()
{
var nombres = new[] { "rafa", "hola" };
Expression <Func <Cliente, string[]> > selectBody = x => Sql.Record(nombres);
var pars = new SqlExprParams(selectBody.Parameters[0], null, false, "cli", new SqlFromList.ExprStrRawSql[0], ParamMode.None, new SqlParamDic());
var rules =
SqlFunctions.AtomRawRule(pars)
.Concat(
SqlFunctions.ExprParamsRules(pars)
)
.Concat(new[] {
DefaultRewrite.StringFormat
})
.Concat(SqlFunctions.stringCalls)
.Concat(SqlFunctions.sqlCalls)
.Concat(SqlFunctions.rawAtom)
.ToList();
var ret = ApplyRules(selectBody, rules);
var rawBody = ((MethodCallExpression)((LambdaExpression)ret).Body).Arguments[0];
ExprEval.TryEvalExpr <string>(rawBody, out var rawStr);
var expected = "('rafa', 'hola')";
Assert.AreEqual(expected, rawStr);
}
public void ToSqlRuleContainsifCond()
{
string[] nombres = null;
Expression <Func <Cliente, bool> > selectBody = x => SqlExpr.IfCond.Invoke(nombres.Any(), nombres.Contains(x.Nombre));
var pars = new SqlExprParams(selectBody.Parameters[0], null, false, "cli", new SqlFromList.ExprStrRawSql[0], ParamMode.None, new SqlParamDic());
var visitor = new SqlRewriteVisitor(pars);
{
nombres = new[] { "rafa", "hola" };
var ret = visitor.Visit(selectBody);
var rawBody = ((MethodCallExpression)((LambdaExpression)ret).Body).Arguments[0];
ExprEval.TryEvalExpr <string>(rawBody, out var rawStr);
var expected = "(\"cli\".\"Nombre\" IN ('rafa', 'hola'))";
Assert.AreEqual(expected, rawStr);
}
{
nombres = new string[0];
var ret = visitor.Visit(selectBody);
var rawBody = ((MethodCallExpression)((LambdaExpression)ret).Body).Arguments[0];
ExprEval.TryEvalExpr <string>(rawBody, out var rawStr);
var expected = "True";
Assert.AreEqual(expected, rawStr);
}
}
/// <summary>
///
/// </summary>
static string OnConflictDoUpdate(OnConflictDoUpdateClause doUpdate, ParamMode paramMode, SqlParamDic paramDic, string origTableName)
{
var b = new StringBuilder();
b.AppendLine("DO UPDATE");
b.AppendLine("SET");
var exprAlias = new[]
{
new SqlFromList.ExprStrRawSql(doUpdate.Set.Parameters[0], "EXCLUDED"),
new SqlFromList.ExprStrRawSql(doUpdate.Set.Parameters[1], $"\"{origTableName}\""),
};
var setSql = SqlUpdate.SetToSql(doUpdate.Set.Body, paramMode, paramDic, exprAlias);
b.Append(SqlSelect.TabStr(setSql));
if (doUpdate.Where != null)
{
b.AppendLine();
var pars = new SqlExprParams(null, null, false, "", new SqlFromList.ExprStrRawSql[0], paramMode, paramDic);
var whereSql = SqlExpression.ExprToSql(doUpdate.Where.Body, pars, true);
b.Append(whereSql);
}
return(b.ToString());
}
public void ToSqlRule3()
{
Expression <Func <Cliente, bool> > selectBody = x => x.Nombre.Contains("Hola");
var pars = new SqlExprParams(selectBody.Parameters[0], null, false, "cli", new SqlFromList.ExprStrRawSql[0], ParamMode.None, new SqlParamDic());
var rules =
SqlFunctions.rawAtom.Concat(
SqlFunctions.ExprParamsRules(pars)
)
.Concat(new[] {
DefaultRewrite.StringFormat
})
.Concat(SqlFunctions.stringCalls)
.Concat(SqlFunctions.AtomRawRule(pars))
.ToList();
var ret = ApplyRules(selectBody, rules);
var rawBody = ((MethodCallExpression)((LambdaExpression)ret).Body).Arguments[0];
ExprEval.TryEvalExpr <string>(rawBody, out var rawStr);
var expected = "(\"cli\".\"Nombre\" LIKE '%' || 'Hola' || '%')";
Assert.AreEqual(expected, rawStr);
}
public void MultipleIfCond()
{
var filtro = new Uruz.FiltroFacturas
{
FechaInicio = new DateTime(2019, 01, 26)
};
Expression <Func <Uruz.FacturaDTO, bool> > expr = x =>
SqlExpr.IfCond.Invoke(filtro.FechaInicio != null, x.FechaCreacion >= filtro.FechaInicio) &&
SqlExpr.IfCond.Invoke(filtro.FechaFinal != null, x.FechaCreacion <= filtro.FechaFinal) &&
SqlExpr.IfCond.Invoke(filtro.FechaPagoInicio != null, x.FechaPago >= filtro.FechaPagoInicio) &&
SqlExpr.IfCond.Invoke(filtro.FechaPagoFinal != null, x.FechaPago <= filtro.FechaPagoFinal)
;
var pars = new SqlExprParams(expr.Parameters[0], null, false, "fac", new SqlFromList.ExprStrRawSql[0], ParamMode.EntityFramework, new SqlParamDic());
var visitor = new SqlRewriteVisitor(pars);
var ret = visitor.Visit(expr);
var apps = RewriteVisitor.applications;
var expected = "x => Raw(\"(\"fac\".\"FechaCreacion\" >= @FechaInicio)\")";
var actual = ret.ToString();
Assert.AreEqual(expected, actual);
}
public static IEnumerable <RewriteRule> AtomInvokeParam(SqlExprParams pars)
{
var invokeRule = RewriteRule.Create(
"atomInvokeParam",
() => RewriteSpecial.Call <RewriteTypes.C2>(null, "Invoke"),
null,
null,
(match, expr, visit) =>
{
var exprCall = (MethodCallExpression)expr;
var origArgs = exprCall.Arguments;
//Aplicar la transformación del from a los parametros, esto es importante porque el from
//en algunos casos se usa sólo para especificar el tipo en un método generico pero su valor
//no es importante.
var fromArgs = origArgs
.Select(x => Rewriter.RecApplyRules(x, ExprParamsRules(pars), ExcludeFromRewrite))
.ToList()
;
var arg = fromArgs[0];
var args = fromArgs;
var atomArg = Expression.Call(typeof(RewriteSpecial), "Atom", new[] { arg.Type }, arg);
var atomArgs = new[] { atomArg }.Concat(args.Skip(1)).ToList();
var retCall = Expression.Call(exprCall.Object, exprCall.Method, atomArgs);
return(retCall);
});
return(new[] { invokeRule });
}
/// <summary>
/// Convierte la cláusula RETURNING a SQL
/// </summary>
static (string sql, IReadOnlyList <string> cols) ReturningToString(LambdaExpression returning, ParamMode paramMode, SqlParamDic paramDic, string tableName)
{
var pars = new SqlExprParams(returning.Parameters[0], null, false, tableName, new SqlFromList.ExprStrRawSql[0], paramMode, paramDic);
var select = SqlSelect.SelectBodyToStr(returning.Body, pars);
var sql = $"RETURNING {Environment.NewLine}{SqlSelect.TabStr(SqlSelect.SelectExprToStr(select.Values))}";
var cols = select.Values.Select(x => x.Column).ToList();
return(sql, cols);
}
static void ConvertWhere(StringBuilder b, DeleteClause clause, ParamMode paramMode, SqlParamDic paramDic)
{
var whereParms = clause.Where.Parameters;
var tableParam = whereParms[0];
var usingParam = whereParms[1];
var replace = new SqlFromList.ExprStrRawSql[0];
var pars = new SqlExprParams(tableParam, null, false, null, replace, paramMode, paramDic);
var whereSql = SqlExpression.ExprToSql(clause.Where.Body, pars, true);
b.Append("WHERE ");
b.Append(whereSql);
}
public SqlRewriteVisitor(SqlExprParams pars)
{
var exprParamRules = SqlFunctions.ExprParamsRules(pars);
//The order of the rule passes is important to the performance and correctness!
rules = new List <IEnumerable <RewriteRule> >();
//Primero quita los invokes
rules.Add(
new[]
{
DefaultRewrite.InvokeRule(exprParamRules)
});
rules.Add(DefaultRewrite.BooleanSimplify);
rules.Add(
exprParamRules
);
rules.Add(
SqlFunctions.rawAtom
.Concat(SqlFunctions.AtomInvokeParam(pars))
.Concat(
new[]
{
SqlConst.constToSqlRule,
DefaultRewrite.StringFormat,
SqlFunctions.rawCallRule
}
)
.Concat(SqlOperators.eqNullRule)
.Concat(SqlOperators.nullableRules)
.Concat(SqlOperators.unaryRules)
.Concat(SqlOperators.binaryRules)
.Concat(SqlStar.starRules)
.Concat(SqlOperators.compareTo)
.Concat(SqlFunctions.stringCalls)
.Concat(SqlFunctions.subqueryExprs)
.Concat(SqlFunctions.sqlCalls)
.Concat(SqlFunctions.AtomRawRule(pars))
.ToList()
)
;
}
public void ToSqlStringConcat()
{
Expression <Func <Uruz.Factura, string> > nombreFactura = x => x.Serie + "-" + x.Folio;
Expression <Func <Uruz.Factura, string> > selectBody = x => nombreFactura.Invoke(x);
var pars = new SqlExprParams(selectBody.Parameters[0], null, false, "cli", new SqlFromList.ExprStrRawSql[0], ParamMode.None, new SqlParamDic());
var visitor = new SqlRewriteVisitor(pars);
var ret = visitor.Visit(selectBody);
var rawBody = ((MethodCallExpression)((LambdaExpression)ret).Body).Arguments[0];
ExprEval.TryEvalExpr <string>(rawBody, out var rawStr);
var expected = @"((""cli"".""Serie"" || '-') || ""cli"".""Folio"")";
Assert.AreEqual(expected, rawStr);
}
public void ToSqlRuleContainsEmpty()
{
var nombres = new string[0];
Expression <Func <Cliente, bool> > selectBody = x => nombres.Contains(x.Nombre);
var pars = new SqlExprParams(selectBody.Parameters[0], null, false, "cli", new SqlFromList.ExprStrRawSql[0], ParamMode.None, new SqlParamDic());
var visitor = new SqlRewriteVisitor(pars);
{
nombres = new string[0];
var ret = visitor.Visit(selectBody);
var rawBody = ((MethodCallExpression)((LambdaExpression)ret).Body).Arguments[0];
ExprEval.TryEvalExpr <string>(rawBody, out var rawStr);
var expected = "False";
Assert.AreEqual(expected, rawStr);
}
}
/// <summary>
/// Convierte a string la parte del VALUE de un INSERT
/// </summary>
static string InsertValueToString(InsertClause clause, ParamMode paramMode, SqlParamDic paramDic)
{
var b = new StringBuilder();
//Note que aquí el fromAlias no afecta ya se se usan directamente los nombres de las columnas
//y no se puede referenciar a la tabla de origen en los VALUES
var pars = new SqlExprParams(null, null, false, null, new SqlFromList.ExprStrRawSql[0], paramMode, paramDic);
//Hacer el rewrite en todo el body:
var visitor = new SqlRewriteVisitor(pars);
var body = visitor.Visit(clause.Value);
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));
//Nombres de las columnas del INSERT
var columns = subpaths
.Select(x => SqlSelect.MemberToColumnName(x.member, x.subpath))
.Select(SqlSelect.ColNameToStr);
;
//Valores:
var values = subpaths.Select(x => x.subpath.Sql);
//Texto de las columnas:
b.Append("(");
b.Append(string.Join(", ", columns));
b.AppendLine(")");
//Texto de los vaues:
b.Append("VALUES (");
b.Append(string.Join(", ", values));
b.Append(")");
return(b.ToString());
}
public void CompareToTest()
{
Expression <Func <Cliente, bool> > select = (cli) => cli.Nombre.CompareTo(cli.Apellido) >= 0;
var pars = new SqlExprParams(select.Parameters[0], null, false, "cli", new SqlFromList.ExprStrRawSql[0], ParamMode.None, new SqlParamDic());
var rules = SqlFunctions.rawAtom.Concat(
SqlOperators.compareTo
)
.Concat(
SqlFunctions.AtomRawRule(pars)
)
;
var ret = (LambdaExpression)ApplyRules(select, rules);
var raw = ExprEval.EvalExpr <string>(((MethodCallExpression)ret.Body).Arguments[0]).Value;
var expected = "(\"cli\".\"Nombre\" >= \"cli\".\"Apellido\")";
Assert.AreEqual(expected, raw);
}
/// <summary>
/// Convierte la cláusura ON CONFLICT
/// </summary>
static string OnConflict(OnConflictClause onConf, ParamMode paramMode, SqlParamDic paramDic, string tableName)
{
var b = new StringBuilder();
//fromAlias es null ya que en la expresion de indice de ON CONFLICT no se permiten
//los identificadores de las tablas, sólo se permiten los nombres tal cual de las columnas
var pars = new SqlExprParams(null, null, false, null, new SqlFromList.ExprStrRawSql[0], paramMode, paramDic);
var indexExpr = onConf
.IndexExpressions
.Select(x => SqlExpression.ExprToSql(x.Body, pars.ReplaceSelectParams(x.Parameters[0], null), true))
;
b.Append("ON CONFLICT ");
if (indexExpr.Any())
{
b.Append("(");
b.Append(string.Join(", ", indexExpr));
b.Append(") ");
}
if (onConf.Where != null)
{
var whereSql = SqlExpression.ExprToSql(onConf.Where.Body, pars.ReplaceSelectParams(onConf.Where.Parameters[0], null), true);
b.Append("WHERE ");
b.Append(whereSql);
}
if (onConf.DoUpdate == null)
{
//Si DoUpdate es null se considera que es DO NOTHING
b.Append("DO NOTHING");
}
else
{
b.Append(OnConflictDoUpdate(onConf.DoUpdate, paramMode, paramDic, tableName));
}
return(b.ToString());
}
public void MultipleIfCond2()
{
var filtro = new Uruz.FiltroFacturas
{
IdCliente = 10,
};
Expression <Func <Uruz.FacturaDTO, bool> > expr = x =>
SqlExpr.EqualsNullable.Invoke(x.IdCliente, filtro.IdCliente) &&
SqlExpr.EqualsNullable.Invoke(x.IdSucursal, filtro.IdSucursal)
;
var pars = new SqlExprParams(expr.Parameters[0], null, false, "fac", new SqlFromList.ExprStrRawSql[0], ParamMode.EntityFramework, new SqlParamDic());
var visitor = new SqlRewriteVisitor(pars);
var ret = visitor.Visit(expr);
var apps = RewriteVisitor.applications;
var expected = "x => Raw(\"(\"fac\".\"IdCliente\" = @IdCliente)\")";
var actual = ret.ToString();
Assert.AreEqual(expected, actual);
}
public static IEnumerable <RewriteRule> ExprParamsRules(SqlExprParams pars)
{
var ret = new List <RewriteRule>();
//Puede ser null el param en caso de que todo sea por sustituciones del replace
if (pars.Param != null)
{
//Es posible que el fromParam este envuelto en un Convert, por ejemplo cuando se usan propiedades de una interfaz
ret.Add(
RewriteRule.Create(
"convertFromParam",
(RewriteTypes.C1 p) => RewriteSpecial.Operator <RewriteTypes.C1, RewriteTypes.C2>(p, ExpressionType.Convert),
(RewriteTypes.C1 p) => Sql.FromParam <RewriteTypes.C2>(),
(match, expr) => match.Args[0] == pars.Param
));
ret.Add(
new RewriteRule(
"fromParam",
Expression.Lambda(pars.Param), Expression.Lambda(Expression.Call(typeof(Sql), nameof(Sql.FromParam), new[] { pars.Param.Type })), null, null)
);
}
return(ret);
}
/// <summary>
/// La regla que se aplica a las llamadas Atom(Raw(x)), lo que hace es convertir las llamadas ToSql del Atom(Raw(x))
/// </summary>
public static IEnumerable <RewriteRule> AtomRawRule(SqlExprParams pars)
{
var deferredToSqlRule = RewriteRule.Create(
"deferredToSql",
(RewriteTypes.C1 x) => SqlFunctions.ToSql <RewriteTypes.C1>(x),
null,
null,
(match, expr, visit) => Expression.Call(
typeof(SqlExpression),
nameof(SqlExpression.ExprToSql),
new Type[0],
Expression.Constant(match.Args[0]),
Expression.Constant(pars),
Expression.Constant(true)));
var toSqlRule = RewriteRule.Create(
"toSql",
() => RewriteSpecial.Call <string>(typeof(SqlFunctions), nameof(ToSql)),
null,
null,
(match, expr, visit) => Expression.Constant(SqlExpression.ExprToSql(((MethodCallExpression)expr).Arguments[0], pars, true)));
var toSelectBodySqlRule = RewriteRule.Create(
"toSelectBodySqlRule",
() => RewriteSpecial.Call <string>(typeof(SqlFunctions), nameof(ToSelectBodySql)),
null,
null,
(match, expr, visit) =>
Expression.Constant(
SqlSelect.SelectExprToStr(SqlSelect.SelectBodyToStr(((MethodCallExpression)expr).Arguments[0], pars).Values)
)
);
var windowToSqlRule = RewriteRule.Create(
"windowToSql",
(ISqlWindow a) => WindowToSql(a),
null,
null,
(match, expr, visit) => Expression.Constant(SqlCalls.WindowToSql(match.Args[0]))
);
var toSqlRules = new[]
{
toSelectBodySqlRule,
toSqlRule,
windowToSqlRule
};
Func <Expression, Expression> applySqlRule = (Expression ex) => new RewriteVisitor(toSqlRules, ExcludeFromRewrite).Visit(ex);
var atomRawRule = RewriteRule.Create(
"executeAtomRaw",
(string x) => RewriteSpecial.Atom(Sql.Raw <RewriteTypes.C1>(RewriteSpecial.NotConstant(x))),
null,
null,
(match, expr, visit) =>
{
var arg = match.Args[0];
var applySql = applySqlRule(arg);
if (applySql == arg)
{
return(expr);
}
var type = match.Types[typeof(RewriteTypes.C1)];
var value = ExprEval.EvalExpr <string>(applySql).Value;
var ret = Expression.Call(typeof(RewriteSpecial), nameof(RewriteSpecial.Atom), new[] { type },
Expression.Call(typeof(Sql), nameof(Sql.Raw), new[] { type }, Expression.Constant(value))
);
return(ret);
});
return(new[] { atomRawRule });
}
