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);
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var problems = new List <SqlRuleProblem>();
var visitor = new DataTypesVisitor();
fragment.Accept(visitor);
foreach (var columnDef in visitor.ColumnDefinitions)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement)
{
Severity = SqlRuleProblemSeverity.Error
};
problem.SetSourceInformation(new SourceInformation(problem.SourceName, columnDef.StartLine, columnDef.StartColumn));
problems.Add(problem);
}
foreach (var parameter in visitor.ProcedureParameters)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement)
{
Severity = SqlRuleProblemSeverity.Error
};
problem.SetSourceInformation(new SourceInformation(problem.SourceName, parameter.StartLine, parameter.StartColumn));
problems.Add(problem);
}
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);
}
}
}
/// <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(XtendSqlRuleExecutionContext context)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
NullBooleanComparisonVisitor visitor = new NullBooleanComparisonVisitor();
context.ScriptFragment.Accept(visitor);
foreach (var element in visitor.InvalidBooleanComparisons)
{
string description = element.ComparisonType == BooleanComparisonType.NotEqualToBrackets || element.ComparisonType == BooleanComparisonType.NotEqualToExclamation
? "use 'is not null' instead" : element.ComparisonType == BooleanComparisonType.Equals ? "use 'is null' instead" : element.ComparisonType.ToString();
string statement = string.Join("", element.ScriptTokenStream
.Skip(element.FirstTokenIndex)
.Take(element.LastTokenIndex - element.FirstTokenIndex + 1)
.Select(x => x.Text).ToArray());
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
context.RuleDescriptor.DisplayDescription,
statement, description),
context.ModelElement,
element.SecondExpression);
problems.Add(problem);
}
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);
}
}
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var problems = new List <SqlRuleProblem>();
var visitor = new InvalidTypesVisitor();
fragment.Accept(visitor);
foreach (var item in visitor.ColumnDefinitions)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement, item)
{
Severity = SqlRuleProblemSeverity.Warning
};
problems.Add(problem);
}
foreach (var item in visitor.ProcedureParameters)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement, item)
{
Severity = SqlRuleProblemSeverity.Warning
};
problems.Add(problem);
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObject = ruleExecutionContext.ModelElement;
var sqlObjectSourceInfo = sqlObject.GetSourceInformation();
if (ruleExecutionContext.ScriptFragment.ScriptTokenStream != null)
{
foreach (var token in ruleExecutionContext.ScriptFragment.ScriptTokenStream)
{
if (IsKeyword(token) && token.Text?.ToUpper() != token.Text)
{
var problem = new SqlRuleProblem(string.Format(Message, token.Text), sqlObject);
problem.SetSourceInformation(new SourceInformation(sqlObjectSourceInfo.SourceName,
token.Line,
token.Column));
problems.Add(problem);
}
}
}
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(XtendSqlRuleExecutionContext context)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
// Use a visitor to see if the procedure has a nocount set
SetConcatNullYieldsNullVisitor visitor = new SetConcatNullYieldsNullVisitor();
context.ScriptFragment.Accept(visitor);
if (visitor.SetConcatNullYieldsNullEnabled)
{
foreach (PredicateSetStatement element in visitor.SetCalls)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
context.RuleDescriptor.DisplayDescription,
context.ElementName),
context.ModelElement,
element);
problems.Add(problem);
}
}
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(XtendSqlRuleExecutionContext context)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
CursorVisitor visitor = new CursorVisitor(false, true);
context.ScriptFragment.Accept(visitor);
foreach (var element in visitor.IncorrectFetchCursorStatements)
{
var fetch = element.Key;
int fetchCount = fetch.IntoVariables.Count;
string cursorName = element.Value;
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
context.RuleDescriptor.DisplayDescription,
cursorName,
fetchCount),
context.ModelElement,
fetch);
problems.Add(problem);
}
return(problems);
}
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);
}
private static SqlRuleProblem AddProblem(TSqlObject modelElement, RuleDescriptor ruleDescriptor, TSqlFragment fragment)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement, fragment)
{
Severity = SqlRuleProblemSeverity.Warning
};
return(problem);
}
public TestSqlRuleProblem(SqlRuleProblem sqlRuleProblem)
{
RuleId = sqlRuleProblem.RuleId;
Description = sqlRuleProblem.Description;
ErrorMessageString = sqlRuleProblem.ErrorMessageString;
Severity = sqlRuleProblem.Severity.ToString();
SourceName = sqlRuleProblem.SourceName;
StartColumn = sqlRuleProblem.StartColumn;
StartLine = sqlRuleProblem.StartLine;
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var objectName = ruleExecutionContext.ModelElement;
var objectRealName = GetElementName(ruleExecutionContext, objectName);
int IsCorrect = 0;
if (objectName != null)
{
foreach (var child in objectName.GetReferencedRelationshipInstances(Table.Columns))
{
var type = child.Object.GetReferenced(Column.DataType).FirstOrDefault();
//var propertyValue = child.GetReferencing().Where(x => x.ObjectType.Name == "ExtendedProperty").First().GetProperty(ExtendedProperty.Value);
//child.Object.GetReferenced();
var isNullable = type.GetProperty <bool?>(DataType.UddtNullable);
var length = type.GetProperty <int?>(DataType.UddtLength);
problems.Add(new SqlRuleProblem(string.Format("The dimension table {0} has no column", length), objectName));
//do something useful with this information!
}
if (!IsDWHTimeStamp(objectName.Name))
{
IsCorrect++;
}
}
if (IsCorrect > 0)
{
var displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
var formattedName = displayServices.GetElementName(objectName, ElementNameStyle.FullyQualifiedName);
var problemDescription = string.Format(Message, formattedName);
var problem = new SqlRuleProblem(problemDescription, objectName);
problems.Add(problem);
}
//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));
return(problems);
}
protected virtual IList <SqlRuleProblem> CreateProblems(RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement, IEnumerable <TSqlFragment> invalidSqlFragments, SqlRuleProblemSeverity severity)
{
var problems = new List <SqlRuleProblem>();
foreach (var invalidFragment in invalidSqlFragments)
{
var problem = new SqlRuleProblem(string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement, invalidFragment);
problem.Severity = severity;
problems.Add(problem);
}
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 (RuleUtils.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
// 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
foreach (WaitForStatement waitForStatement in waitforDelayStatements)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription, elementName),
modelElement,
waitForStatement);
problems.Add(problem);
}
return(problems);
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var problems = new List <SqlRuleProblem>();
var visitor = new OrderByVisitor();
fragment.Accept(visitor);
foreach (var orderByClause in visitor.InvalidOrderByClauseWithNumber)
{
var problem = new SqlRuleProblem(string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement, orderByClause);
problem.Severity = SqlRuleProblemSeverity.Error;
problems.Add(problem);
}
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;
string elementName = ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(modelElement, ElementNameStyle.EscapedFullyQualifiedName);
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
bool hasDescription = false;
// Check if it has columns. Workaround for tables from referenced projects showing up here.
// Not interested in those. They should be checked in their own project.
List <TSqlObject> columns = modelElement.GetReferenced(Table.Columns).ToList();
if (columns.Count > 0)
{
// Check if it has an extended property
List <TSqlObject> extendedProperties = modelElement.GetReferencing(ExtendedProperty.Host).ToList();
if (extendedProperties.Count > 0)
{
foreach (TSqlObject prop in extendedProperties)
{
if (ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(prop, ElementNameStyle.SimpleName) == "MS_Description")
{
hasDescription = true;
break;
}
}
}
if (!hasDescription)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription,
elementName),
modelElement);
problems.Add(problem);
}
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(modelElement, ElementNameStyle.EscapedFullyQualifiedName);
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
List <TSqlObject> columns = modelElement.GetReferenced(Table.Columns).ToList();
foreach (TSqlObject column in modelElement.GetReferenced(Table.Columns))
{
bool hasDescription = false;
// Check if it has an extended property
List <TSqlObject> extendedProperties = column.GetReferencing(ExtendedProperty.Host).ToList();
if (extendedProperties.Count > 0)
{
foreach (TSqlObject prop in extendedProperties)
{
if (ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(prop, ElementNameStyle.SimpleName) == "MS_Description")
{
hasDescription = true;
break;
}
}
}
if (!hasDescription)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription,
ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(column, ElementNameStyle.EscapedFullyQualifiedName)),
column);
problems.Add(problem);
}
}
return(problems);
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var problems = new List <SqlRuleProblem>();
var visitor = new DynamicSqlVisitor();
fragment.Accept(visitor);
foreach (var item in visitor.ExecuteStatements)
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement, item)
{
Severity = SqlRuleProblemSeverity.Warning
};
problems.Add(problem);
}
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(XtendSqlRuleExecutionContext context)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
// Use a visitor to see if the procedure has a nocount set
SetXActAbortVisitor visitor = new SetXActAbortVisitor();
context.ScriptFragment.Accept(visitor);
if (!visitor.SetXActAbortFound)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
context.RuleDescriptor.DisplayDescription,
context.ElementName),
context.ModelElement);
problems.Add(problem);
}
return(problems);
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var problems = new List <SqlRuleProblem>();
foreach (var token in fragment.ScriptTokenStream)
{
if (token.IsKeyword() && token.Text.Any(char.IsLower))
{
var description = string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription);
var problem = new SqlRuleProblem(description, modelElement)
{
Severity = SqlRuleProblemSeverity.Error
};
problem.SetSourceInformation(new SourceInformation(problem.SourceName, token.Line, token.Column));
problems.Add(problem);
}
}
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);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var objectName = ruleExecutionContext.ModelElement;
if (objectName != null)
{
if (!IsView(objectName.Name))
{
var displayServices = ruleExecutionContext.SchemaModel.DisplayServices;
var formattedName = displayServices.GetElementName(objectName, ElementNameStyle.FullyQualifiedName);
var problemDescription = string.Format(Message, formattedName);
var problem = new SqlRuleProblem(problemDescription, objectName);
problems.Add(problem);
}
}
return(problems);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = GetElementName(ruleExecutionContext, modelElement);
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
var visitor = new SelectStarVisitor();
fragment.Accept(visitor);
foreach (var selectStarExpression in visitor.SelectStarExpressions)
{
var problem = new SqlRuleProblem(string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement, selectStarExpression);
problem.Severity = SqlRuleProblemSeverity.Warning;
problems.Add(problem);
}
return(problems);
}
protected override IList <SqlRuleProblem> ElicitProblems(TSqlFragment fragment, RuleDescriptor ruleDescriptor, string elementName, TSqlObject modelElement)
{
var problems = new List <SqlRuleProblem>();
var visitor = new WhereVisitor();
fragment.Accept(visitor);
if (visitor.IsWhereClauseInvalid)
{
var problem = new SqlRuleProblem(string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement, visitor.InvalidWhereClause);
problem.Severity = SqlRuleProblemSeverity.Error;
problems.Add(problem);
}
foreach (var invalidBooleanExpression in visitor.InvalidBooleanExpresion)
{
var problem = new SqlRuleProblem(string.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement, invalidBooleanExpression);
problem.Severity = SqlRuleProblemSeverity.Error;
problems.Add(problem);
}
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;
}
/// <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(XtendSqlRuleExecutionContext context)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
// Use a visitor to see if the procedure has unused variables
UnusedVariableVisitor visitor = new UnusedVariableVisitor(true);
context.ScriptFragment.Accept(visitor);
foreach (DeclareVariableElement element in visitor.DeclareVariableElements.Values)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
context.RuleDescriptor.DisplayDescription,
element.VariableName.Value,
context.ElementName),
context.ModelElement,
element);
problems.Add(problem);
}
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(XtendSqlRuleExecutionContext context)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
// Use a visitor to see if the procedure has unused variables
MandatoryParameterVisitor visitor = new MandatoryParameterVisitor(context.SchemaModel);
context.ScriptFragment.Accept(visitor);
foreach (ExecutableProcedureReference element in visitor.ProcedureCalls)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
context.RuleDescriptor.DisplayDescription,
element.ProcedureReference.ProcedureReference.Name.SchemaIdentifier.Value,
element.ProcedureReference.ProcedureReference.Name.BaseIdentifier.Value,
visitor.MissingParameters[element]),
context.ModelElement,
element);
problems.Add(problem);
}
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>
/// 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>
/// 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;
string elementName = ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(modelElement, ElementNameStyle.EscapedFullyQualifiedName);
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
// Get schema of the procedure.
TSqlObject schema = modelElement.GetReferenced(Procedure.Schema).SingleOrDefault();
if (schema != null && fragment.FragmentLength > 0)
{
CursorVisitor visitor = new CursorVisitor(true, false);
fragment.Accept(visitor);
foreach (var element in visitor.DeclareCursorStatementsMissingDeallocate)
{
string cursorName = element.Name.Value;
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription, "Missing deallocate",
cursorName,
elementName),
modelElement,
element.CursorDefinition);
problems.Add(problem);
}
foreach (var element in visitor.IncorrectDeallocateCursorStatements)
{
string cursorName = element.Cursor?.Name.Value ?? "NULL";
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription, "Invalid deallocate",
cursorName,
elementName),
modelElement,
element);
problems.Add(problem);
}
foreach (var element in visitor.DeclareCursorStatementsMissingOpen)
{
string cursorName = element.Name.Value;
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription, "Missing open",
cursorName,
elementName),
modelElement,
element.CursorDefinition);
problems.Add(problem);
}
foreach (var element in visitor.IncorrectOpenCursorStatements)
{
string cursorName = element.Cursor?.Name.Value ?? "NULL";
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription, "Invalid open",
cursorName,
elementName),
modelElement,
element);
problems.Add(problem);
}
}
return(problems);
}
public static string Rule(this SqlRuleProblem problem) => problem.RuleId.Split('.').Last();
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);
}
}
}
