public CohortCompilerUI()
{
InitializeComponent();
if (VisualStudioDesignMode)
{
return;
}
Compiler = new CohortCompiler(null);
tlvConfiguration.CanExpandGetter += CanExpandGetter;
tlvConfiguration.ChildrenGetter += ChildrenGetter;
olvAggregate.ImageGetter += ImageGetter;
olvAggregate.AspectGetter += ToString_AspectGetter;
tlvConfiguration.RowFormatter += RowFormatter;
olvIdentifierCount.AspectGetter += RowCountAspectGetter;
olvCachedQueryUseCount.AspectGetter = CachedQueryUseCount_AspectGetter;
refreshThreadCountPeriodically.Start();
tlvConfiguration.RowHeight = 19;
_cohortUnionImage = CatalogueIcons.UNIONCohortAggregate;
_cohortIntersectImage = CatalogueIcons.INTERSECTCohortAggregate;
_cohortExceptImage = CatalogueIcons.EXCEPTCohortAggregate;
AssociatedCollection = RDMPCollection.Cohort;
}
public void AddContainer_StaysAtOne()
{
CohortCompiler compiler = new CohortCompiler(cohortIdentificationConfiguration);
rootcontainer.AddChild(aggregate1, 1);
compiler.AddTask(rootcontainer, null);//add the root container
Assert.AreEqual(1, compiler.Tasks.Count);
var oldTask = compiler.Tasks.First();
//adding it again with the same SQL should result in it ignoring it
compiler.AddTask(rootcontainer, null);
Assert.AreEqual(1, compiler.Tasks.Count);
//add another aggregate into the container
rootcontainer.AddChild(aggregate2, 1);
compiler.AddTask(rootcontainer, null);
Assert.AreEqual(1, compiler.Tasks.Count);//should still be 1 task
//old task should have been asked to cancel
Assert.IsTrue(oldTask.Key.CancellationToken.IsCancellationRequested);
Assert.AreNotSame(oldTask, compiler.Tasks.Single()); //new task should not be the same as the old one
Assert.IsFalse(compiler.Tasks.Single().Key.CancellationToken.IsCancellationRequested); //new task should not be cancelled
rootcontainer.RemoveChild(aggregate1);
rootcontainer.RemoveChild(aggregate2);
}
/// <summary>
/// Asserts that the given <paramref name="task"/> (when run on it's own) crashed with the given
/// <see cref="expectedErrorMessageToContain"/>
/// </summary>
private void AssertCrashed(CohortCompiler compiler, AggregateConfiguration task, string expectedErrorMessageToContain)
{
var acResult = compiler.Tasks.Single(t => t.Key is AggregationTask a && a.Aggregate.Equals(task));
Assert.AreEqual(CompilationState.Crashed, acResult.Key.State);
StringAssert.Contains(expectedErrorMessageToContain, acResult.Key.CrashMessage.Message);
}
/// <summary>
/// Asserts that the given <paramref name="container"/> ran successfully and fetched it's results from the cache (if any) with the
/// given <paramref name="expectedCacheUsageCount"/>
/// </summary>
/// <param name="compiler"></param>
/// <param name="container"></param>
/// <param name="expectedCacheUsageCount">The amount you expect to be used of the cache e.g. "2/2" </param>
private void AssertCacheUsed(CohortCompiler compiler, CohortAggregateContainer container, string expectedCacheUsageCount)
{
//cache should have been used
var containerResult = compiler.Tasks.Single(t => t.Key is AggregationContainerTask c && c.Container.Equals(container));
Assert.AreEqual(CompilationState.Finished, containerResult.Key.State);
Assert.AreEqual(expectedCacheUsageCount, containerResult.Key.GetCachedQueryUseCount());
}
public JoinableTask(JoinableCohortAggregateConfiguration joinable, CohortCompiler compiler) : base(compiler)
{
Joinable = joinable;
_aggregate = Joinable.AggregateConfiguration;
_cohortIdentificationConfiguration = _aggregate.GetCohortIdentificationConfigurationIfAny();
_catalogueName = Joinable.AggregateConfiguration.Catalogue.Name;
RefreshIsUsedState();
}
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");
}
}
/// <summary>
/// Asserts that the given <paramref name="task"/> (when run on it's own) completed successfully and that the SQL executed
/// included all the regex patterns <see cref="expectedSqlBits"/>
/// </summary>
/// <param name="compiler"></param>
/// <param name="task"></param>
/// <param name="expectedSqlBits">regex patterns you expect to be in the sql executed</param>
private void AssertNoErrors(CohortCompiler compiler, CohortAggregateContainer task,
params string[] expectedSqlBits)
{
var acResult = compiler.Tasks.Single(t => t.Key is AggregationContainerTask a && a.Container.Equals(task));
Assert.AreEqual(CompilationState.Finished, acResult.Key.State);
foreach (var s in expectedSqlBits)
{
StringAssert.Contains(s, acResult.Value.CountSQL);
}
}
public CohortIdentificationConfigurationUI()
{
InitializeComponent();
Compiler = new CohortCompiler(null);
olvExecute.IsButton = true;
olvExecute.ButtonSizing = OLVColumn.ButtonSizingMode.CellBounds;
tlvCic.RowHeight = 19;
olvExecute.AspectGetter += ExecuteAspectGetter;
tlvCic.ButtonClick += tlvCic_ButtonClick;
olvOrder.AspectGetter += (o) => o is JoinableCollectionNode ? null : o is ISqlParameter ? null : (o as IOrderable)?.Order;
olvOrder.IsEditable = false;
tlvCic.ItemActivate += TlvCic_ItemActivate;
AssociatedCollection = RDMPCollection.Cohort;
timer.Tick += refreshColumnValues;
timer.Interval = 2000;
timer.Start();
olvCount.AspectGetter = Count_AspectGetter;
olvCached.AspectGetter = Cached_AspectGetter;
olvCumulativeTotal.AspectGetter = CumulativeTotal_AspectGetter;
olvTime.AspectGetter = Time_AspectGetter;
olvWorking.AspectGetter = Working_AspectGetter;
olvCatalogue.AspectGetter = Catalogue_AspectGetter;
cbIncludeCumulative.CheckedChanged += (s, e) =>
{
Compiler.IncludeCumulativeTotals = cbIncludeCumulative.Checked;
RecreateAllTasks();
};
//This is important, OrderableComparer ensures IOrderable objects appear in the correct order but the comparator
//doesn't get called unless the column has a sorting on it
olvNameCol.Sortable = true;
tlvCic.Sort(olvNameCol);
RDMPCollectionCommonFunctionality.SetupColumnTracking(tlvCic, olvCached, new Guid("59c6eda9-dcf3-4a24-801f-4c5467c76f94"));
RDMPCollectionCommonFunctionality.SetupColumnTracking(tlvCic, olvCatalogue, new Guid("59c6f9a6-4a93-4167-a268-9ea755d0ad94"));
RDMPCollectionCommonFunctionality.SetupColumnTracking(tlvCic, olvCount, new Guid("4ca6588f-2511-4082-addd-ec42e9d75b39"));
RDMPCollectionCommonFunctionality.SetupColumnTracking(tlvCic, olvCumulativeTotal, new Guid("a3e901e2-c6b8-4365-bea8-5666b9b74821"));
RDMPCollectionCommonFunctionality.SetupColumnTracking(tlvCic, olvExecute, new Guid("f8ad1751-b273-42d7-a6d1-0c580099ceee"));
RDMPCollectionCommonFunctionality.SetupColumnTracking(tlvCic, olvNameCol, new Guid("63db1af5-061c-42b9-873c-7d3d3ac21cd8"));
RDMPCollectionCommonFunctionality.SetupColumnTracking(tlvCic, olvOrder, new Guid("5be4e6e7-bad6-4bd5-821c-a235bc056053"));
RDMPCollectionCommonFunctionality.SetupColumnTracking(tlvCic, olvTime, new Guid("88f88d4a-6204-4f83-b9a7-5421186808b7"));
RDMPCollectionCommonFunctionality.SetupColumnTracking(tlvCic, olvWorking, new Guid("cfe55a4f-9e17-4205-9016-ae506667f22d"));
tt.SetToolTip(btnExecute, "Starts running and caches all cohort sets and containers");
tt.SetToolTip(btnAbortLoad, "Cancells execution of any running cohort sets");
}
private DataTable GetDataTable(IDataLoadEventListener listener)
{
if (listener != null)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to lookup which server to interrogate for CohortIdentificationConfiguration " + _cohortIdentificationConfiguration));
}
if (_cohortIdentificationConfiguration.RootCohortAggregateContainer_ID == null)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "CohortIdentificationConfiguration '" + _cohortIdentificationConfiguration + "' has no RootCohortAggregateContainer_ID, is it empty?"));
}
var cohortCompiler = new CohortCompiler(_cohortIdentificationConfiguration);
ICompileable rootContainerTask;
//no caching set up so no point in running CohortCompilerRunner
if (_cohortIdentificationConfiguration.QueryCachingServer_ID == null)
{
rootContainerTask = RunRootContainerOnlyNoCaching(cohortCompiler);
}
else
{
rootContainerTask = RunAllTasksWithRunner(cohortCompiler, listener);
}
if (rootContainerTask.State != CompilationState.Finished)
{
throw new Exception("CohortIdentificationCriteria execution resulted in state '" + rootContainerTask.State + "'", rootContainerTask.CrashMessage);
}
if (rootContainerTask == null)
{
throw new Exception("Root container task was null, was the execution cancelled? / crashed");
}
var execution = cohortCompiler.Tasks[rootContainerTask];
if (execution.Identifiers == null || execution.Identifiers.Rows.Count == 0)
{
throw new Exception("CohortIdentificationCriteria execution resulted in an empty dataset (there were no cohorts matched by the query?)");
}
var dt = execution.Identifiers;
foreach (DataColumn column in dt.Columns)
{
column.ReadOnly = false;
}
return(dt);
}
/// <summary>
/// Asserts that the given <paramref name="task"/> (when run on it's own) completed successfully and that the SQL executed
/// included all the regex patterns <see cref="expectedSqlBits"/>
/// </summary>
/// <param name="compiler"></param>
/// <param name="task"></param>
/// <param name="expectedSqlBits">regex patterns you expect to be in the sql executed</param>
private void AssertNoErrors(CohortCompiler compiler, AggregateConfiguration task,
params string[] expectedSqlBits)
{
var acResult = compiler.Tasks.Single(t => t.Key is AggregationTask a && a.Aggregate.Equals(task));
Console.WriteLine($"Build Log For '{task}':");
Console.WriteLine(acResult.Key.Log);
Assert.AreEqual(CompilationState.Finished, acResult.Key.State);
foreach (var s in expectedSqlBits)
{
StringAssert.IsMatch(s, acResult.Value.CountSQL);
}
}
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>
/// Shows the state of the <paramref name="compiler"/> and asserts that all the jobs are finished
/// </summary>
/// <param name="compiler"></param>
private void AssertNoErrors(CohortCompiler compiler)
{
Assert.IsNotEmpty(compiler.Tasks);
TestContext.WriteLine($"| Task | Type | State | Error | RowCount | CacheUse |");
int i = 0;
foreach (var kvp in compiler.Tasks)
{
TestContext.WriteLine($"{i++} - {kvp.Key.ToString()} | {kvp.Key.GetType()} | {kvp.Key.State} | {kvp.Key?.CrashMessage} | {kvp.Key.FinalRowCount} | {kvp.Key.GetCachedQueryUseCount()}");
}
Assert.IsTrue(compiler.Tasks.All(t => t.Key.State == CompilationState.Finished), "Expected all tasks to finish without error");
}
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 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 AddSameTaskTwice_StaysAtOne()
{
CohortCompiler compiler = new CohortCompiler(cohortIdentificationConfiguration);
container1.AddChild(aggregate1, 0);
try
{
compiler.AddTask(aggregate1, null);
Assert.AreEqual(1, compiler.Tasks.Count);
var oldTask = compiler.Tasks.First();
//adding it again with the same SQL should result in it ignoring it
compiler.AddTask(aggregate1, null);
Assert.AreEqual(1, compiler.Tasks.Count);
//make a change to the SQL
foreach (var d in aggregate1.AggregateDimensions)
{
d.SelectSQL = "'fish'";
d.SaveToDatabase();
}
//now add it again
var newAggregate1 = CatalogueRepository.GetObjectByID <AggregateConfiguration>(aggregate1.ID);
compiler.AddTask(newAggregate1, null);
Assert.AreEqual(1, compiler.Tasks.Count); //should still be 1 task
// TN: Task was never asked to start so was still at NotScheduled so cancellation wouldn't actually happen
//old task should have been asked to cancel
// Assert.IsTrue(oldTask.Key.CancellationToken.IsCancellationRequested);
Assert.AreNotSame(oldTask, compiler.Tasks.Single()); //new task should not be the same as the old one
Assert.IsFalse(compiler.Tasks.Single().Key.CancellationToken.IsCancellationRequested);
//new task should not be cancelled} finally {
}
finally
{
container1.RemoveChild(aggregate1);
}
}
public CohortIdentificationConfigurationUI()
{
InitializeComponent();
Compiler = new CohortCompiler(null);
olvExecute.IsButton = true;
olvExecute.ButtonSizing = OLVColumn.ButtonSizingMode.CellBounds;
tlvCic.RowHeight = 19;
olvExecute.AspectGetter += ExecuteAspectGetter;
tlvCic.ButtonClick += tlvCic_ButtonClick;
olvOrder.AspectGetter += (o) => o is JoinableCollectionNode ? null : o is ParametersNode ? null : (o as IOrderable)?.Order;
olvOrder.IsEditable = false;
tlvCic.ItemActivate += TlvCic_ItemActivate;
AssociatedCollection = RDMPCollection.Cohort;
timer.Tick += refreshColumnValues;
timer.Interval = 2000;
timer.Start();
olvCount.AspectGetter = Count_AspectGetter;
olvCached.AspectGetter = Cached_AspectGetter;
olvCumulativeTotal.AspectGetter = CumulativeTotal_AspectGetter;
olvTime.AspectGetter = Time_AspectGetter;
olvWorking.AspectGetter = Working_AspectGetter;
olvCatalogue.AspectGetter = Catalogue_AspectGetter;
_miClearCache.Click += MiClearCacheClick;
_miClearCache.Image = CatalogueIcons.ExternalDatabaseServer_Cache;
cbIncludeCumulative.CheckedChanged += (s, e) =>
{
Compiler.IncludeCumulativeTotals = cbIncludeCumulative.Checked;
RecreateAllTasks();
};
//This is important, OrderableComparer ensures IOrderable objects appear in the correct order but the comparator
//doesn't get called unless the column has a sorting on it
olvNameCol.Sortable = true;
tlvCic.Sort(olvNameCol);
}
private ICompileable RunAllTasksWithRunner(CohortCompiler cohortCompiler, IDataLoadEventListener listener)
{
if (ClearCohortIdentificationConfigurationCacheBeforeRunning)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Clearing Cohort Identifier Cache"));
var cacheManager = new CachedAggregateConfigurationResultsManager(_cohortIdentificationConfiguration.QueryCachingServer);
cohortCompiler.AddAllTasks(false);
foreach (var cacheable in cohortCompiler.Tasks.Keys.OfType <ICacheableTask>())
{
cacheable.ClearYourselfFromCache(cacheManager);
}
}
var runner = new CohortCompilerRunner(cohortCompiler, Timeout);
runner.RunSubcontainers = false;
runner.PhaseChanged += (s, e) => listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "CohortCompilerRunner entered Phase '" + runner.ExecutionPhase + "'"));
return(runner.Run(_cancelGlobalOperations.Token));
}
//[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 CacheIdentifierListWithRunner_WithCaching()
{
DiscoveredDatabase db;
CohortIdentificationConfiguration cic;
DataTable dt;
SetupCohort(out db, out cic, out dt);
MasterDatabaseScriptExecutor e = new MasterDatabaseScriptExecutor(db);
var p = new QueryCachingPatcher();
e.CreateAndPatchDatabase(p, new AcceptAllCheckNotifier());
var serverReference = new ExternalDatabaseServer(CatalogueRepository, "Cache", p);
serverReference.SetProperties(db);
cic.QueryCachingServer_ID = serverReference.ID;
cic.SaveToDatabase();
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 5000);
runner.Run(new CancellationToken());
Assert.AreEqual(CohortCompilerRunner.Phase.Finished, runner.ExecutionPhase);
var rootTask = runner.Compiler.Tasks.Single(t => t.Key is AggregationContainerTask);
Assert.IsTrue(rootTask.Value.IsResultsForRootContainer);
Assert.IsNull(rootTask.Key.CrashMessage);
Assert.AreEqual(CompilationState.Finished, rootTask.Key.State);
Assert.IsTrue(runner.Compiler.AreaAllQueriesCached(rootTask.Key));
Assert.AreEqual(dt.Rows.Count, rootTask.Value.Identifiers.Rows.Count);
}
private ICompileable RunRootContainerOnlyNoCaching(CohortCompiler cohortCompiler)
{
//add root container task
var task = cohortCompiler.AddTask(_cohortIdentificationConfiguration.RootCohortAggregateContainer, _cohortIdentificationConfiguration.GetAllParameters());
cohortCompiler.LaunchSingleTask(task, Timeout, false);
//timeout is in seconds
int countDown = Math.Max(5000, Timeout * 1000);
while (
//hasn't timed out
countDown > 0 &&
(
//state isn't a final state
task.State == CompilationState.Executing || task.State == CompilationState.NotScheduled || task.State == CompilationState.Scheduled)
)
{
Task.Delay(100).Wait();
countDown -= 100;
}
if (countDown <= 0)
{
try
{
throw new Exception("Cohort failed to reach a final state (Finished/Crashed) after " + Timeout + " seconds. Current state is " + task.State + ". The task will be cancelled");
}
finally
{
cohortCompiler.CancelAllTasks(true);
}
}
return(task);
}
public void CacheIdentifierListWithRunner_SimpleCase()
{
DiscoveredDatabase db;
CohortIdentificationConfiguration cic;
DataTable dt;
SetupCohort(out db, out cic, out dt);
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 5000);
runner.Run(new CancellationToken());
Assert.AreEqual(CohortCompilerRunner.Phase.Finished, runner.ExecutionPhase);
var rootTask = runner.Compiler.Tasks.Single(t => t.Key is AggregationContainerTask);
Assert.IsTrue(rootTask.Value.IsResultsForRootContainer);
Assert.IsNull(rootTask.Key.CrashMessage);
Assert.AreEqual(CompilationState.Finished, rootTask.Key.State);
Assert.AreEqual(dt.Rows.Count, rootTask.Value.Identifiers.Rows.Count);
}
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 void TestCompilerAddAllTasks(TestCompilerAddAllTasksTestCase testCase, bool includeSubcontainers)
{
var aggregate4 =
new AggregateConfiguration(CatalogueRepository, testData.catalogue, "UnitTestAggregate4");
aggregate4.CountSQL = null;
aggregate4.SaveToDatabase();
new AggregateDimension(CatalogueRepository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate4);
var aggregate5 =
new AggregateConfiguration(CatalogueRepository, testData.catalogue, "UnitTestAggregate5");
aggregate5.CountSQL = null;
aggregate5.SaveToDatabase();
new AggregateDimension(CatalogueRepository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate5);
var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate5);
try
{
//EXCEPT
//Aggregate 1
//UNION
//Aggregate 3
//Aggregate 4
//Aggregate 2
//Joinable:aggregate5 (patient index table, the other Aggregates could JOIN to this)
CohortCompiler compiler = new CohortCompiler(cohortIdentificationConfiguration);
rootcontainer.AddChild(aggregate1, 1);
rootcontainer.AddChild(container1);
container1.Order = 2;
container1.SaveToDatabase();
rootcontainer.AddChild(aggregate2, 3);
container1.AddChild(aggregate3, 1);
container1.AddChild(aggregate4, 2);
cohortIdentificationConfiguration.RootCohortAggregateContainer_ID = rootcontainer.ID;
cohortIdentificationConfiguration.SaveToDatabase();
//The bit we are testing
List <ICompileable> tasks;
switch (testCase)
{
case TestCompilerAddAllTasksTestCase.CIC:
tasks = compiler.AddAllTasks(includeSubcontainers);
Assert.AreEqual(joinable, tasks.OfType <JoinableTask>().Single().Joinable); //should be a single joinable
Assert.AreEqual(includeSubcontainers?7:6, tasks.Count); //all joinables, aggregates and root container
break;
case TestCompilerAddAllTasksTestCase.RootContainer:
tasks = compiler.AddTasksRecursively(new ISqlParameter[0], cohortIdentificationConfiguration.RootCohortAggregateContainer, includeSubcontainers);
Assert.AreEqual(includeSubcontainers?6:5, tasks.Count); //all aggregates and root container (but not joinables)
break;
case TestCompilerAddAllTasksTestCase.Subcontainer:
tasks = compiler.AddTasksRecursively(new ISqlParameter[0], container1, includeSubcontainers);
Assert.AreEqual(includeSubcontainers?3:2, tasks.Count); //subcontainer and it's aggregates
break;
default:
throw new ArgumentOutOfRangeException("testCase");
}
rootcontainer.RemoveChild(aggregate1);
rootcontainer.RemoveChild(aggregate2);
container1.RemoveChild(aggregate3);
container1.RemoveChild(aggregate4);
container1.MakeIntoAnOrphan();
}
finally
{
aggregate4.DeleteInDatabase();
joinable.DeleteInDatabase();
aggregate5.DeleteInDatabase();
}
}
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!
}
protected Compileable(CohortCompiler compiler)
{
_compiler = compiler;
}
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");
}
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");
}
//[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");
}
}
