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