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();
}
}
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
//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()
{
// 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
