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