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)); } }