public static ITable[] Execute(IDatabaseConnection connection, SqlQuery query)
{
// StatementTree caching
// Substitute all parameter substitutions in the statement tree.
// TODO: IExpressionPreparer preparer = new QueryPreparer(query);
// Create a new parser and set the parameters...
IEnumerable<Statement> statements;
string commandText = query.Text;
try {
lock (SqlParser) {
SqlParser.ReInit(new StreamReader(new MemoryStream(Encoding.Unicode.GetBytes(commandText)), Encoding.Unicode));
SqlParser.Reset();
// Parse the statement.
statements = SqlParser.SequenceOfStatements();
}
} catch (ParseException e) {
var tokens = SqlParser.token_source.tokenHistory;
throw new SqlParseException(e, commandText, tokens);
}
List<ITable> results = new List<ITable>();
foreach (Statement parsedStatement in statements) {
var statement = parsedStatement;
// TODO: statement = statement.PrepareExpressions(preparer);
// Convert the StatementTree to a statement object
statement.Query = query;
DatabaseQueryContext context = new DatabaseQueryContext(connection);
// Prepare the statement
statement = statement.PrepareStatement(context);
// Evaluate the SQL statement.
results.Add(statement.Evaluate(context));
}
return results.ToArray();
}
public static ITable All(this ITable table, IQueryContext context, Expression expression, Operator op, ITable other)
{
// Check the table only has 1 column
if (other.TableInfo.ColumnCount != 1)
{
throw new ApplicationException("Input table <> 1 columns.");
}
// Handle trivial case of no entries to select from
if (table.RowCount == 0)
{
return(table);
}
var theTable = table as Table;
if (theTable == null)
{
return(table);
}
// If 'table' is empty then we return the complete set. ALL { empty set }
// is always true.
if (other.RowCount == 0)
{
return(table);
}
// Is the lhs expression a constant?
if (expression.IsConstant())
{
// We know lhs is a constant so no point passing arguments,
DataObject value = expression.Evaluate(context);
bool comparedToTrue;
// The various operators
if (op.IsEquivalent(Operator.Greater) ||
op.IsEquivalent(Operator.GreaterOrEqual))
{
// Find the maximum value in the table
DataObject cell = other.GetLastCell(0);
comparedToTrue = CompareCells(value, cell, op);
}
else if (op.IsEquivalent(Operator.Smaller) ||
op.IsEquivalent(Operator.SmallerOrEqual))
{
// Find the minimum value in the table
DataObject cell = other.GetFirstCell(0);
comparedToTrue = CompareCells(value, cell, op);
}
else if (op.IsEquivalent(Operator.Equal))
{
// Only true if rhs is a single value
DataObject cell = other.GetSingleCell(0);
comparedToTrue = (cell != null && CompareCells(value, cell, op));
}
else if (op.IsEquivalent(Operator.NotEqual))
{
// true only if lhs_cell is not found in column.
comparedToTrue = !other.ColumnContainsValue(0, value);
}
else
{
throw new ApplicationException("Don't understand operator '" + op + "' in ALL.");
}
// If matched return this table
if (comparedToTrue)
{
return(table);
}
// No entries matches so return an empty table.
return(table.EmptySelect());
}
Table sourceTable;
int colIndex;
// Is the lhs expression a single variable?
ObjectName expVar = expression.AsVariable();
// NOTE: It'll be less common for this part to be called.
if (expVar == null)
{
// This is a complex expression so make a FunctionTable as our new
// source.
DatabaseQueryContext dbContext = (DatabaseQueryContext)context;
FunctionTable funTable = new FunctionTable((Table)table, new[] { expression }, new [] { "1" }, dbContext);
sourceTable = funTable;
colIndex = 0;
}
else
{
// The expression is an easy to resolve reference in this table.
sourceTable = (Table)table;
colIndex = sourceTable.FindFieldName(expVar);
if (colIndex == -1)
{
throw new ApplicationException("Can't find column '" + expVar + "'.");
}
}
// Check that the first column of 'table' is of a compatible type with
// source table column (lhs_col_index).
// ISSUE: Should we convert to the correct type via a FunctionTable?
DataColumnInfo sourceCol = sourceTable.TableInfo[colIndex];
DataColumnInfo destCol = other.TableInfo[0];
if (!sourceCol.DataType.IsComparable(destCol.DataType))
{
throw new ApplicationException("The type of the sub-query expression " +
sourceCol.DataType + " is incompatible " +
"with the sub-query " + destCol.DataType +
".");
}
// We now have all the information to solve this query.
// We work output as follows:
// For >, >= type ALL we find the highest value in 'table' and
// select from 'source' all the rows that are >, >= than the
// highest value.
// For <, <= type ALL we find the lowest value in 'table' and
// select from 'source' all the rows that are <, <= than the
// lowest value.
// For = type ALL we see if 'table' contains a single value. If it
// does we select all from 'source' that equals the value, otherwise an
// empty table.
// For <> type ALL we use the 'not in' algorithm.
IList <long> selectList;
if (op.IsEquivalent(Operator.Greater) ||
op.IsEquivalent(Operator.GreaterOrEqual))
{
// Select the last from the set (the highest value),
DataObject highestCell = other.GetLastCell(0);
// Select from the source table all rows that are > or >= to the
// highest cell,
selectList = sourceTable.SelectRows(colIndex, op, highestCell).ToList();
}
else if (op.IsEquivalent(Operator.Smaller) ||
op.IsEquivalent(Operator.SmallerOrEqual))
{
// Select the first from the set (the lowest value),
DataObject lowestCell = other.GetFirstCell(0);
// Select from the source table all rows that are < or <= to the
// lowest cell,
selectList = sourceTable.SelectRows(colIndex, op, lowestCell).ToList();
}
else if (op.IsEquivalent(Operator.Equal))
{
// Select the single value from the set (if there is one).
DataObject singleCell = other.GetSingleCell(0);
if (singleCell != null)
{
// Select all from source_table all values that = this cell
selectList = sourceTable.SelectRows(colIndex, op, singleCell).ToList();
}
else
{
// No single value so return empty set (no value in LHS will equal
// a value in RHS).
return(table.EmptySelect());
}
}
else if (op.IsEquivalent(Operator.NotEqual))
{
// Equiv. to NOT IN
selectList = sourceTable.NotIn(other, colIndex, 0).ToList();
}
else
{
throw new ApplicationException("Don't understand operator '" + op + "' in ALL.");
}
// Make into a table to return.
VirtualTable rtable = new VirtualTable(theTable);
rtable.Set((Table)table, selectList);
return(rtable);
}
/// <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;
}