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