/// <summary>
/// Get provider
/// </summary>
private IDbProvider GetProvider(BiQueryDefinition queryDefinition)
{
var connectionString = this.m_configurationManager.GetConnectionString(queryDefinition.DataSources.First().ConnectionString);
var provider = this.m_configurationManager.GetSection <OrmConfigurationSection>().GetProvider(connectionString.Provider);
provider.ConnectionString = connectionString.Value;
provider.ReadonlyConnectionString = connectionString.Value;
return(provider);
}
/// <summary>
/// Get the SQL definition for the specified provider invariant
/// </summary>
/// <param name="queryDefinition">The query definition from which the SQL should be extracted</param>
/// <param name="provider">The provider for which the SQL should be retrieved</param>
/// <returns>The SQL definition</returns>
private BiSqlDefinition GetSqlDefinition(BiQueryDefinition queryDefinition, IDbProvider provider)
{
var rdbmsQueryDefinition = queryDefinition.QueryDefinitions.FirstOrDefault(o => o.Invariants.Contains(provider.Invariant));
if (rdbmsQueryDefinition == null)
{
throw new InvalidOperationException($"Could not find a SQL definition for invariant {provider.Invariant} from {queryDefinition?.Id} (supported invariants: {String.Join(",", queryDefinition.QueryDefinitions.SelectMany(o => o.Invariants))})");
}
return(rdbmsQueryDefinition);
}
/// <summary>
/// Perform a check on the ACL for the
/// </summary>
/// <param name="queryDefinition">The query definition to perform a demand on</param>
private void AclCheck(BiQueryDefinition queryDefinition)
{
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)
{
this.m_policyEnforcementService.Demand(pol);
}
}
/// <summary>
/// Creates a new result context
/// </summary>
public BisResultContext(BiQueryDefinition definition,
IDictionary <String, Object> arguments,
IBiDataSource source,
IEnumerable <dynamic> results,
DateTime startTime
)
{
this.Arguments = arguments;
this.Dataset = results;
this.DataSource = source;
this.QueryDefinition = definition;
this.StartTime = startTime;
this.StopTime = DateTime.Now;
}
/// <summary>
/// Refresh materialized view
/// </summary>
public void RefreshMaterializedView(BiQueryDefinition materializeDefinition)
{
if (materializeDefinition == null)
{
throw new ArgumentNullException(nameof(materializeDefinition));
}
materializeDefinition = BiUtils.ResolveRefs(materializeDefinition);
// The ADO.NET provider only allows one connection to one db at a time, so verify the connections are appropriate
if (materializeDefinition.DataSources?.Count != 1)
{
throw new InvalidOperationException($"ADO.NET BI queries can only source data from 1 connection source, query {materializeDefinition.Name} has {materializeDefinition.DataSources?.Count}");
}
// We want to open the specified connection
var provider = this.GetProvider(materializeDefinition);
// Query definition
var rdbmsQueryDefinition = this.GetSqlDefinition(materializeDefinition, provider);
if (rdbmsQueryDefinition.Materialize == null)
{
return; // no materialized view
}
else if (String.IsNullOrEmpty(rdbmsQueryDefinition.Materialize.Name))
{
throw new InvalidOperationException($"Materialization on {materializeDefinition.Id} must have a unique name");
}
// Get connection and execute
if (provider.Features.HasFlag(SqlEngineFeatures.MaterializedViews))
{
using (var context = provider.GetWriteConnection())
{
try
{
context.Open();
context.CommandTimeout = 360000;
context.ExecuteNonQuery(new SqlStatement(provider, provider.CreateSqlKeyword(SqlKeyword.RefreshMaterializedView))
.Append(rdbmsQueryDefinition.Materialize.Name));
}
catch (Exception e)
{
throw new DataPersistenceException($"Error refreshing materialized view for {materializeDefinition.Id}", e);
}
}
}
}
/// <summary>
/// Execute the specified query definition remotely
/// </summary>
public BisResultContext ExecuteQuery(BiQueryDefinition queryDefinition, IDictionary <string, object> parameters, BiAggregationDefinition[] aggregation, int offset, int?count)
{
try
{
var parmDict = parameters.ToDictionary(o => o.Key, o => o.Value);
if (!parmDict.ContainsKey("_count"))
{
parmDict.Add("_count", count);
}
if (!parmDict.ContainsKey("_offset"))
{
parmDict.Add("_offset", offset);
}
var startTime = DateTime.Now;
using (var client = this.GetRestClient())
{
var results = client.Get <IEnumerable <dynamic> >($"Query/{queryDefinition.Id}", parameters.ToArray());
return(new BisResultContext(queryDefinition, parameters, this, results, startTime));
}
}
catch (System.Net.WebException e)
{
var wr = e.Response as HttpWebResponse;
this.m_tracer.TraceWarning("Remote service indicated failure: {0}", e);
if (wr?.StatusCode == HttpStatusCode.NotFound)
{
throw new KeyNotFoundException($"Could not find definition with id {queryDefinition.Id}", e);
}
else
{
throw new Exception($"Error fetching BIS definition {queryDefinition.Id}", e);
}
}
catch (Exception e)
{
this.m_tracer.TraceError($"Error executing BIS query {queryDefinition.Name} - {e}");
throw new Exception($"Error executing BIS query {queryDefinition.Name}", e);
throw;
}
}
public void RefreshMaterializedView(BiQueryDefinition materializeDefinition)
{
throw new NotImplementedException();
}
/// <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>
/// 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));
}
queryDefinition = 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
this.AclCheck(queryDefinition);
// 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
{
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
{
parameters[kv.Key] = DateTime.Parse(kv.Value.ToString());
}
break;
case BiDataType.Integer:
if (string.IsNullOrEmpty(kv.Value?.ToString()))
{
parameters[kv.Key] = DBNull.Value;
}
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
{
parameters[kv.Key] = kv.Value.ToString();
}
break;
case BiDataType.Uuid:
if (string.IsNullOrEmpty(kv.Value?.ToString()))
{
parameters[kv.Key] = DBNull.Value;
}
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 = this.GetProvider(queryDefinition);
// Query definition
var rdbmsQueryDefinition = this.GetSqlDefinition(queryDefinition, provider);
// Prepare the templated SQL
List <Object> values = new List <object>();
var stmt = this.m_parmRegex.Replace(rdbmsQueryDefinition.Sql, (m) =>
{
object pValue = null;
parameters.TryGetValue(m.Groups[1].Value, out pValue);
values.Add(pValue);
return("?");
});
// Aggregation definitions
if (aggregation?.Length > 0)
{
var agg = aggregation.FirstOrDefault(o => o.Invariants?.Contains(provider.Invariant) == 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.Invariant}");
}
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.Concat(selector))} " +
$"FROM ({stmt}) {(provider.Features.HasFlag(SqlEngineFeatures.MustNameSubQuery) ? " AS _inner" : "")} " +
$"GROUP BY {String.Join(",", groupings)}";
}
// Get a readonly context
using (var context = provider.GetReadonlyConnection())
{
try
{
context.Open();
DateTime startTime = DateTime.Now;
var sqlStmt = new SqlStatement(provider, stmt, values.ToArray());
this.m_tracer.TraceInfo("Executing BI Query: {0}", context.GetQueryLiteral(sqlStmt.Build()));
var results = context.Query <ExpandoObject>(sqlStmt).Skip(offset).Take(count ?? 10000).ToArray();
return(new BisResultContext(
queryDefinition,
parameters,
this,
results,
startTime));
}
catch (Exception e)
{
this.m_tracer.TraceError("Error executing BIS data query {1} \r\n SQL: {2}\r\n Error: {0}", e, queryDefinition.Id, stmt);
throw new DataPersistenceException($"Error executing BIS data query", e);
}
}
}
/// <summary>
/// Refreshing materialized views remotely not supported
/// </summary>
public void RefreshMaterializedView(BiQueryDefinition materializeDefinition)
{
}
/// <summary>
/// Creating materialized views remotely not supported
/// </summary>
public void CreateMaterializedView(BiQueryDefinition materializeDefinition)
{
}
