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); }