/// <summary>
/// Constructs the source.
/// </summary>
/// <param name="caseInsensitive"></param>
/// <param name="uniqueKey"></param>
/// <param name="tableExpression"></param>
/// <param name="fromSet"></param>
/// <param name="aliasedTableName"></param>
internal FromTableSubQuerySource(bool caseInsensitive, string uniqueKey, TableSelectExpression tableExpression,
TableExpressionFromSet fromSet, ObjectName aliasedTableName)
{
this.uniqueKey = uniqueKey;
this.tableExpression = tableExpression;
this.fromSet = fromSet;
endTableName = aliasedTableName;
// Is the database case insensitive?
this.caseInsensitive = caseInsensitive;
}
/// <summary>
/// Constructs the source.
/// </summary>
/// <param name="caseInsensitive"></param>
/// <param name="uniqueKey"></param>
/// <param name="tableExpression"></param>
/// <param name="fromSet"></param>
/// <param name="aliasedTableName"></param>
internal FromTableSubQuerySource(bool caseInsensitive, string uniqueKey, TableSelectExpression tableExpression,
TableExpressionFromSet fromSet, ObjectName aliasedTableName)
{
this.uniqueKey = uniqueKey;
this.tableExpression = tableExpression;
this.fromSet = fromSet;
endTableName = aliasedTableName;
// Is the database case insensitive?
this.caseInsensitive = caseInsensitive;
}
public Expression Prepare(Expression expression)
{
TableSelectExpression sqlExpr = ((QueryExpression)expression).SelectExpression;
TableExpressionFromSet sqlFromSet = GenerateFromSet(sqlExpr, db);
sqlFromSet.Parent = fromSet;
IQueryPlanNode sqlPlan = FormQueryPlan(db, sqlExpr, sqlFromSet, null);
// Form this into a command plan type
return(Expression.Constant(new DataObject(PrimitiveTypes.Query(), new CachePointNode(sqlPlan))));
}
private static int ResolveGroupBy(TableSelectExpression expression, TableExpressionFromSet fromSet, IQueryContext context, out ObjectName[] groupByList, out IList <Expression> groupByFunctions)
{
// Any GROUP BY functions,
groupByFunctions = new List <Expression>();
// Resolve the GROUP BY variable list references in this from set
IList <ByColumn> groupListIn = expression.GroupBy;
int gsz = groupListIn.Count;
groupByList = new ObjectName[gsz];
for (int i = 0; i < gsz; ++i)
{
ByColumn byColumn = groupListIn[i];
Expression exp = byColumn.Expression;
// Prepare the group by expression
exp.Prepare(fromSet.ExpressionQualifier);
// Is the group by variable a complex expression?
ObjectName v = exp.AsVariable();
Expression groupByExpression;
if (v == null)
{
groupByExpression = exp;
}
else
{
// Can we dereference the variable to an expression in the SELECT?
groupByExpression = fromSet.DereferenceAssignment(v);
}
if (groupByExpression != null)
{
if (groupByExpression.HasAggregateFunction(context))
{
throw new ApplicationException("Aggregate expression '" + groupByExpression + "' is not allowed in GROUP BY clause.");
}
// Complex expression so add this to the function list.
int groupByFunNum = groupByFunctions.Count;
groupByFunctions.Add(groupByExpression);
v = new ObjectName(GroupByFunctionTable, "#GROUPBY-" + groupByFunNum);
}
groupByList[i] = v;
}
return(gsz);
}
private static QueryTableSetPlanner SetupPlanners(IDatabaseConnection db, TableExpressionFromSet fromSet)
{
// Set up plans for each table in the from clause of the command. For
// sub-queries, we recurse.
var tablePlanner = new QueryTableSetPlanner();
for (int i = 0; i < fromSet.SetCount; ++i)
{
IFromTableSource table = fromSet.GetTable(i);
if (table is FromTableSubQuerySource)
{
// This represents a sub-command in the FROM clause
var sqlTable = (FromTableSubQuerySource)table;
TableSelectExpression sqlExpr = sqlTable.TableExpression;
TableExpressionFromSet sqlFromSet = sqlTable.FromSet;
// Form a plan for evaluating the sub-command FROM
IQueryPlanNode sqlPlan = FormQueryPlan(db, sqlExpr, sqlFromSet, null);
// The top should always be a SubsetNode,
if (sqlPlan is SubsetNode)
{
var subsetNode = (SubsetNode)sqlPlan;
subsetNode.SetGivenName(sqlTable.AliasedName);
}
else
{
throw new Exception("Top plan is not a SubsetNode!");
}
tablePlanner.AddTableSource(sqlPlan, sqlTable);
}
else if (table is FromTableDirectSource)
{
// This represents a direct referencable table in the FROM clause
var dsTable = (FromTableDirectSource)table;
IQueryPlanNode dsPlan = dsTable.CreateFetchQueryPlanNode();
tablePlanner.AddTableSource(dsPlan, dsTable);
}
else
{
throw new Exception("Unknown table source instance: " + table.GetType());
}
}
return(tablePlanner);
}
private static ObjectName ResolveGroupMax(TableSelectExpression expression, TableExpressionFromSet fromSet)
{
// Resolve GROUP MAX variable to a reference in this from set
ObjectName groupmaxColumn = expression.GroupMax;
if (groupmaxColumn != null)
{
ObjectName v = fromSet.ResolveReference(groupmaxColumn);
if (v == null)
{
throw new ApplicationException("Could find GROUP MAX reference '" + groupmaxColumn + "'");
}
groupmaxColumn = v;
}
return(groupmaxColumn);
}
private static QuerySelectColumnSet BuildColumnSet(TableSelectExpression expression, TableExpressionFromSet fromSet)
{
// What we are selecting
var columnSet = new QuerySelectColumnSet(fromSet);
// The list of columns being selected.
ICollection <SelectColumn> columns = expression.Columns;
// For each column being selected
foreach (SelectColumn col in columns)
{
// Is this a glob? (eg. Part.* )
if (col.IsGlob)
{
// Find the columns globbed and add to the 'selectedColumns' result.
if (col.IsAll)
{
columnSet.SelectAllColumnsFromAllSources();
}
else
{
// Otherwise the glob must be of the form '[table name].*'
string tname = col.GlobPrefix;
ObjectName tn = ObjectName.Parse(tname);
columnSet.SelectAllColumnsFromSource(tn);
}
}
else
{
// Otherwise must be a standard column reference.
columnSet.SelectSingleColumn(col);
}
} // for each column selected
return(columnSet);
}
/// <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>
/// <returns></returns>
public static IQueryPlanNode FormQueryPlan(IDatabaseConnection db, TableSelectExpression expression, TableExpressionFromSet fromSet)
{
return(FormQueryPlan(db, expression, fromSet, new List <ByColumn>()));
}
/// <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>
/// <returns></returns>
public static IQueryPlanNode FormQueryPlan(IDatabaseConnection db, TableSelectExpression expression, TableExpressionFromSet fromSet)
{
return FormQueryPlan(db, expression, fromSet, new List<ByColumn>());
}
public ExpressionPreparerImpl(IDatabaseConnection db, TableExpressionFromSet fromSet)
{
this.db = db;
this.fromSet = fromSet;
}
private static QueryTableSetPlanner SetupPlanners(IDatabaseConnection db, TableExpressionFromSet fromSet)
{
// Set up plans for each table in the from clause of the command. For
// sub-queries, we recurse.
var tablePlanner = new QueryTableSetPlanner();
for (int i = 0; i < fromSet.SetCount; ++i) {
IFromTableSource table = fromSet.GetTable(i);
if (table is FromTableSubQuerySource) {
// This represents a sub-command in the FROM clause
var sqlTable = (FromTableSubQuerySource)table;
TableSelectExpression sqlExpr = sqlTable.TableExpression;
TableExpressionFromSet sqlFromSet = sqlTable.FromSet;
// Form a plan for evaluating the sub-command FROM
IQueryPlanNode sqlPlan = FormQueryPlan(db, sqlExpr, sqlFromSet, null);
// The top should always be a SubsetNode,
if (sqlPlan is SubsetNode) {
var subsetNode = (SubsetNode)sqlPlan;
subsetNode.SetGivenName(sqlTable.AliasedName);
} else {
throw new Exception("Top plan is not a SubsetNode!");
}
tablePlanner.AddTableSource(sqlPlan, sqlTable);
} else if (table is FromTableDirectSource) {
// This represents a direct referencable table in the FROM clause
var dsTable = (FromTableDirectSource)table;
IQueryPlanNode dsPlan = dsTable.CreateFetchQueryPlanNode();
tablePlanner.AddTableSource(dsPlan, dsTable);
} else {
throw new Exception("Unknown table source instance: " + table.GetType());
}
}
return tablePlanner;
}
/// <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;
}
private static ObjectName ResolveGroupMax(TableSelectExpression expression, TableExpressionFromSet fromSet)
{
// Resolve GROUP MAX variable to a reference in this from set
ObjectName groupmaxColumn = expression.GroupMax;
if (groupmaxColumn != null) {
ObjectName v = fromSet.ResolveReference(groupmaxColumn);
if (v == null)
throw new ApplicationException("Could find GROUP MAX reference '" + groupmaxColumn + "'");
groupmaxColumn = v;
}
return groupmaxColumn;
}
private static int ResolveGroupBy(TableSelectExpression expression, TableExpressionFromSet fromSet, IQueryContext context, out ObjectName[] groupByList, out IList<Expression> groupByFunctions)
{
// Any GROUP BY functions,
groupByFunctions = new List<Expression>();
// Resolve the GROUP BY variable list references in this from set
IList<ByColumn> groupListIn = expression.GroupBy;
int gsz = groupListIn.Count;
groupByList = new ObjectName[gsz];
for (int i = 0; i < gsz; ++i) {
ByColumn byColumn = groupListIn[i];
Expression exp = byColumn.Expression;
// Prepare the group by expression
exp.Prepare(fromSet.ExpressionQualifier);
// Is the group by variable a complex expression?
ObjectName v = exp.AsVariable();
Expression groupByExpression;
if (v == null) {
groupByExpression = exp;
} else {
// Can we dereference the variable to an expression in the SELECT?
groupByExpression = fromSet.DereferenceAssignment(v);
}
if (groupByExpression != null) {
if (groupByExpression.HasAggregateFunction(context)) {
throw new ApplicationException("Aggregate expression '" + groupByExpression + "' is not allowed in GROUP BY clause.");
}
// Complex expression so add this to the function list.
int groupByFunNum = groupByFunctions.Count;
groupByFunctions.Add(groupByExpression);
v = new ObjectName(GroupByFunctionTable, "#GROUPBY-" + groupByFunNum);
}
groupByList[i] = v;
}
return gsz;
}
/// <summary>
/// Prepares the given SearchExpression object.
/// </summary>
/// <param name="db"></param>
/// <param name="fromSet"></param>
/// <param name="expression"></param>
/// <remarks>
/// This goes through each element of the expression. If the
/// element is a variable it is qualified.
/// If the element is a <see cref="TableSelectExpression"/> it's
/// converted to a <see cref="SelectStatement"/> object and prepared.
/// </remarks>
private static FilterExpression PrepareSearchExpression(IDatabaseConnection db, TableExpressionFromSet fromSet, FilterExpression 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 ExpressionPreparerImpl(db, fromSet));
// Then qualify all the variables. Note that this will not qualify
// variables in the sub-queries.
expression = expression.Prepare(fromSet.ExpressionQualifier);
return expression;
}
private static FilterExpression PrepareJoins(QueryTableSetPlanner tablePlanner, TableSelectExpression expression, TableExpressionFromSet fromSet, FilterExpression whereClause)
{
// Look at the join set and resolve the ON Expression to this statement
JoiningSet joinSet = expression.From.JoinSet;
var result = whereClause;
// Perform a quick scan and see if there are any outer joins in the
// expression.
bool allInnerJoins = true;
for (int i = 0; i < joinSet.TableCount - 1; ++i) {
JoinType type = joinSet.GetJoinType(i);
if (type != JoinType.Inner)
allInnerJoins = false;
}
// Prepare the joins
for (int i = 0; i < joinSet.TableCount - 1; ++i) {
JoinType type = joinSet.GetJoinType(i);
Expression onExpression = joinSet.GetOnExpression(i);
if (allInnerJoins) {
// If the whole join set is inner joins then simply move the on
// expression (if there is one) to the WHERE clause.
if (onExpression != null) {
result = result.Append(onExpression);
}
} else {
// Not all inner joins,
if (type == 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 Exception("No ON expression in join.");
// Resolve the on_expression
onExpression = onExpression.Prepare(fromSet.ExpressionQualifier);
// And set it in the planner
tablePlanner.SetJoinInfoBetweenSources(i, type, onExpression);
}
}
}
return result;
}
public ExpressionPreparerImpl(IDatabaseConnection db, TableExpressionFromSet fromSet)
{
this.db = db;
this.fromSet = fromSet;
}
private static FilterExpression PrepareJoins(QueryTableSetPlanner tablePlanner, TableSelectExpression expression, TableExpressionFromSet fromSet, FilterExpression whereClause)
{
// Look at the join set and resolve the ON Expression to this statement
JoiningSet joinSet = expression.From.JoinSet;
var result = whereClause;
// Perform a quick scan and see if there are any outer joins in the
// expression.
bool allInnerJoins = true;
for (int i = 0; i < joinSet.TableCount - 1; ++i)
{
JoinType type = joinSet.GetJoinType(i);
if (type != JoinType.Inner)
{
allInnerJoins = false;
}
}
// Prepare the joins
for (int i = 0; i < joinSet.TableCount - 1; ++i)
{
JoinType type = joinSet.GetJoinType(i);
Expression onExpression = joinSet.GetOnExpression(i);
if (allInnerJoins)
{
// If the whole join set is inner joins then simply move the on
// expression (if there is one) to the WHERE clause.
if (onExpression != null)
{
result = result.Append(onExpression);
}
}
else
{
// Not all inner joins,
if (type == 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 Exception("No ON expression in join.");
}
// Resolve the on_expression
onExpression = onExpression.Prepare(fromSet.ExpressionQualifier);
// And set it in the planner
tablePlanner.SetJoinInfoBetweenSources(i, type, onExpression);
}
}
}
return(result);
}
// 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 QuerySelectColumnSet(TableExpressionFromSet fromSet)
{
this.fromSet = fromSet;
SelectedColumns = new List<SelectColumn>();
FunctionColumns = new List<SelectColumn>();
}
/// <summary>
/// Prepares the given SearchExpression object.
/// </summary>
/// <param name="db"></param>
/// <param name="fromSet"></param>
/// <param name="expression"></param>
/// <remarks>
/// This goes through each element of the expression. If the
/// element is a variable it is qualified.
/// If the element is a <see cref="TableSelectExpression"/> it's
/// converted to a <see cref="SelectStatement"/> object and prepared.
/// </remarks>
private static FilterExpression PrepareSearchExpression(IDatabaseConnection db, TableExpressionFromSet fromSet, FilterExpression 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 ExpressionPreparerImpl(db, fromSet));
// Then qualify all the variables. Note that this will not qualify
// variables in the sub-queries.
expression = expression.Prepare(fromSet.ExpressionQualifier);
return(expression);
}
internal static TableExpressionFromSet GenerateFromSet(TableSelectExpression selectExpression, ITableSpaceContext db)
{
// Get the 'from_clause' from the table expression
FromClause fromClause = selectExpression.From;
// Create a TableExpressionFromSet for this table expression
var fromSet = new TableExpressionFromSet(db.IsInCaseInsensitive);
// Add all tables from the 'fromClause'
foreach (FromTable fromTable in fromClause.AllTables) {
string uniqueKey = fromTable.UniqueKey;
ObjectName alias = fromTable.Alias;
// If this is a sub-command table,
if (fromTable.IsSubQueryTable) {
// eg. FROM ( SELECT id FROM Part )
TableSelectExpression subQuery = fromTable.SubSelect;
TableExpressionFromSet subQueryFromSet = GenerateFromSet(subQuery, db);
// The aliased name of the table
ObjectName aliasTableName = null;
if (alias != null)
aliasTableName = alias.Clone();
var source = new FromTableSubQuerySource(db.IsInCaseInsensitive, uniqueKey, subQuery, subQueryFromSet, aliasTableName);
// Add to list of subquery tables to add to command,
fromSet.AddTable(source);
} else {
// Else must be a standard command table,
ObjectName name = fromTable.Name;
// Resolve to full table name
ObjectName tableName = db.ResolveTableName(name);
if (!db.TableExists(tableName))
throw new ApplicationException("Table '" + tableName + "' was not found.");
ObjectName givenName = null;
if (alias != null)
givenName = alias.Clone();
// Get the ITableQueryInfo object for this table name (aliased).
ITableQueryInfo tableQueryInfo = db.GetTableQueryInfo(tableName, givenName);
var source = new FromTableDirectSource(db.IsInCaseInsensitive, tableQueryInfo, uniqueKey, givenName, tableName);
fromSet.AddTable(source);
}
} // foreach
// Set up functions, aliases and exposed variables for this from set,
// For each column being selected
foreach (SelectColumn col in selectExpression.Columns) {
// Is this a glob? (eg. Part.* )
if (col.IsGlob) {
// Find the columns globbed and add to the 'selectedColumns' result.
if (col.IsAll) {
fromSet.ExposeAllColumns();
} else {
// Otherwise the glob must be of the form '[table name].*'
string tname = col.GlobPrefix;
ObjectName tn = ObjectName.Parse(tname);
fromSet.ExposeAllColumnsFromSource(tn);
}
} 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
// TableExpressionFromSet.
var varExpression = (VariableExpression)col.Expression;
ObjectName alias = col.Alias;
ObjectName v = varExpression.VariableName;
bool aliasMatchV = (v != null && alias != null && fromSet.StringCompare(v.Name, alias.Name));
if (alias != null && !aliasMatchV) {
fromSet.AddFunctionRef(alias.Name, col.Expression);
fromSet.ExposeVariable(alias.Clone());
} else if (v != null) {
ObjectName resolved = fromSet.ResolveReference(v);
fromSet.ExposeVariable(resolved ?? v);
} else {
string funName = col.Expression.ToString();
fromSet.AddFunctionRef(funName, col.Expression);
fromSet.ExposeVariable(ObjectName.Parse(funName));
}
}
} // for each column selected
return fromSet;
}
/// <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);
}
public ExpressionPreparerImpl(TableExpressionFromSet fromSet)
{
this.fromSet = fromSet;
}
/// <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);
}
private static QuerySelectColumnSet BuildColumnSet(TableSelectExpression expression, TableExpressionFromSet fromSet)
{
// What we are selecting
var columnSet = new QuerySelectColumnSet(fromSet);
// The list of columns being selected.
ICollection<SelectColumn> columns = expression.Columns;
// For each column being selected
foreach (SelectColumn col in columns) {
// Is this a glob? (eg. Part.* )
if (col.IsGlob) {
// Find the columns globbed and add to the 'selectedColumns' result.
if (col.IsAll) {
columnSet.SelectAllColumnsFromAllSources();
} else {
// Otherwise the glob must be of the form '[table name].*'
string tname = col.GlobPrefix;
ObjectName tn = ObjectName.Parse(tname);
columnSet.SelectAllColumnsFromSource(tn);
}
} else {
// Otherwise must be a standard column reference.
columnSet.SelectSingleColumn(col);
}
} // for each column selected
return columnSet;
}
public ExpressionPreparerImpl(TableExpressionFromSet fromSet)
{
this.fromSet = fromSet;
}
/// <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;
}
internal static TableExpressionFromSet GenerateFromSet(TableSelectExpression selectExpression, ITableSpaceContext db)
{
// Get the 'from_clause' from the table expression
FromClause fromClause = selectExpression.From;
// Create a TableExpressionFromSet for this table expression
var fromSet = new TableExpressionFromSet(db.IsInCaseInsensitive);
// Add all tables from the 'fromClause'
foreach (FromTable fromTable in fromClause.AllTables)
{
string uniqueKey = fromTable.UniqueKey;
ObjectName alias = fromTable.Alias;
// If this is a sub-command table,
if (fromTable.IsSubQueryTable)
{
// eg. FROM ( SELECT id FROM Part )
TableSelectExpression subQuery = fromTable.SubSelect;
TableExpressionFromSet subQueryFromSet = GenerateFromSet(subQuery, db);
// The aliased name of the table
ObjectName aliasTableName = null;
if (alias != null)
{
aliasTableName = alias.Clone();
}
var source = new FromTableSubQuerySource(db.IsInCaseInsensitive, uniqueKey, subQuery, subQueryFromSet, aliasTableName);
// Add to list of subquery tables to add to command,
fromSet.AddTable(source);
}
else
{
// Else must be a standard command table,
ObjectName name = fromTable.Name;
// Resolve to full table name
ObjectName tableName = db.ResolveTableName(name);
if (!db.TableExists(tableName))
{
throw new ApplicationException("Table '" + tableName + "' was not found.");
}
ObjectName givenName = null;
if (alias != null)
{
givenName = alias.Clone();
}
// Get the ITableQueryInfo object for this table name (aliased).
ITableQueryInfo tableQueryInfo = db.GetTableQueryInfo(tableName, givenName);
var source = new FromTableDirectSource(db.IsInCaseInsensitive, tableQueryInfo, uniqueKey, givenName, tableName);
fromSet.AddTable(source);
}
} // foreach
// Set up functions, aliases and exposed variables for this from set,
// For each column being selected
foreach (SelectColumn col in selectExpression.Columns)
{
// Is this a glob? (eg. Part.* )
if (col.IsGlob)
{
// Find the columns globbed and add to the 'selectedColumns' result.
if (col.IsAll)
{
fromSet.ExposeAllColumns();
}
else
{
// Otherwise the glob must be of the form '[table name].*'
string tname = col.GlobPrefix;
ObjectName tn = ObjectName.Parse(tname);
fromSet.ExposeAllColumnsFromSource(tn);
}
}
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
// TableExpressionFromSet.
var varExpression = (VariableExpression)col.Expression;
ObjectName alias = col.Alias;
ObjectName v = varExpression.VariableName;
bool aliasMatchV = (v != null && alias != null && fromSet.StringCompare(v.Name, alias.Name));
if (alias != null && !aliasMatchV)
{
fromSet.AddFunctionRef(alias.Name, col.Expression);
fromSet.ExposeVariable(alias.Clone());
}
else if (v != null)
{
ObjectName resolved = fromSet.ResolveReference(v);
fromSet.ExposeVariable(resolved ?? v);
}
else
{
string funName = col.Expression.ToString();
fromSet.AddFunctionRef(funName, col.Expression);
fromSet.ExposeVariable(ObjectName.Parse(funName));
}
}
} // for each column selected
return(fromSet);
}
// 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 QuerySelectColumnSet(TableExpressionFromSet fromSet)
{
this.fromSet = fromSet;
SelectedColumns = new List <SelectColumn>();
FunctionColumns = new List <SelectColumn>();
}
