Inheritance: SqlExpression
示例#1
0
 private FromTable(string tableName, SqlQueryExpression query, string alias)
 {
     Name       = tableName;
     SubQuery   = query;
     Alias      = alias;
     IsSubQuery = query != null;
 }
        public static void PrepareFromSqlQuery()
        {
            var sqlQuery = new SqlQuery("SELECT * FROM table1 WHERE a = ? AND b = ?", SqlQueryParameterNaming.Marker);

            sqlQuery.Parameters.Add(new SqlQueryParameter(PrimitiveTypes.Integer(), (SqlNumber)2));
            sqlQuery.Parameters.Add(new SqlQueryParameter(PrimitiveTypes.Integer(), (SqlNumber)1023));

            var preparer = sqlQuery.ExpressionPreparer;

            var queryExp = new SqlQueryExpression();

            queryExp.From.Table(new ObjectName("table1"));
            queryExp.Items.Add(SqlExpression.Reference(new ObjectName("*")));
            queryExp.Where = SqlExpression.And(
                SqlExpression.Equal(SqlExpression.Reference(new ObjectName("a")), SqlExpression.Parameter()),
                SqlExpression.Equal(SqlExpression.Reference(new ObjectName("b")), SqlExpression.Parameter()));

            var prepared = queryExp.Prepare(preparer);

            Assert.IsType <SqlQueryExpression>(prepared);

            var sb = new SqlStringBuilder();

            sb.AppendLine("SELECT *");
            sb.AppendLine("  FROM table1");
            sb.Append("  WHERE a = 2 AND b = 1023");

            var expectString = sb.ToString();

            Assert.Equal(expectString, prepared.ToString());
        }
示例#3
0
 private FromTable(string tableName, SqlQueryExpression query, string alias)
 {
     Name = tableName;
     SubQuery = query;
     Alias = alias;
     IsSubQuery = query != null;
 }
        public override SqlExpression VisitQuery(SqlQueryExpression query)
        {
            var selectColumns = Prepare(query.SelectColumns, preparer);
            var newExpression = new SqlQueryExpression(selectColumns);

            var where = query.WhereExpression;
            if (where != null)
            {
                where = where.Prepare(preparer);
            }

            newExpression.WhereExpression = where;

            var having = query.HavingExpression;

            if (having != null)
            {
                having = having.Prepare(preparer);
            }

            newExpression.HavingExpression = having;

            var from = query.FromClause;

            if (from != null)
            {
                from = (FromClause)((IPreparable)from).Prepare(preparer);
            }

            newExpression.FromClause = from;

            var nextComposite = query.NextComposite;

            if (nextComposite != null)
            {
                nextComposite = (SqlQueryExpression)nextComposite.Prepare(preparer);
            }

            var groupBy = query.GroupBy;

            if (groupBy != null)
            {
                var newGroupBy = new List <SqlExpression>();
                foreach (var groupExpression in groupBy)
                {
                    var newGroupByExp = groupExpression.Prepare(preparer);
                    newGroupBy.Add(newGroupByExp);
                }

                newExpression.GroupBy = newGroupBy;
            }

            newExpression.NextComposite     = nextComposite;
            newExpression.CompositeFunction = query.CompositeFunction;
            newExpression.IsCompositeAll    = query.IsCompositeAll;

            newExpression.Distinct = query.Distinct;

            return(newExpression);
        }
示例#5
0
        protected override SqlStatement PrepareStatement(IRequest context)
        {
            var tableName = context.Access().ResolveTableName(TableName);
            if (!context.Access().TableExists(tableName))
                throw new ObjectNotFoundException(tableName);

            var queryExpression = new SqlQueryExpression(new[]{SelectColumn.Glob("*") });
            queryExpression.FromClause.AddTable(tableName.FullName);
            queryExpression.WhereExpression = WherExpression;

            var queryFrom = QueryExpressionFrom.Create(context, queryExpression);
            var queryPlan = context.Query.Context.QueryPlanner().PlanQuery(new QueryInfo(context, queryExpression));

            var columns = new List<SqlAssignExpression>();
            foreach (var assignment in Assignments) {
                var columnName = ObjectName.Parse(assignment.ColumnName);

                var refName = queryFrom.ResolveReference(columnName);
                var expression = assignment.Expression.Prepare(queryFrom.ExpressionPreparer);

                var assign = SqlExpression.Assign(SqlExpression.Reference(refName), expression);
                columns.Add(assign);
            }

            return new Prepared(tableName, queryPlan, columns.ToArray(), Limit);
        }
