/// <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);
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = RuleUtils.GetElementName(ruleExecutionContext, modelElement);
CreateTableStatementVisitor visitor = new CreateTableStatementVisitor();
fragment.Accept(visitor);
return((
from table in visitor.Nodes
where table.SchemaObjectName.BaseIdentifier.Value.ToUpper().Contains("VIEW")
select new SqlRuleProblem(String.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement)
).ToList());
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = RuleUtils.GetElementName(ruleExecutionContext, modelElement);
CreateTableStatementVisitor visitor = new CreateTableStatementVisitor();
fragment.Accept(visitor);
// Begin with alpha character
Regex regex = new Regex(@"^[A-Z][a-zA-Z0-9]*$");
return((
from table in visitor.Nodes
where !regex.IsMatch(table.SchemaObjectName.BaseIdentifier.Value)
select new SqlRuleProblem(String.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, table.SchemaObjectName.BaseIdentifier.Value), modelElement)
).ToList());
}
/// <summary>
/// Ensure Primary Key included for each table
/// is created
/// </summary>
/// <param name="ruleExecutionContext"></param>
/// <returns></returns>
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = RuleUtils.GetElementName(ruleExecutionContext, modelElement);
CreateTableStatementVisitor visitor = new CreateTableStatementVisitor();
fragment.Accept(visitor);
return((
from table in visitor.Nodes
let isTemp = table.SchemaObjectName.BaseIdentifier.Value.StartsWith("#") || table.SchemaObjectName.BaseIdentifier.Value.StartsWith("@")
where !isTemp && !table.Definition.TableConstraints.OfType <UniqueConstraintDefinition>().Any(x => x.IsPrimaryKey)
select new SqlRuleProblem(String.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement)
).ToList());
//List<SqlRuleProblem> problems = new List<SqlRuleProblem>();
//TSqlObject sqlObj = ruleExecutionContext.ModelElement;
//if (sqlObj != null)
//{
// if (sqlObj.Name != null && sqlObj.Name.Parts != null && sqlObj.Name.Parts.Any(x => x.StartsWith("#") || x.StartsWith("@")))
// {
// return problems;
// }
// var child = sqlObj.GetChildren(DacQueryScopes.All).FirstOrDefault(x => x.ObjectType == PrimaryKeyConstraint.TypeClass);
// if (child == null)
// {
// string msg = string.Format(Message, RuleUtils.GetElementName(ruleExecutionContext, sqlObj));
// problems.Add(new SqlRuleProblem(msg, sqlObj));
// }
//}
//return problems;
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
IList <SqlRuleProblem> problems = new List <SqlRuleProblem>();
TSqlFragment fragment = ruleExecutionContext.ScriptFragment;
RuleDescriptor ruleDescriptor = ruleExecutionContext.RuleDescriptor;
TSqlObject modelElement = ruleExecutionContext.ModelElement;
string elementName = RuleUtils.GetElementName(ruleExecutionContext, modelElement);
CreateTableStatementVisitor visitor = new CreateTableStatementVisitor();
fragment.Accept(visitor);
// Begin with alpha character. Only contain alphanumeric.
Regex regex = new Regex(@"^[A-Z][a-zA-Z0-9]*$");
return((
from table in visitor.Nodes
where table.Definition.ColumnDefinitions.Any(x => !regex.IsMatch(x.ColumnIdentifier.Value))
select new SqlRuleProblem(String.Format(CultureInfo.CurrentCulture, ruleDescriptor.DisplayDescription, elementName), modelElement)
).ToList());
}
public override IList <SqlRuleProblem> Analyze(SqlRuleExecutionContext ruleExecutionContext)
{
var problems = new List <SqlRuleProblem>();
var sqlObj = ruleExecutionContext.ModelElement; //proc / view / function
try
{
if (sqlObj != null)
{
var fragment = ruleExecutionContext.ScriptFragment;
//get the combined parameters and declare variables into one searchable list
var variablesVisitor = new VariablesVisitor();
fragment.AcceptChildren(variablesVisitor);
var variables = variablesVisitor.GetVariables();
var selectStatementVisitor = new SelectStatementVisitor();
fragment.Accept(selectStatementVisitor);
foreach (var select in selectStatementVisitor.Statements)
{
var query = select.QueryExpression as QuerySpecification;
if (query != null && query.WhereClause != null)
{
var booleanComparisonVisitor = new BooleanComparisonVisitor();
query.WhereClause.Accept(booleanComparisonVisitor);
foreach (var comparison in booleanComparisonVisitor.Statements)
{
var datatype1 = GetDataType(sqlObj, query, comparison.FirstExpression, variables);
if (string.IsNullOrEmpty(datatype1))
{
continue;
}
var datatype2 = GetDataType(sqlObj, query, comparison.SecondExpression, variables);
if (string.IsNullOrEmpty(datatype2))
{
continue;
}
//when checking the numeric literal I am not sure if it is a bit or tinyint.
if ((_comparer.Equals(datatype1, "bit") && _comparer.Equals(datatype2, "tinyint")) || (_comparer.Equals(datatype1, "tinyint") && _comparer.Equals(datatype2, "bit")))
{
continue;
}
if (!_comparer.Equals(datatype1, datatype2))
{
string msg = string.Format(Message, sqlObj.ObjectType.Name, RuleUtils.GetElementName(ruleExecutionContext, sqlObj));
problems.Add(new SqlRuleProblem(msg, sqlObj, comparison));
}
}
}
}
}
}
catch (System.Exception ex)
{
//TODO: PROPERLY LOG THIS ERROR
Debug.WriteLine(ex.ToString());
//throw;
}
return(problems);
}
