public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement; //proc / view / function
try
{
if (sqlObj != null)
{
var fragment = ruleExecutionContext.ScriptFragment;
//get the combined parameters and declare variables into one searchable list
var variablesVisitor = new VariablesVisitor();
fragment.AcceptChildren(variablesVisitor);
var variables = variablesVisitor.GetVariables();
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var select in selectStatementVisitor.Statements)
{
var query = select.QueryExpression as QuerySpecification;
if (query != null && query.WhereClause != null)
{
var booleanComparisonVisitor = new BooleanComparisonVisitor();
query.WhereClause.Accept(booleanComparisonVisitor);
foreach (var comparison in booleanComparisonVisitor.Statements)
{
var datatype1 = GetDataType(sqlObj, query, comparison.FirstExpression, variables);
if (string.IsNullOrEmpty(datatype1))
{
continue;
}
var datatype2 = GetDataType(sqlObj, query, comparison.SecondExpression, variables);
if (string.IsNullOrEmpty(datatype2))
{
continue;
}
//when checking the numeric literal I am not sure if it is a bit or tinyint.
if ((_comparer.Equals(datatype1, "bit") && _comparer.Equals(datatype2, "tinyint")) || (_comparer.Equals(datatype1, "tinyint") && _comparer.Equals(datatype2, "bit")))
{
continue;
}
if (!_comparer.Equals(datatype1, datatype2))
{
string msg = string.Format(Message, sqlObj.ObjectType.Name, RuleUtils.GetElementName(ruleExecutionContext, sqlObj));
problems.Add(new SqlRuleProblem(msg, sqlObj, comparison));
}
}
}
}
}
}
catch (System.Exception ex)
{
//TODO: PROPERLY LOG THIS ERROR
Debug.WriteLine(ex.ToString());
//throw;
}
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; //proc / view / function
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
try
{
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
//get the combined parameters and declare variables into one search-able list
var variablesVisitor = new VariablesVisitor();
fragment.AcceptChildren(variablesVisitor);
var variables = variablesVisitor.GetVariables();
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var select in selectStatementVisitor.Statements)
{
if (select.QueryExpression is QuerySpecification query && query.WhereClause != null)
{
var booleanComparisonVisitor = new BooleanComparisonVisitor();
query.WhereClause.Accept(booleanComparisonVisitor);
foreach (var comparison in booleanComparisonVisitor.Statements)
{
if ((comparison.FirstExpression is NullLiteral || comparison.SecondExpression is NullLiteral) &&
(comparison.ComparisonType == BooleanComparisonType.Equals ||
comparison.ComparisonType == BooleanComparisonType.NotEqualToBrackets ||
comparison.ComparisonType == BooleanComparisonType.NotEqualToExclamation) //probably can remove the ComparisonTypeCheck
)
{
problems.Add(new SqlRuleProblem(Message, sqlObj, comparison));
}
}
}
}
}
catch (System.Exception ex)
{
//TODO: PROPERLY LOG THIS ERROR
Debug.WriteLine(ex.ToString());
//throw;
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement; //proc / view / function
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
try
{
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
//get the combined parameters and declare variables into one searchable list
var variablesVisitor = new VariablesVisitor();
fragment.AcceptChildren(variablesVisitor);
var variables = variablesVisitor.GetVariables();
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var select in selectStatementVisitor.Statements)
{
if (select.QueryExpression is QuerySpecification query && query.WhereClause != null && query.FromClause != null)
{
var booleanComparisonVisitor = new BooleanComparisonVisitor();
query.WhereClause.Accept(booleanComparisonVisitor);
var comparisons = booleanComparisonVisitor.Statements
.Where(x =>
(x.FirstExpression is ColumnReferenceExpression ||
x.SecondExpression is ColumnReferenceExpression));
if (!comparisons.Any())
{
continue;
}
var dataTypesList = new Dictionary <NamedTableView, IDictionary <string, DataTypeView> >();
select.GetTableColumnDataTypes(dataTypesList, ruleExecutionContext.SchemaModel);
foreach (var comparison in comparisons)
{
var col1 = comparison.FirstExpression as ColumnReferenceExpression;
var col2 = comparison.SecondExpression as ColumnReferenceExpression;
var datatype1 = string.Empty;
var datatype2 = string.Empty;
if (col1 != null)
{
var dtView = dataTypesList.GetDataTypeView(col1);
if (dtView != null)
{
datatype1 = dtView.DataType;
}
}
else
{
datatype1 = GetDataType(sqlObj,
query,
comparison.FirstExpression,
variables,
ruleExecutionContext.SchemaModel);
}
if (col2 != null)
{
var dtView = dataTypesList.GetDataTypeView(col2);
if (dtView != null)
{
datatype2 = dtView.DataType;
}
}
else
{
datatype2 = GetDataType(sqlObj,
query,
comparison.SecondExpression,
variables,
ruleExecutionContext.SchemaModel);
}
if (string.IsNullOrWhiteSpace(datatype1) || string.IsNullOrWhiteSpace(datatype2))
{
continue;
}
//when checking the numeric literal I am not sure if it is a bit or tinyint.
if ((_comparer.Equals(datatype1, "bit") && _comparer.Equals(datatype2, "tinyint")) ||
(_comparer.Equals(datatype1, "tinyint") && _comparer.Equals(datatype2, "bit")))
{
continue;
}
if (!_comparer.Equals(datatype1, datatype2))
{
problems.Add(new SqlRuleProblem(Message, sqlObj, comparison));
}
}
}
}
}
catch (System.Exception ex)
{
//TODO: PROPERLY LOG THIS ERROR
Debug.WriteLine(ex.ToString());
//throw;
}
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(
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);
}