示例#6
0
        public SelectStatement(SqlQueryExpression queryExpression, IEnumerable<SortColumn> orderBy)
        {
            if (queryExpression == null)
                throw new ArgumentNullException("queryExpression");

            QueryExpression = queryExpression;
            OrderBy = orderBy;
        }
示例#7
0
        public ISelectStatementBuilder Query(SqlQueryExpression expression)
        {
            if (expression == null)
                throw new ArgumentNullException("expression");

            queryExpression = expression;
            return this;
        }
 public SqlQueryExpressionSource(SqlQueryExpression query, string alias)
     : this(null, query, alias)
 {
     if (query == null)
     {
         throw new ArgumentNullException(nameof(query));
     }
 }
        public static void DeclareInsensitiveCursor(this IRequest context, string cursorName, SqlQueryExpression query, bool withScroll)
        {
            var flags = CursorFlags.Insensitive;
            if (withScroll)
                flags |= CursorFlags.Scroll;

            context.DeclareCursor(cursorName, flags, query);
        }
示例#10
0
 /// <summary>
 /// A table that is a sub-query and given an aliased name.
 /// </summary>
 /// <param name="query"></param>
 /// <param name="tableAlias"></param>
 public FromTable(SqlQueryExpression query, string tableAlias)
     : this(null, query, tableAlias)
 {
     if (query == null)
     {
         throw new ArgumentNullException("query");
     }
 }
示例#11
0
        public IQueryPlanNode PlanQuery(IQueryContext context, SqlQueryExpression queryExpression, IEnumerable<SortColumn> sortColumns)
        {
            var queryFrom = QueryExpressionFrom.Create(context, queryExpression);
            var orderBy = new List<SortColumn>();
            if (sortColumns != null)
                orderBy.AddRange(sortColumns);

            return PlanQuery(context, queryExpression, queryFrom, orderBy);
        }
示例#12
0
        internal FromTableSubQuerySource(bool caseInsensitive, string uniqueKey, SqlQueryExpression queryExpression,
			QueryExpressionFrom fromSet, ObjectName alias)
        {
            UniqueName = uniqueKey;
            QueryExpression = queryExpression;
            QueryFrom = fromSet;
            AliasName = alias;
            IgnoreCase = caseInsensitive;
        }
示例#13
0
        internal JoinPart(JoinType joinType, SqlQueryExpression subQuery, SqlExpression onExpression)
        {
            if (subQuery == null)
                throw new ArgumentNullException("subQuery");

            OnExpression = onExpression;
            JoinType = joinType;
            SubQuery = subQuery;
        }
示例#14
0
        public SelectIntoStatement(SqlQueryExpression queryExpression, SqlExpression reference)
        {
            if (queryExpression == null)
                throw new ArgumentNullException("queryExpression");
            if (reference == null)
                throw new ArgumentNullException("reference");

            QueryExpression = queryExpression;
            Reference = reference;
        }
示例#15
0
        public CreateViewStatement(string viewName, IEnumerable<string> columnNames, SqlQueryExpression queryExpression)
        {
            if (viewName == null)
                throw new ArgumentNullException("viewName");
            if (queryExpression == null)
                throw new ArgumentNullException("queryExpression");

            ViewName = viewName;
            ColumnNames = columnNames;
            QueryExpression = queryExpression;
        }
示例#16
0
        public ViewInfo(TableInfo tableInfo, SqlQueryExpression queryExpression, IQueryPlanNode queryPlan)
        {
            if (tableInfo == null)
                throw new ArgumentNullException("tableInfo");
            if (queryExpression == null)
                throw new ArgumentNullException("queryExpression");

            TableInfo = tableInfo;
            QueryExpression = queryExpression;
            QueryPlan = queryPlan;
        }
示例#17
0
        public void DeclareInsensitiveCursor()
        {
            var query = new SqlQueryExpression(new []{new SelectColumn(SqlExpression.Constant("*")) });
            query.FromClause = new FromClause();
            query.FromClause.AddTable("test_table");

            Assert.DoesNotThrow(() => Query.DeclareInsensitiveCursor("c1", query));

            var exists = Query.ObjectExists(DbObjectType.Cursor, new ObjectName("c1"));
            Assert.IsTrue(exists);
        }
        public InsertSelectStatement(ObjectName tableName, IEnumerable<string> columnNames, SqlQueryExpression queryExpression)
        {
            if (tableName == null)
                throw new ArgumentNullException("tableName");
            if (queryExpression == null)
                throw new ArgumentNullException("queryExpression");

            TableName = tableName;
            ColumnNames = columnNames;
            QueryExpression = queryExpression;
        }
