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)); }