/// <summary>
/// Creates a entirely new copy of the <see cref="CohortIdentificationConfiguration"/> with all new IDs on the root and all child objects. This includes
/// filters, patient index tables, parameters, set containers etc.
/// <para>This is done in a transaction so that if it fails halfway through you won't end up with half a clone configuration</para>
/// </summary>
/// <param name="notifier">Event listener for reporting cloning progress and any problems</param>
/// <returns></returns>
public CohortIdentificationConfiguration CreateClone(ICheckNotifier notifier)
{
//todo this would be nice if it was ICatalogueRepository but transaction is super SQLy
var cataRepo = ((CatalogueRepository)Repository);
//start a new super transaction
using (cataRepo.BeginNewTransactedConnection())
{
try
{
notifier.OnCheckPerformed(new CheckEventArgs("Super Transaction started on Catalogue Repository", CheckResult.Success));
var clone = new CohortIdentificationConfiguration(cataRepo, Name + " (Clone)");
notifier.OnCheckPerformed(new CheckEventArgs("Created clone configuration '" + clone.Name + "' with ID " + clone.ID + " called " + clone, CheckResult.Success));
//clone the global parameters
foreach (var p in GetAllParameters())
{
notifier.OnCheckPerformed(new CheckEventArgs("Cloning global parameter " + p.ParameterName, CheckResult.Success));
var cloneP = new AnyTableSqlParameter(cataRepo, clone, p.ParameterSQL);
cloneP.Comment = p.Comment;
cloneP.Value = p.Value;
cloneP.SaveToDatabase();
}
//key is the original, value is the clone
var parentToCloneJoinablesDictionary = new Dictionary <JoinableCohortAggregateConfiguration, JoinableCohortAggregateConfiguration>();
//clone the joinables
foreach (var joinable in GetAllJoinables())
{
//clone the aggregate which has permission to be joinable
var cloneJoinableAggregate = joinable.AggregateConfiguration.CreateClone();
//clone the join permission
var cloneJoinable = new JoinableCohortAggregateConfiguration(cataRepo, clone, cloneJoinableAggregate);
parentToCloneJoinablesDictionary.Add(joinable, cloneJoinable);
}
clone.ClonedFrom_ID = ID;
clone.RootCohortAggregateContainer_ID = RootCohortAggregateContainer.CloneEntireTreeRecursively(notifier, this, clone, parentToCloneJoinablesDictionary).ID;
clone.SaveToDatabase();
notifier.OnCheckPerformed(new CheckEventArgs("Clone creation successful, about to commit Super Transaction", CheckResult.Success));
cataRepo.EndTransactedConnection(true);
notifier.OnCheckPerformed(new CheckEventArgs("Super Transaction committed successfully", CheckResult.Success));
return(clone);
}
catch (Exception e)
{
cataRepo.EndTransactedConnection(false);
notifier.OnCheckPerformed(new CheckEventArgs("Cloning failed, See Exception for details, the Super Transaction was rolled back successfully though", CheckResult.Fail, e));
}
}
return(null);
}
public JoinableTask(JoinableCohortAggregateConfiguration joinable, CohortCompiler compiler) : base(compiler)
{
Joinable = joinable;
_aggregate = Joinable.AggregateConfiguration;
_cohortIdentificationConfiguration = _aggregate.GetCohortIdentificationConfigurationIfAny();
_catalogueName = Joinable.AggregateConfiguration.Catalogue.Name;
RefreshIsUsedState();
}
public void CreateUsers_SelfReferrential()
{
var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1);
try
{
var ex = Assert.Throws <NotSupportedException>(() => joinable.AddUser(aggregate1));
Assert.AreEqual("Cannot configure AggregateConfiguration UnitTestAggregate1 as a Join user to itself!", ex.Message);
}
finally
{
joinable.DeleteInDatabase();
}
}
public void CreateUsers_DuplicateUser()
{
var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1);
try
{
joinable.AddUser(aggregate2);
Assert.Throws <SqlException>(() => joinable.AddUser(aggregate2));
}
finally
{
joinable.DeleteInDatabase();
}
}
public void QueryBuilderTest()
{
var builder = new CohortQueryBuilder(aggregate1, null);
//make aggregate 2 a joinable
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
joinable2.AddUser(aggregate1);
Console.WriteLine(builder.SQL);
try
{
using (var con = (SqlConnection)Database.Server.GetConnection())
{
con.Open();
var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();
//can read at least one row
Assert.IsTrue(dbReader.Read());
}
string expectedTableAlias = "ix" + joinable2.ID;
//after joinables
Assert.AreEqual(
string.Format(
@"/*cic_{1}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData]
LEFT Join (
/*cic_{1}_UnitTestAggregate2*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData]
){0}
on [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] = {0}.chi", expectedTableAlias, cohortIdentificationConfiguration.ID), builder.SQL);
}
finally
{
joinable2.Users[0].DeleteInDatabase();
joinable2.DeleteInDatabase();
}
}
public void CreateJoinable_AddTwice()
{
//delete the first dimension (chi)
var join1 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1);
try
{
var ex = Assert.Throws <SqlException>(() => new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1));
Assert.IsTrue(ex.Message.Contains("ix_eachAggregateCanOnlyBeJoinableOnOneProject"));
}
finally
{
join1.DeleteInDatabase();
}
}
public void CreateUsers_ToAnyOtherJoinable()
{
var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1);
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
try
{
var ex = Assert.Throws <NotSupportedException>(() => joinable.AddUser(aggregate2));
Assert.AreEqual("Cannot add user UnitTestAggregate2 because that AggregateConfiguration is itself a JoinableCohortAggregateConfiguration", ex.Message);
}
finally
{
joinable.DeleteInDatabase();
joinable2.DeleteInDatabase();
}
}
public void CreateUsers_ToNoExtractionIdentifierTable()
{
var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1);
aggregate2.AggregateDimensions.First().DeleteInDatabase();
aggregate2.ClearAllInjections();
try
{
var ex = Assert.Throws <NotSupportedException>(() => joinable.AddUser(aggregate2));
Assert.AreEqual("Cannot configure AggregateConfiguration UnitTestAggregate2 as join user because it does not contain exactly 1 IsExtractionIdentifier dimension", ex.Message);
}
finally
{
joinable.DeleteInDatabase();
}
}
public void CreateJoinable()
{
JoinableCohortAggregateConfiguration joinable = null;
try
{
joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1);
Assert.AreEqual(joinable.CohortIdentificationConfiguration_ID, cohortIdentificationConfiguration.ID);
Assert.AreEqual(joinable.AggregateConfiguration_ID, aggregate1.ID);
}
finally
{
if (joinable != null)
{
joinable.DeleteInDatabase();
}
}
}
public void CreateUsers()
{
JoinableCohortAggregateConfiguration joinable = null;
try
{
joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate1);
joinable.AddUser(aggregate2);
Assert.IsTrue(joinable.Users.Length == 1);
Assert.AreEqual(aggregate2, joinable.Users[0].AggregateConfiguration);
}
finally
{
if (joinable != null)
{
joinable.DeleteInDatabase();
}
}
}
/// <summary>
/// Creates a table HospitalAdmissions that uses the patient index table <paramref name="joinable"/> to return distinct patients
/// who have been hospitalised after receiving an NA test (no time limit)
/// </summary>
/// <param name="db"></param>
/// <param name="people"></param>
/// <param name="r"></param>
/// <param name="cic"></param>
/// <param name="joinable"></param>
private AggregateConfiguration SetupPatientIndexTableUser(DiscoveredDatabase db, PersonCollection people, Random r, CohortIdentificationConfiguration cic, JoinableCohortAggregateConfiguration joinable)
{
var syntax = db.Server.GetQuerySyntaxHelper();
var ac = SetupAggregateConfiguration(db, people, r, cic);
var and = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filter = new AggregateFilter(CatalogueRepository, "Hospitalised after an NA", and);
filter.WhereSQL = syntax.EnsureWrapped("AdmissionDate") + " > " + syntax.EnsureWrapped("SampleDate");
filter.SaveToDatabase();
ac.RootFilterContainer_ID = and.ID;
ac.SaveToDatabase();
//ac joins against the joinable
new JoinableCohortAggregateConfigurationUse(CatalogueRepository, ac, joinable);
return(ac);
}
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 JoinablesWithCache()
{
string queryCachingDatabaseName = To.GetRuntimeName();
_queryCachingDatabase = To;
var builder = new CohortQueryBuilder(aggregate1, null);
//make aggregate 2 a joinable
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
joinable2.AddUser(aggregate1);
//make aggregate 2 have an additional column (dtCreated)
var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated"));
aggregate2.AddDimension(anotherCol);
MasterDatabaseScriptExecutor scripter = new MasterDatabaseScriptExecutor(_queryCachingDatabase);
scripter.CreateAndPatchDatabase(new QueryCachingPatcher(), new AcceptAllCheckNotifier());
var queryCachingDatabaseServer = new ExternalDatabaseServer(CatalogueRepository, queryCachingDatabaseName, null);
queryCachingDatabaseServer.SetProperties(_queryCachingDatabase);
//make the builder use the query cache we just set SetUp
builder.CacheServer = queryCachingDatabaseServer;
try
{
var builderForCaching = new CohortQueryBuilder(aggregate2, null, true);
var cacheDt = new DataTable();
using (SqlConnection con = (SqlConnection)Database.Server.GetConnection())
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(builderForCaching.SQL, con));
da.Fill(cacheDt);
}
var cacheManager = new CachedAggregateConfigurationResultsManager(queryCachingDatabaseServer);
cacheManager.CommitResults(new CacheCommitJoinableInceptionQuery(aggregate2, builderForCaching.SQL, cacheDt, null, 30));
try
{
Console.WriteLine(builder.SQL);
using (var con = (SqlConnection)Database.Server.GetConnection())
{
con.Open();
var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();
//can read at least one row
Assert.IsTrue(dbReader.Read());
}
string expectedTableAlias = "ix" + joinable2.ID;
//after joinables
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"/*cic_{2}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData]
LEFT Join (
/*Cached:cic_{2}_UnitTestAggregate2*/
select * from [{3}]..[JoinableInceptionQuery_AggregateConfiguration{1}]
){0}
on [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] = {0}.chi",
expectedTableAlias, //{0}
aggregate2.ID, //{1}
cohortIdentificationConfiguration.ID, //{2}
queryCachingDatabaseName) //{3}
), CollapseWhitespace(builder.SQL));
}
finally
{
joinable2.Users[0].DeleteInDatabase();
joinable2.DeleteInDatabase();
}
}
finally
{
queryCachingDatabaseServer.DeleteInDatabase();
DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(queryCachingDatabaseName).Drop();
}
}
public void QueryBuilderTest_JoinableCloning()
{
var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated"));
aggregate2.AddDimension(anotherCol);
//make aggregate 2 a joinable
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
joinable2.AddUser(aggregate1);
string expectedTableAlias = "ix" + joinable2.ID;
var filterContainer1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filterContainer2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filter1 = new AggregateFilter(CatalogueRepository, "Within 1 year of event", filterContainer1);
var filter2 = new AggregateFilter(CatalogueRepository, "DateAfter2001", filterContainer2);
filter1.WhereSQL = string.Format("ABS(DATEDIFF(year, {0}.dtCreated, [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].dtCreated)) <= 1", expectedTableAlias);
filter1.SaveToDatabase();
filter2.WhereSQL = "dtCreated > '2001-01-01'";
filter2.SaveToDatabase();
aggregate1.RootFilterContainer_ID = filterContainer1.ID;
aggregate1.SaveToDatabase();
aggregate2.RootFilterContainer_ID = filterContainer2.ID;
aggregate2.SaveToDatabase();
//add the first aggregate to the configuration
rootcontainer.AddChild(aggregate1, 1);
var globalParameter = new AnyTableSqlParameter(CatalogueRepository, cohortIdentificationConfiguration, "DECLARE @fish varchar(50)");
globalParameter.Comment = "Comments for the crazies";
globalParameter.Value = "'fishes'";
globalParameter.SaveToDatabase();
var builder = new CohortQueryBuilder(cohortIdentificationConfiguration);
try
{
var clone = cohortIdentificationConfiguration.CreateClone(new ThrowImmediatelyCheckNotifier());
var cloneBuilder = new CohortQueryBuilder(clone);
string origSql = builder.SQL;
string cloneOrigSql = cloneBuilder.SQL;
Console.WriteLine("//////////////////////////////////////////////VERBATIM//////////////////////////////////////////////");
Console.WriteLine(origSql);
Console.WriteLine(cloneOrigSql);
Console.WriteLine("//////////////////////////////////////////////END VERBATIM//////////////////////////////////////////////");
var builderSql = Regex.Replace(Regex.Replace(origSql, "cic_[0-9]+_", ""), "ix[0-9]+", "ix");
var cloneBuilderSql = Regex.Replace(Regex.Replace(cloneOrigSql, "cic_[0-9]+_", ""), "ix[0-9]+", "ix").Replace("(Clone)", "");//get rid of explicit ix53 etc for the comparison
Console.WriteLine("//////////////////////////////////////////////TEST COMPARISON IS//////////////////////////////////////////////");
Console.WriteLine(builderSql);
Console.WriteLine(cloneBuilderSql);
Console.WriteLine("//////////////////////////////////////////////END COMPARISON//////////////////////////////////////////////");
Assert.AreEqual(builderSql, cloneBuilderSql);
////////////////Cleanup Database//////////////////////////////
//find the WHERE logic too
var containerClone = clone.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively() //get all the aggregates
.Union(clone.GetAllJoinables().Select(j => j.AggregateConfiguration)) //including the joinables
.Where(a => a.RootFilterContainer_ID != null) //that have WHERE sql
.Select(ag => ag.RootFilterContainer); //grab their containers so we can clean them SetUp
((IDeleteable)clone.GetAllParameters()[0]).DeleteInDatabase();
clone.DeleteInDatabase();
//delete the WHERE logic too
foreach (AggregateFilterContainer c in containerClone)
{
c.DeleteInDatabase();
}
}
finally
{
rootcontainer.RemoveChild(aggregate1);
filter1.DeleteInDatabase();
filter2.DeleteInDatabase();
filterContainer1.DeleteInDatabase();
filterContainer2.DeleteInDatabase();
joinable2.Users[0].DeleteInDatabase();
joinable2.DeleteInDatabase();
globalParameter.DeleteInDatabase();
}
}
private AggregateConfiguration SetupPatientIndexTableUserWithFilter(DiscoveredDatabase db, PersonCollection people, Random r, CohortIdentificationConfiguration cic, JoinableCohortAggregateConfiguration joinable, bool useParameter, string paramName, string paramValue)
{
var syntax = db.Server.GetQuerySyntaxHelper();
var ac1 = SetupPatientIndexTableUser(db, people, r, cic, joinable);
AddFilter(db, "My Filter", syntax.EnsureWrapped("AdmissionDate") + " < ", ac1, useParameter, paramName, paramValue);
return(ac1);
}
public void QueryBuilderTest_AdditionalColumn()
{
var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated"));
aggregate2.AddDimension(anotherCol);
//make aggregate 2 a joinable
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
joinable2.AddUser(aggregate1);
string expectedTableAlias = "ix" + joinable2.ID;
var filterContainer1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filterContainer2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filter1 = new AggregateFilter(CatalogueRepository, "Within 1 year of event", filterContainer1);
var filter2 = new AggregateFilter(CatalogueRepository, "DateAfter2001", filterContainer2);
filter1.WhereSQL = string.Format("ABS(DATEDIFF(year, {0}.dtCreated, [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].dtCreated)) <= 1", expectedTableAlias);
filter1.SaveToDatabase();
filter2.WhereSQL = "dtCreated > '2001-01-01'";
filter2.SaveToDatabase();
aggregate1.RootFilterContainer_ID = filterContainer1.ID;
aggregate1.SaveToDatabase();
aggregate2.RootFilterContainer_ID = filterContainer2.ID;
aggregate2.SaveToDatabase();
var builder = new CohortQueryBuilder(aggregate1, null);
Console.WriteLine(builder.SQL);
try
{
using (var con = (SqlConnection)Database.Server.GetConnection())
{
con.Open();
var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();
//can read at least one row
Assert.IsTrue(dbReader.Read());
}
//after joinables
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"/*cic_{1}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData]
LEFT Join (
/*cic_{1}_UnitTestAggregate2*/
SELECT distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi], [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[dtCreated]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData]
WHERE
(
/*DateAfter2001*/
dtCreated > '2001-01-01'
)
){0}
on [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] = {0}.chi
WHERE
(
/*Within 1 year of event*/
ABS(DATEDIFF(year, {0}.dtCreated, [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].dtCreated)) <= 1
)", expectedTableAlias, cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.SQL));
}
finally
{
filter1.DeleteInDatabase();
filter2.DeleteInDatabase();
filterContainer1.DeleteInDatabase();
filterContainer2.DeleteInDatabase();
joinable2.Users[0].DeleteInDatabase();
joinable2.DeleteInDatabase();
}
}
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();
}
}