/// <summary>
/// Prepares the join tree
/// </summary>
private JoinTrie PreparePrincipalJoin()
{
// construct the join tree
var allPaths = new List <string[]>();
if (_select != null)
{
allPaths.AddRange(_select.ColumnAccesses().Select(e => e.Path));
}
if (_orderby != null)
{
allPaths.AddRange(_orderby.ColumnAccesses().Select(e => e.Path));
}
if (_filter != null)
{
allPaths.AddRange(_filter.ColumnAccesses().Select(e => e.Path));
}
if (_having != null)
{
allPaths.AddRange(_having.ColumnAccesses().Select(e => e.Path));
}
// This will represent the mapping from paths to symbols
var joinTree = JoinTrie.Make(TypeDescriptor.Get <T>(), allPaths);
return(joinTree);
}
/// <summary>
/// Create the <see cref="JoinTrie"/> from the paths in all the arguments
/// </summary>
private JoinTrie PrepareJoin(ArraySegment <string>?pathToCollection = null)
{
// construct the join tree
var allPaths = new List <string[]>();
if (Select != null)
{
allPaths.AddRange(Select.Select(e => e.Path));
}
if (Expand != null)
{
allPaths.AddRange(Expand.Select(e => e.Path));
}
if (Filter != null)
{
allPaths.AddRange(Filter.ColumnAccesses().Select(e => e.Path));
}
if (OrderBy != null)
{
allPaths.AddRange(OrderBy.ColumnAccesses().Select(e => e.Path));
}
if (pathToCollection != null)
{
var pathToCollectionEntity = new ArraySegment <string>(
pathToCollection.Value.Array,
pathToCollection.Value.Offset,
pathToCollection.Value.Count - 1);
allPaths.Add(pathToCollectionEntity.ToArray());
}
// This will represent the mapping from paths to symbols
return(JoinTrie.Make(ResultDescriptor, allPaths));
}
/// <summary>
/// Prepares the SELECT statement and the column map, using the <see cref="Select"/> argument
/// </summary>
private SqlSelectClause PrepareSelect(JoinTrie joinTree)
{
var selects = new HashSet <(string Symbol, string PropName)>();
var columns = new List <(string Symbol, ArraySegment <string> Path, string PropName)>();
void AddSelect(string symbol, ArraySegment <string> path, string propName)
{
// NULL happens when there is a select that has been segmented from the middle
// and the first section of the segment no longer terminates with a simple property
// propName = propName ?? "Id";
if (propName == null)
{
return;
}
if (selects.Add((symbol, propName)))
{
columns.Add((symbol, path, propName));
}
}
// Any path step that is touched by a select (which has a property) ignores the expand, the joinTree below
// allows us to efficiently check if any particular step is touched by a select
JoinTrie overridingSelectTree = Select == null ? null : JoinTrie.Make(ResultDescriptor, Select.Select(e => e.Path)); // Overriding select paths
// Optimization: remember the joins that have been selected and don't select them again
HashSet <JoinTrie> selectedJoins = new HashSet <JoinTrie>();
// For every expanded entity that has not been tainted by a select argument, we add all its properties to the list of selects
Expand ??= ExpressionExpand.Empty;
foreach (var expand in Expand.Union(ExpressionExpand.RootSingleton))
{
string[] path = expand.Path;
for (int i = 0; i <= path.Length; i++)
{
var subpath = new ArraySegment <string>(path, 0, i);
var selectMatch = overridingSelectTree?[subpath];
if (selectMatch == null) // This expand is not overridden by a select
{
var join = joinTree[subpath];
if (join == null)
{
// Developer mistake
throw new InvalidOperationException($"The path '{string.Join('.', subpath)}' was not found in the joinTree");
}
else if (selectedJoins.Contains(join))
{
continue;
}
else
{
selectedJoins.Add(join);
}
foreach (var prop in join.EntityDescriptor.SimpleProperties)
{
AddSelect(join.Symbol, subpath, prop.Name);
}
}
}
}
if (Select != null)
{
foreach (var select in Select)
{
// Add the property
string[] path = select.Path;
{
var join = joinTree[path];
var propName = select.Property; // Can be null
AddSelect(join.Symbol, path, propName);
}
// In this loop we ensure all levels to the selected properties
// have their Ids and Foreign Keys added to the select collection
for (int i = 0; i <= path.Length; i++)
{
var subpath = new ArraySegment <string>(path, 0, i);
var join = joinTree[subpath];
if (join == null)
{
// Developer mistake
throw new InvalidOperationException($"The path '{string.Join('.', subpath)}' was not found in the joinTree");
}
else if (selectedJoins.Contains(join))
{
// All properties were added earlier in an expand
continue;
}
else
{
selectedJoins.Add(join);
}
// The Id is ALWAYS required in every EntityWithKey
if (join.EntityDescriptor.HasId)
{
AddSelect(join.Symbol, subpath, "Id");
}
// Add all the foreign keys to the next level down
foreach (var nextJoin in join.Values)
{
AddSelect(join.Symbol, subpath, nextJoin.ForeignKeyName);
}
}
}
}
// If the foreign key to the principal query is specified, then always include that
// otherwise there will be no way to link the collection to the principal query once we load the data
if (!string.IsNullOrWhiteSpace(ForeignKeyToPrincipalQuery))
{
var path = new string[0];
AddSelect(joinTree.Symbol, path, ForeignKeyToPrincipalQuery);
}
// Deals with trees
foreach (var path in PathsToParentEntitiesWithExpandedAncestors)
{
var join = joinTree[path];
AddSelect(join.Symbol, path, "ParentId");
}
if (IsAncestorExpand)
{
var path = new string[0];
AddSelect(joinTree.Symbol, path, "ParentId");
}
// Change the hash set to a list so that the order is well defined
return(new SqlSelectClause(columns));
}
public async Task <(List <DynamicRow> result, IEnumerable <TreeDimensionResult> trees)> ToListAsync(CancellationToken cancellation)
{
var queryArgs = await _factory(cancellation);
var conn = queryArgs.Connection;
var sources = queryArgs.Sources;
var userId = queryArgs.UserId;
var userToday = queryArgs.UserToday;
var localizer = queryArgs.Localizer;
var logger = queryArgs.Logger;
// ------------------------ Validation Step
// SELECT Validation
if (_select == null)
{
string message = $"The select argument is required";
throw new InvalidOperationException(message);
}
// Make sure that measures are well formed: every column access is wrapped inside an aggregation function
foreach (var exp in _select)
{
if (exp.ContainsAggregations) // This is a measure
{
// Every column access must descend from an aggregation function
var exposedColumnAccess = exp.UnaggregatedColumnAccesses().FirstOrDefault();
if (exposedColumnAccess != null)
{
throw new QueryException($"Select parameter contains a measure with a column access {exposedColumnAccess} that is not included within an aggregation.");
}
}
}
// ORDER BY Validation
if (_orderby != null)
{
foreach (var exp in _orderby)
{
// Order by cannot be a constant
if (!exp.ContainsAggregations && !exp.ContainsColumnAccesses)
{
throw new QueryException("OrderBy parameter cannot be a constant, every orderby expression must contain either an aggregation or a column access.");
}
}
}
// FILTER Validation
if (_filter != null)
{
var conditionWithAggregation = _filter.Expression.Aggregations().FirstOrDefault();
if (conditionWithAggregation != null)
{
throw new QueryException($"Filter contains a condition with an aggregation function: {conditionWithAggregation}");
}
}
// HAVING Validation
if (_having != null)
{
// Every column access must descend from an aggregation function
var exposedColumnAccess = _having.Expression.UnaggregatedColumnAccesses().FirstOrDefault();
if (exposedColumnAccess != null)
{
throw new QueryException($"Having parameter contains a column access {exposedColumnAccess} that is not included within an aggregation.");
}
}
// ------------------------ Preparation Step
// If all is good Prepare some universal variables and parameters
var vars = new SqlStatementVariables();
var ps = new SqlStatementParameters();
var today = userToday ?? DateTime.Today;
var now = DateTimeOffset.Now;
// ------------------------ Tree Analysis Step
// By convention if A.B.Id AND A.B.ParentId are both in the select expression,
// then this is a tree dimension and we return all the ancestors of A.B,
// What do we select for the ancestors? All non-aggregated expressions in
// the original select that contain column accesses exclusively starting with A.B
var additionalNodeSelects = new List <QueryexColumnAccess>();
var ancestorsStatements = new List <DimensionAncestorsStatement>();
{
// Find all column access atoms that terminate with ParentId, those are the potential tree dimensions
var parentIdSelects = _select
.Where(e => e is QueryexColumnAccess ca && ca.Property == "ParentId")
.Cast <QueryexColumnAccess>();
foreach (var parentIdSelect in parentIdSelects)
{
var pathToTreeEntity = parentIdSelect.Path; // A.B
// Confirm it's a tree dimension
var idSelect = _select.FirstOrDefault(e => e is QueryexColumnAccess ca && ca.Property == "Id" && ca.PathEquals(pathToTreeEntity));
if (idSelect != null)
{
// Prepare the Join Trie
var treeType = TypeDescriptor.Get <T>();
foreach (var step in pathToTreeEntity)
{
treeType = treeType.NavigationProperty(step)?.TypeDescriptor ??
throw new QueryException($"Property {step} does not exist on type {treeType.Name}.");
}
// Create or Get the name of the Node column
string nodeColumnName = NodeColumnName(additionalNodeSelects.Count);
additionalNodeSelects.Add(new QueryexColumnAccess(pathToTreeEntity, "Node")); // Tell the principal query to include this node
// Get all atoms that contain column accesses exclusively starting with A.B
var principalSelectsWithMatchingPrefix = _select
.Where(exp => exp.ColumnAccesses().All(ca => ca.PathStartsWith(pathToTreeEntity)));
// Calculate the target indices
var targetIndices = principalSelectsWithMatchingPrefix
.Select(exp => SelectIndexDictionary[exp]);
// Remove the prefix from all column accesses
var ancestorSelects = principalSelectsWithMatchingPrefix
.Select(exp => exp.Clone(prefixToRemove: pathToTreeEntity));
var allPaths = ancestorSelects.SelectMany(e => e.ColumnAccesses()).Select(e => e.Path);
var joinTrie = JoinTrie.Make(treeType, allPaths);
var joinSql = joinTrie.GetSql(sources, fromSql: null);
// Prepare the Context
var ctx = new QxCompilationContext(joinTrie, sources, vars, ps, today, now, userId);
// Prepare the SQL components
var selectSql = PrepareAncestorSelectSql(ctx, ancestorSelects);
var principalQuerySql = PreparePrincipalQuerySql(nodeColumnName);
// Combine the SQL components
string sql = QueryTools.CombineSql(
selectSql: selectSql,
joinSql: joinSql,
principalQuerySql: principalQuerySql,
whereSql: null,
orderbySql: null,
offsetFetchSql: null,
groupbySql: null,
havingSql: null,
selectFromTempSql: null);
// Get the index of the id select
int idIndex = SelectIndexDictionary[idSelect];
// Create and add the statement object
var statement = new DimensionAncestorsStatement(idIndex, sql, targetIndices);
ancestorsStatements.Add(statement);
}
}
}
// ------------------------ The SQL Generation Step
// (1) Prepare the JOIN's clause
var principalJoinTrie = PreparePrincipalJoin();
var principalJoinSql = principalJoinTrie.GetSql(sources, fromSql: null);
// Compilation context
var principalCtx = new QxCompilationContext(principalJoinTrie, sources, vars, ps, today, now, userId);
// (2) Prepare all the SQL clauses
var(principalSelectSql, principalGroupbySql, principalColumnCount) = PreparePrincipalSelectAndGroupBySql(principalCtx, additionalNodeSelects);
string principalWhereSql = PreparePrincipalWhereSql(principalCtx);
string principalHavingSql = PreparePrincipalHavingSql(principalCtx);
string principalOrderbySql = PreparePrincipalOrderBySql();
string principalSelectFromTempSql = PrepareSelectFromTempSql();
// (3) Put together the final SQL statement and return it
string principalSql = QueryTools.CombineSql(
selectSql: principalSelectSql,
joinSql: principalJoinSql,
principalQuerySql: null,
whereSql: principalWhereSql,
orderbySql: principalOrderbySql,
offsetFetchSql: null,
groupbySql: principalGroupbySql,
havingSql: principalHavingSql,
selectFromTempSql: principalSelectFromTempSql
);
// ------------------------ Execute SQL and return Result
var principalStatement = new SqlDynamicStatement(principalSql, principalColumnCount);
var(result, trees, _) = await EntityLoader.LoadDynamicStatement(
principalStatement : principalStatement,
dimAncestorsStatements : ancestorsStatements,
includeCount : false,
vars : vars,
ps : ps,
conn : conn,
logger : logger,
cancellation : cancellation);
return(result, trees);
}