public override void BeginDatabaseVisit(Database database)
{
foreach (Rule rule in _rules)
{
_report.SetCurrentRule(rule);
rule.CheckDatabase(_report, database);
}
}
static void CheckRules(Database database)
{
RuleVisitor visitor = new RuleVisitor();
Assembly thisAssembly = Assembly.GetExecutingAssembly();
PopulateRuleVisitorFromAssembly(visitor, thisAssembly);
database.Visit(visitor);
}
internal Table(Database database, ObjectName name)
{
_database = database;
_name = name;
_parsedName = new ParsedTableIdentifier(name.Name);
_tableConstraints = new Dictionary<ObjectName, TableConstraint>();
_foreignKeyConstraints = new Dictionary<ObjectName, ForeignKeyConstraint>();
_foreignKeyDependencies = new List<ForeignKeyConstraint>();
}
static string GetFilename(Database database, SqlConnection connection, string extension)
{
string identity = IdentifyCompany(database, connection);
string date = IdentifyLastUseDate(database, connection);
string fileNameBase = string.Format("Database Dump, {0} ({1}, {2})",
database.Name, identity, date);
string filteredBase = _invalidFileNameComponent.Replace(fileNameBase, "");
return string.Format("{0}.{1}", filteredBase, extension.TrimStart('.'));
}
public static string IdentifyLastUseDate(Database database, SqlConnection connection)
{
if (database.TablesByName.ContainsKey(new ObjectName("dbo", "Booking_1")))
{
Table bookingTable = database.TablesByName[new ObjectName("dbo", "Booking_1")];
if (bookingTable.Columns.ContainsKey("Job_BookingTime"))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText =
"SELECT MAX(Job_BookingTime) FROM dbo.Booking_1";
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read() && !reader.IsDBNull(0))
{
return reader.GetDateTime(0).ToString("d MMMM yyyy");
}
}
}
}
}
return "Unknown Date";
}
public static string IdentifyCompany(Database database, SqlConnection connection)
{
if (database.TablesByName.ContainsKey(new ObjectName("dbo", "Company")))
{
Table companyTable = database.TablesByName[new ObjectName("dbo", "Company")];
if (companyTable.Columns.ContainsKey("Comp_ID") && companyTable.Columns.ContainsKey("Comp_Name"))
{
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText =
"SELECT TOP 1 Comp_Name FROM dbo.Company ORDER BY Comp_ID ASC";
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read() && !reader.IsDBNull(0))
{
return Capitalisation.ToNameCase(reader.GetString(0)).Trim();
}
}
}
}
}
return "Unknown Company";
}
public static void DumpSerialized(Database database, SqlConnection connection)
{
string fileName = GetFilename(database, connection, "databasecop");
using (Stream stream = new FileStream(fileName, FileMode.Create))
{
BinaryFormatter formatter = new BinaryFormatter();
formatter.Serialize(stream, database);
}
}
internal TableConstraint BuildTableConstraint(Database database)
{
if (!(_constraintType == ConstraintType.PrimaryKey ||
_constraintType == ConstraintType.Unique) ||
_foreignUniqueConstraintName != null)
{
throw new InvalidOperationException();
}
if (!database.TablesByName.ContainsKey(_tableName))
{
throw new InvalidOperationException("A table referenced in a constraint was not found in the table set.");
}
Table table = database.TablesByName[_tableName];
return new TableConstraint(_constraintName, table, _constraintType, ResolveColumns(_constraintColumns, table));
}
public static void CreateDependenciesGraph(Database database, Set<Table> tableSubset)
{
using (StreamWriter writer = new StreamWriter("C:\\test.viz"))
{
writer.WriteLine("digraph database {");
writer.WriteLine(" node [fontname=Helvetica];");
writer.WriteLine(" node [shape=none, fontcolor=white, style=filled];");
foreach (Table table in database.TablesByName.Values)
{
if (tableSubset != null && !tableSubset.Contains(table)) continue;
ParsedTableIdentifier tableIdentifier = new ParsedTableIdentifier(table.Name.Name);
string nodeStyle = "black";
if (tableIdentifier.Prefix == "common") nodeStyle = "fillcolor=yellow4";
if (tableIdentifier.Prefix == "courier") nodeStyle = "fillcolor=darkolivegreen";
if (tableIdentifier.Prefix == "haulage") nodeStyle = "fillcolor=darkgoldenrod4";
string nodeSizing = "";
int connectionCount = table.ForeignKeyConstraintsByName.Count;
foreach (TableConstraint constraint in table.TableConstraints)
{
connectionCount += constraint.RelatedForeignKeysByName.Count;
}
double width = 1.5 + 1.5 * connectionCount / 3.0;
double height = 0.5 + 0.5 * connectionCount / 3.0;
if (connectionCount > 3) nodeSizing = string.Format(", width={0:0.0}, height={1:0.0}", width, height);
writer.WriteLine(" \"{0}\" [{1}{2}]", tableIdentifier.Value, nodeStyle, nodeSizing);
foreach (ForeignKeyConstraint foreignKey in table.ForeignKeyConstraints)
{
if (tableSubset != null && !tableSubset.Contains(foreignKey.Table)) continue;
ParsedTableIdentifier foreignKeyTableName = new ParsedTableIdentifier(foreignKey.Table.Name.Name);
ParsedTableIdentifier primaryKeyTableName = new ParsedTableIdentifier(foreignKey.UniqueConstraint.Table.Name.Name);
string edgeStyle = "color=gray15";
string arrowStyle = "diamondnormal";
if (foreignKey.IsOneToOne) arrowStyle = "normal";
if (foreignKey.IsDisabled ?? false) edgeStyle = "style=dotted, color=gray15";
if (foreignKey.UpdateRule == ForeignKeyRule.Cascade) edgeStyle = "color=red4";
writer.WriteLine(" \"{0}\" -> \"{1}\" [{2}, arrowhead={3}]",
foreignKeyTableName.Value, primaryKeyTableName.Value, edgeStyle, arrowStyle);
}
}
writer.WriteLine("}");
writer.Close();
}
}
public static Database FromConnection(SqlConnection connection)
{
Database database = null;
Dictionary<ObjectName, Table> tables = new Dictionary<ObjectName, Table>();
using (SqlCommand command = new SqlCommand())
{
command.Connection = connection;
command.CommandText =
"SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE FROM INFORMATION_SCHEMA.TABLES";
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
// Create the database upon reading the first row so that the database name
// can be set using the canonical capitalisation.
if (database == null) database = new Database(reader.GetString(0));
Table table = new Table(database, new ObjectName(reader.GetString(1), reader.GetString(2)));
tables[table.Name] = table;
}
}
}
foreach (Table table in tables.Values)
{
table.LoadColumnsFromDatabase(connection);
}
database._tables = tables;
database._tablesByShortName = new Dictionary<string, Table>();
foreach (Table table in tables.Values)
{
database._tablesByShortName[table.ParsedName.Value] = table;
}
Constraint.PopulateTableConstraintsFromConnection(connection, database);
return database;
}
static Dictionary<string, string> GetColumnTypeMappings(Database database)
{
Dictionary<string, string> fieldMappings = new Dictionary<string, string>();
Set<string> ambiguousMappings = new Set<string>();
foreach (Table table in database.Tables)
{
foreach (KeyValuePair<string, Column> pair in table.Columns)
{
string fieldNameLower = pair.Key.ToLower();
if (fieldMappings.ContainsKey(fieldNameLower))
{
if (fieldMappings[fieldNameLower] != pair.Value.DataType)
{
ambiguousMappings.UnionUpdate(fieldNameLower);
}
}
else
{
fieldMappings[fieldNameLower] = pair.Value.DataType;
}
}
}
foreach (string ambiguousMapping in ambiguousMappings)
{
fieldMappings.Remove(ambiguousMapping);
}
return fieldMappings;
}
public static void PopulateTableConstraintsFromConnection(SqlConnection connection, Database database)
{
Dictionary<ObjectName, ConstraintBuilder> builders = FetchConstraints(connection);
FetchConstraintColumns(connection, builders);
FetchReferentialConstraints(connection, builders);
FetchDisabledForeignKeyConstraints(connection, builders);
// Build the table constraints first; they will be required for building referential constraints:
Dictionary<ObjectName, TableConstraint> tableConstraints = new Dictionary<ObjectName, TableConstraint>();
foreach (ConstraintBuilder builder in builders.Values)
{
if (builder.ConstraintType != ConstraintType.ForeignKey)
{
tableConstraints[builder.ConstraintName] = builder.BuildTableConstraint(database);
}
}
// Build the referential constraints:
Dictionary<ObjectName, ForeignKeyConstraint> foreignKeyConstraints = new Dictionary<ObjectName, ForeignKeyConstraint>();
foreach (ConstraintBuilder builder in builders.Values)
{
if (builder.ConstraintType == ConstraintType.ForeignKey)
{
foreignKeyConstraints[builder.ConstraintName] =
builder.BuildForeignKeyConstraint(database, tableConstraints);
}
}
// Attach and link them:
foreach (TableConstraint constraint in tableConstraints.Values)
{
constraint.Table.AddTableConstraint(constraint);
}
foreach (ForeignKeyConstraint constraint in foreignKeyConstraints.Values)
{
constraint.Table.AddForeignKeyConstraint(constraint);
constraint.UniqueConstraint.AddRelatedForeignKeyConstraint(constraint);
}
}
public virtual void EndDatabaseVisit(Database database)
{
}
public virtual void BeginDatabaseVisit(Database database)
{
}
public virtual void CheckDatabase(ViolationReport report, Database database)
{
}
public static void CheckDependencies(Database database)
{
ICollection<Table> ordered = TopologicalSort.Sort<Table>(database.TablesByName.Values, EdgeGetter);
}
public static void CreateDependenciesGraph(Database database)
{
CreateDependenciesGraph(database, null);
}
public override void BeginDatabaseVisit(Database database)
{
Console.WriteLine(string.Format("Database {0}", database.Name));
}
internal ForeignKeyConstraint BuildForeignKeyConstraint(Database database, Dictionary<ObjectName, TableConstraint> tableConstraints)
{
if (_constraintType != ConstraintType.ForeignKey ||
_foreignUniqueConstraintName == null)
{
throw new InvalidOperationException();
}
if (!database.TablesByName.ContainsKey(_tableName))
{
throw new InvalidOperationException("A table referenced in a constraint was not found in the table set.");
}
if (!tableConstraints.ContainsKey(_foreignUniqueConstraintName))
{
throw new InvalidOperationException("A unique constraint referenced in a foreign key was not found.");
}
Table table = database.TablesByName[_tableName];
TableConstraint uniqueConstraint = tableConstraints[_foreignUniqueConstraintName];
return new ForeignKeyConstraint(_constraintName, table, _constraintType, ResolveColumns(_constraintColumns, table),
uniqueConstraint, _foreignUpdateRule, _foreignDeleteRule, _isDisabled);
}
public static void CreateFilteredDependenciesGraph(Database database, Table startTable, Plan plan)
{
Set<Table> visited = new Set<Table>();
Stack<Table> seen = new Stack<Table>();
seen.Push(startTable);
// The relations that, as a precondition of the delete, must not exist:
while (seen.Count > 0)
{
Table table = seen.Pop();
visited.UnionUpdate(table);
foreach (ForeignKeyConstraint constraint in table.ForeignKeyDependencies)
{
if (plan.IsConstraintExcludedFromDependencyDiagram(constraint)) continue;
if (!visited.Contains(constraint.Table))
{
seen.Push(constraint.Table);
}
}
}
CreateDependenciesGraph(database, visited);
}
public static void Dump(Database database, SqlConnection connection)
{
string fileName = GetFilename(database, connection, "txt");
using (StreamWriter writer = new StreamWriter(fileName))
{
List<ObjectName> tableNames = new List<ObjectName>();
tableNames.AddRange(database.TablesByName.Keys);
tableNames.Sort();
foreach (ObjectName tableName in tableNames)
{
Table table = database.TablesByName[tableName];
writer.WriteLine("table \"{0}\"", table.Name);
writer.WriteLine("{");
foreach (Column column in table.Columns.Values)
{
writer.Write(" column \"{0}\" ", column.Name);
writer.Write("{ ");
writer.Write("type \"{0}\";", column.DataType);
if (column.IsNullable) writer.Write(" nullable;");
if (column.ColumnDefault != null)
{
writer.Write(" default \"{0}\";", column.ColumnDefault);
}
if (column.CharacterMaximumLength.HasValue)
{
writer.Write(" length {0};", column.CharacterMaximumLength.Value);
}
writer.WriteLine(" }");
}
writer.WriteLine("}");
writer.WriteLine();
}
}
}
public Plan(Database database)
{
_database = database;
_foreignKeyActions = new DictionaryOfLists<Constraint, PlanForeignKeyAction>();
}
