/// <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(); } }