/// <summary>
/// This is called when two plans are merged together to set
/// up the left and right join information for the new plan.
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <remarks>
/// This sets the left join info from the left plan and the
/// right join info from the right plan.
/// </remarks>
public void SetJoinInfoMergedBetween(PlanTableSource left, PlanTableSource right)
{
if (left.RightPlan != right)
{
if (left.RightPlan != null)
{
SetRightJoinInfo(left.RightPlan, left.RightJoinType, left.RightOnExpression);
RightPlan.LeftPlan = this;
}
if (right.LeftPlan != null)
{
SetLeftJoinInfo(right.LeftPlan, right.LeftJoinType, right.LeftOnExpression);
LeftPlan.RightPlan = this;
}
}
if (left.LeftPlan != right)
{
if (LeftPlan == null && left.LeftPlan != null)
{
SetLeftJoinInfo(left.LeftPlan, left.LeftJoinType, left.LeftOnExpression);
LeftPlan.RightPlan = this;
}
if (RightPlan == null && right.RightPlan != null)
{
SetRightJoinInfo(right.RightPlan, right.RightJoinType, right.RightOnExpression);
RightPlan.LeftPlan = this;
}
}
}
/// <summary>
/// Naturally joins two <see cref="PlanTableSource"/> objects in this planner.
/// </summary>
/// <param name="plan1"></param>
/// <param name="plan2"></param>
/// <remarks>
/// When this method returns the actual plans will be joined together. This method
/// modifies <see cref="tableList"/>.
/// </remarks>
/// <returns></returns>
private PlanTableSource NaturallyJoinPlans(PlanTableSource plan1, PlanTableSource plan2)
{
JoinType joinType;
Expression onExpr;
PlanTableSource leftPlan, rightPlan;
// Are the plans linked by common join information?
if (plan1.RightPlan == plan2) {
joinType = plan1.RightJoinType;
onExpr = plan1.RightOnExpression;
leftPlan = plan1;
rightPlan = plan2;
} else if (plan1.LeftPlan == plan2) {
joinType = plan1.LeftJoinType;
onExpr = plan1.LeftOnExpression;
leftPlan = plan2;
rightPlan = plan1;
} else {
// Assertion - make sure no join clashes!
if ((plan1.LeftPlan != null && plan2.LeftPlan != null) ||
(plan1.RightPlan != null && plan2.RightPlan != null)) {
throw new Exception(
"Assertion failed - plans can not be naturally join because " +
"the left/right join plans clash.");
}
// Else we must assume a non-dependant join (not an outer join).
// Perform a natural join
IQueryPlanNode node1 = new NaturalJoinNode(plan1.Plan, plan2.Plan);
return MergeTables(plan1, plan2, node1);
}
// This means plan1 and plan2 are linked by a common join and ON
// expression which we evaluate now.
bool outerJoin;
if (joinType == JoinType.Left) {
// Mark the left plan
leftPlan.UpdatePlan(new MarkerNode(leftPlan.Plan, "OUTER_JOIN"));
outerJoin = true;
} else if (joinType == JoinType.Right) {
// Mark the right plan
rightPlan.UpdatePlan(new MarkerNode(rightPlan.Plan, "OUTER_JOIN"));
outerJoin = true;
} else if (joinType == JoinType.Inner) {
// Inner join with ON expression
outerJoin = false;
} else {
throw new Exception("Join type (" + joinType + ") is not supported.");
}
// Make a Planner object for joining these plans.
var planner = new QueryTableSetPlanner();
planner.AddPlanTableSource(leftPlan.Copy());
planner.AddPlanTableSource(rightPlan.Copy());
// Evaluate the on expression
IQueryPlanNode node = planner.LogicalEvaluate(onExpr);
// If outer join add the left outer join node
if (outerJoin)
node = new LeftOuterJoinNode(node, "OUTER_JOIN");
// And merge the plans in this set with the new node.
return MergeTables(plan1, plan2, node);
}
/// <summary>
/// Joins two tables when a plan is generated for joining the two tables.
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="mergePlan"></param>
/// <returns></returns>
private PlanTableSource MergeTables(PlanTableSource left, PlanTableSource right, IQueryPlanNode mergePlan)
{
// Remove the sources from the table list.
tableList.Remove(left);
tableList.Remove(right);
// Add the concatenation of the left and right tables.
PlanTableSource cPlan = ConcatTableSources(left, right, mergePlan);
cPlan.SetJoinInfoMergedBetween(left, right);
cPlan.SetUpdated();
AddPlanTableSource(cPlan);
// Return the name plan
return cPlan;
}
/// <summary>
/// Returns the index of the given <see cref="PlanTableSource"/> in the
/// table list.
/// </summary>
/// <param name="source"></param>
/// <returns></returns>
private int IndexOfPlanTableSource(PlanTableSource source)
{
int sz = tableList.Count;
for (int i = 0; i < sz; ++i) {
if (tableList[i] == source)
return i;
}
return -1;
}
/// <summary>
/// Add a <see cref="PlanTableSource"/> to this planner.
/// </summary>
/// <param name="source"></param>
private void AddPlanTableSource(PlanTableSource source)
{
tableList.Add(source);
HasJoinOccured = true;
}
/// <summary>
/// Forms a new PlanTableSource that's the concatination of the given two
/// <see cref="PlanTableSource"/> objects.
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <param name="plan"></param>
/// <returns></returns>
private static PlanTableSource ConcatTableSources(PlanTableSource left, PlanTableSource right, IQueryPlanNode plan)
{
// Merge the variable list
var newVarList = new ObjectName[left.VariableNames.Length + right.VariableNames.Length];
int i = 0;
foreach (ObjectName v in left.VariableNames) {
newVarList[i] = v;
++i;
}
foreach (ObjectName v in right.VariableNames) {
newVarList[i] = v;
++i;
}
// Merge the unique table names list
var newUniqueList = new string[left.UniqueNames.Length + right.UniqueNames.Length];
i = 0;
foreach (string uniqueName in left.UniqueNames) {
newUniqueList[i] = uniqueName;
++i;
}
foreach (string uniqueName in right.UniqueNames) {
newUniqueList[i] = uniqueName;
++i;
}
// Return the new table source plan.
return new PlanTableSource(plan, newVarList, newUniqueList);
}
/// <summary>
/// Returns true if it is possible to naturally join the two plans.
/// </summary>
/// <param name="plan1"></param>
/// <param name="plan2"></param>
/// <remarks>
/// Two plans can be joined under the following sitations:
/// <list type="number">
/// <item>The left or right plan of the first source points
/// to the second source.</item>
/// <item>Either one has no left plan and the other has no
/// right plan, or one has no right plan and the other has
/// no left plan.</item>
/// </list>
/// </remarks>
/// <returns></returns>
private static int CanPlansBeNaturallyJoined(PlanTableSource plan1, PlanTableSource plan2)
{
if (plan1.LeftPlan == plan2 || plan1.RightPlan == plan2)
return 0;
if (plan1.LeftPlan != null && plan2.LeftPlan != null)
// This is a left clash
return 2;
if (plan1.RightPlan != null && plan2.RightPlan != null)
// This is a right clash
return 1;
if ((plan1.LeftPlan == null && plan2.RightPlan == null) ||
(plan1.RightPlan == null && plan2.LeftPlan == null))
// This means a merge between the plans is fine
return 0;
// Must be a left and right clash
return 2;
}
/// <summary>
/// Adds a single var plan to the given list.
/// </summary>
/// <param name="list"></param>
/// <param name="table"></param>
/// <param name="variable"></param>
/// <param name="singleVar"></param>
/// <param name="expParts"></param>
/// <param name="op"></param>
private static void AddSingleVarPlanTo(IList<SingleVarPlan> list, PlanTableSource table, ObjectName variable, ObjectName singleVar, Expression[] expParts, Operator op)
{
var exp = Expression.Binary(expParts[0], op.AsExpressionType(), expParts[1]);
// Is this source in the list already?
foreach (SingleVarPlan plan1 in list) {
if (plan1.TableSource == table &&
(variable == null || plan1.Variable.Equals(variable))) {
// Append to end of current expression
plan1.Variable = variable;
plan1.Expression = Expression.And(plan1.Expression, exp);
return;
}
}
// Didn't find so make a new entry in the list.
SingleVarPlan plan = new SingleVarPlan();
plan.TableSource = table;
plan.Variable = variable;
plan.SingleVariable = singleVar;
plan.Expression = exp;
list.Add(plan);
return;
}
/// <summary>
/// Sets the left join information for this plan.
/// </summary>
/// <param name="left"></param>
/// <param name="joinType"></param>
/// <param name="onExpression"></param>
public void SetLeftJoinInfo(PlanTableSource left, JoinType joinType, Expression onExpression)
{
LeftPlan = left;
LeftJoinType = joinType;
LeftOnExpression = onExpression;
}
/// <summary>
/// Sets the right join information for this plan.
/// </summary>
/// <param name="right"></param>
/// <param name="joinType"></param>
/// <param name="onExpression"></param>
public void SetRightJoinInfo(PlanTableSource right, JoinType joinType, Expression onExpression)
{
RightPlan = right;
RightJoinType = joinType;
RightOnExpression = onExpression;
}
/// <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);
}
/// <summary>
/// Sets the right join information for this plan.
/// </summary>
/// <param name="right"></param>
/// <param name="joinType"></param>
/// <param name="onExpression"></param>
public void SetRightJoinInfo(PlanTableSource right, JoinType joinType, Expression onExpression)
{
RightPlan = right;
RightJoinType = joinType;
RightOnExpression = onExpression;
}
/// <summary>
/// Sets the left join information for this plan.
/// </summary>
/// <param name="left"></param>
/// <param name="joinType"></param>
/// <param name="onExpression"></param>
public void SetLeftJoinInfo(PlanTableSource left, JoinType joinType, Expression onExpression)
{
LeftPlan = left;
LeftJoinType = joinType;
LeftOnExpression = onExpression;
}
/// <summary>
/// This is called when two plans are merged together to set
/// up the left and right join information for the new plan.
/// </summary>
/// <param name="left"></param>
/// <param name="right"></param>
/// <remarks>
/// This sets the left join info from the left plan and the
/// right join info from the right plan.
/// </remarks>
public void SetJoinInfoMergedBetween(PlanTableSource left, PlanTableSource right)
{
if (left.RightPlan != right) {
if (left.RightPlan != null) {
SetRightJoinInfo(left.RightPlan, left.RightJoinType, left.RightOnExpression);
RightPlan.LeftPlan = this;
}
if (right.LeftPlan != null) {
SetLeftJoinInfo(right.LeftPlan, right.LeftJoinType, right.LeftOnExpression);
LeftPlan.RightPlan = this;
}
}
if (left.LeftPlan != right) {
if (LeftPlan == null && left.LeftPlan != null) {
SetLeftJoinInfo(left.LeftPlan, left.LeftJoinType, left.LeftOnExpression);
LeftPlan.RightPlan = this;
}
if (RightPlan == null && right.RightPlan != null) {
SetRightJoinInfo(right.RightPlan, right.RightJoinType, right.RightOnExpression);
RightPlan.LeftPlan = this;
}
}
}