public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { foreach (var table in database.Tables) { if (table.Columns.Count == 0) { var issue = new Issue(this, this.DefaultSeverity.Value) { Name = string.Format("Table Without Columns"), Context = new TableContext(table), Description = new Description("Table {0} does not have any columns", table), }; issueCollector.ReportIssue(issue); } else if (table.Columns.Count == 1) { var issue = new Issue(this, this.DefaultSeverity.Value) { Name = string.Format("Table With Only One Column"), Context = new TableContext(table), Description = new Description("Table {0} has only a single column: '{1}'", table, table.Columns[0]) }; issueCollector.ReportIssue(issue); } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { foreach (var table in database.Tables) { foreach (var fk in table.ForeignKeys) { bool doesIndexExist = table.Indices.Any(ix => { if (ix.Columns.Count < fk.ColumnPairs.Count) return false; for (int i = 0; i < fk.ColumnPairs.Count; i++) { var fkColumn = fk.ColumnPairs[i].FKColumn; if (!ix.Columns[i].Equals(fkColumn)) return false; } return true; }); if (!doesIndexExist) { var dt = new DataTable(); dt.Columns.Add("Column", typeof(Column)); foreach (var col in fk.ColumnPairs) dt.Rows.Add(col.FKColumn); issueCollector.ReportIssue(new Issue(this, this.DefaultSeverity.Value) { Name = "Missing Index on Foreign-Key Column(s)", Context = new TableContext(table), Description = new Description("Missing index on the columns used in foreign-key '{0}' in table '{1}'", fk.ForeignKeyName, table), ExtendedDescription = new Description("{0}", dt) }); } } } }
//Constructs issues from the result of the SQL query private void HandleSQLResult(DataTable result, IIssueCollector issueCollector, Model.Database database) { var columns = result.Columns; //Check for missing description column (which is the only required column to exist in the result set) if (!columns.Contains("description")) { Issue missingDescIssue = new Issue(this, this.DefaultSeverity.Value); missingDescIssue.Name = "Failed to execute SQL rule"; missingDescIssue.Description = new Description("Failed to execute SQL rule '{0}'. Column 'description' is missing", this.Name); missingDescIssue.Context = new DatabaseContext(database); issueCollector.ReportIssue(missingDescIssue); } //The extended description is optional bool hasExtendedDescription = columns.Contains("extended_description"); //Iterate through each row in the result and add an issue for every row if (result.Rows.Count > 0) { foreach (DataRow row in result.Rows) { Issue issue = new Issue(this, this.Severity); issue.Name = this.Name; issue.Context = getContextFromSQLIssue(result, row, database); issue.Description = new Description(row["description"].ToString()); if (hasExtendedDescription) { issue.ExtendedDescription = new Description(row["extended_description"].ToString()); } issueCollector.ReportIssue(issue); } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { foreach (var table in database.Tables.Where(t => t.ForeignKeys.Count > 0 && t.PrimaryKey != null)) { foreach (var fk in table.ForeignKeys.Where(fk => TableID.TableEquals(fk.PKTable, fk.FKTable))) { bool isContained = true; foreach (var pair in fk.ColumnPairs) { if (!table.PrimaryKey.Columns.Contains(pair.FKColumn)) { isContained = false; } } if (isContained) { issueCollector.ReportIssue(new Issue(this, DefaultSeverity.Value) { Name = this.Name, Context = new TableContext(table), Description = new Description("The primary key '{0}' in table '{1}' references itself", table.PrimaryKey.PrimaryKeyName, table), ExtendedDescription = new Description("Having a self-referencing primary key is likely not what is intended."), }); } } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { foreach (var table in database.Tables) { foreach (var foreignKey in table.ForeignKeys) { foreach (var reference in foreignKey.ColumnPairs) { if ((!reference.FKColumn.DataType.Equals(reference.PKColumn.DataType)) || reference.FKColumn.DataType.Equals(reference.PKColumn.DataType) && (!reference.FKColumn.CharacterMaxLength.Equals(reference.PKColumn.CharacterMaxLength) || !reference.FKColumn.NumericPrecision.Equals(reference.PKColumn.NumericPrecision) || !reference.FKColumn.NumericScale.Equals(reference.PKColumn.NumericScale))) { issueCollector.ReportIssue(new Issue(this, this.DefaultSeverity.Value) { Name = this.Name, Description = new Description("Different data types between source and target columns in foreign key from table '{0}' to table '{1}'", reference.FKColumn.Table, reference.PKColumn.Table), ExtendedDescription = new Description("The table below shows the column name and data type for the columns.\n{0}", this.GetTable(reference.FKColumn, reference.PKColumn)), Context = new ColumnContext(reference.FKColumn) }); } } } } }
public override void Execute(DataTable table, IIssueCollector issueCollector, IProviderCollection providers) { var columns = table.QueryableColumns.Where(c => DataTypesLists.TextTypes().Contains(c.DataType)); var candidates = columns.ToList().Select(c => new Candidate(c)).ToList(); using (var rowEnumerable = table.GetTableRowEnumerable()) foreach (var row in rowEnumerable) { foreach (var candidate in candidates) { Object value = row[candidate.Column.ColumnName]; if (value is String) { String valueStr = (String)value; if (valueStr == String.Empty) { continue; } ValueType type = Classifier.Classify(valueStr); candidate.AddValue(valueStr, type); } } if (candidates.Count() == 0) { break; } } foreach (var candidate in candidates) { if (candidate.Values.Count <= 1) { continue; } System.Data.DataTable valuesTable = new System.Data.DataTable(); valuesTable.Columns.Add("Value Examples", typeof(String)); valuesTable.Columns.Add("Data Type", typeof(String)); foreach (var dicEntry in candidate.Values) { foreach (var val in dicEntry.Value) { var row = valuesTable.NewRow(); row[0] = val; row[1] = dicEntry.Key.ToString(); valuesTable.Rows.Add(row); } } Issue issue = new Issue(this, this.Severity); issue.Name = "Mixture of Data Types in a Column"; issue.Description = new Description("The column '{0}' contains a mixture of data types", candidate.Column); issue.ExtendedDescription = new Description("Examples:\n\n{0}", valuesTable); issue.Context = new ColumnContext(candidate.Column); issueCollector.ReportIssue(issue); } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { var tables = from tab in database.Tables where tab.Indices.Count > 1 select tab; try { var start = DateTime.Now; foreach (var table in tables) { var sortedIndices = table.Indices.OrderByDescending(t => t.Columns.Count).ToList(); List <Index> excludeIndex = new List <Index>(); List <Index> redundantIndices = new List <Index>(); for (int i = 0; i < sortedIndices.Count - 1; i++) { if (!excludeIndex.Contains(sortedIndices[i])) { for (int j = i + 1; j < sortedIndices.Count; j++) { var subIndexLen = sortedIndices[j].Columns.Count; if (sortedIndices[i].Columns.ToList().GetRange(0, subIndexLen).SequenceEqual(sortedIndices[j].Columns)) { redundantIndices.Add(sortedIndices[j]); if (!excludeIndex.Contains(sortedIndices[j])) { excludeIndex.Add(sortedIndices[j]); } } } if (redundantIndices.Count > 0) { var issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Redundant Index"; issue.Context = new TableContext(table); issue.Description = new Description("Redundant indices for the index '{0}' in table {1}", sortedIndices[i], table); issue.ExtendedDescription = new Description("The indices in the second table below are redundant to the index:\n\n{0}\n\nRedundant indices:\n\n{1}", GetIndexTable(new List <Index>() { sortedIndices[i] }), GetIndexTable(redundantIndices)); issueCollector.ReportIssue(issue); } } redundantIndices.Clear(); } } var end = DateTime.Now; //Console.WriteLine("redundant indices time: {0}", (end - start)); } catch (Exception ex) { Console.WriteLine(String.Format("\tError in redundant indices:\n{0}", ex.Message)); throw ex; } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { this._nodeProps = DictionaryFactory.CreateTableID <NodeProperties>(); this._foundCycles = new List <List <Table> >(); this._stack = new Stack <Table>(); this._index = 0; foreach (var table in database.Tables) { if (!this._nodeProps.ContainsKey(table)) { this.Tarjan(table); } } foreach (var cycle in this._foundCycles) { var canInsert = false; var foundTables = new Dictionary <Table, List <ForeignKey> >(); foreach (var table in cycle) { var fksValues = table.ForeignKeys.Where(p => cycle.Contains(p.PKTable)).ToList(); foundTables.Add(table, fksValues); foreach (var fkValue in fksValues) { if (fkValue.ColumnPairs.All(f => f.FKColumn.IsNullable) || (fkValue.Deferrability == Deferrability.Deferrable && fkValue.InitialMode == InitialMode.InitiallyDeferred)) { canInsert = true; } } } var issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Cycle Dependency Between Tables"; issue.Context = IssueContext.Create(cycle); string tableList = String.Join(", ", cycle.Select(c => c.TableName)); if (tableList.Length > 20) { tableList = tableList.Substring(0, 20) + "..."; } issue.Description = new Description("Cycle dependency between {0} tables: {1}", cycle.Count, tableList); var str = new StringBuilder(); // Delete rules is no longer included in the check because of complex cycles if (!canInsert) { str.Append("There is a cycle dependency, where the referential constraints could yield problems."); str.Append("\n- Deferability and initially-deferred configurations could complicate insert, update and delete statements."); issue.ExtendedDescription = new Description("{0}\n\nThe cycle contains the tables:\n\n{1}", str.ToString(), GetTable(foundTables)); } else { issue.Severity = CycleWithoutDeferabilityProblems.Value; issue.ExtendedDescription = new Description("There is a cyclic dependency, but there are no deferability constraints that could yield problems. Consider if the cyclic dependency is needed.\n\nThe cycle contains the tables:\n\n{0}", GetTable(foundTables)); } issueCollector.ReportIssue(issue); } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { foreach (var table in database.Tables) { List <RFK> redundants = new List <RFK>(); foreach (var fk in table.ForeignKeys) { var s_columns = fk.ColumnPairs.Select(p => p.FKColumn).ToList(); var t_columns = fk.ColumnPairs.Select(p => p.PKColumn).ToList(); var re = redundants.Where(r => r.SourceColumns.SequenceEqual(s_columns) && r.TargetColumns.SequenceEqual(t_columns)).FirstOrDefault(); if (re != null) { re.ForeignKeys.Add(fk); } else { var rfk = new RFK() { SourceColumns = s_columns, TargetColumns = t_columns }; rfk.ForeignKeys.Add(fk); redundants.Add(rfk); } } foreach (var rfk in redundants) { if (rfk.ForeignKeys.Count <= 1) { continue; } var descTable = new System.Data.DataTable(); descTable.Columns.Add("Name"); descTable.Columns.Add("Source Column(s)", typeof(String)); descTable.Columns.Add("Target Column(s)", typeof(String)); foreach (var fk in rfk.ForeignKeys) { var row = descTable.NewRow(); row[0] = fk.ForeignKeyName; row[1] = String.Join("\n", fk.ColumnPairs.Select(p => p.FKColumn.ColumnName)); row[2] = String.Join("\n", fk.ColumnPairs.Select(p => p.PKColumn.Table.TableName + "." + p.PKColumn.ColumnName)); descTable.Rows.Add(row); } Issue issue = new Issue(this, this.Severity); issue.Name = "Redundant Foreign Key"; issue.Context = new TableContext(table); issue.Description = new Description("Redundant foreign key in table {0}", table); issue.ExtendedDescription = new Description("{0}", descTable); issueCollector.ReportIssue(issue); } } }
public override void Execute(DataTable table, IIssueCollector issueCollector, IProviderCollection providers) { if (table.Cardinality < MinRows.Value) { return; } var checkedColumns = table.QueryableColumns; if (checkedColumns.Count == 0) { return; } var columnNullCount = DictionaryFactory.CreateColumnID <int>(); foreach (var col in checkedColumns) { columnNullCount[col] = 0; } int rowCount = 0; using (var rowEnumerable = table.GetTableRowEnumerable()) foreach (var row in rowEnumerable) { rowCount++; foreach (var col in checkedColumns) { var val = row[col.ColumnName]; if (val is DBNull) { columnNullCount[col] += 1; } } } var maxnullCount = this.MaxPercentageNull.Value * rowCount / 100f; var columnsWithTooManyNulls = from cp in columnNullCount where cp.Value > maxnullCount select cp; foreach (var columnWithTooManyNulls in columnsWithTooManyNulls) { var percentNull = Math.Round(columnWithTooManyNulls.Value * 100f / rowCount, 1); issueCollector.ReportIssue(new Issue(this, this.Severity) { Name = "Column Containing Too Many Nulls", Context = new ColumnContext(columnWithTooManyNulls.Key), Description = new Description("Column '{0}' has {1} percent nulls", columnWithTooManyNulls.Key, percentNull) }); } }
public override void Execute(DataTable table, IIssueCollector issueCollector, IProviderCollection providers) { if (table.Cardinality == 0) { issueCollector.ReportIssue(new Issue(this, Severity.Medium) { Name = "Empty Table", Context = new TableContext(table), Description = new Description("Table {0} is empty", table) }); } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { float allowedPercentage = AllowedPercentage.Value / 100f; foreach (var table in database.Tables.Where(tbl => tbl.Columns.Any(c => c.IsNullable))) { IEnumerable <Column> pkColumns = Enumerable.Empty <Column>(); if (table.PrimaryKey != null) { pkColumns = table.PrimaryKey.Columns; } int nullableCount = 0; int colsNotInPkCount = 0; foreach (var col in table.Columns) { if (col.IsNullable) { nullableCount++; } else { if (!pkColumns.Contains(col)) { colsNotInPkCount++; } } } if (nullableCount > 0 && colsNotInPkCount == 0 && pkColumns.Count() <= 1) { issueCollector.ReportIssue(new Issue(this, this.DefaultSeverity.Value) { Name = "Too Many Nullable Columns", Description = new Description("All columns in table {0} are nullable (except the primary key columns)", table), ExtendedDescription = new Description("Consider adding not-null constraints."), Context = new TableContext(table) }); } else if (nullableCount > 2 && nullableCount > table.Columns.Count * allowedPercentage) { float percent = nullableCount * 100f / table.Columns.Count; issueCollector.ReportIssue(new Issue(this, HighPercentageProperty.Value) { Name = "Too Many Nullable Columns", Description = new Description("{1} percent of columns in table '{0}' are nullable. Consider adding more not-null constraints.", table, Math.Round(percent)), Context = new TableContext(table) }); } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { foreach (var table in database.Tables) { var colGroups = (from col in table.Columns let colName = Regex.Replace(col.ColumnName, @"\d+$", "") group col by colName into groups where groups.Count() > 1 select groups); foreach (var colGroup in colGroups) { int count = colGroup.Count(); var sorted = (from c in colGroup let match = Regex.Match(c.ColumnName, @"^.*?(\d+)$") let postfix = int.Parse((match.Groups.Count <= 1) ? "1" : match.Groups[1].Value) orderby postfix select new { Col = c, Postfix = postfix }); int?prev = null; foreach (var col in sorted) { if (prev == null) { prev = col.Postfix; } else { if (prev + 1 != col.Postfix && sorted.Count() > 3) { var issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Missing Column(s) in a Sequence of Columns"; issue.Context = new TableContext(table); var columnList = String.Join(", ", sorted.Select(s => s.Col.ColumnName)); if (columnList.Length > 20) { columnList = columnList.Substring(0, 20) + ".."; } issue.Description = new Description("Incomplete sequence of columns ({0}) in table {1}", columnList, table); issue.ExtendedDescription = new Description("The following sequence of columns is incomplete:\n{0}", sorted.Select(c => c.Col).ToList()); issueCollector.ReportIssue(issue); break; } prev = col.Postfix; } } } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { var tables = database.Tables.Where(t => t.PrimaryKey == null); foreach (var table in tables) { Issue issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Missing Primary Key"; issue.Context = new TableContext(table); issue.Description = new Description("Table {0} is missing a primary key", table); issueCollector.ReportIssue(issue); } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { var longColumNames = database.Columns.Where(c => c.ColumnName.Length > this.MaxLength.Value); foreach (var column in longColumNames) { var issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Too Long Column Name"; issue.Context = new ColumnContext(column); issue.Description = new Description("The column name '{0}' in table {1} is too long (length: {2})", column, column.Table, column.ColumnName.Length); issueCollector.ReportIssue(issue); } }
public override void Execute(DataTable table, IIssueCollector issueCollector, IProviderCollection providers) { if (table.Cardinality < MinRows.Value) { return; } var candidateColumns = table.Columns.Where(c => DataTypes.DataTypesLists.TextTypes().Contains(c.DataType)); Dictionary <Column, int> candidates = new Dictionary <Column, int>(); //column -> blank_count candidateColumns.ToList().ForEach(c => candidates.Add(c, 0)); using (var rowEnumerable = table.GetTableRowEnumerable()) foreach (var row in rowEnumerable) { foreach (var candidate in candidates.Keys.ToArray()) { Object val = row[candidate.ColumnName]; if (val is String && val.Equals(String.Empty)) { candidates[candidate] += 1; } } } foreach (var candidate in candidates) { float percentBlanks = ((float)candidate.Value / table.Cardinality) * 100; if (percentBlanks > Threshold.Value) { Issue issue = new Issue(this, this.Severity); issue.Name = "The Empty String Used to Represent Null"; issue.Context = new ColumnContext(candidate.Key); if (candidate.Key.IsNullable) { issue.Description = new Description("The nullable column '{0}' contains {2} rows of which {1} are the empty string. Consider using null", candidate.Key.ColumnName, candidate.Value, table.Cardinality); } else { issue.Description = new Description("The not-null column '{0}' contains {2} rows of which {1} are the empty string", candidate.Key.ColumnName, candidate.Value, table.Cardinality); issue.ExtendedDescription = new Description("Consider removing the not-null constraint and use null to represent non-existing values"); } issueCollector.ReportIssue(issue); } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { var columns = database.Tables.SelectMany(t => t.Columns).Where(c => c.ColumnName.Length < this.MinimalCharactersInName.Value); foreach (var column in columns) { issueCollector.ReportIssue(new Issue(this, this.DefaultSeverity.Value) { Name = "Too Short Column Name", Description = new Description("Column '{0}' has a shorter name than allowed in table {1}.", column, column.Table), Context = new ColumnContext(column) }); } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { var largeVarchars = (from col in database.Columns where col.DataType == DataType.VARCHAR && col.CharacterMaxLength > this.LargeVarcharLength.Value select col); foreach (var column in largeVarchars) { var issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Too Large Varchar Column"; issue.Context = new ColumnContext(column); issue.Description = new Description("Length of varchar column '{0}' in table '{1}' is {2}", column, column.Table, column.CharacterMaxLength); issueCollector.ReportIssue(issue); } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { foreach (var tbl in database.Tables) { foreach (var column in tbl.Columns.Where(c => c.IsNullable && c.Unique)) { issueCollector.ReportIssue(new Issue(this, this.DefaultSeverity.Value) { Name = "Nullable and Unique Column", Description = new Description("Column '{0}' in table '{1}' is both nullable and unique", column, column.Table), Context = new ColumnContext(column) }); } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { foreach (var table in database.Tables) { var textColumns = table.Columns.Where(c => c.DataType == DataType.VARCHAR && c.CharacterMaxLength == 0); foreach (var column in textColumns) { var issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Varchar Column With Length Zero"; issue.Context = new TableContext(table); issue.Description = new Description("Column '{0}' in table {1} is a varchar column with length zero", column, table); issueCollector.ReportIssue(issue); } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { var indices = database.Tables.SelectMany(t => t.Indices); foreach (var index in indices) { if (index.IsUnique && index.Columns.Count > this.MaxColumnsUnique.Value) { issueCollector.ReportIssue( new Issue(this, this.DefaultSeverity.Value) { Name = "Too Big Index", Context = new TableContext(index), Description = new Description("Too many columns in unique index '{0}' in table '{1}'", index.IndexName, index.Table), ExtendedDescription = new Description("Index '{0}' on the colums below has {2} columns which is more than maximally allowed in a unique/primary key index. {1}", index.IndexName, index.Columns.ToArray(), index.Columns.Count) }); } else if (index.Columns.Count > this.MaxColumns.Value) { issueCollector.ReportIssue( new Issue(this, this.DefaultSeverity.Value) { Name = "Too Big Index", Context = new TableContext(index), Description = new Description("Too many columns in index '{0}' in table '{1}'", index.IndexName, index.Table), ExtendedDescription = new Description("Index '{0}' on the colums below has {2} columns which is more than maximally allowed in an index. {1}", index.IndexName, index.Columns.ToArray(), index.Columns.Count) }); } else if (GetIndexSize(index) > this.MaxSize.Value) { var size = GetIndexSize(index); issueCollector.ReportIssue(new Issue(this, this.DefaultSeverity.Value) { Name = "Too Big Index", Context = new TableContext(index.Table), Description = new Description("Too big key size in index '{0}' in table '{1}'", index.IndexName, index.Table), ExtendedDescription = new Description("Index '{0}' on the colums below has an approximate size of {2} bytes. Reduce this index if possible. {1}", index.IndexName, index.Columns.ToArray(), size) }); } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { this.regex = new Regex(this.RegexConfig.Value, RegexOptions.Compiled); Match match; foreach (var schema in database.Schemas) { if (IsInvalid(schema.SchemaName, out match)) { issueCollector.ReportIssue(GetIssue(schema, match)); } foreach (var table in schema.Tables) { if (IsInvalid(table.TableName, out match)) { issueCollector.ReportIssue(GetIssue(table, match)); } foreach (var column in table.Columns) { if (IsInvalid(column.ColumnName, out match)) { issueCollector.ReportIssue(GetIssue(column, match)); } } foreach (var foreignKey in table.ForeignKeys) { if (IsInvalid(foreignKey.ForeignKeyName, out match)) { issueCollector.ReportIssue(GetIssue(foreignKey, match)); } } foreach (var index in table.Indices) { if (IsInvalid(index.IndexName, out match)) { issueCollector.ReportIssue(GetIssue(index, match)); } } } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { this.BuildReservedWordList(); var ignorePrimaryKeyNames = ((from table in database.Tables where table.PrimaryKey != null && table.PrimaryKey.PrimaryKeyName.ToLower() == "primary" select table).Count() == database.Tables.Where(p => p.PrimaryKey != null).Count()); foreach (var schema in database.Schemas) { /*if (CheckString(schema.SchemaName)) * issueCollector.ReportIssue(GetIssue(schema));*/ foreach (var table in schema.Tables) { if (CheckString(table.TableName)) { issueCollector.ReportIssue(GetIssue(table)); } foreach (var column in table.Columns) { if (CheckString(column.ColumnName)) { issueCollector.ReportIssue(GetIssue(column)); } } if (!ignorePrimaryKeyNames && table.PrimaryKey != null) { if (CheckString(table.PrimaryKey.PrimaryKeyName)) { issueCollector.ReportIssue(GetIssue(table.PrimaryKey)); } } foreach (var foreignKey in table.ForeignKeys) { if (CheckString(foreignKey.ForeignKeyName)) { issueCollector.ReportIssue(GetIssue(foreignKey)); } } } } }
public override void Execute(DataTable table, IIssueCollector issueCollector, IProviderCollection providers) { if (table.Cardinality < this.MinRows.Value) { return; } var candidates = table.QueryableColumns.Where(c => c.DefaultValue != null).ToList(); using (var rowEnumerable = table.GetTableRowEnumerable()) { foreach (var row in rowEnumerable) { foreach (var candidateColumn in candidates.ToArray()) { Object val = row[candidateColumn.ColumnName]; if (!val.ToString().Equals(candidateColumn.DefaultValue)) { candidates.Remove(candidateColumn); } } if (candidates.Count == 0) { break; } } } foreach (var candidateColumn in candidates) { if (candidateColumn.DefaultValue == "0" || candidateColumn.DefaultValue == "1") { continue; } Issue issue = new Issue(this, this.Severity); issue.Name = "All Values Equals the Default Value"; issue.Context = new ColumnContext(candidateColumn); issue.Description = new Description("All {0} values in column '{1}' equals default value '{2}'. Is the column necessary?", table.Cardinality, candidateColumn.ColumnName, candidateColumn.DefaultValue); issueCollector.ReportIssue(issue); } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { float allowedPercentage = AllowedPercentage.Value / 100f; foreach (var table in database.Tables) { var textColumns = table.Columns.Where(c => c.DataType == DataType.TEXT || c.DataType == DataType.LONGVARCHAR); float percent = ((float)textColumns.Count() / table.Columns.Count); if (textColumns.Count() > 2 && percent > allowedPercentage) { var issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Too Many Text Column(s)"; issue.Context = new TableContext(table); issue.Description = new Description("Overuse of data type text in table {0}", table); issue.ExtendedDescription = new Description("The table contains {0} columns and {1} of them are of data type text. Consider using a more appropiate data type for one or more of the following columns:\n{2}", table.Columns.Count, textColumns.Count(), GetTable(textColumns)); issueCollector.ReportIssue(issue); } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { foreach (var table in database.Tables.Where(t => t.PrimaryKey != null && t.UniqueConstraints.Count > 0)) { foreach (var uq in table.UniqueConstraints) { if (uq.Columns.All(c => table.PrimaryKey.Columns.Contains(c))) { System.Data.DataTable descTable = new System.Data.DataTable(); descTable.Columns.Add("PK Column(s)", typeof(String)); descTable.Columns.Add("UK Column(s)", typeof(String)); var row = descTable.NewRow(); row[0] = String.Join("\n", table.PrimaryKey.Columns.Select(c => c.ColumnName)); row[1] = String.Join("\n", uq.Columns.Select(c => c.ColumnName)); descTable.Rows.Add(row); if (uq.Columns.Count == table.PrimaryKey.Columns.Count) { issueCollector.ReportIssue(new Issue(this, this.DefaultSeverity.Value) { Context = new TableContext(table), Description = new Description("Columns in the primary key in table '{0}' is the same as the columns in a unique constraint", table), ExtendedDescription = new Description("{0}", descTable), Name = this.Name }); } else { issueCollector.ReportIssue(new Issue(this, this.DefaultSeverity.Value) { Context = new TableContext(table), Description = new Description("The columns in a unique constraint in table '{0}' is a subset of the primary key", table), ExtendedDescription = new Description("The primary key is a super key. Consider reducing the number of columns in the primary key.\n\n {0}", descTable), Name = this.Name }); } } } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { // Select only column of type 'char' and only if the max length is larger than 20 and // if the default value fills below 50% of the possible max length var columnsToCheck = (from col in database.Columns where col.DataType == DataType.CHAR && col.CharacterMaxLength > MaxLength.Value && col.DefaultValue != null && ((float)col.DefaultValue.Length / col.CharacterMaxLength) < (MinimumFill.Value / 100f) select col); foreach (var column in columnsToCheck) { var issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Inappropriate Length of Default Value For Char Column"; issue.Context = new ColumnContext(column); issue.Description = new Description("Inappropriate length of default value for char column '{0}' in table {1}", column, column.Table); issue.ExtendedDescription = new Description("The maximum character length of this column is {0}, but the default value has a length of {1}. Consider using varchar to save space.", column.CharacterMaxLength, column.DefaultValue.Length); issueCollector.ReportIssue(issue); } }
public override void Execute(DataTable table, IIssueCollector issueCollector, IProviderCollection providers) { var columns = table.Columns.Where(columnPredicate).Select(c => new StatCount { Column = c }).ToArray(); using (var rowEnumerable = table.GetTableRowEnumerable()) foreach (var row in rowEnumerable) { foreach (var col in columns) { var val = row[col.Column.ColumnName]; if (val is DBNull || val.ToString().Equals(String.Empty)) { continue; } var length = val.ToString().Length; col.Items += 1; col.MaxLength = Math.Max(col.MaxLength, length); col.TotalLength += length; } } foreach (var statCount in columns) { if (statCount.Items < this.MinRows.Value) { continue; } var avgLength = statCount.TotalLength / statCount.Items; if (avgLength < statCount.Column.CharacterMaxLength * 0.6 && statCount.MaxLength < statCount.Column.CharacterMaxLength * 0.8) { issueCollector.ReportIssue(new Issue(this, this.Severity) { Name = "Large Text Column not Filled", Context = new TableContext(table), Description = new Description("Column '{0}' contains on average {1} characters and the longest value has {2} characters. The maximum length of the column is declared to be {3} charaters", statCount.Column, avgLength, statCount.MaxLength, statCount.Column.CharacterMaxLength) }); } } }
public override void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers) { var tables = (from tab in database.Tables where tab.PrimaryKey != null select tab).ToList(); foreach (var table in tables) { for (int i = 0; i < table.PrimaryKey.Columns.Count; i++) { if (table.PrimaryKey.Columns[i] != table.Columns[i]) { var issue = new Issue(this, this.DefaultSeverity.Value); issue.Name = "Primary-Key Column(s) Not Positioned First"; issue.Description = new Description("The columns in primary key in table {0} should be positioned first, and in the same order as the primary-key index", table); issue.Context = new TableContext(table); issueCollector.ReportIssue(issue); break; } } } }
public override void Execute(DataTable table, IIssueCollector issueCollector, IProviderCollection providers) { var values = new Dictionary <Column, HashSet <Object> >(); var columns = (from fk in table.ForeignKeys from colPair in fk.ColumnPairs select colPair.FKColumn).ToList(); if (!columns.All(c => table.QueryableColumns.Contains(c))) { return; } columns.ForEach(c => values.Add(c, new HashSet <Object>())); using (var rowEnumerable = table.GetTableRowEnumerable()) foreach (var row in rowEnumerable) { foreach (var column in columns) { Object val = row[column.ColumnName]; if (values[column].Contains(val)) { return; } else { values[column].Add(val); } } } Issue issue = new Issue(this, this.Severity); issue.Name = "Unnecessary One-to-One Relational Table"; issue.Context = new TableContext(table); issue.Description = new Description("The data in table {0} represents a one-to-one relationship.", table); issueCollector.ReportIssue(issue); }