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