private AdoQueryPayload GenerateRankingQuery(SearchRequest searchRequest)
{
var queryPayload = new AdoQueryPayload();
BuildRankingQuery(queryPayload, searchRequest);
return queryPayload;
}
private void BuildRankingQuery(AdoQueryPayload queryPayload, SearchRequest searchRequest)
{
queryPayload.AppendLine("SELECT TOP " + searchRequest.Limit);
queryPayload.AppendLine(@" ED.FingerprintText
,ED.ApplicationId
,ED.OriginExceptionType
,ED.OriginStackFrame
,ED.LowestAppStackFrame
,ED.HighestAppStackFrame
,SUM(T.Frequency) AS TotalErrors
FROM Timeline T WITH(NOLOCK)
JOIN ErrorDefinition AS ED WITH(NOLOCK) ON T.Fingerprint = ED.Fingerprint");
// if we have one or more Definition Groups then we'll need to add date and server filters
// which are not included in Definition Group sub queries
if (!searchRequest.Groups.Any() || ExistDefinitionOnlySearchGroups(searchRequest.Groups))
{
AddServerJoin(queryPayload, searchRequest.Servers);
queryPayload.Append("WHERE ");
AddDateRangeClause(queryPayload, searchRequest.DateRange, "T");
AddServersClause(queryPayload, searchRequest.Servers, false);
}
else
{
queryPayload.AppendLine("WHERE 1=1");
}
AddMatchConditionGroups(queryPayload, searchRequest.Groups, searchRequest.DateRange, searchRequest.Servers);
queryPayload.AppendLine(@"GROUP BY ED.FingerprintText
,ED.ApplicationId
,ED.OriginExceptionType
,ED.OriginStackFrame
,ED.LowestAppStackFrame
,ED.HighestAppStackFrame");
if(searchRequest.MinFrequency.HasValue)
queryPayload.AppendLine("HAVING SUM(T.Frequency) >= " + searchRequest.MinFrequency.Value);
queryPayload.Append(@"ORDER BY TotalErrors DESC
,ED.ApplicationId
,ED.HighestAppStackFrame
OPTION(MAXDOP 1)");
}
private void AddMatchConditionGroups(AdoQueryPayload queryPayload, List<MatchConditionGroup> matchConditionGroups, DateRange dateRange, List<string> servers)
{
for (int i = 0; i < matchConditionGroups.Count; i++)
{
if(i == 0)
AddLogicalOperator(queryPayload, LqlLogicalOperator.And);
else
AddLogicalOperator(queryPayload, matchConditionGroups[i-1].LogicalOperatorToNextGroup);
AddMatchConditionGroup(queryPayload, matchConditionGroups[i], dateRange, servers);
}
}
private void AddFullTextSearchClause(MatchCondition matchCondition, AdoQueryPayload queryPayload)
{
switch (matchCondition.Operator)
{
case LqlOperator.Like:
queryPayload.Append("CONTAINS(");
break;
case LqlOperator.NotLike:
queryPayload.Append("NOT CONTAINS(");
break;
default:
throw new SqlGenerationException("LQL Operator not supported for Full Text Search: " + matchCondition.Operator);
}
queryPayload.Append(GetColumnName(matchCondition.Object));
queryPayload.Append(", ");
queryPayload.AddParameter(matchCondition, true);
queryPayload.AppendLine(")");
}
private void AddClause(MatchCondition matchCondition, AdoQueryPayload queryPayload)
{
queryPayload.Append(GetColumnName(matchCondition.Object));
switch (matchCondition.Operator)
{
case LqlOperator.Equals:
queryPayload.Append(" = ");
queryPayload.AddParameter(matchCondition, false);
break;
case LqlOperator.NotEquals:
queryPayload.Append(" <> ");
queryPayload.AddParameter(matchCondition, false);
break;
case LqlOperator.Like:
queryPayload.Append(" LIKE '%' + ");
queryPayload.AddParameter(matchCondition, false);
queryPayload.Append(" + '%'");
break;
case LqlOperator.NotLike:
queryPayload.Append(" NOT LIKE '%' + ");
queryPayload.AddParameter(matchCondition, false);
queryPayload.Append(" + '%'");
break;
case LqlOperator.In:
queryPayload.Append(" IN (");
queryPayload.AddParameter(matchCondition, false);
queryPayload.Append(")");
break;
case LqlOperator.NotIn:
queryPayload.Append(" NOT IN (");
queryPayload.AddParameter(matchCondition, false);
queryPayload.Append(")");
break;
default:
throw new SqlGenerationException("LQL Operator not supported for SQL query generation: " + matchCondition.Operator);
}
queryPayload.AddNewLine();
}
private void AddLogicalOperator(AdoQueryPayload queryPayload, LqlLogicalOperator logicalOperator)
{
if (logicalOperator == LqlLogicalOperator.And)
queryPayload.Append("AND");
else if (logicalOperator == LqlLogicalOperator.Or)
queryPayload.Append("OR");
}
private void AddSingleClause(MatchCondition matchCondition, AdoQueryPayload queryPayload)
{
if (IsFullTextSearchCompatible(matchCondition.Object, matchCondition.Operator))
AddFullTextSearchClause(matchCondition, queryPayload);
else
AddClause(matchCondition, queryPayload);
}
private void AddDateRangeClause(AdoQueryPayload queryPayload, DateRange dateRange, string tableAlias)
{
queryPayload.Append(tableAlias + ".OccurredAt BETWEEN ");
queryPayload.AddFromDateParameter(dateRange.From);
queryPayload.Append(" AND ");
queryPayload.AddToDateParameter(dateRange.To);
queryPayload.AddNewLine();
}
private void AddServersClause(AdoQueryPayload queryPayload, List<string> servers, bool indented)
{
if (servers.Any())
{
if(indented)
queryPayload.Append(" ");
queryPayload.Append("AND ");
queryPayload.Append("S.ServerName IN (");
queryPayload.AddServerParameters(servers);
queryPayload.AppendLine(")");
}
}
private void AddTimelineIdFilterSelectAndJoins(AdoQueryPayload queryPayload, MatchConditionGroup matchConditionGroup, DateRange dateRange, List<string> servers)
{
bool hasApplication = matchConditionGroup.MatchConditions.Any(x => x.Object == LqlObject.Application);
bool hasExceptionType = matchConditionGroup.MatchConditions.Any(x => x.Object == LqlObject.ExceptionType);
bool hasFingerprint = matchConditionGroup.MatchConditions.Any(x => x.Object == LqlObject.Fingerprint);
bool hasStackFrame = matchConditionGroup.MatchConditions.Any(x => x.Object == LqlObject.StackFrame);
queryPayload.AppendLine(" SELECT DISTINCT T.TimelineId FROM Timeline T WITH(NOLOCK)");
if (hasApplication || hasFingerprint)
queryPayload.AppendLine(" JOIN ErrorDefinition ED WITH(NOLOCK) ON T.Fingerprint = ED.Fingerprint");
if (hasExceptionType || hasStackFrame)
queryPayload.AppendLine(" JOIN ErrorBreakdown AS EB WITH(NOLOCK) ON T.Fingerprint = EB.Fingerprint");
if (servers.Any())
{
queryPayload.Append(" ");
AddServerJoin(queryPayload, servers);
}
queryPayload.Append(" WHERE ");
AddDateRangeClause(queryPayload, dateRange, "T");
AddServersClause(queryPayload, servers, true);
}
private void AddTimelineIdFilterWhereClauses(AdoQueryPayload queryPayload, MatchConditionGroup matchConditionGroup)
{
int clauseCounter = 1;
foreach (var matchCondition in matchConditionGroup.MatchConditions)
{
if (clauseCounter == 1)
queryPayload.Append(" AND ");
else
queryPayload.Append(" " + matchConditionGroup.LogicalOperator.ToString().ToUpper() + " ");
AddSingleClause(matchCondition, queryPayload);
clauseCounter++;
}
}
private void AddUnionFilters(AdoQueryPayload queryPayload, MatchCondition matchCondition, DateRange dateRange, List<string> servers)
{
AddServerJoin(queryPayload, servers);
if (matchCondition.Object == LqlObject.Application || matchCondition.Object == LqlObject.Fingerprint)
{
queryPayload.AppendLine("JOIN ErrorDefinition ED WITH (NOLOCK) ON T.Fingerprint = ED.Fingerprint");
queryPayload.Append("WHERE ");
}
else if (matchCondition.Object == LqlObject.ExceptionType || matchCondition.Object == LqlObject.StackFrame)
{
queryPayload.AppendLine("JOIN ErrorBreakdown EB WITH (NOLOCK) ON EB.Fingerprint = T.Fingerprint");
queryPayload.Append("WHERE ");
}
else if (matchCondition.Object == LqlObject.Message)
{
queryPayload.Append("WHERE ");
AddDateRangeClause(queryPayload, dateRange, "T");
queryPayload.AddNewLine();
queryPayload.Append("AND ");
}
else
throw new SqlGenerationException("Non supported object type: " + matchCondition.Object);
AddServersClause(queryPayload, servers, true);
queryPayload.AddNewLine();
queryPayload.Append("AND ");
AddSingleClause(matchCondition, queryPayload);
}
private void AddFilterUnionSelects(AdoQueryPayload queryPayload, MatchConditionGroup matchConditionGroup, DateRange dateRange, List<string> servers)
{
int counter = 0;
foreach (var matchCondition in matchConditionGroup.MatchConditions)
{
if (counter > 0)
queryPayload.AppendLine(" UNION ALL ");
queryPayload.AppendLine("SELECT T.TimelineId FROM Timeline T WITH(NOLOCK)");
AddUnionFilters(queryPayload, matchCondition, dateRange, servers);
counter++;
}
}
private void AddServerJoin(AdoQueryPayload queryPayload, List<string> servers)
{
if (servers.Any())
queryPayload.AppendLine("JOIN Server AS S WITH(NOLOCK) ON T.ServerId = S.ServerId");
}
private void AddDefinitionGroup(AdoQueryPayload queryPayload, MatchConditionGroup matchConditionGroup)
{
queryPayload.AppendLine(" SELECT DISTINCT ED.Fingerprint FROM ErrorDefinition ED WITH(NOLOCK)");
if (matchConditionGroup.MatchConditions.Any(x => x.Object == LqlObject.ExceptionType || x.Object == LqlObject.StackFrame))
queryPayload.AppendLine(" JOIN ErrorBreakdown AS EB WITH(NOLOCK) ON ED.Fingerprint = EB.Fingerprint");
queryPayload.Append(" WHERE ");
int conditionCounter = 1;
foreach (var condition in matchConditionGroup.MatchConditions)
{
if (conditionCounter > 1)
queryPayload.Append(" " + matchConditionGroup.LogicalOperator.ToString().ToUpper() + " ");
AddSingleClause(condition, queryPayload);
conditionCounter++;
}
}
private void AddMatchConditionGroup(AdoQueryPayload queryPayload, MatchConditionGroup matchConditionGroup, DateRange dateRange, List<string> servers)
{
if (matchConditionGroup.Exclude)
queryPayload.Append(" NOT");
if (IsDefinitionGroup(matchConditionGroup))
queryPayload.AppendLine(" T.Fingerprint IN (");
else
queryPayload.AppendLine(" T.TimelineId IN (");
if (IsDefinitionGroup(matchConditionGroup))
{
AddDefinitionGroup(queryPayload, matchConditionGroup);
}
else
{
if (UseUnionsToAvoidFtsPerfIssue(matchConditionGroup))
{
AddFilterUnionSelects(queryPayload, matchConditionGroup, dateRange, servers);
}
else
{
AddTimelineIdFilterSelectAndJoins(queryPayload, matchConditionGroup, dateRange, servers);
AddTimelineIdFilterWhereClauses(queryPayload, matchConditionGroup);
}
}
queryPayload.AppendLine(")");
}