private QuerySelectColumns BuildSelectColumns(SqlQueryExpression expression, QueryExpressionFrom queryFrom)
{
var selectColumns = new QuerySelectColumns(queryFrom);
foreach (var column in expression.SelectColumns)
{
// Is this a glob? (eg. Part.* )
if (column.IsGlob)
{
// Find the columns globbed and add to the 'selectedColumns' result.
if (column.IsAll)
{
selectColumns.SelectAllColumnsFromAllSources();
}
else
{
// Otherwise the glob must be of the form '[table name].*'
selectColumns.SelectAllColumnsFromSource(column.TableName);
}
}
else
{
// Otherwise must be a standard column reference.
selectColumns.SelectSingleColumn(column);
}
}
return(selectColumns);
}
internal FromTableSubQuerySource(bool caseInsensitive, string uniqueKey, SqlQueryExpression queryExpression,
QueryExpressionFrom fromSet, ObjectName alias)
{
UniqueName = uniqueKey;
QueryExpression = queryExpression;
QueryFrom = fromSet;
AliasName = alias;
IgnoreCase = caseInsensitive;
}
internal FromTableSubQuerySource(bool caseInsensitive, string uniqueKey, SqlQueryExpression queryExpression,
QueryExpressionFrom fromSet, ObjectName alias)
{
UniqueName = uniqueKey;
QueryExpression = queryExpression;
QueryFrom = fromSet;
AliasName = alias;
IgnoreCase = caseInsensitive;
}
public SqlExpression Prepare(SqlExpression expression)
{
var queryExpression = (SqlQueryExpression)expression;
var queryFrom = QueryExpressionFrom.Create(context, queryExpression);
queryFrom.Parent = parent;
var plan = planner.PlanQuery(context, queryExpression, queryFrom, null, null);
return(SqlExpression.Constant(new DataObject(new QueryType(), new SqlQueryObject(new CachePointNode(plan)))));
}
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);
}
private QueryTablePlanner CreateTablePlanner(IRequest context, QueryExpressionFrom queryFrom)
{
// Set up plans for each table in the from clause of the command. For
// sub-queries, we recurse.
var tablePlanner = new QueryTablePlanner();
for (int i = 0; i < queryFrom.SourceCount; i++)
{
var tableSource = queryFrom.GetTableSource(i);
IQueryPlanNode plan;
if (tableSource is FromTableSubQuerySource)
{
var subQuerySource = (FromTableSubQuerySource)tableSource;
var subQueryExpr = subQuerySource.QueryExpression;
var subQueryFrom = subQuerySource.QueryFrom;
plan = PlanQuery(context, subQueryExpr, subQueryFrom, null, null);
if (!(plan is SubsetNode))
{
throw new InvalidOperationException("The root node of a sub-query plan must be a subset.");
}
var subsetNode = (SubsetNode)plan;
subsetNode.SetAliasParentName(subQuerySource.AliasName);
}
else if (tableSource is FromTableDirectSource)
{
var directSource = (FromTableDirectSource)tableSource;
plan = directSource.QueryPlan;
}
else
{
throw new InvalidOperationException(String.Format("The type of FROM source '{0}' is not supported.", tableSource.GetType()));
}
tablePlanner.AddPlan(plan, tableSource);
}
return(tablePlanner);
}
private SqlExpression PrepareSearchExpression(IRequest context, QueryExpressionFrom queryFrom, SqlExpression expression)
{
// first check the expression is not null
if (expression == null)
{
return(null);
}
// This is used to prepare sub-queries and qualify variables in a
// search expression such as WHERE or HAVING.
// Prepare the sub-queries first
expression = expression.Prepare(new QueryExpressionPreparer(this, queryFrom, context));
// Then qualify all the variables. Note that this will not qualify
// variables in the sub-queries.
expression = expression.Prepare(queryFrom.ExpressionPreparer);
return(expression);
}
public IQueryPlanNode PlanQuery(QueryInfo queryInfo)
{
if (queryInfo == null)
{
throw new ArgumentNullException("queryInfo");
}
var context = queryInfo.Request;
var queryExpression = queryInfo.Expression;
var sortColumns = queryInfo.SortColumns;
var limit = queryInfo.Limit;
var queryFrom = QueryExpressionFrom.Create(context, queryExpression);
var orderBy = new List <SortColumn>();
if (sortColumns != null)
{
orderBy.AddRange(sortColumns);
}
return(PlanQuery(context, queryExpression, queryFrom, orderBy, limit));
}
private int ResolveGroupBy(SqlQueryExpression queryExpression, QueryExpressionFrom queryFrom, IRequest 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);
}
private QuerySelectColumns BuildSelectColumns(SqlQueryExpression expression, QueryExpressionFrom queryFrom)
{
var selectColumns = new QuerySelectColumns(queryFrom);
foreach (var column in expression.SelectColumns) {
// Is this a glob? (eg. Part.* )
if (column.IsGlob) {
// Find the columns globbed and add to the 'selectedColumns' result.
if (column.IsAll) {
selectColumns.SelectAllColumnsFromAllSources();
} else {
// Otherwise the glob must be of the form '[table name].*'
selectColumns.SelectAllColumnsFromSource(column.TableName);
}
} else {
// Otherwise must be a standard column reference.
selectColumns.SelectSingleColumn(column);
}
}
return selectColumns;
}
public FromExpressionPreparer(QueryExpressionFrom fromSet)
{
this.fromSet = fromSet;
}
public FromExpressionPreparer(QueryExpressionFrom fromSet)
{
this.fromSet = fromSet;
}
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;
}
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);
}
}
}
}
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 QueryExpressionFrom Create(IRequest context, SqlQueryExpression expression)
{
// Get the 'from_clause' from the table expression
var fromClause = expression.FromClause;
var ignoreCase = context.Query.IgnoreIdentifiersCase();
var queryFrom = new QueryExpressionFrom(ignoreCase);
foreach (var fromTable in fromClause.AllTables) {
var uniqueKey = fromTable.UniqueKey;
var alias = fromTable.Alias;
if (fromTable.IsSubQuery) {
// eg. FROM ( SELECT id FROM Part )
var subQuery = fromTable.SubQuery;
var subQueryFrom = Create(context, subQuery);
// The aliased name of the table
ObjectName aliasTableName = null;
if (alias != null)
aliasTableName = new ObjectName(alias);
// Add to list of sub-query tables to add to command,
queryFrom.AddTable(new FromTableSubQuerySource(ignoreCase, uniqueKey, subQuery, subQueryFrom, aliasTableName));
} else {
// Else must be a standard command table,
string name = fromTable.Name;
// Resolve to full table name
var tableName = context.Query.ResolveTableName(name);
if (!context.Query.TableExists(tableName))
throw new InvalidOperationException(String.Format("Table '{0}' was not found.", tableName));
ObjectName givenName = null;
if (alias != null)
givenName = new ObjectName(alias);
// Get the ITableQueryInfo object for this table name (aliased).
ITableQueryInfo tableQueryInfo = context.Query.GetTableQueryInfo(tableName, givenName);
queryFrom.AddTable(new FromTableDirectSource(ignoreCase, tableQueryInfo, uniqueKey, givenName, tableName));
}
}
// Set up functions, aliases and exposed variables for this from set,
foreach (var selectColumn in expression.SelectColumns) {
// Is this a glob? (eg. Part.* )
if (selectColumn.IsGlob) {
// Find the columns globbed and add to the 'selectedColumns' result.
if (selectColumn.IsAll) {
queryFrom.ExposeAllColumns();
} else {
// Otherwise the glob must be of the form '[table name].*'
queryFrom.ExposeColumns(selectColumn.TableName);
}
} else {
// Otherwise must be a standard column reference. Note that at this
// time we aren't sure if a column expression is correlated and is
// referencing an outer source. This means we can't verify if the
// column expression is valid or not at this point.
// If this column is aliased, add it as a function reference to the
// select expression
string alias = selectColumn.Alias;
var v = selectColumn.Expression.AsReferenceName();
bool aliasMatchV = (v != null && alias != null &&
queryFrom.CompareStrings(v.Name, alias));
if (alias != null && !aliasMatchV) {
queryFrom.AddExpression(new ExpressionReference(selectColumn.Expression, alias));
queryFrom.ExposeColumn(new ObjectName(alias));
} else if (v != null) {
var resolved = queryFrom.ResolveReference(v);
queryFrom.ExposeColumn(resolved ?? v);
} else {
string funName = selectColumn.Expression.ToString();
queryFrom.AddExpression(new ExpressionReference(selectColumn.Expression, funName));
queryFrom.ExposeColumn(new ObjectName(funName));
}
}
}
return queryFrom;
}
private QueryTablePlanner CreateTablePlanner(IQueryContext context, QueryExpressionFrom queryFrom)
{
// Set up plans for each table in the from clause of the command. For
// sub-queries, we recurse.
var tablePlanner = new QueryTablePlanner();
for (int i = 0; i < queryFrom.SourceCount; i++) {
var tableSource = queryFrom.GetTableSource(i);
IQueryPlanNode plan;
if (tableSource is FromTableSubQuerySource) {
var subQuerySource = (FromTableSubQuerySource) tableSource;
var subQueryExpr = subQuerySource.QueryExpression;
var subQueryFrom = subQuerySource.QueryFrom;
plan = PlanQuery(context, subQueryExpr, subQueryFrom, null);
if (!(plan is SubsetNode))
throw new InvalidOperationException("The root node of a sub-query plan must be a subset.");
var subsetNode = (SubsetNode) plan;
subsetNode.SetAliasParentName(subQuerySource.AliasName);
} else if (tableSource is FromTableDirectSource) {
var directSource = (FromTableDirectSource) tableSource;
plan = directSource.QueryPlan;
} else {
throw new InvalidOperationException(String.Format("The type of FROM source '{0}' is not supported.", tableSource.GetType()));
}
tablePlanner.AddPlan(plan, tableSource);
}
return tablePlanner;
}
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);
}
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;
}
public QueryExpressionPreparer(QueryPlanner planner, QueryExpressionFrom parent, IQueryContext context)
{
this.planner = planner;
this.parent = parent;
this.context = context;
}
private SqlExpression PrepareSearchExpression(IQueryContext context, QueryExpressionFrom queryFrom, SqlExpression expression)
{
// first check the expression is not null
if (expression == null)
return null;
// This is used to prepare sub-queries and qualify variables in a
// search expression such as WHERE or HAVING.
// Prepare the sub-queries first
expression = expression.Prepare(new QueryExpressionPreparer(this, queryFrom, context));
// Then qualify all the variables. Note that this will not qualify
// variables in the sub-queries.
expression = expression.Prepare(queryFrom.ExpressionPreparer);
return expression;
}
// The count of aggregate and constant columns included in the result set.
// Aggregate columns are, (count(*), avg(cost_of) * 0.75, etc). Constant
// columns are, (9 * 4, 2, (9 * 7 / 4) + 4, etc).
public QuerySelectColumns(QueryExpressionFrom fromSet)
{
this.fromSet = fromSet;
selectedColumns = new List <SelectColumn>();
}
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;
}
// The count of aggregate and constant columns included in the result set.
// Aggregate columns are, (count(*), avg(cost_of) * 0.75, etc). Constant
// columns are, (9 * 4, 2, (9 * 7 / 4) + 4, etc).
public QuerySelectColumns(QueryExpressionFrom fromSet)
{
this.fromSet = fromSet;
selectedColumns = new List<SelectColumn>();
}
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 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);
}
public QueryExpressionPreparer(QueryPlanner planner, QueryExpressionFrom parent, IRequest context)
{
this.planner = planner;
this.parent = parent;
this.context = context;
}
public static QueryExpressionFrom Create(IRequest context, SqlQueryExpression expression)
{
// Get the 'from_clause' from the table expression
var fromClause = expression.FromClause;
var ignoreCase = context.Query.IgnoreIdentifiersCase();
var queryFrom = new QueryExpressionFrom(ignoreCase);
foreach (var fromTable in fromClause.AllTables)
{
var uniqueKey = fromTable.UniqueKey;
var alias = fromTable.Alias;
if (fromTable.IsSubQuery)
{
// eg. FROM ( SELECT id FROM Part )
var subQuery = fromTable.SubQuery;
var subQueryFrom = Create(context, subQuery);
// The aliased name of the table
ObjectName aliasTableName = null;
if (alias != null)
{
aliasTableName = new ObjectName(alias);
}
// Add to list of sub-query tables to add to command,
queryFrom.AddTable(new FromTableSubQuerySource(ignoreCase, uniqueKey, subQuery, subQueryFrom, aliasTableName));
}
else
{
// Else must be a standard command table,
string name = fromTable.Name;
// Resolve to full table name
var tableName = context.Query.ResolveTableName(name);
if (!context.Query.TableExists(tableName))
{
throw new InvalidOperationException(String.Format("Table '{0}' was not found.", tableName));
}
ObjectName givenName = null;
if (alias != null)
{
givenName = new ObjectName(alias);
}
// Get the ITableQueryInfo object for this table name (aliased).
ITableQueryInfo tableQueryInfo = context.Query.GetTableQueryInfo(tableName, givenName);
queryFrom.AddTable(new FromTableDirectSource(ignoreCase, tableQueryInfo, uniqueKey, givenName, tableName));
}
}
// Set up functions, aliases and exposed variables for this from set,
foreach (var selectColumn in expression.SelectColumns)
{
// Is this a glob? (eg. Part.* )
if (selectColumn.IsGlob)
{
// Find the columns globbed and add to the 'selectedColumns' result.
if (selectColumn.IsAll)
{
queryFrom.ExposeAllColumns();
}
else
{
// Otherwise the glob must be of the form '[table name].*'
queryFrom.ExposeColumns(selectColumn.TableName);
}
}
else
{
// Otherwise must be a standard column reference. Note that at this
// time we aren't sure if a column expression is correlated and is
// referencing an outer source. This means we can't verify if the
// column expression is valid or not at this point.
// If this column is aliased, add it as a function reference to the
// select expression
string alias = selectColumn.Alias;
var v = selectColumn.Expression.AsReferenceName();
bool aliasMatchV = (v != null && alias != null &&
queryFrom.CompareStrings(v.Name, alias));
if (alias != null && !aliasMatchV)
{
queryFrom.AddExpression(new ExpressionReference(selectColumn.Expression, alias));
queryFrom.ExposeColumn(new ObjectName(alias));
}
else if (v != null)
{
var resolved = queryFrom.ResolveReference(v);
queryFrom.ExposeColumn(resolved ?? v);
}
else
{
string funName = selectColumn.Expression.ToString();
queryFrom.AddExpression(new ExpressionReference(selectColumn.Expression, funName));
queryFrom.ExposeColumn(new ObjectName(funName));
}
}
}
return(queryFrom);
}
