public static SqlExpressionType SubQueryPlainType(this SqlExpressionType type)
{
if (type.IsAny())
{
if (type == SqlExpressionType.AnyEqual)
{
return(SqlExpressionType.Equal);
}
if (type == SqlExpressionType.AnyNotEqual)
{
return(SqlExpressionType.NotEqual);
}
if (type == SqlExpressionType.AnyGreaterThan)
{
return(SqlExpressionType.GreaterThan);
}
if (type == SqlExpressionType.AnyGreaterOrEqualThan)
{
return(SqlExpressionType.GreaterOrEqualThan);
}
if (type == SqlExpressionType.AnySmallerThan)
{
return(SqlExpressionType.AnySmallerThan);
}
if (type == SqlExpressionType.AnySmallerOrEqualThan)
{
return(SqlExpressionType.SmallerOrEqualThan);
}
}
if (type.IsAll())
{
if (type == SqlExpressionType.AllEqual)
{
return(SqlExpressionType.Equal);
}
if (type == SqlExpressionType.AllNotEqual)
{
return(SqlExpressionType.NotEqual);
}
if (type == SqlExpressionType.AllGreaterThan)
{
return(SqlExpressionType.GreaterThan);
}
if (type == SqlExpressionType.AllGreaterOrEqualThan)
{
return(SqlExpressionType.GreaterOrEqualThan);
}
if (type == SqlExpressionType.AllSmallerThan)
{
return(SqlExpressionType.AllSmallerThan);
}
if (type == SqlExpressionType.AllSmallerOrEqualThan)
{
return(SqlExpressionType.SmallerOrEqualThan);
}
}
throw new ArgumentException();
}
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();
}
private DataObject EvaluateBinary(DataObject left, SqlExpressionType binaryType, DataObject right)
{
if (binaryType.IsAll())
return left.Any(binaryType.SubQueryPlainType(), right, context);
if (binaryType.IsAny())
return left.All(binaryType.SubQueryPlainType(), right, context);
switch (binaryType) {
case SqlExpressionType.Add:
return left.Add(right);
case SqlExpressionType.Subtract:
return left.Subtract(right);
case SqlExpressionType.Multiply:
return left.Multiply(right);
case SqlExpressionType.Divide:
return left.Divide(right);
case SqlExpressionType.Modulo:
return left.Modulus(right);
case SqlExpressionType.GreaterThan:
return left.IsGreaterThan(right);
case SqlExpressionType.GreaterOrEqualThan:
return left.IsGreterOrEqualThan(right);
case SqlExpressionType.SmallerThan:
return left.IsSmallerThan(right);
case SqlExpressionType.SmallerOrEqualThan:
return left.IsSmallerOrEqualThan(right);
case SqlExpressionType.Equal:
return left.IsEqualTo(right);
case SqlExpressionType.NotEqual:
return left.IsNotEqualTo(right);
case SqlExpressionType.Is:
return left.Is(right);
case SqlExpressionType.IsNot:
return left.IsNot(right);
case SqlExpressionType.Like:
return left.IsLike(right);
case SqlExpressionType.NotLike:
return left.IsNotLike(right);
case SqlExpressionType.And:
return left.And(right);
case SqlExpressionType.Or:
return left.Or(right);
case SqlExpressionType.XOr:
return left.XOr(right);
// TODO: ANY and ALL
default:
throw new ExpressionEvaluateException(String.Format("The type {0} is not a binary expression or is not supported.", binaryType));
}
}
public static bool IsSubQuery(this SqlExpressionType type)
{
return(type.IsAny() || type.IsAll());
}
private DataObject EvaluateBinary(DataObject left, SqlExpressionType binaryType, DataObject right)
{
if (binaryType.IsAll())
{
return(left.Any(binaryType.SubQueryPlainType(), right, context));
}
if (binaryType.IsAny())
{
return(left.All(binaryType.SubQueryPlainType(), right, context));
}
switch (binaryType)
{
case SqlExpressionType.Add:
return(left.Add(right));
case SqlExpressionType.Subtract:
return(left.Subtract(right));
case SqlExpressionType.Multiply:
return(left.Multiply(right));
case SqlExpressionType.Divide:
return(left.Divide(right));
case SqlExpressionType.Modulo:
return(left.Modulus(right));
case SqlExpressionType.GreaterThan:
return(left.IsGreaterThan(right));
case SqlExpressionType.GreaterOrEqualThan:
return(left.IsGreterOrEqualThan(right));
case SqlExpressionType.SmallerThan:
return(left.IsSmallerThan(right));
case SqlExpressionType.SmallerOrEqualThan:
return(left.IsSmallerOrEqualThan(right));
case SqlExpressionType.Equal:
return(left.IsEqualTo(right));
case SqlExpressionType.NotEqual:
return(left.IsNotEqualTo(right));
case SqlExpressionType.Is:
return(left.Is(right));
case SqlExpressionType.IsNot:
return(left.IsNot(right));
case SqlExpressionType.Like:
return(left.IsLike(right));
case SqlExpressionType.NotLike:
return(left.IsNotLike(right));
case SqlExpressionType.And:
return(left.And(right));
case SqlExpressionType.Or:
return(left.Or(right));
case SqlExpressionType.XOr:
return(left.XOr(right));
// TODO: ANY and ALL
default:
throw new ExpressionEvaluateException(String.Format("The type {0} is not a binary expression or is not supported.", binaryType));
}
}
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 SqlBinaryExpression Binary(SqlExpression left, SqlExpressionType expressionType, SqlExpression right)
{
if (expressionType == SqlExpressionType.Add)
return Add(left, right);
if (expressionType == SqlExpressionType.Subtract)
return Subtract(left, right);
if (expressionType == SqlExpressionType.Multiply)
return Multiply(left, right);
if (expressionType == SqlExpressionType.Divide)
return Divide(left, right);
if (expressionType == SqlExpressionType.Modulo)
return Modulo(left, right);
if (expressionType == SqlExpressionType.Equal)
return Equal(left, right);
if (expressionType == SqlExpressionType.NotEqual)
return NotEqual(left, right);
if (expressionType == SqlExpressionType.Is)
return Is(left, right);
if (expressionType == SqlExpressionType.IsNot)
return IsNot(left, right);
if (expressionType == SqlExpressionType.GreaterThan)
return GreaterThan(left, right);
if (expressionType == SqlExpressionType.GreaterOrEqualThan)
return GreaterOrEqualThan(left, right);
if (expressionType == SqlExpressionType.SmallerThan)
return SmallerThan(left, right);
if (expressionType == SqlExpressionType.SmallerOrEqualThan)
return SmallerOrEqualThan(left, right);
if (expressionType == SqlExpressionType.Like)
return Like(left, right);
if (expressionType == SqlExpressionType.NotLike)
return NotLike(left, right);
if (expressionType == SqlExpressionType.And)
return And(left, right);
if (expressionType == SqlExpressionType.Or)
return Or(left, right);
if (expressionType == SqlExpressionType.XOr)
return XOr(left, right);
if (expressionType.IsAny())
return Any(left, expressionType.SubQueryPlainType(), right);
if (expressionType.IsAll())
return All(left, expressionType.SubQueryPlainType(), right);
throw new ArgumentException(String.Format("Expression type {0} is not a Binary", expressionType));
}
public static SqlBinaryExpression Binary(SqlExpression left, SqlExpressionType expressionType, SqlExpression right)
{
if (expressionType == SqlExpressionType.Add)
{
return(Add(left, right));
}
if (expressionType == SqlExpressionType.Subtract)
{
return(Subtract(left, right));
}
if (expressionType == SqlExpressionType.Multiply)
{
return(Multiply(left, right));
}
if (expressionType == SqlExpressionType.Divide)
{
return(Divide(left, right));
}
if (expressionType == SqlExpressionType.Modulo)
{
return(Modulo(left, right));
}
if (expressionType == SqlExpressionType.Equal)
{
return(Equal(left, right));
}
if (expressionType == SqlExpressionType.NotEqual)
{
return(NotEqual(left, right));
}
if (expressionType == SqlExpressionType.Is)
{
return(Is(left, right));
}
if (expressionType == SqlExpressionType.IsNot)
{
return(IsNot(left, right));
}
if (expressionType == SqlExpressionType.GreaterThan)
{
return(GreaterThan(left, right));
}
if (expressionType == SqlExpressionType.GreaterOrEqualThan)
{
return(GreaterOrEqualThan(left, right));
}
if (expressionType == SqlExpressionType.SmallerThan)
{
return(SmallerThan(left, right));
}
if (expressionType == SqlExpressionType.SmallerOrEqualThan)
{
return(SmallerOrEqualThan(left, right));
}
if (expressionType == SqlExpressionType.Like)
{
return(Like(left, right));
}
if (expressionType == SqlExpressionType.NotLike)
{
return(NotLike(left, right));
}
if (expressionType == SqlExpressionType.And)
{
return(And(left, right));
}
if (expressionType == SqlExpressionType.Or)
{
return(Or(left, right));
}
if (expressionType == SqlExpressionType.XOr)
{
return(XOr(left, right));
}
if (expressionType.IsAny())
{
return(Any(left, expressionType.SubQueryPlainType(), right));
}
if (expressionType.IsAll())
{
return(All(left, expressionType.SubQueryPlainType(), right));
}
throw new ArgumentException(String.Format("Expression type {0} is not a Binary", expressionType));
}