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 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 override void Finalize(Model.Database database, IProviderCollection providers)
{
var informationContent = providers.GetProvider <InformationContent>();
var fks = database.Tables.SelectMany(t => t.ForeignKeys);
DatabaseDictionary <TableID, List <JoinEdge> > dbJoinEdges = DictionaryFactory.CreateTableID <List <JoinEdge> >();
DatabaseDictionary <TableID, double> tableTotalEntropyTransfer = DictionaryFactory.CreateTableID <double>();
foreach (var tbl in database.Tables)
{
dbJoinEdges[tbl] = new List <JoinEdge>(4);
tableTotalEntropyTransfer[tbl] = 0;
}
foreach (var foreignKey in fks)
{
var pkColumns = (from cp in foreignKey.ColumnPairs
select cp.PKColumn).ToArray();
var fkColumns = (from cp in foreignKey.ColumnPairs
select cp.FKColumn).ToArray();
double fkEdgeEntropy;
if (foreignKey.IsSingleColumn)
{
fkEdgeEntropy = informationContent[foreignKey.FKColumn];
}
else
{
fkEdgeEntropy = informationContent.GetMultiColumnEntropy((DataTable)foreignKey.FKTable, fkColumns);
}
var pkEdgeEntropy = Math.Log(Math.Max(foreignKey.PKTable.Cardinality, 1), 2); // Primary key guarantees uniqueness across pkcolumns, hence entropy equals log of cardinality.
dbJoinEdges[foreignKey.PKTable].Add(new JoinEdge {
Table = foreignKey.FKTable, Columns = fkColumns, EdgeEntropy = fkEdgeEntropy
});
dbJoinEdges[foreignKey.FKTable].Add(new JoinEdge {
Table = foreignKey.PKTable, Columns = pkColumns, EdgeEntropy = pkEdgeEntropy
});
tableTotalEntropyTransfer[foreignKey.PKTable] += pkEdgeEntropy;
tableTotalEntropyTransfer[foreignKey.FKTable] += fkEdgeEntropy;
}
DatabaseDictionary <TableID, DatabaseDictionary <TableID, double> > pmatrix = DictionaryFactory.CreateTableID <DatabaseDictionary <TableID, double> >();
foreach (var tbl in database.Tables)
{
pmatrix[tbl] = DictionaryFactory.CreateTableID <double>();
}
foreach (var toTable in database.Tables)
{
var joinEdges = dbJoinEdges[toTable];
foreach (var joinEdge in joinEdges)
{
var fromTable = joinEdge.Table;
var columnsEntropy = joinEdge.EdgeEntropy;
var tableInformationContent = informationContent[fromTable];
var tableTotalTransfer = tableTotalEntropyTransfer[fromTable];
var todic = pmatrix[toTable];
if (!todic.ContainsKey(fromTable))
{
todic[fromTable] = 0;
}
if (tableInformationContent + tableTotalTransfer > 0)
{
todic[fromTable] += columnsEntropy / (tableInformationContent + tableTotalTransfer);
}
}
var toTableRow = pmatrix[toTable];
}
foreach (var keyRow in pmatrix.Keys)
{
double selfLoopValue = 1d;
foreach (var keyColumn in pmatrix.Keys)
{
var row = pmatrix[keyColumn];
if (row.ContainsKey(keyRow))
{
selfLoopValue -= row[keyRow];
}
}
if (selfLoopValue < 0)
{
}
pmatrix[keyRow][keyRow] = selfLoopValue;
}
DatabaseDictionary <TableID, double> importanceVector = DictionaryFactory.CreateTableID <double>();
DatabaseDictionary <TableID, double> calculateVector = DictionaryFactory.CreateTableID <double>();
foreach (var table in database.Tables)
{
importanceVector[table] = Math.Max(informationContent[table], 0);
}
for (int i = 0; i < 100; i++)
{
foreach (var table in importanceVector.Keys)
{
double newRank = 0;
var fromTables = pmatrix[table];
foreach (var fromTable in fromTables)
{
var fromRank = importanceVector[fromTable.Key];
newRank += fromRank * fromTable.Value;
}
calculateVector[table] = newRank;
}
{
var tmp = calculateVector;
calculateVector = importanceVector;
importanceVector = tmp;
}
}
var totalEntropy = importanceVector.Values.Sum();
foreach (var k in importanceVector.Keys)
{
if (importanceVector[k] < 0)
{
}
if (totalEntropy == 0)
{
importanceVector[k] = 100f / database.Tables.Count;
}
else
{
importanceVector[k] *= 100f / totalEntropy;
}
}
this._importanceVector = importanceVector;
}
public override void Execute(Database database, IProviderCollection providers)
{
double damping = 0.85;
DatabaseDictionary <TableID, DoubleWrapper> ranks = DictionaryFactory.CreateTableID <DoubleWrapper>();
DatabaseDictionary <TableID, DoubleWrapper> rankCalculator = DictionaryFactory.CreateTableID <DoubleWrapper>();
DatabaseDictionary <TableID, Table[]> neighborMatrix = DictionaryFactory.CreateTableID <Table[]>();
var initialRank = 1f / database.Tables.Count;
foreach (var table in database.Tables)
{
ranks.Add(table, new DoubleWrapper {
Value = 1
});
var referencedBy = new List <Table>();
foreach (var fkTable in table.ReferencedBy)
{
foreach (var foreignKey in fkTable.ForeignKeys)
{
if (foreignKey.PKTable.Equals(table))
{
referencedBy.Add(fkTable);
}
}
}
neighborMatrix.Add(table, referencedBy.ToArray());
}
foreach (var tableID in ranks.Keys)
{
rankCalculator[tableID] = new DoubleWrapper {
Value = 0
};
ranks[tableID] = new DoubleWrapper {
Value = 0
};
}
for (int i = 0; i < 100; i++)
{
double error = 0;
foreach (var tableID in ranks.Keys)
{
double value = 0;
var references = neighborMatrix[tableID];
foreach (var reference in references)
{
var referenceRank = ranks[reference];
value += referenceRank.Value / reference.ForeignKeys.Count;
}
double newRank;
rankCalculator[tableID].Value = newRank = value * damping + initialRank * (1 - damping);
error += Math.Abs(ranks[tableID].Value - newRank);
}
{
var tmp = rankCalculator;
rankCalculator = ranks;
ranks = tmp;
}
if (error < 0.001)
{
break;
}
}
double sum = 0;
foreach (var key in ranks.Keys)
{
sum += ranks[key].Value;
}
foreach (var key in ranks.Keys)
{
_ranks[key] = ranks[key].Value / sum * 100;
}
}
public static Database DatabaseFactory(Extractor extractor, IEnumerable <DBLint.DataAccess.DBObjects.Schema> selectedSchemas, IEnumerable <DBLint.DataAccess.DBObjects.TableID> ignoredTables)
{
var database = new Database(extractor.DatabaseName, extractor.Database);
var ignoredDictionary = DictionaryFactory.CreateTableID <TableID>();
foreach (var ignoredTable in ignoredTables)
{
var tblid = new TableID(extractor.DatabaseName, ignoredTable.SchemaName, ignoredTable.TableName);
ignoredDictionary.Add(tblid, tblid);
}
using (var db = database)
{
var dbSchemas = extractor.Database.GetSchemas();
foreach (var dbSchema in dbSchemas)
{
using (var schema = new Schema(db.DatabaseName, dbSchema.SchemaName))
{
if (!selectedSchemas.Any(p => p.Equals(dbSchema)))
{
continue;
}
schema.Database = db;
db._schemas.Add(schema);
#region Table and table columns
var dbTables = extractor.Database.GetTables(schema.SchemaName);
var tables = from dbTable in dbTables
orderby dbTable.TableName
select dbTable;
foreach (var dbTable in tables)
{
var table = new DataTable(db.DatabaseName, schema.SchemaName, dbTable.TableName, extractor.Database);
if (ignoredDictionary.ContainsKey(table))
{
database.IgnoredTables.Add(table);
continue;
}
schema._tables.Add(table);
db.tableDictionary.Add(table, table);
table.Database = db;
table.Schema = schema;
}
var dbColumns = extractor.Database.GetColumns(schema.SchemaName);
var columnID = new ColumnID(extractor.DatabaseName, schema.SchemaName, null, null);
foreach (var dbColumn in dbColumns)
{
columnID.TableName = dbColumn.TableName;
columnID.ColumnName = dbColumn.ColumnName;
if (db.tableDictionary.ContainsKey(columnID))
{
var column = new Column(db.DatabaseName, schema.SchemaName, dbColumn.TableName, dbColumn.ColumnName);
var table = db.tableDictionary[column];
table._columns.Add(column);
column.DataType = dbColumn.DataType;
column.DefaultValue = dbColumn.DefaultValue;
column.OrdinalPosition = dbColumn.OrdinalPosition;
column.CharacterMaxLength = dbColumn.CharacterMaxLength;
column.IsNullable = dbColumn.IsNullable;
column.NumericPrecision = dbColumn.NumericPrecision;
column.NumericScale = dbColumn.NumericScale;
column.Table = table;
column.Schema = schema;
column.Database = db;
column.IsSequence = dbColumn.IsSequence;
column.IsDefaultValueAFunction = dbColumn.DefaultIsFunction;
}
}
#endregion
#region Views and view columns
var dbViews = extractor.Database.GetViews(schema.SchemaName);
var views = from dbView in dbViews
orderby dbView.ViewName
select dbView;
foreach (var dbView in views)
{
var view = new View(db.DatabaseName, schema.SchemaName, dbView.ViewName);
schema._views.Add(view);
db.viewDictionary.Add(view, view);
view.Database = db;
view.Schema = schema;
}
var dbViewColumns = extractor.Database.GetViewColumns(schema.SchemaName);
var viewColumnID = new ViewColumnID(extractor.DatabaseName, schema.SchemaName, null, null);
foreach (var dbViewColumn in dbViewColumns)
{
viewColumnID.ViewName = dbViewColumn.ViewName;
viewColumnID.ColumnName = dbViewColumn.ColumnName;
if (db.viewDictionary.ContainsKey(viewColumnID))
{
var viewColumn = new ViewColumn(db.DatabaseName, schema.SchemaName, dbViewColumn.ViewName, dbViewColumn.ColumnName);
var view = db.viewDictionary[viewColumn];
view._columns.Add(viewColumn);
viewColumn.Database = db;
viewColumn.Schema = schema;
viewColumn.View = view;
viewColumn.DataType = dbViewColumn.DataType;
viewColumn.DefaultValue = dbViewColumn.DefaultValue;
viewColumn.OrdinalPosition = dbViewColumn.OrdinalPosition;
viewColumn.CharacterMaxLength = dbViewColumn.CharacterMaxLength;
viewColumn.IsNullable = dbViewColumn.IsNullable;
viewColumn.NumericPrecision = dbViewColumn.NumericPrecision;
viewColumn.NumericScale = dbViewColumn.NumericScale;
viewColumn.Privileges = dbViewColumn.Privileges;
}
}
#endregion
// Adding functions
var functions = extractor.Database.GetFunctions(schema.SchemaName);
foreach (var function in functions)
{
var f = new Function(
database.DatabaseName,
function.SchemaName,
function.RoutineName);
f.Database = database;
f.Schema = schema;
schema._functions.Add(f);
}
var fParameters = extractor.Database.GetFunctionsParameters(schema.SchemaName);
foreach (var fParameter in fParameters)
{
var fp = new Parameter(database.DatabaseName, fParameter.SchemaName, fParameter.RoutineName, fParameter.ParameterName);
fp.Database = schema.Database;
fp.Schema = schema;
fp.DataType = fParameter.DataType;
fp.Direction = fParameter.Direction;
fp.CharacterMaxLength = fParameter.CharacterMaxLength;
fp.NumericPrecision = fParameter.NumericPrecision;
fp.NumericScale = fParameter.NumericScale;
fp.OrdinalPosition = fParameter.OrdinalPosition;
var tmpF = schema._functions.Where(f => f.FunctionName.Equals(fp.RoutineName)).FirstOrDefault();
if (tmpF != null)
{
fp.Routine = tmpF;
tmpF._parameters.Add(fp);
}
}
// Adding stored procedures
var storedProcedures = extractor.Database.GetStoredProcedures(schema.SchemaName);
foreach (var storedProcedure in storedProcedures)
{
var sp = new StoredProcedure(
database.DatabaseName,
storedProcedure.SchemaName,
storedProcedure.RoutineName);
sp.Database = database;
sp.Schema = schema;
schema._storedProcedures.Add(sp);
}
var parameters = extractor.Database.GetStoredProceduresParameters(schema.SchemaName);
foreach (var parameter in parameters)
{
var p = new Parameter(database.DatabaseName, parameter.SchemaName, parameter.RoutineName, parameter.ParameterName);
p.Database = schema.Database;
p.Schema = schema;
p.DataType = parameter.DataType;
p.Direction = parameter.Direction;
p.CharacterMaxLength = parameter.CharacterMaxLength;
p.NumericPrecision = parameter.NumericPrecision;
p.NumericScale = parameter.NumericScale;
p.OrdinalPosition = parameter.OrdinalPosition;
var tmpSp = schema._storedProcedures.Where(sp => sp.StoredProcedureName.Equals(p.RoutineName)).FirstOrDefault();
if (tmpSp != null)
{
p.Routine = tmpSp;
tmpSp._parameters.Add(p);
}
}
}
}
}
AddForeignKeys(extractor, database);
AddPrimaryKeys(extractor, database);
AddIndices(extractor, database);
AddUniqueConstraints(extractor, database);
foreach (var tbl in database.Tables)
{
tbl.Dispose();
}
foreach (var view in database.Views)
{
view.Dispose();
}
AddCardinalities(database.tableDictionary, extractor, selectedSchemas);
database.Escaper = Escaper.GetEscaper(extractor.Database.DBMS);
database.DBMS = extractor.Database.DBMS;
return(database);
}