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);
            }
        }
Esempio n. 2
0
        /// <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);
        }
Esempio n. 4
0
        /// <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);
        }
    }