internal static void Test_Scenario(StructuredQuery structuredQuery, Action invalidationCallback, bool expectInvalidation)
{
CachingQuerySqlBuilder cachingQuerySqlBuilder;
Mock <IQuerySqlBuilder> mockQuerySqlBuilder;
IQuerySqlBuilder querySqlBuilder;
QuerySettings settings;
QueryBuild queryBuild;
IUserRuleSetProvider userRuleSetProvider = MockUserRuleSetProvider( );
settings = new QuerySettings( );
queryBuild = new QueryBuild( );
var cacheInvalidators = new CacheInvalidatorFactory( ).CacheInvalidatorsList_TestOnly;
mockQuerySqlBuilder = new Mock <IQuerySqlBuilder>(MockBehavior.Strict);
mockQuerySqlBuilder.Setup(x => x.BuildSql(It.IsAny <StructuredQuery>( ), settings))
.Returns <StructuredQuery, QuerySettings>((sq, qs) =>
{
QuerySqlBuilder.IdentifyCacheDependencies(sq, settings);
return(queryBuild);
})
.Verifiable( );
querySqlBuilder = mockQuerySqlBuilder.Object;
cachingQuerySqlBuilder = new CachingQuerySqlBuilder(querySqlBuilder, userRuleSetProvider);
try
{
// Add current cache invalidator to global factory
cacheInvalidators.Add(cachingQuerySqlBuilder.CacheInvalidator);
using (var scope = Factory.Current.BeginLifetimeScope(cb =>
{
cb.Register(c => cachingQuerySqlBuilder).As <ICacheService>( );
}))
using (Factory.SetCurrentScope(scope))
{
// Run first time
cachingQuerySqlBuilder.BuildSql(structuredQuery.DeepCopy( ), settings);
// Perform potential invalidation task
using (new SecurityBypassContext( ))
{
invalidationCallback( );
}
// Run second time
cachingQuerySqlBuilder.BuildSql(structuredQuery.DeepCopy( ), settings);
}
int times = expectInvalidation ? 2 : 1;
mockQuerySqlBuilder.Verify(x => x.BuildSql(It.IsAny <StructuredQuery>( ), settings), Times.Exactly(times));
mockQuerySqlBuilder.VerifyAll( );
}
finally
{
// Restore cache invalidators
cacheInvalidators.Remove(cachingQuerySqlBuilder.CacheInvalidator);
}
}
/// <summary>
/// Returns a suitable key for comparing query runs.
/// </summary>
/// <returns>A key, or null if the query should not be cached.</returns>
private CachingQueryRunnerKey CreateCacheKeyAndQuery(StructuredQuery query, QuerySettings settings, out QueryBuild builtQuery)
{
// Build the SQL for the query
// Note: unfortunately this may mutate the query, so keep a copy
StructuredQuery queryCopy = query.DeepCopy( );
builtQuery = BuildQueryImpl(queryCopy, settings);
// Check if query can even participate in cache
bool doNotCacheResult = builtQuery.SqlIsUncacheable ||
builtQuery.DataIsUncacheable ||
!CachingQueryRunnerKey.DoesRequestAllowForCaching(query, settings);
if (doNotCacheResult)
{
return(null);
}
// If we get to this point, then the cache is definitely participating
// Determine the cache key - either shared across users, or specific to the current user.
CachingQueryRunnerKey key = CreateCacheKeyImpl(query, settings, builtQuery.DataReliesOnCurrentUser);
return(key);
}
private PreparedQuery PrepareReportRollupRun(Model.Report report, StructuredQuery structuredQuery, ReportSettings reportSettings, QuerySettings nonRollupQuerySettings)
{
StructuredQuery rollupQuery = null;
ClientAggregate clientAggregate = null;
StructuredQuery optimisedQuery;
QuerySettings rollupSettings;
bool adhocRollup;
bool reportRollup;
adhocRollup = reportSettings.ReportParameters != null && reportSettings.ReportParameters.GroupAggregateRules != null;
reportRollup = !adhocRollup && report.ReportColumns.Any(rc => rc.ColumnRollup.Count > 0 || rc.ColumnGrouping.Count > 0);
if (adhocRollup)
{
clientAggregate = ApplyAdhocAggregates(reportSettings.ReportParameters.GroupAggregateRules, structuredQuery);
}
else if (reportRollup)
{
clientAggregate = new ClientAggregate(report, structuredQuery);
clientAggregate.IncludeRollup = true;
}
else if (report.RollupGrandTotals != null || report.RollupSubTotals != null || report.RollupOptionLabels != null)
{
return(new PreparedQuery
{
ClientAggregate = new ClientAggregate( )
});
}
else
{
return(new PreparedQuery( ));
}
// Clone the query, so that runs and rollups won't intefere with each others caches if they mutate the structure
// In particular, calculated columns get evaluated during execution and mutate the query .. but only if the result doesn't come from cache, but this interferes with the rollup cache key.
// Ideally, both calculations and optimisations would be provided in layers, and both applied, and cached, before either normal or rollup executions are run.
rollupQuery = structuredQuery.DeepCopy( );
// A poor proxy for determining that this is not a pivot chart.
bool isGroupedReport = !(reportSettings.ReportParameters != null &&
reportSettings.ReportParameters.GroupAggregateRules != null &&
reportSettings.ReportParameters.GroupAggregateRules.IgnoreRows);
if (isGroupedReport)
{
ReportRollupHelper.EnsureShowTotalsHasCount(rollupQuery, clientAggregate);
}
// Remove unused columns
bool supportQuickSearch = !string.IsNullOrWhiteSpace(reportSettings.QuickSearch);
optimisedQuery = ReportRollupHelper.RemoveUnusedColumns(rollupQuery, clientAggregate, supportQuickSearch);
rollupSettings = new QuerySettings
{
SecureQuery = nonRollupQuerySettings.SecureQuery,
SupportClientAggregate = true,
SupportPaging = false,
QuickSearchTerm = reportSettings.QuickSearch,
SupportQuickSearch = supportQuickSearch, // rollups query support quick search.
ClientAggregate = clientAggregate,
AdditionalOrderColumns = BuildAdditionOrderColumnDictionary(optimisedQuery, clientAggregate),
FullAggregateClustering = true,
Hint = "RptRollup-" + report.Id,
TargetResource = nonRollupQuerySettings.TargetResource,
IncludeResources = nonRollupQuerySettings.IncludeResources,
ExcludeResources = nonRollupQuerySettings.ExcludeResources
};
// Note : do not apply quick search filter to rollups (for scalability reasons)
PreparedQuery preparedQuery = new PreparedQuery
{
ClientAggregate = clientAggregate,
StructuredQuery = optimisedQuery,
QuerySettings = rollupSettings
};
return(preparedQuery);
}
/// <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);
}
}