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);
}
/// <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);
}
//ModelSchema.Procedure, ModelSchema.ScalarFunction, ModelSchema.TableValuedFunction, ModelSchema.Table
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(
typeof(CreateProcedureStatement),
typeof(CreateFunctionStatement),
typeof(CreateTableStatement)
);
var variableVisitor = new VariablesVisitor();
var tableDefinitionVisitor = new TableDefinitionVisitor();
fragment.Accept(variableVisitor, tableDefinitionVisitor);
var variables =
from d in variableVisitor.DeclareVariables
from v in d.Declarations
let type = (v.DataType as SqlDataTypeReference)
let typeOption = type?.SqlDataTypeOption
where _types.Contains(typeOption.GetValueOrDefault(SqlDataTypeOption.None))
where type?.Parameters.Count != _expectParameterCount
select v;
var parameters =
from p in variableVisitor.ProcedureParameters
let type = (p.DataType as SqlDataTypeReference)
let typeOption = type?.SqlDataTypeOption
where _types.Contains(typeOption.GetValueOrDefault(SqlDataTypeOption.None))
where type?.Parameters.Count != _expectParameterCount
select p;
var columns =
from s in tableDefinitionVisitor.Statements
from c in s.ColumnDefinitions
let type = (c.DataType as SqlDataTypeReference)
let typeOption = type?.SqlDataTypeOption
where _types.Contains(typeOption.GetValueOrDefault(SqlDataTypeOption.None))
where type?.Parameters.Count != _expectParameterCount
select c;
problems.AddRange(variables.Select(p => new SqlRuleProblem(_message, sqlObj, p)));
problems.AddRange(parameters.Select(p => new SqlRuleProblem(_message, sqlObj, p)));
problems.AddRange(columns.Select(p => new SqlRuleProblem(_message, sqlObj, p)));
var castVisitor = new CastCallVisitor();
var convertVisitor = new ConvertCallVisitor();
fragment.Accept(castVisitor, convertVisitor);
var castCalls = from c in castVisitor.Statements
let type = (c.DataType as SqlDataTypeReference)
let typeOption = type?.SqlDataTypeOption
where _types.Contains(typeOption.GetValueOrDefault(SqlDataTypeOption.None))
where type?.Parameters.Count != _expectParameterCount
select c;
var convertCalls = from c in convertVisitor.Statements
let type = (c.DataType as SqlDataTypeReference)
let typeOption = type?.SqlDataTypeOption
where _types.Contains(typeOption.GetValueOrDefault(SqlDataTypeOption.None))
where type?.Parameters.Count != _expectParameterCount
select c;
problems.AddRange(castCalls.Select(p => new SqlRuleProblem(_message, sqlObj, p)));
problems.AddRange(convertCalls.Select(p => new SqlRuleProblem(_message, sqlObj, p)));
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);
}
