private void ChangeCreateIndexOperationalProps(DeploymentPlanContributorContext context,
IList <IndexOption> options)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundMainSection = false;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
// We only want to analyze the main part of the deployment script - we'll skip
// any steps until we pass the end of the predeployment section, and stop once
// we hit the start of the postdeployment section
if (currentStep is EndPreDeploymentScriptStep)
{
foundMainSection = true;
continue;
}
if (!foundMainSection)
{
// Haven't gotten past predeployment yet
continue;
}
if (currentStep is BeginPostDeploymentScriptStep)
{
break;
}
// We only care about CreateElementSteps for Indexes.
CreateElementStep createElementStep = currentStep as CreateElementStep;
if (createElementStep != null &&
createElementStep.SourceElement != null &&
Index.TypeClass.Equals(createElementStep.SourceElement.ObjectType))
{
TSqlFragment fragment = createElementStep.Script;
CreateIndexStatementVisitor visitor = new CreateIndexStatementVisitor(options);
fragment.Accept(visitor);
}
}
}
private void ChangeCreateIndexOperationalProps(DeploymentPlanContributorContext context,
IList<IndexOption> options)
{
DeploymentStep nextStep = context.PlanHandle.Head;
// Loop through all steps in the deployment plan
bool foundMainSection = false;
while (nextStep != null)
{
DeploymentStep currentStep = nextStep;
nextStep = currentStep.Next;
// We only want to analyze the main part of the deployment script - we'll skip
// any steps until we pass the end of the predeployment section, and stop once
// we hit the start of the postdeployment section
if (currentStep is EndPreDeploymentScriptStep)
{
foundMainSection = true;
continue;
}
if (!foundMainSection)
{
// Haven't gotten past predeployment yet
continue;
}
if (currentStep is BeginPostDeploymentScriptStep)
{
break;
}
// We only care about CreateElementSteps for Indexes.
CreateElementStep createElementStep = currentStep as CreateElementStep;
if (createElementStep != null
&& createElementStep.SourceElement != null
&& Index.TypeClass.Equals(createElementStep.SourceElement.ObjectType))
{
TSqlFragment fragment = createElementStep.Script;
CreateIndexStatementVisitor visitor = new CreateIndexStatementVisitor(options);
fragment.Accept(visitor);
}
}
}
/// <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 objName = sqlObj.Name.GetName();
var indexes = sqlObj.GetReferencing(DacQueryScopes.All)
.Where(x => x.ObjectType == Index.TypeClass).Select(x => x.GetFragment());
if (indexes.Count() == 0)
{
return(problems);
}
var indexVisitor = new CreateIndexStatementVisitor();
foreach (var index in indexes)
{
index.Accept(indexVisitor);
}
var indexInfo = new Dictionary <CreateIndexStatement, List <string> >();
foreach (var index in indexVisitor.Statements)
{
indexInfo.Add(index, new List <string>(index.Columns.Select(col => col.Column.GetName().ToLower())));
}
if (indexInfo.Count == 0)
{
return(problems);
}
//find all the duplicates where all the columns match
var dupes = indexInfo.GroupBy(x => string.Join(",", x.Value))
.Where(x => x.Count() > 1).SelectMany(x => x).ToList();
problems.AddRange(dupes
.Select(ix => new SqlRuleProblem(string.Format(MessageDuplicate, ix.Key.Name.Value), sqlObj, ix.Key)));
//remove the exact duplicates to try to search for border line duplicates
indexInfo.RemoveAll((key, value) => dupes.Any(x => x.Key == key));
if (indexInfo.Count <= 1)
{
return(problems);
}
//find all the borderline duplicates where the first column matches
var borderLineDupes = indexInfo.GroupBy(x => x.Value.First()).Where(x => x.Count() > 1).SelectMany(x => x).ToList();
problems.AddRange(borderLineDupes
.Select(ix => new SqlRuleProblem(string.Format(MessageBorderLine, ix.Key.Name.Value), sqlObj, ix.Key)));
return(problems);
}
