/// <summary>
/// Creates a new patient index table based on Biochemistry which selects the distinct dates of "NA" test results
/// for every patient
/// </summary>
/// <param name="db"></param>
/// <param name="people"></param>
/// <param name="r"></param>
/// <param name="cic"></param>
/// <returns></returns>
private JoinableCohortAggregateConfiguration SetupPatientIndexTable(DiscoveredDatabase db, PersonCollection people, Random r, CohortIdentificationConfiguration cic)
{
var syntax = db.Server.GetQuerySyntaxHelper();
var tbl = CreateDataset <Biochemistry>(db, people, 10000, r);
var cata = Import(tbl, out _, out _, out _, out ExtractionInformation[] eis);
var chi = eis.Single(ei => ei.GetRuntimeName().Equals("chi", StringComparison.CurrentCultureIgnoreCase));
var code = eis.Single(ei => ei.GetRuntimeName().Equals("TestCode", StringComparison.CurrentCultureIgnoreCase));
var date = eis.Single(ei => ei.GetRuntimeName().Equals("SampleDate", StringComparison.CurrentCultureIgnoreCase));
chi.IsExtractionIdentifier = true;
chi.SaveToDatabase();
var ac = new AggregateConfiguration(CatalogueRepository, cata, "NA by date");
ac.AddDimension(chi);
ac.AddDimension(code);
ac.AddDimension(date);
ac.CountSQL = null;
cic.EnsureNamingConvention(ac);
var and = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filter = new AggregateFilter(CatalogueRepository, "TestCode is NA", and);
filter.WhereSQL = syntax.EnsureWrapped("TestCode") + " = 'NA'";
filter.SaveToDatabase();
ac.RootFilterContainer_ID = and.ID;
ac.SaveToDatabase();
return(new JoinableCohortAggregateConfiguration(CatalogueRepository, cic, ac));
}
private CohortIdentificationConfiguration CreateCohortIdentificationConfiguration(ExtractionFilter inclusionFilter1)
{
//Create the top level configuration object
var cic = new CohortIdentificationConfiguration(_repos.CatalogueRepository, "Tayside Lung Cancer Cohort");
//create a UNION container for Inclusion Criteria
var container = new CohortAggregateContainer(_repos.CatalogueRepository, SetOperation.UNION);
container.Name = "Inclusion Criteria";
container.SaveToDatabase();
cic.RootCohortAggregateContainer_ID = container.ID;
cic.SaveToDatabase();
//Create a new cohort set to the 'Inclusion Criteria' based on the filters Catalogue
var cata = inclusionFilter1.ExtractionInformation.CatalogueItem.Catalogue;
var ac = cic.CreateNewEmptyConfigurationForCatalogue(cata, (a, b) => { throw new Exception("Problem encountered with chi column(s)"); }, false);
container.AddChild(ac, 0);
//Add the filter to the WHERE logic of the cohort set
var whereContainer = new AggregateFilterContainer(_repos.CatalogueRepository, FilterContainerOperation.OR);
ac.Name = "People with " + inclusionFilter1.Name;
ac.RootFilterContainer_ID = whereContainer.ID;
cic.EnsureNamingConvention(ac); //this will put cicx at the front and cause implicit SaveToDatabase
FilterImporter filterImporter = new FilterImporter(new AggregateFilterFactory(_repos.CatalogueRepository), null);
var cloneFilter = filterImporter.ImportFilter(inclusionFilter1, null);
whereContainer.AddChild(cloneFilter);
return(cic);
}
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);
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);
cic.EnsureNamingConvention(agg);
}
public void SetupTestData(ICatalogueRepository repository)
{
BlitzMainDataTables();
Database = GetCleanedServer(FAnsi.DatabaseType.MicrosoftSQLServer);
testData = new BulkTestsData(repository, Database, 100);
testData.SetupTestData();
testData.ImportAsCatalogue();
aggregate1 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate1");
aggregate1.CountSQL = null;
aggregate1.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate1);
aggregate2 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate2");
aggregate2.CountSQL = null;
aggregate2.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate2);
aggregate3 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate3");
aggregate3.CountSQL = null;
aggregate3.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate3);
cohortIdentificationConfiguration = new CohortIdentificationConfiguration(repository, "UnitTestIdentification");
rootcontainer = new CohortAggregateContainer(repository, SetOperation.EXCEPT);
container1 = new CohortAggregateContainer(repository, SetOperation.UNION);
cohortIdentificationConfiguration.RootCohortAggregateContainer_ID = rootcontainer.ID;
cohortIdentificationConfiguration.SaveToDatabase();
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate1);
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate2);
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate3);
}
public void SetupTestData(ICatalogueRepository repository)
{
testData = new BulkTestsData(repository, DiscoveredDatabaseICanCreateRandomTablesIn, 100);
testData.SetupTestData();
testData.ImportAsCatalogue();
aggregate1 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate1");
aggregate1.CountSQL = null;
aggregate1.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate1);
aggregate2 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate2");
aggregate2.CountSQL = null;
aggregate2.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate2);
aggregate3 =
new AggregateConfiguration(repository, testData.catalogue, "UnitTestAggregate3");
aggregate3.CountSQL = null;
aggregate3.SaveToDatabase();
new AggregateDimension(repository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate3);
cohortIdentificationConfiguration = new CohortIdentificationConfiguration(repository, "UnitTestIdentification");
rootcontainer = new CohortAggregateContainer(repository, SetOperation.EXCEPT);
container1 = new CohortAggregateContainer(repository, SetOperation.UNION);
cohortIdentificationConfiguration.RootCohortAggregateContainer_ID = rootcontainer.ID;
cohortIdentificationConfiguration.SaveToDatabase();
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate1);
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate2);
cohortIdentificationConfiguration.EnsureNamingConvention(aggregate3);
}
/// <summary>
/// Creates a table HospitalAdmissions with no filters
/// </summary>
/// <param name="db"></param>
/// <param name="people"></param>
/// <param name="r"></param>
/// <param name="cic"></param>
/// <returns></returns>
private AggregateConfiguration SetupAggregateConfiguration(DiscoveredDatabase db, PersonCollection people, Random r, CohortIdentificationConfiguration cic)
{
var existingTbl = db.ExpectTable("HospitalAdmissions");
var tbl = existingTbl.Exists() ? existingTbl : CreateDataset <HospitalAdmissions>(db, people, 10000, r);
var cata = Import(tbl, out _, out _, out _, out ExtractionInformation[] eis);
var chi = eis.Single(ei => ei.GetRuntimeName().Equals("chi", StringComparison.CurrentCultureIgnoreCase));
chi.IsExtractionIdentifier = true;
chi.SaveToDatabase();
var ac = new AggregateConfiguration(CatalogueRepository, cata, "Hospitalised after NA");
ac.AddDimension(chi);
ac.CountSQL = null;
cic.EnsureNamingConvention(ac);
return(ac);
}
public void CohortGenerationDifferingTableValuedParametersTest()
{
CreateFunction();
//In this example we have 2 configurations which both target the same table valued function but which must have different parameter values
var config1 = new AggregateConfiguration(CatalogueRepository, _function.Cata, "CohortGenerationDifferingTableValuedParametersTest_1");
config1.CountSQL = null;
config1.SaveToDatabase();
var config2 = new AggregateConfiguration(CatalogueRepository, _function.Cata, "CohortGenerationDifferingTableValuedParametersTest_2");
config2.CountSQL = null;
config2.SaveToDatabase();
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "CohortGenerationDifferingTableValuedParametersTest");
cic.EnsureNamingConvention(config1);
cic.EnsureNamingConvention(config2);
try
{
//make the string column the extraction identifier
_function.ExtractionInformations[1].IsExtractionIdentifier = true;
_function.ExtractionInformations[1].SaveToDatabase();
//add the extraction identtifier as the only dimension one ach of the aggregate configurations that we will use for the cohort identification query
new AggregateDimension(CatalogueRepository, _function.ExtractionInformations[1], config1);
new AggregateDimension(CatalogueRepository, _function.ExtractionInformations[1], config2);
Assert.IsNull(cic.RootCohortAggregateContainer_ID);
//create a root container for it
CohortAggregateContainer container = new CohortAggregateContainer(CatalogueRepository, SetOperation.INTERSECT);
//set the container as the root container for the cohort identification task object
cic.RootCohortAggregateContainer_ID = container.ID;
cic.SaveToDatabase();
//put both the aggregates into the container
container.AddChild(config1, 0);
container.AddChild(config2, 1);
CohortQueryBuilder builder = new CohortQueryBuilder(cic);
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"DECLARE @startNumber AS int;
SET @startNumber=5;
DECLARE @stopNumber AS int;
SET @stopNumber=10;
DECLARE @name AS varchar(50);
SET @name='fish';
(
/*cic_{0}_CohortGenerationDifferingTableValuedParametersTest_1*/
SELECT
distinct
MyAwesomeFunction.[Name]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..MyAwesomeFunction(@startNumber,@stopNumber,@name) AS MyAwesomeFunction
INTERSECT
/*cic_{0}_CohortGenerationDifferingTableValuedParametersTest_2*/
SELECT
distinct
MyAwesomeFunction.[Name]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..MyAwesomeFunction(@startNumber,@stopNumber,@name) AS MyAwesomeFunction
)
", cic.ID)),
CollapseWhitespace(builder.SQL));
//now override JUST @name
var param1 = new AnyTableSqlParameter(CatalogueRepository, config1, "DECLARE @name AS varchar(50);");
param1.Value = "'lobster'";
param1.SaveToDatabase();
var param2 = new AnyTableSqlParameter(CatalogueRepository, config2, "DECLARE @name AS varchar(50);");
param2.Value = "'monkey'";
param2.SaveToDatabase();
CohortQueryBuilder builder2 = new CohortQueryBuilder(cic);
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"DECLARE @startNumber AS int;
SET @startNumber=5;
DECLARE @stopNumber AS int;
SET @stopNumber=10;
DECLARE @name AS varchar(50);
SET @name='lobster';
DECLARE @name_2 AS varchar(50);
SET @name_2='monkey';
(
/*cic_{0}_CohortGenerationDifferingTableValuedParametersTest_1*/
SELECT
distinct
MyAwesomeFunction.[Name]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..MyAwesomeFunction(@startNumber,@stopNumber,@name) AS MyAwesomeFunction
INTERSECT
/*cic_{0}_CohortGenerationDifferingTableValuedParametersTest_2*/
SELECT
distinct
MyAwesomeFunction.[Name]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..MyAwesomeFunction(@startNumber,@stopNumber,@name_2) AS MyAwesomeFunction
)
", cic.ID)),
CollapseWhitespace(builder2.SQL));
}
finally
{
cic.DeleteInDatabase();
config1.DeleteInDatabase();
config2.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!
}