protected virtual IQueryPlanNode VisitSubset(SubsetNode node) { var child = node.Child; if (child != null) { child = VisitNode(child); } return(new SubsetNode(child, node.OriginalColumnNames, node.AliasColumnNames)); }
private static IQueryPlanNode PlanForOrderBy(IQueryPlanNode plan, IList<SortColumn> orderBy, QueryExpressionFrom queryFrom, IList<SelectColumn> selectedColumns) { // 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<SqlExpression>(); for (int i = 0; i < sz; ++i) { var column = orderBy[i]; SqlExpression exp = column.Expression; ascendingList[i] = column.Ascending; var v = exp.AsReferenceName(); if (v != null) { var newV = queryFrom.ResolveReference(v); if (newV == null) throw new InvalidOperationException(String.Format("Could not resolve ORDER BY column '{0}' in expression", v)); newV = ReplaceAliasedVariable(newV, selectedColumns); orderList[i] = newV; } else { // Otherwise we must be ordering by an expression such as // '0 - a'. // Resolve the expression, exp = exp.Prepare(queryFrom.ExpressionPreparer); // Make sure we substitute any aliased columns in the order by // columns. exp = ReplaceAliasedVariables(exp, selectedColumns); // The new ordering functions are called 'FUNCTIONTABLE.#ORDER-n' // where n is the number of the ordering expression. orderList[i] = new ObjectName(FunctionTableName, "#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 SqlExpression[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 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; var mappedNames = topSubsetNode.AliasColumnNames; // 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; }
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 static IQueryPlanNode PlanForOrderBy(IQueryPlanNode plan, IList <SortColumn> orderBy, QueryExpressionFrom queryFrom, IList <SelectColumn> selectedColumns) { // 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 <SqlExpression>(); for (int i = 0; i < sz; ++i) { var column = orderBy[i]; SqlExpression exp = column.Expression; ascendingList[i] = column.Ascending; var v = exp.AsReferenceName(); if (v != null) { var newV = queryFrom.ResolveReference(v); if (newV == null) { throw new InvalidOperationException(String.Format("Could not resolve ORDER BY column '{0}' in expression", v)); } newV = ReplaceAliasedVariable(newV, selectedColumns); orderList[i] = newV; } else { // Otherwise we must be ordering by an expression such as // '0 - a'. // Resolve the expression, exp = exp.Prepare(queryFrom.ExpressionPreparer); // Make sure we substitute any aliased columns in the order by // columns. exp = ReplaceAliasedVariables(exp, selectedColumns); // The new ordering functions are called 'FUNCTIONTABLE.#ORDER-n' // where n is the number of the ordering expression. orderList[i] = new ObjectName(FunctionTableName, "#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 SqlExpression[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 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; var mappedNames = topSubsetNode.AliasColumnNames; // 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); }
private IQueryPlanNode PlanQuery(IRequest context, SqlQueryExpression queryExpression, QueryExpressionFrom queryFrom, IList <SortColumn> sortColumns, QueryLimit limit) { // ----- 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, 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); } } if (limit != null) { node = new LimitNode(node, limit.Offset, limit.Count); } return(node); }
protected virtual IQueryPlanNode VisitSubset(SubsetNode node) { var child = node.Child; if (child != null) child = VisitNode(child); return new SubsetNode(child, node.OriginalColumnNames, node.AliasColumnNames); }
protected override SqlPreparedStatement PrepareStatement(IExpressionPreparer preparer, IQueryContext context) { var viewName = context.ResolveTableName(ViewName); var queryFrom = QueryExpressionFrom.Create(context, QueryExpression); var queryPlan = context.DatabaseContext().QueryPlanner().PlanQuery(context, QueryExpression, null); var colList = ColumnNames == null ? new string[0] : ColumnNames.ToArray(); // Wrap the result around a SubsetNode to alias the columns in the // table correctly for this view. int sz = colList.Length; var originalNames = queryFrom.GetResolvedColumns(); var newColumnNames = new ObjectName[originalNames.Length]; if (sz > 0) { if (sz != originalNames.Length) throw new InvalidOperationException("Column list is not the same size as the columns selected."); for (int i = 0; i < sz; ++i) { var colName = colList[i]; newColumnNames[i] = new ObjectName(viewName, colName); } } else { sz = originalNames.Length; for (int i = 0; i < sz; ++i) { newColumnNames[i] = new ObjectName(viewName, originalNames[i].Name); } } // Check there are no repeat column names in the table. for (int i = 0; i < sz; ++i) { var columnName = newColumnNames[i]; for (int n = i + 1; n < sz; ++n) { if (newColumnNames[n].Equals(columnName)) throw new InvalidOperationException(String.Format("Duplicate column name '{0}' in view. A view may not contain duplicate column names.", columnName)); } } // Wrap the plan around a SubsetNode plan queryPlan = new SubsetNode(queryPlan, originalNames, newColumnNames); // We have to execute the plan to get the TableInfo that represents the // result of the view execution. var table = queryPlan.Evaluate(context); var tableInfo = table.TableInfo.Alias(viewName); return new Prepared(tableInfo, QueryExpression, queryPlan, ReplaceIfExists); }
IStatement IPreparableStatement.Prepare(IRequest context) { var viewName = context.Query.ResolveTableName(ViewName); var queryFrom = QueryExpressionFrom.Create(context, QueryExpression); var queryPlan = context.Query.Context.QueryPlanner().PlanQuery(new QueryInfo(context, QueryExpression)); var colList = ColumnNames == null ? new string[0] : ColumnNames.ToArray(); // Wrap the result around a SubsetNode to alias the columns in the // table correctly for this view. int sz = colList.Length; var originalNames = queryFrom.GetResolvedColumns(); var newColumnNames = new ObjectName[originalNames.Length]; if (sz > 0) { if (sz != originalNames.Length) throw new InvalidOperationException("Column list is not the same size as the columns selected."); for (int i = 0; i < sz; ++i) { var colName = colList[i]; newColumnNames[i] = new ObjectName(viewName, colName); } } else { sz = originalNames.Length; for (int i = 0; i < sz; ++i) { newColumnNames[i] = new ObjectName(viewName, originalNames[i].Name); } } // Check there are no repeat column names in the table. for (int i = 0; i < sz; ++i) { var columnName = newColumnNames[i]; for (int n = i + 1; n < sz; ++n) { if (newColumnNames[n].Equals(columnName)) throw new InvalidOperationException(String.Format("Duplicate column name '{0}' in view. A view may not contain duplicate column names.", columnName)); } } // Wrap the plan around a SubsetNode plan queryPlan = new SubsetNode(queryPlan, originalNames, newColumnNames); return new Prepared(viewName, QueryExpression, queryPlan, ReplaceIfExists); }