using System;
using System.Collections.Generic;
using System.Linq.Expressions;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using Signum.Utilities;
using Signum.Utilities.ExpressionTrees;
using Signum.Utilities.DataStructures;
using Signum.Entities;
using Signum.Utilities.Reflection;
using Signum.Engine.Properties;
using System.Data;
using Signum.Engine.Maps;
using Signum.Entities.DynamicQuery;
namespace Signum.Engine.Linq
{
/// <summary>
/// QueryFormatter is a visitor that converts an bound expression tree into SQL query text
/// </summary>
internal class QueryFormatter : DbExpressionVisitor
{
StringBuilder sb = new StringBuilder();
int indent = 2;
int depth;
ParameterExpression row = Expression.Parameter(typeof(IProjectionRow), "row");
static PropertyInfo miReader = ReflectionTools.GetPropertyInfo((IProjectionRow row) => row.Reader);
class ParameterInfo
{
internal MethodCallExpression Expression;
internal string Name;
}
Dictionary<Expression, ParameterInfo> parameterExpressions = new Dictionary<Expression, ParameterInfo>();
int parameter = 0;
public string GetNextParamAlias()
{
return "@p" + (parameter++);
}
MethodInfo miUnsafeCreateParameter = ReflectionTools.GetMethodInfo(() => SqlParameterBuilder.UnsafeCreateParameter(null, SqlDbType.BigInt, false, null));
ParameterInfo CreateParameter(Expression value)
{
string name = GetNextParamAlias();
bool nullable = value.Type.IsClass || value.Type.IsNullable();
Type clrType = value.Type.UnNullify();
if (clrType.IsEnum)
clrType = typeof(int);
SqlDbType sqlDbType = Schema.Current.Settings.TypeValues.TryGetS(clrType) ?? SqlDbType.Variant;
Expression valExpression = value.Type.IsNullable() ?
Expression.Coalesce(Expression.Convert(value, typeof(object)), Expression.Constant(DBNull.Value)) :
(Expression)Expression.Convert(value, typeof(object));
return new ParameterInfo
{
Expression = Expression.Call(null, miUnsafeCreateParameter,
Expression.Constant(name),
Expression.Constant(sqlDbType),
Expression.Constant(nullable),
valExpression),
Name = name
};
}
private QueryFormatter() { }
static internal string Format(Expression expression, out Expression<Func<SqlParameter[]>> getParameters)
{
QueryFormatter qf = new QueryFormatter();
qf.Visit(expression);
getParameters = Expression.Lambda<Func<SqlParameter[]>>(
Expression.NewArrayInit(typeof(SqlParameter), qf.parameterExpressions.Values.Select(pi=>pi.Expression).ToArray()));
return qf.sb.ToString();
}
protected enum Indentation
{
Same,
Inner,
Outer
}
internal int IndentationWidth
{
get { return this.indent; }
set { this.indent = value; }
}
private void AppendNewLine(Indentation style)
{
sb.AppendLine();
this.Indent(style);
for (int i = 0, n = this.depth * this.indent; i < n; i++)
{
sb.Append(" ");
}
}
private void Indent(Indentation style)
{
if (style == Indentation.Inner)
{
this.depth++;
}
else if (style == Indentation.Outer)
{
this.depth--;
System.Diagnostics.Debug.Assert(this.depth >= 0);
}
}
protected override Expression VisitUnary(UnaryExpression u)
{
switch (u.NodeType)
{
case ExpressionType.Not:
sb.Append(" NOT ");
this.Visit(u.Operand);
break;
case ExpressionType.Negate:
sb.Append(" - ");
this.Visit(u.Operand);
break;
case ExpressionType.UnaryPlus:
sb.Append(" + ");
this.Visit(u.Operand);
break;
case ExpressionType.Convert:
//Las unicas conversiones explicitas son a Binary y desde datetime a numeros
this.Visit(u.Operand);
break;
default:
throw new NotSupportedException(string.Format("The unary perator {0} is not supported", u.NodeType));
}
return u;
}
bool IsNull(Expression exp)
{
return exp.NodeType == ExpressionType.Constant && ((ConstantExpression)exp).Value == null;
}
protected override Expression VisitBinary(BinaryExpression b)
{
if (b.NodeType == ExpressionType.Coalesce)
{
sb.Append("IsNull(");
Visit(b.Left);
sb.Append(",");
Visit(b.Right);
sb.Append(")");
}
else if (b.NodeType == ExpressionType.Equal || b.NodeType == ExpressionType.NotEqual)
{
sb.Append("(");
Visit(b.Left);
sb.Append(b.NodeType == ExpressionType.Equal ? " = " : " <> ");
Visit(b.Right);
sb.Append(")");
}
else
{
sb.Append("(");
this.Visit(b.Left);
switch (b.NodeType)
{
case ExpressionType.And:
case ExpressionType.AndAlso:
sb.Append(b.Type.UnNullify() == typeof(bool) ? " AND " : " & ");
break;
case ExpressionType.Or:
case ExpressionType.OrElse:
sb.Append(b.Type.UnNullify() == typeof(bool) ? " OR " : " | ");
break;
case ExpressionType.ExclusiveOr:
sb.Append(" ^ ");
break;
case ExpressionType.LessThan:
sb.Append(" < ");
break;
case ExpressionType.LessThanOrEqual:
sb.Append(" <= ");
break;
case ExpressionType.GreaterThan:
sb.Append(" > ");
break;
case ExpressionType.GreaterThanOrEqual:
sb.Append(" >= ");
break;
case ExpressionType.Add:
case ExpressionType.AddChecked:
sb.Append(" + ");
break;
case ExpressionType.Subtract:
case ExpressionType.SubtractChecked:
sb.Append(" - ");
break;
case ExpressionType.Multiply:
case ExpressionType.MultiplyChecked:
sb.Append(" * ");
break;
case ExpressionType.Divide:
sb.Append(" / ");
break;
case ExpressionType.Modulo:
sb.Append(" % ");
break;
default:
throw new NotSupportedException(string.Format("The binary operator {0} is not supported", b.NodeType));
}
this.Visit(b.Right);
sb.Append(")");
}
return b;
}
protected override Expression VisitRowNumber(RowNumberExpression rowNumber)
{
sb.Append("ROW_NUMBER() OVER(ORDER BY ");
for (int i = 0, n = rowNumber.OrderBy.Count; i < n; i++)
{
OrderExpression exp = rowNumber.OrderBy[i];
if (i > 0)
sb.Append(", ");
this.Visit(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
sb.Append(" DESC");
}
sb.Append(")");
return rowNumber;
}
protected override Expression VisitCase(CaseExpression cex)
{
AppendNewLine(Indentation.Inner);
sb.Append("CASE");
AppendNewLine(Indentation.Inner);
for (int i = 0, n = cex.Whens.Count; i < n; i++)
{
When when = cex.Whens[i];
sb.Append("WHEN ");
Visit(when.Condition);
sb.Append(" THEN ");
Visit(when.Value);
AppendNewLine(Indentation.Same);
}
if (cex.DefaultValue != null)
{
sb.Append("ELSE ");
Visit(cex.DefaultValue);
AppendNewLine(Indentation.Outer);
}
sb.Append("END");
AppendNewLine(Indentation.Outer);
return cex;
}
protected override Expression VisitLike(LikeExpression like)
{
Visit(like.Expression);
sb.Append(" LIKE ");
Visit(like.Pattern);
return like;
}
protected override Expression VisitExists(ExistsExpression exists)
{
sb.Append("EXISTS(");
this.Visit(exists.Select);
sb.Append(")");
return exists;
}
protected override Expression VisitScalar(ScalarExpression exists)
{
sb.Append("(");
this.Visit(exists.Select);
sb.Append(")");
return exists;
}
protected override Expression VisitIsNull(IsNullExpression isNull)
{
sb.Append("(");
this.Visit(isNull.Expression);
sb.Append(") IS NULL");
return isNull;
}
protected override Expression VisitIsNotNull(IsNotNullExpression isNotNull)
{
sb.Append("(");
this.Visit(isNotNull.Expression);
sb.Append(") IS NOT NULL");
return isNotNull;
}
protected override Expression VisitIn(InExpression inExpression)
{
Visit(inExpression.Expression);
sb.Append(" IN (");
if (inExpression.Select == null)
{
bool any = false;
foreach (var obj in inExpression.Values)
{
VisitConstant(Expression.Constant(obj));
sb.Append(",");
any = true;
}
if (any)
sb.Remove(sb.Length - 1, 1);
}
else
{
Visit(inExpression.Select);
}
sb.Append(" )");
return inExpression;
}
protected override Expression VisitSqlEnum(SqlEnumExpression sqlEnum)
{
sb.Append(sqlEnum.Value);
return sqlEnum;
}
protected override Expression VisitSqlCast(SqlCastExpression castExpr)
{
sb.Append("CAST(");
Visit(castExpr.Expression);
sb.Append(" as ");
sb.Append(castExpr.SqlDbType.ToString().ToUpperInvariant());
sb.Append(")");
return castExpr;
}
internal static bool IsSupported(Type type)
{
return type == typeof(Guid) || Type.GetTypeCode(type) != TypeCode.Object;
}
protected override Expression VisitConstant(ConstantExpression c)
{
if (c.Value == null)
sb.Append("NULL");
else
{
if (!IsSupported(c.Value.GetType()))
throw new NotSupportedException(string.Format("The constant for {0} is not supported", c.Value));
var pi = parameterExpressions.GetOrCreate(c, ()=> this.CreateParameter(c));
sb.Append(pi.Name);
}
return c;
}
protected override Expression VisitSqlConstant(SqlConstantExpression c)
{
if (c.Value == null)
sb.Append("NULL");
else
{
if (!IsSupported(c.Value.GetType()))
throw new NotSupportedException(string.Format("The constant for {0} is not supported", c.Value));
if (c.Value.Equals(true))
sb.Append("1");
else if (c.Value.Equals(false))
sb.Append("0");
else
sb.Append(c.ToString());
}
return c;
}
protected override Expression VisitColumn(ColumnExpression column)
{
sb.Append(column.Alias.Name.SqlScape());
sb.Append(".");
sb.Append(column.Name.SqlScape());
return column;
}
protected override Expression VisitSelect(SelectExpression select)
{
bool isFirst = sb.Length == 0;
if (!isFirst)
{
AppendNewLine(Indentation.Inner);
sb.Append("(");
}
sb.Append("SELECT ");
if (select.Distinct)
sb.Append("DISTINCT ");
if (select.Top != null)
{
sb.Append("TOP (");
Visit(select.Top);
sb.Append(") ");
}
if (select.Columns.Count == 0)
sb.Append("0 as Dummy");
else
for (int i = 0, n = select.Columns.Count; i < n; i++)
{
ColumnDeclaration column = select.Columns[i];
if (i > 0)
sb.Append(", ");
AppendColumn(column);
}
if (select.From != null)
{
this.AppendNewLine(Indentation.Same);
sb.Append("FROM ");
this.VisitSource(select.From);
}
if (select.Where != null)
{
this.AppendNewLine(Indentation.Same);
sb.Append("WHERE ");
this.Visit(select.Where);
}
if (select.GroupBy != null && select.GroupBy.Count > 0)
{
this.AppendNewLine(Indentation.Same);
sb.Append("GROUP BY ");
for (int i = 0, n = select.GroupBy.Count; i < n; i++)
{
Expression exp = select.GroupBy[i];
if (i > 0)
{
sb.Append(", ");
}
this.Visit(exp);
}
}
if (select.OrderBy != null && select.OrderBy.Count > 0)
{
this.AppendNewLine(Indentation.Same);
sb.Append("ORDER BY ");
for (int i = 0, n = select.OrderBy.Count; i < n; i++)
{
OrderExpression exp = select.OrderBy[i];
if (i > 0)
{
sb.Append(", ");
}
this.Visit(exp.Expression);
if (exp.OrderType != OrderType.Ascending)
{
sb.Append(" DESC");
}
}
}
if (!isFirst)
{
sb.Append(")");
AppendNewLine(Indentation.Outer);
}
return select;
}
Dictionary<AggregateFunction, string> dic = new Dictionary<AggregateFunction, string>
{
{AggregateFunction.Average, "AVG"},
{AggregateFunction.Count, "COUNT"},
{AggregateFunction.Max, "MAX"},
{AggregateFunction.Min, "MIN"},
{AggregateFunction.Sum, "SUM"}
};
protected override Expression VisitAggregate(AggregateExpression aggregate)
{
sb.Append(dic[aggregate.AggregateFunction]);
sb.Append("(");
if (aggregate.Source == null)
sb.Append("*");
else
Visit(aggregate.Source);
sb.Append(")");
return aggregate;
}
protected override Expression VisitSqlFunction(SqlFunctionExpression sqlFunction)
{
sb.Append(sqlFunction.SqlFunction);
sb.Append("(");
for (int i = 0, n = sqlFunction.Arguments.Count; i < n; i++)
{
Expression exp = sqlFunction.Arguments[i];
if (i > 0)
sb.Append(", ");
this.Visit(exp);
}
sb.Append(")");
return sqlFunction;
}
private void AppendColumn(ColumnDeclaration column)
{
ColumnExpression c = this.Visit(column.Expression) as ColumnExpression;
if (column.Name.HasText() && (c == null || c.Name != column.Name))
{
sb.Append(" AS ");
sb.Append(column.Name.SqlScape());
}
}
protected override Expression VisitTable(TableExpression table)
{
sb.Append(table.Name.SqlScape());
return table;
}
protected override SourceExpression VisitSource(SourceExpression source)
{
if (source is SourceWithAliasExpression)
{
if (source is TableExpression)
Visit(source);
else
{
sb.Append("(");
Visit(source);
sb.Append(")");
}
sb.Append(" AS ");
sb.Append(((SourceWithAliasExpression)source).Alias.Name.SqlScape());
}
else
this.VisitJoin((JoinExpression)source);
return source;
}
protected override Expression VisitJoin(JoinExpression join)
{
this.VisitSource(join.Left);
this.AppendNewLine(Indentation.Same);
switch (join.JoinType)
{
case JoinType.CrossJoin:
sb.Append("CROSS JOIN ");
break;
case JoinType.InnerJoin:
sb.Append("INNER JOIN ");
break;
case JoinType.LeftOuterJoin:
case JoinType.SingleRowLeftOuterJoin:
sb.Append("LEFT OUTER JOIN ");
break;
case JoinType.RightOuterJoin:
sb.Append("RIGHT OUTER JOIN ");
break;
case JoinType.FullOuterJoin:
sb.Append("FULL OUTER JOIN ");
break;
case JoinType.CrossApply:
sb.Append("CROSS APPLY ");
break;
case JoinType.OuterApply:
sb.Append("OUTER APPLY ");
break;
}
bool needsMoreParenthesis = (join.JoinType == JoinType.CrossApply || join.JoinType == JoinType.OuterApply) && join.Right is JoinExpression;
if (needsMoreParenthesis)
sb.Append("(");
this.VisitSource(join.Right);
if (needsMoreParenthesis)
sb.Append(")");
if (join.Condition != null)
{
this.AppendNewLine(Indentation.Inner);
sb.Append("ON ");
this.Visit(join.Condition);
this.Indent(Indentation.Outer);
}
return join;
}
protected override Expression VisitDelete(DeleteExpression delete)
{
sb.Append("DELETE ");
sb.Append(delete.Table.Name);
this.AppendNewLine(Indentation.Same);
sb.Append("FROM ");
VisitSource(delete.Source);
if (delete.Where != null)
{
this.AppendNewLine(Indentation.Same);
sb.Append("WHERE ");
Visit(delete.Where);
}
return delete;
}
protected override Expression VisitUpdate(UpdateExpression update)
{
sb.Append("UPDATE ");
sb.Append(update.Table.Name);
sb.Append(" SET");
this.AppendNewLine(Indentation.Inner);
for (int i = 0, n = update.Assigments.Count; i < n; i++)
{
ColumnAssignment assignment= update.Assigments[i];
if (i > 0)
{
sb.Append(",");
this.AppendNewLine(Indentation.Same);
}
sb.Append(assignment.Column);
sb.Append(" = ");
this.Visit(assignment.Expression);
}
this.AppendNewLine(Indentation.Outer);
sb.Append("FROM ");
VisitSource(update.Source);
if (update.Where != null)
{
this.AppendNewLine(Indentation.Same);
sb.Append("WHERE ");
Visit(update.Where);
}
return update;
}
protected override Expression VisitSelectRowCount(SelectRowCountExpression src)
{
sb.Append("SELECT @@rowcount");
return src;
}
protected override Expression VisitCommandAggregate(CommandAggregateExpression cea)
{
for (int i = 0, n = cea.Commands.Count; i < n; i++)
{
CommandExpression command = cea.Commands[i];
if (i > 0)
{
sb.Append(";");
this.AppendNewLine(Indentation.Same);
this.AppendNewLine(Indentation.Same);
}
this.Visit(command);
}
return cea;
}
}
}