示例#19
0
        internal JoinPart(JoinType joinType, SqlQueryExpression subQuery, SqlExpression onExpression)
        {
            if (subQuery == null)
            {
                throw new ArgumentNullException("subQuery");
            }

            OnExpression = onExpression;
            JoinType     = joinType;
            SubQuery     = subQuery;
        }
示例#20
0
        public QueryInfo(IRequest request, SqlQueryExpression expression, IEnumerable<SortColumn> sortColumns, QueryLimit limit)
        {
            if (expression == null)
                throw new ArgumentNullException("expression");
            if (request == null)
                throw new ArgumentNullException("request");

            Expression = expression;
            Request = request;
            SortColumns = sortColumns;
            Limit = limit;
        }
示例#21
0
        private Field SelectScalar(string column)
        {
            var query = new SqlQueryExpression(new [] {new SelectColumn(SqlExpression.Reference(new ObjectName(column))) });
            query.FromClause.AddTable("seq1");

            var result = AdminQuery.Select(query);
            var row = result.FirstOrDefault();
            if (row == null)
                return Field.Null();

            return row.GetValue(0);
        }
        public DeclareCursorStatement(string cursorName, IEnumerable<CursorParameter> parameters, CursorFlags flags, SqlQueryExpression queryExpression)
        {
            if (queryExpression == null)
                throw new ArgumentNullException("queryExpression");
            if (String.IsNullOrEmpty(cursorName))
                throw new ArgumentNullException("cursorName");

            CursorName = cursorName;
            Parameters = parameters;
            Flags = flags;
            QueryExpression = queryExpression;
        }
示例#23
0
        public static void VisitQuery()
        {
            var exp = new SqlQueryExpression();

            exp.Items.Add(SqlExpression.Reference(new ObjectName("a")));
            exp.From.Table(new ObjectName("b"));
            exp.From.Join(JoinType.Left,
                          SqlExpression.Equal(SqlExpression.Reference(ObjectName.Parse("b.id")), SqlExpression.Reference(ObjectName.Parse("c.b_id"))));
            exp.From.Table(new ObjectName("c"));
            exp.Where = SqlExpression.GreaterThanOrEqual(SqlExpression.Reference(new ObjectName("a")), SqlExpression.Constant(SqlObject.BigInt(22)));

            Visit(exp);
        }
示例#24
0
        public override SqlExpression VisitQuery(SqlQueryExpression query)
        {
            if (!rootQuery)
            {
                builder.Append("(");
            }

            builder.Append("SELECT ");
            if (query.Distinct)
            {
                builder.Append("DISTINCT ");
            }

            PrintQueryColumns(query.SelectColumns);
            builder.Append(" ");

            PrintFromClause(query.FromClause);

            if (query.WhereExpression != null)
            {
                builder.Append(" WHERE ");
                Visit(query.WhereExpression);
            }

            if (query.GroupBy != null)
            {
                builder.Append(" GROUP BY ");
                VisitExpressionList(query.GroupBy.ToArray());

                if (query.HavingExpression != null)
                {
                    builder.Append(" HVAING ");
                    Visit(query.HavingExpression);
                }
            }

            if (query.GroupMax != null)
            {
                builder.Append(" GROUP MAX ");
                builder.Append(query.GroupMax.FullName);
            }

            // TODO: COMPOSITE ...

            if (!rootQuery)
            {
                builder.Append(")");
            }

            return(query);
        }
