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