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 hasForceOrder = stmt.OptimizerHints?.Any(oh => oh.HintKind == OptimizerHintKind.ForceOrder);
if (hasForceOrder.GetValueOrDefault(false))
{
continue;
}
var querySpecificationVisitor = new QuerySpecificationVisitor();
stmt.QueryExpression.Accept(querySpecificationVisitor);
foreach (var query in querySpecificationVisitor.Statements)
{
var fromClause = query.FromClause;
if (fromClause == null)
{
continue;
}
var namedTableVisitor = new NamedTableReferenceVisitor();
fromClause.Accept(namedTableVisitor);
var tableCount = namedTableVisitor.Count - 1;
if (tableCount > 8)
{
var msg = string.Format(Message, tableCount);
problems.Add(new SqlRuleProblem(msg, sqlObj, fromClause));
}
}
}
return(problems);
}
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 visitor = new SelectStatementVisitor();
fragment.Accept(visitor);
foreach (var stmt in visitor.Statements)
{
var querySpecificationVisitor = new QuerySpecificationVisitor();
stmt.QueryExpression.Accept(querySpecificationVisitor);
foreach (var query in querySpecificationVisitor.Statements)
{
var fromClause = query.FromClause;
if (fromClause == null)
{
continue;
}
var joinVisitor = new JoinVisitor();
fromClause.Accept(joinVisitor);
var outerJoins =
(from j in joinVisitor.QualifiedJoins
let t = j.QualifiedJoinType
where (t == QualifiedJoinType.LeftOuter || t == QualifiedJoinType.RightOuter) &&
Ignorables.ShouldNotIgnoreRule(j.ScriptTokenStream, RuleId, j.StartLine)
select j).ToList();
if (outerJoins.Count == 0)
{
continue;
}
var columns = ColumnReferenceExpressionVisitor.VisitSelectElements(query.SelectElements);
var whereClause = query.WhereClause;
if (whereClause == null)
{
continue;
}
var isnullVisitor = new ISNULLVisitor();
whereClause.Accept(isnullVisitor);
if (isnullVisitor.Count == 0)
{
continue;
}
foreach (var join in outerJoins)
{
TableReference table = null;
if (join.QualifiedJoinType == QualifiedJoinType.LeftOuter)
{
table = join.SecondTableReference as TableReference;
}
else
{
table = join.FirstTableReference as TableReference;
}
var tableName = table.GetName();
var alias = (table as TableReferenceWithAlias)?.Alias.Value;
//are there any columns in the select that match this table?
if (columns.Any(c =>
{
var colTableName = c.GetName();
return(_comparer.Equals(tableName, colTableName) || _comparer.Equals(alias, colTableName));
}))
{
continue;
}
//no columns, now we need to look in the where clause for a null check againt this table.
if (isnullVisitor.Statements.Any(nc =>
{
var col = nc.FirstExpression as ColumnReferenceExpression ?? nc.SecondExpression as ColumnReferenceExpression;
if (col == null)
{
return(false);
}
var colTableName = col.GetName();
return(_comparer.Equals(tableName, colTableName) || _comparer.Equals(alias, colTableName));
}))
{
problems.Add(new SqlRuleProblem(Message, sqlObj, join));
}
}
}
}
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);
}
