/// <summary>
/// Run the comparison.
/// </summary>
/// <returns></returns>
public string Execute()
{
var sb = new StringBuilder();
var compareTables = new CompareTables(sb, _writer);
//make sure they are in topological order- if 2 tables are added, the first must not have a foreign key to the second...
var comparedTables = SchemaTablesSorter.TopologicalSort(_compareSchema);
compareTables.Execute(_baseSchema.Tables, comparedTables);
//compare sequences
var compareSequences = new CompareSequences(sb, _writer);
compareSequences.Execute(_baseSchema.Sequences, _compareSchema.Sequences);
//compare views
var compareViews = new CompareViews(sb, _writer);
compareViews.Execute(_baseSchema.Views, _compareSchema.Views);
//compare stored procedures and functions
var compareProcedures = new CompareProcedures(sb, _writer);
compareProcedures.Execute(_baseSchema.StoredProcedures, _compareSchema.StoredProcedures);
var compareFunctions = new CompareFunctions(sb, _writer);
compareFunctions.Execute(_baseSchema.Functions, _compareSchema.Functions);
//compare packages
var comparePackages = new ComparePackages(sb, _writer);
comparePackages.Execute(_baseSchema.Packages, _compareSchema.Packages);
return(sb.ToString());
}
/// <summary>
/// Run the comparison and returns a <see cref="CompareResult"/>.
/// </summary>
/// <returns></returns>
public IList <CompareResult> ExecuteResult()
{
var list = new List <CompareResult>();
var compareTables = new CompareTables(list, _writer);
//make sure they are in topological order- if 2 tables are added, the first must not have a foreign key to the second...
var comparedTables = SchemaTablesSorter.TopologicalSort(_compareSchema);
compareTables.Execute(_baseSchema.Tables, comparedTables);
//compare sequences
var compareSequences = new CompareSequences(list, _writer);
compareSequences.Execute(_baseSchema.Sequences, _compareSchema.Sequences);
//compare views
var compareViews = new CompareViews(list, _writer);
compareViews.Execute(_baseSchema.Views, _compareSchema.Views);
//compare stored procedures and functions
var compareProcedures = new CompareProcedures(list, _writer);
compareProcedures.Execute(_baseSchema.StoredProcedures, _compareSchema.StoredProcedures);
var compareFunctions = new CompareFunctions(list, _writer);
compareFunctions.Execute(_baseSchema.Functions, _compareSchema.Functions);
//compare packages
var comparePackages = new ComparePackages(list, _writer);
comparePackages.Execute(_baseSchema.Packages, _compareSchema.Packages);
return(list);
}
public bool Execute()
{
InvokeProgressChanged(0, "Reading origin database");
var databaseSchema = _databaseReader.ReadAll();
_dbFactory = DbProviderFactories.GetFactory(databaseSchema.Provider);
_originConnection = databaseSchema.ConnectionString;
//for SQLServer CE we are actually using the SqlServer settings
//CE only supports a subset of SqlServer datatypes and functionality
//the big problem is VARCHAR(MAX) - CE only has NTEXT.
var factory = new DdlGeneratorFactory(_destinationType);
var tableGenerator = factory.AllTablesGenerator(databaseSchema);
tableGenerator.IncludeSchema = false;
var ddl = tableGenerator.Write();
IDatabaseCreator dbCreator;
if (_useSqlServerCe)
{
dbCreator = new SqlServerCeDatabaseCreator(_filePath);
}
else
{
dbCreator = new DatabaseCreator(_filePath);
}
InvokeProgressChanged(0, "Creating database tables");
try
{
dbCreator.CreateTables(ddl);
}
catch (DbException exception)
{
LastErrorMessage = exception.Message;
return(false);
}
//put them in fk dependency order
var sortedTables = SchemaTablesSorter.TopologicalSort(databaseSchema);
var count = databaseSchema.Tables.Count;
decimal current = 0;
foreach (var databaseTable in sortedTables)
{
var percentage = (int)((current / count) * 100);
InvokeProgressChanged(percentage, "Copying table " + databaseTable.Name);
if (!Copy(dbCreator, databaseTable))
{
return(false);
}
current++;
}
return(true);
}
public void TestTopologicalSort()
{
//arrange
var schema = new DatabaseSchema(null, null);
var orders = new DatabaseTable();
orders.Name = "orders";
var productsFk = new DatabaseConstraint
{
ConstraintType = ConstraintType.ForeignKey,
RefersToTable = "products"
};
orders.AddConstraint(productsFk);
schema.Tables.Add(orders);
var categories = new DatabaseTable();
categories.Name = "categories";
schema.Tables.Add(categories);
var products = new DatabaseTable();
products.Name = "products";
var categoriesFk = new DatabaseConstraint();
categoriesFk.ConstraintType = ConstraintType.ForeignKey;
categoriesFk.RefersToTable = "categories";
products.AddConstraint(categoriesFk);
schema.Tables.Add(products);
//act
var sortedTables = SchemaTablesSorter.TopologicalSort(schema);
//assert
var first = sortedTables.First();
var last = sortedTables.Last();
Assert.AreEqual(categories, first);
Assert.AreEqual(orders, last);
}
public void WithBidirectionalDepndencyTopologicalSort()
{
//arrange
var schema = new DatabaseSchema(null, null);
var orders = new DatabaseTable();
orders.Name = "countries";
var productsFk = new DatabaseConstraint();
productsFk.ConstraintType = ConstraintType.ForeignKey;
productsFk.RefersToTable = "capitalcities";
orders.AddConstraint(productsFk);
schema.Tables.Add(orders);
var products = new DatabaseTable();
products.Name = "capitalcities";
var categoriesFk = new DatabaseConstraint();
categoriesFk.ConstraintType = ConstraintType.ForeignKey;
categoriesFk.RefersToTable = "countries";
products.AddConstraint(categoriesFk);
schema.Tables.Add(products);
//a country has one capital city
//a capital city is in one country
//But bidirectional foreign keys is terrible database design - you really only need one direction.
//(you have to save the country with a null capital, then the capital, then update the country again).
//Topological sorts don't support cycles, so we should just get back the original list
//act
var sortedTables = SchemaTablesSorter.TopologicalSort(schema);
//assert
Assert.AreEqual(2, sortedTables.Count());
//non-deterministic order
}
public void DeleteAllData(DatabaseSchema databaseSchema)
{
try
{
var orderedTables = SchemaTablesSorter.TopologicalSort(databaseSchema).Reverse();
var sb = new StringBuilder();
sb.AppendLine("-- delete data from all tables in safe order");
foreach (var databaseTable in orderedTables)
{
if (databaseTable.ForeignKeyChildren.Contains(databaseTable))
{
sb.AppendLine("-- WARNING: " + databaseTable.Name + " has SELF-JOIN");
}
var sqlWriter = new SqlWriter(databaseTable, _sqlType);
sb.AppendLine("DELETE FROM " + sqlWriter.EscapedTableName);
}
Clipboard.SetText(sb.ToString(), TextDataFormat.UnicodeText);
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
}
public string DeleteAllData(DatabaseSchema databaseSchema)
{
var sb = new StringBuilder();
try
{
var orderedTables = SchemaTablesSorter.TopologicalSort(databaseSchema).Reverse();
sb.AppendLine("-- 删除所有表的数据");
foreach (var databaseTable in orderedTables)
{
if (databaseTable.ForeignKeyChildren.Contains(databaseTable))
{
sb.AppendLine("-- 警告: " + databaseTable.Name + "有主外键关联");
}
var sqlWriter = new SqlWriter(databaseTable, m_sqlType);
sb.AppendLine("DELETE FROM " + sqlWriter.EscapedTableName);
}
}
catch (Exception exception)
{
Debug.WriteLine(exception.Message);
}
return(sb.ToString());
}
