/// <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(
typeof(CreateProcedureStatement),
typeof(CreateTriggerStatement));
var visitor = new PredicateVisitor();
fragment.Accept(visitor);
var predicates = from o
in visitor.Statements
where o.Options == SetOptions.NoCount && o.IsOn
select o;
var createToken = fragment.ScriptTokenStream.FirstOrDefault(t => t.TokenType == TSqlTokenType.Create);
if (!predicates.Any() && Ignorables.ShouldNotIgnoreRule(fragment.ScriptTokenStream, RuleId, createToken.Line))
{
problems.Add(new SqlRuleProblem(Message, sqlObj));
}
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(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);
}
/// <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 name = ruleExecutionContext.GetObjectName(sqlObj, ElementNameStyle.SimpleName).ToLower();
var fragment = ruleExecutionContext.GetFragment();
if (PartialPredicate(name)(BadCharacters) &&
Ignorables.ShouldNotIgnoreRule(fragment.ScriptTokenStream, _RuleId, fragment.StartLine))
{
problems.Add(new SqlRuleProblem(Message, sqlObj));
}
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 proc = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateProcedureStatement)) as CreateProcedureStatement;
if (proc.Options.Any(o =>
o.OptionKind == ProcedureOptionKind.Recompile) &&
Ignorables.ShouldNotIgnoreRule(proc.ScriptTokenStream, RuleId, proc.StartLine))
{
problems.Add(new SqlRuleProblem(Message, sqlObj));
}
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 = sqlObj.GetFragment();
if (fragment.ScriptTokenStream == null)
{
return(problems);
}
var visitor = new VariablesVisitor();
fragment.Accept(visitor);
var parms = from pp in visitor.ProcedureParameters
join t in fragment.ScriptTokenStream
on new { Name = pp.VariableName.Value?.ToLower(), Type = TSqlTokenType.Variable }
equals new { Name = t.Text?.ToLower(), Type = t.TokenType }
where Ignorables.ShouldNotIgnoreRule(fragment.ScriptTokenStream, RuleId, pp.StartLine)
select pp;
var unusedParms = parms.GroupBy(p => p.VariableName.Value?.ToLower())
.Where(g => g.Count() == 1).Select(g => g.First());
problems.AddRange(unusedParms.Select(rp => new SqlRuleProblem(string.Format(Message, rp.VariableName.Value), sqlObj, rp)));
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);
}