/// <summary>
/// Performs analysis and returns a list of problems detected
/// </summary>
/// <param name="ruleExecutionContext">Contains the schema model and model element to analyze</param>
/// <returns>
/// The problems detected by the rule in the given element
/// </returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var sqlObjName = ruleExecutionContext.GetObjectName(sqlObj);
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var whereClauseVisitor = new WhereClauseVisitor();
fragment.Accept(whereClauseVisitor);
foreach (var whereClause in whereClauseVisitor.Statements)
{
var inPredicateVisitor = new InPredicateVisitor();
whereClause.Accept(inPredicateVisitor);
var offenders = inPredicateVisitor.NotIgnoredStatements(RuleId).Where(i => i.Subquery != null);
problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t)));
}
return(problems);
}
/// <summary>
/// Performs analysis and returns a list of problems detected
/// </summary>
/// <param name="ruleExecutionContext">Contains the schema model and model element to analyze</param>
/// <returns>
/// The problems detected by the rule in the given element
/// </returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var stmt in selectStatementVisitor.Statements)
{
var querySpecificationVisitor = new QuerySpecificationVisitor();
stmt.QueryExpression.Accept(querySpecificationVisitor);
foreach (var query in querySpecificationVisitor.Statements)
{
var inPredicateVisitor = new InPredicateVisitor();
query.Accept(inPredicateVisitor);
var inClauses = inPredicateVisitor.NotIgnoredStatements(RuleId)
.Where(i => !i.NotDefined && i.Expression is ColumnReferenceExpression);
if (inClauses.Count() == 0)
{
continue;
}
foreach (var inClause in inClauses)
{
var indexColumnExists = false;
var column = inClause.Expression as ColumnReferenceExpression;
var table = GetTableFromColumn(sqlObj, query, column);
//most likely the base is a view.... /sigh
if (table == null)
{
continue;
}
var indexes = table.GetChildren(DacQueryScopes.All).Where(x => x.ObjectType == ModelSchema.Index);
foreach (var index in indexes)
{
indexColumnExists = index.GetReferenced(DacQueryScopes.All)
.Any(x =>
x.ObjectType == ModelSchema.Column &&
_comparer.Equals(x.Name.Parts.Last(), column.MultiPartIdentifier.Identifiers.Last().Value));
if (indexColumnExists)
{
break;
}
}
if (!indexColumnExists)
{
problems.Add(new SqlRuleProblem(Message, sqlObj, inClause));
}
}
}
}
return(problems);
}
