/// <summary>
/// Generate the query SQL. Do not actually run it.
/// </summary>
/// <param name="query">The structured query object to convert.</param>
/// <param name="settings">Build-time settings for the conversion.</param>
/// <returns>An object structure containing SQL, and other discovered information.</returns>
public QueryBuild BuildSql(StructuredQuery query, QuerySqlBuilderSettings settings)
{
if (query == null)
{
throw new ArgumentNullException("query");
}
// Initialise settings
if (settings == null)
{
settings = new QuerySqlBuilderSettings( );
}
// Optimise tree
StructuredQuery optimisedQuery = StructuredQueryHelper.PruneQueryTree(query);
// Generate SQL
QueryBuilder queryBuilder = new QueryBuilder(optimisedQuery, settings);
QueryBuild result = queryBuilder.GetSqlInternal( );
// Logging
using (MessageContext msg = new MessageContext("Reports"))
{
msg.Append(() => new string( '-', 50 ));
msg.Append(() => "Final structured query:\n" + StructuredQueryHelper.ToXml(optimisedQuery));
msg.Append(() => new string( '-', 50 ));
msg.Append(() => "SQL:\n" + result.Sql);
msg.Append(() => new string( '-', 50 ));
}
// Note: identify cache dependencies after getting SQL, so that any calculations are resolved into the structured query.
IdentifyCacheDependencies(optimisedQuery, settings);
return(result);
}
private void AssertNodes(StructuredQuery query, params Entity[] nodes)
{
var actual = StructuredQueryHelper.WalkNodes(query.RootEntity);
// need to compare IDs instead of nodes because mutations result in clones
Assert.That(actual.Select(n => n.NodeId), Is.EquivalentTo(nodes.Select(n => n.NodeId)), "Expected nodes");
}
public void Test_GetReferencedRelationships_NullRootEntity()
{
Assert.That(() => StructuredQueryHelper.GetReferencedRelationships(new StructuredQuery {
RootEntity = null
}),
Throws.TypeOf <ArgumentException>());
}
public void Test_FindNodePath_FourLevel()
{
ResourceEntity rootEntity;
ResourceEntity firstLevelEntity;
ResourceEntity secondLevelEntity;
ResourceEntity thirdLevelEntity;
Guid rootEntityNodeId = Guid.NewGuid();
Guid firstLevelEntityNodeId = Guid.NewGuid();
Guid secondLevelEntityNodeId = Guid.NewGuid();
Guid thirdLevelEntityNodeId = Guid.NewGuid();
rootEntity = new ResourceEntity {
NodeId = rootEntityNodeId
};
firstLevelEntity = new RelatedResource {
NodeId = firstLevelEntityNodeId
};
secondLevelEntity = new RelatedResource {
NodeId = secondLevelEntityNodeId
};
thirdLevelEntity = new RelatedResource {
NodeId = thirdLevelEntityNodeId
};
secondLevelEntity.RelatedEntities.Add(thirdLevelEntity);
firstLevelEntity.RelatedEntities.Add(secondLevelEntity);
rootEntity.RelatedEntities.Add(firstLevelEntity);
List <Entity> findNodePath = StructuredQueryHelper.FindNodePath(thirdLevelEntityNodeId, rootEntity);
Assert.AreEqual(findNodePath.Count, 3);
}
/// <summary>
/// Performs any special processing when the resource is serialized into XML.
/// </summary>
/// <param name="xmlWriter">The writer used to write the image.</param>
protected void OnToXml(System.Xml.XmlWriter xmlWriter)
{
if (this.StructuredQuery != null)
{
StructuredQueryHelper.ToXml(xmlWriter, this.StructuredQuery);
}
}
private static void RunReport(StringBuilder sb, long contextType, IExpression expr)
{
// Build structured query
ResourceEntity re = new ResourceEntity();
re.EntityTypeId = new EntityRef(contextType);
StructuredQuery sq = new StructuredQuery {
RootEntity = re
};
// Convert script to query
QueryBuilderSettings qsettings = new QueryBuilderSettings();
qsettings.StructuredQuery = sq;
qsettings.ContextEntity = re;
ScalarExpression resExpr = Factory.ExpressionCompiler.CreateQueryEngineExpression(expr, qsettings);
sb.AppendLine("OK");
// Render XML
sb.AppendLine("\nXML:");
sb.AppendLine(StructuredQueryHelper.ToXml(sq));
// Render SQL
sb.AppendLine("\nSQL:");
sq.SelectColumns.Add(new SelectColumn {
Expression = resExpr
});
sb.AppendLine("declare @tenant as bigint = (select min(Id) from _vTenant) -- test harness");
string sql = EDC.ReadiNow.Metadata.Query.Structured.Builder.QueryBuilder.GetSql(sq);
sb.AppendLine(sql);
}
/// <summary>
/// Identify cache dependencies.
/// </summary>
private static void IdentifyCacheDependencies(Report report, ReportToQueryConverterSettings settings, StructuredQuery query)
{
// Tell the cache what entities were referenced to return
// the StructuredQuery result
using (CacheContext cacheContext = CacheContext.GetContext( ))
{
cacheContext.Entities.Add(report.Id);
if (query.Conditions != null)
{
foreach (var condition in query.Conditions)
{
cacheContext.Entities.Add(condition.EntityId);
if (condition.Expression != null)
{
cacheContext.Entities.Add(condition.Expression.EntityId);
}
}
}
if (query.OrderBy != null)
{
foreach (var orderBy in query.OrderBy)
{
if (orderBy.Expression != null)
{
cacheContext.Entities.Add(orderBy.Expression.EntityId);
}
}
}
if (query.SelectColumns != null)
{
foreach (var column in query.SelectColumns)
{
cacheContext.Entities.Add(column.EntityId);
if (column.Expression != null)
{
cacheContext.Entities.Add(column.Expression.EntityId);
}
}
}
if (report.ReportOrderBys != null)
{
foreach (var orderBy in report.ReportOrderBys)
{
if (orderBy != null)
{
cacheContext.Entities.Add(orderBy.Id);
}
}
}
}
StructuredQueryHelper.IdentifyStructureCacheDependencies(query, settings.ConditionsOnly);
}
/// <summary>
/// Performs any special processing when the resource is reconstructed from its XML image.
/// </summary>
/// <param name="node">An XML node containing the resource's XML image.</param>
protected void OnFromXml(System.Xml.XmlNode node)
{
XmlNamespaceManager namespaces = new XmlNamespaceManager(node.OwnerDocument.NameTable);
namespaces.AddNamespace("q", Constants.StructuredQueryNamespace);
XmlNode queryNode = node.SelectSingleNode("q:Query", namespaces);
StructuredQueryHelper.FromXml(queryNode);
}
public void Test_VisitNodes(Entity node, IEnumerable <Tuple <Entity, IEnumerable <Entity> > > expectedResults)
{
List <Tuple <Entity, IEnumerable <Entity> > > actualResults;
actualResults = new List <Tuple <Entity, IEnumerable <Entity> > >();
StructuredQueryHelper.VisitNodes(node,
(entity, descendants) => actualResults.Add(new Tuple <Entity, IEnumerable <Entity> >(entity, descendants)));
Assert.That(actualResults, Is.EquivalentTo(expectedResults).Using(this));
}
public void Test_WalkExpressions_None(bool filteringOnly, int expected)
{
StructuredQuery sq = new StructuredQuery( );
sq.RootEntity = new ResourceEntity( );
var res = StructuredQueryHelper.WalkExpressions(sq, filteringOnly);
Assert.That(res.Count( ), Is.EqualTo(expected));
}
/// <summary>
/// Check cache dependencies. (Note: wrapped, so we can write tests guaranteeing we have the same implementation)
/// </summary>
internal static void IdentifyCacheDependencies(StructuredQuery query, QuerySqlBuilderSettings settings)
{
// Note: if we are suppressing the root type check, then it means that the caller will be joining the query into a larger query.
// (I.e. this is a security subquery in a secured report).
// If that is the case, then the parent query will already be registering invalidation watches for the type of that node.
// So we don't need to further add them for the security query as well.
// This is an important optimisation because there are security reports that apply to all resources, and get joined into nodes that
// are only for specific resource types. So without ignoring the root, we would basically invalidate every report as part of every entity change.
StructuredQueryHelper.IdentifyResultCacheDependencies(query, false, settings.SuppressRootTypeCheck || settings.SupportRootIdFilter);
}
private void RunTest(string testName, Action <StructuredQuery> additionalProcessing = null, int derivedTypesTempTableThreshold = -1)
{
XmlDocument doc = GetTestCaseXml( );
XmlNode test = doc.DocumentElement.SelectSingleNode("Test[@name='" + testName + "']");
if (test == null)
{
throw new Exception("Test not found in Query tests.xml: " + testName);
}
// Load test
StructuredQuery query;
try
{
query = StructuredQueryHelper.FromXml(test.FirstChild);
}
catch (Exception ex)
{
throw new Exception("Failed to load query for " + testName, ex);
}
query.TimeZoneName = TimeZoneHelper.SydneyTimeZoneName;
if (additionalProcessing != null)
{
additionalProcessing(query);
}
// Generate SQL
var settings = new QuerySettings {
Hint = "test", DebugMode = true, DerivedTypesTempTableThreshold = derivedTypesTempTableThreshold
};
QueryBuild result = QueryBuilder.GetSql(query, settings);
string sql = result.Sql;
sql = Canonical(sql);
// Get expected results
XmlNode selectSingleNode = test.SelectSingleNode("Expect/text()");
if (selectSingleNode != null)
{
string expected = Canonical(selectSingleNode.Value);
expected = expected.Replace("{userResource}", new EntityRef("core", "userResource").Id.ToString(CultureInfo.InvariantCulture));
Assert.AreEqual(expected, sql, "Generated SQL did not match expected.");
}
StructuredQueryHelper.ToXml(query);
}
public void NotPruned_ColumnReferesToRoot( )
{
StructuredQuery sq = new StructuredQuery( );
var root = sq.RootEntity = new ResourceEntity( );
AddColumn(sq, new IdExpression {
NodeId = root.NodeId
});
var pruned = StructuredQueryHelper.PruneQueryTree(sq);
Assert.That(pruned, Is.SameAs(sq));
AssertNodes(pruned, root);
}
public void Test_WalkExpressions_GroupBy(bool filteringOnly, int expected)
{
StructuredQuery sq = new StructuredQuery( );
AggregateEntity root = new AggregateEntity( );
sq.RootEntity = root;
root.GroupedEntity = new ResourceEntity( );
root.GroupBy.Add(new IdExpression( ));
root.GroupBy.Add(new MutateExpression {
Expression = new IdExpression( )
});
var res = StructuredQueryHelper.WalkExpressions(sq, filteringOnly);
Assert.That(res.Count( ), Is.EqualTo(expected));
}
public void Pruned_UnusedFirstRelationship( )
{
StructuredQuery sq = new StructuredQuery( );
var root = sq.RootEntity = new ResourceEntity( );
var related1 = new RelatedResource( );
root.RelatedEntities.Add(related1);
AddColumn(sq, new IdExpression {
NodeId = root.NodeId
});
var pruned = StructuredQueryHelper.PruneQueryTree(sq);
Assert.That(pruned, Is.Not.SameAs(sq));
AssertNodes(pruned, root);
}
public void Test_WalkExpressions_NodeCondition(bool filteringOnly, int expected)
{
StructuredQuery sq = new StructuredQuery( );
sq.RootEntity = new ResourceEntity {
Conditions = new List <ScalarExpression>( )
};
sq.RootEntity.Conditions.Add(new IdExpression( ));
sq.RootEntity.Conditions.Add(new MutateExpression {
Expression = new IdExpression( )
});
var res = StructuredQueryHelper.WalkExpressions(sq, filteringOnly);
Assert.That(res.Count( ), Is.EqualTo(expected));
}
/// <summary>
/// /// Get the queries for a given user and permission or operation.
/// </summary>
/// <param name="subjectId">
/// The ID of the <see cref="Subject"/>, that is a <see cref="UserAccount"/> or <see cref="Role"/> instance.
/// This cannot be negative.
/// </param>
/// <param name="permission">
/// The permission to get the query for. This should be one of <see cref="Permissions.Read"/>,
/// <see cref="Permissions.Modify"/> or <see cref="Permissions.Delete"/>. Or null to match all permissions.
/// </param>
/// <param name="securableEntityTypes">
/// The IDs of <see cref="SecurableEntity"/> types being accessed. Or null to match all entity types.
/// </param>
/// <returns>
/// The queries to run.
/// </returns>
/// <exception cref="ArgumentException">
/// <paramref name="subjectId"/> does not exist. Also, <paramref name="permission"/> should
/// be one of <see cref="Permissions.Read"/>, <see cref="Permissions.Modify"/> or <see cref="Permissions.Delete"/>
/// </exception>
public IEnumerable <AccessRuleQuery> GetQueries(long subjectId, [CanBeNull] EntityRef permission, [CanBeNull] IList <long> securableEntityTypes)
{
// Get all applicable access rules for this subject/permission/types
ICollection <AccessRule> accessRules = RuleRepository.GetAccessRules(subjectId, permission, securableEntityTypes);
IList <AccessRuleQuery> result = new List <AccessRuleQuery>( );
// Store the enties that, when changed, should invalidate this cache entry.
using (CacheContext cacheContext = CacheContext.GetContext())
using (new SecurityBypassContext())
{
foreach (AccessRule allowAccess in accessRules)
{
Report accessRuleReport = allowAccess.AccessRuleReport;
if (accessRuleReport == null)
{
continue;
}
if (allowAccess.ControlAccess == null)
{
continue;
}
// Load the report query graph
Report accessRuleReportGraph = ReportEntityRepository.Get <Report>(allowAccess.AccessRuleReport.Id, ReportHelpers.QueryPreloaderQuery);
// Convert the report to a structured query
StructuredQuery accessRuleReportQuery = Converter.Convert(accessRuleReportGraph, ConverterSettings);
StructuredQueryHelper.OptimiseAuthorisationQuery(accessRuleReportQuery);
// Add cache invalidations
// Consider using .. StructuredQueryHelper.IdentifyStructureCacheDependencies
// See also: cacheContext in EntityAccessControlChecker.CheckAccessControlByQuery
cacheContext.Entities.Add(accessRuleReport.Id);
// Should this rule be considered for reports?
bool ignoreForReports = allowAccess.AccessRuleIgnoreForReports == true;
// Create container object
AccessRuleQuery accessRuleQuery = new AccessRuleQuery(allowAccess.Id, accessRuleReport.Id, allowAccess.ControlAccess.Id, accessRuleReportQuery, ignoreForReports);
result.Add(accessRuleQuery);
}
}
return(result);
}
public void Test_WalkExpressions_OrderBy(bool filteringOnly, int expected)
{
StructuredQuery sq = new StructuredQuery( );
sq.RootEntity = new ResourceEntity( );
sq.OrderBy.Add(new OrderByItem {
Expression = new IdExpression()
});
sq.OrderBy.Add(new OrderByItem {
Expression = new MutateExpression {
Expression = new IdExpression( )
}
});
var res = StructuredQueryHelper.WalkExpressions(sq, filteringOnly);
Assert.That(res.Count( ), Is.EqualTo(expected));
}
public void NotPruned_UnusedRootAggregate( )
{
StructuredQuery sq = new StructuredQuery( );
var agg = new AggregateEntity( );
sq.RootEntity = agg;
var related2 = new RelatedResource( );
agg.GroupedEntity = related2;
var related3 = new RelatedResource( );
related2.RelatedEntities.Add(related3);
var pruned = StructuredQueryHelper.PruneQueryTree(sq);
Assert.That(pruned, Is.SameAs(sq));
AssertNodes(pruned, agg, related2, related3);
}
public void NotPruned_ColumnReferesToRelatedRelated( )
{
StructuredQuery sq = new StructuredQuery( );
var root = sq.RootEntity = new ResourceEntity( );
var related1 = new RelatedResource( );
root.RelatedEntities.Add(related1);
var related2 = new RelatedResource( );
related1.RelatedEntities.Add(related2);
AddColumn(sq, new IdExpression {
NodeId = related2.NodeId
});
var pruned = StructuredQueryHelper.PruneQueryTree(sq);
Assert.That(pruned, Is.SameAs(sq));
AssertNodes(pruned, root, related1, related2);
}
/// <summary>
/// Get the field types referenced in the query condition only. Ignore fields
/// elsewhere.
/// </summary>
/// <param name="structuredQuery">
/// The <see cref="StructuredQuery"/> to check. This cannot be null.
/// </param>
/// <returns>
/// A list of field types or empty, if the query references no fields.
/// </returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="structuredQuery"/> cannot be null.
/// </exception>
/// <exception cref="ArgumentException">
/// <paramref name="structuredQuery"/>'s Conditions property cannot be null.
/// </exception>
public static IList <EntityRef> GetFieldTypesReferencedByCondition(StructuredQuery structuredQuery)
{
if (structuredQuery == null)
{
throw new ArgumentNullException("structuredQuery");
}
if (structuredQuery.Conditions == null)
{
throw new ArgumentNullException("structuredQuery", "Conditions property cannot be null");
}
return(structuredQuery.Conditions
.SelectMany(cond => StructuredQueryHelper.WalkExpressions(cond.Expression))
.Where(se => se is ResourceDataColumn)
.Cast <ResourceDataColumn>()
.Select(rdc => rdc.FieldId)
.Distinct(EntityRefComparer.Instance)
.ToList());
}
/// <summary>
/// Assigns the column node unique identifier for expression.
/// </summary>
/// <param name="columns">The columns.</param>
/// <param name="reportColumn">The report column.</param>
/// <param name="context">The context.</param>
private static void AssignColumnNodeGuidForExpression(IEnumerable <SelectColumn> columns, ReportColumn reportColumn, FromEntityContext context)
{
// Get the GUID of the report column
Guid reportColumnGuid;
if (context.ReportColumnMap.ContainsKey(reportColumn.Id))
{
reportColumnGuid = context.ReportColumnMap[reportColumn.Id];
}
else
{
return;
}
// Look up the select column using the GUID
SelectColumn selectColumn = columns.FirstOrDefault(c => c.ColumnId == reportColumnGuid);
if (selectColumn == null)
{
return;
}
// Handle column reference expressions
if (selectColumn.Expression != null)
{
ColumnReferenceExpression columnReferenceExpression =
reportColumn.ColumnExpression.As <ColumnReferenceExpression>();
if (columnReferenceExpression != null)
{
long rootNodeId = columnReferenceExpression.ExpressionReferencesColumn.Id;
// Get the GUID for the referenced column rather than root node and populate
ColumnReference columnReference = selectColumn.Expression as ColumnReference;
if (columnReference != null)
{
columnReference.ColumnId = context.ReportColumnMap[rootNodeId];
}
}
else
{
ResolveExpressionToNode(StructuredQueryHelper.WalkExpressions(selectColumn.Expression), context);
}
}
}
/// <summary>
/// Get the relationship types referenced in the query.
/// </summary>
/// <param name="structuredQuery">
/// The <see cref="StructuredQuery"/> to check. This cannot be null.
/// </param>
/// <returns>
/// A list of relationship types or empty, if the query references no relationships.
/// </returns>
/// <exception cref="ArgumentNullException">
/// <paramref name="structuredQuery"/> cannot be null.
/// </exception>
/// <exception cref="ArgumentNullException">
/// <paramref name="structuredQuery"/>'s RootEntity property cannot be null.
/// </exception>
public static IList <EntityRef> GetReferencedRelationshipTypes(StructuredQuery structuredQuery)
{
if (structuredQuery == null)
{
throw new ArgumentNullException("structuredQuery");
}
if (structuredQuery.RootEntity == null)
{
throw new ArgumentNullException("structuredQuery", "RootEntity cannot be null");
}
HashSet <EntityRef> result;
result = new HashSet <EntityRef>();
IEnumerable <ResourceDataColumn> resourceDataColumns;
RelatedResource relatedResource;
resourceDataColumns = structuredQuery.Conditions
.SelectMany(cond => StructuredQueryHelper.WalkExpressions(cond.Expression))
.Where(se => se is ResourceDataColumn)
.Cast <ResourceDataColumn>();
foreach (ResourceDataColumn resourceDataColumn in resourceDataColumns)
{
StructuredQueryHelper.VisitNodes(structuredQuery.RootEntity, (node, ancestors) =>
{
if (node.NodeId == resourceDataColumn.NodeId)
{
relatedResource = node as RelatedResource;
if (relatedResource != null)
{
result.Add(relatedResource.RelationshipTypeId);
result.UnionWith(
ancestors.Where(n => n is RelatedResource)
.Cast <RelatedResource>()
.Select(rr => rr.RelationshipTypeId));
}
}
});
}
return(result.ToList());
}
/// <summary>
/// Resolves the references to hook up various node and column identifiers from the entity model context.
/// </summary>
/// <param name="query">The query.</param>
/// <param name="report">The report.</param>
/// <param name="context">The context.</param>
internal static void ResolveReferences(StructuredQuery query, Report report, FromEntityContext context)
{
if (query.RootEntity is AggregateEntity)
{
AggregateEntity ae = query.RootEntity as AggregateEntity;
foreach (ResourceDataColumn rdc in ae.GroupBy.OfType <ResourceDataColumn>())
{
rdc.NodeId = context.ReportNodeMap[rdc.SourceNodeEntityId];
}
}
// Resolve the Node Expressions for report columns
foreach (ReportColumn reportColumn in report.ReportColumns)
{
AssignColumnNodeGuidForExpression(query.SelectColumns, reportColumn, context);
}
// Resolve the Node expressions for the analyser
foreach (QueryCondition queryCondition in query.Conditions)
{
ResolveExpressionToNode(StructuredQueryHelper.WalkExpressions(queryCondition.Expression), context);
ColumnReference columnReference = queryCondition.Expression as ColumnReference;
//if the expression is ColumnReferenceExpression, the referenced report column must exists
if (columnReference != null && context.ColumnReferenceMap.ContainsKey(columnReference.ExpressionId) && context.ReportColumnMap.ContainsKey(context.ColumnReferenceMap[columnReference.ExpressionId]))
{
columnReference.ColumnId = context.ReportColumnMap[context.ColumnReferenceMap[columnReference.ExpressionId]];
}
}
// Resolve the order by expressions (NOTE: Am assuming that order by can only contain column references here)
foreach (ColumnReference columnReference in query.OrderBy.Select(orderByItem => orderByItem.Expression).OfType <ColumnReference>())
{
//the orderby expression is ColumnReferenceExpression, the referenced report column must exists
if (context.ColumnReferenceMap.ContainsKey(columnReference.ExpressionId) &&
context.ReportColumnMap.ContainsKey(context.ColumnReferenceMap[columnReference.ExpressionId]))
{
columnReference.ColumnId =
context.ReportColumnMap[context.ColumnReferenceMap[columnReference.ExpressionId]];
}
}
}
public void Test_FindNodePath_TwoLevel()
{
ResourceEntity rootEntity;
ResourceEntity firstLevelEntity;
Guid rootEntityNodeId = Guid.NewGuid();
Guid firstLevelEntityNodeId = Guid.NewGuid();
rootEntity = new ResourceEntity {
NodeId = rootEntityNodeId
};
firstLevelEntity = new RelatedResource {
NodeId = firstLevelEntityNodeId
};
rootEntity.RelatedEntities.Add(firstLevelEntity);
List <Entity> findNodePath = StructuredQueryHelper.FindNodePath(firstLevelEntityNodeId, rootEntity);
Assert.AreEqual(findNodePath.Count, 1);
}
/// <summary>
/// Get the structured query, possibly from cache.
/// </summary>
/// <param name="report">The report to convert.</param>
/// <param name="settings">The report run settings.</param>
/// <param name="suppressPreload">True if we should suppress preloading.</param>
/// <returns>The structured query.</returns>
private StructuredQuery GetStructuredQuery(Model.Report report, ReportSettings settings, bool suppressPreload)
{
using (MessageContext msg = new MessageContext("Reports"))
{
StructuredQuery immutableStructuredQuery;
StructuredQuery structuredQuery;
bool useStructuredQueryCache = settings.UseStructuredQueryCache;
ReportToQueryConverterSettings converterSettings = new ReportToQueryConverterSettings
{
SuppressPreload = suppressPreload,
RefreshCachedStructuredQuery = settings.RefreshCachedStructuredQuery,
SchemaOnly = settings.RequireSchemaMetadata
};
if (settings != null && settings.UseStructuredQueryCache)
{
// don't allow mutations of cached copy
immutableStructuredQuery = CachedReportToQueryConverter.Convert(report, converterSettings);
}
else
{
// don't allow mutations, just so we can log it correctly
immutableStructuredQuery = NonCachedReportToQueryConverter.Convert(report, converterSettings);
}
structuredQuery = immutableStructuredQuery.DeepCopy( ); // so we can mutate it (in case we need to)
// Logging
msg.Append(() => new String('-', 50));
msg.Append(() => "GetStructuredQuery");
msg.Append(() => "suppressPreload = " + suppressPreload);
msg.Append(() => "useStructuredQueryCache = " + useStructuredQueryCache);
msg.Append(() => "Structured Query:\n" + StructuredQueryHelper.ToXml(immutableStructuredQuery));
msg.Append(() => new String('-', 50));
return(structuredQuery);
}
}
public void NotPruned_ChildrenOfUsedAggregate( )
{
StructuredQuery sq = new StructuredQuery( );
var root = sq.RootEntity = new ResourceEntity( );
var agg = new AggregateEntity( );
root.RelatedEntities.Add(agg);
var related2 = new RelatedResource( );
agg.GroupedEntity = related2;
var related3 = new RelatedResource( );
related2.RelatedEntities.Add(related3);
AddColumn(sq, new IdExpression {
NodeId = agg.NodeId
});
var pruned = StructuredQueryHelper.PruneQueryTree(sq);
Assert.That(pruned, Is.SameAs(sq));
AssertNodes(pruned, root, agg, related2, related3);
}
/// <summary>
/// Remove any columns from a report that are not required to achieve a rollup result.
/// </summary>
/// <remarks>
/// If columns get removed here then the query optimiser will later remove various joins.
/// This can affect whether some rows are repeated.
/// Some aggregate types (e.g. max/min) are unaffected by this.
/// Some types (e.g. Count) are very affected by this, so we replace columns with simpler ones, rather than removing them completely.
/// Some types (e.g. Sum) in principle could be affected by this, but in practice are OK because they will reference the relationship branch that is relevant to them anyway.
/// </remarks>
/// <param name="query">The original query.</param>
/// <param name="clientAggregate">Aggregate settings that are used to determine what columns are used.</param>
/// <returns>A clone of the query, with unused columns removed.</returns>
public static StructuredQuery RemoveUnusedColumns(StructuredQuery query, ClientAggregate clientAggregate, bool supportQuickSearch = false)
{
StructuredQuery queryCopy = query.DeepCopy( );
// Determine columns that are used by the rollup
HashSet <Guid> referencedColumns = new HashSet <Guid>(clientAggregate.AggregatedColumns.Select(a => a.ReportColumnId)
.Concat(clientAggregate.GroupedColumns.Select(g => g.ReportColumnId)));
// Also include columns that are referenced by analyzer conditions
foreach (QueryCondition condition in query.Conditions)
{
ColumnReference colRefExpr = condition.Expression as ColumnReference;
if (colRefExpr == null)
{
continue;
}
referencedColumns.Add(colRefExpr.ColumnId);
}
// Ensure that the inner report returns at least something. (This will typically be the ID column).
if (referencedColumns.Count == 0 && query.SelectColumns.Count > 0)
{
referencedColumns.Add(query.SelectColumns [0].ColumnId);
}
// There are two types of optimisations. Either we can just pull out all unused columns, and let the structured query optimiser
// remove the subsequent relationship joins - which is OK for things like max & min in particular.
// But in some cases (e.g. for Count) we need to ensure we capture all relationships to get the true fanout.
// This is safer, but less efficient.
bool strictlyMaintainRowPresence = clientAggregate.AggregatedColumns.Any(ag => ag.AggregateMethod == AggregateMethod.Count);
if (!strictlyMaintainRowPresence)
{
// Remove all unused columns
queryCopy.SelectColumns.RemoveAll(column => !referencedColumns.Contains(column.ColumnId));
}
else
{
// Visit each column and determine if it can be removed, or converted to a simpler type
List <SelectColumn> columns = queryCopy.SelectColumns;
List <Guid> toRemove = new List <Guid>( );
for (int i = 0; i < columns.Count; i++)
{
SelectColumn column = columns [i];
if (referencedColumns.Contains(column.ColumnId))
{
continue;
}
// Replace field lookups with an equivalent ID column .. to maintain the relationship join, but remove the field join.
ResourceDataColumn fieldColumnExpr = column.Expression as ResourceDataColumn;
if (fieldColumnExpr != null)
{
// TODO: we could delete this column entire IF the entire relationship path to it is 'to one' relationships, without any advance properties to enforce rows.
// UPDATE: for quick serach purpose, the column expression should be ResourceDataColumn but skip in select clause.
// however for performance reasons, if without quick search, still change to IdExpression
if (supportQuickSearch)
{
column.IsHidden = true;
}
else
{
column.Expression = new IdExpression {
NodeId = fieldColumnExpr.NodeId
}
};
continue;
}
// Remove aggregate expressions if they don't have group-bys. (A group-by would cause the aggregate to return more than one row).
AggregateExpression aggExpr = column.Expression as AggregateExpression;
if (aggExpr != null)
{
AggregateEntity aggNode = StructuredQueryHelper.FindNode(queryCopy.RootEntity, aggExpr.NodeId) as AggregateEntity;
if (aggNode != null)
{
if (aggNode.GroupBy == null || aggNode.GroupBy.Count == 0)
{
toRemove.Add(column.ColumnId);
}
}
}
// Would be nice to remove calculated columns .. but probably too risky
}
queryCopy.SelectColumns.RemoveAll(column => toRemove.Contains(column.ColumnId));
}
// Remove any obsolete order-by instructions
queryCopy.OrderBy.RemoveAll(orderBy =>
{
ColumnReference colRefExpr = orderBy.Expression as ColumnReference;
if (colRefExpr == null)
{
return(false);
}
bool colStillPresent = queryCopy.SelectColumns.Any(column => column.ColumnId == colRefExpr.ColumnId);
return(!colStillPresent);
});
return(queryCopy);
}
}
public void Test_FindNodePath_NullNode()
{
List <Entity> findNodePath = StructuredQueryHelper.FindNodePath(Guid.Empty, null);
Assert.AreEqual(findNodePath.Count, 0);
}
private void AssertNodes(StructuredQuery query, params Entity [] nodes)
{
var actual = StructuredQueryHelper.WalkNodes(query.RootEntity);
Assert.That(actual, Is.EquivalentTo(nodes), "Expected nodes");
}
