Beispiel #1
0
 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" : "")}");
 }
Beispiel #2
0
        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);
        }
Beispiel #3
0
        /// <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}\"");
        }
Beispiel #4
0
        /// <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);
        }
Beispiel #5
0
        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})");
        }
Beispiel #6
0
        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);
        }
Beispiel #7
0
        /// <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));
        }
Beispiel #8
0
        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)));
        }
Beispiel #9
0
        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"));
        }
Beispiel #10
0
        /// <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());
        }
Beispiel #11
0
        /// <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);
        }
Beispiel #12
0
 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)})");
 }
Beispiel #13
0
 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)})");
 }
Beispiel #14
0
 public static string LikeToSql(MethodCallExpression call, SqlExprParams pars)
 {
     return($"{SqlExpression.ExprToSql(call.Arguments[0], pars, false)} LIKE {SqlExpression.ExprToSql(call.Arguments[1], pars, false)}");
 }
Beispiel #15
0
 public static string OverToSql(MethodCallExpression call, SqlExprParams pars)
 {
     return($"{SqlExpression.ExprToSql(call.Arguments[0], pars, false)} OVER {WindowToSql(call.Arguments[1])}");
 }
Beispiel #16
0
        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})");
        }
Beispiel #17
0
 static string OrderByStr(IReadOnlyList <OrderByExpr> orderBy, SqlExprParams pars)
 {
     return($"ORDER BY {string.Join(", ", orderBy.Select(x => OrderByItemStr(x, pars)))}");
 }
Beispiel #18
0
        /// <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());
        }
Beispiel #19
0
        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);
        }
Beispiel #20
0
 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)))})");
 }
Beispiel #21
0
        public static string ScalarToSql(MethodCallExpression call, SqlExprParams pars)
        {
            var selectCall = call.Arguments[0];

            return(SubqueryToSql(selectCall, pars));
        }
Beispiel #22
0
 public static string ExprToSql(Expression expr, SqlExprParams pars, bool rewrite)
 {
     return(SqlSubpath.Single(ExprToSqlStar(expr, pars, rewrite).sql));
 }
Beispiel #23
0
 /// <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)));
 }
Beispiel #24
0
        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)}");
        }
Beispiel #25
0
        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}");
        }
Beispiel #26
0
        /// <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);
        }
Beispiel #27
0
        /// <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));
        }