/// <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) { List <SqlRuleProblem> problems = new List <SqlRuleProblem>(); TSqlObject sqlObj = ruleExecutionContext.ModelElement; if (sqlObj == null || sqlObj.IsWhiteListed()) { return(problems); } var fragment = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateProcedureStatement)); var name = sqlObj.Name.GetName(); var visitor = new InsertStatementVisitor(); fragment.Accept(visitor); if (visitor.Count == 0) { return(problems); } var offenders = visitor.Statements.Where(s => s.InsertSpecification.Columns.Count == 0).ToList(); problems.AddRange(offenders.Select(o => new SqlRuleProblem(Message, sqlObj, o))); return(problems); }
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext) { List <SqlRuleProblem> problems = new List <SqlRuleProblem>(); TSqlObject sqlObj = ruleExecutionContext.ModelElement; if (sqlObj == null || sqlObj.IsWhiteListed()) { return(problems); } var fragment = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateTableStatement)); var tableName = sqlObj.Name.GetName(); ColumnDefinitionVisitor columnVisitor = new ColumnDefinitionVisitor(); fragment.Accept(columnVisitor); var longChars = columnVisitor.Statements .Where(col => col.DataType != null && col.DataType.Name != null) .Select(col => new { column = col, name = col.ColumnIdentifier.Value, type = col.DataType.Name.Identifiers.FirstOrDefault()?.Value, length = GetDataTypeLength(col) }) .Where(x => (_comparer.Equals(x.type, "char") || _comparer.Equals(x.type, "nchar")) && x.length > 9); problems.AddRange(longChars.Select(col => new SqlRuleProblem(Message, sqlObj, col.column))); 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) { List <SqlRuleProblem> problems = new List <SqlRuleProblem>(); TSqlObject sqlObj = ruleExecutionContext.ModelElement; if (sqlObj == null || sqlObj.IsWhiteListed()) { return(problems); } var fragment = ruleExecutionContext.ScriptFragment.GetFragment( typeof(CreateTableStatement), typeof(CreateProcedureStatement), typeof(CreateViewStatement) ); if (sqlObj.ObjectType == ModelSchema.Procedure) { var parameters = sqlObj.GetReferenced().Where(x => x.ObjectType == ModelSchema.Parameter); foreach (var parameter in parameters) { var datatypes = parameter.GetReferenced(Parameter.DataType).Where(t => { var name = t.Name?.Parts.LastOrDefault()?.ToLower(); return(name == "real" || name == "float"); }); if (datatypes.Any()) { problems.Add(new SqlRuleProblem(Message, parameter)); } } } else //tables, views { var columns = sqlObj.GetReferenced().Where(x => x.ObjectType == ModelSchema.Column); foreach (var column in columns) { var datatypes = column.GetReferenced(Column.DataType).Where(t => { var name = t.Name?.Parts.LastOrDefault()?.ToLower(); return(name == "real" || name == "float"); }); if (datatypes.Any()) { problems.Add(new SqlRuleProblem(Message, column)); } } } 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) { List <SqlRuleProblem> problems = new List <SqlRuleProblem>(); TSqlObject sqlObj = ruleExecutionContext.ModelElement; if (sqlObj == null || sqlObj.IsWhiteListed()) { return(problems); } var fragment = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateProcedureStatement)); var name = sqlObj.Name.GetName(); var transactionVisitor = new TransactionVisitor(); var actionStatementVisitor = new ActionStatementVisitor() { TypeFilter = ObjectTypeFilter.PermanentOnly }; fragment.Accept(actionStatementVisitor); if (actionStatementVisitor.Count <= 1) { return(problems); } fragment.Accept(transactionVisitor); if (transactionVisitor.Count == 0) { problems.Add(new SqlRuleProblem(Message, sqlObj)); return(problems); } //eliminate rollbacks, and ensure all the action statements are wrapped inside the begin tran...commit tran var transactionStatements = transactionVisitor.Statements .Where(st => st.GetType() == typeof(BeginTransactionStatement) || st.GetType() == typeof(CommitTransactionStatement)); var possibleOffenders = new List <DataModificationStatement>(actionStatementVisitor.Statements); for (int i = 0; i < transactionStatements.Count(); i += 2) { var beginTranLine = transactionStatements.ElementAt(i).StartLine; var commitTranLine = transactionStatements.ElementAt(i + 1).StartLine; possibleOffenders.RemoveAll(st => st.StartLine > beginTranLine && st.StartLine < commitTranLine); } problems.AddRange(possibleOffenders.Select(po => new SqlRuleProblem(Message, sqlObj, po))); 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) { List <SqlRuleProblem> problems = new List <SqlRuleProblem>(); TSqlObject sqlObj = ruleExecutionContext.ModelElement; if (sqlObj == null || sqlObj.IsWhiteListed()) { return(problems); } var objName = sqlObj.Name.GetName(); var columns = sqlObj.GetReferenced(DacQueryScopes.All).Where(x => x.ObjectType == Column.TypeClass).ToList(); if (!columns.Any()) { return(problems); } var keyColumn = columns.FirstOrDefault(); var dataType = keyColumn.GetReferenced(Column.DataType, DacQueryScopes.All).FirstOrDefault(); if (dataType == null || dataType.Name == null) { return(problems); } var dataTypeName = dataType.Name.Parts.Last(); if (_comparer.Equals(dataTypeName, "uniqueidentifier")) { problems.Add(new SqlRuleProblem(GuidMessage, sqlObj)); } if (columns.Any(col => { var len = col.GetProperty <int>(Column.Length); dataTypeName = col.GetReferenced(Column.DataType).First().Name.Parts.Last(); return((_comparer.Equals(dataTypeName, "varchar") && len > 50) || (_comparer.Equals(dataTypeName, "nvarchar") && len > 100)); })) { problems.Add(new SqlRuleProblem(WideVarcharMessage, sqlObj)); } 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) { List <SqlRuleProblem> problems = new List <SqlRuleProblem>(); TSqlObject sqlObj = ruleExecutionContext.ModelElement; if (sqlObj == null || sqlObj.IsWhiteListed()) { return(problems); } var fragment = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateProcedureStatement)); var name = sqlObj.Name.GetName(); var tryCatchVisitor = new TryCatchVisitor(); var actionStatementVisitor = new ActionStatementVisitor(); // not going to ignore temps for this rule as they should be wrapped in a try fragment.Accept(actionStatementVisitor); if (actionStatementVisitor.Count <= 1) { return(problems); } fragment.Accept(tryCatchVisitor); if (tryCatchVisitor.Count == 0) { problems.Add(new SqlRuleProblem(Message, sqlObj)); return(problems); } var possibleOffenders = new List <DataModificationStatement>(actionStatementVisitor.Statements); foreach (var statement in tryCatchVisitor.Statements) { var startLine = statement.StartLine; var endline = statement.CatchStatements.StartLine; possibleOffenders.RemoveAll(st => st.StartLine > startLine && st.StartLine < endline); } problems.AddRange(possibleOffenders.Select(po => new SqlRuleProblem(Message, sqlObj, po))); 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) { List <SqlRuleProblem> problems = new List <SqlRuleProblem>(); TSqlObject sqlObj = ruleExecutionContext.ModelElement; if (sqlObj == null || sqlObj.IsWhiteListed()) { return(problems); } var fragment = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateTableStatement)); var objName = sqlObj.Name.GetName(); var dbCollation = ruleExecutionContext.SchemaModel.CopyModelOptions().Collation; ColumnDefinitionVisitor columnVisitor = new ColumnDefinitionVisitor(); fragment.Accept(columnVisitor); var statements = columnVisitor.NotIgnoredStatements(RuleId).ToList(); var columnOffenders = statements.Where(col => (col.Collation != null && !_comparer.Equals(col.Collation?.Value, dbCollation)) ).ToList(); problems.AddRange(columnOffenders.Select(col => new SqlRuleProblem(MessageColumn, sqlObj, col))); var defaultOffenders = statements.Where(col => { var collation = (col.DefaultConstraint?.Expression as PrimaryExpression)?.Collation; return(collation != null && !_comparer.Equals(collation.Value, dbCollation)); }).ToList(); problems.AddRange(defaultOffenders.Select(col => new SqlRuleProblem(MessageDefault, sqlObj, col.DefaultConstraint))); return(problems); }