/// <summary> /// Connects to the DB and loads the results of a single dynamic statement described in <paramref name="args"/>. /// </summary> /// <param name="connString">The connection string of the SQL database from which to load the data.</param> /// <param name="args">All the information needed to connect to the database and execute the statement.</param> /// <param name="cancellation">The cancellation instruction.</param> /// <returns>The requested data packaged in a <see cref="DynamicOutput"/>.</returns> public async Task <DynamicOutput> LoadDynamic(string connString, DynamicLoaderArguments args, CancellationToken cancellation = default) { // Destructure the args var countSql = args.CountSql; var principalStatement = args.PrincipalStatement; var dimAncestorsStatements = args.DimensionAncestorsStatements ?? new List <SqlDimensionAncestorsStatement>(); var ps = args.Parameters; var vars = args.Variables; ////////////// 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: variablesSql, countSql: countSql, statements.ToArray()); // The result DynamicOutput result = null; try { using var trx = TransactionFactory.ReadCommitted(); await ExponentialBackoff(async() => { var rows = new List <DynamicRow>(); var trees = new List <DimensionAncestorsOutput>(); var count = 0; // Connection using var conn = new SqlConnection(connString); // Command Text using var cmd = conn.CreateCommand(); cmd.CommandTimeout = TimeoutInSeconds; cmd.CommandText = sql; // Parameters foreach (var parameter in ps) { cmd.Parameters.Add(parameter); } // Execute try // To capture { await conn.OpenAsync(cancellation); using var reader = await cmd.ExecuteReaderAsync(cancellation); // (1) Load the count if any if (!string.IsNullOrWhiteSpace(countSql)) { if (await reader.ReadAsync(cancellation)) { count = reader.GetInt32(0); } // Go over to the next result set await reader.NextResultAsync(cancellation); } // (2) Load results of the principal query { int columnCount = principalStatement.ColumnCount; while (await reader.ReadAsync(cancellation)) { var row = new DynamicRow(columnCount); for (int index = 0; index < columnCount; index++) { var dbValue = reader.Value(index); row.Add(dbValue); } rows.Add(row); } } // (3) 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 DimensionAncestorsOutput ( idIndex: treeStatement.IdIndex, minIndex: minIndex, result: new List <DynamicRow>() ); await reader.NextResultAsync(cancellation); while (await reader.ReadAsync(cancellation)) { var row = new DynamicRow(columnCount); for (index = 0; index < targetIndices.Length; index++) { var dbValue = reader.Value(index); int targetIndex = targetIndices[index]; row.AddAt(dbValue, targetIndex); } treeResult.Result.Add(row); } trees.Add(treeResult); } } catch (SqlException ex) when(ex.Number is 8134) // Divide by zero { throw new QueryException(DivisionByZeroMessage); } finally { // Otherwise it will fail on retry cmd.Parameters.Clear(); } trx.Complete(); result = new DynamicOutput(rows, trees, count); }, cancellation); } catch (Exception ex) when(ex is not OperationCanceledException && ex is not ReportableException) { // Include the SQL and the parameters throw new StatementLoaderException(sql, ps, ex); } return(result); }
private async Task <(List <DynamicRow>, int count)> ToListAndCountInnerAsync(bool includeCount, int maxCount, QueryContext ctx2, CancellationToken cancellation) { var queryArgs = await _factory(cancellation); var connString = queryArgs.ConnectionString; var sources = queryArgs.Sources; var loader = queryArgs.Loader; var userId = ctx2.UserId; var userToday = ctx2.UserToday; // ------------------------ 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); // 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 string countSql = null; if (includeCount) { string countSelectSql = maxCount > 0 ? $"SELECT TOP {maxCount} [P].*" : "SELECT [P].*"; countSql = QueryTools.CombineSql( selectSql: countSelectSql, joinSql: joinSql, principalQuerySql: null, whereSql: whereSql, orderbySql: null, offsetFetchSql: null, groupbySql: null, havingSql: null, selectFromTempSql: null ); countSql = $@"SELECT COUNT(*) As [Count] FROM ( {countSql.IndentLines()} ) AS [Q]"; } // ------------------------ Execute SQL and return Result var principalStatement = new SqlDynamicStatement(sql, columnCount); var args = new DynamicLoaderArguments { CountSql = countSql, // No counting in aggregate functions PrincipalStatement = principalStatement, DimensionAncestorsStatements = null, // No ancestors Variables = vars, Parameters = ps, }; var result = await loader.LoadDynamic(connString, args, cancellation); return(result.Rows, result.Count); }
public async Task <DynamicOutput> ToListAsync(QueryContext ctx, CancellationToken cancellation) { var queryArgs = await _factory(cancellation); var sources = queryArgs.Sources; var connString = queryArgs.ConnectionString; var loader = queryArgs.Loader; var userId = ctx.UserId; var userToday = ctx.UserToday; // ------------------------ 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 <SqlDimensionAncestorsStatement>(); { // 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); // Prepare the Context var complicationCtx = new QxCompilationContext(joinTrie, sources, vars, ps, today, now, userId); // Prepare the SQL components var selectSql = PrepareAncestorSelectSql(complicationCtx, 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 SqlDimensionAncestorsStatement(idIndex, sql, targetIndices); ancestorsStatements.Add(statement); } } } // ------------------------ The SQL Generation Step // (1) Prepare the JOIN's clause var principalJoinTrie = PreparePrincipalJoin(); var principalJoinSql = principalJoinTrie.GetSql(sources); // 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 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 Output var principalStatement = new SqlDynamicStatement(principalSql, principalColumnCount); var args = new DynamicLoaderArguments { CountSql = null, // No counting in aggregate functions PrincipalStatement = principalStatement, DimensionAncestorsStatements = ancestorsStatements, Variables = vars, Parameters = ps, }; var output = await loader.LoadDynamic(connString, args, cancellation); return(output); }