public void TestMerge(DatabaseType databaseType) { //microsoft one gets called for free in test setup (see base class) if (databaseType != DatabaseType.MicrosoftSQLServer) { SetupFromTo(databaseType); } var dt = new DataTable(); var colName = new DataColumn("Name", typeof(string)); var colAge = new DataColumn("Age", typeof(int)); dt.Columns.Add(colName); dt.Columns.Add(colAge); dt.Columns.Add("Postcode", typeof(string)); //Data in live awaiting toTbl be updated dt.Rows.Add(new object[] { "Dave", 18, "DD3 1AB" }); dt.Rows.Add(new object[] { "Dave", 25, "DD1 1XS" }); dt.Rows.Add(new object[] { "Mango", 32, DBNull.Value }); dt.Rows.Add(new object[] { "Filli", 32, "DD3 78L" }); dt.Rows.Add(new object[] { "Mandrake", 32, DBNull.Value }); dt.PrimaryKey = new[] { colName, colAge }; var toTbl = To.CreateTable("ToTable", dt); Assert.IsTrue(toTbl.DiscoverColumn("Name").IsPrimaryKey); Assert.IsTrue(toTbl.DiscoverColumn("Age").IsPrimaryKey); Assert.IsFalse(toTbl.DiscoverColumn("Postcode").IsPrimaryKey); dt.Rows.Clear(); //new data being loaded dt.Rows.Add(new object[] { "Dave", 25, "DD1 1PS" }); //update toTbl change postcode toTbl "DD1 1PS" dt.Rows.Add(new object[] { "Chutney", 32, DBNull.Value }); //new insert Chutney dt.Rows.Add(new object[] { "Mango", 32, DBNull.Value }); //ignored because already present in dataset dt.Rows.Add(new object[] { "Filli", 32, DBNull.Value }); //update from "DD3 78L" null dt.Rows.Add(new object[] { "Mandrake", 32, "DD1 1PS" }); //update from null toTbl "DD1 1PS" dt.Rows.Add(new object[] { "Mandrake", 31, "DD1 1PS" }); // insert because Age is unique (and part of pk) var fromTbl = From.CreateTable(DatabaseName + "_ToTable_STAGING", dt); //import the toTbl table as a TableInfo TableInfo ti; ColumnInfo[] cis; var cata = Import(toTbl, out ti, out cis); //put the backup trigger on the live table (this will also create the needed hic_ columns etc) var triggerImplementer = new TriggerImplementerFactory(databaseType).Create(toTbl); triggerImplementer.CreateTrigger(new ThrowImmediatelyCheckNotifier()); var configuration = new MigrationConfiguration(From, LoadBubble.Staging, LoadBubble.Live, new FixedStagingDatabaseNamer(toTbl.Database.GetRuntimeName(), fromTbl.Database.GetRuntimeName())); var lmd = new LoadMetadata(CatalogueRepository); cata.LoadMetadata_ID = lmd.ID; cata.SaveToDatabase(); var migrationHost = new MigrationHost(From, To, configuration, new HICDatabaseConfiguration(lmd)); //set SetUp a logging task var logServer = new ServerDefaults(CatalogueRepository).GetDefaultFor(PermissableDefaults.LiveLoggingServer_ID); var logManager = new LogManager(logServer); logManager.CreateNewLoggingTaskIfNotExists("CrossDatabaseMergeCommandTest"); var dli = logManager.CreateDataLoadInfo("CrossDatabaseMergeCommandTest", "tests", "running test", "", true); var job = new ThrowImmediatelyDataLoadJob(); job.DataLoadInfo = dli; job.RegularTablesToLoad = new List <ITableInfo>(new[] { ti }); migrationHost.Migrate(job, new GracefulCancellationToken()); var resultantDt = toTbl.GetDataTable(); Assert.AreEqual(7, resultantDt.Rows.Count); AssertRowEquals(resultantDt, "Dave", 25, "DD1 1PS"); AssertRowEquals(resultantDt, "Chutney", 32, DBNull.Value); AssertRowEquals(resultantDt, "Mango", 32, DBNull.Value); AssertRowEquals(resultantDt, "Filli", 32, DBNull.Value); AssertRowEquals(resultantDt, "Mandrake", 32, "DD1 1PS"); AssertRowEquals(resultantDt, "Mandrake", 31, "DD1 1PS"); AssertRowEquals(resultantDt, "Dave", 18, "DD3 1AB"); var archival = logManager.GetArchivalDataLoadInfos("CrossDatabaseMergeCommandTest", new CancellationToken()); var log = archival.First(); Assert.AreEqual(dli.ID, log.ID); Assert.AreEqual(2, log.TableLoadInfos.Single().Inserts); Assert.AreEqual(3, log.TableLoadInfos.Single().Updates); }
public void CohortIdentificationConfiguration_Join_PatientIndexTable() { DataTable header = new DataTable(); header.Columns.Add("ID"); header.Columns.Add("Chi"); header.Columns.Add("Age"); header.Columns.Add("Date"); header.Columns.Add("Healthboard"); header.PrimaryKey = new [] { header.Columns["ID"] }; header.Rows.Add("1", "0101010101", 50, new DateTime(2001, 1, 1), "T"); header.Rows.Add("2", "0202020202", 50, new DateTime(2002, 2, 2), "T"); var hTbl = From.CreateTable("header", header); var cata = Import(hTbl, out TableInfo hTi, out _); cata.Name = "My Combo Join Catalogue"; cata.SaveToDatabase(); var scripter = new MasterDatabaseScriptExecutor(To); var patcher = new QueryCachingPatcher(); scripter.CreateAndPatchDatabase(patcher, new AcceptAllCheckNotifier()); var edsCache = new ExternalDatabaseServer(CatalogueRepository, "Cache", new QueryCachingPatcher()); edsCache.SetProperties(To); DataTable results = new DataTable(); results.Columns.Add("Header_ID"); results.Columns.Add("TestCode"); results.Columns.Add("Result"); results.Rows.Add("1", "HBA1C", 50); results.Rows.Add("1", "ECOM", "Hi fellas"); results.Rows.Add("1", "ALB", 100); results.Rows.Add("2", "ALB", 50); var rTbl = From.CreateTable("results", results); var importer = new TableInfoImporter(CatalogueRepository, rTbl); importer.DoImport(out TableInfo rTi, out ColumnInfo[] rColInfos); var fe = new ForwardEngineerCatalogue(rTi, rColInfos, true); fe.ExecuteForwardEngineering(cata); //Should now be 1 Catalogue with all the columns (tables will have to be joined to build the query though) Assert.AreEqual(8, cata.GetAllExtractionInformation(ExtractionCategory.Core).Length); var ji = new JoinInfo(CatalogueRepository, rTi.ColumnInfos.Single(ci => ci.GetRuntimeName().Equals("Header_ID", StringComparison.CurrentCultureIgnoreCase)), hTi.ColumnInfos.Single(ci => ci.GetRuntimeName().Equals("ID", StringComparison.CurrentCultureIgnoreCase)), ExtractionJoinType.Right, null ); //setup a cic that uses the cache var cic = new CohortIdentificationConfiguration(CatalogueRepository, "MyCic"); cic.CreateRootContainerIfNotExists(); cic.QueryCachingServer_ID = edsCache.ID; cic.SaveToDatabase(); //create a patient index table that shows all the times that they had a test in any HB (with the HB being part of the result set) var acPatIndex = new AggregateConfiguration(CatalogueRepository, cata, "My PatIndes"); var eiChi = cata.GetAllExtractionInformation(ExtractionCategory.Core).Single(ei => ei.GetRuntimeName().Equals("Chi")); eiChi.IsExtractionIdentifier = true; acPatIndex.CountSQL = null; eiChi.SaveToDatabase(); acPatIndex.AddDimension(eiChi); acPatIndex.AddDimension(cata.GetAllExtractionInformation(ExtractionCategory.Core).Single(ei => ei.GetRuntimeName().Equals("Date"))); acPatIndex.AddDimension(cata.GetAllExtractionInformation(ExtractionCategory.Core).Single(ei => ei.GetRuntimeName().Equals("Healthboard"))); cic.EnsureNamingConvention(acPatIndex); var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cic, acPatIndex); Assert.IsTrue(acPatIndex.IsCohortIdentificationAggregate); Assert.IsTrue(acPatIndex.IsJoinablePatientIndexTable()); var compiler = new CohortCompiler(cic); var runner = new CohortCompilerRunner(compiler, 50); var cancellation = new System.Threading.CancellationToken(); runner.Run(cancellation); //they should not be executing and should be completed Assert.IsFalse(compiler.Tasks.Any(t => t.Value.IsExecuting)); Assert.AreEqual(Phase.Finished, runner.ExecutionPhase); var manager = new CachedAggregateConfigurationResultsManager(edsCache); var cacheTableName = manager.GetLatestResultsTableUnsafe(acPatIndex, AggregateOperation.JoinableInceptionQuery); Assert.IsNotNull(cacheTableName, "No results were cached!"); var cacheTable = To.ExpectTable(cacheTableName.GetRuntimeName()); //chi, Date and TestCode Assert.AreEqual(3, cacheTable.DiscoverColumns().Length); //healthboard should be a string Assert.AreEqual(typeof(string), cacheTable.DiscoverColumn("Healthboard").DataType.GetCSharpDataType()); /* Query Cache contains this: * * Chi Date Healthboard * 0101010101 2001-01-01 00:00:00.0000000 T * 0202020202 2002-02-02 00:00:00.0000000 T */ Assert.AreEqual(2, cacheTable.GetRowCount()); //Now we could add a new AggregateConfiguration that uses the joinable! }