Пример #1
0
        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());
        }