Example #1
0
        /// <summary>
        /// Plans an ORDER BY set.
        /// </summary>
        /// <param name="plan"></param>
        /// <param name="orderBy"></param>
        /// <param name="fromSet"></param>
        /// <param name="selectedColumns"></param>
        /// <remarks>
        /// This is given its own function because we may want to plan
        /// this at the end of a number of composite functions.
        /// </remarks>
        /// <returns></returns>
        private static IQueryPlanNode PlanForOrderBy(IQueryPlanNode plan, IList <ByColumn> orderBy, TableExpressionFromSet fromSet, IList <SelectColumn> selectedColumns)
        {
            var functionTable = new ObjectName("FUNCTIONTABLE");

            // Sort on the ORDER BY clause
            if (orderBy.Count > 0)
            {
                int sz            = orderBy.Count;
                var orderList     = new ObjectName[sz];
                var ascendingList = new bool[sz];

                var functionOrders = new List <Expression>();

                for (int i = 0; i < sz; ++i)
                {
                    ByColumn   column = orderBy[i];
                    Expression exp    = column.Expression;
                    ascendingList[i] = column.Ascending;
                    ObjectName v = exp.AsVariable();
                    if (v != null)
                    {
                        ObjectName newV = fromSet.ResolveReference(v);
                        if (newV == null)
                        {
                            throw new ApplicationException("Can not resolve ORDER BY variable: " + v);
                        }

                        newV         = SubstituteAliasedVariable(newV, selectedColumns);
                        orderList[i] = newV;
                    }
                    else
                    {
                        // Otherwise we must be ordering by an expression such as
                        // '0 - a'.

                        // Resolve the expression,
                        exp = exp.Prepare(fromSet.ExpressionQualifier);

                        // Make sure we substitute any aliased columns in the order by
                        // columns.
                        exp = SubstituteAliasedVariables(exp, selectedColumns);

                        // The new ordering functions are called 'FUNCTIONTABLE.#ORDER-n'
                        // where n is the number of the ordering expression.
                        orderList[i] = new ObjectName(functionTable, "#ORDER-" + functionOrders.Count);
                        functionOrders.Add(exp);
                    }
                }

                // If there are functional orderings,
                // For this we must define a new FunctionTable with the expressions,
                // then order by those columns, and then use another SubsetNode
                // command node.
                int fsz = functionOrders.Count;
                if (fsz > 0)
                {
                    var funs   = new Expression[fsz];
                    var fnames = new String[fsz];
                    for (int n = 0; n < fsz; ++n)
                    {
                        funs[n]   = functionOrders[n];
                        fnames[n] = "#ORDER-" + n;
                    }

                    if (plan is SubsetNode)
                    {
                        // If the top plan is a QueryPlan.SubsetNode then we use the
                        //   information from it to create a new SubsetNode that
                        //   doesn't include the functional orders we have attached here.
                        var          topSubsetNode = (SubsetNode)plan;
                        ObjectName[] mappedNames   = topSubsetNode.NewColumnNames;

                        // Defines the sort functions
                        plan = new CreateFunctionsNode(plan, funs, fnames);
                        // Then plan the sort
                        plan = new SortNode(plan, orderList, ascendingList);
                        // Then plan the subset
                        plan = new SubsetNode(plan, mappedNames, mappedNames);
                    }
                    else
                    {
                        // Defines the sort functions
                        plan = new CreateFunctionsNode(plan, funs, fnames);
                        // Plan the sort
                        plan = new SortNode(plan, orderList, ascendingList);
                    }
                }
                else
                {
                    // No functional orders so we only need to sort by the columns
                    // defined.
                    plan = new SortNode(plan, orderList, ascendingList);
                }
            }

            return(plan);
        }
