/// <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 visitor = new SelectStatementVisitor(); fragment.Accept(visitor); var offenders = from s in visitor.NotIgnoredStatements(RuleId) let tn = s.Into == null ? "" : s.Into.Identifiers?.LastOrDefault()?.Value where s.Into != null && !(tn.StartsWith("#") || !tn.StartsWith("@")) select s.Into; problems.AddRange(offenders.Select(s => new SqlRuleProblem(Message, sqlObj, s))); 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 select in selectStatementVisitor.Statements) { var selectStarVisitor = new SelectStarExpressionVisitor(); select.AcceptChildren(selectStarVisitor); problems.AddRange(selectStarVisitor.NotIgnoredStatements(RuleId) .Select(ss => new SqlRuleProblem(Message, sqlObj, ss))); } return(problems); }
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; if (sqlObj == null || sqlObj.IsWhiteListed()) { return(problems); } var name = sqlObj.Name.GetName(); var fragment = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateProcedureStatement)); if (fragment.ScriptTokenStream == null) { return(problems); } var parameterVisitor = new ParameterVisitor(); var selectVisitor = new SelectStatementVisitor(); fragment.Accept(parameterVisitor); fragment.Accept(selectVisitor); if (parameterVisitor.Count == 0 || selectVisitor.Count == 0) { return(problems); } var setVisitor = new SetVariableStatementVisitor(); fragment.Accept(setVisitor); foreach (var param in parameterVisitor.Statements.Select(p => p.VariableName.Value)) { var selectsUsingParam = selectVisitor.Statements.GetSelectsUsingParameterInWhere(param).ToList(); if (!selectsUsingParam.Any()) { continue; } var selectStartLine = selectsUsingParam.FirstOrDefault()?.StartLine; var getAssignmentSelects = selectVisitor.NotIgnoredStatements(RuleId) .GetSelectsSettingParameterValue(param).Where(sel => sel.StartLine < selectStartLine); var setStatements = setVisitor.NotIgnoredStatements(RuleId) .Where(set => _comparer.Equals(set.Variable.Name, param) && set.StartLine < selectStartLine); problems.AddRange(getAssignmentSelects.Select(x => new SqlRuleProblem(Message, sqlObj, x))); problems.AddRange(setStatements.Select(x => new SqlRuleProblem(Message, sqlObj, x))); } 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 = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes); var selectStatementVisitor = new SelectStatementVisitor(); fragment.Accept(selectStatementVisitor); if (selectStatementVisitor.Count == 0) { return(problems); } foreach (var select in selectStatementVisitor.Statements) { if (select.QueryExpression is QuerySpecification query) { var fromClause = query.FromClause; if (fromClause == null) { continue; } //check to ensure we have more than one table var namedTableVisitor = new NamedTableReferenceVisitor(); fromClause.Accept(namedTableVisitor); if (namedTableVisitor.Count <= 1) { continue; } var columnReferences = new ColumnReferenceExpressionVisitor(); query.Accept(columnReferences); var offenders = columnReferences.NotIgnoredStatements(RuleId) .Where(c => CheckName(c)) .Select(n => n.MultiPartIdentifier.Identifiers[0]); if (offenders.Any()) { problems.Add(new SqlRuleProblem(Message, sqlObj, select)); } } } 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 = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes); var selectStatementVisitor = new SelectStatementVisitor(); fragment.Accept(selectStatementVisitor); if (selectStatementVisitor.Count == 0) { return(problems); } foreach (var select in selectStatementVisitor.Statements) { var fromClause = (select.QueryExpression as QuerySpecification)?.FromClause; //ignore selects that do not use a from clause with tables if (fromClause == null) { continue; } var visitor = new NamedTableReferenceVisitor() { TypeFilter = ObjectTypeFilter.PermanentOnly }; fromClause.Accept(visitor); //only scan for aliases if there are more than 1 table in the from clause if (visitor.Count <= 1) { continue; } var offenders = from t in visitor.NotIgnoredStatements(RuleId) where t.Alias == null select t; problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t))); } 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 stmt in selectStatementVisitor.Statements) { var hasForceOrder = stmt.OptimizerHints?.Any(oh => oh.HintKind == OptimizerHintKind.ForceOrder); if (hasForceOrder.GetValueOrDefault(false)) { continue; } var querySpecificationVisitor = new QuerySpecificationVisitor(); stmt.QueryExpression.Accept(querySpecificationVisitor); foreach (var query in querySpecificationVisitor.Statements) { var fromClause = query.FromClause; if (fromClause == null) { continue; } var namedTableVisitor = new NamedTableReferenceVisitor(); fromClause.Accept(namedTableVisitor); var tableCount = namedTableVisitor.Count - 1; if (tableCount > 8) { var msg = string.Format(Message, tableCount); problems.Add(new SqlRuleProblem(msg, sqlObj, fromClause)); } } } 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 select in selectStatementVisitor.Statements) { var booleanCompareVisitor = new BooleanComparisonVisitor(); select.Accept(booleanCompareVisitor); var offenders = from cmp in booleanCompareVisitor.NotIgnoredStatements(RuleId) where TestCompare(cmp) select cmp; problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t))); } var actionStatementVisitor = new ActionStatementVisitor(); fragment.Accept(actionStatementVisitor); foreach (var action in actionStatementVisitor.Statements) { var booleanCompareVisitor = new BooleanComparisonVisitor(); action.Accept(booleanCompareVisitor); var offenders = from cmp in booleanCompareVisitor.NotIgnoredStatements(RuleId) where TestCompare(cmp) select cmp; problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t))); } 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); }
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext) { var problems = new List <SqlRuleProblem>(); var candidates = new List <StatementWithCtesAndXmlNamespaces>(); var sqlObj = ruleExecutionContext.ModelElement; if (sqlObj == null) { return(problems); } var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingSchemaTypes); List <StatementWithCtesAndXmlNamespaces> statements = new List <StatementWithCtesAndXmlNamespaces>(); var selectVisitor = new SelectStatementVisitor(); fragment.Accept(selectVisitor); statements.AddRange(selectVisitor.NotIgnoredStatements(RuleId)); var actionStatementVisitor = new ActionStatementVisitor(); fragment.Accept(actionStatementVisitor); statements.AddRange(actionStatementVisitor.NotIgnoredStatements(RuleId)); if (statements.Count() > 1) { statements.ForEach(statement => { if (DoesStatementHaveDateFunction(statement)) { candidates.Add(statement); } }); } problems.AddRange(candidates.Select(s => new SqlRuleProblem(Message, sqlObj, s))); 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 = ruleExecutionContext.ScriptFragment.GetFragment( typeof(CreateTriggerStatement) ); var selectVisitor = new SelectStatementVisitor(); fragment.Accept(selectVisitor); problems.AddRange(selectVisitor.NotIgnoredStatements(RuleId).Select(t => new SqlRuleProblem(Message, sqlObj, t))); 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(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); }
/// <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 selectStatementVisitor = new SelectStatementVisitor(); fragment.Accept(selectStatementVisitor); foreach (var stmt in selectStatementVisitor.Statements) { var querySpecificationVisitor = new QuerySpecificationVisitor(); stmt.QueryExpression.Accept(querySpecificationVisitor); foreach (var query in querySpecificationVisitor.Statements) { var inPredicateVisitor = new InPredicateVisitor(); query.Accept(inPredicateVisitor); var inClauses = inPredicateVisitor.NotIgnoredStatements(RuleId) .Where(i => !i.NotDefined && i.Expression is ColumnReferenceExpression); if (inClauses.Count() == 0) { continue; } foreach (var inClause in inClauses) { var indexColumnExists = false; var column = inClause.Expression as ColumnReferenceExpression; var table = GetTableFromColumn(sqlObj, query, column); //most likely the base is a view.... /sigh if (table == null) { continue; } var indexes = table.GetChildren(DacQueryScopes.All).Where(x => x.ObjectType == ModelSchema.Index); foreach (var index in indexes) { indexColumnExists = index.GetReferenced(DacQueryScopes.All) .Any(x => x.ObjectType == ModelSchema.Column && _comparer.Equals(x.Name.Parts.Last(), column.MultiPartIdentifier.Identifiers.Last().Value)); if (indexColumnExists) { break; } } if (!indexColumnExists) { problems.Add(new SqlRuleProblem(Message, sqlObj, inClause)); } } } } return(problems); }