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