Example #2
0
        /// <summary>
        /// Forms a command plan <see cref="IQueryPlanNode"/> from the given 
        /// <see cref="TableSelectExpression"/> and <see cref="TableExpressionFromSet"/>.
        /// </summary>
        /// <param name="db"></param>
        /// <param name="expression">Describes the <i>SELECT</i> command 
        /// (or sub-command).</param>
        /// <param name="fromSet">Used to resolve expression references.</param>
        /// <param name="orderBy">A list of <see cref="ByColumn"/> objects 
        /// that represent an optional <i>ORDER BY</i> clause. If this is null 
        /// or the list is empty, no ordering is done.</param>
        /// <returns></returns>
        public static IQueryPlanNode FormQueryPlan(IDatabaseConnection db, TableSelectExpression expression, TableExpressionFromSet fromSet, IList<ByColumn> orderBy)
        {
            IQueryContext context = new DatabaseQueryContext(db);

            // ----- 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 = (expression.Columns.Count != 0);

            // What we are selecting
            var columnSet = BuildColumnSet(expression, fromSet);

            // Prepare the column_set,
            columnSet.Prepare(context);

            ResolveOrderByRefs(columnSet, orderBy);

            // -----

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

            var tablePlanner = SetupPlanners(db, fromSet);

            // -----

            // The WHERE and HAVING clauses
            FilterExpression whereClause = expression.Where;
            FilterExpression havingClause = expression.Having;

            whereClause = PrepareJoins(tablePlanner, expression, fromSet, whereClause);

            // Prepare the WHERE and HAVING clause, qualifies all variables and
            // prepares sub-queries.
            whereClause = PrepareSearchExpression(db, fromSet, whereClause);
            havingClause = PrepareSearchExpression(db, fromSet, 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<Expression>();
            if (havingClause != null && havingClause.Expression != null) {
                Expression newHavingClause = FilterHavingClause(havingClause.Expression, extraAggregateFunctions, context);
                havingClause = new FilterExpression(newHavingClause);
            }

            // Any GROUP BY functions,
            ObjectName[] groupByList;
            IList<Expression> groupByFunctions;
            var gsz = ResolveGroupBy(expression, fromSet, context, out groupByList, out groupByFunctions);

            // Resolve GROUP MAX variable to a reference in this from set
            ObjectName groupmaxColumn = ResolveGroupMax(expression, fromSet);

            // -----

            // 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 (fromSet.SetCount == 0)
                return EvaluateSingle(columnSet);

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

            Expression[] defFunList;
            string[] defFunNames;
            var fsz = MakeupFunctions(columnSet, extraAggregateFunctions, out defFunList, out defFunNames);

            node = PlanGroup(node, columnSet, groupmaxColumn, gsz, groupByList, groupByFunctions, fsz, defFunNames, defFunList);

            // The result column list
            List<SelectColumn> selectColumns = columnSet.SelectedColumns;
            int sz = selectColumns.Count;

            // Evaluate the having clause if necessary
            if (havingClause != null && havingClause.Expression != null) {
                // Before we evaluate the having expression we must substitute all the
                // aliased variables.
                Expression havingExpr = havingClause.Expression;
                havingExpr = SubstituteAliasedVariables(havingExpr, selectColumns);
                havingClause = new FilterExpression(havingExpr);

                PlanTableSource source = tablePlanner.SingleTableSource;
                source.UpdatePlan(node);
                node = tablePlanner.PlanSearchExpression(havingClause);
            }

            // Do we have a composite select expression to process?
            IQueryPlanNode rightComposite = null;
            if (expression.NextComposite != null) {
                TableSelectExpression compositeExpr = expression.NextComposite;
                // Generate the TableExpressionFromSet hierarchy for the expression,
                TableExpressionFromSet compositeFromSet = GenerateFromSet(compositeExpr, db);

                // Form the right plan
                rightComposite = FormQueryPlan(db, compositeExpr, compositeFromSet, null);
            }

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

                // If we are distinct then add the DistinctNode here
                if (expression.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 && orderBy != null)
                    node = PlanForOrderBy(node, orderBy, fromSet, selectColumns);

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

            // Do we have a composite to merge in?
            if (rightComposite != null) {
                // For the composite
                node = new CompositeNode(node, rightComposite,
                            expression.CompositeFunction, expression.IsCompositeAll);
                // Final order by?
                if (orderBy != null) {
                    node = PlanForOrderBy(node, orderBy, fromSet, selectColumns);
                }
                // Ensure a final subset node
                if (!(node is SubsetNode) && aliases != null) {
                    node = new SubsetNode(node, aliases, aliases);
                }
            }

            return node;
        }
Example #3
0
        /// <summary>
        /// Forms a command plan <see cref="IQueryPlanNode"/> from the given
        /// <see cref="TableSelectExpression"/> and <see cref="TableExpressionFromSet"/>.
        /// </summary>
        /// <param name="db"></param>
        /// <param name="expression">Describes the <i>SELECT</i> command
        /// (or sub-command).</param>
        /// <param name="fromSet">Used to resolve expression references.</param>
        /// <param name="orderBy">A list of <see cref="ByColumn"/> objects
        /// that represent an optional <i>ORDER BY</i> clause. If this is null
        /// or the list is empty, no ordering is done.</param>
        /// <returns></returns>
        public static IQueryPlanNode FormQueryPlan(IDatabaseConnection db, TableSelectExpression expression, TableExpressionFromSet fromSet, IList <ByColumn> orderBy)
        {
            IQueryContext context = new DatabaseQueryContext(db);

            // ----- 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 = (expression.Columns.Count != 0);

            // What we are selecting
            var columnSet = BuildColumnSet(expression, fromSet);

            // Prepare the column_set,
            columnSet.Prepare(context);

            ResolveOrderByRefs(columnSet, orderBy);

            // -----

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

            var tablePlanner = SetupPlanners(db, fromSet);

            // -----

            // The WHERE and HAVING clauses
            FilterExpression whereClause  = expression.Where;
            FilterExpression havingClause = expression.Having;

            whereClause = PrepareJoins(tablePlanner, expression, fromSet, whereClause);

            // Prepare the WHERE and HAVING clause, qualifies all variables and
            // prepares sub-queries.
            whereClause  = PrepareSearchExpression(db, fromSet, whereClause);
            havingClause = PrepareSearchExpression(db, fromSet, 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 <Expression>();

            if (havingClause != null && havingClause.Expression != null)
            {
                Expression newHavingClause = FilterHavingClause(havingClause.Expression, extraAggregateFunctions, context);
                havingClause = new FilterExpression(newHavingClause);
            }

            // Any GROUP BY functions,
            ObjectName[]       groupByList;
            IList <Expression> groupByFunctions;
            var gsz = ResolveGroupBy(expression, fromSet, context, out groupByList, out groupByFunctions);

            // Resolve GROUP MAX variable to a reference in this from set
            ObjectName groupmaxColumn = ResolveGroupMax(expression, fromSet);

            // -----

            // 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 (fromSet.SetCount == 0)
            {
                return(EvaluateSingle(columnSet));
            }

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

            Expression[] defFunList;
            string[]     defFunNames;
            var          fsz = MakeupFunctions(columnSet, extraAggregateFunctions, out defFunList, out defFunNames);

            node = PlanGroup(node, columnSet, groupmaxColumn, gsz, groupByList, groupByFunctions, fsz, defFunNames, defFunList);

            // The result column list
            List <SelectColumn> selectColumns = columnSet.SelectedColumns;
            int sz = selectColumns.Count;

            // Evaluate the having clause if necessary
            if (havingClause != null && havingClause.Expression != null)
            {
                // Before we evaluate the having expression we must substitute all the
                // aliased variables.
                Expression havingExpr = havingClause.Expression;
                havingExpr   = SubstituteAliasedVariables(havingExpr, selectColumns);
                havingClause = new FilterExpression(havingExpr);

                PlanTableSource source = tablePlanner.SingleTableSource;
                source.UpdatePlan(node);
                node = tablePlanner.PlanSearchExpression(havingClause);
            }

            // Do we have a composite select expression to process?
            IQueryPlanNode rightComposite = null;

            if (expression.NextComposite != null)
            {
                TableSelectExpression compositeExpr = expression.NextComposite;
                // Generate the TableExpressionFromSet hierarchy for the expression,
                TableExpressionFromSet compositeFromSet = GenerateFromSet(compositeExpr, db);

                // Form the right plan
                rightComposite = FormQueryPlan(db, compositeExpr, compositeFromSet, null);
            }

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

                // If we are distinct then add the DistinctNode here
                if (expression.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 && orderBy != null)
                {
                    node = PlanForOrderBy(node, orderBy, fromSet, selectColumns);
                }

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

            // Do we have a composite to merge in?
            if (rightComposite != null)
            {
                // For the composite
                node = new CompositeNode(node, rightComposite,
                                         expression.CompositeFunction, expression.IsCompositeAll);
                // Final order by?
                if (orderBy != null)
                {
                    node = PlanForOrderBy(node, orderBy, fromSet, selectColumns);
                }
                // Ensure a final subset node
                if (!(node is SubsetNode) && aliases != null)
                {
                    node = new SubsetNode(node, aliases, aliases);
                }
            }

            return(node);
        }
Example #4
0
        /// <summary>
        /// Plans an ORDER BY set.
        /// </summary>
        /// <param name="plan"></param>
        /// <param name="orderBy"></param>
        /// <param name="fromSet"></param>
        /// <param name="selectedColumns"></param>
        /// <remarks>
        /// This is given its own function because we may want to plan 
        /// this at the end of a number of composite functions.
        /// </remarks>
        /// <returns></returns>
        private static IQueryPlanNode PlanForOrderBy(IQueryPlanNode plan, IList<ByColumn> orderBy, TableExpressionFromSet fromSet, IList<SelectColumn> selectedColumns)
        {
            var functionTable = new ObjectName("FUNCTIONTABLE");

            // Sort on the ORDER BY clause
            if (orderBy.Count > 0) {
                int sz = orderBy.Count;
                var orderList = new ObjectName[sz];
                var ascendingList = new bool[sz];

                var functionOrders = new List<Expression>();

                for (int i = 0; i < sz; ++i) {
                    ByColumn column = orderBy[i];
                    Expression exp = column.Expression;
                    ascendingList[i] = column.Ascending;
                    ObjectName v = exp.AsVariable();
                    if (v != null) {
                        ObjectName newV = fromSet.ResolveReference(v);
                        if (newV == null)
                            throw new ApplicationException("Can not resolve ORDER BY variable: " + v);

                        newV = SubstituteAliasedVariable(newV, selectedColumns);
                        orderList[i] = newV;
                    } else {
                        // Otherwise we must be ordering by an expression such as
                        // '0 - a'.

                        // Resolve the expression,
                        exp = exp.Prepare(fromSet.ExpressionQualifier);

                        // Make sure we substitute any aliased columns in the order by
                        // columns.
                        exp = SubstituteAliasedVariables(exp, selectedColumns);

                        // The new ordering functions are called 'FUNCTIONTABLE.#ORDER-n'
                        // where n is the number of the ordering expression.
                        orderList[i] = new ObjectName(functionTable, "#ORDER-" + functionOrders.Count);
                        functionOrders.Add(exp);
                    }
                }

                // If there are functional orderings,
                // For this we must define a new FunctionTable with the expressions,
                // then order by those columns, and then use another SubsetNode
                // command node.
                int fsz = functionOrders.Count;
                if (fsz > 0) {
                    var funs = new Expression[fsz];
                    var fnames = new String[fsz];
                    for (int n = 0; n < fsz; ++n) {
                        funs[n] = functionOrders[n];
                        fnames[n] = "#ORDER-" + n;
                    }

                    if (plan is SubsetNode) {
                        // If the top plan is a QueryPlan.SubsetNode then we use the
                        //   information from it to create a new SubsetNode that
                        //   doesn't include the functional orders we have attached here.
                        var topSubsetNode = (SubsetNode)plan;
                        ObjectName[] mappedNames = topSubsetNode.NewColumnNames;

                        // Defines the sort functions
                        plan = new CreateFunctionsNode(plan, funs, fnames);
                        // Then plan the sort
                        plan = new SortNode(plan, orderList, ascendingList);
                        // Then plan the subset
                        plan = new SubsetNode(plan, mappedNames, mappedNames);
                    } else {
                        // Defines the sort functions
                        plan = new CreateFunctionsNode(plan, funs, fnames);
                        // Plan the sort
                        plan = new SortNode(plan, orderList, ascendingList);
                    }

                } else {
                    // No functional orders so we only need to sort by the columns
                    // defined.
                    plan = new SortNode(plan, orderList, ascendingList);
                }
            }

            return plan;
        }