Ejemplo n.º 1
0
        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);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// This methods loads the results of an <see cref="AggregateQuery{T}"/> into a list of <see cref="DynamicRow"/>
        /// </summary>
        /// <param name="principalStatement">The principal <see cref="SqlStatement"/> to load first.</param>
        /// <param name="preparatorySql">Any SQL to be included at the very beginning the main script (cannot contain a SELECT or return a result set</param>
        /// <param name="ps">The parameters needed by SQL</param>
        /// <param name="conn">The SQL Server connection through which to execute the SQL script</param>
        /// <returns>The list of hydrated <see cref="DynamicRow"/>s</returns>
        public static async Task <(List <DynamicRow> result, IEnumerable <TreeDimensionResult> treeDimensions, int count)> LoadDynamicStatement(
            SqlDynamicStatement principalStatement,
            IEnumerable <DimensionAncestorsStatement> dimAncestorsStatements,
            bool includeCount,
            SqlStatementVariables vars,
            SqlStatementParameters ps,
            SqlConnection conn,
            ILogger logger,
            CancellationToken cancellation)
        {
            dimAncestorsStatements ??= new List <DimensionAncestorsStatement>();
            var result      = new List <DynamicRow>();
            var treeResults = new List <TreeDimensionResult>();
            int count       = 0;

            ////////////// Prepare the complete SQL code
            // Add any variables in the preparatory SQL
            string variablesSql = vars.ToSql();

            var statements = new List <string>(1 + dimAncestorsStatements.Count())
            {
                principalStatement.Sql
            };

            statements.AddRange(dimAncestorsStatements.Select(e => e.Sql));

            string sql = PrepareSql(variablesSql, null, null, statements.ToArray());

            using (var cmd = conn.CreateCommand())
            {
                // Command Text
                cmd.CommandText = sql;

                // Command Parameters
                foreach (var parameter in ps)
                {
                    cmd.Parameters.Add(parameter);
                }

                // It will always be open, but we add this nonetheless for robustness
                bool ownsConnection = conn.State != System.Data.ConnectionState.Open;
                if (ownsConnection)
                {
                    conn.Open();
                }

                // Results are loaded
                try
                {
                    // Load results of the principal query
                    using var reader = await cmd.ExecuteReaderAsync(cancellation);

                    {
                        int columnCount = principalStatement.ColumnCount;
                        while (await reader.ReadAsync(cancellation))
                        {
                            var row = new DynamicRow(columnCount);
                            for (int index = 0; index < columnCount; index++)
                            {
                                var dbValue = reader[index];
                                if (dbValue == DBNull.Value)
                                {
                                    dbValue = null;
                                }

                                row.Add(dbValue);
                            }

                            result.Add(row);
                        }
                    }

                    // Load the tree dimensions
                    foreach (var treeStatement in dimAncestorsStatements)
                    {
                        int columnCount = treeStatement.TargetIndices.Count();

                        int   index;
                        int   minIndex      = treeStatement.TargetIndices.Min();
                        int[] targetIndices = treeStatement.TargetIndices.Select(i => i - minIndex).ToArray();

                        var treeResult = new TreeDimensionResult(treeStatement.IdIndex, minIndex);

                        await reader.NextResultAsync(cancellation);

                        while (await reader.ReadAsync(cancellation))
                        {
                            var row = new DynamicRow(columnCount);
                            for (index = 0; index < targetIndices.Length; index++)
                            {
                                int targetIndex = targetIndices[index];

                                var dbValue = reader[index];
                                if (dbValue == DBNull.Value)
                                {
                                    dbValue = null;
                                }

                                row.AddAt(dbValue, targetIndex);
                            }

                            treeResult.Result.Add(row);
                        }

                        treeResults.Add(treeResult);
                    }

                    // Load the count if any
                    if (includeCount)
                    {
                        await reader.NextResultAsync(cancellation);

                        if (await reader.ReadAsync(cancellation))
                        {
                            count = reader.GetInt32(0);
                        }
                    }
                }
                catch (SqlException ex) when(ex.Number is 8134)  // Divide by zero
                {
                    throw new QueryException(DIVISION_BY_ZERO_MESSAGE);
                }
                catch (SqlException ex)
                {
                    logger.LogError(ex, FullSqlForLogger(sql, ps)); // Log the generated SQL code for debugging
                    throw;
                }
                finally
                {
                    // Otherwise we might get an error when a parameter is reused
                    cmd.Parameters.Clear();

                    // The connection is never owned, but we add this code anyways for robustness
                    if (ownsConnection)
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }

            return(result, treeResults, count);
        }
Ejemplo n.º 3
0
        private async Task <(List <DynamicRow>, int count)> ToListAndCountInnerAsync(bool includeCount, int maxCount, 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)
            {
                var aggregation = exp.Aggregations().FirstOrDefault();
                if (aggregation != null)
                {
                    throw new QueryException($"Select cannot contain an aggregation function like: {aggregation.Name}");
                }
            }

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

            // ------------------------ 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;

            // ------------------------ The SQL Generation Step

            // (1) Prepare the JOIN's clause
            var joinTrie = PrepareJoin();
            var joinSql  = joinTrie.GetSql(sources, fromSql: null);

            // Compilation context
            var ctx = new QxCompilationContext(joinTrie, sources, vars, ps, today, now, userId);

            // (2) Prepare all the SQL clauses
            var(selectSql, columnCount) = PrepareSelectSql(ctx);
            string whereSql       = PrepareWhereSql(ctx);
            string orderbySql     = PrepareOrderBySql(ctx);
            string offsetFetchSql = PrepareOffsetFetch();

            // (3) Put together the final SQL statement and return it
            string sql = QueryTools.CombineSql(
                selectSql: selectSql,
                joinSql: joinSql,
                principalQuerySql: null,
                whereSql: whereSql,
                orderbySql: orderbySql,
                offsetFetchSql: offsetFetchSql,
                groupbySql: null,
                havingSql: null,
                selectFromTempSql: null
                );

            // ------------------------ Prepare the Count SQL
            if (includeCount)
            {
                string countSelectSql = maxCount > 0 ? $"SELECT TOP {maxCount} [P].*" : "SELECT [P].*";

                string countSql = QueryTools.CombineSql(
                    selectSql: countSelectSql,
                    joinSql: joinSql,
                    principalQuerySql: null,
                    whereSql: whereSql,
                    orderbySql: null,
                    offsetFetchSql: null,
                    groupbySql: null,
                    havingSql: null,
                    selectFromTempSql: null
                    );

                sql = $@"
{sql}

SELECT COUNT(*) As [Count] FROM (
{QueryTools.IndentLines(countSql)}
) AS [Q]";
            }

            // ------------------------ Execute SQL and return Result

            var principalStatement = new SqlDynamicStatement(sql, columnCount);

            var(result, _, count) = await EntityLoader.LoadDynamicStatement(
                principalStatement : principalStatement,
                dimAncestorsStatements : null,
                includeCount,
                vars : vars,
                ps : ps,
                conn : conn,
                logger : logger,
                cancellation : cancellation);

            return(result, count);
        }