示例#25
0
        /// <summary>
        ///
        /// </summary>
        /// <param name="query"></param>
        /// <returns></returns>
        public virtual SqlExpression VisitQuery(SqlQueryExpression query)
        {
            var selectColumns = new List <SelectColumn>();

            foreach (var column in query.SelectColumns)
            {
                var newColumn = new SelectColumn(Visit(column.Expression), column.Alias);
                newColumn.InternalName = column.InternalName;
                newColumn.ResolvedName = column.ResolvedName;
                selectColumns.Add(newColumn);
            }

            var newQuery = new SqlQueryExpression(selectColumns);

            if (query.FromClause != null)
            {
                newQuery.FromClause = VisitFromClause(query.FromClause);
            }

            if (query.WhereExpression != null)
            {
                newQuery.WhereExpression = Visit(query.WhereExpression);
            }
            if (query.HavingExpression != null)
            {
                newQuery.HavingExpression = Visit(query.HavingExpression);
            }

            if (query.GroupBy != null)
            {
                newQuery.GroupBy = VisitExpressionList(newQuery.GroupBy.ToArray());
            }

            newQuery.GroupMax = query.GroupMax;
            newQuery.Distinct = query.Distinct;

            if (query.NextComposite != null)
            {
                var visitedComposite = Visit(query.NextComposite);
                if (visitedComposite.ExpressionType == SqlExpressionType.Query)
                {
                    newQuery.NextComposite = (SqlQueryExpression)visitedComposite;
                }

                newQuery.CompositeFunction = query.CompositeFunction;
                newQuery.IsCompositeAll    = query.IsCompositeAll;
            }

            return(query);
        }
        public static void MakeNewNaturalJoinQuery()
        {
            var query = new SqlQueryExpression();

            query.Items.Add(SqlExpression.Reference(ObjectName.Parse("a.*")));
            query.Items.Add(SqlExpression.Reference(ObjectName.Parse("b.*")));
            query.From.Table(ObjectName.Parse("table1"), "a");
            query.From.Table(ObjectName.Parse("table2"), "b");

            var expected = new SqlStringBuilder();

            expected.AppendLine("SELECT a.*, b.*");
            expected.Append("  FROM table1 AS a, table2 AS b");
            Assert.Equal(expected.ToString(), query.ToString());
        }
示例#27
0
        public virtual SqlExpression VisitQuery(SqlQueryExpression expression)
        {
            var items = VisitQueryItems(expression.Items);

            var query = new SqlQueryExpression {
                Distinct = expression.Distinct
            };

            if (items != null)
            {
                foreach (var item in items)
                {
                    query.Items.Add(item);
                }
            }

            var from = expression.From;

            if (from != null)
            {
                from = VisitQueryFrom(from);
            }

            query.From = from;

            var where = expression.Where;
            if (where != null)
            {
                where = Visit(where);
            }

            query.Where = where;

            var having = expression.Having;

            if (having != null)
            {
                having = Visit(having);
            }

            query.Having = having;

            query.GroupBy  = VisitExpressionList(expression.GroupBy);
            query.GroupMax = expression.GroupMax;

            return(query);
        }
        public static void CreateNewSimpleQuery()
        {
            var query = new SqlQueryExpression();

            query.Items.Add(SqlExpression.Reference(ObjectName.Parse("a.*")));
            query.Items.Add(SqlExpression.Reference(ObjectName.Parse("b")));
            query.From.Table(ObjectName.Parse("tab1"));


            var expected = new SqlStringBuilder();

            expected.AppendLine("SELECT a.*, b");
            expected.Append("  FROM tab1");

            Assert.False(query.From.IsEmpty);
            Assert.Equal(expected.ToString(), query.ToString());
        }
示例#29
0
        public override SqlExpression VisitQuery(SqlQueryExpression query)
        {
            if (context.Request == null)
            {
                throw new ExpressionEvaluateException("A query expression is required to evaluate a query.");
            }

            try {
                var planner = context.Request.Context.QueryPlanner();
                var plan    = planner.PlanQuery(new QueryInfo(context.Request, query));
                return(SqlExpression.Constant(new DataObject(new QueryType(), new SqlQueryObject(plan))));
            } catch (ExpressionEvaluateException) {
                throw;
            } catch (Exception ex) {
                throw new ExpressionEvaluateException("Evaluation of a QUERY expression could not generate a plan.", ex);
            }
        }
        public static int DeleteFrom(this IQuery context, ObjectName tableName, SqlExpression expression, int limit)
        {
            if (expression is SqlQueryExpression)
                return context.DeleteFrom(tableName, (SqlQueryExpression)expression, limit);

            var table = context.GetMutableTable(tableName);
            if (table == null)
                throw new ObjectNotFoundException(tableName);

            var queryExpression = new SqlQueryExpression(new List<SelectColumn> { SelectColumn.Glob("*") });
            queryExpression.FromClause.AddTable(tableName.Name);
            queryExpression.WhereExpression = expression;

            var planExpression = queryExpression.Evaluate(context, null);
            var plan = (SqlQueryObject)((SqlConstantExpression)planExpression).Value.Value;
            var deleteSet = plan.QueryPlan.Evaluate(context);

            return context.DeleteFrom(tableName, deleteSet, limit);
        }
