/// <summary> /// Perform a domain query /// </summary> protected OrmResultSet <TResult> DomainQueryInternal <TResult>(DataContext context, SqlStatement domainQuery) { // Build and see if the query already exists on the stack??? domainQuery = domainQuery.Build(); var results = context.Query <TResult>(domainQuery); // Cache query result return(results); }
/// <summary> /// Create a command /// </summary> public IDbCommand CreateCommand(DataContext context, SqlStatement stmt) { var finStmt = stmt.Build(); #if DB_DEBUG if (System.Diagnostics.Debugger.IsAttached) { this.Explain(context, CommandType.Text, finStmt.SQL, finStmt.Arguments.ToArray()); } #endif return(this.CreateCommandInternal(context, CommandType.Text, finStmt.SQL, finStmt.Arguments.ToArray())); }
/// <summary> /// Perform a domain query /// </summary> protected IEnumerable <TResult> DomainQueryInternal <TResult>(DataContext context, SqlStatement domainQuery, ref int totalResults) { // Build and see if the query already exists on the stack??? domainQuery = domainQuery.Build(); var cachedQueryResults = context.CacheQuery(domainQuery); if (cachedQueryResults != null) { totalResults = cachedQueryResults.Count(); return(cachedQueryResults.OfType <TResult>()); } var results = context.Query <TResult>(domainQuery).ToList(); // Cache query result context.AddQuery(domainQuery, results.OfType <Object>()); return(results); }
/// <summary> /// Return exists /// </summary> public SqlStatement Exists(SqlStatement sqlStatement) { return(new SqlStatement(this, "SELECT CASE WHEN EXISTS (").Append(sqlStatement.Build()).Append(") THEN true ELSE false END")); }
/// <summary> /// Return exists /// </summary> public SqlStatement Count(SqlStatement sqlStatement) { return(new SqlStatement(this, "SELECT COUNT(*) FROM (").Append(sqlStatement.Build()).Append(") Q0")); }
/// <summary> /// Executes the query /// </summary> public BisResultContext ExecuteQuery(BiQueryDefinition queryDefinition, IDictionary <string, object> parameters, BiAggregationDefinition[] aggregation, int offset, int?count) { if (queryDefinition == null) { throw new ArgumentNullException(nameof(queryDefinition)); } // First we want to grab the connection strings used by this object var filledQuery = BiUtils.ResolveRefs(queryDefinition); // The ADO.NET provider only allows one connection to one db at a time, so verify the connections are appropriate if (queryDefinition.DataSources?.Count != 1) { throw new InvalidOperationException($"ADO.NET BI queries can only source data from 1 connection source, query {queryDefinition.Name} has {queryDefinition.DataSources?.Count}"); } // Ensure we have sufficient priviledge var demandList = queryDefinition.DataSources.SelectMany(o => o?.MetaData.Demands); if (queryDefinition.MetaData?.Demands != null) { demandList = demandList.Union(queryDefinition.MetaData?.Demands); } foreach (var pol in demandList) { ApplicationServiceContext.Current.GetService <IPolicyEnforcementService>().Demand(pol); } // Apply defaults where possible foreach (var defaultParm in queryDefinition.Parameters.Where(p => !String.IsNullOrEmpty(p.DefaultValue) && !parameters.ContainsKey(p.Name))) { parameters.Add(defaultParm.Name, defaultParm.DefaultValue); } // Next we validate parameters if (!queryDefinition.Parameters.Where(p => p.Required == true).All(p => parameters.ContainsKey(p.Name))) { throw new InvalidOperationException("Missing required parameter"); } // Validate parameter values foreach (var kv in parameters.ToArray()) { var parmDef = queryDefinition.Parameters.FirstOrDefault(p => p.Name == kv.Key); if (parmDef == null) { continue; // skip } else { switch (parmDef.Type) { case BiDataType.Boolean: if (string.IsNullOrEmpty(kv.Value?.ToString())) { parameters[kv.Key] = DBNull.Value; } else if (parmDef.Multiple && parameters[kv.Key] is IEnumerable <String> arr) { parameters[kv.Key] = arr.Select(o => Boolean.Parse(o)).ToArray(); } else { parameters[kv.Key] = Boolean.Parse(kv.Value.ToString()); } break; case BiDataType.Date: case BiDataType.DateTime: if (string.IsNullOrEmpty(kv.Value?.ToString())) { parameters[kv.Key] = DBNull.Value; } else if (parmDef.Multiple && parameters[kv.Key] is IEnumerable <String> arr) { parameters[kv.Key] = arr.Select(o => DateTime.Parse(o)).ToArray(); } else { parameters[kv.Key] = DateTime.Parse(kv.Value.ToString()); } break; case BiDataType.Integer: if (string.IsNullOrEmpty(kv.Value?.ToString())) { parameters[kv.Key] = DBNull.Value; } else if (parmDef.Multiple && parameters[kv.Key] is IEnumerable <String> arr) { parameters[kv.Key] = arr.Select(o => Int32.Parse(o)).ToArray(); } else { parameters[kv.Key] = Int32.Parse(kv.Value.ToString()); } break; case BiDataType.String: if (string.IsNullOrEmpty(kv.Value?.ToString())) { parameters[kv.Key] = DBNull.Value; } else if (parmDef.Multiple && parameters[kv.Key] is IEnumerable <String> arr) { parameters[kv.Key] = arr.ToArray(); } else { parameters[kv.Key] = kv.Value; } break; case BiDataType.Uuid: if (string.IsNullOrEmpty(kv.Value?.ToString())) { parameters[kv.Key] = DBNull.Value; } else if (parmDef.Multiple && parameters[kv.Key] is IEnumerable <String> arr) { parameters[kv.Key] = arr.Select(o => Guid.Parse(o)).ToArray(); } else { parameters[kv.Key] = Guid.Parse(kv.Value.ToString()); } break; default: throw new InvalidOperationException($"Cannot determine how to parse {parmDef.Type}"); } } } // We want to open the specified connection var provider = "sqlite"; var connectionString = ApplicationServiceContext.Current.GetService <IConfigurationManager>().GetSection <DataConfigurationSection>().ConnectionString.FirstOrDefault(o => o.Name == queryDefinition.DataSources.First().ConnectionString); // Query definition var rdbmsQueryDefinition = queryDefinition.QueryDefinitions.FirstOrDefault(o => o.Invariants.Contains(provider)); if (rdbmsQueryDefinition == null) { throw new InvalidOperationException($"Could not find a query definition for invariant {provider}"); } // Prepare the templated SQL var parmRegex = new Regex(@"\$\{([\w_][\-\d\w\._]*?)\}"); List <Object> values = new List <object>(); var stmt = parmRegex.Replace(rdbmsQueryDefinition.Sql, (m) => { object pValue = null; parameters.TryGetValue(m.Groups[1].Value, out pValue); if (pValue is Array arr) { values.AddRange(arr.OfType <Object>()); return(string.Join(",", arr.OfType <Object>().Select(o => "?"))); } else { values.Add(pValue); return("?"); } }); // Aggregation definitions if (aggregation?.Length > 0) { var agg = aggregation.FirstOrDefault(o => o.Invariants?.Contains(provider) == true) ?? aggregation.FirstOrDefault(o => o.Invariants?.Count == 0) ?? aggregation.FirstOrDefault(o => o.Invariants == null); // Aggregation found if (agg == null) { throw new InvalidOperationException($"No provided aggregation can be found for {provider}"); } var selector = agg.Columns?.Select(c => { switch (c.Aggregation) { case BiAggregateFunction.Average: return($"AVG({c.ColumnSelector}) AS {c.Name}"); case BiAggregateFunction.Count: return($"COUNT({c.ColumnSelector}) AS {c.Name}"); case BiAggregateFunction.CountDistinct: return($"COUNT(DISTINCT {c.ColumnSelector}) AS {c.Name}"); case BiAggregateFunction.First: return($"FIRST({c.ColumnSelector}) AS {c.Name}"); case BiAggregateFunction.Last: return($"LAST({c.ColumnSelector}) AS {c.Name}"); case BiAggregateFunction.Max: return($"MAX({c.ColumnSelector}) AS {c.Name}"); case BiAggregateFunction.Min: return($"MIN({c.ColumnSelector}) AS {c.Name}"); case BiAggregateFunction.Sum: return($"SUM({c.ColumnSelector}) AS {c.Name}"); case BiAggregateFunction.Value: return($"{c.ColumnSelector} AS {c.Name}"); default: throw new InvalidOperationException("Cannot apply aggregation function"); } }).ToArray() ?? new string[] { "*" }; String[] groupings = agg.Groupings.Select(g => g.ColumnSelector).ToArray(), colGroupings = agg.Groupings.Select(g => $"{g.ColumnSelector} AS {g.Name}").ToArray(); // Aggregate stmt = $"SELECT {String.Join(",", colGroupings.Union(selector))} " + $"FROM ({stmt}) AS _inner " + $"GROUP BY {String.Join(",", groupings)}"; } // Get a readonly connection var connParts = new SqliteConnectionStringBuilder(connectionString.Value); var file = connParts["dbfile"]; var enc = connParts["encrypt"]; using (SQLiteConnectionManager.Current.ExternLock(connectionString.Name)) using (var conn = new SqliteConnection($"Data Source=\"{file}\"")) { try { // Decrypt database var securityKey = ApplicationContext.Current.GetCurrentContextSecurityKey(); if (securityKey != null && (enc ?? "true").Equals("true")) { conn.SetPassword(securityKey); } // Open the database conn.Open(); // Attach any other connection sources foreach (var itm in queryDefinition.DataSources.Skip(1)) { using (var attcmd = conn.CreateCommand()) { var cstr = ApplicationContext.Current.ConfigurationManager.GetConnectionString(itm.ConnectionString); if (cstr.GetComponent("encrypt") == "true") { attcmd.CommandText = $"ATTACH DATABASE '{cstr.GetComponent("dbfile")}' AS {itm.Identifier} KEY ''"; } else { attcmd.CommandText = $"ATTACH DATABASE '{cstr.GetComponent("dbfile")}' AS {itm.Identifier} KEY X'{BitConverter.ToString(ApplicationContext.Current.GetCurrentContextSecurityKey()).Replace("-", "")}'"; } attcmd.CommandType = System.Data.CommandType.Text; attcmd.ExecuteNonQuery(); } } // Start time DateTime startTime = DateTime.Now; var sqlStmt = new SqlStatement(stmt, values.ToArray()).Limit(count ?? 10000).Offset(offset).Build(); this.m_tracer.TraceInfo("Executing BI Query: {0}", sqlStmt.Build().SQL); // Create command for execution using (var cmd = this.CreateCommand(conn, sqlStmt.SQL, sqlStmt.Arguments.ToArray())) { var results = new List <ExpandoObject>(); using (var rdr = cmd.ExecuteReader()) while (rdr.Read()) { results.Add(this.MapExpando(rdr)); } return(new BisResultContext( queryDefinition, parameters, this, results, startTime)); } } catch (Exception e) { this.m_tracer.TraceError("Error executing BIS data query: {0}", e); throw new DataPersistenceException($"Error executing BIS data query", e); } } }
/// <summary> /// Queries for the specified model. /// </summary> /// <param name="context">The context.</param> /// <param name="query">The query.</param> /// <param name="offset">The offset.</param> /// <param name="count">The count.</param> /// <param name="totalResults">The total results.</param> /// <param name="countResults">if set to <c>true</c> [count results].</param> /// <param name="overrideAuthContext">The principal to use instead of the default.</param> /// <returns>Returns a list of the specified model instance which match the given query expression.</returns> public override IEnumerable <TModel> QueryInternal(DataContext context, Expression <Func <TModel, bool> > query, int offset, int?count, out int totalResults, bool countResults) { try { // Domain query SqlStatement domainQuery = context.CreateSqlStatement <TDomain>().SelectFrom(); var expression = ModelMapper.MapModelExpression <TModel, TDomain, bool>(query, false); if (expression != null) { Type lastJoined = typeof(TDomain); if (typeof(CompositeResult).IsAssignableFrom(typeof(TQueryReturn))) { foreach (var p in typeof(TQueryReturn).GenericTypeArguments.Select(o => ReportingPersistenceService.ModelMapper.MapModelType(o))) { if (p != typeof(TDomain)) { // Find the FK to join domainQuery.InnerJoin(lastJoined, p); lastJoined = p; } } } domainQuery.Where(expression); } else { this.traceSource.TraceEvent(EventLevel.Verbose, "Will use slow query construction due to complex mapped fields"); domainQuery = ReportingPersistenceService.QueryBuilder.CreateQuery(query); } // Build and see if the query already exists on the stack??? domainQuery = domainQuery.Build(); if (Configuration.TraceSql) { traceSource.TraceEvent(EventLevel.Verbose, "Trace SQL flag is set to true, printing SQL statement"); traceSource.TraceEvent(EventLevel.Verbose, $"GENERATED SQL STATEMENT: {domainQuery.SQL}"); } if (offset > 0) { domainQuery.Offset(offset); } if (count.HasValue) { domainQuery.Limit(count.Value); } var results = context.Query <TQueryReturn>(domainQuery).OfType <object>(); totalResults = results.Count(); return(results.Select(r => ToModelInstance(r, context))); } catch (Exception e) { traceSource.TraceEvent(EventLevel.Error, $"Unable to query: {e}"); throw; } }
/// <summary> /// Perform the query /// </summary> protected virtual IEnumerable <Object> QueryInternal(DataContext context, Expression <Func <TModel, bool> > query, Guid queryId, int offset, int?count, out int totalResults, bool incudeCount = true) { #if DEBUG Stopwatch sw = new Stopwatch(); sw.Start(); #endif SqlStatement domainQuery = null; try { // Query has been registered? if (queryId != Guid.Empty && this.m_queryPersistence?.IsRegistered(queryId.ToString()) == true) { totalResults = (int)this.m_queryPersistence.QueryResultTotalQuantity(queryId.ToString()); var resultKeys = this.m_queryPersistence.GetQueryResults <Guid>(queryId.ToString(), offset, count.Value); return(resultKeys.Select(p => p.Id).OfType <Object>()); } // Is obsoletion time already specified? if (!query.ToString().Contains("ObsoletionTime") && typeof(BaseEntityData).IsAssignableFrom(typeof(TModel))) { var obsoletionReference = Expression.MakeBinary(ExpressionType.Equal, Expression.MakeMemberAccess(query.Parameters[0], typeof(TModel).GetProperty(nameof(BaseEntityData.ObsoletionTime))), Expression.Constant(null)); query = Expression.Lambda <Func <TModel, bool> >(Expression.MakeBinary(ExpressionType.AndAlso, obsoletionReference, query.Body), query.Parameters); } // Domain query domainQuery = context.CreateSqlStatement <TDomain>().SelectFrom(); var expression = m_mapper.MapModelExpression <TModel, TDomain>(query, false); if (expression != null) { Type lastJoined = typeof(TDomain); if (typeof(CompositeResult).IsAssignableFrom(typeof(TQueryReturn))) { foreach (var p in typeof(TQueryReturn).GenericTypeArguments.Select(o => AdoPersistenceService.GetMapper().MapModelType(o))) { if (p != typeof(TDomain)) { // Find the FK to join domainQuery.InnerJoin(lastJoined, p); lastJoined = p; } } } domainQuery.Where <TDomain>(expression); } else { m_tracer.TraceEvent(System.Diagnostics.TraceEventType.Verbose, 0, "Will use slow query construction due to complex mapped fields"); domainQuery = AdoPersistenceService.GetQueryBuilder().CreateQuery(query); } // Count = 0 means we're not actually fetching anything so just hit the db if (count != 0) { domainQuery = this.AppendOrderBy(domainQuery); // Query id just get the UUIDs in the db if (queryId != Guid.Empty && count != 0) { ColumnMapping pkColumn = null; if (typeof(CompositeResult).IsAssignableFrom(typeof(TQueryReturn))) { foreach (var p in typeof(TQueryReturn).GenericTypeArguments.Select(o => AdoPersistenceService.GetMapper().MapModelType(o))) { if (!typeof(DbSubTable).IsAssignableFrom(p) && !typeof(IDbVersionedData).IsAssignableFrom(p)) { pkColumn = TableMapping.Get(p).Columns.SingleOrDefault(o => o.IsPrimaryKey); break; } } } else { pkColumn = TableMapping.Get(typeof(TQueryReturn)).Columns.SingleOrDefault(o => o.IsPrimaryKey); } var keyQuery = AdoPersistenceService.GetQueryBuilder().CreateQuery(query, pkColumn).Build(); var resultKeys = context.Query <Guid>(keyQuery.Build()); //ApplicationContext.Current.GetService<IThreadPoolService>().QueueNonPooledWorkItem(a => this.m_queryPersistence?.RegisterQuerySet(queryId.ToString(), resultKeys.Select(o => new Identifier<Guid>(o)).ToArray(), query), null); // Another check this.m_queryPersistence?.RegisterQuerySet(queryId.ToString(), resultKeys.Count(), resultKeys.Select(o => new Identifier <Guid>(o)).Take(1000).ToArray(), query); ApplicationContext.Current.GetService <IThreadPoolService>().QueueNonPooledWorkItem(o => { int ofs = 1000; var rkeys = o as Guid[]; while (ofs < rkeys.Length) { this.m_queryPersistence?.AddResults(queryId.ToString(), rkeys.Skip(ofs).Take(1000).Select(k => new Identifier <Guid>(k)).ToArray()); ofs += 1000; } }, resultKeys.ToArray()); if (incudeCount) { totalResults = (int)resultKeys.Count(); } else { totalResults = 0; } var retVal = resultKeys.Skip(offset); if (count.HasValue) { retVal = retVal.Take(count.Value); } return(retVal.OfType <Object>()); } else if (incudeCount) { totalResults = context.Count(domainQuery); if (totalResults == 0) { return(new List <Object>()); } } else { totalResults = 0; } if (offset > 0) { domainQuery.Offset(offset); } if (count.HasValue) { domainQuery.Limit(count.Value); } return(this.DomainQueryInternal <TQueryReturn>(context, domainQuery, ref totalResults).OfType <Object>()); } else { totalResults = context.Count(domainQuery); return(new List <Object>()); } } catch (Exception ex) { if (domainQuery != null) { this.m_tracer.TraceEvent(TraceEventType.Error, ex.HResult, context.GetQueryLiteral(domainQuery.Build())); } context.Dispose(); // No longer important throw; } #if DEBUG finally { sw.Stop(); } #endif }
/// <summary> /// Create a command from the specified contxt with sql statement /// </summary> public IDbCommand CreateCommand(DataContext context, SqlStatement stmt) { var c = stmt.Build(); return(this.CreateCommandInternal(context, CommandType.Text, c.SQL, c.Arguments.ToArray())); }
/// <summary> /// Perform the query /// </summary> protected virtual IEnumerable <Object> QueryInternalEx(DataContext context, Expression <Func <TModel, bool> > query, Guid queryId, int offset, int?count, out int totalResults, bool incudeCount, ModelSort <TModel>[] orderBy) { #if DEBUG Stopwatch sw = new Stopwatch(); sw.Start(); #endif SqlStatement domainQuery = null; try { // Query has been registered? if (queryId != Guid.Empty && this.m_queryPersistence?.IsRegistered(queryId) == true) { totalResults = (int)this.m_queryPersistence.QueryResultTotalQuantity(queryId); var resultKeys = this.m_queryPersistence.GetQueryResults(queryId, offset, count.Value); return(resultKeys.OfType <Object>()); } // Is obsoletion time already specified? if (!query.ToString().Contains("ObsoletionTime") && typeof(BaseEntityData).IsAssignableFrom(typeof(TModel))) { var obsoletionReference = Expression.MakeBinary(ExpressionType.Equal, Expression.MakeMemberAccess(query.Parameters[0], typeof(TModel).GetProperty(nameof(BaseEntityData.ObsoletionTime))), Expression.Constant(null)); query = Expression.Lambda <Func <TModel, bool> >(Expression.MakeBinary(ExpressionType.AndAlso, obsoletionReference, query.Body), query.Parameters); } // Domain query domainQuery = context.CreateSqlStatement <TDomain>().SelectFrom(); var expression = m_mapper.MapModelExpression <TModel, TDomain, bool>(query, false); if (expression != null) { Type lastJoined = typeof(TDomain); if (typeof(CompositeResult).IsAssignableFrom(typeof(TQueryReturn))) { foreach (var p in typeof(TQueryReturn).GenericTypeArguments.Select(o => AdoAuditPersistenceService.GetMapper().MapModelType(o))) { if (p != typeof(TDomain)) { // Find the FK to join domainQuery.InnerJoin(lastJoined, p); lastJoined = p; } } } domainQuery.Where <TDomain>(expression); } else { m_tracer.TraceVerbose("Will use slow query construction due to complex mapped fields"); domainQuery = AdoAuditPersistenceService.GetQueryBuilder().CreateQuery(query, orderBy); } var retVal = this.DomainQueryInternal <TQueryReturn>(context, domainQuery); this.AppendOrderBy(retVal.Statement, orderBy); // Count = 0 means we're not actually fetching anything so just hit the db if (count != 0) { // Stateful query identifier = We need to add query results if (queryId != Guid.Empty && ApplicationServiceContext.Current.GetService <IQueryPersistenceService>() != null) { // Create on a separate thread the query results var keys = retVal.Keys <Guid>().ToArray(); totalResults = keys.Length; this.m_queryPersistence?.RegisterQuerySet(queryId, keys, query, totalResults); } else if (count.HasValue && !AdoAuditPersistenceService.GetConfiguration().UseFuzzyTotals) // Get an exact total { totalResults = retVal.Count(); } else { totalResults = 0; } // Fuzzy totals - This will only fetch COUNT + 1 as the total results if (count.HasValue) { if ((AdoAuditPersistenceService.GetConfiguration().UseFuzzyTotals) && totalResults == 0) { var fuzzResults = retVal.Skip(offset).Take(count.Value + 1).OfType <Object>().ToList(); totalResults = offset + fuzzResults.Count(); return(fuzzResults.Take(count.Value)); } else { return(retVal.Skip(offset).Take(count.Value).OfType <Object>()); } } else { return(retVal.Skip(offset).OfType <Object>()); } } else { totalResults = retVal.Count(); return(new List <Object>()); } } catch (Exception ex) { if (domainQuery != null) { this.m_tracer.TraceError(context.GetQueryLiteral(domainQuery.Build())); } context.Dispose(); // No longer important throw; } #if DEBUG finally { sw.Stop(); } #endif }