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!
}