示例#31
0
        IStatement IPreparableStatement.Prepare(IRequest request)
        {
            var tableName = request.Query.ResolveTableName(TableName);

            if (!request.Query.TableExists(tableName))
                throw new ObjectNotFoundException(tableName);

            var queryExp = new SqlQueryExpression(new SelectColumn[] {SelectColumn.Glob("*") });
            queryExp.FromClause.AddTable(tableName.FullName);
            queryExp.WhereExpression = WhereExpression;

            var queryInfo = new QueryInfo(request, queryExp);
            if (Limit > 0)
                queryInfo.Limit = new QueryLimit(Limit);

            var queryPlan = request.Query.Context.QueryPlanner().PlanQuery(queryInfo);

            return new Prepared(tableName, queryPlan);
        }
        public static void MakeNewInnerJoinQuery()
        {
            var query = new SqlQueryExpression();

            query.Items.Add(SqlExpression.Reference(ObjectName.Parse("a.*")));
            query.Items.Add(SqlExpression.Reference(ObjectName.Parse("b.*")));
            query.From.Table(ObjectName.Parse("table1"), "a");
            query.From.Join(JoinType.Inner,
                            SqlExpression.Equal(SqlExpression.Reference(ObjectName.Parse("a.id")),
                                                SqlExpression.Reference(ObjectName.Parse("b.a_id"))));
            query.From.Table(ObjectName.Parse("table2"), "b");

            var expected = new SqlStringBuilder();

            expected.AppendLine("SELECT a.*, b.*");
            expected.Append("  FROM table1 AS a INNER JOIN table2 AS b ON a.id = b.a_id");

            Assert.Equal(expected.ToString(), query.ToString());
        }
示例#33
0
        public void ExecuteSelectAll()
        {
            var expression =
                new SqlQueryExpression(new[] {new SelectColumn(SqlExpression.Reference(new ObjectName("first_name")))});
            expression.FromClause.AddTable("test_table");

            Field result = null;
            Assert.DoesNotThrow(() => result = expression.EvaluateToConstant(AdminQuery, null));
            Assert.IsNotNull(result);
            Assert.IsInstanceOf<QueryType>(result.Type);
            Assert.IsNotNull(result.Value);
            Assert.IsInstanceOf<SqlQueryObject>(result.Value);

            ITable queryResult = null;

            Assert.DoesNotThrow(() => queryResult = ((SqlQueryObject) result.Value).QueryPlan.Evaluate(AdminQuery));
            Assert.IsNotNull(queryResult);
            Assert.AreEqual(3, queryResult.RowCount);
        }
示例#34
0
        public override SqlExpression VisitQuery(SqlQueryExpression query)
        {
            var selectColumns = Prepare(query.SelectColumns, preparer);
            var newExpression = new SqlQueryExpression(selectColumns);

            var where = query.WhereExpression;
            if (where != null)
            {
                where = where.Prepare(preparer);
            }

            newExpression.WhereExpression = where;

            var having = query.HavingExpression;

            if (having != null)
            {
                having = having.Prepare(preparer);
            }

            newExpression.HavingExpression = having;

            var from = query.FromClause;

            if (from != null)
            {
                from = (FromClause)((IPreparable)from).Prepare(preparer);
            }

            query.FromClause = from;

            var nextComposite = query.NextComposite;

            if (nextComposite != null)
            {
                nextComposite = (SqlQueryExpression)nextComposite.Prepare(preparer);
            }

            query.NextComposite = nextComposite;

            return(newExpression);
        }
        public void ExecuteSelectAll()
        {
            var expression =
                new SqlQueryExpression(new[] { new SelectColumn(SqlExpression.Reference(new ObjectName("first_name"))) });

            expression.FromClause.AddTable("test_table");

            Field result = null;

            Assert.DoesNotThrow(() => result = expression.EvaluateToConstant(AdminQuery, null));
            Assert.IsNotNull(result);
            Assert.IsInstanceOf <QueryType>(result.Type);
            Assert.IsNotNull(result.Value);
            Assert.IsInstanceOf <SqlQueryObject>(result.Value);

            ITable queryResult = null;

            Assert.DoesNotThrow(() => queryResult = ((SqlQueryObject)result.Value).QueryPlan.Evaluate(AdminQuery));
            Assert.IsNotNull(queryResult);
            Assert.AreEqual(3, queryResult.RowCount);
        }
        public static void CreateNewQuerySource()
        {
            var fromTable = ObjectName.Parse("table1");
            var query     = new SqlQueryExpression();

            query.AllItems = true;
            query.From.Table(fromTable);
            var source = new SqlQueryExpressionSource(query, "a");

            Assert.True(source.IsQuery);
            Assert.False(source.IsTable);
            Assert.True(source.IsAliased);
            Assert.Equal("a", source.Alias);
            Assert.True(source.Query.AllItems);

            var expected = new SqlStringBuilder();

            expected.AppendLine("(SELECT *");
            expected.Append("  FROM table1) AS a");

            Assert.Equal(expected.ToString(), source.ToString());
        }
        public static int DeleteFrom(this IQuery context, ObjectName tableName, SqlQueryExpression query, int limit)
        {
            IQueryPlanNode plan;

            try {
                var planValue = query.EvaluateToConstant(context, null);
                if (planValue == null)
                    throw new InvalidOperationException();

                if (!(planValue.Type is QueryType))
                    throw new InvalidOperationException();

                plan = ((SqlQueryObject)planValue.Value).QueryPlan;
            } catch (QueryException) {
                throw;
            } catch (SecurityException) {
                throw;
            } catch (Exception ex) {
                throw new InvalidOperationException(String.Format("Could not delete from table '{0}': unable to form the delete set.", tableName), ex);
            }

            var deleteSet = plan.Evaluate(context);
            return context.DeleteFrom(tableName, deleteSet, limit);
        }
 public SqlQueryExpressionComposite(CompositeFunction function, bool all, SqlQueryExpression expression)
 {
     Function   = function;
     All        = all;
     Expression = expression;
 }
