protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
//Get the indexes of the table
var indexes = table.GetReferencing(Index.IndexedObject);
if (indexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("The anchor table {0} has no index", name), table);
//Ensure that one of them is effecively not a clustered index
var nonClusturedIndexes = indexes.Where(i => !i.GetProperty<bool>(Index.Clustered) && i.GetProperty<bool>(Index.Unique));
if (nonClusturedIndexes == null)
yield return new SqlRuleProblem(string.Format("No existing non-clustered unique index for the anchor table {0}", name), table);
else
{
//Ensure that at least one of them is name BK
var bkIndexes = nonClusturedIndexes.Where(i => i.Name.Parts.Last().StartsWith(Configuration.Anchor.BusinessKeyPrefix));
if (bkIndexes.Count()==0)
yield return new SqlRuleProblem(string.Format("None of the non-clustered unique indexes for the anchor table {0} are starting by BK_", name), table);
else
{
foreach (var bkIndex in bkIndexes)
{
//Ensure that the unique index is not active on the identity column
var columns = bkIndex.GetReferenced(Index.Columns).Where(c => c.GetProperty<bool>(Column.IsIdentity));
if (columns.Count()>0)
yield return new SqlRuleProblem(string.Format("The business key (non-clustered unique index) {1} for the anchor table {0} contains the identity column.", name, bkIndex.Name), table);
//By default SQL Server will include the indentity column (because this column should be the clustered index)
var includedColumns = bkIndex.GetReferenced(Index.IncludedColumns).Where(c => c.GetProperty<bool>(Column.IsIdentity));
if (includedColumns.Count() > 0)
yield return new SqlRuleProblem(string.Format("The business key (non-clustered unique index) {1} for the anchor table {0} includes the identity column.", name, bkIndex.Name), table);
}
}
}
}
protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
//Get the indexes of the table
var indexes = table.GetReferencing(Index.IndexedObject);
if (indexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("The info table {0} has no index", name), table);
//Ensure that one of them is effecively a clustered index
var clusteredIndex = indexes.FirstOrDefault(i => i.GetProperty<bool>(Index.Clustered));
if (clusteredIndex == null)
yield return new SqlRuleProblem(string.Format("No clustered index for the info table {0}", name), table);
else
{
//Ensure that this index is effectively unique
var uniqueClusturedIndex = indexes.FirstOrDefault(i => i.GetProperty<bool>(Index.Clustered) && i.GetProperty<bool>(Index.Unique));
if (uniqueClusturedIndex == null)
yield return new SqlRuleProblem(string.Format("Clustured index for the info table {0} is not unique ", name), table);
else
{
//Ensure that the clustered index is active only on the identity column
var columns = uniqueClusturedIndex.GetReferenced(Index.Columns);
if (columns.Count() > 1)
yield return new SqlRuleProblem(string.Format("The info table {0} has a clustered index but this index has more than one column.", name), table);
if (columns.Count(c => c.GetProperty<bool>(Column.IsIdentity)) == 0)
yield return new SqlRuleProblem(string.Format("The info table {0} has a clustered index but this index doesn't include the identity column.", name), table);
}
}
}
public static bool CheckForFkIndex(this TSqlObject table, IList <ObjectIdentifier> columnNames)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (columnNames == null)
{
throw new ArgumentNullException(nameof(columnNames));
}
if (table.ObjectType != Table.TypeClass)
{
throw new ArgumentException("The parameter is not of type Table", nameof(table));
}
//convert the column names to a list of string
var fkColumnNames = columnNames.Select(x => x.Parts.Last()).ToList();
//get all the indexes for this table
var indexes = table.GetReferencing(DacQueryScopes.All)
.Where(x => x.ObjectType == Index.TypeClass ||
x.ObjectType == PrimaryKeyConstraint.TypeClass ||
x.ObjectType == UniqueConstraint.TypeClass).ToList();
if (indexes.Count == 0)
{
return(false);
}
//pull all the column names out of the indexes
var indexInfo = new Dictionary <string, IList <string> >();
foreach (var index in indexes)
{
var columns = index.GetReferenced(DacQueryScopes.All)
.Where(x => x.ObjectType == Column.TypeClass);
indexInfo.Add(index.Name.GetName(),
new List <string>(columns.Select(c => c.Name.Parts.Last()))
);
}
//find any index that contains all the columns from the foreign key
return(indexInfo.Any(ii =>
{
//intersect works, but the index must match the column names in
//the correct order, and the proper ordinal in the index hence the for...
//i.Value.Intersect(fkColumnNames).Count() == fkColumnNames.Count())
for (int i = 0; i < fkColumnNames.Count; i++)
{
if (!fkColumnNames[i].StringEquals(ii.Value?.ElementAtOrDefault(i)))
{
return false;
}
}
return true;
}));
}
/// <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));
}
}
/// <summary>
/// Get primary key column(s) from a table
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static IEnumerable <TSqlObject> GetPrimaryKeyColumns(this TSqlObject table)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
TSqlObject pk = table.GetReferencing(PrimaryKeyConstraint.Host, DacQueryScopes.UserDefined).FirstOrDefault();
if (pk != null)
{
var columns = pk.GetReferenced(PrimaryKeyConstraint.Columns);
if (columns != null)
{
return(columns);
}
}
return(new TSqlObject[0]);
}
/// <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);
}
protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
var indexes = table.GetReferencing(Index.IndexedObject);
var firstTimeIndexes = indexes.Where(i => i.GetReferenced(Index.Columns).First().Name.Parts.Last()
== Configuration.Link.IsFirst);
if (firstTimeIndexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("No index where is-first column is '{0}' for link table {1}", Configuration.TimeBased.Key, name), table);
foreach (var fIndex in firstTimeIndexes)
{
var indexColumns = fIndex.GetReferenced(Index.Columns).Where(c => c.Name.Parts.Last() != Configuration.TimeBased.Key);
var includedColumns = fIndex.GetReferenced(Index.IncludedColumns);
var allColumns = indexColumns.Union(includedColumns);
if (allColumns.Count() == 0)
yield return new SqlRuleProblem(string.Format("The time-based index {0} for link table {1} has no additional or included columns", fIndex.Name, name), table);
yield return new SqlRuleProblem(string.Format("The time-based index {0} for link table {1} has no additional or included columns", fIndex.Name, name), table);
}
}
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);
}
protected override IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table)
{
var indexes = table.GetReferencing(Index.IndexedObject);
var timeBasedIndexes = indexes.Where(i => i.GetReferenced(Index.Columns).First().Name.Parts.Last()
== Configuration.TimeBased.Key);
if (timeBasedIndexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("No index where first column is '{0}' for link table {1}", Configuration.TimeBased.Key, name), table);
foreach (var tbIndex in timeBasedIndexes)
{
var unexpectedColumns = tbIndex.GetReferenced(Index.Columns).Where(c => c.Name.Parts.Last() != Configuration.TimeBased.Key);
if (unexpectedColumns.Count()>0)
{
yield return new SqlRuleProblem(
string.Format(
"The time-based index '{0}' for link table '{1}' contains additional columns. Unexpected column{2} '{3}'"
, tbIndex.Name
, name
, (unexpectedColumns.Count() == 1) ? " is " : "s are "
, string.Join("', '", unexpectedColumns.Select(c => c.Name.Parts.Last()))
), table);
}
var idColumns = table.GetReferenced(Table.Columns).Where(c => c.Name.Parts.Last() != Configuration.TimeBased.Key && c.Name.Parts.Last().EndsWith("Id"));
var includedColumns = tbIndex.GetReferenced(Index.IncludedColumns);
var missingColumns = idColumns.Except(includedColumns);
if (missingColumns.Count()>0)
{
yield return new SqlRuleProblem(
string.Format(
"The time-based index '{0}' for link table '{1}' doesn't include some Id columns. Missing column{2} '{3}'"
, tbIndex.Name
, name
, (missingColumns.Count() == 1) ? " is " : "s are "
, string.Join("', '", missingColumns.Select(c => c.Name.Parts.Last()))
), table);
}
}
}
/// <summary>
/// Get uk(s) with attached column(s) from a table. Based on unique constrains finding
/// </summary>
/// <param name="table"></param>
/// <returns></returns>
public static IEnumerable <IEnumerable <TSqlObject> > GetUniqueKeysWithColumns(this TSqlObject table)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
IEnumerable <TSqlObject> uks = table.GetReferencing(UniqueConstraint.Host, DacQueryScopes.UserDefined);
if (uks != null)
{
foreach (var uk in uks)
{
var columns = uk.GetReferenced(UniqueConstraint.Columns);
if (columns != null)
{
yield return(columns);
}
}
}
}
protected IEnumerable<SqlRuleProblem> OnAnalyze(string name, TSqlObject table, string columnName)
{
var indexes = table.GetReferencing(Index.IndexedObject);
var lastIndexes = indexes.Where(i => i.GetReferenced(Index.Columns).Last().Name.Parts.Last()
== columnName);
if (lastIndexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("No index on the column '{0}' for link table {1}", columnName, name), table);
var filteredIndexes = lastIndexes.Where(i => i.GetProperty<bool>(Index.FilterPredicate));
if (filteredIndexes.Count() == 0)
yield return new SqlRuleProblem(string.Format("An index exists on the column '{0}' for link table {0} but this index is not filtered.", columnName, name), table);
foreach (var lastIndex in lastIndexes)
{
var indexColumns = lastIndex.GetReferenced(Index.Columns).Where(c => c.Name.Parts.Last() != columnName);
var includedColumns = lastIndex.GetReferenced(Index.IncludedColumns);
var allColumns = indexColumns.Union(includedColumns);
if (allColumns.Count() == 0)
yield return new SqlRuleProblem(string.Format("The index {0} for link table {1} has no additional column or included column.", lastIndex.Name, name), table);
}
}
public static IDictionary <string, ForeignKeyInfo> GetTableFKInfos(this TSqlObject table)
{
if (table == null)
{
throw new ArgumentNullException(nameof(table));
}
if (table.ObjectType != Table.TypeClass)
{
throw new ArgumentException("The parameter is not of type Table", nameof(table));
}
var fks = new Dictionary <string, ForeignKeyInfo>();
var tableFks = table.GetReferencing(DacQueryScopes.All).Where(x => x.ObjectType == ForeignKeyConstraint.TypeClass);
foreach (var fk in tableFks)
{
var name = fk.Name.GetName();
if (!fks.ContainsKey(name))
{
fks.Add(name, fk.GetFKInfo());
}
}
return(fks);
}
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;
// Get columns of the foreign key
List <TSqlObject> fkColumns = modelElement.GetReferenced(ForeignKeyConstraint.Columns).ToList();
// Get table of the foreign key.
TSqlObject table = modelElement.GetReferenced(ForeignKeyConstraint.Host).SingleOrDefault();
bool foundMatch = false;
if (table != null)
{
// Check the indexes of the table
foreach (TSqlObject index in table.GetReferencing(Index.IndexedObject))
{
// Get columns of the index
List <TSqlObject> indexColumns = index.GetReferenced(Index.Columns).ToList();
foundMatch = CompareColumns(fkColumns, indexColumns, ruleExecutionContext);
if (foundMatch)
{
break;
}
}
if (!foundMatch)
{
// Check the primary keys as well
foreach (TSqlObject pk in table.GetReferencing(PrimaryKeyConstraint.Host))
{
// Get columns of the primary key
List <TSqlObject> pkColumns = pk.GetReferenced(PrimaryKeyConstraint.Columns).ToList();
foundMatch = CompareColumns(fkColumns, pkColumns, ruleExecutionContext);
if (foundMatch)
{
break;
}
}
}
if (!foundMatch)
{
// Check the unique constraints as well
foreach (TSqlObject uq in table.GetReferencing(UniqueConstraint.Host))
{
// Get columns of the primary key
List <TSqlObject> uqColumns = uq.GetReferenced(UniqueConstraint.Columns).ToList();
// Try to match all the columns in the foreign key to the index.
foundMatch = CompareColumns(fkColumns, uqColumns, ruleExecutionContext);
if (foundMatch)
{
break;
}
}
}
if (!foundMatch)
{
SqlRuleProblem problem = new SqlRuleProblem(
String.Format(
CultureInfo.CurrentCulture,
ruleDescriptor.DisplayDescription,
elementName,
ruleExecutionContext.SchemaModel.DisplayServices.GetElementName(table, ElementNameStyle.EscapedFullyQualifiedName)),
modelElement);
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));
}
}