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 RaiseErrorVisitor();
fragment.Accept(visitor);
var offenders =
from r in visitor.Statements
where
r.SecondParameter is IntegerLiteral &&
int.Parse((r.SecondParameter as Literal)?.Value) > 18 &&
(r.RaiseErrorOptions & RaiseErrorOptions.Log) != RaiseErrorOptions.Log
select r;
problems.AddRange(offenders.Select(o => new SqlRuleProblem(Message, sqlObj, o)));
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 sqlObjName = ruleExecutionContext.GetObjectName(sqlObj);
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(ProgrammingAndViewSchemaTypes);
var whereClauseVisitor = new WhereClauseVisitor();
fragment.Accept(whereClauseVisitor);
foreach (var whereClause in whereClauseVisitor.Statements)
{
var inPredicateVisitor = new InPredicateVisitor();
whereClause.Accept(inPredicateVisitor);
var offenders = inPredicateVisitor.NotIgnoredStatements(RuleId).Where(i => i.Subquery != null);
problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t)));
}
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);
// only visit the operators in the where clause
var visitor = new WhereClauseVisitor();
fragment.Accept(visitor);
foreach (var clause in visitor.Statements)
{
var beVisitor = new BooleanParenthesesExpressionVisitor();
clause.Accept(beVisitor);
var offenders = (from BooleanParenthesisExpression be in beVisitor.NotIgnoredStatements(RuleId)
where TestClause(be)
select be).ToList();
problems.AddRange(offenders.Select(o => new SqlRuleProblem(Message, sqlObj, o)));
}
return(problems);
}
private void AnalyzeJoins(IList <SqlRuleProblem> problems, SqlRuleExecutionContext ruleExecutionContext,
QuerySpecification querySpecification)
{
var visitor = new CollectJoinVisitor();
querySpecification.Accept(visitor);
foreach (var join in visitor.Joins)
{
// We should have already handled the first table reference here, as it's included from an earlier part
// of the query. As such, we now handle the second table only.
var tablePartitions = CollectFromTableReference(ruleExecutionContext, join.SecondTableReference,
join.SecondTableReference).ToList();
if (tablePartitions.Count == 0)
{
continue;
}
if (join is QualifiedJoin qualifiedJoin)
{
var condition = qualifiedJoin.SearchCondition;
SearchConstraints(condition, tablePartitions);
}
foreach (var partitionedColumn in tablePartitions)
{
SqlRuleProblem problem = CreateProblem(ruleExecutionContext, partitionedColumn);
problems.Add(problem);
}
}
}
private IEnumerable <PartitionedColumn> CollectFromTableReference(SqlRuleExecutionContext ruleExecutionContext,
TSqlFragment fragment, TableReference tableReference)
{
if (!(tableReference is NamedTableReference namedTableReference))
{
return(new List <PartitionedColumn>());
}
// Resolve the table from its name.
var table =
ResolveTable(ruleExecutionContext.SchemaModel, namedTableReference.SchemaObject);
if (table == null)
{
return(new List <PartitionedColumn>());
}
// In the tabe object, find all columns that are a partition column, and save them.
var partitionColumns = table.GetReferenced(Table.PartitionColumn, DacQueryScopes.All);
var allowedTableNames = new HashSet <string>();
allowedTableNames.Add(namedTableReference.SchemaObject.BaseIdentifier.Value);
if (namedTableReference.Alias != null)
{
allowedTableNames.Add(namedTableReference.Alias.Value);
}
return(partitionColumns
.Select(x => new PartitionedColumn(
allowedTableNames,
x.Name.Parts.Last(), fragment)));
}
/// <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);
if (fragment.ScriptTokenStream == null)
{
return(problems);
}
var visitor = new DeclareVariableElementVisitor();
fragment.Accept(visitor);
var vars = from pp in visitor.Statements
join t in fragment.ScriptTokenStream
on new { Name = pp.VariableName.Value?.ToLower(), Type = TSqlTokenType.Variable }
equals new { Name = t.Text?.ToLower(), Type = t.TokenType }
select pp;
var unusedVars = vars.GroupBy(p => p.VariableName.Value?.ToLower()).Where(g => g.Count() == 1).Select(g => g.First());
problems.AddRange(unusedVars.Select(v => new SqlRuleProblem(string.Format(Message, v.VariableName.Value), sqlObj, v)));
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(CreateProcedureStatement),
typeof(CreateTriggerStatement));
var visitor = new PredicateVisitor();
fragment.Accept(visitor);
var predicates = from o
in visitor.Statements
where o.Options == SetOptions.NoCount && o.IsOn
select o;
var createToken = fragment.ScriptTokenStream.FirstOrDefault(t => t.TokenType == TSqlTokenType.Create);
if (!predicates.Any() && Ignorables.ShouldNotIgnoreRule(fragment.ScriptTokenStream, RuleId, createToken.Line))
{
problems.Add(new SqlRuleProblem(Message, sqlObj));
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext context)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject index = context.ModelElement;
if (!IsIndexOnMemoryOptimizedTable(index))
{
// Only examining memory optimized tables, anything else can be ignored
// Note the call to the Table.MemoryOptimized property: this is where we verify the in-memory behavior
return(problems);
}
TSqlObject databaseOptions = context.SchemaModel
.GetObjects(DacQueryScopes.UserDefined, DatabaseOptions.TypeClass).SingleOrDefault();
if (databaseOptions == null)
{
// This should never happen as the database options are automatically created
return(problems);
}
string defaultCollation = databaseOptions.GetProperty <string>(DatabaseOptions.Collation);
AnalyzeColumns(context, index, defaultCollation, problems);
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 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)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject modelElement = ruleExecutionContext.ModelElement;
if (IsInlineTableValuedFunction(modelElement))
{
return(problems);
}
string elementName = GetElementName(ruleExecutionContext, modelElement);
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
WaitForDelayVisitor visitor = new WaitForDelayVisitor();
fragment.Accept(visitor);
IList <WaitForStatement> waitForDelayStatements = visitor.WaitForDelayStatements;
foreach (WaitForStatement waitForStatement in waitForDelayStatements)
{
var problem = new SqlRuleProblem(string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement, waitForStatement);
problems.Add(problem);
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var procedure = ruleExecutionContext.ModelElement;
var script = procedure.GetScript();
var parser = new TSql120Parser(true);
IList <ParseError> errors = new List <ParseError>();
var fragment = parser.Parse(new StringReader(script), out errors);
var visitor = new TransactionVisitor();
fragment.Accept(visitor);
var dif = visitor.GetDifference();
if (dif.Any())
{
return
new List <SqlRuleProblem>()
{
new SqlRuleProblem("No balanced transaction", procedure)
}
}
;
return(new List <SqlRuleProblem>());
}
}
/// <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 whereClauseVisitor = new WhereClauseVisitor();
fragment.Accept(whereClauseVisitor);
foreach (var whereClause in whereClauseVisitor.Statements)
{
var functionVisitor = new FunctionCallVisitor();
whereClause.Accept(functionVisitor);
var offenders = from FunctionCall f in functionVisitor.NotIgnoredStatements(RuleId)
where CheckFunction(f)
select f;
problems.AddRange(offenders.Select(f => new SqlRuleProblem(Message, sqlObj, f)));
}
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(ProgrammingSchemaTypes);
var openCursorVisitor = new OpenCursorVisitor();
fragment.Accept(openCursorVisitor);
if (openCursorVisitor.Count > 0)
{
var deallocateCursorVisitor = new DeallocateCursorVisitor();
fragment.Accept(deallocateCursorVisitor);
var localOpenCursors = openCursorVisitor.Statements.Where(c => !c.Cursor.IsGlobal);
var localDeallocateCursors = deallocateCursorVisitor.Statements.Where(c => !c.Cursor.IsGlobal);
var unDeallocatedCursors = localOpenCursors.Where(c =>
!localDeallocateCursors.Any(c2 => _comparer.Equals(c.Cursor.Name.Value, c2.Cursor.Name.Value)));
foreach (var cursor in unDeallocatedCursors)
{
problems.Add(new SqlRuleProblem(Message, sqlObj, cursor));
}
}
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));
var typesVisitor = new TypesVisitor(
typeof(SelectStatement),
typeof(UpdateStatement),
typeof(DeleteStatement),
typeof(InsertStatement),
typeof(CreateTableStatement)
);
fragment.Accept(typesVisitor);
if (typesVisitor.Count == 0)
{
return(problems);
}
var offenders = typesVisitor.Statements
.SkipWhile(t => t.GetType() == typeof(CreateTableStatement))
.Where(t1 => t1.GetType() == typeof(CreateTableStatement));
problems.AddRange(offenders.Select(t => new SqlRuleProblem(Message, sqlObj, t)));
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(ProgrammingSchemaTypes);
var visitor = new OrderByVisitor();
fragment.Accept(visitor);
var offenders = (from o in visitor.NotIgnoredStatements(RuleId)
from e in o.OrderByElements
where e.Expression is IntegerLiteral
select e).Distinct();
foreach (var offender in offenders)
{
problems.Add(new SqlRuleProblem(Message, sqlObj, offender));
}
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(ProgrammingSchemaTypes);
// only visit the function calls inside the where clauses
var visitor = new WhereClauseVisitor();
fragment.Accept(visitor);
foreach (var clause in visitor.Statements)
{
var functionVisitor = new FunctionCallVisitor("charindex");
clause.Accept(functionVisitor);
problems.AddRange(functionVisitor.NotIgnoredStatements(RuleId).Select(f => new SqlRuleProblem(Message, sqlObj, f)));
}
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));
var objName = sqlObj.Name.GetName();
var grantVisitor = new GrantVisitor();
fragment.Accept(grantVisitor);
if (grantVisitor.Statements
.Any(g => g.SecurityTargetObject.ObjectName.MultiPartIdentifier.CompareTo(sqlObj.Name) >= 5))
{
problems.Add(new SqlRuleProblem(Message, 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)
{
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);
}
private static void AnalyzeColumns(
SqlRuleExecutionContext context,
TSqlObject index,
string defaultCollation,
IList <SqlRuleProblem> problems)
{
foreach (TSqlObject column in index.GetReferenced(Index.Columns)
.Where(column => IsCharacterColumn(column)))
{
// Fall back on the default project collation if none is defined for the specific column
string collation = column.GetProperty <string>(Column.Collation) ?? defaultCollation ?? string.Empty;
if (!collation.EndsWith(Bin2Ending, StringComparison.OrdinalIgnoreCase))
{
// Error looks liks "Index <name> on column <name> should have a BIN2 collation instead of <collation>"
// Choosing to add 1 problem per-column. This will cause more warnings in the error manager but is more precise
string errorMsg = string.Format(CultureInfo.CurrentCulture,
context.RuleDescriptor.DisplayDescription,
RuleUtils.GetElementName(index, context, ElementNameStyle.EscapedFullyQualifiedName),
RuleUtils.GetElementName(column, context, ElementNameStyle.EscapedFullyQualifiedName),
collation);
SqlRuleProblem problem = new SqlRuleProblem(errorMsg, index);
problems.Add(problem);
}
}
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
var modelElement = ruleExecutionContext.ModelElement;
var displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
var objectName = displayServices.GetElementName(ruleExecutionContext.ModelElement,
ElementNameStyle.FullyQualifiedName);
var fragment = ruleExecutionContext.ScriptFragment;
var visitor = new ParameterVisitor();
fragment.Accept(visitor);
foreach (var element in visitor.DeclareVariableElements)
{
var problemDescription = string.Format(Message, element.VariableName.Value, objectName);
var problem = new SqlRuleProblem(problemDescription, modelElement, element);
problems.Add(problem);
}
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 topVisitor = new TopRowFilterVisitor();
fragment.Accept(topVisitor);
if (topVisitor.Count > 0)
{
var orderByvisitor = new OrderByVisitor();
fragment.Accept(orderByvisitor);
if (orderByvisitor.Count < 1)
{
problems.Add(new SqlRuleProblem(Message, sqlObj, topVisitor.Statements[0]));
}
}
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)
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateProcedureStatement));
IfStatementVisitor ifVisitor = new IfStatementVisitor();
fragment.Accept(ifVisitor);
if (!ifVisitor.Statements.Any())
{
return(problems);
}
foreach (var ifStatement in ifVisitor.NotIgnoredStatements(RuleId))
{
var tableVisitor = new NamedTableReferenceVisitor()
{
TypeFilter = ObjectTypeFilter.PermanentOnly
};
ifStatement.ThenStatement?.Accept(tableVisitor);
ifStatement.ElseStatement?.Accept(tableVisitor);
problems.AddRange(tableVisitor.Statements.Select(s => new SqlRuleProblem(Message, sqlObj, s)));
}
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)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement;
if (sqlObj == null || sqlObj.IsWhiteListed())
{
return(problems);
}
var fkinfo = sqlObj.GetFKInfo();
var table = ruleExecutionContext.SchemaModel.GetObject(Table.TypeClass, fkinfo.TableName, DacQueryScopes.All);
if (!table.CheckForFkIndex(fkinfo.ColumnNames))
{
problems.Add(new SqlRuleProblem(Message, sqlObj));
return(problems);
}
table = ruleExecutionContext.SchemaModel.GetObject(Table.TypeClass, fkinfo.ToTableName, DacQueryScopes.All);
if (!table.CheckForFkIndex(fkinfo.ToColumnNames))
{
problems.Add(new SqlRuleProblem(Message, sqlObj));
return(problems);
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var table = ruleExecutionContext.ModelElement;
var permissions = table.GetReferencing(Permission.SecuredObject);
var readerRole = new ObjectIdentifier("ReaderRole");
var comparer = ruleExecutionContext.SchemaModel.CollationComparer;
foreach (var permission in permissions)
{
var permissionType = (PermissionType)permission.GetProperty(Permission.PermissionType);
var permissionAction = (PermissionAction)permission.GetProperty(Permission.PermissionAction);
var grantees = permission.GetReferenced(Permission.Grantee).Select(grantee => grantee.Name);
if (grantees.Contains(readerRole, comparer) && permissionAction == PermissionAction.Grant && permissionType == PermissionType.Select)
{
return(new List <SqlRuleProblem>());
}
}
return(new List <SqlRuleProblem>()
{
new SqlRuleProblem("Table don't have permission for [ReaderRole]", table)
});
}
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);
}
/// <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 ifVisitor = new IfStatementVisitor();
fragment.Accept(ifVisitor);
if (ifVisitor.Count == 0)
{
return(problems);
}
foreach (var ifstmt in ifVisitor.Statements)
{
var functionVisitor = new FunctionCallVisitor("count");
ifstmt.Predicate.Accept(functionVisitor);
if (functionVisitor.Statements.Any() && CheckIf(ifstmt))
{
problems.Add(new SqlRuleProblem(Message, sqlObj, ifstmt));
}
}
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)
{
return(problems);
}
var fragment = ruleExecutionContext.ScriptFragment.GetFragment(typeof(CreateProcedureStatement));
var createTableVisitor = new CreateTableVisitor();
fragment.Accept(createTableVisitor);
var statements = createTableVisitor
.Statements
.Where(p => p.SchemaObjectName
.Identifiers
.Any(a => a.Value.StartsWith("#", System.StringComparison.InvariantCultureIgnoreCase)));
foreach (var statement in statements)
{
var noCollationColumns = statement.Definition.ColumnDefinitions.Where(p => p.Collation == null &&
(((SqlDataTypeReference)p.DataType).SqlDataTypeOption == SqlDataTypeOption.VarChar ||
((SqlDataTypeReference)p.DataType).SqlDataTypeOption == SqlDataTypeOption.Char ||
((SqlDataTypeReference)p.DataType).SqlDataTypeOption == SqlDataTypeOption.NVarChar ||
((SqlDataTypeReference)p.DataType).SqlDataTypeOption == SqlDataTypeOption.NChar));
problems.AddRange(noCollationColumns.Select(s => new SqlRuleProblem(Message, sqlObj, s)));
}
return(problems);
}
private void AnalyzeQueries(IList <SqlRuleProblem> problems, TSqlFragment fragment,
SqlRuleExecutionContext ruleExecutionContext)
{
// Find all queries in current checking fragment.
var visitor = new GetQuerySpecificationsVisitor();
fragment.Accept(visitor);
foreach (var querySpecification in visitor.Queries)
{
// Get the specified tables in the query.
var unspecifiedPartitions = CollectFromClause(ruleExecutionContext, querySpecification);
// If the query isn't querying a partition table, we don't need to check it.
if (unspecifiedPartitions.Count > 0)
{
// Get all constraints in the query.
if (querySpecification.WhereClause != null)
{
SearchConstraints(querySpecification.WhereClause, unspecifiedPartitions);
}
}
// If there are any unspecified partitions left, these are considered problems.
foreach (var unspecifiedPartition in unspecifiedPartitions)
{
SqlRuleProblem problem = CreateProblem(ruleExecutionContext, unspecifiedPartition);
problems.Add(problem);
}
AnalyzeJoins(problems, ruleExecutionContext, querySpecification);
}
}
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 whereClauseVisitor = new WhereClauseVisitor();
fragment.Accept(whereClauseVisitor);
foreach (var whereClause in whereClauseVisitor.Statements)
{
var binaryExpressionVisitor = new BinaryExpressionVisitor();
whereClause.Accept(binaryExpressionVisitor);
foreach (var comparison in binaryExpressionVisitor.NotIgnoredStatements(RuleId))
{
if (CheckBinaryExpression(comparison).GetValueOrDefault(false))
{
problems.Add(new SqlRuleProblem(Message, sqlObj, comparison));
}
}
}
return(problems);
}
/// <summary>
/// Gets a formatted element name
/// </summary>
public static string GetElementName(TSqlObject modelElement, SqlRuleExecutionContext ruleExecutionContext, ElementNameStyle style)
{
// Get the element name using the built in DisplayServices. This provides a number of useful formatting options to
// make a name user-readable
var displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
string elementName = displayServices.GetElementName(modelElement, style);
return elementName;
}
private static string GetElementName(SqlRuleExecutionContext ruleExecutionContext, TSqlObject modelElement)
{
// Get the element name using the built in DisplayServices. This provides a number of
// useful formatting options to
// make a name user-readable
var displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
string elementName = displayServices.GetElementName(modelElement, ElementNameStyle.EscapedFullyQualifiedName);
return elementName;
}
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var tableElement = ruleExecutionContext.ModelElement;
var tableName = GetElementName(ruleExecutionContext, tableElement);
//Ensure that it's effectively an info table
if (IsAnchor(tableElement))
return OnAnalyze(tableName, tableElement).ToList();
return new List<SqlRuleProblem>();
}
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext context)
{
List<SqlRuleProblem> problems = new List<SqlRuleProblem>();
_model = context.SchemaModel;
// Query all top level user-defined objects in the model. This restricts scope to objects actually defined
// in this model, rather than same database references, built in types, or system references
foreach (TSqlObject tSqlObject in _model.GetObjects(DacQueryScopes.UserDefined))
{
AnalyzeObject(tSqlObject, problems);
}
return problems;
}
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext context)
{
IList<SqlRuleProblem> problems = new List<SqlRuleProblem>();
TSqlObject table = context.ModelElement;
if (table.GetProperty<bool>(Table.MemoryOptimized))
{
// In this case we look up "Referencing" relationships. This is a way to iterate
// over the objects that reference the current object. Note how the actual relationship
// that we care about is defined on the View class rather than on the table.
foreach (TSqlObject view in table.GetReferencing(View.BodyDependencies))
{
ValidateViewHasSchemaBinding(context, view, table, problems);
ValidateViewHasNoIndexes(context, view, table, problems);
}
}
return problems;
}
/// <summary>
/// Check if the table has any datetime2 column, and then check if they have a scale of 7 or more.
/// </summary>
/// <param name="ruleExecutionContext"></param>
/// <returns></returns>
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
List<SqlRuleProblem> problems = new List<SqlRuleProblem>();
TSqlObject table = ruleExecutionContext.ModelElement;
if (table != null)
{
foreach (var column in table.GetReferenced(Table.Columns))
{
if (IsDateTime2WithExcessiveScale(column))
{
//DisplayServices is a useful helper service for formatting names
DisplayServices displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
string formattedName = displayServices.GetElementName(column, ElementNameStyle.FullyQualifiedName);
string problemDescription = string.Format(DateTime2ColumnWithExcessiveScaleMsgFormat,
formattedName);
SqlRuleProblem problem = new SqlRuleProblem(problemDescription, table);
problems.Add(problem);
}
}
}
return problems;
}
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List<SqlRuleProblem>();
var tableElement = ruleExecutionContext.ModelElement;
var tableName = GetElementName(ruleExecutionContext, tableElement);
//Ensure that it's effectively a dimension table
if (tableElement.Name.HasName && tableElement.Name.Parts.Last().StartsWith("Dim", StringComparison.OrdinalIgnoreCase))
{
//Get the columns of the table
var columns = tableElement.GetReferenced(Table.Columns);
if (columns.Count() == 0)
problems.Add(new SqlRuleProblem(string.Format("The dimension table {0} has no column", tableName), tableElement));
//Ensure that one of them is effecively an identity
var identityColumn = columns.FirstOrDefault(c => c.GetProperty<bool>(Column.IsIdentity));
if (identityColumn == null)
problems.Add(new SqlRuleProblem(string.Format("No identity column for the dimension table {0}", tableName), tableElement));
}
return problems;
}
/// <summary>
/// Analysis is quite simple - the table's name is examined and if it ends with "View" then a problem
/// is created
/// </summary>
/// <param name="ruleExecutionContext"></param>
/// <returns></returns>
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
List<SqlRuleProblem> problems = new List<SqlRuleProblem>();
TSqlObject table = ruleExecutionContext.ModelElement;
if (table != null)
{
if (NameEndsInView(table.Name))
{
// DisplayServices is a useful helper service for formatting names
DisplayServices displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
string formattedName = displayServices.GetElementName(table, ElementNameStyle.FullyQualifiedName);
string problemDescription = string.Format(NameEndingInViewMsgFormat,
formattedName);
SqlRuleProblem problem = new SqlRuleProblem(problemDescription, table);
problems.Add(problem);
}
}
return problems;
}
/// <summary>
/// No Indexes of any kind are allowed on Views that reference a memory optimized table.
/// </summary>
private void ValidateViewHasNoIndexes(SqlRuleExecutionContext context, TSqlObject view, TSqlObject table, IList<SqlRuleProblem> problems)
{
foreach (TSqlObject index in view.GetReferencing(Index.IndexedObject))
{
string description = string.Format(CultureInfo.CurrentCulture,
RuleResources.ViewsOnMemoryOptimizedTable_IndexProblemDescription,
RuleUtils.GetElementName(context, index),
RuleUtils.GetElementName(context, view),
RuleUtils.GetElementName(context, table));
TSqlFragment nameFragment = TsqlScriptDomUtils.LookupSchemaObjectName(index);
// Note that nameFragment can be null - in this case the index's position information will be used.
// This is just a little less precise than pointing to the position of the name for that index
problems.Add(new SqlRuleProblem(description, index, nameFragment));
}
}
private static void AnalyzeColumns(
SqlRuleExecutionContext context,
TSqlObject index,
string defaultCollation,
IList<SqlRuleProblem> problems)
{
foreach (TSqlObject column in index.GetReferenced(Index.Columns)
.Where(column => IsCharacterColumn(column)))
{
// Fall back on the default project collation if none is defined for the specific column
string collation = column.GetProperty<string>(Column.Collation) ?? defaultCollation ?? string.Empty;
if (!collation.EndsWith(Bin2Ending, StringComparison.OrdinalIgnoreCase))
{
// Error looks liks "Index <name> on column <name> should have a BIN2 collation instead of <collation>"
// Choosing to add 1 problem per-column. This will cause more warnings in the error manager but is more precise
string errorMsg = string.Format(CultureInfo.CurrentCulture,
context.RuleDescriptor.DisplayDescription,
RuleUtils.GetElementName(index, context, ElementNameStyle.EscapedFullyQualifiedName),
RuleUtils.GetElementName(column, context, ElementNameStyle.EscapedFullyQualifiedName),
collation);
SqlRuleProblem problem = new SqlRuleProblem(errorMsg, index);
problems.Add(problem);
}
}
}
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext context)
{
IList<SqlRuleProblem> problems = new List<SqlRuleProblem>();
TSqlObject index = context.ModelElement;
if (!IsIndexOnMemoryOptimizedTable(index))
{
// Only examining memory optimized tables, anything else can be ignored
// Note the call to the Table.MemoryOptimized property: this is where we verify the in-memory behavior
return problems;
}
TSqlObject databaseOptions = context.SchemaModel
.GetObjects(DacQueryScopes.UserDefined, DatabaseOptions.TypeClass).SingleOrDefault();
if (databaseOptions == null)
{
// This should never happen as the database options are automatically created
return problems;
}
string defaultCollation = databaseOptions.GetProperty<string>(DatabaseOptions.Collation);
AnalyzeColumns(context, index, defaultCollation, problems);
return problems;
}
/// <summary>
/// For element-scoped rules the Analyze method is executed once for every matching object in the model.
/// </summary>
/// <param name="ruleExecutionContext">The context object contains the TSqlObject being analyzed, a TSqlFragment
/// that's the AST representation of the object, the current rule's descriptor, and a reference to the model being
/// analyzed.
/// </param>
/// <returns>A list of problems should be returned. These will be displayed in the Visual Studio error list</returns>
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList<SqlRuleProblem> problems = new List<SqlRuleProblem>();
TSqlObject modelElement = ruleExecutionContext.ModelElement;
// this rule does not apply to inline table-valued function
// we simply do not return any problem in that case.
if (IsInlineTableValuedFunction(modelElement))
{
return problems;
}
string elementName = RuleUtils.GetElementName(ruleExecutionContext, modelElement);
// The rule execution context has all the objects we'll need, including the fragment representing the object,
// and a descriptor that lets us access rule metadata
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
// To process the fragment and identify WAITFOR DELAY statements we will use a visitor
//
WaitForDelayVisitor visitor = new WaitForDelayVisitor();
fragment.Accept(visitor);
IList<WaitForStatement> waitforDelayStatements = visitor.WaitForDelayStatements;
// Create problems for each WAITFOR DELAY statement found
foreach (WaitForStatement waitForStatement in waitforDelayStatements)
{
// When creating a rule problem, always include the TSqlObject being analyzed. This is used to determine
// the name of the source this problem was found in and a best guess as to the line/column the problem was found at
//
// In addition if you have a specific TSqlFragment that is related to the problem also include this
// since the most accurate source position information (start line and column) will be read from the fragment
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName),
modelElement,
waitForStatement);
problems.Add(problem);
}
return problems;
}
/// <summary>
/// Gets a formatted element name with the default style <see cref="ElementNameStyle.EscapedFullyQualifiedName"/>
/// </summary>
public static string GetElementName(SqlRuleExecutionContext ruleExecutionContext, TSqlObject modelElement)
{
return GetElementName(modelElement, ruleExecutionContext, ElementNameStyle.EscapedFullyQualifiedName);
}
/// <summary>
// Views must be schema bound if they reference a memory optimized table
/// </summary>
private static void ValidateViewHasSchemaBinding(SqlRuleExecutionContext context, TSqlObject view, TSqlObject table,
IList<SqlRuleProblem> problems)
{
if (!view.GetProperty<bool>(View.WithSchemaBinding))
{
string description = string.Format(CultureInfo.CurrentCulture,
RuleResources.ViewsOnMemoryOptimizedTable_SchemaBindingProblemDescription,
RuleUtils.GetElementName(context, view),
RuleUtils.GetElementName(context, table));
TSqlFragment nameFragment = TsqlScriptDomUtils.LookupSchemaObjectName(view);
problems.Add(new SqlRuleProblem(description, view, nameFragment));
}
}
public override IList<SqlRuleProblem> Analyze(SqlRuleExecutionContext context)
{
TSQLSmellWorker Worker = new TSQLSmellWorker(context, RuleId);
return (Worker.Analyze());
}
public TSQLSmellWorker(SqlRuleExecutionContext context,string ruleID)
{
_model = context.SchemaModel;
_ruleID = ruleID;
}
