public override void Execute(HortonOptions options) { Program.PrintLine("=== Dump Schema ==="); Program.PrintLine(); var tables = LoadSchema(options); Program.PrintLine(); var ddlList = FindChanges(tables); var sb = new StringBuilder(); var textWriter = new IndentedTextWriter(new StringWriter(sb)); foreach (var ddl in ddlList) { ddl.AppendDDL(textWriter); textWriter.WriteLine(); textWriter.WriteLine(); } if (sb.Length > 0) { File.WriteAllText(Path.Combine(options.MigrationsDirectoryPath, "000_BaseSchema.sql"), sb.ToString()); } }
public SchemaInfo(HortonOptions options) { Connection = new SqlConnection(options.CreateConnectionString()); Connection.Open(); }
private List<Sys.Table> LoadSchema(HortonOptions options) { using (var connection = new SqlConnection(options.CreateConnectionString())) { connection.Open(); Program.Print("Querying schemas..."); var schemas = connection.Query<Sys.Schema>("SELECT * FROM sys.schemas").ToDictionary(x => x.schema_id); Program.PrintLine("...done."); Program.Print("Querying tables..."); var tables = connection.Query<Sys.Table>("SELECT * FROM sys.tables").ToDictionary(x => x.object_id); Program.PrintLine("...done."); Program.Print("Querying foreign keys..."); var fkCols = connection.Query<Sys.ForeignKeyColumn>(Sys.ForeignKeyColumn.SQL_SelectAll).ToLookup(x => x.constraint_object_id); var fks = connection.Query<Sys.ForeignKey>(Sys.ForeignKey.SQL_SelectAll).ToList(); foreach (var fk in fks) { fk.Columns.AddRange(fkCols[fk.object_id]); fk.Parent = tables[fk.parent_object_id]; fk.Parent.ForeignKeys.Add(fk); fk.Referenced = tables[fk.referenced_object_id]; fk.Referenced.OutboundForeignKeys.Add(fk); } Program.PrintLine("...done."); Program.Print("Querying indexes..."); var indexedColumns = connection.Query<Sys.IndexedColumn>("SELECT * FROM sys.index_columns").ToLookup(x => Tuple.Create(x.object_id, x.index_id)); var indexes = connection.Query<Sys.Index>(Sys.Index.SQL_SelectAll).ToList(); foreach (var index in indexes) { index.Columns.AddRange(indexedColumns[Tuple.Create(index.object_id, index.index_id)]); if (tables.ContainsKey(index.object_id)) { index.Table = tables[index.object_id]; index.Table.Indexes.Add(index); } } Program.PrintLine("...done."); Program.Print("Query columns and table constraints..."); foreach (var table in tables.Values) { table.Schema = schemas[table.schema_id]; table.TableCheckConstraints.AddRange(connection.Query<Sys.CheckConstraint>("SELECT * FROM sys.check_constraints WHERE parent_column_id = 0 AND parent_object_id= @object_id", new { table.object_id })); table.Columns.AddRange(connection.Query<Sys.Column>(Sys.Column.SQL_SelectAll + " WHERE c.object_id= @object_id", new { table.object_id })); foreach (var fk in table.ForeignKeys) { foreach (var col in fk.Columns) { col.ParentColumn = table.Columns.Single(x => x.column_id == col.parent_column_id); } } foreach (var fk in table.OutboundForeignKeys) { foreach (var col in fk.Columns) { col.ReferencedColumn = table.Columns.Single(x => x.column_id == col.referenced_column_id); } } foreach (var idx in table.Indexes) { foreach (var col in idx.Columns) { col.Column = table.Columns.Single(x => x.column_id == col.column_id); } } } Program.PrintLine("...done."); Program.Print("Analyzing FK dependency graph..."); foreach (var table in tables.Values) { foreach (var fk in table.ForeignKeys) { fk.IsCircularDependency = IsCircularDependency(table, fk); } } Program.PrintLine("...done."); connection.Close(); return tables.Values.OrderBy(x => x.create_date).ThenBy(x => x.Schema.name).ThenBy(x => x.name).ToList(); } }
public abstract void Execute(HortonOptions options);