/// <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);
if (selectStatementVisitor.Count == 0)
{
return(problems);
}
foreach (var select in selectStatementVisitor.Statements)
{
if (select.QueryExpression is QuerySpecification query)
{
var fromClause = query.FromClause;
if (fromClause == null)
{
continue;
}
//check to ensure we have more than one table
var namedTableVisitor = new NamedTableReferenceVisitor();
fromClause.Accept(namedTableVisitor);
if (namedTableVisitor.Count <= 1)
{
continue;
}
var columnReferences = new ColumnReferenceExpressionVisitor();
query.Accept(columnReferences);
var offenders = columnReferences.NotIgnoredStatements(RuleId)
.Where(c => CheckName(c))
.Select(n => n.MultiPartIdentifier.Identifiers[0]);
if (offenders.Any())
{
problems.Add(new SqlRuleProblem(Message, sqlObj, select));
}
}
}
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 scalarSubqueryVisitor = new ScalarSubqueryVisitor();
fragment.Accept(scalarSubqueryVisitor);
var offenders = scalarSubqueryVisitor.NotIgnoredStatements(RuleId).Where(s =>
{
var whereClause = (s.QueryExpression as QuerySpecification)?.WhereClause;
if (whereClause == null)
{
return(false);
}
var booleanCompares = new BooleanComparisonVisitor();
whereClause.Accept(booleanCompares);
foreach (var booleanCompare in booleanCompares.Statements)
{
var colVisitor = new ColumnReferenceExpressionVisitor();
booleanCompare.AcceptChildren(colVisitor);
if (colVisitor.Count > 1)
{
return(true);
}
}
return(false);
}).ToList();
problems.AddRange(offenders.Select(o => new SqlRuleProblem(Message, sqlObj, o)));
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);
}
