public string Build(SelectExpression selectExpression, QueryContext queryContext)
{
// A scope usually has:
// - a SELECT: the operation creating a CLR object with data coming from SQL tier
// - a FROM: list of tables
// - a WHERE: list of conditions
// - a GROUP BY: grouping by selected columns
// - a ORDER BY: sort
var from = BuildFrom(selectExpression.Tables, queryContext);
var where = BuildWhere(selectExpression.Tables, selectExpression.Where, queryContext);
var select = BuildSelect(selectExpression, queryContext);
var groupBy = BuildGroupBy(selectExpression.Group, queryContext);
var having = BuildHaving(selectExpression.Where, queryContext);
var orderBy = BuildOrderBy(selectExpression.OrderBy, queryContext);
select = Join(queryContext, select, from, where, groupBy, having, orderBy);
select = BuildLimit(selectExpression, select, queryContext);
if (selectExpression.NextSelectExpression != null)
{
var nextLiteralSelect = Build(selectExpression.NextSelectExpression, queryContext);
select = queryContext.DataContext.Vendor.SqlProvider.GetLiteral(
selectExpression.NextSelectExpressionOperator,
select, nextLiteralSelect);
}
return select;
}
public SelectExpression(SelectExpression parentSelectExpression)
: base(ExpressionType, null, null)
{
Parent = parentSelectExpression;
// Tables and columns are empty, since the table/column lookup recurses to parentScopePiece
Tables = new List<TableExpression>();
Columns = new List<ColumnExpression>();
// Local clauses
Where = new List<Expression>();
OrderBy = new List<OrderByExpression>();
Group = new List<GroupExpression>();
}
public override SelectExpression OuterExpression(SelectExpression e)
{
// Check for (from f in foo orderby f.Field select f).Count() trees
// Firebird doesn't support 'ORDER BY' for 'SELECT COUNT(*)'.
if (e.Operands.Select(o => o as SpecialExpression)
.Where(o => o != null)
.Where(s => s.SpecialNodeType == SpecialExpressionType.Count)
.Any())
{
e.OrderBy.Clear();
}
return e;
}
/// <summary>
/// Returns a list of sorted tables, given a select expression.
/// The tables are sorted by dependency: independent tables first, dependent tables next
/// </summary>
/// <param name="selectExpression"></param>
/// <returns></returns>
protected IList<TableExpression> GetSortedTables(SelectExpression selectExpression)
{
var tables = new List<TableExpression>();
foreach (var table in selectExpression.Tables)
{
// the rules are:
// a table climbs up to 0 until we find the table it depends on
// we keep the index and insert on it
// we place joining tables under joined tables
int tableIndex;
for (tableIndex = tables.Count; tableIndex > 0; tableIndex--)
{
// above us, the joined table? Stop now
if (tables[tableIndex - 1] == table.JoinedTable)
break;
// if the current table is joining and we have a non-joining table above, we stop here too
if (table.JoinExpression != null && tables[tableIndex - 1].JoinExpression == null)
break;
}
tables.Insert(tableIndex, table);
}
return tables;
}
/// <summary>
/// Find the common ancestor between two ScopeExpressions
/// </summary>
/// <param name="a"></param>
/// <param name="b"></param>
/// <returns></returns>
protected virtual SelectExpression FindCommonScope(SelectExpression a, SelectExpression b)
{
for (var aScope = a; aScope != null; aScope = aScope.Parent)
{
for (var bScope = b; bScope != null; bScope = bScope.Parent)
{
if (aScope == bScope)
return aScope;
}
}
throw Error.BadArgument("S0127: No common ScopeExpression found");
}
/// <summary>
/// Main SQL builder
/// </summary>
/// <param name="selectExpression"></param>
/// <param name="queryContext"></param>
/// <returns></returns>
public SqlStatement Build(SelectExpression selectExpression, QueryContext queryContext)
{
var translator = GetTranslator(queryContext.DataContext.Vendor.SqlProvider);
var sqlProvider = queryContext.DataContext.Vendor.SqlProvider;
selectExpression = translator.OuterExpression(selectExpression);
// A scope usually has:
// - a SELECT: the operation creating a CLR object with data coming from SQL tier
// - a FROM: list of tables
// - a WHERE: list of conditions
// - a GROUP BY: grouping by selected columns
// - a ORDER BY: sort
var select = BuildSelect(selectExpression, queryContext);
if (select.ToString() == string.Empty)
{
SubSelectExpression subselect = null;
if (selectExpression.Tables.Count == 1)
subselect = selectExpression.Tables[0] as SubSelectExpression;
if(subselect != null)
return sqlProvider.GetParenthesis(Build(subselect.Select, queryContext));
}
// TODO: the following might be wrong (at least this might be the wrong place to do this
if (select.ToString() == string.Empty)
select = new SqlStatement("SELECT " + sqlProvider.GetLiteral(null) + " AS " + sqlProvider.GetSafeName("Empty"));
var tables = GetSortedTables(selectExpression);
var from = BuildFrom(tables, queryContext);
var join = BuildJoin(tables, queryContext);
var where = BuildWhere(tables, selectExpression.Where, queryContext);
var groupBy = BuildGroupBy(selectExpression.Group, queryContext);
var having = BuildHaving(selectExpression.Where, queryContext);
var orderBy = BuildOrderBy(selectExpression.OrderBy, queryContext);
select = Join(queryContext, select, from, join, where, groupBy, having, orderBy);
select = BuildLimit(selectExpression, select, queryContext);
if (selectExpression.NextSelectExpression != null)
{
var nextLiteralSelect = Build(selectExpression.NextSelectExpression, queryContext);
select = queryContext.DataContext.Vendor.SqlProvider.GetLiteral(
selectExpression.NextSelectExpressionOperator,
select, nextLiteralSelect);
}
return select;
}
protected virtual SqlStatement BuildLimit(SelectExpression select, SqlStatement literalSelect, QueryContext queryContext)
{
if (select.Limit != null)
{
var literalLimit = BuildExpression(select.Limit, queryContext);
if (select.Offset != null)
{
var literalOffset = BuildExpression(select.Offset, queryContext);
var literalOffsetAndLimit = BuildExpression(select.OffsetAndLimit, queryContext);
return queryContext.DataContext.Vendor.SqlProvider.GetLiteralLimit(literalSelect, literalLimit,
literalOffset,
literalOffsetAndLimit);
}
return queryContext.DataContext.Vendor.SqlProvider.GetLiteralLimit(literalSelect, literalLimit);
}
return literalSelect;
}
/// <summary>
/// Find the common ancestor between two ScopeExpressions
/// </summary>
/// <param name="a"></param>
/// <param name="b"></param>
/// <returns></returns>
protected virtual SelectExpression FindCommonScope(SelectExpression a, SelectExpression b)
{
for (var aScope = a; aScope != null; aScope = aScope.Parent)
{
for (var bScope = b; bScope != null; bScope = bScope.Parent)
{
if (aScope == bScope)
return aScope;
}
}
return null;
}
protected override Expression Mutate2(IList<Expression> newOperands)
{
Type type;
if (newOperands.Count > 0)
type = newOperands[0].Type;
else
type = Type;
var scopeExpression = new SelectExpression(type, newOperands);
scopeExpression.Tables = Tables;
scopeExpression.Columns = Columns;
scopeExpression.Where = Where;
scopeExpression.OrderBy = OrderBy;
scopeExpression.Group = Group;
scopeExpression.Parent = Parent;
scopeExpression.ExecuteMethodName = ExecuteMethodName;
scopeExpression.Reader = Reader;
scopeExpression.Limit = Limit;
scopeExpression.Offset = Offset;
scopeExpression.OffsetAndLimit = OffsetAndLimit;
scopeExpression.NextSelectExpression = NextSelectExpression;
scopeExpression.NextSelectExpressionOperator = NextSelectExpressionOperator;
return scopeExpression;
}
/// <summary>
/// Creates a new BuilderContext with a new query scope which is parent of the current one
/// </summary>
/// <returns></returns>
public void NewParentSelect()
{
SelectExpression currentSelect = this.CurrentSelect;
SelectExpression newParentSelect = new SelectExpression(currentSelect.Parent);
while (currentSelect != null)
{
currentSelect.Parent = newParentSelect;
currentSelect = currentSelect.NextSelectExpression;
}
this.currentScopeIndex = SelectExpressions.Count;
SelectExpressions.Add(newParentSelect);
}
/// <summary>
/// Translate the entire (outermost) expression.
/// </summary>
/// <param name="e">
/// A <see cref="SelectExpression" /> containing the expression to
/// translate.
/// </param>
/// <returns>
/// The <see cref="SelectExpression" /> to use for SQL generation.
/// </returns>
/// <remarks>
/// <para>
/// Derived classes can override this method to manipulate the
/// entire expression prior to SQL generation.
/// </para>
/// <para>
/// The default implementation returns <c>e</c> unchanged.
/// </para>
/// </remarks>
public virtual SelectExpression OuterExpression(SelectExpression e)
{
return e;
}
public SubSelectExpression(SelectExpression select, Type type, string alias)
: base(type, alias)
{
this.Select = select;
this.Alias = alias;
}
public SubSelectExpression(SelectExpression select, Type type, string alias)
: base(type, alias)
{
this.Select = select;
this.Alias = alias;
}