示例#39
0
 public static void DeclareCursor(this IRequest context, string cursorName, CursorFlags flags, SqlQueryExpression query)
 {
     context.DeclareCursor(new CursorInfo(cursorName, flags, query));
 }
示例#40
0
 public static void DeclareInsensitiveCursor(this IRequest context, string cursorName, SqlQueryExpression query)
 {
     DeclareInsensitiveCursor(context, cursorName, query, false);
 }
示例#41
0
 /// <summary>
 /// Adds a sub-query expression as source of the query.
 /// </summary>
 /// <param name="subQuery">The sub-query expression as source of the query.</param>
 /// <seealso cref="AddSubQuery(string, SqlQueryExpression)"/>
 public void AddSubQuery(SqlQueryExpression subQuery)
 {
     AddSubQuery(null, subQuery);
 }
示例#42
0
 public static void DeclareCursor(this IRequest context, string cursorName, SqlQueryExpression query)
 {
     DeclareCursor(context, cursorName, (CursorFlags)0, query);
 }
示例#43
0
 /// <summary>
 /// Adds a sub-query expression as source of the query.
 /// </summary>
 /// <param name="subQuery">The sub-query expression as source of the query.</param>
 /// <seealso cref="AddSubQuery(string, SqlQueryExpression)"/>
 public void AddSubQuery(SqlQueryExpression subQuery)
 {
     AddSubQuery(null, subQuery);
 }
示例#44
0
 /// <summary>
 /// Adds a sub-query expression as source of the query.
 /// </summary>
 /// <param name="alias">The unique alias name of the expression within the clause.</param>
 /// <param name="subQuery">The sub-query expression as source of the query.</param>
 /// <seealso cref="AddTable(string, FromTable)"/>
 /// <seealso cref="SqlQueryExpression"/>
 public void AddSubQuery(string alias, SqlQueryExpression subQuery)
 {
     AddTable(alias, new FromTable(subQuery, alias));
 }
示例#45
0
        private ObjectName ResolveGroupMax(SqlQueryExpression queryExpression, QueryExpressionFrom queryFrom)
        {
            var groupMax = queryExpression.GroupMax;
            if (groupMax != null) {
                var variable = queryFrom.ResolveReference(groupMax);
                if (variable == null)
                    throw new InvalidOperationException(String.Format("The GROUP MAX column '{0}' was not found.", groupMax));

                groupMax = variable;
            }

            return groupMax;
        }
 public SqlQueryExpressionComposite(CompositeFunction function, SqlQueryExpression expression)
     : this(function, false, expression)
 {
 }
示例#47
0
 /// <summary>
 /// Adds a sub-query expression as source of the query.
 /// </summary>
 /// <param name="alias">The unique alias name of the expression within the clause.</param>
 /// <param name="subQuery">The sub-query expression as source of the query.</param>
 /// <seealso cref="AddTable(string, FromTable)"/>
 /// <seealso cref="SqlQueryExpression"/>
 public void AddSubQuery(string alias, SqlQueryExpression subQuery)
 {
     AddTable(alias, new FromTable(subQuery, alias));
 }
