public static ITable SimpleJoin(this ITable theTable, IQueryContext context, ITable table, ObjectName columnName, Operator op, Expression expression) { Table t = (Table)theTable; // Find the row with the name given in the condition. int lhsColumn = t.FindFieldName(columnName); if (lhsColumn == -1) { throw new Exception("Unable to find the LHS column specified in the condition: " + columnName); } // Create a variable resolver that can resolve columns in the destination // table. Table.TableVariableResolver resolver = ((Table)table).GetVariableResolver(); // The join algorithm. It steps through the RHS expression, selecting the // cells that match the relation from the LHS table (this table). List <long> thisRowSet = new List <long>(); List <long> tableRowSet = new List <long>(); IEnumerator <long> e = table.GetRowEnumerator(); while (e.MoveNext()) { long rowIndex = e.Current; resolver.SetId = (int)rowIndex; // Resolve expression into a constant. DataObject value = expression.Evaluate(resolver, context); // Select all the rows in this table that match the joining condition. IList <long> selectedSet = theTable.SelectRows(lhsColumn, op, value).ToList(); // Include in the set. int size = selectedSet.Count; for (int i = 0; i < size; ++i) { tableRowSet.Add(rowIndex); } thisRowSet.AddRange(selectedSet); } // Create the new VirtualTable with the joined tables. Table[] tabs = new Table[] { (Table)theTable, (Table)table }; IList <long>[] rowSets = new IList <long>[] { thisRowSet, tableRowSet }; VirtualTable outTable = new VirtualTable(tabs); outTable.Set(tabs, rowSets); return(outTable); }
public static ITable Distinct(this ITable table, int[] columns) { var theTable = table as Table; if (theTable == null) { return(table); } List <long> resultList = new List <long>(); IList <long> rowList = theTable.OrdereddRows(columns); long rowCount = rowList.Count; long previousRow = -1; for (int i = 0; i < rowCount; ++i) { long rowIndex = rowList[i]; if (previousRow != -1) { bool equal = true; // Compare cell in column in this row with previous row. for (int n = 0; n < columns.Length && equal; ++n) { DataObject c1 = table.GetValue(columns[n], rowIndex); DataObject c2 = table.GetValue(columns[n], previousRow); equal = (c1.CompareTo(c2) == 0); } if (!equal) { resultList.Add(rowIndex); } } else { resultList.Add(rowIndex); } previousRow = rowIndex; } // Return the new table with distinct rows only. VirtualTable vt = new VirtualTable(theTable); vt.Set(theTable, resultList); return(vt); }
public static ITable RangeSelect(this ITable table, ObjectName columnName, SelectableRange[] ranges) { // If this table is empty then there is no range to select so // trivially return this object. if (table.RowCount == 0) { return(table); } // Are we selecting a black or null range? if (ranges == null || ranges.Length == 0) { // Yes, so return an empty table return(table.EmptySelect()); } // Are we selecting the entire range? if (ranges.Length == 1 && ranges[0].Equals(SelectableRange.FullRange)) { // Yes, so return this table. return(table); } // Must be a non-trivial range selection. var t = (Table)table; // Find the column index of the column selected int column = t.FindFieldName(columnName); if (column == -1) { throw new Exception("Unable to find the column given to select the range of: " + columnName.Name); } // Select the range IList <long> rows = table.SelectRange(column, ranges); // Make a new table with the range selected VirtualTable vTable = new VirtualTable((Table)table); vTable.Set((Table)table, rows); // We know the new set is ordered by the column. vTable.OptimisedPostSet(column); return(vTable); }
public static ITable EmptySelect(this ITable table) { if (table.RowCount == 0) { return(table); } var theTable = table as Table; if (theTable == null) { return(table); } VirtualTable vTable = new VirtualTable((Table)table); vTable.Set((Table)table, new List <long>(0)); return(vTable); }
public static ITable OrderBy(this ITable table, int column, bool ascending) { // Check the field can be sorted DataColumnInfo colInfo = table.TableInfo[column]; List <long> rows = new List <long>(table.SelectAll(column)); // Reverse the list if we are not ascending if (ascending == false) { rows.Reverse(); } // We now has an int[] array of rows from this table to make into a // new table. VirtualTable vTable = new VirtualTable((Table)table); vTable.Set((Table)table, rows); return(table); }
///<summary> /// Returns a Table that is this function table merged with the cross /// reference table. ///</summary> ///<param name="maxColumn"></param> /// <remarks> /// The result table includes only one row from each group. /// <para> /// The 'max_column' argument is optional (can be null). If it's set to a /// column in the reference table, then the row with the max value from the /// group is used as the group row. For example, 'Part.id' will return the /// row with the maximum part.id from each group. /// </para> /// </remarks> ///<returns></returns> public Table MergeWithReference(ObjectName maxColumn) { Table table = ReferenceTable; IList <long> rowList; if (wholeTableAsGroup) { // Whole table is group, so take top entry of table. rowList = new List <long>(1); IEnumerator <long> rowEnum = table.GetRowEnumerator(); if (rowEnum.MoveNext()) { rowList.Add(rowEnum.Current); } else { // MAJOR HACK: If the referencing table has no elements then we choose // an arbitary index from the reference table to merge so we have // at least one element in the table. // This is to fix the 'SELECT COUNT(*) FROM empty_table' bug. rowList.Add(Int32.MaxValue - 1); } } else if (table.RowCount == 0) { rowList = new List <long>(0); } else if (groupLinks != null) { // If we are grouping, reduce down to only include one row from each // group. if (maxColumn == null) { rowList = GetTopFromEachGroup(); } else { int col_num = ReferenceTable.FindFieldName(maxColumn); rowList = GetMaxFromEachGroup(col_num); } } else { // OPTIMIZATION: This should be optimized. It should be fairly trivial // to generate a Table implementation that efficiently merges this // function table with the reference table. // This means there is no grouping, so merge with entire table, long rowCount = table.RowCount; rowList = new List <long>((int)rowCount); IEnumerator <long> en = table.GetRowEnumerator(); while (en.MoveNext()) { rowList.Add(en.Current); } } // Create a virtual table that's the new group table merged with the // functions in this... Table[] tabs = new Table[] { table, this }; IList <long>[] rowSets = new IList <long>[] { rowList, rowList }; VirtualTable outTable = new VirtualTable(tabs); outTable.Set(tabs, rowSets); table = outTable; return(table); }
public static ITable All(this ITable table, IQueryContext context, Expression expression, Operator op, ITable other) { // Check the table only has 1 column if (other.TableInfo.ColumnCount != 1) { throw new ApplicationException("Input table <> 1 columns."); } // Handle trivial case of no entries to select from if (table.RowCount == 0) { return(table); } var theTable = table as Table; if (theTable == null) { return(table); } // If 'table' is empty then we return the complete set. ALL { empty set } // is always true. if (other.RowCount == 0) { return(table); } // Is the lhs expression a constant? if (expression.IsConstant()) { // We know lhs is a constant so no point passing arguments, DataObject value = expression.Evaluate(context); bool comparedToTrue; // The various operators if (op.IsEquivalent(Operator.Greater) || op.IsEquivalent(Operator.GreaterOrEqual)) { // Find the maximum value in the table DataObject cell = other.GetLastCell(0); comparedToTrue = CompareCells(value, cell, op); } else if (op.IsEquivalent(Operator.Smaller) || op.IsEquivalent(Operator.SmallerOrEqual)) { // Find the minimum value in the table DataObject cell = other.GetFirstCell(0); comparedToTrue = CompareCells(value, cell, op); } else if (op.IsEquivalent(Operator.Equal)) { // Only true if rhs is a single value DataObject cell = other.GetSingleCell(0); comparedToTrue = (cell != null && CompareCells(value, cell, op)); } else if (op.IsEquivalent(Operator.NotEqual)) { // true only if lhs_cell is not found in column. comparedToTrue = !other.ColumnContainsValue(0, value); } else { throw new ApplicationException("Don't understand operator '" + op + "' in ALL."); } // If matched return this table if (comparedToTrue) { return(table); } // No entries matches so return an empty table. return(table.EmptySelect()); } Table sourceTable; int colIndex; // Is the lhs expression a single variable? ObjectName expVar = expression.AsVariable(); // NOTE: It'll be less common for this part to be called. if (expVar == null) { // This is a complex expression so make a FunctionTable as our new // source. DatabaseQueryContext dbContext = (DatabaseQueryContext)context; FunctionTable funTable = new FunctionTable((Table)table, new[] { expression }, new [] { "1" }, dbContext); sourceTable = funTable; colIndex = 0; } else { // The expression is an easy to resolve reference in this table. sourceTable = (Table)table; colIndex = sourceTable.FindFieldName(expVar); if (colIndex == -1) { throw new ApplicationException("Can't find column '" + expVar + "'."); } } // Check that the first column of 'table' is of a compatible type with // source table column (lhs_col_index). // ISSUE: Should we convert to the correct type via a FunctionTable? DataColumnInfo sourceCol = sourceTable.TableInfo[colIndex]; DataColumnInfo destCol = other.TableInfo[0]; if (!sourceCol.DataType.IsComparable(destCol.DataType)) { throw new ApplicationException("The type of the sub-query expression " + sourceCol.DataType + " is incompatible " + "with the sub-query " + destCol.DataType + "."); } // We now have all the information to solve this query. // We work output 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. IList <long> selectList; if (op.IsEquivalent(Operator.Greater) || op.IsEquivalent(Operator.GreaterOrEqual)) { // Select the last from the set (the highest value), DataObject highestCell = other.GetLastCell(0); // Select from the source table all rows that are > or >= to the // highest cell, selectList = sourceTable.SelectRows(colIndex, op, highestCell).ToList(); } else if (op.IsEquivalent(Operator.Smaller) || op.IsEquivalent(Operator.SmallerOrEqual)) { // Select the first from the set (the lowest value), DataObject lowestCell = other.GetFirstCell(0); // Select from the source table all rows that are < or <= to the // lowest cell, selectList = sourceTable.SelectRows(colIndex, op, lowestCell).ToList(); } else if (op.IsEquivalent(Operator.Equal)) { // Select the single value from the set (if there is one). DataObject singleCell = other.GetSingleCell(0); if (singleCell != null) { // Select all from source_table all values that = this cell selectList = sourceTable.SelectRows(colIndex, op, singleCell).ToList(); } else { // No single value so return empty set (no value in LHS will equal // a value in RHS). return(table.EmptySelect()); } } else if (op.IsEquivalent(Operator.NotEqual)) { // Equiv. to NOT IN selectList = sourceTable.NotIn(other, colIndex, 0).ToList(); } else { throw new ApplicationException("Don't understand operator '" + op + "' in ALL."); } // Make into a table to return. VirtualTable rtable = new VirtualTable(theTable); rtable.Set((Table)table, selectList); return(rtable); }
public static ITable Any(this ITable theTable, IQueryContext context, Expression expression, Operator op, Table rightTable) { ITable table = rightTable; // Check the table only has 1 column if (table.TableInfo.ColumnCount != 1) { throw new ApplicationException("Input table <> 1 columns."); } // Handle trivial case of no entries to select from if (theTable.RowCount == 0) { return(theTable); } // If 'table' is empty then we return an empty set. ANY { empty set } is // always false. if (table.RowCount == 0) { return(theTable.EmptySelect()); } // Is the lhs expression a constant? if (expression.IsConstant()) { // We know lhs is a constant so no point passing arguments, DataObject value = expression.Evaluate(null, context); // Select from the table. IEnumerable <long> list = table.SelectRows(0, op, value); if (list.Any()) { // There's some entries so return the whole table, return(theTable); } // No entries matches so return an empty table. return(theTable.EmptySelect()); } Table sourceTable; int lhsColIndex; // Is the lhs expression a single variable? ObjectName expVar = expression.AsVariable(); // NOTE: It'll be less common for this part to be called. if (expVar == null) { // This is a complex expression so make a FunctionTable as our new // source. FunctionTable funTable = new FunctionTable((Table)theTable, new Expression[] { expression }, new String[] { "1" }, context); sourceTable = funTable; lhsColIndex = 0; } else { // The expression is an easy to resolve reference in this table. sourceTable = (Table)theTable; lhsColIndex = sourceTable.FindFieldName(expVar); if (lhsColIndex == -1) { throw new ApplicationException("Can't find column '" + expVar + "'."); } } // Check that the first column of 'table' is of a compatible type with // source table column (lhs_col_index). // ISSUE: Should we convert to the correct type via a FunctionTable? DataColumnInfo sourceCol = sourceTable.TableInfo[lhsColIndex]; DataColumnInfo destCol = table.TableInfo[0]; if (!sourceCol.DataType.IsComparable(destCol.DataType)) { throw new ApplicationException("The type of the sub-query expression " + sourceCol.DataType + " is incompatible " + "with the sub-query " + destCol.DataType + "."); } // We now have all the information to solve this query. // We work output 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. IList <long> selectRows; if (op.IsEquivalent(Operator.Greater) || op.IsEquivalent(Operator.GreaterOrEqual)) { // Select the first from the set (the lowest value), DataObject lowestCell = table.GetFirstCell(0); // Select from the source table all rows that are > or >= to the // lowest cell, selectRows = sourceTable.SelectRows(lhsColIndex, op, lowestCell).ToList(); } else if (op.IsEquivalent(Operator.Smaller) || op.IsEquivalent(Operator.SmallerOrEqual)) { // Select the last from the set (the highest value), DataObject highestCell = table.GetLastCell(0); // Select from the source table all rows that are < or <= to the // highest cell, selectRows = sourceTable.SelectRows(lhsColIndex, op, highestCell).ToList(); } else if (op.IsEquivalent(Operator.Equal)) { // Equiv. to IN selectRows = sourceTable.In(table, lhsColIndex, 0); } else if (op.IsEquivalent(Operator.NotEqual)) { // Select the value that is the same of the entire column DataObject cell = table.GetSingleCell(0); if (cell != null) { // All values from 'source_table' that are <> than the given cell. selectRows = sourceTable.SelectRows(lhsColIndex, op, cell).ToList(); } else { // No, this means there are different values in the given set so the // query evaluates to the entire table. return(theTable); } } else { throw new ApplicationException("Don't understand operator '" + op + "' in ANY."); } // Make into a table to return. VirtualTable rtable = new VirtualTable((Table)theTable); rtable.Set((Table)theTable, selectRows); return(rtable); }
public static ITable Outer(this ITable theTable, ITable rightTable) { // Form the row list for right hand table, List <long> rowList = new List <long>((int)rightTable.RowCount); IEnumerator <long> e = rightTable.GetRowEnumerator(); while (e.MoveNext()) { rowList.Add(e.Current); } var t = (Table)theTable; int colIndex = ((Table)rightTable).FindFieldName(t.GetResolvedVariable(0)); ((Table)rightTable).SetToRowTableDomain(colIndex, rowList, t); // This row set List <long> thisTableSet = new List <long>((int)theTable.RowCount); e = theTable.GetRowEnumerator(); while (e.MoveNext()) { thisTableSet.Add(e.Current); } // 'rowList' is now the rows in this table that are in 'rtable'. // Sort both 'thisTableSet' and 'rowList' thisTableSet.Sort(); rowList.Sort(); // Find all rows that are in 'this_table_set' and not in 'row_list' List <long> resultList = new List <long>(96); int size = thisTableSet.Count; int rowListIndex = 0; int rowListSize = rowList.Count; for (int i = 0; i < size; ++i) { long thisVal = thisTableSet[i]; if (rowListIndex < rowListSize) { long inVal = rowList[rowListIndex]; if (thisVal < inVal) { resultList.Add(thisVal); } else if (thisVal == inVal) { while (rowListIndex < rowListSize && rowList[rowListIndex] == inVal) { ++rowListIndex; } } else { throw new ApplicationException("'this_val' > 'in_val'"); } } else { resultList.Add(thisVal); } } // Return the new VirtualTable VirtualTable table = new VirtualTable(t); table.Set(t, resultList); return(table); }
///<summary> /// Returns a Table that is this function table merged with the cross /// reference table. ///</summary> ///<param name="maxColumn"></param> /// <remarks> /// The result table includes only one row from each group. /// <para> /// The 'max_column' argument is optional (can be null). If it's set to a /// column in the reference table, then the row with the max value from the /// group is used as the group row. For example, 'Part.id' will return the /// row with the maximum part.id from each group. /// </para> /// </remarks> ///<returns></returns> public Table MergeWithReference(ObjectName maxColumn) { Table table = ReferenceTable; IList<long> rowList; if (wholeTableAsGroup) { // Whole table is group, so take top entry of table. rowList = new List<long>(1); IEnumerator<long> rowEnum = table.GetRowEnumerator(); if (rowEnum.MoveNext()) { rowList.Add(rowEnum.Current); } else { // MAJOR HACK: If the referencing table has no elements then we choose // an arbitary index from the reference table to merge so we have // at least one element in the table. // This is to fix the 'SELECT COUNT(*) FROM empty_table' bug. rowList.Add(Int32.MaxValue - 1); } } else if (table.RowCount == 0) { rowList = new List<long>(0); } else if (groupLinks != null) { // If we are grouping, reduce down to only include one row from each // group. if (maxColumn == null) { rowList = GetTopFromEachGroup(); } else { int col_num = ReferenceTable.FindFieldName(maxColumn); rowList = GetMaxFromEachGroup(col_num); } } else { // OPTIMIZATION: This should be optimized. It should be fairly trivial // to generate a Table implementation that efficiently merges this // function table with the reference table. // This means there is no grouping, so merge with entire table, long rowCount = table.RowCount; rowList = new List<long>((int)rowCount); IEnumerator<long> en = table.GetRowEnumerator(); while (en.MoveNext()) { rowList.Add(en.Current); } } // Create a virtual table that's the new group table merged with the // functions in this... Table[] tabs = new Table[] { table, this }; IList<long>[] rowSets = new IList<long>[] { rowList, rowList }; VirtualTable outTable = new VirtualTable(tabs); outTable.Set(tabs, rowSets); table = outTable; return table; }
public static ITable SelectRange(this ITable thisTable, ObjectName columnName, IndexRange[] ranges) { // If this table is empty then there is no range to select so // trivially return this object. if (thisTable.RowCount == 0) return thisTable; // Are we selecting a black or null range? if (ranges == null || ranges.Length == 0) // Yes, so return an empty table return thisTable.EmptySelect(); // Are we selecting the entire range? if (ranges.Length == 1 && ranges[0].Equals(IndexRange.FullRange)) // Yes, so return this table. return thisTable; // Must be a non-trivial range selection. // Find the column index of the column selected int column = thisTable.IndexOfColumn(columnName); if (column == -1) { throw new Exception( "Unable to find the column given to select the range of: " + columnName.Name); } // Select the range var rows = thisTable.SelectRowsRange(column, ranges); // Make a new table with the range selected var result = new VirtualTable(thisTable, rows.ToArray()); // We know the new set is ordered by the column. result.SortColumn = column; return result; }
public static ITable Join(this ITable table, ITable otherTable, bool quick) { ITable outTable; if (quick) { // This implementation doesn't materialize the join outTable = new NaturallyJoinedTable(table, otherTable); } else { var tabs = new [] { table, otherTable}; var rowSets = new IList<int>[2]; // Optimized trivial case, if either table has zero rows then result of // join will contain zero rows also. if (table.RowCount == 0 || otherTable.RowCount == 0) { rowSets[0] = new List<int>(0); rowSets[1] = new List<int>(0); } else { // The natural join algorithm. List<int> thisRowSet = new List<int>(); List<int> tableRowSet = new List<int>(); // Get the set of all rows in the given table. var tableSelectedSet = otherTable.Select(x => x.RowId.RowNumber).ToList(); int tableSelectedSetSize = tableSelectedSet.Count; // Join with the set of rows in this table. var e = table.GetEnumerator(); while (e.MoveNext()) { int rowIndex = e.Current.RowId.RowNumber; for (int i = 0; i < tableSelectedSetSize; ++i) { thisRowSet.Add(rowIndex); } tableRowSet.AddRange(tableSelectedSet); } // The row sets we are joining from each table. rowSets[0] = thisRowSet; rowSets[1] = tableRowSet; } // Create the new VirtualTable with the joined tables. outTable = new VirtualTable(tabs, rowSets); } return outTable; }
public static ITable ExhaustiveSelect(this ITable table, IQueryContext context, SqlExpression expression) { var result = table; // Exit early if there's nothing in the table to select from int rowCount = table.RowCount; if (rowCount > 0) { var tableResolver = table.GetVariableResolver(); List<int> selectedSet = new List<int>(rowCount); foreach (var row in table) { int rowIndex = row.RowId.RowNumber; var rowResolver = tableResolver.ForRow(rowIndex); // Resolve expression into a constant. var exp = expression.Evaluate(context, rowResolver); if (exp.ExpressionType != SqlExpressionType.Constant) throw new NotSupportedException(); var value = ((SqlConstantExpression) exp).Value; // If resolved to true then include in the selected set. if (!value.IsNull && value.Type is BooleanType && value == true) { selectedSet.Add(rowIndex); } } result = new VirtualTable(table, selectedSet); ; } return result; }