Example #1
0
        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();
        }
Example #3
0
        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));
                }
            }
        }
Example #4
0
        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};
            }
        }