示例#48
0
        private void PrepareJoins(QueryTablePlanner tablePlanner, SqlQueryExpression queryExpression, QueryExpressionFrom queryFrom, ref SqlExpression searchExpression)
        {
            var fromClause = queryExpression.FromClause;

            bool allInner = true;
            for (int i = 0; i < fromClause.JoinPartCount; i++) {
                var joinPart = fromClause.GetJoinPart(i);
                if (joinPart.JoinType != JoinType.Inner)
                    allInner = false;
            }

            for (int i = 0; i < fromClause.JoinPartCount; i++) {
                var joinPart = fromClause.GetJoinPart(i);

                var joinType = joinPart.JoinType;
                var onExpression = joinPart.OnExpression;

                if (allInner) {
                    // If the whole join set is inner joins then simply move the on
                    // expression (if there is one) to the WHERE clause.
                    if (searchExpression != null && onExpression != null)
                        searchExpression = SqlExpression.And(searchExpression, onExpression);
                } else {
                    // Not all inner joins,
                    if (joinType == JoinType.Inner && onExpression == null) {
                        // Regular join with no ON expression, so no preparation necessary
                    } else {
                        // Either an inner join with an ON expression, or an outer join with
                        // ON expression
                        if (onExpression == null)
                            throw new InvalidOperationException(String.Format("Join of type {0} requires ON expression.", joinType));

                        // Resolve the on_expression
                        onExpression = onExpression.Prepare(queryFrom.ExpressionPreparer);
                        // And set it in the planner
                        tablePlanner.JoinAt(i, joinType, onExpression);
                    }
                }
            }
        }
 public static int DeleteFrom(this IQuery context, ObjectName tableName, SqlQueryExpression query)
 {
     return DeleteFrom(context, tableName, query, -1);
 }
示例#50
0
 public override SqlExpression VisitQuery(SqlQueryExpression query)
 {
     return(base.VisitQuery(query));
 }
示例#51
0
 public void Query(SqlQueryExpression query, string alias)
 {
     Source(new SqlQueryExpressionSource(query, alias));
 }
示例#52
0
 /// <summary>
 /// A table that is a sub-query with no alias set.
 /// </summary>
 /// <param name="query"></param>
 public FromTable(SqlQueryExpression query)
     : this(query, null)
 {
 }
示例#53
0
        private int ResolveGroupBy(SqlQueryExpression queryExpression, QueryExpressionFrom queryFrom, IQueryContext context, out ObjectName[] columnNames, out IList<SqlExpression> expressions)
        {
            var groupBy = queryExpression.GroupBy == null
                ? new List<SqlExpression>(0)
                : queryExpression.GroupBy.ToList();
            var groupBySize = groupBy.Count;

            expressions = new List<SqlExpression>();
            columnNames = new ObjectName[groupBySize];

            for (int i = 0; i < groupBySize; i++) {
                var expression = groupBy[i];

                // Prepare the group by expression
                expression = expression.Prepare(queryFrom.ExpressionPreparer);

                var columnName = expression.AsReferenceName();
                if (columnName != null)
                    expression = queryFrom.FindExpression(columnName);

                if (expression != null) {
                    if (expression.HasAggregate(context))
                        throw new InvalidOperationException(String.Format("Aggregate expression '{0}' is not allowed in a GROUP BY clause", expression));

                    expressions.Add(expression);
                    columnName = new ObjectName(FunctionTableName, String.Format("#GROUPBY-{0}", expressions.Count -1));
                }

                columnNames[i] = columnName;
            }

            return groupBySize;
        }
