public static List <DbUpgradeScript> CompareDatabaseSchemas(DbFirstConfig config, Type modelType)
{
var entApp = Activator.CreateInstance(modelType) as EntityApp;
entApp.EntityClassProvider = new DummyEntityClassProvider();
entApp.Init();
// important - do not use DbOptions.AutoIndexForeignKeys - which is recommended for MS SQL, but is not helpful here.
// This will create a bunch of extra indexes on FKs in entities schema and result in extra differences with original schema.
// We ignore stored procs
var dbOptions = config.Driver.GetDefaultOptions() & ~DbOptions.AutoIndexForeignKeys;
var dbSettings = new DbSettings(config.Driver, dbOptions, config.ConnectionString,
upgradeMode: DbUpgradeMode.Always,
upgradeOptions: DbUpgradeOptions.UpdateTables | DbUpgradeOptions.UpdateIndexes
);
//dbSettings.SetSchemas(config.Schemas);
var log = new ActivationLog(null);
var dbModelBuilder = new DbModelBuilder(entApp.Model, dbSettings.ModelConfig, log);
var dbModel = dbModelBuilder.Build();
var db = new Database(dbModel, dbSettings);
var ds = new DataSource("main", db);
var upgradeMgr = new DbUpgradeManager(db, log);
var upgradeInfo = upgradeMgr.BuildUpgradeInfo();
return(upgradeInfo.AllScripts);
}
public void TestDbModelCompare()
{
var app = Startup.BooksApp;
//Once we started tests, db model is updated; now if we compare db model with entity model, there should be no changes
// We test here how well the DbModelComparer works - it should not signal any false positives (find differences when there are none)
var ds = app.GetDefaultDataSource();
var upgradeMgr = new DbUpgradeManager(ds.Database, app.ActivationLog);
var upgradeInfo = upgradeMgr.BuildUpgradeInfo(); //.AddDbModelChanges(currentDbModel, modelInDb, DbUpgradeOptions.Default, app.ActivationLog);
var changeCount = upgradeInfo.TableChanges.Count + upgradeInfo.NonTableChanges.Count;
#region Rant about Postgres
// For Postgres we have no way to compare view definitions to detect change.
// The view SQL returned by information_schema.Views is extensively modified and beautified (!) version of
// the original View SQL (these guys must be very proud, I'm happy for them).
// So views in PG driver will always be marked as modified, unless you use extra upgradeMode parameter in RegisterView.
#endregion
if (Startup.ServerType == DbServerType.Postgres)
{
var viewCount = app.Model.Entities.Where(e => e.Kind == Entities.Model.EntityKind.View).Count();
changeCount -= viewCount; // views are marked as mismatch, so ignore these
}
if (changeCount > 0)
{
// sometimes randomly fails, trying to catch it
var changes = string.Join(Environment.NewLine, upgradeInfo.AllScripts.Select(s => s.Sql).ToList());
Debug.WriteLine("\r\nFAILED DbModelCompare test, update scripts: ====================================================\r\n"
+ changes);
//Debugger.Break();
}
Assert.AreEqual(0, changeCount, "Expected no changes");
}
public bool GenerateScripts(XmlDocument xmlConfig)
{
const string header =
@"-- DDL Scripts generated by VITA DB Tool.
-- Generated on: {0}
-- Target database: {1}
-- Executed by user {2} on machine {3}.
";
_config = new DbUpdateConfig(xmlConfig);
Util.Check(File.Exists(_config.AssemblyPath), "Assembly file '{0}' not found.", _config.AssemblyPath);
var asm = Assembly.LoadFrom(_config.AssemblyPath);
var appType = asm.GetType(_config.AppClassName);
Util.Check(appType != null, "Type {0} not found in target assembly.");
// Using NonPublic flag to allow internal constructor;
// EntityApp must have a parameterless constructor, but it may be made internal, to hide from regular code
var flags = BindingFlags.Public | BindingFlags.NonPublic | BindingFlags.Instance;
var appObj = Activator.CreateInstance(appType, flags, null, null, null);
Util.Check(appObj != null, "Failed to create instance of class {0}.", _config.AppClassName);
var entApp = appObj as EntityApp;
Util.Check(entApp != null, "The target instance of class {0} is not an EntityApp instance.", _config.AppClassName);
entApp.Init();
var dbSettings = new DbSettings(_config.Driver, _config.DbOptions, _config.ConnectionString,
upgradeMode: DbUpgradeMode.Always, upgradeOptions: _config.ModelUpdateOptions);
/*
* var schemas = entApp.Areas.Select(a => a.Name).ToList();
* dbSettings.SetSchemas(schemas);
*/
var log = new BufferedLog(LogContext.SystemLogContext);
var dbModelBuilder = new DbModelBuilder(entApp.Model, dbSettings.ModelConfig, log);
var dbModel = dbModelBuilder.Build();
var db = new Database(dbModel, dbSettings);
var updateMgr = new DbUpgradeManager(db, log);
var upgrades = updateMgr.BuildUpgradeInfo();
var ddlSep = dbModel.Driver.SqlDialect.DDLSeparator;
var ddl = string.Join(ddlSep, upgrades.AllScripts.Select(scr => scr.Sql));
if (string.IsNullOrEmpty(ddl))
{
ddl = "-- (No changes detected)";
}
var text = string.Format(header, DateTime.Now.ToString("s"), _config.ConnectionString, "(unknown)", Environment.MachineName) + ddl;
File.WriteAllText(_config.OutputPath, text);
_feedback.WriteLine(" Generated {0} scripts.", upgrades.AllScripts.Count);
_feedback.WriteLine(" DDL scripts are saved to '{0}'", _config.OutputPath);
return(true);
}
//Separate implementation for SQLite. SQLite does not support column-modify, table-rename, etc.
// So we try to account for these in this special version
private void TestSchemaUpdateImplSQLite()
{
//Start from fresh copy of the database
File.Copy("..\\..\\VitaTestSQLite.db", "VitaTestSQLite.db", overwrite: true);
// Note - we have the flag AddSchemaToTableName for SQlite setup (default),
// so table names are prefixed with 'upd_'
//version 1 of model/schema
{
var app = new EntityAppV1();
Startup.ActivateApp(app); //updates schema
// Load DbModel and verify it is correct
var dbModel = Startup.LoadDbModel(SchemaName, app.SystemLog);
Assert.AreEqual(6, dbModel.Tables.Count(), "Expected 4 tables.");
var parTable = dbModel.GetTable(null, "upd_ParentEntity");
Assert.AreEqual(8, parTable.Columns.Count, "Invalid number of columns in parent table.");
var keyCount = CountKeys(parTable);
//Keys: PK, FK to IEntityToDelete, index on FK to IEntityToDelete, index on IntProp,StringProp
Assert.AreEqual(4, keyCount, "Invalid # of keys in parent table.");
//child entity
var childTable = dbModel.GetTable(SchemaName, "upd_ChildEntity");
Assert.AreEqual(5, childTable.Columns.Count, "Invalid number of columns in child table.");
// 3 - PK + 2FKs, no indexes on FKs
Assert.AreEqual(3, childTable.Keys.Count, "Invalid # of keys in child table.");
//Create a few records
var session = app.OpenSession();
var parent = session.NewEntity <EntityModuleV1.IParentEntity>();
parent.IntProp = 4;
parent.StringProp = "Some string";
parent.SingleProp = 4.56f;
parent.DoubleProp = 5.67;
parent.IntPropWithDefault = 567;
parent.StringProp2_OldName = "Old string";
var child = session.NewEntity <EntityModuleV1.IChildEntity>();
child.Parent = parent;
child.ParentRefToDelete = parent;
child.Name = "Child name";
var entToDelete = session.NewEntity <EntityModuleV1.IEntityToDelete>();
entToDelete.Name = "some-name";
parent.EntityToDeleteRef = entToDelete;
session.SaveChanges();
app.Flush();
app.Shutdown();
}
//Now change to version 2 ================================================================
{
var app = new EntityAppV2();
Startup.ActivateApp(app, dropUnknownTables: true);
//At this point the schema should have been updated; let's check it
// Load DbModel and verify it is correct
var dbModel = Startup.LoadDbModel(SchemaName, app.SystemLog);
//Note that we still have 4 tables, EntityToDelete is not dropped because of incoming FK
Assert.AreEqual(7, dbModel.Tables.Count(), "Expected 7 tables after update.");
var parTable = dbModel.GetTable(SchemaName, "upd_ParentEntity");
//NO support for dropping columns, so old columns are not deleted; instead of renaming a new column is added
Assert.AreEqual(11, parTable.Columns.Count, "Invalid number of columns in parent table after schema update.");
Assert.AreEqual(4, parTable.Keys.Count, //PK, FK->EntityToDelete, indexes (IntProp,StringProp), (StropProp,Id)
"Invalid # of keys in parent table after update.");
//child entity
var childTable = dbModel.GetTable(SchemaName, "upd_ChildEntity");
Assert.AreEqual(6, childTable.Columns.Count, "Invalid number of columns in child table after update.");
// = 3: Clustered PK, FK to parent, index on FK
Assert.AreEqual(3, childTable.Keys.Count, "Invalid # of keys in child table after update.");
//Check that post-upgrade action is executed - check records are added to INewTable
var session = app.OpenSession();
var newTableEntCount = session.EntitySet <EntityModuleV2.INewTable>().Count();
Assert.AreEqual(2, newTableEntCount, "Expected 2 entities in INewTable.");
// Now create model again, compare it and make sure no schema updates
var ds = app.GetDefaultDataSource();
var upgradeMgr = new DbUpgradeManager(ds);
var upgradeInfo = upgradeMgr.BuildUpgradeInfo();
if (upgradeInfo.AllScripts.Count > 0)
{
var strUpdates = upgradeInfo.AllScripts.GetAllAsText();
Debug.WriteLine("Detected updates when no schema changes should be present:");
Debug.WriteLine(strUpdates);
Assert.IsTrue(false, "Schema changes count should be zero.");
}
app.Flush();
}
}
private void TestSchemaUpdateImpl()
{
Startup.DropSchemaObjects(SchemaName);
var supportsClustIndex = Startup.Driver.Supports(DbFeatures.ClusteredIndexes);
//version 1 of model/schema
{
var app = new EntityAppV1();
Startup.ActivateApp(app); //updates schema
// Load DbModel and verify it is correct
var dbModel = Startup.LoadDbModel(SchemaName, app.SystemLog);
Assert.AreEqual(6, dbModel.Tables.Count(), "Expected 6 tables."); //2 tables in DbInfo + 4 tables in our module
var parTable = dbModel.GetTable(SchemaName, "ParentEntity");
Assert.AreEqual(8, parTable.Columns.Count, "Invalid number of columns in parent table.");
var keyCount = CountKeys(parTable);
//Keys: PK, FK to IEntityToDelete, index on FK to IEntityToDelete, index on IntProp,StringProp, (ClusteredIndex?)
Assert.AreEqual(5, keyCount, "Invalid # of keys in parent table.");
if (supportsClustIndex)
{
var parCI = parTable.Keys.First(k => k.KeyType.IsSet(KeyType.ClusteredIndex));
Assert.AreEqual(1, parCI.KeyColumns.Count, "Invalid number of fields in clustered index.");
}
//child entity
var childTable = dbModel.GetTable(SchemaName, "ChildEntity");
Assert.AreEqual(5, childTable.Columns.Count, "Invalid number of columns in child table.");
// some servers like MySql create FK and Index on it under the same name. When loading, such a key should be marked with 2 flags
// so let's count these separately; should be 5: PK + 2 foreign keys + 2 indexes on FK
keyCount = CountKeys(childTable);
Assert.AreEqual(5, keyCount, "Invalid # of keys in child table.");
//Create a few records
var session = app.OpenSession();
var parent = session.NewEntity <EntityModuleV1.IParentEntity>();
parent.IntProp = 4;
parent.StringProp = "Some string";
parent.SingleProp = 4.56f;
parent.DoubleProp = 5.67;
parent.IntPropWithDefault = 567;
parent.StringProp2_OldName = "Old string";
var child = session.NewEntity <EntityModuleV1.IChildEntity>();
child.Parent = parent;
child.ParentRefToDelete = parent;
child.Name = "Child name";
var entToDelete = session.NewEntity <EntityModuleV1.IEntityToDelete>();
entToDelete.Name = "some-name";
parent.EntityToDeleteRef = entToDelete;
session.SaveChanges();
app.Shutdown();
}
//Now change to version 2 ================================================================
{
var app = new EntityAppV2();
Startup.ActivateApp(app, dropUnknownTables: true);
//At this point the schema should have been updated; let's check it
// Load DbModel and verify it is correct
var dbModel = Startup.LoadDbModel(SchemaName, app.SystemLog);
Assert.AreEqual(6, dbModel.Tables.Count(), "Expected 6 tables after update.");
var parTable = dbModel.GetTable(SchemaName, "ParentEntity");
Assert.AreEqual(7, parTable.Columns.Count, "Invalid number of columns in parent table after schema update.");
Assert.AreEqual(3, parTable.Keys.Count, //PK, Clustered index, index on IntProp,StringProp
"Invalid # of keys in parent table after update.");
if (supportsClustIndex)
{
//SQL CE does not support clustered indexes
var parCI = parTable.Keys.First(k => k.KeyType.IsSet(KeyType.ClusteredIndex));
Assert.AreEqual(2, parCI.KeyColumns.Count, "Invalid number of fields in clustered index."); //
}
//child entity
var childTable = dbModel.GetTable(SchemaName, "ChildEntityRenamed");
Assert.AreEqual(5, childTable.Columns.Count, "Invalid number of columns in child table after update.");
var keyCount = CountKeys(childTable); // = 5: Clustered PK, FK to parent, index on FK, FK to OtherParent, index on FK
Assert.AreEqual(5, keyCount, "Invalid # of keys in child table after update.");
//Check that post-upgrade action is executed - check records are added to INewTable
var session = app.OpenSession();
var newTableEntCount = session.EntitySet <EntityModuleV2.INewTable>().Count();
Assert.AreEqual(2, newTableEntCount, "Expected 2 entities in INewTable.");
// Now create model again, compare it and make sure no schema updates
var ds = app.GetDefaultDataSource();
var dbUpdater = new DbUpgradeManager(ds);
var upgradeInfo = dbUpdater.BuildUpgradeInfo();
// if we have upgrade scripts, this is error
if (upgradeInfo.AllScripts.Count > 0)
{
var strUpdates = upgradeInfo.AllScripts.GetAllAsText();
Debug.WriteLine("Detected updates when no schema changes should be present:");
Debug.WriteLine(strUpdates);
Assert.IsTrue(false, "Schema changes count should be zero.");
}
app.Flush();
}
}//method
private void TestSchemaUpdateImpl()
{
// Prepare Db settings, wipe out old tables - we have to do it this way, because of complexities with Oracle
var dbSettings = new DbSettings(Startup.Driver, Startup.DbOptions,
Startup.ConnectionString, upgradeMode: DbUpgradeMode.Always, upgradeOptions: DbUpgradeOptions.Default);
DataUtility.DropTablesSafe(dbSettings, SchemaName, "ChildEntity", "ChildEntityRenamed", "ParentEntity",
"Table", "NewTable", "DbInfo", "DbModuleInfo");
var supportsClustIndex = Startup.Driver.Supports(DbFeatures.ClusteredIndexes);
//version 1 of model/schema
{
var app = new EntityAppV1();
SpecialActivateApp(app, dbSettings, true);
// Load DbModel and verify it is correct
var dbModel = Startup.LoadDbModel(app);
Assert.AreEqual(6, dbModel.Tables.Count, "Expected 6 tables."); //2 tables in DbInfo + 4 tables in our module
var parTable = FindTable(dbModel, SchemaName, "ParentEntity");
Assert.AreEqual(8, parTable.Columns.Count, "Invalid number of columns in parent table.");
var keyCount = CountKeys(parTable);
//Keys: PK, FK to IEntityToDelete, index on FK to IEntityToDelete, index on IntProp,StringProp
Assert.AreEqual(4, keyCount, "Invalid # of keys in parent table.");
//child entity
var childTable = FindTable(dbModel, SchemaName, "ChildEntity");
Assert.AreEqual(5, childTable.Columns.Count, "Invalid number of columns in child table.");
// some servers like MySql create FK and Index on it under the same name. When loading, such a key should be marked with 2 flags
// so let's count these separately; should be 3: PK + 2 foreign keys
// note: indexes on FK are not auto-created
keyCount = CountKeys(childTable);
// For MySql, for every FK a supporting index is created (automatically), so we have 2 extra indexes on FKs
// for other servers indexes on FK not created.
var expectedKeyCount = Startup.ServerType == DbServerType.MySql ? 5 : 3;
Assert.AreEqual(expectedKeyCount, keyCount, "Invalid # of keys in child table.");
//Create a few records
var session = app.OpenSession();
var parent = session.NewEntity <EntityModuleV1.IParentEntity>();
parent.IntProp = 4;
parent.StringProp = "Some string";
parent.SingleProp = 4.56f;
parent.DoubleProp = 5.67;
parent.IntPropWithDefault = 567;
parent.StringProp2_OldName = "Old string";
var child = session.NewEntity <EntityModuleV1.IChildEntity>();
child.Parent = parent;
child.ParentRefToDelete = parent;
child.Name = "Child name";
var entToDelete = session.NewEntity <EntityModuleV1.IEntityToDelete>();
entToDelete.Name = "some-name";
parent.EntityToDeleteRef = entToDelete;
session.SaveChanges();
app.Shutdown();
}
//Now change to version 2 ================================================================
{
var app = new EntityAppV2();
// use fresh dbSettings to avoid sharing db model (we could drop the DbOptions.ShareDbModel flag instead)
dbSettings = new DbSettings(Startup.Driver, Startup.DbOptions,
Startup.ConnectionString, upgradeMode: DbUpgradeMode.Always, upgradeOptions: DbUpgradeOptions.Default | DbUpgradeOptions.DropUnknownObjects);
SpecialActivateApp(app, dbSettings, false);
//At this point the schema should have been updated; let's check it
// Load DbModel and verify it is correct
var dbModel = Startup.LoadDbModel(app);
// 2 tables in DbInfo module, 4 tables in test app
Assert.AreEqual(6, dbModel.Tables.Count, "Expected 6 tables after update.");
var parTable = FindTable(dbModel, SchemaName, "ParentEntity");
Assert.AreEqual(7, parTable.Columns.Count, "Invalid number of columns in parent table after schema update.");
Assert.AreEqual(3, parTable.Keys.Count, //PK, Clustered index, index on IntProp,StringProp
"Invalid # of keys in parent table after update.");
if (supportsClustIndex)
{
//SQL CE does not support clustered indexes
var parCI = parTable.Keys.First(k => k.KeyType.IsSet(KeyType.ClusteredIndex));
Assert.AreEqual(2, parCI.KeyColumns.Count, "Invalid number of fields in clustered index."); //
}
//child entity
var childTable = FindTable(dbModel, SchemaName, "ChildEntityRenamed");
Assert.AreEqual(5, childTable.Columns.Count, "Invalid number of columns in child table after update.");
var keyCount = CountKeys(childTable); // = 3: Clustered PK, FK to parent, FK to OtherParent; (indexes on FKs are not auto-created)
// For MySql, for every FK a supporting index is created (automatically), so we have 2 extra indexes on FKs
// for other servers indexes on FK not created.
var expectedKeyCount = Startup.ServerType == DbServerType.MySql ? 5 : 3;
Assert.AreEqual(expectedKeyCount, keyCount, "Invalid # of keys in child table after update.");
//Check that post-upgrade action is executed - check records are added to INewTable
var session = app.OpenSession();
var newTableEntCount = session.EntitySet <EntityModuleV2.INewTable>().Count();
Assert.AreEqual(2, newTableEntCount, "Expected 2 entities in INewTable.");
// Now create model again, compare it and make sure no schema updates
var ds = app.GetDefaultDataSource();
var dbUpdater = new DbUpgradeManager(ds.Database, app.ActivationLog);
var upgradeInfo = dbUpdater.BuildUpgradeInfo();
// if we have upgrade scripts, this is error
if (upgradeInfo.AllScripts.Count > 0)
{
var strUpdates = upgradeInfo.AllScripts.GetAllAsText();
Debug.WriteLine("Detected updates when no schema changes should be present:");
Debug.WriteLine(strUpdates);
Assert.IsTrue(false, "Schema changes count should be zero.");
}
app.Flush();
}
}//method