public void TestIPluginCohortCompiler_PopulatesCacheCorrectly()
{
var activator = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier())
{
DisallowInput = true
};
// create a cohort config
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "mycic");
cic.QueryCachingServer_ID = externalDatabaseServer.ID;
cic.SaveToDatabase();
// this special Catalogue will be detected by ExamplePluginCohortCompiler and interpreted as an API call
var myApi = new Catalogue(CatalogueRepository, ExamplePluginCohortCompiler.ExampleAPIName);
// add it to the cohort config
cic.CreateRootContainerIfNotExists();
// create a use of the API as an AggregateConfiguration
var cmd = new ExecuteCommandAddCatalogueToCohortIdentificationSetContainer(activator, new CatalogueCombineable(myApi), cic.RootCohortAggregateContainer);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
// run the cic
var source = new CohortIdentificationConfigurationSource();
source.PreInitialize(cic, new ThrowImmediatelyDataLoadEventListener());
var dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
// 5 random chi numbers
Assert.AreEqual(5, dt.Rows.Count);
// test stale
cmd.AggregateCreatedIfAny.Description = "2";
cmd.AggregateCreatedIfAny.SaveToDatabase();
// run the cic again
source = new CohortIdentificationConfigurationSource();
source.PreInitialize(cic, new ThrowImmediatelyDataLoadEventListener());
dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
// because the rules changed to generate 2 chis only there should be a new result
Assert.AreEqual(2, dt.Rows.Count);
var results = new[] { (string)dt.Rows[0][0], (string)dt.Rows[1][0] };
// run the cic again with no changes, the results should be unchanged since there is no config changed
// I.e. no new chis should be generated and the cached values returned
source = new CohortIdentificationConfigurationSource();
source.PreInitialize(cic, new ThrowImmediatelyDataLoadEventListener());
dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
Assert.AreEqual(2, dt.Rows.Count);
var results2 = new[] { (string)dt.Rows[0][0], (string)dt.Rows[1][0] };
Assert.AreEqual(results[0], results2[0]);
Assert.AreEqual(results[1], results2[1]);
}
/// <summary>
/// Rebuilds the CohortCompiler diagram which shows all the currently configured tasks
/// </summary>
/// <param name="cancelTasks"></param>
private void RecreateAllTasks(bool cancelTasks = true)
{
if (cancelTasks)
{
Compiler.CancelAllTasks(false);
}
tlvConfiguration.ClearObjects();
tlvConfiguration.Enabled = true;
_cic.CreateRootContainerIfNotExists();
//if there is no root container,create one
_root = _cic.RootCohortAggregateContainer;
_globals = _cic.GetAllParameters();
//Could have configured/unconfigured a joinable state
foreach (var j in Compiler.Tasks.Keys.OfType <JoinableTask>())
{
j.RefreshIsUsedState();
}
try
{
tlvConfiguration.AddObject(new CohortIdentificationHeader());
tlvConfiguration.AddObject(new JoinableCollectionNode(_cic, _cic.GetAllJoinables()));
tlvConfiguration.ExpandAll();
}
catch (Exception e)
{
tlvConfiguration.Enabled = false;
ExceptionViewer.Show("Failed to populate tree of Tasks", e);
}
}
private void SetupCohort(out DiscoveredDatabase db, out CohortIdentificationConfiguration cic, out DataTable dt)
{
dt = new DataTable();
dt.Columns.Add("PK");
//add lots of rows
for (int i = 0; i < 100000; i++)
{
dt.Rows.Add(i);
}
db = GetCleanedServer(DatabaseType.MicrosoftSQLServer, true);
var tbl = db.CreateTable("CohortCompilerRunnerTestsTable", dt);
var cata = Import(tbl);
var ei = cata.CatalogueItems[0].ExtractionInformation;
ei.IsExtractionIdentifier = true;
ei.SaveToDatabase();
var agg = new AggregateConfiguration(CatalogueRepository, cata, "MyAgg");
agg.CountSQL = null;
agg.SaveToDatabase();
var dimension = new AggregateDimension(CatalogueRepository, ei, agg);
cic = new CohortIdentificationConfiguration(CatalogueRepository, "MyCic");
cic.CreateRootContainerIfNotExists();
cic.RootCohortAggregateContainer.AddChild(agg, 0);
}
protected override void SetUp()
{
base.SetUp();
c = new Catalogue(CatalogueRepository, "MyCata");
ci = new CatalogueItem(CatalogueRepository, c, "MyCataItem");
ci2 = new CatalogueItem(CatalogueRepository, c, "YearColumn");
t = new TableInfo(CatalogueRepository, "MyTable");
col = new ColumnInfo(CatalogueRepository, "mycol", "varchar(10)", t);
col2 = new ColumnInfo(CatalogueRepository, "myOtherCol", "varchar(10)", t);
acCohort = new AggregateConfiguration(CatalogueRepository, c, CohortIdentificationConfiguration.CICPrefix + "Agg1_Cohort");
acDataset = new AggregateConfiguration(CatalogueRepository, c, "Agg2_Dataset");
ei_Year = new ExtractionInformation(CatalogueRepository, ci2, col2, "Year");
ei_Year.IsExtractionIdentifier = true;
ei_Year.SaveToDatabase();
acDataset.AddDimension(ei_Year);
acDataset.CountSQL = "count(*)";
acDataset.SaveToDatabase();
ei_Chi = new ExtractionInformation(CatalogueRepository, ci, col, "CHI");
ei_Chi.IsExtractionIdentifier = true;
ei_Chi.SaveToDatabase();
acCohort.AddDimension(ei_Chi);
cic = new CohortIdentificationConfiguration(CatalogueRepository, "mycic");
cic.CreateRootContainerIfNotExists();
cic.RootCohortAggregateContainer.AddChild(acCohort, 0);
}
public void Join_PatientIndexTable_OptionalCacheOnSameServer(DatabaseType dbType, bool createQueryCache)
{
/*
* Server1
* _____________
* |Biochemistry|
* ↓
* ___________________
* | Cache (optional) |
* ↓ join ↓
* _____________________
* | Hospital Admissions|
*
*/
var db = GetCleanedServer(dbType);
ExternalDatabaseServer cache = null;
if (createQueryCache)
{
cache = CreateCache(db);
}
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(db, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(db, people, r, cic, joinable);
cic.RootCohortAggregateContainer.AddChild(hospitalAdmissions, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
if (createQueryCache)
{
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("1/1")), "Expected cache to be used for the joinable");
}
else
{
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("0/1")), "Did not create cache so expected cache usage to be 0");
}
}
public void TestSimpleMerge()
{
var merger = new CohortIdentificationConfigurationMerger(CatalogueRepository);
var cic1 = new CohortIdentificationConfiguration(CatalogueRepository, "cic1");
var cic2 = new CohortIdentificationConfiguration(CatalogueRepository, "cic2");
cic1.CreateRootContainerIfNotExists();
var root1 = cic1.RootCohortAggregateContainer;
root1.Name = "Root1";
root1.SaveToDatabase();
root1.AddChild(aggregate1, 1);
cic2.CreateRootContainerIfNotExists();
var root2 = cic2.RootCohortAggregateContainer;
root2.Name = "Root2";
root2.SaveToDatabase();
root2.AddChild(aggregate2, 2);
Assert.AreEqual(1, cic1.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
Assert.AreEqual(1, cic2.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
int numberOfCicsBefore = CatalogueRepository.GetAllObjects <CohortIdentificationConfiguration>().Count();
var result = merger.Merge(new [] { cic1, cic2 }, SetOperation.UNION);
//original should still be intact
Assert.AreEqual(1, cic1.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
Assert.AreEqual(1, cic2.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
//the new merged set should contain both
Assert.AreEqual(2, result.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
Assert.IsFalse(result.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Any(c => c.Equals(aggregate1)), "Expected the merge to include clone aggregates not the originals! (aggregate1)");
Assert.IsFalse(result.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Any(c => c.Equals(aggregate2)), "Expected the merge to include clone aggregates not the originals! (aggregate2)");
// Now should be a new one
Assert.AreEqual(numberOfCicsBefore + 1, CatalogueRepository.GetAllObjects <CohortIdentificationConfiguration>().Count());
var newCicId = result.ID;
// Should have the root containers of the old configs
Assert.AreEqual("Root2", result.RootCohortAggregateContainer.GetSubContainers()[0].Name);
Assert.AreEqual("Root1", result.RootCohortAggregateContainer.GetSubContainers()[1].Name);
// And should have
Assert.AreEqual($"cic_{newCicId}_UnitTestAggregate2", result.RootCohortAggregateContainer.GetSubContainers()[0].GetAggregateConfigurations()[0].Name);
Assert.AreEqual($"cic_{newCicId}_UnitTestAggregate1", result.RootCohortAggregateContainer.GetSubContainers()[1].GetAggregateConfigurations()[0].Name);
Assert.AreEqual($"Merged cics (IDs {cic1.ID},{cic2.ID})", result.Name);
Assert.IsTrue(cic1.Exists());
Assert.IsTrue(cic2.Exists());
}
private void btnGo_Click(object sender, EventArgs e)
{
if (string.IsNullOrWhiteSpace(tbName.Text))
{
MessageBox.Show("Enter a name for your Cohort Identification Criteria");
return;
}
if (!Activator.YesNo("Are you sure you are happy with your configuration, this wizard will close after creating?", "Confirm"))
{
return;
}
var cic = new CohortIdentificationConfiguration(Activator.RepositoryLocator.CatalogueRepository, tbName.Text);
cic.CreateRootContainerIfNotExists();
var root = cic.RootCohortAggregateContainer;
root.Operation = SetOperation.EXCEPT;
root.Name = "EXCEPT";
root.SaveToDatabase();
var includeContainer = setOperationInclude.CreateCohortAggregateContainer(root);
inclusionCriteria1.CreateCohortSet(cic, includeContainer, 1);
if (cbInclusion2.Checked)
{
inclusionCriteria2.CreateCohortSet(cic, includeContainer, 2);
}
if (cbExclusion1.Checked || cbExclusion2.Checked)
{
var excludeContainer = setOperationExclude.CreateCohortAggregateContainer(root);
if (cbExclusion1.Checked)
{
exclusionCriteria1.CreateCohortSet(cic, excludeContainer, 1);
}
if (cbExclusion2.Checked)
{
exclusionCriteria2.CreateCohortSet(cic, excludeContainer, 2);
}
}
Activator.RefreshBus.Publish(this, new RefreshObjectEventArgs(cic));
CohortIdentificationCriteriaCreatedIfAny = cic;
DialogResult = DialogResult.OK;
Close();
}
public void TestImportTree_FromCohortIdentificationConfiguration_ToSelectedDatasets_PreserveOperation()
{
var sds = WhenIHaveA <SelectedDataSets>();
var cata = sds.ExtractableDataSet.Catalogue;
var cic = new CohortIdentificationConfiguration(Repository, "my cic");
cic.CreateRootContainerIfNotExists();
var ac = new AggregateConfiguration(Repository, cata, "myagg");
ac.CreateRootContainerIfNotExists();
cic.RootCohortAggregateContainer.AddChild(ac, 1);
var filterToImport = new AggregateFilter(Repository, "MyFilter")
{
WhereSQL = "true"
};
var root = ac.RootFilterContainer;
root.AddChild(filterToImport);
root.Operation = FilterContainerOperation.OR;
root.SaveToDatabase();
// add 2 subcontainers, these should also get cloned and should preserve the Operation correctly
root.AddChild(new AggregateFilterContainer(Repository, FilterContainerOperation.AND));
root.AddChild(new AggregateFilterContainer(Repository, FilterContainerOperation.OR));
//there should be no root container
Assert.IsNull(sds.RootFilterContainer);
//run the command
var mgr = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier());
mgr.DisallowInput = true;
var cmd = new ExecuteCommandImportFilterContainerTree(mgr, sds, ac);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
sds.ClearAllInjections();
Assert.AreEqual(FilterContainerOperation.OR, sds.RootFilterContainer.Operation);
Assert.IsNotNull(sds.RootFilterContainer);
Assert.AreEqual(1, sds.RootFilterContainer.GetFilters().Length);
var subContainers = sds.RootFilterContainer.GetSubContainers();
Assert.AreEqual(2, subContainers.Length);
Assert.AreEqual(1, subContainers.Count(e => e.Operation == FilterContainerOperation.AND));
Assert.AreEqual(1, subContainers.Count(e => e.Operation == FilterContainerOperation.OR));
}
public void Join_PatientIndexTable_DoNotUseCacheOnDifferentServer(DatabaseType dbType)
{
/*
* Server1 Server 2
* _____________ _________
* |Biochemistry| → | Cache | (cache is still populated but not used in the resulting join).
*
* ↓ join ↓ (do not use cache)
* _____________________
* | Hospital Admissions|
*
*/
//get the data database
var db = GetCleanedServer(dbType);
//create the cache on the other server type (doesn't matter what type just as long as it's different).
var dbCache =
GetCleanedServer(Enum.GetValues(typeof(DatabaseType)).Cast <DatabaseType>().First(t => t != dbType));
ExternalDatabaseServer cache = CreateCache(dbCache);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(db, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(db, people, r, cic, joinable);
cic.RootCohortAggregateContainer.AddChild(hospitalAdmissions, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("1/1")), "Expected cache to be used only for the final UNION");
}
/// <summary>
/// Adds all subqueries and containers that are below the current CohortIdentificationConfiguration as tasks to the compiler
/// </summary>
/// <param name="addSubcontainerTasks">The root container is always added to the task list but you could skip subcontainer totals if all you care about is the final total for the cohort
/// and you don't have a dependant UI etc. Passing false will add all joinables, subqueries etc and the root container (final answer for who is in cohort) only.</param>
/// <returns></returns>
public List <ICompileable> AddAllTasks(bool addSubcontainerTasks = true)
{
var toReturn = new List <ICompileable>();
var globals = CohortIdentificationConfiguration.GetAllParameters();
CohortIdentificationConfiguration.CreateRootContainerIfNotExists();
foreach (var joinable in CohortIdentificationConfiguration.GetAllJoinables())
{
toReturn.Add(AddTask(joinable, globals));
}
toReturn.AddRange(AddTasksRecursively(globals, CohortIdentificationConfiguration.RootCohortAggregateContainer, addSubcontainerTasks));
return(toReturn);
}
public void Join_PatientIndexTable_ThreeServers()
{
/*
* Server1 Server 2 Server 3
* _____________ _________
* |Biochemistry| → (successfully caches joinable bit) | Cache |
*
* ↘ join ↘ (should crash)
* _____________________
* | Hospital Admissions|
*
*/
var server1 = GetCleanedServer(DatabaseType.MySql);
var server2 = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
var server3 = GetCleanedServer(DatabaseType.Oracle);
ExternalDatabaseServer cache = CreateCache(server3);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(server1, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(server2, people, r, cic, joinable);
cic.RootCohortAggregateContainer.AddChild(hospitalAdmissions, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
var hospitalAdmissionsTask = compiler.Tasks.Keys.OfType <AggregationTask>().Single(t => t.Aggregate.Equals(hospitalAdmissions));
Assert.AreEqual(CompilationState.Crashed, hospitalAdmissionsTask.State);
StringAssert.Contains("is not fully cached and CacheUsageDecision is MustUse", hospitalAdmissionsTask.CrashMessage.ToString());
}
public void TestSimpleUnMerge()
{
var merger = new CohortIdentificationConfigurationMerger(CatalogueRepository);
var cicInput = new CohortIdentificationConfiguration(CatalogueRepository, "cic99");
cicInput.CreateRootContainerIfNotExists();
var root = cicInput.RootCohortAggregateContainer;
root.Name = "Root";
root.SaveToDatabase();
var sub1 = new CohortAggregateContainer(CatalogueRepository, SetOperation.INTERSECT);
sub1.Order = 1;
sub1.SaveToDatabase();
var sub2 = new CohortAggregateContainer(CatalogueRepository, SetOperation.EXCEPT);
sub2.Order = 2;
sub2.SaveToDatabase();
root.AddChild(sub1);
root.AddChild(sub2);
sub1.AddChild(aggregate1, 0);
sub2.AddChild(aggregate2, 0);
sub2.AddChild(aggregate3, 1);
int numberOfCicsBefore = CatalogueRepository.GetAllObjects <CohortIdentificationConfiguration>().Count();
var results = merger.UnMerge(root);
// Now should be two new ones
Assert.AreEqual(numberOfCicsBefore + 2, CatalogueRepository.GetAllObjects <CohortIdentificationConfiguration>().Count());
Assert.AreEqual(2, results.Length);
Assert.AreEqual(SetOperation.INTERSECT, results[0].RootCohortAggregateContainer.Operation);
Assert.AreEqual(1, results[0].RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
Assert.IsFalse(results[0].RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Intersect(new [] { aggregate1, aggregate2, aggregate3 }).Any(), "Expected new aggregates to be new!");
Assert.AreEqual(SetOperation.EXCEPT, results[1].RootCohortAggregateContainer.Operation);
Assert.AreEqual(2, results[1].RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
Assert.IsFalse(results[1].RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Intersect(new [] { aggregate1, aggregate2, aggregate3 }).Any(), "Expected new aggregates to be new!");
}
/// <summary>
/// Rebuilds the CohortCompiler diagram which shows all the currently configured tasks
/// </summary>
/// <param name="cancelTasks"></param>
private void RecreateAllTasks(bool cancelTasks = true)
{
if (cancelTasks)
{
Compiler.CancelAllTasks(false);
}
_configuration.CreateRootContainerIfNotExists();
//if there is no root container,create one
_root = _configuration.RootCohortAggregateContainer;
_globals = _configuration.GetAllParameters();
//Could have configured/unconfigured a joinable state
foreach (var j in Compiler.Tasks.Keys.OfType <JoinableTask>())
{
j.RefreshIsUsedState();
}
}
public void TestImportTree_FromSelectedDatasets_ToCohortIdentificationConfiguration()
{
// Import From Selected Dataset
var sds = WhenIHaveA <SelectedDataSets>();
sds.CreateRootContainerIfNotExists();
var filterToImport = new DeployedExtractionFilter(Repository, "MyFilter", (FilterContainer)sds.RootFilterContainer)
{
WhereSQL = "true"
};
filterToImport.SaveToDatabase();
var cata = sds.ExtractableDataSet.Catalogue;
// Into an Aggregate Configuration
var cic = new CohortIdentificationConfiguration(Repository, "my cic");
cic.CreateRootContainerIfNotExists();
var ac = new AggregateConfiguration(Repository, cata, "myagg");
cic.RootCohortAggregateContainer.AddChild(ac, 1);
//there should be no root container
Assert.IsNull(ac.RootFilterContainer);
//run the command
var mgr = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier());
mgr.DisallowInput = true;
var cmd = new ExecuteCommandImportFilterContainerTree(mgr, ac, sds);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
ac.ClearAllInjections();
Assert.IsNotNull(ac.RootFilterContainer);
Assert.AreEqual(1, ac.RootFilterContainer.GetFilters().Length);
Assert.AreEqual("MyFilter", ac.RootFilterContainer.GetFilters()[0].Name);
Assert.AreEqual("true", ac.RootFilterContainer.GetFilters()[0].WhereSQL);
Assert.AreNotEqual(filterToImport.GetType(), ac.RootFilterContainer.GetFilters()[0].GetType());
}
public void Join_PatientIndexTable_NotOnCacheServer()
{
/*
* Server1 Server 2
* _____________ _________
* |Biochemistry| → | Cache | (cache must first be populated)
*
* ↘ join ↘ (must use cache)
* _____________________
* | Hospital Admissions|
*
*/
var server1 = GetCleanedServer(DatabaseType.MySql);
var server2 = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
ExternalDatabaseServer cache = CreateCache(server2);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(server1, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(server2, people, r, cic, joinable);
cic.RootCohortAggregateContainer.AddChild(hospitalAdmissions, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("1/1")), "Expected cache to be used only for the final UNION");
}
public void TestIPluginCohortCompiler_APIsCantHavePatientIndexTables()
{
var activator = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier())
{
DisallowInput = true
};
// create a cohort config
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "mycic");
cic.QueryCachingServer_ID = externalDatabaseServer.ID;
cic.SaveToDatabase();
// this special Catalogue will be detected by ExamplePluginCohortCompiler and interpreted as an API call
var myApi = new Catalogue(CatalogueRepository, ExamplePluginCohortCompiler.ExampleAPIName);
// add it to the cohort config
cic.CreateRootContainerIfNotExists();
// We need something in the root container otherwise the cic won't build
var cmd = new ExecuteCommandAddCatalogueToCohortIdentificationSetContainer(activator, new CatalogueCombineable(myApi), cic.RootCohortAggregateContainer);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
var regularAggregate = cmd.AggregateCreatedIfAny;
// The thing we are wanting to test - creating a use of the API as a patient index table
var cmd2 = new ExecuteCommandAddCatalogueToCohortIdentificationAsPatientIndexTable(
activator, new CatalogueCombineable(myApi), cic);
Assert.IsFalse(cmd2.IsImpossible, cmd2.ReasonCommandImpossible);
cmd2.Execute();
var joinables = cic.GetAllJoinables();
// make them join one another
var ex = Assert.Throws <NotSupportedException>(() =>
new JoinableCohortAggregateConfigurationUse(CatalogueRepository, regularAggregate, joinables[0]));
Assert.AreEqual("API calls cannot join with PatientIndexTables (The API call must be self contained)", ex.Message);
}
private void PopulateCohortDatabaseWithRecordsFromNonTvfCatalogue()
{
//create a cohort identification configuration (identifies people from datasets using set operations - see CohortManager)
_cic = new CohortIdentificationConfiguration(CatalogueRepository, "TbvfCIC");
_cic.CreateRootContainerIfNotExists();
//turn the catalogue _nonTvfCatalogue into a cohort set and add it to the root container
var newAggregate = _cic.CreateNewEmptyConfigurationForCatalogue(_nonTvfCatalogue, (s, e) => { throw new Exception("Did not expect there to be more than 1!"); });
var root = _cic.RootCohortAggregateContainer;
root.AddChild(newAggregate, 0);
//create a pipeline for executing this CIC and turning it into a cohort
_pipe = new Pipeline(CatalogueRepository, "CREATE COHORT:By Executing CIC");
var source = new PipelineComponent(CatalogueRepository, _pipe, typeof(CohortIdentificationConfigurationSource), 0, "CIC Source");
_project = new Project(DataExportRepository, "TvfProject");
_project.ProjectNumber = 12;
_project.ExtractionDirectory = TestContext.CurrentContext.TestDirectory;
_project.SaveToDatabase();
var destination = new PipelineComponent(CatalogueRepository, _pipe, typeof(BasicCohortDestination), 1, "Destination");
_pipe.SourcePipelineComponent_ID = source.ID;
_pipe.DestinationPipelineComponent_ID = destination.ID;
_pipe.SaveToDatabase();
//create pipeline arguments
source.CreateArgumentsForClassIfNotExists <CohortIdentificationConfigurationSource>();
destination.CreateArgumentsForClassIfNotExists <BasicCohortDestination>();
//create pipeline initialization objects
var request = new CohortCreationRequest(_project, new CohortDefinition(null, "MyFirstCohortForTvfTest", 1, 12, _externalCohortTable), (DataExportRepository)DataExportRepository, "Here goes nothing");
request.CohortIdentificationConfiguration = _cic;
var engine = request.GetEngine(_pipe, new ThrowImmediatelyDataLoadEventListener());
engine.ExecutePipeline(new GracefulCancellationToken());
}
private void GetObjects(out Catalogue cata, out CohortIdentificationConfiguration cic)
{
cic = WhenIHaveA <CohortIdentificationConfiguration>();
cic.CreateRootContainerIfNotExists();
//clear anything old
foreach (var old in cic.RootCohortAggregateContainer.GetOrderedContents().Cast <DatabaseEntity>())
{
old.DeleteInDatabase();
}
//we need a patient identifier column
var ei = WhenIHaveA <ExtractionInformation>();
ei.IsExtractionIdentifier = true;
ei.SaveToDatabase();
//in a catalogue
cata = ei.CatalogueItem.Catalogue;
}
//[TestCase(DatabaseType.Oracle,true)] //Oracle FAnsi doesn't currently support parameters
//[TestCase(DatabaseType.Oracle,false)]
public void Test_SingleServer_WithOneParameter(DatabaseType dbType, bool useParameter)
{
/*
* Server1 Server2
* _____________________ _____________________
* |HospitalAdmissions | → | Cache |
* @date_of_max
*
*/
var server1 = GetCleanedServer(dbType);
var server2 = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
var cache = CreateCache(server2);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var ac1 = SetupAggregateConfigurationWithFilter(server1, people, r, cic, useParameter, "@date_of_max", "'2001-01-01'");
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache.ID;
cic.SaveToDatabase();
var root = cic.RootCohortAggregateContainer;
root.AddChild(ac1, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
Assert.IsTrue(compiler.Tasks.Where(t => t.Key is AggregationContainerTask).Any(t => t.Key.GetCachedQueryUseCount().Equals("1/1")), "Expected UNION container to use the cache");
}
public void TestSimpleImportCic()
{
var merger = new CohortIdentificationConfigurationMerger(CatalogueRepository);
var cic1 = new CohortIdentificationConfiguration(CatalogueRepository, "cic1");
var cic2 = new CohortIdentificationConfiguration(CatalogueRepository, "cic2");
cic1.CreateRootContainerIfNotExists();
var root1 = cic1.RootCohortAggregateContainer;
root1.Name = "Root1";
root1.SaveToDatabase();
root1.AddChild(aggregate1, 1);
cic2.CreateRootContainerIfNotExists();
var root2 = cic2.RootCohortAggregateContainer;
root2.Name = "Root2";
root2.SaveToDatabase();
root2.AddChild(aggregate2, 2);
Assert.AreEqual(1, cic1.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
Assert.AreEqual(1, cic2.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
int numberOfCicsBefore = CatalogueRepository.GetAllObjects <CohortIdentificationConfiguration>().Count();
//import 2 into 1
merger.Import(new [] { cic2 }, cic1.RootCohortAggregateContainer);
//no new cics
Assert.AreEqual(numberOfCicsBefore, CatalogueRepository.GetAllObjects <CohortIdentificationConfiguration>().Count());
// cic 1 should now have both aggregates
Assert.AreEqual(2, cic1.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively().Count);
Assert.AreEqual("Root1", cic1.RootCohortAggregateContainer.Name);
Assert.AreEqual("Root2", cic1.RootCohortAggregateContainer.GetSubContainers()[0].Name);
}
private CohortIdentificationConfiguration GenerateBasicCohortIdentificationConfiguration()
{
var name = _name;
if (name == null)
{
if (!TypeText("Cohort Query Name", "Cohort Name", out name))
{
return(null);
}
}
var cic = new CohortIdentificationConfiguration(BasicActivator.RepositoryLocator.CatalogueRepository, name);
cic.CreateRootContainerIfNotExists();
var root = cic.RootCohortAggregateContainer;
root.Name = "Root Container";
root.Operation = SetOperation.EXCEPT;
root.SaveToDatabase();
var inclusion = new CohortAggregateContainer(BasicActivator.RepositoryLocator.CatalogueRepository, SetOperation.UNION);
inclusion.Name = InclusionCriteriaName;
inclusion.Order = 0;
inclusion.SaveToDatabase();
var exclusion = new CohortAggregateContainer(BasicActivator.RepositoryLocator.CatalogueRepository, SetOperation.UNION);
exclusion.Name = ExclusionCriteriaName;
exclusion.Order = 1;
exclusion.SaveToDatabase();
root.AddChild(inclusion);
root.AddChild(exclusion);
return(cic);
}
public void TestIPluginCohortCompiler_TestCloneCic()
{
var activator = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier())
{
DisallowInput = true
};
// create a cohort config
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "mycic");
cic.QueryCachingServer_ID = externalDatabaseServer.ID;
cic.SaveToDatabase();
// this special Catalogue will be detected by ExamplePluginCohortCompiler and interpreted as an API call
var myApi = new Catalogue(CatalogueRepository, ExamplePluginCohortCompiler.ExampleAPIName);
// add it to the cohort config
cic.CreateRootContainerIfNotExists();
// create a use of the API as an AggregateConfiguration
var cmd = new ExecuteCommandAddCatalogueToCohortIdentificationSetContainer(activator, new CatalogueCombineable(myApi), cic.RootCohortAggregateContainer);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
cmd.AggregateCreatedIfAny.Description = "33";
cmd.AggregateCreatedIfAny.SaveToDatabase();
// clone the cic
var cmd2 = new ExecuteCommandCloneCohortIdentificationConfiguration(activator, cic);
Assert.IsFalse(cmd2.IsImpossible, cmd2.ReasonCommandImpossible);
cmd2.Execute();
var cloneAc = cmd2.CloneCreatedIfAny.RootCohortAggregateContainer.GetAggregateConfigurations()[0];
Assert.AreEqual("33", cloneAc.Description);
}
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!
}
public void TestIPluginCohortCompiler_AsPatientIndexTable()
{
var activator = new ConsoleInputManager(RepositoryLocator, new ThrowImmediatelyCheckNotifier())
{
DisallowInput = true
};
// Create a regular normal boring old table that will join into the results of the API call
var db = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
using DataTable dt = new DataTable();
dt.Columns.Add("chi");
dt.Rows.Add("0101010101");
var tbl = db.CreateTable("RegularBoringOldTable", dt);
var cata = (Catalogue)Import(tbl);
var eiChi = cata.GetAllExtractionInformation()[0];
eiChi.IsExtractionIdentifier = true;
eiChi.SaveToDatabase();
// create a cohort config
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "mycic");
cic.QueryCachingServer_ID = externalDatabaseServer.ID;
cic.SaveToDatabase();
// this special Catalogue will be detected by ExamplePluginCohortCompiler and interpreted as an API call
var myApi = new Catalogue(CatalogueRepository, ExamplePluginCohortCompiler.ExampleAPIName);
// add it to the cohort config
cic.CreateRootContainerIfNotExists();
// Add the regular table
var cmd = new ExecuteCommandAddCatalogueToCohortIdentificationSetContainer(activator, new CatalogueCombineable(cata), cic.RootCohortAggregateContainer);
Assert.IsFalse(cmd.IsImpossible, cmd.ReasonCommandImpossible);
cmd.Execute();
var regularAggregate = cmd.AggregateCreatedIfAny;
// The thing we are wanting to test - creating a use of the API as a patient index table
var cmd2 = new ExecuteCommandAddCatalogueToCohortIdentificationAsPatientIndexTable(
activator, new CatalogueCombineable(myApi), cic);
Assert.IsFalse(cmd2.IsImpossible, cmd2.ReasonCommandImpossible);
cmd2.Execute();
var joinables = cic.GetAllJoinables();
Assert.AreEqual(1, joinables.Length);
// make them join one another
new JoinableCohortAggregateConfigurationUse(CatalogueRepository, regularAggregate, joinables[0]);
// run the cic again
var source = new CohortIdentificationConfigurationSource();
source.PreInitialize(cic, new ThrowImmediatelyDataLoadEventListener());
var result = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
Assert.AreEqual(1, result.Rows.Count);
}
public void RefreshCohort_WithCaching()
{
ExtractionPipelineUseCase useCase;
IExecuteDatasetExtractionDestination results;
var pipe = new Pipeline(CatalogueRepository, "RefreshPipeWithCaching");
var source = new PipelineComponent(CatalogueRepository, pipe, typeof(CohortIdentificationConfigurationSource), 0);
var args = source.CreateArgumentsForClassIfNotExists <CohortIdentificationConfigurationSource>();
var freezeArg = args.Single(a => a.Name.Equals("FreezeAfterSuccessfulImport"));
freezeArg.SetValue(false);
freezeArg.SaveToDatabase();
var dest = new PipelineComponent(CatalogueRepository, pipe, typeof(BasicCohortDestination), 0);
var argsDest = dest.CreateArgumentsForClassIfNotExists <BasicCohortDestination>();
var allocatorArg = argsDest.Single(a => a.Name.Equals("ReleaseIdentifierAllocator"));
allocatorArg.SetValue(null);
allocatorArg.SaveToDatabase();
pipe.SourcePipelineComponent_ID = source.ID;
pipe.DestinationPipelineComponent_ID = dest.ID;
pipe.SaveToDatabase();
Execute(out useCase, out results);
var oldcohort = _configuration.Cohort;
//Create a query cache
var p = new QueryCachingPatcher();
ExternalDatabaseServer queryCacheServer = new ExternalDatabaseServer(CatalogueRepository, "TestCohortRefreshing_CacheTest", p);
DiscoveredDatabase cachedb = DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase("TestCohortRefreshing_CacheTest");
if (cachedb.Exists())
{
cachedb.Drop();
}
new MasterDatabaseScriptExecutor(cachedb).CreateAndPatchDatabase(p, new ThrowImmediatelyCheckNotifier());
queryCacheServer.SetProperties(cachedb);
//Create a Cohort Identification configuration (query) that will identify the cohort
CohortIdentificationConfiguration cic = new CohortIdentificationConfiguration(RepositoryLocator.CatalogueRepository, "RefreshCohort.cs");;
try
{
//make it use the cache
cic.QueryCachingServer_ID = queryCacheServer.ID;
cic.SaveToDatabase();
//give it a single table query to fetch distinct chi from test data
var agg = cic.CreateNewEmptyConfigurationForCatalogue(_catalogue, null);
//add the sub query as the only entry in the cic (in the root container)
cic.CreateRootContainerIfNotExists();
cic.RootCohortAggregateContainer.AddChild(agg, 1);
//make the ExtractionConfiguration refresh cohort query be the cic
_configuration.CohortIdentificationConfiguration_ID = cic.ID;
_configuration.CohortRefreshPipeline_ID = pipe.ID;
_configuration.SaveToDatabase();
//get a refreshing engine
var engine = new CohortRefreshEngine(new ThrowImmediatelyDataLoadEventListener(), _configuration);
engine.Execute();
Assert.NotNull(engine.Request.NewCohortDefinition);
var oldData = oldcohort.GetExternalData();
Assert.AreEqual(oldData.ExternalDescription, engine.Request.NewCohortDefinition.Description);
Assert.AreEqual(oldData.ExternalVersion + 1, engine.Request.NewCohortDefinition.Version);
Assert.AreNotEqual(oldcohort.CountDistinct, engine.Request.CohortCreatedIfAny.CountDistinct);
//now nuke all data in the catalogue so the cic returns nobody (except that the identifiers are cached eh?)
DataAccessPortal.GetInstance().ExpectDatabase(_tableInfo, DataAccessContext.InternalDataProcessing).ExpectTable(_tableInfo.GetRuntimeName()).Truncate();
var toMem = new ToMemoryDataLoadEventListener(false);
//get a new engine
engine = new CohortRefreshEngine(toMem, _configuration);
//execute it
var ex = Assert.Throws <Exception>(() => engine.Execute());
Assert.IsTrue(ex.InnerException.InnerException.Message.Contains("CohortIdentificationCriteria execution resulted in an empty dataset"));
//expected this message to happen
//that it did clear the cache
Assert.AreEqual(1, toMem.EventsReceivedBySender.SelectMany(kvp => kvp.Value).Count(msg => msg.Message.Equals("Clearing Cohort Identifier Cache")));
}
finally
{
//make the ExtractionConfiguration not use the cic query
_configuration.CohortRefreshPipeline_ID = null;
_configuration.CohortIdentificationConfiguration_ID = null;
_configuration.SaveToDatabase();
//delete the cic query
cic.QueryCachingServer_ID = null;
cic.SaveToDatabase();
cic.DeleteInDatabase();
//delete the caching database
queryCacheServer.DeleteInDatabase();
cachedb.Drop();
}
}
//[TestCase(DatabaseType.Oracle)] //Oracle FAnsi doesn't currently support parameters
public void Test_SingleServer_WithTwoParameters(DatabaseType dbType, bool useCache)
{
/*
* Server1 Server2
* ____________________ _____________________
* |HospitalAdmissions | → | Cache |
* @date_of_max
* ____________________ ↗
* |HospitalAdmissions |
* @date_of_max
*
* (has different value so rename operations come into effect)
*/
var server1 = GetCleanedServer(dbType);
var server2 = GetCleanedServer(DatabaseType.MicrosoftSQLServer);
var cache = useCache ? CreateCache(server2): null;
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var ac1 = SetupAggregateConfigurationWithFilter(server1, people, r, cic, true, "@date_of_max", "'2001-01-01'");
var ac2 = SetupAggregateConfigurationWithFilter(server1, people, r, cic, true, "@date_of_max", "'2005-01-01'");
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var root = cic.RootCohortAggregateContainer;
root.AddChild(ac1, 0);
root.AddChild(ac2, 1);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
//each container on it's own ran with the normal SQL
AssertNoErrors(compiler, ac1, "@date_of_max='2001-01-01'");
AssertNoErrors(compiler, ac2, "@date_of_max='2005-01-01'");
if (useCache)
{
//the root container run with dual cache fetch and no parameters
AssertNoErrors(compiler, root, "IndexedExtractionIdentifierList");
AssertCacheUsed(compiler, root, "2/2");
}
else
{
//the root container ran with a rename operations (no cache available)
AssertNoErrors(compiler, root, "@date_of_max='2001-01-01'", "@date_of_max_2='2005-01-01'");
AssertCacheUsed(compiler, root, "0/2");
}
}
public void Test_SingleServerPatientIndexTable_WithTwoParameters(DatabaseType dbType, bool useSameName, bool useCache)
{
/*
* Server1 (Also Server1 - if useCache is true)
* ______________________________ __________________________
* | Patient Index Table | → | Cache |
* | (NA by date ) | ↗
* @date_of_max '2001-01-01'
* ↗
* JOIN (should use cache)
* ___________________________ ↗
* | Hospitalised after NA (ac) |
* @date_of_max (or @maximum) '2005-01-01'
*
* (has different value so rename operations come into effect)
*/
var server1 = GetCleanedServer(dbType);
var cache = useCache? CreateCache(server1): null;
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var patientIndexTable = SetupPatientIndexTableWithFilter(server1, people, r, cic, true, "@date_of_max", "'2001-01-01'");
var ac = SetupPatientIndexTableUserWithFilter(server1, people, r, cic, patientIndexTable, true,
useSameName ? "@date_of_max": "@maximum", "'2005-01-01'");
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var root = cic.RootCohortAggregateContainer;
root.AddChild(ac, 0);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
if (useSameName)
{
AssertCrashed(compiler, ac, "PatientIndexTables cannot have parameters with the same name as their users. Offending parameter(s) were @date_of_max");
}
else
{
if (useCache)
{
//we should hit up the cache for the interior of the query and therefore not need the parameter
AssertNoErrors(compiler, ac, "@maximum='2005-01-01'", "JoinableInceptionQuery_AggregateConfiguration", "AdmissionDate. < @maximum");
AssertCacheUsed(compiler, root, "1/1");
}
else
{
AssertNoErrors(compiler, ac, "@date_of_max='2001-01-01'", "@maximum='2005-01-01'", "SampleDate. < @date_of_max", "AdmissionDate. < @maximum");
}
AssertNoErrors(compiler);
}
}
public override void Execute()
{
base.Execute();
CohortIdentificationConfiguration cic;
if (UserSettings.ShowCohortWizard)
{
var wizard = new CreateNewCohortIdentificationConfigurationUI(Activator);
if (wizard.ShowDialog() == DialogResult.OK)
{
cic = wizard.CohortIdentificationCriteriaCreatedIfAny;
}
else
{
return;
}
}
else
{
if (TypeText("Cohort Query Name", "Cohort Name", out string name))
{
cic = new CohortIdentificationConfiguration(Activator.RepositoryLocator.CatalogueRepository, name);
cic.CreateRootContainerIfNotExists();
var exclusion = cic.RootCohortAggregateContainer;
exclusion.Name = "Exclusion Criteria";
exclusion.Operation = SetOperation.EXCEPT;
exclusion.SaveToDatabase();
var inclusion = new CohortAggregateContainer(Activator.RepositoryLocator.CatalogueRepository, SetOperation.UNION);
inclusion.Name = "Inclusion Criteria";
inclusion.SaveToDatabase();
exclusion.AddChild(inclusion);
}
else
{
return;
}
}
if (cic == null)
{
return;
}
if (_associateWithProject != null)
{
var assoc = _associateWithProject.AssociateWithCohortIdentification(cic);
Publish(assoc);
Emphasise(assoc, int.MaxValue);
}
else
{
Publish(cic);
Emphasise(cic, int.MaxValue);
}
Activate(cic);
}
public void Join_PatientIndexTable_ThenShipToCacheForSets(DatabaseType dbType)
{
/*
* Server1 Server 2
* _____________ _________
* |Biochemistry| → | Cache | (cache is still populated but not used in the resulting join).
*
* ↓ join ↓ (do not use cache)
* _______________________
* | Hospital Admissions 1| → results1
*
* EXCEPT
* _______________________
* | Hospital Admissions 2| → results 2
* ↓ result = 0 records
*/
//get the data database
var db = GetCleanedServer(dbType);
//create the cache on the other server type (either Sql Server or Oracle) since MySql can't do EXCEPT or UNION etc)
var dbCache =
GetCleanedServer(Enum.GetValues(typeof(DatabaseType)).Cast <DatabaseType>().First(t =>
t != dbType && t != DatabaseType.MySql));
ExternalDatabaseServer cache = CreateCache(dbCache);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var joinable = SetupPatientIndexTable(db, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache?.ID;
cic.SaveToDatabase();
var hospitalAdmissions = SetupPatientIndexTableUser(db, people, r, cic, joinable);
var hospitalAdmissions2 = SetupAggregateConfiguration(db, people, r, cic);
var root = cic.RootCohortAggregateContainer;
root.AddChild(hospitalAdmissions, 0);
root.AddChild(hospitalAdmissions2, 1);
root.Name = "EXCEPT";
root.Operation = SetOperation.EXCEPT;
root.SaveToDatabase();
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
AssertNoErrors(compiler);
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("2/2")), "Expected cache to be used for both top level operations in the EXCEPT");
}
public void Test_EXCEPT_TwoAggregates(DatabaseType dbType)
{
/*
* Server1
* _____________________
* |HospitalAdmissions x2|
* ↓ both run into ↓
* ___________________
* | Cache |
*
*
* HospitalAdmissions
* EXCEPT
* HospitalAdmissions (copy)
* = 0
*/
var db = GetCleanedServer(dbType);
var cache = CreateCache(db);
var r = new Random(500);
var people = new PersonCollection();
people.GeneratePeople(5000, r);
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "cic");
var ac1 = SetupAggregateConfiguration(db, people, r, cic);
var ac2 = SetupAggregateConfiguration(db, people, r, cic);
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = cache.ID;
cic.SaveToDatabase();
var root = cic.RootCohortAggregateContainer;
root.Operation = SetOperation.EXCEPT;
root.Name = "EXCEPT";
root.SaveToDatabase();
root.AddChild(ac1, 0);
root.AddChild(ac2, 1);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50000);
runner.Run(new CancellationToken());
if (dbType == DatabaseType.MySql)
{
var crashed = compiler.Tasks.Single(t => t.Key.State == CompilationState.Crashed);
StringAssert.Contains("INTERSECT / UNION / EXCEPT are not supported by MySql", crashed.Key.CrashMessage.Message);
return;
}
AssertNoErrors(compiler);
Assert.AreEqual(compiler.Tasks.Single(t => t.Value != null && t.Value.IsResultsForRootContainer).Key.FinalRowCount, 0);
Assert.Greater(compiler.Tasks.Single(t => t.Key is AggregationTask at && at.Aggregate.Equals(ac1)).Key.FinalRowCount, 0); //both ac should have the same total
Assert.Greater(compiler.Tasks.Single(t => t.Key is AggregationTask at && at.Aggregate.Equals(ac2)).Key.FinalRowCount, 0); // that is not 0
Assert.IsTrue(compiler.Tasks.Any(t => t.Key.GetCachedQueryUseCount().Equals("2/2")), "Expected EXCEPT container to use the cache");
}