public static List<DbUpgradeScript> CompareDatabaseSchemas(DbFirstConfig config, Type modelType)
{
var entApp = Activator.CreateInstance(modelType) as EntityApp;
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 = DbOptions.Default & ~DbOptions.AutoIndexForeignKeys;
var dbSettings = new DbSettings(config.Driver, dbOptions, config.ConnectionString,
upgradeMode: DbUpgradeMode.Always,
upgradeOptions : DbUpgradeOptions.UpdateTables | DbUpgradeOptions.UpdateIndexes
);
dbSettings.SetSchemas(config.Schemas);
var db = new Database(entApp, dbSettings);
var ds = new DataSource("main", db);
var upgradeMgr = new DbUpgradeManager(ds);
var upgradeInfo = upgradeMgr.BuildUpgradeInfo();
return upgradeInfo.AllScripts;
}
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 db = new Database(entApp, dbSettings);
var ds = new DataSource(null, db);
var updateMgr = new DbUpgradeManager(ds);
var upgrades = updateMgr.BuildUpgradeInfo();
var ddl = string.Join(_config.Driver.DDLSeparator, 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, Environment.UserName, 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;
}
private void TestSchemaUpdateImpl()
{
SetupHelper.DropSchemaObjects(SchemaName);
var supportsClustIndex = SetupHelper.Driver.Supports(DbFeatures.ClusteredIndexes);
//version 1 of model/schema
{
var app = new EntityAppV1();
SetupHelper.ActivateApp(app); //updates schema
// Load DbModel and verify it is correct
var dbModel = SetupHelper.LoadDbModel(SchemaName, app.ActivationLog);
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();
SetupHelper.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 = SetupHelper.LoadDbModel(SchemaName, app.ActivationLog);
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();
}
}
//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);
//version 1 of model/schema
{
var app = new EntityAppV1();
SetupHelper.ActivateApp(app); //updates schema
// Load DbModel and verify it is correct
var dbModel = SetupHelper.LoadDbModel(SchemaName, app.ActivationLog);
Assert.AreEqual(6, dbModel.Tables.Count(), "Expected 4 tables.");
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
Assert.AreEqual(4, keyCount, "Invalid # of keys in parent table.");
//child entity
var childTable = dbModel.GetTable(SchemaName, "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();
SetupHelper.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 = SetupHelper.LoadDbModel(SchemaName, app.ActivationLog);
//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, "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, "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();
}
}
public void RegisterDataSource(DataSource dataSource) {
Util.CheckNotEmpty(dataSource.Name, "DataSource name may not be empty.");
//dataSource.Database.CheckConnectivity();
lock (_lock) {
var oldDs = LookupDataSource(dataSource.Name);
if (oldDs != null)
return;
//create copy, add to it, and then replace with interlock
IDictionary<string, DataSource> newDict;
if (_dataSources == null)
newDict = new Dictionary<string, DataSource>(StringComparer.InvariantCultureIgnoreCase);
else
newDict = new Dictionary<string, DataSource>(_dataSources, StringComparer.InvariantCultureIgnoreCase);
newDict.Add(dataSource.Name, dataSource);
System.Threading.Interlocked.Exchange(ref _dataSources, newDict);
}
//Invoke upgrade
_events.OnDataSourceStatusChanging(new DataSourceEventArgs(dataSource, DataSourceEventType.Connecting));
// Update db model
var db = dataSource.Database;
if (db.Settings.UpgradeMode != DbUpgradeMode.Never) {
var updateMgr = new DbUpgradeManager(dataSource);
var upgradeInfo = updateMgr.UpgradeDatabase();
_app.CheckActivationErrors();
ApplyMigrations(upgradeInfo);
}
_events.OnDataSourceStatusChanging(new DataSourceEventArgs(dataSource, DataSourceEventType.Connected));
}