public void PlanManagementTest()
{
var dbName = TestDatabaseNames.GetConsistentName("PlanManagementTests");
var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName);
db.Create(true);
BulkTestsData bulk = new BulkTestsData(CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn, 100);
bulk.SetupTestData();
bulk.ImportAsCatalogue();
var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, bulk.catalogue);
planManager.TargetDatabase = db;
//no operations are as yet configured
Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier()));
//create a table with the same name in the endpoint database to confirm that that's a problem
db.CreateTable(bulk.tableInfo.GetRuntimeName(), new DatabaseColumnRequest[]
{
new DatabaseColumnRequest("fish", "varchar(100)")
});
//throws because table already exists
Assert.Throws <Exception>(() => planManager.Check(new ThrowImmediatelyCheckNotifier()));
db.ExpectTable(bulk.tableInfo.GetRuntimeName()).Drop();
//back to being fine again
Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier()));
//setup test rules for migrator
CreateMigrationRules(planManager, bulk);
//rules should pass
Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier()));
var chi = bulk.GetColumnInfo("chi");
Assert.Throws <Exception>(() =>
{
planManager.GetPlanForColumnInfo(chi).Plan = Plan.Drop;
planManager.GetPlanForColumnInfo(chi).Check(new ThrowImmediatelyCheckNotifier());
}
, "Should not be able to drop primary key column");
db.Drop();
}
public void CreateANOVersionTest_IntIdentity()
{
var dbName = TestDatabaseNames.GetConsistentName("CreateANOVersionTest");
//setup the anonymisation database (destination)
var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName);
db.Create(true);
//Create this table in the scratch database
var tbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("MyTable", new[]
{
new DatabaseColumnRequest("id", "int identity(1,1)", false)
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("Name", new DatabaseTypeRequest(typeof(string), 10), false)
});
TableInfo ti;
ColumnInfo[] cols;
var cata = Import(tbl, out ti, out cols);
var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, cata);
planManager.TargetDatabase = db;
var nameCol = cols.Single(c => c.GetRuntimeName().Equals("Name"));
//setup test rules for migrator
planManager.Plans[nameCol].Plan = Plan.Drop;
//rules should pass checks
planManager.Check(new ThrowImmediatelyCheckNotifier());
var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager);
engine.Execute();
var anoCatalogue = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Folder.Path.StartsWith("\\ano"));
Assert.IsTrue(anoCatalogue.Exists());
//should only be one (the id column
Assert.AreEqual(1, anoCatalogue.CatalogueItems.Length);
var idColInAnoDatabase = anoCatalogue.CatalogueItems[0].ColumnInfo;
Assert.AreEqual("int", idColInAnoDatabase.Data_type);
db.Drop();
var exports = CatalogueRepository.GetAllObjects <ObjectExport>().Count();
var imports = CatalogueRepository.GetAllObjects <ObjectImport>().Count();
Assert.AreEqual(exports, imports);
Assert.IsTrue(exports > 0);
}
public void CreateANOVersionTest()
{
var dbName = TestDatabaseNames.GetConsistentName("CreateANOVersionTest");
var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName);
db.Create(true);
BulkTestsData bulk = new BulkTestsData(CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn, 100);
bulk.SetupTestData();
bulk.ImportAsCatalogue();
var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, bulk.catalogue);
planManager.TargetDatabase = db;
//setup test rules for migrator
CreateMigrationRules(planManager, bulk);
//rules should pass checks
Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier()));
var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager);
engine.Execute();
var anoCatalogue = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Folder.Path.StartsWith("\\ano"));
Assert.IsTrue(anoCatalogue.Exists());
db.Drop();
var exports = CatalogueRepository.GetAllObjects <ObjectExport>().Count();
var imports = CatalogueRepository.GetAllObjects <ObjectImport>().Count();
Assert.AreEqual(exports, imports);
Assert.IsTrue(exports > 0);
}
public void CreateANOVersionTest_LookupsAndExtractionInformations()
{
var dbName = TestDatabaseNames.GetConsistentName("CreateANOVersionTest");
var db = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(dbName);
db.Create(true);
BulkTestsData bulk = new BulkTestsData(CatalogueRepository, DiscoveredDatabaseICanCreateRandomTablesIn, 100);
bulk.SetupTestData();
bulk.ImportAsCatalogue();
//Create a lookup table on the server
var lookupTbl = DiscoveredDatabaseICanCreateRandomTablesIn.CreateTable("z_sexLookup", new[]
{
new DatabaseColumnRequest("Code", "varchar(1)")
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("hb_Code", "varchar(1)")
{
IsPrimaryKey = true
},
new DatabaseColumnRequest("Description", "varchar(100)")
});
//import a reference to the table
TableInfoImporter importer = new TableInfoImporter(CatalogueRepository, lookupTbl);
ColumnInfo[] lookupColumnInfos;
TableInfo lookupTableInfo;
importer.DoImport(out lookupTableInfo, out lookupColumnInfos);
//Create a Lookup reference
var ciSex = bulk.catalogue.CatalogueItems.Single(c => c.Name == "sex");
var ciHb = bulk.catalogue.CatalogueItems.Single(c => c.Name == "hb_extract");
var eiChi = bulk.extractionInformations.Single(ei => ei.GetRuntimeName() == "chi");
eiChi.IsExtractionIdentifier = true;
eiChi.SaveToDatabase();
var eiCentury = bulk.extractionInformations.Single(ei => ei.GetRuntimeName() == "century");
eiCentury.HashOnDataRelease = true;
eiCentury.ExtractionCategory = ExtractionCategory.Internal;
eiCentury.SaveToDatabase();
//add a transform
var eiPostcode = bulk.extractionInformations.Single(ei => ei.GetRuntimeName() == "current_postcode");
eiPostcode.SelectSQL = string.Format("LEFT(10,{0}.[current_postcode])", eiPostcode.ColumnInfo.TableInfo.Name);
eiPostcode.Alias = "MyMutilatedColumn";
eiPostcode.SaveToDatabase();
//add a combo transform
var ciComboCol = new CatalogueItem(CatalogueRepository, bulk.catalogue, "ComboColumn");
var colForename = bulk.columnInfos.Single(c => c.GetRuntimeName() == "forename");
var colSurname = bulk.columnInfos.Single(c => c.GetRuntimeName() == "surname");
var eiComboCol = new ExtractionInformation(CatalogueRepository, ciComboCol, colForename, colForename + " + ' ' + " + colSurname);
eiComboCol.Alias = "ComboColumn";
eiComboCol.SaveToDatabase();
var eiDataLoadRunId = bulk.extractionInformations.Single(ei => ei.GetRuntimeName().Equals(SpecialFieldNames.DataLoadRunID));
eiDataLoadRunId.DeleteInDatabase();
var lookup = new Lookup(CatalogueRepository, lookupColumnInfos[2], ciSex.ColumnInfo, lookupColumnInfos[0], ExtractionJoinType.Left, null);
//now lets make it worse, lets assume the sex code changes per healthboard therefore the join to the lookup requires both fields sex and hb_extract
var compositeLookup = new LookupCompositeJoinInfo(CatalogueRepository, lookup, ciHb.ColumnInfo, lookupColumnInfos[1]);
//now lets make the _Desc field in the original Catalogue
int orderToInsertDescriptionFieldAt = ciSex.ExtractionInformation.Order;
//bump everyone down 1
foreach (var toBumpDown in bulk.catalogue.CatalogueItems.Select(ci => ci.ExtractionInformation).Where(e => e != null && e.Order > orderToInsertDescriptionFieldAt))
{
toBumpDown.Order++;
toBumpDown.SaveToDatabase();
}
var ciDescription = new CatalogueItem(CatalogueRepository, bulk.catalogue, "Sex_Desc");
var eiDescription = new ExtractionInformation(CatalogueRepository, ciDescription, lookupColumnInfos[2], lookupColumnInfos[2].Name);
eiDescription.Alias = "Sex_Desc";
eiDescription.Order = orderToInsertDescriptionFieldAt + 1;
eiDescription.ExtractionCategory = ExtractionCategory.Supplemental;
eiDescription.SaveToDatabase();
bulk.catalogue.ClearAllInjections();
//check it worked
QueryBuilder qb = new QueryBuilder(null, null);
qb.AddColumnRange(bulk.catalogue.GetAllExtractionInformation(ExtractionCategory.Any));
//The query builder should be able to succesfully create SQL
Console.WriteLine(qb.SQL);
//there should be 2 tables involved in the query [z_sexLookup] and [BulkData]
Assert.AreEqual(2, qb.TablesUsedInQuery.Count);
//the query builder should have identified the lookup
Assert.AreEqual(lookup, qb.GetDistinctRequiredLookups().Single());
//////////////////////////////////////////////////////////////////////////////////////The Actual Bit Being Tested////////////////////////////////////////////////////
var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, bulk.catalogue);
planManager.TargetDatabase = db;
//setup test rules for migrator
CreateMigrationRules(planManager, bulk);
//rules should pass checks
Assert.DoesNotThrow(() => planManager.Check(new ThrowImmediatelyCheckNotifier()));
var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager);
engine.Execute();
//////////////////////////////////////////////////////////////////////////////////////End The Actual Bit Being Tested////////////////////////////////////////////////////
var anoCatalogue = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Folder.Path.StartsWith("\\ano"));
Assert.IsTrue(anoCatalogue.Exists());
//The new Catalogue should have the same number of ExtractionInformations
var eiSource = bulk.catalogue.GetAllExtractionInformation(ExtractionCategory.Any).OrderBy(ei => ei.Order).ToArray();
var eiDestination = anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).OrderBy(ei => ei.Order).ToArray();
Assert.AreEqual(eiSource.Length, eiDestination.Length, "Both the new and the ANO catalogue should have the same number of ExtractionInformations (extractable columns)");
for (int i = 0; i < eiSource.Length; i++)
{
Assert.AreEqual(eiSource[i].Order, eiDestination[i].Order, "ExtractionInformations in the source and destination Catalogue should have the same order");
Assert.AreEqual(eiSource[i].GetRuntimeName(),
eiDestination[i].GetRuntimeName().Replace("ANO", ""), "ExtractionInformations in the source and destination Catalogue should have the same names (excluding ANO prefix)");
Assert.AreEqual(eiSource[i].ExtractionCategory, eiDestination[i].ExtractionCategory, "Old / New ANO ExtractionInformations did not match on ExtractionCategory");
Assert.AreEqual(eiSource[i].IsExtractionIdentifier, eiDestination[i].IsExtractionIdentifier, "Old / New ANO ExtractionInformations did not match on IsExtractionIdentifier");
Assert.AreEqual(eiSource[i].HashOnDataRelease, eiDestination[i].HashOnDataRelease, "Old / New ANO ExtractionInformations did not match on HashOnDataRelease");
Assert.AreEqual(eiSource[i].IsPrimaryKey, eiDestination[i].IsPrimaryKey, "Old / New ANO ExtractionInformations did not match on IsPrimaryKey");
}
//check it worked
QueryBuilder qbdestination = new QueryBuilder(null, null);
qbdestination.AddColumnRange(anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any));
//The query builder should be able to succesfully create SQL
Console.WriteLine(qbdestination.SQL);
var anoEiPostcode = anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(ei => ei.GetRuntimeName().Equals("MyMutilatedColumn"));
//The transform on postcode should have been refactored to the new table name and preserve the scalar function LEFT...
Assert.AreEqual(string.Format("LEFT(10,{0}.[current_postcode])", anoEiPostcode.ColumnInfo.TableInfo.GetFullyQualifiedName()), anoEiPostcode.SelectSQL);
var anoEiComboCol = anoCatalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(ei => ei.GetRuntimeName().Equals("ComboColumn"));
//The transform on postcode should have been refactored to the new table name and preserve the scalar function LEFT...
Assert.AreEqual(string.Format("{0}.[forename] + ' ' + {0}.[surname]", anoEiPostcode.ColumnInfo.TableInfo.GetFullyQualifiedName()), anoEiComboCol.SelectSQL);
//there should be 2 tables involved in the query [z_sexLookup] and [BulkData]
Assert.AreEqual(2, qbdestination.TablesUsedInQuery.Count);
//the query builder should have identified the lookup but it should be the new one not the old one
Assert.AreEqual(1, qbdestination.GetDistinctRequiredLookups().Count(), "New query builder for ano catalogue did not correctly identify that there was a Lookup");
Assert.AreNotEqual(lookup, qbdestination.GetDistinctRequiredLookups().Single(), "New query builder for ano catalogue identified the OLD Lookup!");
Assert.AreEqual(1, qbdestination.GetDistinctRequiredLookups().Single().GetSupplementalJoins().Count(), "The new Lookup did not have the composite join key (sex/hb_extract)");
Assert.AreNotEqual(compositeLookup, qbdestination.GetDistinctRequiredLookups().Single().GetSupplementalJoins(), "New query builder for ano catalogue identified the OLD LookupCompositeJoinInfo!");
db.Drop();
var exports = CatalogueRepository.GetAllObjects <ObjectExport>().Count();
var imports = CatalogueRepository.GetAllObjects <ObjectImport>().Count();
Assert.AreEqual(exports, imports);
Assert.IsTrue(exports > 0);
}
public void TestAnonymisingJoinKey()
{
//Create a plan for the first Catlogue (Tests) - single Table dataset
var plan1 = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, cata1);
var testIdHeadPlan = plan1.GetPlanForColumnInfo(c1.Single(c => c.GetRuntimeName().Equals("TestId")));
plan1.TargetDatabase = _destinationDatabase;
//the plan is that the column TestId should be anonymised - where it's name will become ANOTestId
testIdHeadPlan.Plan = Plan.ANO;
testIdHeadPlan.ANOTable = _anoTable;
plan1.Check(new ThrowImmediatelyCheckNotifier());
var engine1 = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, plan1);
engine1.Execute();
var plan1ExtractionInformationsAtDestination = engine1.NewCatalogue.GetAllExtractionInformation(ExtractionCategory.Any);
var ei1 = plan1ExtractionInformationsAtDestination.Single(e => e.GetRuntimeName().Equals("ANOTestId"));
Assert.IsTrue(ei1.Exists());
//Now create a plan for the combo Catalogue which contains references to both tables (Tests and Results). Remember Tests has already been migrated as part of plan1
var plan2 = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, _comboCata);
//tell it to skip table 1 (Tests) and only anonymise Results
plan2.SkippedTables.Add(t1);
plan2.TargetDatabase = _destinationDatabase;
plan2.Check(new ThrowImmediatelyCheckNotifier());
//Run the anonymisation
var engine2 = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, plan2);
engine2.Execute();
//Did it succesfully pick SetUp the correct ANO column
var plan2ExtractionInformationsAtDestination = engine2.NewCatalogue.GetAllExtractionInformation(ExtractionCategory.Any);
var ei2 = plan2ExtractionInformationsAtDestination.Single(e => e.GetRuntimeName().Equals("ANOTestId"));
Assert.IsTrue(ei2.Exists());
//and can the query be executed succesfully
var qb = new QueryBuilder(null, null);
qb.AddColumnRange(plan2ExtractionInformationsAtDestination);
using (var con = _destinationDatabase.Server.GetConnection())
{
con.Open();
var cmd = _destinationDatabase.Server.GetCommand(qb.SQL, con);
Assert.DoesNotThrow(() => cmd.ExecuteNonQuery());
}
Console.WriteLine("Final migrated combo dataset SQL was:" + qb.SQL);
Assert.IsTrue(_comboCata.CatalogueItems.Any(ci => ci.Name.Equals("Measuree")));
Assert.IsTrue(engine2.NewCatalogue.CatalogueItems.Any(ci => ci.Name.Equals("Measuree")), "ANO Catalogue did not respect the original CatalogueItem Name");
}