private FromTable(string tableName, SqlQueryExpression query, string alias)
{
Name = tableName;
SubQuery = query;
Alias = alias;
IsSubQuery = query != null;
}
public static void PrepareFromSqlQuery()
{
var sqlQuery = new SqlQuery("SELECT * FROM table1 WHERE a = ? AND b = ?", SqlQueryParameterNaming.Marker);
sqlQuery.Parameters.Add(new SqlQueryParameter(PrimitiveTypes.Integer(), (SqlNumber)2));
sqlQuery.Parameters.Add(new SqlQueryParameter(PrimitiveTypes.Integer(), (SqlNumber)1023));
var preparer = sqlQuery.ExpressionPreparer;
var queryExp = new SqlQueryExpression();
queryExp.From.Table(new ObjectName("table1"));
queryExp.Items.Add(SqlExpression.Reference(new ObjectName("*")));
queryExp.Where = SqlExpression.And(
SqlExpression.Equal(SqlExpression.Reference(new ObjectName("a")), SqlExpression.Parameter()),
SqlExpression.Equal(SqlExpression.Reference(new ObjectName("b")), SqlExpression.Parameter()));
var prepared = queryExp.Prepare(preparer);
Assert.IsType <SqlQueryExpression>(prepared);
var sb = new SqlStringBuilder();
sb.AppendLine("SELECT *");
sb.AppendLine(" FROM table1");
sb.Append(" WHERE a = 2 AND b = 1023");
var expectString = sb.ToString();
Assert.Equal(expectString, prepared.ToString());
}
private FromTable(string tableName, SqlQueryExpression query, string alias)
{
Name = tableName;
SubQuery = query;
Alias = alias;
IsSubQuery = query != null;
}
public override SqlExpression VisitQuery(SqlQueryExpression query)
{
var selectColumns = Prepare(query.SelectColumns, preparer);
var newExpression = new SqlQueryExpression(selectColumns);
var where = query.WhereExpression;
if (where != null)
{
where = where.Prepare(preparer);
}
newExpression.WhereExpression = where;
var having = query.HavingExpression;
if (having != null)
{
having = having.Prepare(preparer);
}
newExpression.HavingExpression = having;
var from = query.FromClause;
if (from != null)
{
from = (FromClause)((IPreparable)from).Prepare(preparer);
}
newExpression.FromClause = from;
var nextComposite = query.NextComposite;
if (nextComposite != null)
{
nextComposite = (SqlQueryExpression)nextComposite.Prepare(preparer);
}
var groupBy = query.GroupBy;
if (groupBy != null)
{
var newGroupBy = new List <SqlExpression>();
foreach (var groupExpression in groupBy)
{
var newGroupByExp = groupExpression.Prepare(preparer);
newGroupBy.Add(newGroupByExp);
}
newExpression.GroupBy = newGroupBy;
}
newExpression.NextComposite = nextComposite;
newExpression.CompositeFunction = query.CompositeFunction;
newExpression.IsCompositeAll = query.IsCompositeAll;
newExpression.Distinct = query.Distinct;
return(newExpression);
}
protected override SqlStatement PrepareStatement(IRequest context)
{
var tableName = context.Access().ResolveTableName(TableName);
if (!context.Access().TableExists(tableName))
throw new ObjectNotFoundException(tableName);
var queryExpression = new SqlQueryExpression(new[]{SelectColumn.Glob("*") });
queryExpression.FromClause.AddTable(tableName.FullName);
queryExpression.WhereExpression = WherExpression;
var queryFrom = QueryExpressionFrom.Create(context, queryExpression);
var queryPlan = context.Query.Context.QueryPlanner().PlanQuery(new QueryInfo(context, queryExpression));
var columns = new List<SqlAssignExpression>();
foreach (var assignment in Assignments) {
var columnName = ObjectName.Parse(assignment.ColumnName);
var refName = queryFrom.ResolveReference(columnName);
var expression = assignment.Expression.Prepare(queryFrom.ExpressionPreparer);
var assign = SqlExpression.Assign(SqlExpression.Reference(refName), expression);
columns.Add(assign);
}
return new Prepared(tableName, queryPlan, columns.ToArray(), Limit);
}
public SelectStatement(SqlQueryExpression queryExpression, IEnumerable<SortColumn> orderBy)
{
if (queryExpression == null)
throw new ArgumentNullException("queryExpression");
QueryExpression = queryExpression;
OrderBy = orderBy;
}
public ISelectStatementBuilder Query(SqlQueryExpression expression)
{
if (expression == null)
throw new ArgumentNullException("expression");
queryExpression = expression;
return this;
}
public SqlQueryExpressionSource(SqlQueryExpression query, string alias)
: this(null, query, alias)
{
if (query == null)
{
throw new ArgumentNullException(nameof(query));
}
}
public static void DeclareInsensitiveCursor(this IRequest context, string cursorName, SqlQueryExpression query, bool withScroll)
{
var flags = CursorFlags.Insensitive;
if (withScroll)
flags |= CursorFlags.Scroll;
context.DeclareCursor(cursorName, flags, query);
}
/// <summary>
/// A table that is a sub-query and given an aliased name.
/// </summary>
/// <param name="query"></param>
/// <param name="tableAlias"></param>
public FromTable(SqlQueryExpression query, string tableAlias)
: this(null, query, tableAlias)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
}
public IQueryPlanNode PlanQuery(IQueryContext context, SqlQueryExpression queryExpression, IEnumerable<SortColumn> sortColumns)
{
var queryFrom = QueryExpressionFrom.Create(context, queryExpression);
var orderBy = new List<SortColumn>();
if (sortColumns != null)
orderBy.AddRange(sortColumns);
return PlanQuery(context, queryExpression, queryFrom, orderBy);
}
internal FromTableSubQuerySource(bool caseInsensitive, string uniqueKey, SqlQueryExpression queryExpression,
QueryExpressionFrom fromSet, ObjectName alias)
{
UniqueName = uniqueKey;
QueryExpression = queryExpression;
QueryFrom = fromSet;
AliasName = alias;
IgnoreCase = caseInsensitive;
}
internal JoinPart(JoinType joinType, SqlQueryExpression subQuery, SqlExpression onExpression)
{
if (subQuery == null)
throw new ArgumentNullException("subQuery");
OnExpression = onExpression;
JoinType = joinType;
SubQuery = subQuery;
}
public SelectIntoStatement(SqlQueryExpression queryExpression, SqlExpression reference)
{
if (queryExpression == null)
throw new ArgumentNullException("queryExpression");
if (reference == null)
throw new ArgumentNullException("reference");
QueryExpression = queryExpression;
Reference = reference;
}
public CreateViewStatement(string viewName, IEnumerable<string> columnNames, SqlQueryExpression queryExpression)
{
if (viewName == null)
throw new ArgumentNullException("viewName");
if (queryExpression == null)
throw new ArgumentNullException("queryExpression");
ViewName = viewName;
ColumnNames = columnNames;
QueryExpression = queryExpression;
}
public ViewInfo(TableInfo tableInfo, SqlQueryExpression queryExpression, IQueryPlanNode queryPlan)
{
if (tableInfo == null)
throw new ArgumentNullException("tableInfo");
if (queryExpression == null)
throw new ArgumentNullException("queryExpression");
TableInfo = tableInfo;
QueryExpression = queryExpression;
QueryPlan = queryPlan;
}
public void DeclareInsensitiveCursor()
{
var query = new SqlQueryExpression(new []{new SelectColumn(SqlExpression.Constant("*")) });
query.FromClause = new FromClause();
query.FromClause.AddTable("test_table");
Assert.DoesNotThrow(() => Query.DeclareInsensitiveCursor("c1", query));
var exists = Query.ObjectExists(DbObjectType.Cursor, new ObjectName("c1"));
Assert.IsTrue(exists);
}
public InsertSelectStatement(ObjectName tableName, IEnumerable<string> columnNames, SqlQueryExpression queryExpression)
{
if (tableName == null)
throw new ArgumentNullException("tableName");
if (queryExpression == null)
throw new ArgumentNullException("queryExpression");
TableName = tableName;
ColumnNames = columnNames;
QueryExpression = queryExpression;
}
internal JoinPart(JoinType joinType, SqlQueryExpression subQuery, SqlExpression onExpression)
{
if (subQuery == null)
{
throw new ArgumentNullException("subQuery");
}
OnExpression = onExpression;
JoinType = joinType;
SubQuery = subQuery;
}
public QueryInfo(IRequest request, SqlQueryExpression expression, IEnumerable<SortColumn> sortColumns, QueryLimit limit)
{
if (expression == null)
throw new ArgumentNullException("expression");
if (request == null)
throw new ArgumentNullException("request");
Expression = expression;
Request = request;
SortColumns = sortColumns;
Limit = limit;
}
private Field SelectScalar(string column)
{
var query = new SqlQueryExpression(new [] {new SelectColumn(SqlExpression.Reference(new ObjectName(column))) });
query.FromClause.AddTable("seq1");
var result = AdminQuery.Select(query);
var row = result.FirstOrDefault();
if (row == null)
return Field.Null();
return row.GetValue(0);
}
public DeclareCursorStatement(string cursorName, IEnumerable<CursorParameter> parameters, CursorFlags flags, SqlQueryExpression queryExpression)
{
if (queryExpression == null)
throw new ArgumentNullException("queryExpression");
if (String.IsNullOrEmpty(cursorName))
throw new ArgumentNullException("cursorName");
CursorName = cursorName;
Parameters = parameters;
Flags = flags;
QueryExpression = queryExpression;
}
public static void VisitQuery()
{
var exp = new SqlQueryExpression();
exp.Items.Add(SqlExpression.Reference(new ObjectName("a")));
exp.From.Table(new ObjectName("b"));
exp.From.Join(JoinType.Left,
SqlExpression.Equal(SqlExpression.Reference(ObjectName.Parse("b.id")), SqlExpression.Reference(ObjectName.Parse("c.b_id"))));
exp.From.Table(new ObjectName("c"));
exp.Where = SqlExpression.GreaterThanOrEqual(SqlExpression.Reference(new ObjectName("a")), SqlExpression.Constant(SqlObject.BigInt(22)));
Visit(exp);
}
public override SqlExpression VisitQuery(SqlQueryExpression query)
{
if (!rootQuery)
{
builder.Append("(");
}
builder.Append("SELECT ");
if (query.Distinct)
{
builder.Append("DISTINCT ");
}
PrintQueryColumns(query.SelectColumns);
builder.Append(" ");
PrintFromClause(query.FromClause);
if (query.WhereExpression != null)
{
builder.Append(" WHERE ");
Visit(query.WhereExpression);
}
if (query.GroupBy != null)
{
builder.Append(" GROUP BY ");
VisitExpressionList(query.GroupBy.ToArray());
if (query.HavingExpression != null)
{
builder.Append(" HVAING ");
Visit(query.HavingExpression);
}
}
if (query.GroupMax != null)
{
builder.Append(" GROUP MAX ");
builder.Append(query.GroupMax.FullName);
}
// TODO: COMPOSITE ...
if (!rootQuery)
{
builder.Append(")");
}
return(query);
}
/// <summary>
///
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public virtual SqlExpression VisitQuery(SqlQueryExpression query)
{
var selectColumns = new List <SelectColumn>();
foreach (var column in query.SelectColumns)
{
var newColumn = new SelectColumn(Visit(column.Expression), column.Alias);
newColumn.InternalName = column.InternalName;
newColumn.ResolvedName = column.ResolvedName;
selectColumns.Add(newColumn);
}
var newQuery = new SqlQueryExpression(selectColumns);
if (query.FromClause != null)
{
newQuery.FromClause = VisitFromClause(query.FromClause);
}
if (query.WhereExpression != null)
{
newQuery.WhereExpression = Visit(query.WhereExpression);
}
if (query.HavingExpression != null)
{
newQuery.HavingExpression = Visit(query.HavingExpression);
}
if (query.GroupBy != null)
{
newQuery.GroupBy = VisitExpressionList(newQuery.GroupBy.ToArray());
}
newQuery.GroupMax = query.GroupMax;
newQuery.Distinct = query.Distinct;
if (query.NextComposite != null)
{
var visitedComposite = Visit(query.NextComposite);
if (visitedComposite.ExpressionType == SqlExpressionType.Query)
{
newQuery.NextComposite = (SqlQueryExpression)visitedComposite;
}
newQuery.CompositeFunction = query.CompositeFunction;
newQuery.IsCompositeAll = query.IsCompositeAll;
}
return(query);
}
public static void MakeNewNaturalJoinQuery()
{
var query = new SqlQueryExpression();
query.Items.Add(SqlExpression.Reference(ObjectName.Parse("a.*")));
query.Items.Add(SqlExpression.Reference(ObjectName.Parse("b.*")));
query.From.Table(ObjectName.Parse("table1"), "a");
query.From.Table(ObjectName.Parse("table2"), "b");
var expected = new SqlStringBuilder();
expected.AppendLine("SELECT a.*, b.*");
expected.Append(" FROM table1 AS a, table2 AS b");
Assert.Equal(expected.ToString(), query.ToString());
}
public virtual SqlExpression VisitQuery(SqlQueryExpression expression)
{
var items = VisitQueryItems(expression.Items);
var query = new SqlQueryExpression {
Distinct = expression.Distinct
};
if (items != null)
{
foreach (var item in items)
{
query.Items.Add(item);
}
}
var from = expression.From;
if (from != null)
{
from = VisitQueryFrom(from);
}
query.From = from;
var where = expression.Where;
if (where != null)
{
where = Visit(where);
}
query.Where = where;
var having = expression.Having;
if (having != null)
{
having = Visit(having);
}
query.Having = having;
query.GroupBy = VisitExpressionList(expression.GroupBy);
query.GroupMax = expression.GroupMax;
return(query);
}
public static void CreateNewSimpleQuery()
{
var query = new SqlQueryExpression();
query.Items.Add(SqlExpression.Reference(ObjectName.Parse("a.*")));
query.Items.Add(SqlExpression.Reference(ObjectName.Parse("b")));
query.From.Table(ObjectName.Parse("tab1"));
var expected = new SqlStringBuilder();
expected.AppendLine("SELECT a.*, b");
expected.Append(" FROM tab1");
Assert.False(query.From.IsEmpty);
Assert.Equal(expected.ToString(), query.ToString());
}
public override SqlExpression VisitQuery(SqlQueryExpression query)
{
if (context.Request == null)
{
throw new ExpressionEvaluateException("A query expression is required to evaluate a query.");
}
try {
var planner = context.Request.Context.QueryPlanner();
var plan = planner.PlanQuery(new QueryInfo(context.Request, query));
return(SqlExpression.Constant(new DataObject(new QueryType(), new SqlQueryObject(plan))));
} catch (ExpressionEvaluateException) {
throw;
} catch (Exception ex) {
throw new ExpressionEvaluateException("Evaluation of a QUERY expression could not generate a plan.", ex);
}
}
public static int DeleteFrom(this IQuery context, ObjectName tableName, SqlExpression expression, int limit)
{
if (expression is SqlQueryExpression)
return context.DeleteFrom(tableName, (SqlQueryExpression)expression, limit);
var table = context.GetMutableTable(tableName);
if (table == null)
throw new ObjectNotFoundException(tableName);
var queryExpression = new SqlQueryExpression(new List<SelectColumn> { SelectColumn.Glob("*") });
queryExpression.FromClause.AddTable(tableName.Name);
queryExpression.WhereExpression = expression;
var planExpression = queryExpression.Evaluate(context, null);
var plan = (SqlQueryObject)((SqlConstantExpression)planExpression).Value.Value;
var deleteSet = plan.QueryPlan.Evaluate(context);
return context.DeleteFrom(tableName, deleteSet, limit);
}
IStatement IPreparableStatement.Prepare(IRequest request)
{
var tableName = request.Query.ResolveTableName(TableName);
if (!request.Query.TableExists(tableName))
throw new ObjectNotFoundException(tableName);
var queryExp = new SqlQueryExpression(new SelectColumn[] {SelectColumn.Glob("*") });
queryExp.FromClause.AddTable(tableName.FullName);
queryExp.WhereExpression = WhereExpression;
var queryInfo = new QueryInfo(request, queryExp);
if (Limit > 0)
queryInfo.Limit = new QueryLimit(Limit);
var queryPlan = request.Query.Context.QueryPlanner().PlanQuery(queryInfo);
return new Prepared(tableName, queryPlan);
}
public static void MakeNewInnerJoinQuery()
{
var query = new SqlQueryExpression();
query.Items.Add(SqlExpression.Reference(ObjectName.Parse("a.*")));
query.Items.Add(SqlExpression.Reference(ObjectName.Parse("b.*")));
query.From.Table(ObjectName.Parse("table1"), "a");
query.From.Join(JoinType.Inner,
SqlExpression.Equal(SqlExpression.Reference(ObjectName.Parse("a.id")),
SqlExpression.Reference(ObjectName.Parse("b.a_id"))));
query.From.Table(ObjectName.Parse("table2"), "b");
var expected = new SqlStringBuilder();
expected.AppendLine("SELECT a.*, b.*");
expected.Append(" FROM table1 AS a INNER JOIN table2 AS b ON a.id = b.a_id");
Assert.Equal(expected.ToString(), query.ToString());
}
public void ExecuteSelectAll()
{
var expression =
new SqlQueryExpression(new[] {new SelectColumn(SqlExpression.Reference(new ObjectName("first_name")))});
expression.FromClause.AddTable("test_table");
Field result = null;
Assert.DoesNotThrow(() => result = expression.EvaluateToConstant(AdminQuery, null));
Assert.IsNotNull(result);
Assert.IsInstanceOf<QueryType>(result.Type);
Assert.IsNotNull(result.Value);
Assert.IsInstanceOf<SqlQueryObject>(result.Value);
ITable queryResult = null;
Assert.DoesNotThrow(() => queryResult = ((SqlQueryObject) result.Value).QueryPlan.Evaluate(AdminQuery));
Assert.IsNotNull(queryResult);
Assert.AreEqual(3, queryResult.RowCount);
}
public override SqlExpression VisitQuery(SqlQueryExpression query)
{
var selectColumns = Prepare(query.SelectColumns, preparer);
var newExpression = new SqlQueryExpression(selectColumns);
var where = query.WhereExpression;
if (where != null)
{
where = where.Prepare(preparer);
}
newExpression.WhereExpression = where;
var having = query.HavingExpression;
if (having != null)
{
having = having.Prepare(preparer);
}
newExpression.HavingExpression = having;
var from = query.FromClause;
if (from != null)
{
from = (FromClause)((IPreparable)from).Prepare(preparer);
}
query.FromClause = from;
var nextComposite = query.NextComposite;
if (nextComposite != null)
{
nextComposite = (SqlQueryExpression)nextComposite.Prepare(preparer);
}
query.NextComposite = nextComposite;
return(newExpression);
}
public void ExecuteSelectAll()
{
var expression =
new SqlQueryExpression(new[] { new SelectColumn(SqlExpression.Reference(new ObjectName("first_name"))) });
expression.FromClause.AddTable("test_table");
Field result = null;
Assert.DoesNotThrow(() => result = expression.EvaluateToConstant(AdminQuery, null));
Assert.IsNotNull(result);
Assert.IsInstanceOf <QueryType>(result.Type);
Assert.IsNotNull(result.Value);
Assert.IsInstanceOf <SqlQueryObject>(result.Value);
ITable queryResult = null;
Assert.DoesNotThrow(() => queryResult = ((SqlQueryObject)result.Value).QueryPlan.Evaluate(AdminQuery));
Assert.IsNotNull(queryResult);
Assert.AreEqual(3, queryResult.RowCount);
}
public static void CreateNewQuerySource()
{
var fromTable = ObjectName.Parse("table1");
var query = new SqlQueryExpression();
query.AllItems = true;
query.From.Table(fromTable);
var source = new SqlQueryExpressionSource(query, "a");
Assert.True(source.IsQuery);
Assert.False(source.IsTable);
Assert.True(source.IsAliased);
Assert.Equal("a", source.Alias);
Assert.True(source.Query.AllItems);
var expected = new SqlStringBuilder();
expected.AppendLine("(SELECT *");
expected.Append(" FROM table1) AS a");
Assert.Equal(expected.ToString(), source.ToString());
}
public static int DeleteFrom(this IQuery context, ObjectName tableName, SqlQueryExpression query, int limit)
{
IQueryPlanNode plan;
try {
var planValue = query.EvaluateToConstant(context, null);
if (planValue == null)
throw new InvalidOperationException();
if (!(planValue.Type is QueryType))
throw new InvalidOperationException();
plan = ((SqlQueryObject)planValue.Value).QueryPlan;
} catch (QueryException) {
throw;
} catch (SecurityException) {
throw;
} catch (Exception ex) {
throw new InvalidOperationException(String.Format("Could not delete from table '{0}': unable to form the delete set.", tableName), ex);
}
var deleteSet = plan.Evaluate(context);
return context.DeleteFrom(tableName, deleteSet, limit);
}
public SqlQueryExpressionComposite(CompositeFunction function, bool all, SqlQueryExpression expression)
{
Function = function;
All = all;
Expression = expression;
}
public static void DeclareCursor(this IRequest context, string cursorName, CursorFlags flags, SqlQueryExpression query)
{
context.DeclareCursor(new CursorInfo(cursorName, flags, query));
}
public static void DeclareInsensitiveCursor(this IRequest context, string cursorName, SqlQueryExpression query)
{
DeclareInsensitiveCursor(context, cursorName, query, false);
}
/// <summary>
/// Adds a sub-query expression as source of the query.
/// </summary>
/// <param name="subQuery">The sub-query expression as source of the query.</param>
/// <seealso cref="AddSubQuery(string, SqlQueryExpression)"/>
public void AddSubQuery(SqlQueryExpression subQuery)
{
AddSubQuery(null, subQuery);
}
public static void DeclareCursor(this IRequest context, string cursorName, SqlQueryExpression query)
{
DeclareCursor(context, cursorName, (CursorFlags)0, query);
}
/// <summary>
/// Adds a sub-query expression as source of the query.
/// </summary>
/// <param name="subQuery">The sub-query expression as source of the query.</param>
/// <seealso cref="AddSubQuery(string, SqlQueryExpression)"/>
public void AddSubQuery(SqlQueryExpression subQuery)
{
AddSubQuery(null, subQuery);
}
/// <summary>
/// Adds a sub-query expression as source of the query.
/// </summary>
/// <param name="alias">The unique alias name of the expression within the clause.</param>
/// <param name="subQuery">The sub-query expression as source of the query.</param>
/// <seealso cref="AddTable(string, FromTable)"/>
/// <seealso cref="SqlQueryExpression"/>
public void AddSubQuery(string alias, SqlQueryExpression subQuery)
{
AddTable(alias, new FromTable(subQuery, alias));
}
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;
}
public SqlQueryExpressionComposite(CompositeFunction function, SqlQueryExpression expression)
: this(function, false, expression)
{
}
/// <summary>
/// Adds a sub-query expression as source of the query.
/// </summary>
/// <param name="alias">The unique alias name of the expression within the clause.</param>
/// <param name="subQuery">The sub-query expression as source of the query.</param>
/// <seealso cref="AddTable(string, FromTable)"/>
/// <seealso cref="SqlQueryExpression"/>
public void AddSubQuery(string alias, SqlQueryExpression subQuery)
{
AddTable(alias, new FromTable(subQuery, alias));
}
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 int DeleteFrom(this IQuery context, ObjectName tableName, SqlQueryExpression query)
{
return DeleteFrom(context, tableName, query, -1);
}
public override SqlExpression VisitQuery(SqlQueryExpression query)
{
return(base.VisitQuery(query));
}
public void Query(SqlQueryExpression query, string alias)
{
Source(new SqlQueryExpressionSource(query, alias));
}
/// <summary>
/// A table that is a sub-query with no alias set.
/// </summary>
/// <param name="query"></param>
public FromTable(SqlQueryExpression query)
: this(query, null)
{
}
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 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;
}
private SqlQueryExpressionSource(ObjectName tableName, SqlQueryExpression query, string alias)
{
TableName = tableName;
Query = query;
Alias = alias;
}