private void EvaluateSingles(List <SqlBinaryExpression> list, List <ExpressionPlan> plans)
{
// The list of simple expression plans (lhs = single)
var simplePlanList = new List <SingleColumnPlan>();
// The list of complex function expression plans (lhs = expression)
var complexPlanList = new List <SingleColumnPlan>();
foreach (var expression in list)
{
// The single var
ObjectName singleVar;
SqlExpressionType op = expression.ExpressionType;
SqlExpression left = expression.Left, right = expression.Right;
if (op.IsSubQuery())
{
singleVar = expression.Left.AsReferenceName();
if (singleVar != null)
{
plans.Add(new SimpleSelectPlan(this, singleVar, op, expression.Right));
}
else
{
singleVar = expression.Left.DiscoverReferences().First();
plans.Add(new ComplexSinglePlan(this, singleVar, expression));
}
}
else
{
singleVar = expression.Left.DiscoverReferences().FirstOrDefault();
if (singleVar == null)
{
// Reverse the expressions and the operator
var tempExp = left;
left = right;
right = tempExp;
op = op.Reverse();
singleVar = left.DiscoverReferences().First();
}
var tableSource = FindPlan(singleVar);
// Simple LHS?
var v = left.AsReferenceName();
if (v != null)
{
AddSingleColumnPlan(simplePlanList, tableSource, v, singleVar, new [] { left, right }, op);
}
else
{
// No, complex lhs
AddSingleColumnPlan(complexPlanList, tableSource, null, singleVar, new [] { left, right }, op);
}
}
}
plans.AddRange(simplePlanList.Select(plan => new SimpleSinglePlan(this, plan.UniqueName, plan.Expression)).Cast <ExpressionPlan>());
plans.AddRange(complexPlanList.Select(plan => new ComplexSinglePlan(this, plan.UniqueName, plan.Expression)).Cast <ExpressionPlan>());
}
public static SqlExpressionType Inverse(this SqlExpressionType type)
{
if (type.IsSubQuery())
{
var plainType = type.SubQueryPlainType();
var invType = plainType.Inverse();
if (type.IsAny())
{
return(invType.Any());
}
if (type.IsAll())
{
return(invType.All());
}
}
switch (type)
{
case SqlExpressionType.Equal:
return(SqlExpressionType.NotEqual);
case SqlExpressionType.NotEqual:
return(SqlExpressionType.Equal);
case SqlExpressionType.GreaterThan:
return(SqlExpressionType.SmallerOrEqualThan);
case SqlExpressionType.SmallerThan:
return(SqlExpressionType.GreaterOrEqualThan);
case SqlExpressionType.GreaterOrEqualThan:
return(SqlExpressionType.SmallerThan);
case SqlExpressionType.SmallerOrEqualThan:
return(SqlExpressionType.GreaterThan);
case SqlExpressionType.And:
return(SqlExpressionType.Or);
case SqlExpressionType.Or:
return(SqlExpressionType.And);
case SqlExpressionType.Like:
return(SqlExpressionType.NotLike);
case SqlExpressionType.NotLike:
return(SqlExpressionType.Like);
case SqlExpressionType.Is:
return(SqlExpressionType.IsNot);
case SqlExpressionType.IsNot:
return(SqlExpressionType.Is);
}
throw new ArgumentException();
}
public IndexRangeSet Intersect(SqlExpressionType op, DataObject value)
{
lock (this) {
int sz = ranges.Count;
var list = ranges.GetRange(0, sz);
if (op.IsSubQuery())
{
op = op.SubQueryPlainType();
}
if (op == SqlExpressionType.NotEqual ||
op == SqlExpressionType.IsNot)
{
bool nullCheck = op == SqlExpressionType.NotEqual;
int j = 0;
while (j < sz)
{
var range = list[j];
var leftRange = IntersectOn(range, SqlExpressionType.SmallerThan, value, nullCheck);
var rightRange = IntersectOn(range, SqlExpressionType.GreaterThan, value, nullCheck);
list.RemoveAt(j);
if (leftRange != IndexRange.Null)
{
list.Add(leftRange);
}
if (rightRange != IndexRange.Null)
{
list.Add(rightRange);
}
j++;
}
return(new IndexRangeSet(list));
}
else
{
bool nullCheck = op != SqlExpressionType.Is;
int j = 0;
while (j < sz)
{
var range = list[j];
range = IntersectOn(range, op, value, nullCheck);
if (range == IndexRange.Null)
{
list.RemoveAt(j);
}
else
{
list[j] = range;
}
j++;
}
return(new IndexRangeSet(list));
}
}
}
public IndexRangeSet Intersect(SqlExpressionType op, DataObject value)
{
lock (this) {
int sz = ranges.Count;
var list = ranges.GetRange(0, sz);
if (op.IsSubQuery())
op = op.SubQueryPlainType();
if (op == SqlExpressionType.NotEqual ||
op == SqlExpressionType.IsNot) {
bool nullCheck = op == SqlExpressionType.NotEqual;
int j = 0;
while (j < sz) {
var range = list[j];
var leftRange = IntersectOn(range, SqlExpressionType.SmallerThan, value, nullCheck);
var rightRange = IntersectOn(range, SqlExpressionType.GreaterThan, value, nullCheck);
list.RemoveAt(j);
if (leftRange != IndexRange.Null) {
list.Add(leftRange);
}
if (rightRange != IndexRange.Null) {
list.Add(rightRange);
}
j++;
}
return new IndexRangeSet(list);
} else {
bool nullCheck = op != SqlExpressionType.Is;
int j = 0;
while (j < sz) {
var range = list[j];
range = IntersectOn(range, op, value, nullCheck);
if (range == IndexRange.Null) {
list.RemoveAt(j);
} else {
list[j] = range;
}
j++;
}
return new IndexRangeSet(list);
}
}
}
public static ITable SelectAnyAllNonCorrelated(this ITable table, ObjectName[] leftColumns, SqlExpressionType op, ITable rightTable)
{
if (rightTable.TableInfo.ColumnCount != leftColumns.Length) {
throw new ArgumentException(String.Format("The right table has {0} columns that is different from the specified column names ({1})",
rightTable.TableInfo.ColumnCount, leftColumns.Length));
}
// Handle trivial case of no entries to select from
if (table.RowCount == 0)
return table;
// Resolve the vars in the left table and check the references are
// compatible.
var sz = leftColumns.Length;
var leftColMap = new int[sz];
var rightColMap = new int[sz];
for (int i = 0; i < sz; ++i) {
leftColMap[i] = table.FindColumn(leftColumns[i]);
rightColMap[i] = i;
if (leftColMap[i] == -1)
throw new Exception("Invalid reference: " + leftColumns[i]);
var leftType = table.TableInfo[leftColMap[i]].ColumnType;
var rightType = rightTable.TableInfo[i].ColumnType;
if (!leftType.IsComparable(rightType)) {
throw new ArgumentException(String.Format("The type of the sub-query expression {0}({1}) " +
"is not compatible with the sub-query type {2}.",
leftColumns[i], leftType, rightType));
}
}
IEnumerable<int> rows;
if (!op.IsSubQuery())
throw new ArgumentException(String.Format("The operator {0} is not a sub-query form.", op));
if (op.IsAll()) {
// ----- ALL operation -----
// We work out 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.
if (op == SqlExpressionType.AllGreaterThan ||
op == SqlExpressionType.AllGreaterOrEqualThan) {
// Select the last from the set (the highest value),
var highestCells = rightTable.GetLastValues(rightColMap);
// Select from the source table all rows that are > or >= to the
// highest cell,
rows = table.SelectRows(leftColMap, op, highestCells);
} else if (op == SqlExpressionType.AllSmallerThan ||
op == SqlExpressionType.AllSmallerOrEqualThan) {
// Select the first from the set (the lowest value),
var lowestCells = rightTable.GetFirstValues(rightColMap);
// Select from the source table all rows that are < or <= to the
// lowest cell,
rows = table.SelectRows(leftColMap, op, lowestCells);
} else if (op == SqlExpressionType.AllEqual) {
// Select the single value from the set (if there is one).
var singleCell = rightTable.GetSingleValues(rightColMap);
if (singleCell != null) {
// Select all from source_table all values that = this cell
rows = table.SelectRows(leftColMap, op, singleCell);
} else {
// No single value so return empty set (no value in LHS will equal
// a value in RHS).
return table.EmptySelect();
}
} else if (op == SqlExpressionType.AllNotEqual) {
// Equiv. to NOT IN
rows = table.SelectRowsNotIn(rightTable, leftColMap, rightColMap);
} else {
throw new ArgumentException(String.Format("Operator of type {0} is not valid in ALL functions.", op.SubQueryPlainType()));
}
} else {
// ----- ANY operation -----
// We work out as follows:
// For >, >= type ANY we find the lowest value in 'table' and
// select from 'source' all the rows that are >, >= than the
// lowest value.
// For <, <= type ANY we find the highest value in 'table' and
// select from 'source' all the rows that are <, <= than the
// highest value.
// For = type ANY we use same method from INHelper.
// For <> type ANY we iterate through 'source' only including those
// rows that a <> query on 'table' returns size() != 0.
if (op == SqlExpressionType.AnyGreaterThan ||
op == SqlExpressionType.AnyGreaterOrEqualThan) {
// Select the first from the set (the lowest value),
var lowestCells = rightTable.GetFirstValues(rightColMap);
// Select from the source table all rows that are > or >= to the
// lowest cell,
rows = table.SelectRows(leftColMap, op, lowestCells);
} else if (op == SqlExpressionType.AnySmallerThan ||
op == SqlExpressionType.AnySmallerOrEqualThan) {
// Select the last from the set (the highest value),
var highestCells = rightTable.GetLastValues(rightColMap);
// Select from the source table all rows that are < or <= to the
// highest cell,
rows = table.SelectRows(leftColMap, op, highestCells);
} else if (op == SqlExpressionType.AnyEqual) {
// Equiv. to IN
rows = table.SelectRowsIn(rightTable, leftColMap, rightColMap);
} else if (op == SqlExpressionType.AnyNotEqual) {
// Select the value that is the same of the entire column
var cells = rightTable.GetSingleValues(rightColMap);
if (cells != null) {
// All values from 'source_table' that are <> than the given cell.
rows = table.SelectRows(leftColMap, op, cells);
} else {
// No, this means there are different values in the given set so the
// query evaluates to the entire table.
return table;
}
} else {
throw new ArgumentException(String.Format("Operator of type {0} is not valid in ANY functions.", op.SubQueryPlainType()));
}
}
return new VirtualTable(table, rows.ToArray());
}
public static ITable SimpleSelect(this ITable table, IQueryContext context, ObjectName columnName, SqlExpressionType op, SqlExpression exp)
{
// Find the row with the name given in the condition.
int column = table.FindColumn(columnName);
if (column == -1)
throw new ArgumentException(String.Format("Unable to find the column {0} in the condition.", columnName.Name));
// If we are doing a sub-query search
if (op.IsSubQuery()) {
// We can only handle constant expressions in the RHS expression, and
// we must assume that the RHS is a Expression[] array.
if (exp.ExpressionType != SqlExpressionType.Constant &&
exp.ExpressionType != SqlExpressionType.Tuple)
throw new ArgumentException();
IEnumerable<SqlExpression> list;
if (exp.ExpressionType == SqlExpressionType.Constant) {
var tob = ((SqlConstantExpression) exp).Value;
if (tob.Type is ArrayType) {
var array = (SqlArray) tob.Value;
list = array;
} else {
throw new Exception("Error with format or RHS expression.");
}
} else {
list = ((SqlTupleExpression) exp).Expressions;
}
// Construct a temporary table with a single column that we are
// comparing to.
var col = table.TableInfo[column];
var ttable = TemporaryTable.SingleColumnTable(table.DatabaseContext, col.ColumnName, col.ColumnType);
foreach (var expression in list) {
var rowNum = ttable.NewRow();
var evalExp = (SqlConstantExpression)expression.Evaluate(context, null, null);
ttable.SetValue(rowNum, 0, evalExp.Value);
}
ttable.BuildIndexes();
// Perform the any/all sub-query on the constant table.
return table.SelectAnyAllNonCorrelated(new[] { columnName }, op, ttable);
}
{
if (!exp.IsConstant())
throw new ArgumentException("The search expression is not constant.");
var evalExp = exp.Evaluate(context, null);
if (evalExp.ExpressionType != SqlExpressionType.Constant)
throw new InvalidOperationException();
var value = ((SqlConstantExpression) evalExp).Value;
IEnumerable<int> rows;
if (op == SqlExpressionType.Like ||
op == SqlExpressionType.NotLike
/* TODO: ||
op.IsOfType(BinaryOperatorType.Regex)*/) {
/*
TODO:
if (op.IsOfType(BinaryOperatorType.Regex)) {
rows = SelectFromRegex(column, op, value);
} else {
*/
rows = table.SelectFromPattern(column, op, value);
} else {
// Is the column we are searching on indexable?
var colInfo = table.TableInfo[column];
if (!colInfo.IsIndexable)
throw new InvalidOperationException(String.Format("Column {0} os type {1} cannot be searched.", colInfo.ColumnName,
colInfo.ColumnType));
rows = table.SelectRows(column, op, value);
}
return new VirtualTable(table, rows.ToArray()) {SortColumn = column};
}
}
