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