public MigrationRunner(IOptions <MigrationRunnerOptions> options,
IProviderCollection providerCollection, IMigrationFilesLoader migrationFilesLoader)
{
_options = options;
_providerCollection = providerCollection;
_migrationFilesLoader = migrationFilesLoader;
}
public CommandMigrationRunner(ILogger <CommandMigrationRunner> logger, IProviderCollection providerCollection,
MigrationOptionsLoader migrationOptionsLoader)
{
_logger = logger;
_providerCollection = providerCollection;
_migrationOptionsLoader = migrationOptionsLoader;
}
public static IIdentifiedSource ToXmlSettings(this string text, IProviderCollection providers)
{
var settings = new XmlStringSettings(text);
settings.Providers = providers;
return(settings);
}
public MigrationOptionsLoader(ILogger <MigrationOptionsLoader> logger,
IProviderCollection providerCollection,
IConnectionStringsProcessorCollection connectionStringsProcessorCollection)
{
_logger = logger;
_providerCollection = providerCollection;
_connectionStringsProcessorCollection = connectionStringsProcessorCollection;
}
public override void Execute(Database database, IProviderCollection providers)
{
ITableImportance importance = providers.GetProvider <TableDataImportance>();
if (importance == null)
{
importance = providers.GetProvider <PageRank>();
}
this._importanceBackend = importance;
}
public override void Execute(DataTable table, IProviderCollection providers)
{
if (table.Cardinality <= 1)
{
return;
}
double tableInformationContent = Math.Log(table.Cardinality, 2);
foreach (var column in table.Columns.Where(c => validTypes.Contains(c.DataType)))
{
double res = GetMultiColumnEntropy(table, column);
if (res < 0)
{
}
_columnInformationContent[column] = res;
tableInformationContent += res;
}
this._tableInformationContent[table] = tableInformationContent;
}
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)
});
}
}
}
}
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 abstract void Execute(Database database, IIssueCollector issueCollector, IProviderCollection providers);
public ProviderLoader(IProviderCollection providers)
{
_providers = providers;
}
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)
});
}
}
private void Write(String dirName, DatabaseLint dblint)
{
this.scoring = new IScoringImpl();
this.scoring.CalculateScores(dblint);
if (DBLint.Settings.IsNormalContext)
{
//Save run for incremental viewing
//File name is a timestamp
DateTime now = DateTime.Now;
String fileName = String.Format("{0}{1}{2}{3}{4}{5}.xml", now.Year, now.Month, now.Day, now.Hour,
now.Minute, now.Second);
//Folder, i.e.: runs/dbname/
String folder = Settings.INCREMENTAL_FOLDER + "testtest"; // dblint.DatabaseModel.DatabaseName;
String filePath = folder + "/" + fileName;
if (!Directory.Exists(folder))
{
Directory.CreateDirectory(folder);
}
//Create run
DBLint.IncrementalRuns.Run run = new IncrementalRuns.Run(dblint.DatabaseModel, dblint.IssueCollector, scoring.GetScores());
//Write run
using (FileStream writer = new FileStream(filePath, FileMode.Create))
{
DataContractSerializer ser = new DataContractSerializer(typeof(DBLint.IncrementalRuns.Run));
ser.WriteObject(writer, run);
writer.Flush();
}
}
DirectoryInfo dir = new DirectoryInfo(dirName);
int tableNameCounter = 1;
foreach (Table table in dblint.DatabaseModel.Tables)
{
String tName = "table" + tableNameCounter.ToString();
this.tableNames.Add(table, tName);
this.tableFiles.Add(table, "tables/" + tName + ".html");
tableNameCounter++;
}
this.dblint = dblint;
this.formatter = new HTMLDescriptionFormatter(this.tableFiles);
IssueCollector issues = dblint.IssueCollector;
//Create result directory if it does not exist
if (!dir.Exists)
{
dir.Create();
}
VelocityContext context = new VelocityContext();
context.Put("db", dblint.DatabaseModel);
context.Put("totalScore", this.scoring.GetScore());
context.Put("issuesTotal", issues.Count());
context.Put("rulesExecuted", this.getRulesExecuted());
context.Put("ruleTypes", this.getRuleTypes());
context.Put("formatter", this.formatter);
context.Put("HTMLBuilder", this);
context.Put("summaries", this.dblint.ExecutionSummary);
context.Put("executionTime", this.formatTimeSpan(this.dblint.ExecutionSummary.ExecutionTime));
//Pagerank
IProviderCollection providers = dblint.RuleController.ProviderCollection;
var rank = providers.GetProvider <DBLint.Rules.SchemaProviders.ImportanceProvider>();
//List all tables
var tables = (from t in dblint.DatabaseModel.Tables
select new
{
Table = t,
Name = t.TableName,
IssueCount = issues.GetIssues(t).Count(),
Score = this.scoring.GetScore(t),
Importance = Math.Round(rank[t], 1)
}).ToList();
context.Put("tables", tables);
//Bottom tables
var bottom = tables.OrderBy(t => t.Score).Take(5).ToList();
context.Put("bottomTables", bottom);
int groupId = 0; //Used in the template to identify a group of issues
//Group issues by name
var issueGroups = (from i in issues
group i by i.Name into g
orderby g.First().Severity
select new
{
Name = g.Key,
Count = g.Count(),
Issues = g,
GroupID = ++groupId,
Severity = g.First().Severity
}).ToList();
context.Put("issueGroups", issueGroups);
//Put issueGroups into severity groups
var severityGroups = (from issueGroup in issueGroups
group issueGroup by issueGroup.Severity into g
orderby g.First().Severity
select new
{
Severity = g.First().Severity,
IssueGroups = g
}
);
context.Put("severityGroups", severityGroups);
//Incremental runs list
var diffs = new List <DBLint.IncrementalRuns.Diff>();
if (DBLint.Settings.IsNormalContext)
{
//Incremental runs
try
{
var runs = DBLint.IncrementalRuns.Run.GetRuns(dblint.DatabaseModel.DatabaseName, 5).ToList();
for (int i = 1; i < runs.Count; i++)
{
var diff = new DBLint.IncrementalRuns.Diff();
diff.Compare(runs[i], runs[i - 1]);
diffs.Add(diff);
}
}
catch { }
context.Put("diffs", diffs);
}
//Create template for the main html page
Template template = Velocity.GetTemplate("mainpage.vm");
//Create outputstream for the main page
TextWriter htmlOut = new StreamWriter(Path.Combine(dir.FullName, "mainpage.html"));
//Write template
template.Merge(context, htmlOut);
htmlOut.Close();
//Write issue groups
String issuePath = Path.Combine(dir.FullName, "issues");
if (!Directory.Exists(issuePath))
{
Directory.CreateDirectory(issuePath);
}
Template issueGroupTemplate = Velocity.GetTemplate("issuegroup.vm");
formatter.PathPrefix = "../";
foreach (var g in issueGroups)
{
context.Put("groupIssues", g.Issues);
TextWriter issueOut = new StreamWriter(Path.Combine(issuePath, g.GroupID.ToString() + ".html"));
issueGroupTemplate.Merge(context, issueOut);
issueOut.Close();
}
if (DBLint.Settings.IsNormalContext)
{
//Write diffs/increments to files:
String incPath = Path.Combine(dir.FullName, "increments");
if (!Directory.Exists(incPath))
{
Directory.CreateDirectory(incPath);
}
Template incrementTemplate = Velocity.GetTemplate("increment.vm");
int diffId = 0;
foreach (var diff in diffs)
{
diffId++;
context.Put("diff", diff);
TextWriter incOut = new StreamWriter(Path.Combine(incPath, diffId.ToString() + ".html"));
incrementTemplate.Merge(context, incOut);
incOut.Close();
}
}
formatter.PathPrefix = "";
writeTableViews(dirName);
}
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)
{
foreach (var schema in database.Schemas)
{
var columnGroups = (from col in schema.Columns
where col.DataType == DataType.VARCHAR && col.CharacterMaxLength > minLength.Value
orderby col.CharacterMaxLength ascending
group col by col.CharacterMaxLength into g
select g).ToList();
var group = new List <List <Column> >();
for (int i = 0; i < columnGroups.Count - 1; i++)
{
if (group.Count == 0)
{
group.Add(columnGroups[i].ToList());
}
if (Math.Abs(columnGroups[i].First().CharacterMaxLength - columnGroups[i + 1].First().CharacterMaxLength) <= threshold.Value)
{
group.Add(columnGroups[i + 1].ToList());
}
else
{
if (group.Count > 1)
{
issueCollector.ReportIssue(GetIssue(group));
}
group.Clear();
}
}
if (group.Count > 1)
{
issueCollector.ReportIssue(GetIssue(group));
}
}
}
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(DataTable table, IIssueCollector issueCollector, IProviderCollection providers)
{
var informationContent = providers.GetProvider <InformationContent>();
if (table.PrimaryKey == null || table.Cardinality < MinRows.Value)
{
return;
}
var pkcolumns = table.PrimaryKey.Columns;
var escaper = table.Database.Escaper;
// Heuristic: Only check columns storing a lot of entropy
var entropyOrderedCOlumns = pkcolumns.OrderByDescending(col => informationContent[col]).ToArray();
double maxPossibleCardinality = 1;
var currentColumns = new List <Column>(entropyOrderedCOlumns.Length);
var first = true;
var currentColumnsString = new StringBuilder();
foreach (var col in entropyOrderedCOlumns)
{
currentColumns.Add(col);
if (currentColumns.Count == entropyOrderedCOlumns.Length)
{
break; // Last column added. It is known to be a key.
}
maxPossibleCardinality *= informationContent[col];
if (table.Cardinality - maxPossibleCardinality > 0.2f)
{
continue; // If not enough entropy to generate a higher card. no need to query the data.
}
var escapedCol = escaper.Escape(col);
if (first)
{
currentColumnsString.AppendFormat("{0}", escapedCol);
first = false;
}
else
{
currentColumnsString.AppendFormat(", {0}", escapedCol);
}
var query = string.Format(@"SELECT COUNT(*)
FROM ( SELECT COUNT(*) AS rowcnt
FROM {0}
GROUP BY {1}
) AS exp1
WHERE rowcnt > 1 ", escaper.Escape(table), currentColumnsString.ToString());
var res = table.QueryTable(query);
if (res is DBNull)
{
break;
}
var num = Convert.ToInt32(res);
if (num == 0)
{
issueCollector.ReportIssue(new Issue(this, this.Severity)
{
Name = "Defined Primary Key is not a Minimal Key",
Context = new TableContext(table),
Description = new Description("Primary key for table {0}, is a superkey.", table),
ExtendedDescription = new Description("Columns {0} are enough to uniquely identify a tuple. Currently used are {1}", currentColumns, table.PrimaryKey.Columns),
});
break;
}
}
}
public override void Execute(DataTable table, IIssueCollector issueCollector, IProviderCollection providers)
{
if (table.Cardinality < MinRows.Value)
{
return;
}
//Find candidates (text columnns)
var textColumns = table.Columns.Where(c => c.DataType == DataTypes.DataType.VARCHAR ||
c.DataType == DataTypes.DataType.CHAR).ToList();
var fk_columns = (from fks in table.ForeignKeys
from colpair in fks.ColumnPairs
select colpair.FKColumn);
textColumns = textColumns.Where(c => fk_columns.Contains(c) == false).ToList();
List <Candidate> candidates = textColumns.Select(c => new Candidate(c)).ToList();
//Prune candidates
using (var rowEnumerable = table.GetTableRowEnumerable())
foreach (DataRow row in rowEnumerable)
{
foreach (var candidate in candidates.ToArray())
{
Object value = row[candidate.Column.ColumnName];
if (value is DBNull || (value is String && ((String)value) == ""))
{
continue;
}
if (candidate.Values.ContainsKey(value))
{
continue;
}
else
{
candidate.Values.Add(value, 0);
}
if (candidate.Values.Count > this.MaximumValues.Value)
{
candidates.Remove(candidate);
}
}
}
//Report issues
foreach (var candidate in candidates)
{
if (candidate.Values.Count < MinimumValues.Value)
{
break;
}
System.Data.DataTable valuesTable = new System.Data.DataTable();
valuesTable.Columns.Add("Values", typeof(String));
foreach (Object value in candidate.Values.Keys)
{
var row = valuesTable.NewRow();
row[0] = value.ToString();
valuesTable.Rows.Add(row);
}
Issue issue = new Issue(this, this.Severity);
issue.Name = "Column Values from a Small Domain";
issue.Description = new Description("Values in column '{0}' indicates that an enum could be appropriate", candidate.Column.ColumnName);
issue.ExtendedDescription = new Description("{0} rows checked.\n\n{1}", table.Cardinality, 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(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(DataTable table, IIssueCollector issueCollector, IProviderCollection providers)
{
if (table.Cardinality < MinValues.Value)
{
return;
}
var columns = table.QueryableColumns.Where(c => DataTypesLists.TextTypes().Contains(c.DataType));
if (columns.Count() == 0)
{
return;
}
var testColumns = columns.Select(c => new TestColumn(c)).ToList();
using (var rowEnumerable = table.GetTableRowEnumerable())
foreach (var row in rowEnumerable)
{
foreach (var testColumn in testColumns)
{
Object val = row[testColumn.Column.ColumnName];
if (val is String)
{
String str = val as String;
if (str.Length < 1)
{
continue;
}
testColumn.ValueCount += 1;
if (str[0] == char.ToLower(str[0]))
{
testColumn.LowercaseStart += 1;
}
else
{
testColumn.UppercaseStart += 1;
}
}
}
}
foreach (var testColumn in testColumns)
{
if (testColumn.ValueCount <= this.MinValues.Value)
{
continue;
}
float lowerPercent = ((float)testColumn.LowercaseStart / testColumn.ValueCount) * 100;
float upperPercent = ((float)testColumn.UppercaseStart / testColumn.ValueCount) * 100;
float lowest = Math.Min(lowerPercent, upperPercent);
if (lowest < Threshold.Value && lowest != 0)
{
Issue issue = new Issue(this, this.Severity);
issue.Name = "Inconsistent Casing of First Character in Text Column";
issue.Context = new ColumnContext(testColumn.Column);
issue.Description = new Description("Inconsistent casing of first character in column '{0}'", testColumn.Column);
issue.ExtendedDescription = new Description("{0}% starts with lowercase\n{1}% starts with uppercase", lowerPercent, upperPercent);
issueCollector.ReportIssue(issue);
}
}
}
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)
{
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(DataTable table, IIssueCollector issueCollector, IProviderCollection providers)
{
var textColumns = table.Columns.Where(c => DataTypes.DataTypesLists.TextTypes().Contains(c.DataType));
var candidates = textColumns.Select(c => new Candidate(c)).ToList();
using (var rowEnumerable = table.GetTableRowEnumerable())
foreach (var row in rowEnumerable)
{
foreach (var candidate in candidates.ToArray())
{
Object val = row[candidate.Column.ColumnName];
if (val is DBNull || (val is String) == false)
{
continue;
}
String str = (String)val;
ValueType type = Classifier.Classify(str);
if (type != ValueType.Date && type != ValueType.Float && type != ValueType.Int)
{
candidates.Remove(candidate);
continue;
}
if (candidate.ValuesFound == 0)
{
candidate.Type = type;
}
if (type != candidate.Type)
{
candidates.Remove(candidate);
}
else
{
candidate.ValuesFound += 1;
}
}
if (candidates.Count == 0)
{
break;
}
}
foreach (var candidate in candidates)
{
if (candidate.ValuesFound < this.minValues.Value)
{
continue;
}
Issue i = new Issue(this, this.Severity);
i.Name = "Numbers or Dates Stored in Varchar Column";
i.Context = new ColumnContext(candidate.Column);
i.Description = new Description("The varchar column '{0}' is used to store values of type '{1}'", candidate.Column, candidate.Type.ToString());
issueCollector.ReportIssue(i);
}
}
public virtual void Finalize(Database database, IProviderCollection providers)
{
}
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 colGroup
where colGroup.Count() > 1
select new { Columns = colGroup.ToList() }).ToList();
var inconsistentGroups = (from colGroup in colGroups
where !colGroup.Columns.TrueForAll(c => c.DataType == colGroup.Columns.First().DataType)
select colGroup.Columns).ToList();
foreach (var incGroup in inconsistentGroups)
{
var dt = new DataTable();
dt.Columns.Add("Column Name");
dt.Columns.Add("Data Type");
foreach (var col in incGroup.Select(c => c))
{
var row = dt.NewRow();
row[0] = col.ColumnName;
row[1] = col.DataType;
dt.Rows.Add(row);
}
var issue = new Issue(this, this.DefaultSeverity.Value);
issue.Name = this.Name;
issue.Context = new TableContext(table);
issue.Description = new Description("Sequence of related columns in table '{0}' do not have the same data type", table);
issue.ExtendedDescription = new Description("Inconsistent data types for the following columns:\n{0}", dt);
issueCollector.ReportIssue(issue);
}
}
}
public static IIdentifiedSource ToXmlSettings(this string text, IProviderCollection providers)
{
var settings = new XmlStringSettings(text);
settings.Providers = providers;
return settings;
}
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);
}
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)
{
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(Database database, IIssueCollector issueCollector, IProviderCollection providers)
{
foreach (var schema in database.Schemas)
{
if (!schema.Tables.Any(t => t.ForeignKeys.Count > 0))
{
Issue s = new Issue(this, Severity.Critical);
s.Name = "No Foreign Keys";
s.Description = new Description("The schema '{0}' has no foreign keys", schema.SchemaName);
s.Context = new SchemaContext(schema);
issueCollector.ReportIssue(s);
continue;
}
DatabaseDictionary <TableID, Table> seenTables = DictionaryFactory.CreateTableID <Table>();
DatabaseDictionary <TableID, Table> notSeen = DictionaryFactory.CreateTableID <Table>();
foreach (var t in schema.Tables)
{
notSeen.Add(t, t);
}
List <List <Table> > clusters = new List <List <Table> >();
while (notSeen.Count > 0)
{
var table = notSeen.First().Value;
var cluster = new List <Table>(schema.Tables.Count);
BrowseTables(table, seenTables, notSeen, cluster);
clusters.Add(cluster);
}
foreach (var cluster in clusters)
{
if (cluster.Count == 1)
{
Issue issue = new Issue(this, this.DefaultSeverity.Value)
{
Context = new TableContext(cluster[0]),
Name = "Table Island",
Description = new Description("Table {0} does not reference anything and is not referenced by anything", cluster[0]),
};
issueCollector.ReportIssue(issue);
}
else
{
if (cluster.Count > schema.Tables.Count * this.MaxFractionTables.Value / 100f || schema.Tables.Count > this.MaxTableIslandCount.Value)
{
continue;
}
string tableList = String.Join(", ", cluster.Select(c => c.TableName));
if (tableList.Length > 20)
{
tableList = tableList.Substring(0, 20) + "...";
}
Issue issue = new Issue(this, LargeTableIslandSeverity.Value)
{
Context = IssueContext.Create(cluster),
Name = "Table Island",
Description = new Description("There is a table island containing {0} tables: {1}", cluster.Count, tableList),
ExtendedDescription = new Description("Tables: {0}", cluster),
};
issueCollector.ReportIssue(issue);
}
}
}
}
public abstract void Execute(DataTable table, IProviderCollection providers);
