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