private void btnCreateNewANOTable_Click(object sender, EventArgs e) { try { var server = ddExternalDatabaseServer.SelectedItem as ExternalDatabaseServer; var a = new ANOTable(Activator.RepositoryLocator.CatalogueRepository, server, tbANOTableName.Text, tbSuffix.Text); //if we know the type is e.g. varchar(5) var length = ColumnInfo.Discover(DataAccessContext.InternalDataProcessing).DataType.GetLengthIfString(); if (length > 0) { a.NumberOfIntegersToUseInAnonymousRepresentation = 0; a.NumberOfCharactersToUseInAnonymousRepresentation = length;//give it a sensible maximal that will work a.SaveToDatabase(); } ANOTable = a;//and set the property to it to populate the rest of the form gbANOTable.Enabled = true; gbSelectExistingANOTable.Enabled = false; gbCreateNewANOTable.Enabled = false; } catch (Exception exception) { checksUI1.OnCheckPerformed(new CheckEventArgs("Could not create ANOTable", CheckResult.Fail, exception)); } }
private void CreateMigrationRules(ForwardEngineerANOCataloguePlanManager planManager, BulkTestsData bulk) { var chi = bulk.GetColumnInfo("chi"); var anoChi = new ANOTable(CatalogueRepository, ANOStore_ExternalDatabaseServer, "ANOCHI", "C"); anoChi.NumberOfIntegersToUseInAnonymousRepresentation = 9; anoChi.NumberOfCharactersToUseInAnonymousRepresentation = 1; anoChi.SaveToDatabase(); anoChi.PushToANOServerAsNewTable(chi.Data_type, new ThrowImmediatelyCheckNotifier()); planManager.GetPlanForColumnInfo(chi).Plan = Plan.ANO; planManager.GetPlanForColumnInfo(chi).ANOTable = anoChi; var dob = bulk.GetColumnInfo("date_of_birth"); planManager.GetPlanForColumnInfo(dob).Plan = Plan.Dilute; planManager.GetPlanForColumnInfo(dob).Dilution = new RoundDateToMiddleOfQuarter(); var postcode = bulk.GetColumnInfo("current_postcode"); planManager.GetPlanForColumnInfo(postcode).Plan = Plan.Dilute; planManager.GetPlanForColumnInfo(postcode).Dilution = new ExcludeRight3OfUKPostcodes(); }
protected override void SetUp() { base.SetUp(); var db = GetCleanedServer(FAnsi.DatabaseType.MicrosoftSQLServer); BlitzMainDataTables(); DeleteANOEndpoint(); ANOTable remnantANO = CatalogueRepository.GetAllObjects <ANOTable>().SingleOrDefault(a => a.TableName.Equals("ANOCondition")); if (remnantANO != null) { remnantANO.DeleteInDatabase(); } //cleanup foreach (var remnant in CatalogueRepository.GetAllObjects <TableInfo>().Where(t => t.GetRuntimeName().Equals(TableName))) { remnant.DeleteInDatabase(); } const string sql = @" CREATE TABLE [ANOMigration]( [AdmissionDate] [datetime] NOT NULL, [DischargeDate] [datetime] NOT NULL, [Condition1] [varchar](4) NOT NULL, [Condition2] [varchar](4) NULL, [Condition3] [varchar](4) NULL, [Condition4] [varchar](4) NULL, [CHI] [varchar](10) NOT NULL CONSTRAINT [PK_ANOMigration] PRIMARY KEY CLUSTERED ( [AdmissionDate] ASC, [Condition1] ASC, [CHI] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x000001B300000000 AS DateTime), CAST(0x000001B600000000 AS DateTime), N'Z61', N'Z29', NULL, N'Z11', N'0809003082') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000021D00000000 AS DateTime), CAST(0x0000022600000000 AS DateTime), N'P024', N'Q230', NULL,N'Z11', N'1610007810') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000032900000000 AS DateTime), CAST(0x0000032A00000000 AS DateTime), N'L73', NULL, NULL, NULL, N'2407011022') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x000004EA00000000 AS DateTime), CAST(0x000004EA00000000 AS DateTime), N'Y523', N'Z29', NULL, NULL, N'1104015472') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000060300000000 AS DateTime), CAST(0x0000060800000000 AS DateTime), N'F721', N'B871', NULL, NULL, N'0203025927') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000065300000000 AS DateTime), CAST(0x0000065700000000 AS DateTime), N'Z914', N'J398', NULL, NULL, N'2702024715') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000070100000000 AS DateTime), CAST(0x0000070800000000 AS DateTime), N'N009', N'V698', NULL, NULL, N'1610007810') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000077000000000 AS DateTime), CAST(0x0000077200000000 AS DateTime), N'E44', N'J050', N'Q560', NULL, N'1610007810') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x000007E800000000 AS DateTime), CAST(0x000007EA00000000 AS DateTime), N'Q824', NULL, NULL, NULL, N'1110029231') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000087700000000 AS DateTime), CAST(0x0000087F00000000 AS DateTime), N'T020', NULL, NULL, NULL, N'2110021261') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x0000088A00000000 AS DateTime), CAST(0x0000089300000000 AS DateTime), N'G009', NULL, NULL, NULL, N'0706013071') INSERT [ANOMigration] ([AdmissionDate], [DischargeDate], [Condition1], [Condition2], [Condition3], [Condition4], [CHI]) VALUES (CAST(0x000008CA00000000 AS DateTime), CAST(0x000008D100000000 AS DateTime), N'T47', N'H311', N'O037', NULL, N'1204057592')"; var server = db.Server; using (var con = server.GetConnection()) { con.Open(); server.GetCommand(sql, con).ExecuteNonQuery(); } var table = db.ExpectTable(TableName); TableInfoImporter importer = new TableInfoImporter(CatalogueRepository, table); importer.DoImport(out _tableInfo, out _columnInfos); //Configure the structure of the ANO transform we want - identifiers should have 3 characters and 2 ints and end with _C _anoConditionTable = new ANOTable(CatalogueRepository, ANOStore_ExternalDatabaseServer, "ANOCondition", "C"); _anoConditionTable.NumberOfCharactersToUseInAnonymousRepresentation = 3; _anoConditionTable.NumberOfIntegersToUseInAnonymousRepresentation = 2; _anoConditionTable.SaveToDatabase(); _anoConditionTable.PushToANOServerAsNewTable("varchar(4)", new ThrowImmediatelyCheckNotifier()); }
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(); } }
protected override void SetUp() { base.SetUp(); string sql = @"CREATE TABLE [dbo].[Tests]( [chi] [varchar](10) NULL, [Date] [datetime] NULL, [hb_extract] [varchar](1) NULL, [TestId] [int] NOT NULL, CONSTRAINT [PK_Tests] PRIMARY KEY CLUSTERED ( [TestId] ASC ) ) GO CREATE TABLE [dbo].[Results]( [TestId] [int] NOT NULL, [Measure] [varchar](10) NOT NULL, [Value] [int] NULL, CONSTRAINT [PK_Results] PRIMARY KEY CLUSTERED ( [TestId] ASC, [Measure] ASC ) ) GO ALTER TABLE [dbo].[Results] WITH CHECK ADD CONSTRAINT [FK_Results_Tests] FOREIGN KEY([TestId]) REFERENCES [dbo].[Tests] ([TestId]) GO"; var server = From.Server; using (var con = server.GetConnection()) { con.Open(); UsefulStuff.ExecuteBatchNonQuery(sql, con); } var importer1 = new TableInfoImporter(CatalogueRepository, From.ExpectTable("Tests")); var importer2 = new TableInfoImporter(CatalogueRepository, From.ExpectTable("Results")); importer1.DoImport(out t1, out c1); importer2.DoImport(out t2, out c2); var engineer1 = new ForwardEngineerCatalogue(t1, c1, true); var engineer2 = new ForwardEngineerCatalogue(t2, c2, true); engineer1.ExecuteForwardEngineering(out cata1, out cataItems1, out eis1); engineer2.ExecuteForwardEngineering(out cata2, out cataItems2, out eis2); new JoinInfo(CatalogueRepository, c1.Single(e => e.GetRuntimeName().Equals("TestId")), c2.Single(e => e.GetRuntimeName().Equals("TestId")), ExtractionJoinType.Left, null); _anoTable = new ANOTable(CatalogueRepository, ANOStore_ExternalDatabaseServer, "ANOTes", "T"); _anoTable.NumberOfCharactersToUseInAnonymousRepresentation = 10; _anoTable.SaveToDatabase(); _anoTable.PushToANOServerAsNewTable("int", new ThrowImmediatelyCheckNotifier()); _comboCata = new Catalogue(CatalogueRepository, "Combo Catalogue"); //pk var ciTestId = new CatalogueItem(CatalogueRepository, _comboCata, "TestId"); var colTestId = c1.Single(c => c.GetRuntimeName().Equals("TestId")); ciTestId.ColumnInfo_ID = colTestId.ID; ciTestId.SaveToDatabase(); var eiTestId = new ExtractionInformation(CatalogueRepository, ciTestId, colTestId, colTestId.Name); //Measure var ciMeasure = new CatalogueItem(CatalogueRepository, _comboCata, "Measuree"); var colMeasure = c2.Single(c => c.GetRuntimeName().Equals("Measure")); ciMeasure.ColumnInfo_ID = colMeasure.ID; ciMeasure.SaveToDatabase(); var eiMeasure = new ExtractionInformation(CatalogueRepository, ciMeasure, colMeasure, colMeasure.Name); //Date var ciDate = new CatalogueItem(CatalogueRepository, _comboCata, "Dat"); var colDate = c1.Single(c => c.GetRuntimeName().Equals("Date")); ciDate.ColumnInfo_ID = colDate.ID; ciDate.SaveToDatabase(); var eiDate = new ExtractionInformation(CatalogueRepository, ciDate, colDate, colDate.Name); _destinationDatabase = To; }