public void AddTableDeclaration(ObjectName tableName, TableSelectExpression select, ObjectName tableAlias)
{
// This is an inner select in the FROM clause
if (tableName == null && select != null)
{
if (tableAlias == null)
{
AddFromTable(null, new FromTable(select));
}
else
{
AddFromTable(tableAlias, new FromTable(select, tableAlias));
}
}
// This is a standard table reference in the FROM clause
else if (tableName != null && select == null)
{
if (tableAlias == null)
{
AddTable(tableName);
}
else
{
AddTable(tableName, tableAlias);
}
}
// Error
else
{
throw new ApplicationException("Unvalid declaration parameters.");
}
}
public FromTable(TableSelectExpression select, ObjectName tableAlias)
{
SubSelect = select;
Name = tableAlias;
Alias = tableAlias;
IsSubQueryTable = true;
}
public FromTable(TableSelectExpression select)
{
SubSelect = select;
Name = null;
Alias = null;
IsSubQueryTable = true;
}
public FromTable(TableSelectExpression select)
{
SubSelect = select;
Name = null;
Alias = null;
IsSubQueryTable = true;
}
public FromTable(TableSelectExpression select, ObjectName tableAlias)
{
SubSelect = select;
Name = tableAlias;
Alias = tableAlias;
IsSubQueryTable = true;
}
/// <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 TableSelectExpression Prepare(IExpressionPreparer preparer)
{
var selectExp = new TableSelectExpression {
GroupMax = GroupMax,
Distinct = Distinct,
CompositeFunction = CompositeFunction,
IsCompositeAll = IsCompositeAll
};
foreach (var column in Columns)
{
selectExp.Columns.Add(column.Prepare(preparer));
}
if (From != null)
{
selectExp.From = From.Prepare(preparer);
}
if (Into != null)
{
selectExp.Into = Into.Prepare(preparer);
}
if (whereClause != null)
{
selectExp.Where = whereClause.Prepare(preparer);
}
foreach (var column in GroupBy)
{
selectExp.GroupBy.Add(column.Prepare(preparer));
}
if (Having != null)
{
selectExp.Having = Having.Prepare(preparer);
}
if (NextComposite != null)
{
selectExp.NextComposite = NextComposite.Prepare(preparer);
}
return(selectExp);
}
public void ChainComposite(TableSelectExpression expression, CompositeFunction composite, bool isAll)
{
NextComposite = expression;
CompositeFunction = composite;
IsCompositeAll = isAll;
}
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>
/// <returns></returns>
public static IQueryPlanNode FormQueryPlan(IDatabaseConnection db, TableSelectExpression expression, TableExpressionFromSet fromSet)
{
return FormQueryPlan(db, expression, fromSet, new List<ByColumn>());
}
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 void AddTableDeclaration(ObjectName tableName, TableSelectExpression select, ObjectName tableAlias)
{
// This is an inner select in the FROM clause
if (tableName == null && select != null) {
if (tableAlias == null) {
AddFromTable(null, new FromTable(select));
} else {
AddFromTable(tableAlias, new FromTable(select, tableAlias));
}
}
// This is a standard table reference in the FROM clause
else if (tableName != null && select == null) {
if (tableAlias == null) {
AddTable(tableName);
} else {
AddTable(tableName, tableAlias);
}
}
// Error
else {
throw new ApplicationException("Unvalid declaration parameters.");
}
}
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 TableSelectExpression Prepare(IExpressionPreparer preparer)
{
var selectExp = new TableSelectExpression {
GroupMax = GroupMax,
Distinct = Distinct,
CompositeFunction = CompositeFunction,
IsCompositeAll = IsCompositeAll
};
foreach (var column in Columns) {
selectExp.Columns.Add(column.Prepare(preparer));
}
if (From != null)
selectExp.From = From.Prepare(preparer);
if (Into != null)
selectExp.Into = Into.Prepare(preparer);
if (whereClause != null)
selectExp.Where = whereClause.Prepare(preparer);
foreach (var column in GroupBy) {
selectExp.GroupBy.Add(column.Prepare(preparer));
}
if (Having != null)
selectExp.Having = Having.Prepare(preparer);
if (NextComposite != null)
selectExp.NextComposite = NextComposite.Prepare(preparer);
return selectExp;
}
public void ChainComposite(TableSelectExpression expression, CompositeFunction composite, bool isAll)
{
NextComposite = expression;
CompositeFunction = composite;
IsCompositeAll = isAll;
}
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>
/// 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;
}
