/// <summary>
/// Query
/// </summary>
public String GetQueryLiteral(SqlStatement query)
{
query = query.Build();
StringBuilder retVal = new StringBuilder(query.SQL);
String sql = retVal.ToString();
var qList = query.Arguments?.ToArray() ?? new object[0];
int parmId = 0;
int lastIndex = 0;
while (sql.IndexOf("?", lastIndex) > -1)
{
var pIndex = sql.IndexOf("?", lastIndex);
retVal.Remove(pIndex, 1);
var obj = qList[parmId++];
if (obj is String || obj is Guid || obj is Guid? || obj is DateTime || obj is DateTimeOffset)
{
obj = $"'{obj}'";
}
else if (obj == null)
{
obj = "null";
}
retVal.Insert(pIndex, obj);
sql = retVal.ToString();
lastIndex = pIndex + obj.ToString().Length;
}
return(retVal.ToString());
}
/// <summary>
/// Append an AND condition
/// </summary>
public SqlStatement Or(SqlStatement clause)
{
if (String.IsNullOrEmpty(this.m_sql) && this.m_rhs == null)
{
return(this.Append(clause));
}
else
{
return(this.Append(new SqlStatement(this.m_provider, " OR ")).Append(clause.Build()));
}
}
/// <summary>
/// Append an AND condition
/// </summary>
public SqlStatement And(SqlStatement clause)
{
if (String.IsNullOrEmpty(this.m_sql) && (this.m_rhs == null || this.m_rhs.Build().SQL.TrimEnd().EndsWith("where", StringComparison.InvariantCultureIgnoreCase)))
{
return(this.Append(clause));
}
else
{
return(this.Append(" AND ").Append(clause.Build()));
}
}
/// <summary>
/// First or default returns only the first object or null if not found
/// </summary>
public TModel FirstOrDefault <TModel>(SqlStatement stmt)
{
#if DEBUG
var sw = new Stopwatch();
sw.Start();
try
{
#endif
lock (this.m_lockObject)
{
var dbc = this.m_lastCommand = this.m_provider.CreateCommand(this, stmt.Build().Limit(1));
try
{
this.IncrementProbe(Diagnostics.OrmPerformanceMetric.ActiveStatements);
if (this.CommandTimeout.HasValue)
{
dbc.CommandTimeout = this.CommandTimeout.Value;
}
using (var rdr = dbc.ExecuteReader())
return(this.ReaderToResult <TModel>(rdr));
}
catch (TimeoutException)
{
try { dbc.Cancel(); } catch { }
throw;
}
finally
{
#if DBPERF
this.PerformanceMonitor(stmt, sw);
#endif
dbc.Dispose();
this.DecrementProbe(Diagnostics.OrmPerformanceMetric.ActiveStatements);
}
}
#if DEBUG
}
finally
{
sw.Stop();
this.AddProbeResponseTime(sw.ElapsedMilliseconds);
this.m_tracer.TraceEvent(EventLevel.Verbose, "FIRST {0} executed in {1} ms", this.GetQueryLiteral(stmt), sw.ElapsedMilliseconds);
}
#endif
}
/// <summary>
/// Performance monitor
/// </summary>
private void PerformanceMonitor(SqlStatement stmt, Stopwatch sw)
{
sw.Stop();
if (sw.ElapsedMilliseconds > 5)
{
lock (s_lockObject)
{
using (var tw = File.AppendText("dbperf.xml"))
{
tw.WriteLine($"<sql><cmd>{this.GetQueryLiteral(stmt.Build())}</cmd><elapsed>{sw.ElapsedMilliseconds}</elapsed>");
tw.WriteLine($"<stack><[!CDATA[{new System.Diagnostics.StackTrace(true).ToString()}]]></stack><plan><![CDATA[");
stmt = this.CreateSqlStatement("EXPLAIN ").Append(stmt);
using (var dbc = this.m_provider.CreateCommand(this, stmt))
using (var rdr = dbc.ExecuteReader())
while (rdr.Read())
{
tw.WriteLine(rdr[0].ToString());
}
tw.WriteLine("]]></plan></sql>");
}
}
}
sw.Start();
}
/// <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);
}
}
}