示例#54
0
        private IQueryPlanNode PlanQuery(IQueryContext context, SqlQueryExpression queryExpression,
			QueryExpressionFrom queryFrom, IList<SortColumn> sortColumns)
        {
            // ----- Resolve the SELECT list
            // If there are 0 columns selected, then we assume the result should
            // show all of the columns in the result.
            bool doSubsetColumn = (queryExpression.SelectColumns.Any());

            // What we are selecting
            var columns = BuildSelectColumns(queryExpression, queryFrom);

            // Prepare the column_set,
            var preparedColumns = columns.Prepare(context);

            sortColumns = ResolveOrderByRefs(preparedColumns, sortColumns);

            // -----

            // Set up plans for each table in the from clause of the command.  For
            // sub-queries, we recurse.

            var tablePlanner = CreateTablePlanner(context, queryFrom);

            // -----

            // The WHERE and HAVING clauses
            var whereClause = queryExpression.WhereExpression;
            var havingClause = queryExpression.HavingExpression;

            PrepareJoins(tablePlanner, queryExpression, queryFrom, ref whereClause);

            // Prepare the WHERE and HAVING clause, qualifies all variables and
            // prepares sub-queries.
            whereClause = PrepareSearchExpression(context, queryFrom, whereClause);
            havingClause = PrepareSearchExpression(context, queryFrom, havingClause);

            // Any extra Aggregate functions that are part of the HAVING clause that
            // we need to add.  This is a list of a name followed by the expression
            // that contains the aggregate function.
            var extraAggregateFunctions = new List<SqlExpression>();
            if (havingClause != null)
                havingClause = FilterHaving(havingClause, extraAggregateFunctions, context);

            // Any GROUP BY functions,
            ObjectName[] groupByList;
            IList<SqlExpression> groupByFunctions;
            var gsz = ResolveGroupBy(queryExpression, queryFrom, context, out groupByList, out groupByFunctions);

            // Resolve GROUP MAX variable to a reference in this from set
            var groupmaxColumn = ResolveGroupMax(queryExpression, queryFrom);

            // -----

            // Now all the variables should be resolved and correlated variables set
            // up as appropriate.

            // If nothing in the FROM clause then simply evaluate the result of the
            // select
            if (queryFrom.SourceCount == 0)
                return EvaluateToSingle(preparedColumns);

            // Plan the where clause.  The returned node is the plan to evaluate the
            // WHERE clause.
            var node = tablePlanner.PlanSearchExpression(whereClause);

            SqlExpression[] defFunList;
            string[] defFunNames;
            var fsz = MakeupFunctions(preparedColumns, extraAggregateFunctions, out defFunList, out defFunNames);

            var groupInfo = new GroupInfo {
                Columns = preparedColumns,
                FunctionCount = fsz,
                FunctionNames = defFunNames,
                FunctionExpressions = defFunList,
                GroupByCount = gsz,
                GroupByNames = groupByList,
                GroupByExpressions = groupByFunctions.ToArray(),
                GroupMax = groupmaxColumn
            };

            node = PlanGroup(node, groupInfo);

            // The result column list
            var selectColumns = preparedColumns.SelectedColumns.ToList();
            int sz = selectColumns.Count;

            // Evaluate the having clause if necessary
            if (havingClause != null) {
                // Before we evaluate the having expression we must substitute all the
                // aliased variables.
                var havingExpr = havingClause;

                // TODO: this requires a visitor to modify the having expression
                havingExpr = ReplaceAliasedVariables(havingExpr, selectColumns);

                var source = tablePlanner.SinglePlan;
                source.UpdatePlan(node);
                node = tablePlanner.PlanSearchExpression(havingExpr);
            }

            // Do we have a composite select expression to process?
            IQueryPlanNode rightComposite = null;
            if (queryExpression.NextComposite != null) {
                var compositeExpr = queryExpression.NextComposite;
                var compositeFrom = QueryExpressionFrom.Create(context, compositeExpr);

                // Form the right plan
                rightComposite = PlanQuery(context, compositeExpr, compositeFrom, null);
            }

            // Do we do a final subset column?
            ObjectName[] aliases = null;
            if (doSubsetColumn) {
                // Make up the lists
                var subsetVars = new ObjectName[sz];
                aliases = new ObjectName[sz];
                for (int i = 0; i < sz; ++i) {
                    SelectColumn scol = selectColumns[i];
                    subsetVars[i] = scol.InternalName;
                    aliases[i] = scol.ResolvedName;
                }

                // If we are distinct then add the DistinctNode here
                if (queryExpression.Distinct)
                    node = new DistinctNode(node, subsetVars);

                // Process the ORDER BY?
                // Note that the ORDER BY has to occur before the subset call, but
                // after the distinct because distinct can affect the ordering of the
                // result.
                if (rightComposite == null && sortColumns != null)
                    node = PlanForOrderBy(node, sortColumns, queryFrom, selectColumns);

                // Rename the columns as specified in the SELECT
                node = new SubsetNode(node, subsetVars, aliases);
            } else {
                // Process the ORDER BY?
                if (rightComposite == null && sortColumns != null)
                    node = PlanForOrderBy(node, sortColumns, queryFrom, selectColumns);
            }

            // Do we have a composite to merge in?
            if (rightComposite != null) {
                // For the composite
                node = new CompositeNode(node, rightComposite, queryExpression.CompositeFunction, queryExpression.IsCompositeAll);

                // Final order by?
                if (sortColumns != null)
                    node = PlanForOrderBy(node, sortColumns, queryFrom, selectColumns);

                // Ensure a final subset node
                if (!(node is SubsetNode) && aliases != null) {
                    node = new SubsetNode(node, aliases, aliases);
                }
            }

            return node;
        }
 private SqlQueryExpressionSource(ObjectName tableName, SqlQueryExpression query, string alias)
 {
     TableName = tableName;
     Query     = query;
     Alias     = alias;
 }