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(ProgrammingSchemaTypes);
var visitor = new JoinVisitor();
fragment.Accept(visitor);
var views = sqlObj.GetReferenced(DacQueryScopes.UserDefined)
.Where(x => x.ObjectType == ModelSchema.View).Select(v => v.Name.Parts.Last()).ToList();
var joins = visitor.QualifiedJoins.Where(j => Ignorables.ShouldNotIgnoreRule(j.ScriptTokenStream, RuleId, j.StartLine));
var leftSideOffenders =
from o in joins
where o.FirstTableReference != null &&
views.Contains((o.FirstTableReference as NamedTableReference)?.SchemaObject.Identifiers.Last().Value)
select o.FirstTableReference as NamedTableReference;
var rightSideOffenders =
from o in joins
where o.SecondTableReference != null &&
views.Contains((o.SecondTableReference as NamedTableReference)?.SchemaObject.Identifiers.Last().Value)
select o.SecondTableReference as NamedTableReference;
problems.AddRange(leftSideOffenders.Union(rightSideOffenders).Select(o => new SqlRuleProblem(Message, sqlObj, o)));
return(problems);
}
protected override Expression VisitMethodCall(MethodCallExpression node)
{
if (node.Method.Name == nameof(Enumerable.Where))
{
var predicate = node.Arguments[1] is UnaryExpression quote ? (LambdaExpression)quote.Operand : (LambdaExpression)node.Arguments[1];
var joinVisitor = new JoinVisitor(_edmModel, node.Arguments[0], predicate.Parameters[0]);
Expression expression = joinVisitor.Visit(node.Arguments[1]);
if (node.Arguments[1] != expression)
{
Expression[] arguments = node.Arguments.ToArray();
arguments[1] = expression;
return(node.Update(node.Object !, arguments));
}
}
else if (node.Method.Name == nameof(Enumerable.SelectMany) && node.Arguments.Count == 2)
{
var predicate = node.Arguments[1] is UnaryExpression quote ? (LambdaExpression)quote.Operand : (LambdaExpression)node.Arguments[1];
var joinVisitor = new JoinVisitor(_edmModel, node.Arguments[0], predicate.Parameters[0]);
Expression body = joinVisitor.Visit(predicate.Body);
if (predicate.Body != body)
{
Expression[] arguments = node.Arguments.ToArray();
arguments[1] = Expression.Lambda(body, predicate.Parameters);
return(node.Update(node.Object !, arguments));
}
}
return(base.VisitMethodCall(node));
}
public static IEnumerable <Type> GetJoinTypes(System.Linq.Expressions.Expression expression)
{
var joinVisitor = new JoinVisitor();
joinVisitor.Visit(expression);
return(joinVisitor.Types);
}
/// <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 tableHintVisitor = new TableHintVisitor();
var queryHintVisitor = new StatementListVisitor();
var joinHintVisitor = new JoinVisitor();
fragment.Accept(tableHintVisitor, queryHintVisitor, joinHintVisitor);
var tableOffenders =
from n in tableHintVisitor.NotIgnoredStatements(RuleId)
where n.HintKind != TableHintKind.NoLock
select n;
var queryOffenders =
from o in queryHintVisitor.NotIgnoredStatements(RuleId)
from o1 in o.Statements
let s = o1 as StatementWithCtesAndXmlNamespaces
where s?.OptimizerHints != null && s?.OptimizerHints.Count > 0
select s;
var joinOffenders =
from j in joinHintVisitor.NotIgnoredStatements(RuleId)
where j is QualifiedJoin &&
((QualifiedJoin)j).JoinHint != JoinHint.None
select j as QualifiedJoin;
problems.AddRange(tableOffenders.Select(o => new SqlRuleProblem(Message, sqlObj, o)));
problems.AddRange(queryOffenders.Select(o => new SqlRuleProblem(Message, sqlObj, o)));
problems.AddRange(joinOffenders.Select(o => new SqlRuleProblem(Message, sqlObj, o)));
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(ProgrammingSchemaTypes);
var joinVisitor = new JoinVisitor();
fragment.Accept(joinVisitor);
foreach (var join in joinVisitor.Statements)
{
var tableVarVisitor = new TableVariableVisitor();
join.Accept(tableVarVisitor);
problems.AddRange(tableVarVisitor.NotIgnoredStatements(RuleId).Select(tv => new SqlRuleProblem(Message, sqlObj, tv)));
}
return(problems);
}
public static IList <JoinInfo> GetFromClauseJoinTables(this FromClause from)
{
if (from == null)
{
throw new ArgumentNullException(nameof(from));
}
var joins = new List <JoinInfo>();
if (from.TableReferences.Count == 0 || from.TableReferences.First().GetType() != typeof(QualifiedJoin))
{
return(joins);
}
var joinVisitor = new JoinVisitor();
from.Accept(joinVisitor);
//build the list of pure tables along with the list of boolean comparisons
foreach (var join in joinVisitor.QualifiedJoins)
{
var joinInfo = new JoinInfo {
};
var boolVisitor = new BooleanComparisonVisitor();
join.SearchCondition.Accept(boolVisitor);
joinInfo.Compares = new List <BooleanComparisonExpression>(boolVisitor.Statements);
if (join.FirstTableReference.GetType() == typeof(NamedTableReference))
{
joinInfo.Table1 = join.FirstTableReference as NamedTableReference;
}
if (join.SecondTableReference.GetType() == typeof(NamedTableReference))
{
joinInfo.Table2 = join.SecondTableReference as NamedTableReference;
}
joins.Add(joinInfo);
}
//table2 should always have a table..... maybe. unless the table is actually a sub-select. Then we will ignore it
foreach (var join in joins.Where(j => j.Table2 != null))
{
var table1 = join.Table1;
var table2 = join.Table2;
var table2Alias = table2.Alias?.Value;
var table2Name = new ObjectIdentifier(table2.SchemaObject.Identifiers.Select(x => x.Value));
//we need to figure out which side of the comparison goes to which table..... PITA. yes.....
foreach (var compare in join.Compares
.Where(x => x.FirstExpression is ColumnReferenceExpression && x.SecondExpression is ColumnReferenceExpression))
{
//we use a loop as we need to check both the first expression and the second expression to see which table the columns belong to
for (int i = 0; i < 2; i++)
{
var col = (i == 0 ? compare.FirstExpression : compare.SecondExpression) as ColumnReferenceExpression;
var colTblName = GetTableOrAliasName(col.MultiPartIdentifier.Identifiers);
if (table2Alias.StringEquals(colTblName.First()) || table2Name.CompareTo(colTblName) >= 5)
{
join.Table2JoinColumns.Add(col);
continue;
}
//use table1 if it was supplied in the compare. else scan the joins to find the matching table to the column
var tbl = table1 ?? joins.Select(x =>
{
if (CheckName(x.Table2, col))
{
return(x.Table2);
}
if (CheckName(x.Table1, col))
{
return(x.Table1);
}
return(null);
}).FirstOrDefault(x => x != null);
if (tbl != null)
{
var tblAlias = tbl.Alias?.Value;
var tblName = new ObjectIdentifier(tbl.SchemaObject.Identifiers.Select(x => x.Value));
if (join.Table1 == null)
{
join.Table1 = tbl;
}
if (tblAlias.StringEquals(colTblName.First()) || tblName.CompareTo(colTblName) >= 5)
{
join.Table1JoinColumns.Add(col);
}
}
}
}
}
return(joins);
}
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);
}
