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