/// <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 whereClauseVisitor = new WhereClauseVisitor();
fragment.Accept(whereClauseVisitor);
foreach (var whereClause in whereClauseVisitor.Statements)
{
var functionVisitor = new FunctionCallVisitor();
whereClause.Accept(functionVisitor);
var offenders = from FunctionCall f in functionVisitor.NotIgnoredStatements(RuleId)
where CheckFunction(f)
select f;
problems.AddRange(offenders.Select(f => new SqlRuleProblem(Message, sqlObj, f)));
}
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);
// only visit the function calls inside the where clauses
var visitor = new WhereClauseVisitor();
fragment.Accept(visitor);
foreach (var clause in visitor.Statements)
{
var functionVisitor = new FunctionCallVisitor("charindex");
clause.Accept(functionVisitor);
problems.AddRange(functionVisitor.NotIgnoredStatements(RuleId).Select(f => new SqlRuleProblem(Message, sqlObj, f)));
}
return(problems);
}
private bool CheckFunctionCallsForDateFunction(IList <FunctionCall> functionCalls)
{
bool hasDateFunctions = false;
foreach (var functionCall in functionCalls)
{
if (FunctionNames.Contains(functionCall.FunctionName.Value.ToLower()))
{
hasDateFunctions = true;
}
else
{
foreach (var param in functionCall.Parameters)
{
var functionVisitor = new FunctionCallVisitor();
param.Accept(functionVisitor);
hasDateFunctions = hasDateFunctions || CheckFunctionCallsForDateFunction(functionVisitor.Statements);
if (hasDateFunctions)
{
break;
}
}
}
if (hasDateFunctions)
{
break;
}
}
return(hasDateFunctions);
}
/// <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 ifVisitor = new IfStatementVisitor();
fragment.Accept(ifVisitor);
if (ifVisitor.Count == 0)
{
return(problems);
}
foreach (var ifstmt in ifVisitor.Statements)
{
var functionVisitor = new FunctionCallVisitor("count");
ifstmt.Predicate.Accept(functionVisitor);
if (functionVisitor.Statements.Any() && CheckIf(ifstmt))
{
problems.Add(new SqlRuleProblem(Message, sqlObj, ifstmt));
}
}
return(problems);
}
private IEnumerable <string> ExtractCalledFunctions(Expression expression)
{
var visitor = new FunctionCallVisitor(_knownFunctions.Select(f => f.Name).ToArray());
visitor.Visit(expression);
return(visitor.FunctionNames);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var model = ruleExecutionContext.SchemaModel;
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingSchemaTypes);
var visitor = new DataModificationStatementVisitor();
fragment.Accept(visitor);
var modelFunctions = model.GetObjects(DacQueryScopes.UserDefined, new[] { ModelSchema.ScalarFunction, ModelSchema.TableValuedFunction });
foreach (var stmt in visitor.Statements)
{
var functionCallVisitor = new FunctionCallVisitor();
stmt.Accept(functionCallVisitor);
foreach (var functionCall in functionCallVisitor.NotIgnoredStatements(RuleId))
{
var createFunctionVisitor = new CreateFunctionVisitor();
IList <ParseError> parseErrors;
TSqlFragment fnFragment;
var fnName = functionCall.GetName();
var modelFunction = modelFunctions.FirstOrDefault(mf => _comparer.Equals(mf.Name.GetName(), fnName));
if (modelFunction == null)
{
continue;
}
//we need to parse the sql into a fragment, so we can use the visitors on it
fnFragment = modelFunction.GetFragment(out parseErrors);
fnFragment.Accept(createFunctionVisitor);
if (!createFunctionVisitor.Statements.Any(crfn => crfn.Options != null && crfn.Options.Any(o => o.OptionKind == FunctionOptionKind.SchemaBinding)))
{
problems.Add(new SqlRuleProblem(Message, sqlObj, functionCall));
}
}
}
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 selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var statement in selectStatementVisitor.Statements)
{
bool found = false;
if (statement.QueryExpression is QuerySpecification selects)
{
foreach (var selectElement in selects.SelectElements)
{
var functionCallVisitor = new FunctionCallVisitor();
selectElement.Accept(functionCallVisitor);
foreach (var function in functionCallVisitor.NotIgnoredStatements(RuleId))
{
if (function.UniqueRowFilter == UniqueRowFilter.Distinct &&
Constants.Aggregates.Contains(function.FunctionName.Value.ToUpper()))
{
problems.Add(new SqlRuleProblem(Message, sqlObj, statement));
}
}
if (found)
{
break;
}
}
}
}
return(problems);
}
private bool DoesStatementHaveDateFunction(StatementWithCtesAndXmlNamespaces statement)
{
bool hasDateFunction = false;
var allFunctions = new FunctionCallVisitor();
statement.Accept(allFunctions);
if (allFunctions.Statements.Any(p => FunctionNames.Contains(p.FunctionName.Value.ToLower())))
{
hasDateFunction = true;
}
else
{
hasDateFunction = CheckFunctionCallsForDateFunction(allFunctions.Statements);
}
return(hasDateFunction);
}
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(CreateFunctionStatement)
);
var variablesVisitor = new VariablesVisitor();
fragment.Accept(variablesVisitor);
var variables = variablesVisitor.GetVariables();
var queries = new QueryStatementVisitor();
fragment.Accept(queries);
foreach (var query in queries.Statements)
{
var visitor = new FunctionCallVisitor("isnull", "coalesce");
query.Accept(visitor);
if (!visitor.Statements.Any())
{
continue;
}
var columnDataTypes = new Dictionary <NamedTableView, IDictionary <string, DataTypeView> >();
query.GetTableColumnDataTypes(columnDataTypes, ruleExecutionContext.SchemaModel);
foreach (var func in visitor.Statements)
{
var paramTypes = new List <string>();
foreach (var parameter in func.Parameters)
{
if (parameter is ColumnReferenceExpression colRef)
{
var dtView = columnDataTypes.GetDataTypeView(colRef);
if (dtView != null)
{
paramTypes.Add(dtView.DataType);
}
}
else
{
paramTypes.Add(GetDataType(parameter, variables));
}
}
if (!paramTypes.All(x => _comparer.Equals(x, paramTypes.First())))
{
var funcName = func.FunctionName.Value.ToUpper();
problems.Add(new SqlRuleProblem(string.Format(Message, funcName), sqlObj, func));
}
}
}
return(problems);
}