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