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); }
private void btnExecute_Click(object sender, EventArgs e) { try { var engine = new ForwardEngineerANOCatalogueEngine(Activator.RepositoryLocator, _planManager); engine.Execute(); if (engine.NewCatalogue != null && engine.LoadMetadata != null) { foreach (KeyValuePair <ITableInfo, QueryBuilder> sqls in engine.SelectSQLForMigrations) { CreateAttacher(sqls.Key, sqls.Value, engine.LoadMetadata, sqls.Key.IsLookupTable()? null:engine.LoadProgressIfAny); } foreach (KeyValuePair <PreLoadDiscardedColumn, IDilutionOperation> dilutionOps in engine.DilutionOperationsForMigrations) { CreateDilutionMutilation(dilutionOps, engine.LoadMetadata); } Publish(engine.NewCatalogue); if (Activator.YesNo("Successfully created Catalogue '" + engine.NewCatalogue + "', close form?", "Success")) { Activator.WindowArranger.SetupEditAnything(this, engine.LoadMetadata); } } else { throw new Exception("Engine did not create a NewCatalogue/LoadMetadata"); } } catch (Exception ex) { ExceptionViewer.Show(ex); } }
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 CreateANOVersion_TestSkippingTables(bool tableInfoAlreadyExistsForSkippedTable, bool putPlanThroughSerialization) { var dbFrom = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(TestDatabaseNames.GetConsistentName("CreateANOVersion_TestSkippingTables_From")); var dbTo = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(TestDatabaseNames.GetConsistentName("CreateANOVersion_TestSkippingTables_To")); dbFrom.Create(true); dbTo.Create(true); try { var tblFromHeads = dbFrom.CreateTable("Heads", new[] { new DatabaseColumnRequest("SkullColor", "varchar(10)"), new DatabaseColumnRequest("Vertebrae", "varchar(25)") }); var cols = new[] { new DatabaseColumnRequest("SpineColor", "varchar(10)"), new DatabaseColumnRequest("Vertebrae", "varchar(25)") }; var tblFromNeck = dbFrom.CreateTable("Necks", cols); //Necks table already exists in the destination so will be skipped for migration but still needs to be imported var tblToNeck = dbTo.CreateTable("Necks", cols); TableInfo fromHeadsTableInfo; ColumnInfo[] fromHeadsColumnInfo; TableInfo fromNeckTableInfo; ColumnInfo[] fromNeckColumnInfo; TableInfo toNecksTableInfo = null; ColumnInfo[] toNecksColumnInfo = null; TableInfoImporter i1 = new TableInfoImporter(CatalogueRepository, tblFromHeads); i1.DoImport(out fromHeadsTableInfo, out fromHeadsColumnInfo); TableInfoImporter i2 = new TableInfoImporter(CatalogueRepository, tblFromNeck); i2.DoImport(out fromNeckTableInfo, out fromNeckColumnInfo); //Table already exists but does the in Catalogue reference exist? if (tableInfoAlreadyExistsForSkippedTable) { TableInfoImporter i3 = new TableInfoImporter(CatalogueRepository, tblToNeck); i3.DoImport(out toNecksTableInfo, out toNecksColumnInfo); } //Create a JoinInfo so the query builder knows how to connect the tables new JoinInfo(CatalogueRepository, fromHeadsColumnInfo.Single(c => c.GetRuntimeName().Equals("Vertebrae")), fromNeckColumnInfo.Single(c => c.GetRuntimeName().Equals("Vertebrae")), ExtractionJoinType.Inner, null ); var cataEngineer = new ForwardEngineerCatalogue(fromHeadsTableInfo, fromHeadsColumnInfo, true); Catalogue cata; CatalogueItem[] cataItems; ExtractionInformation[] extractionInformations; cataEngineer.ExecuteForwardEngineering(out cata, out cataItems, out extractionInformations); var cataEngineer2 = new ForwardEngineerCatalogue(fromNeckTableInfo, fromNeckColumnInfo, true); cataEngineer2.ExecuteForwardEngineering(cata); //4 extraction informations in from Catalogue (2 from Heads and 2 from Necks) Assert.AreEqual(cata.GetAllExtractionInformation(ExtractionCategory.Any).Count(), 4); //setup ANOTable on head var anoTable = new ANOTable(CatalogueRepository, ANOStore_ExternalDatabaseServer, "ANOSkullColor", "C"); anoTable.NumberOfCharactersToUseInAnonymousRepresentation = 10; anoTable.SaveToDatabase(); anoTable.PushToANOServerAsNewTable("varchar(10)", new ThrowImmediatelyCheckNotifier()); //////////////////The actual test!///////////////// var planManager = new ForwardEngineerANOCataloguePlanManager(RepositoryLocator, cata); //ano the table SkullColor var scPlan = planManager.GetPlanForColumnInfo(fromHeadsColumnInfo.Single(col => col.GetRuntimeName().Equals("SkullColor"))); scPlan.ANOTable = anoTable; scPlan.Plan = Plan.ANO; if (putPlanThroughSerialization) { var asString = JsonConvertExtensions.SerializeObject(planManager, RepositoryLocator); planManager = (ForwardEngineerANOCataloguePlanManager)JsonConvertExtensions.DeserializeObject(asString, typeof(ForwardEngineerANOCataloguePlanManager), RepositoryLocator); } //not part of serialization planManager.TargetDatabase = dbTo; planManager.SkippedTables.Add(fromNeckTableInfo);//skip the necks table because it already exists (ColumnInfos may or may not exist but physical table definetly does) var engine = new ForwardEngineerANOCatalogueEngine(RepositoryLocator, planManager); if (!tableInfoAlreadyExistsForSkippedTable) { var ex = Assert.Throws <Exception>(engine.Execute); Assert.IsTrue(Regex.IsMatch(ex.InnerException.Message, "Found '0' ColumnInfos called")); Assert.IsTrue(Regex.IsMatch(ex.InnerException.Message, "[Necks].[SpineColor]")); return; } else { engine.Execute(); } var newCata = CatalogueRepository.GetAllObjects <Catalogue>().Single(c => c.Name.Equals("ANOHeads")); Assert.IsTrue(newCata.Exists()); var newCataItems = newCata.CatalogueItems; Assert.AreEqual(newCataItems.Count(), 4); //should be extraction informations //all extraction informations should point to the new table location Assert.IsTrue(newCataItems.All(ci => ci.ExtractionInformation.SelectSQL.Contains(dbTo.GetRuntimeName()))); //these columns should all exist Assert.IsTrue(newCataItems.Any(ci => ci.ExtractionInformation.SelectSQL.Contains("SkullColor"))); Assert.IsTrue(newCataItems.Any(ci => ci.ExtractionInformation.SelectSQL.Contains("SpineColor"))); Assert.IsTrue(newCataItems.Any(ci => ci.ExtractionInformation.SelectSQL.Contains("Vertebrae"))); //actually there will be 2 copies of this one from Necks one from Heads //new ColumnInfo should have a reference to the anotable Assert.IsTrue(newCataItems.Single(ci => ci.Name.Equals("ANOSkullColor")).ColumnInfo.ANOTable_ID == anoTable.ID); var newSpineColorColumnInfo = newCataItems.Single(ci => ci.Name.Equals("ANOSkullColor")).ColumnInfo; //table info already existed, make sure the new CatalogueItems point to the same columninfos / table infos Assert.IsTrue(newCataItems.Select(ci => ci.ColumnInfo).Contains(newSpineColorColumnInfo)); } finally { dbFrom.Drop(); dbTo.Drop(); } }
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"); }