public static ITable GroupConcat(QueryProcessor processor, bool distinct, ITable group, Expression[] args)
{
// The output string
StringBuilder return_string = new StringBuilder();
// Find the distinct subset of group
if (distinct)
group = processor.DistinctSubset(group, args);
// Push the group table onto the processor stack
processor.PushTable(group);
// Iterator over the group
IRowCursor i = group.GetRowCursor();
bool first = true;
while (i.MoveNext()) {
RowId rowid = i.Current;
processor.UpdateTableRow(rowid);
foreach (Expression op in args) {
ITable val = processor.Execute(op);
SqlObject ob = QueryProcessor.Result(val)[0];
if (!ob.IsNull) {
if (!first) {
return_string.Append(", ");
}
return_string.Append(SqlValue.FromObject(ob.Value).ToString());
first = false;
}
}
}
// Pop the table and return the result
processor.PopTable();
return QueryProcessor.ResultTable(new SqlObject(return_string.ToString()));
}
private ITable SortFilter(ITable table, FilterExpression expression)
{
// The filter operation which is a function that describes the sort terms
Expression filterExp = expression.Filter;
if (!(filterExp is FunctionExpression))
throw new ArgumentException("Expected a function as argument to the filter.");
ITable resultTable = table;
// If there's something to sort,
if (table.RowCount > 1) {
// Get the composite function representing the sort collation,
FunctionExpression compositeExp = (FunctionExpression) filterExp;
if (!compositeExp.Name.Equals("composite"))
throw new ArgumentException("Invalid composite function for sorting.");
// The natural ordering of the child
Expression naturalChildOrder = GetTableOrderComposite(table);
if (naturalChildOrder != null) {
if (naturalChildOrder.Equals(compositeExp))
// No sort necessary, already sorted
return table;
// TODO: test for the reverse condition, which we can optimize
// with a reverse row iterator.
}
int paramCount = compositeExp.Parameters.Count;
int termCount = paramCount / 2;
IIndexSetDataSource rowIndex;
bool naturalOrder = true;
// If 1 sort term,
if (termCount == 1) {
Expression sortExp = (Expression) compositeExp.Parameters[0];
naturalOrder = SqlObject.Equals((SqlObject)compositeExp.Parameters[1], SqlObject.True);
// Get the index candidate
string indexName = sortExp.IndexCandidate;
TableName indexTableName = sortExp.IndexTableName;
// Index available?
rowIndex = GetIndex(table, indexName);
} else {
// Multiple terms,
// Get the index candidate if there is one
string indexName = compositeExp.IndexCandidate;
TableName indexTableame = compositeExp.IndexTableName;
// Index available?
rowIndex = GetIndex(table, indexName);
}
// If we have an index,
if (rowIndex != null) {
IRowCursor sortedCursor = rowIndex.Select(SelectableRange.Full);
if (!naturalOrder)
// Reverse iterator,
sortedCursor = new ReverseRowCursor(sortedCursor);
SubsetTable sortedTable = new SubsetTable(table, sortedCursor);
sortedTable.IndexRequestFallthrough = true;
// Set the order composite function the describes how the subset is
// naturally sorted.
sortedTable.OrderComposite = (Expression) compositeExp.Clone();
resultTable = sortedTable;
} else {
// NOTE: There's lots of directions we can take for optimizing this
// sort. For example, if the number of values being sorted meets some
// criteria (such as all integers and less than 2 millions values)
// then the values being sorted could be read onto the heap and sorted
// in memory with a quick sort.
// Scan sort,
// The working set,
IIndex<RowId> workingSet = transaction.CreateTemporaryIndex<RowId>(table.RowCount);
// Create the resolver
IndexResolver resolver = CreateResolver(table, compositeExp);
// Iterator over the source table
IRowCursor tableCursor = table.GetRowCursor();
// Wrap in a forward prefetch iterator
tableCursor = new PrefetchRowCursor(tableCursor, table);
// Use a buffer,
RowId[] rowIds = new RowId[128];
while (tableCursor.MoveNext()) {
int count = 0;
while (tableCursor.MoveNext() && count < 128) {
rowIds[count] = tableCursor.Current;
++count;
}
for (int i = 0; i < count; ++i) {
RowId rowid = rowIds[i];
// Get the value,
SqlObject[] values = resolver.GetValue(rowid);
// Insert the record into sorted order in the working_set
workingSet.Insert(values, rowid, resolver);
}
}
// TODO: record 'workingSet' for future resolution.
// The result,
IRowCursor sortedCursor = new DefaultRowCursor(workingSet.GetCursor());
SubsetTable sortedTable = new SubsetTable(table, sortedCursor);
sortedTable.IndexRequestFallthrough = true;
// Set the order composite function the describes how the subset is
// naturally sorted.
sortedTable.OrderComposite = (Expression) compositeExp.Clone();
resultTable = sortedTable;
}
}
return resultTable;
}
internal ITable FilterByScan(ITable table, Expression op)
{
// Default is to match table,
ITable resultTable = table;
long rowCount = table.RowCount;
// If the table is not empty,
if (rowCount > 0) {
// Put the table on the stack,
PushTable(table);
// The working set,
IIndex<RowId> workingSet = transaction.CreateTemporaryIndex<RowId>(rowCount);
// TODO: Common expression scans should be optimized
// Default scan (works for everything)
// Fetch the table's row iterator
IRowCursor cursor = table.GetRowCursor();
// Wrap in a forward prefetch iterator
cursor = new PrefetchRowCursor(cursor, table);
// True if all matched
bool allMatched = true;
// For each value,
while (cursor.MoveNext()) {
// Fetch the next row_id from the iterator
RowId rowid = cursor.Current;
// Set the top of stack table row_id
UpdateTableRow(rowid);
// Execute the expression,
ITable expResult = DoExecute(op);
// If it's true, add the row_id to the working set
if (BooleanResult(expResult)) {
// Note, we preserve the ordering of the table being filtered
workingSet.Add(rowid);
} else {
// Wasn't a true result, so we didn't all match.
allMatched = false;
}
}
// If we matched nothing
if (workingSet.Count == 0) {
// Return a subset of the given table that is empty
SubsetTable subsetTable = new SubsetTable(table);
// We inherit the order composite description from the child.
subsetTable.SetOrderCompositeIsChild();
resultTable = subsetTable;
}
// If we matched something
else {
// If all matched we return the table
if (allMatched) {
// Clear the working set and set the result table
workingSet.Clear();
resultTable = table;
} else {
// Something in working set, and we didn't match everything,
IRowCursor setCursor = new DefaultRowCursor(workingSet.GetCursor());
SubsetTable subsetTable = new SubsetTable(table, setCursor);
// We inherit the order composite description from the child.
subsetTable.SetOrderCompositeIsChild();
resultTable = subsetTable;
}
}
// Pop the current table from the stack
PopTable();
}
return resultTable;
}
internal ITable DistinctSubset(ITable table, Expression[] exps)
{
// Trivial case of an empty table,
if (table.RowCount == 0)
return table;
IndexResolver resolver = CreateResolver(table, exps);
// The working set,
IIndex<RowId> workingSet = transaction.CreateTemporaryIndex<RowId>(table.RowCount);
// Iterate over the table
IRowCursor cursor = table.GetRowCursor();
// Wrap in a forward prefetch iterator
cursor = new PrefetchRowCursor(cursor, table);
while (cursor.MoveNext()) {
// The rowid
RowId rowid = cursor.Current;
// Fetch the SqlObject
SqlObject[] val = resolver.GetValue(rowid);
// TODO: How should DISTINCT behave for multiple columns when one of
// the items may or may not be null?
// Insert only if all the values are not null,
bool nullFound = false;
foreach (SqlObject v in val) {
if (v.IsNull)
nullFound = true;
}
if (!nullFound)
// Index it
workingSet.InsertUnique(val, rowid, resolver);
}
// Wrap it in an iterator and return, etc
return new SubsetTable(table, new DefaultRowCursor(workingSet.GetCursor()));
}
private ITable Join(ITable left, ITable right, JoinExpression op)
{
// Get the type of join,
JoinType joinType = op.JoinType;
// The filter expression
Expression filterExp = op.Filter;
// If it's a simple relation
bool simpleRelation = op.IsSimpleRelation;
// If the join is not a simple relation, then we need to naturally join
// and scan
if (!simpleRelation) {
JoinedTableBase result = new NaturalJoinedTable(left, right);
result.SetOrderCompositeIsChild();
if (filterExp != null)
// return the scan over the cartesian product
return FilterByScan(result, filterExp);
return result;
}
// This is a simple relation so we may not need to scan over the
// cartesian join. A simple relation is of the type '[something1]
// [comparison] [something2]' where something1 and 2 reference terms
// in the right and left tables exclusively, or a multi variable
// equivalence comparison such as 't1.a = t2.a and t1.b = t2.b'.
// A join of this type should always be a scan on the left and lookup
// on the right.
// The process cost (roughly)
long processCost = 0;
// NOTE, these are marked up by the QueryCostModel (perhaps should move
// this markup functionality in the planner.
IList<Expression> leftVarExps = (IList<Expression>)op.GetArgument("!left_var_exps");
IList<Expression> rightVarExps = (IList<Expression>)op.GetArgument("!right_var_exps");
IList<string> functionTypes = (IList<string>)op.GetArgument("!function_types");
// Right index, if applicable
string rIndexStr = (string)op.GetArgument("use_right_index");
TableName rIndexTableName = (TableName)op.GetArgument("use_right_index_table_name");
// If the right index is defined, then we know the cost model has
// determined the right table has a single index we can use.
IIndexSetDataSource rightIndex;
IndexResolver rightResolver;
if (rIndexStr != null) {
// Fetch the index
rightIndex = GetIndex(right, rIndexStr);
// If no index, we screwed up somewhere. Error in cost model most
// likely.
if (rightIndex == null)
throw new ApplicationException("Right index '" + rIndexStr + "' not found.");
// Create a resolver for the right table
IndexCollation rcollation = rightIndex.Collation;
rightResolver = new CollationIndexResolver(right, rcollation);
} else {
// No right index, so we need to prepare a temporary index
// We index on the right var ops (note that 'right_var_ops' will not
// necessarily be a variable reference, it may be a complex expression).
// Create the resolver for the term(s) on the right table
Expression[] rops = new Expression[rightVarExps.Count];
rightVarExps.CopyTo(rops, 0);
rightResolver = CreateResolver(right, rops);
// The working set,
IIndex<RowId> workingSet = transaction.CreateTemporaryIndex<RowId>(right.RowCount);
// Iterate over the right table
IRowCursor rightCursor = right.GetRowCursor();
// Wrap in a forward prefetch cursor
rightCursor = new PrefetchRowCursor(rightCursor, right);
while (rightCursor.MoveNext()) {
// The rowid
RowId rowid = rightCursor.Current;
// Fetch the SqlObject
SqlObject[] value = rightResolver.GetValue(rowid);
// Index it
workingSet.Insert(value, rowid, rightResolver);
}
// Map this into a RowIndex object,
rightIndex = new IndexBasedIndexSetDataSource(right, rightResolver, workingSet);
// Rough cost estimate of a sort on the right elements
processCost += rightCursor.Count * 5;
}
// Now we have a rightIndex and rightResolver that describes the keys
// we are searching for. Scan the left table and lookup values in the
// right.
// The join function
string joinFunctionName = functionTypes[0];
// Work out the maximum number of elements needed to perform this join
long maxSize;
long leftSize = left.RowCount;
long rightSize = right.RowCount;
// Make sure to account for the possibility of overflow
if (leftSize < Int32.MaxValue && rightSize < Int32.MaxValue) {
maxSize = leftSize * rightSize;
} else {
// This is a poor estimate, but it meets the requirements of the
// contract of 'createTemporaryIndex'. Idea: use a BigDecimal here?
maxSize = Int64.MaxValue;
}
// Allocate the indexes
IIndex<RowId> leftSet = transaction.CreateTemporaryIndex<RowId>(maxSize);
IIndex<RowId> rightSet = transaction.CreateTemporaryIndex<RowId>(maxSize);
// Create a resolver for the left terms
Expression[] lops = new Expression[leftVarExps.Count];
leftVarExps.CopyTo(lops, 0);
IndexResolver leftResolver = CreateResolver(left, lops);
// Cursor over the left table
IRowCursor leftCursor = left.GetRowCursor();
// Wrap in a forward prefetch cursor
leftCursor = new PrefetchRowCursor(leftCursor, left);
while (leftCursor.MoveNext()) {
// The left rowid
RowId leftRowid = leftCursor.Current;
// TODO: Need to change this to support multi-column join
// conditions,
// Fetch it into a SqlObject
SqlObject[] value = leftResolver.GetValue(leftRowid);
// lookup in the right
SelectableRange joinRange = SelectableRange.Full;
joinRange = joinRange.Intersect(SelectableRange.GetOperatorFromFunction(joinFunctionName), value);
IRowCursor matchedResult = rightIndex.Select(joinRange);
// If there are elements
if (matchedResult.Count > 0) {
// For each matched element, add a left rowid and right rowid
while (matchedResult.MoveNext()) {
RowId rightRowid = matchedResult.Current;
leftSet.Add(leftRowid);
rightSet.Add(rightRowid);
}
} else {
// If there are no elements, is this an outer join?
if (joinType == JoinType.OuterLeft) {
// Yes, so add left with a null entry,
leftSet.Add(leftRowid);
rightSet.Add(null);
}
}
}
// Rough cost estimate on the scan/lookup
processCost += (left.RowCount + (left.RowCount * 5));
// Return the joined table.
JoinedTableBase joinTable = new JoinedTable(left, right, leftSet, rightSet);
joinTable.SetOrderCompositeIsChild();
return joinTable;
}
public EmbeddedQueryContext(ITable backedTable, SystemTransaction transaction, object syncObject)
{
this.syncObject = syncObject;
isClosed = false;
this.backedTable = backedTable;
// Determine the updatability of the result set
notNotUpdatableReason = null;
// If the result set is a mutable table data source object,
if (backedTable is IMutableTable) {
updatableView = new UpdatableResultSetView(transaction, (IMutableTable) backedTable, null, backedTable.GetRowCursor());
} else {
// Can we map this to a native table?
TableName nativeTableName = QueryProcessor.GetNativeTableName(backedTable);
// If we can,
if (nativeTableName != null) {
// The top table must be an operation table and must have all
// FETCHVAR operations,
if (backedTable is ExpressionTable) {
ExpressionTable expressionTable = (ExpressionTable)backedTable;
Expression[] projectionExps = expressionTable.Expressions;
foreach (Expression op in projectionExps) {
if (QueryProcessor.GetAsVariableRef(op) == null) {
notNotUpdatableReason = "Not updatable, result set contains functional " +
"projections. Please simplify the select terms.";
break;
}
}
// Otherwise, if it all looks ok, set the updatable table
if (notNotUpdatableReason == null) {
SystemTable nativeTableSource = transaction.GetTable(nativeTableName);
updatableView = new UpdatableResultSetView(transaction, nativeTableSource, projectionExps, backedTable.GetRowCursor());
}
} else {
notNotUpdatableReason = "This result set is not updatable.";
}
} else {
notNotUpdatableReason = "Not updatable, result set does not source " +
"to a native table.";
}
// If we didn't create an updatable view, we create one with null values
// and use if for iterator caching only
if (updatableView == null) {
updatableView = new UpdatableResultSetView(null, null, null, backedTable.GetRowCursor());
}
}
}
private static ITable ProcessAggregate(QueryProcessor processor, bool distinct, ITable group, Expression[] args, IAggregateInspector aggregator)
{
// Qualify the return type of the parameter
SqlType type = processor.GetExpressionType(group, args[0]);
// If an empty group
if (group.RowCount == 0) {
// Null return type if group is empty,
return QueryProcessor.ResultTable(SqlObject.MakeNull(type));
}
// Find the distinct subset of group
if (distinct)
group = processor.DistinctSubset(group, args);
// Push the group table onto the processor stack
processor.PushTable(group);
// Scan the group table, returning null on a null value
IRowCursor i = group.GetRowCursor();
while (i.MoveNext()) {
RowId rowid = i.Current;
processor.UpdateTableRow(rowid);
ITable val = processor.Execute(args[0]);
SqlObject ob = QueryProcessor.Result(val)[0];
// If we hit a null value, we ignore it. SQL-92 apparently says we
// should generate a warning for nulls that are eliminated by set
// functions.
if (!ob.IsNull) {
aggregator.Accumulate(ob);
}
}
// Pop the table and return the result
processor.PopTable();
SqlObject result = aggregator.Result();
return QueryProcessor.ResultTable(result ?? SqlObject.MakeNull(type));
}
