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