public void NotPopulated_ParameterNotSet() { _filter.Description = "fish swim in the sea and make people happy to be"; _filter.WhereSQL = "LovelyCoconuts = @coconutCount"; new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(_filter, null); var parameter = _filter.GetAllParameters().Single(); parameter.Comment = "It's coconut time!"; parameter.Value = null;//clear it's value parameter.SaveToDatabase(); var ex = Assert.Throws <Exception>(() => new FilterImporter(new ExtractionFilterFactory(_chiExtractionInformation), null).ImportFilter(_filter, null)); Assert.AreEqual("Cannot clone filter called 'folk' because:Parameter '@coconutCount' was rejected :There is no value/default value listed", ex.Message); }
private void CreateParameters(string param1Value, string param2Value) { container1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); acDataset.RootFilterContainer_ID = container1.ID; acDataset.SaveToDatabase(); AggregateFilter filter1 = new AggregateFilter(CatalogueRepository, "Filter1", container1); filter1.WhereSQL = "@bob = 'bob'"; filter1.SaveToDatabase(); var paramCreator = new ParameterCreator(filter1.GetFilterFactory(), null, null); paramCreator.CreateAll(filter1, null); container2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); acCohort.RootFilterContainer_ID = container2.ID; acCohort.SaveToDatabase(); AggregateFilter filter2 = new AggregateFilter(CatalogueRepository, "Filter2", container2); filter2.WhereSQL = "@bob = 'fish'"; filter2.SaveToDatabase(); paramCreator.CreateAll(filter2, null); parama1 = filter1.GetAllParameters()[0]; parama1.Value = param1Value; parama1.SaveToDatabase(); parama2 = filter2.GetAllParameters()[0]; parama2.Value = param2Value; parama2.SaveToDatabase(); }
public void TestGettingAggregateSQLFromEntirity_IncludingParametersAtTop() { CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration); //setup a filter (all filters must be in a container so the container is a default AND container) var AND = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND); var filter = new AggregateFilter(CatalogueRepository, "hithere", AND); //give the filter an implicit parameter requiring bit of SQL filter.WhereSQL = "1=@abracadabra"; filter.SaveToDatabase(); //Make aggregate1 use the filter we just setup (required to happen before parameter creator gets hit because otherwise it won't know the IFilter DatabaseType because IFilter is an orphan at the moment) aggregate1.RootFilterContainer_ID = AND.ID; aggregate1.SaveToDatabase(); //get it to create the parameters for us new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); //get the parameter it just created, set it's value and save it var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "1"; param.ParameterSQL = "DECLARE @abracadabra AS int;"; param.SaveToDatabase(); //set the order so that 2 comes before 1 rootcontainer.AddChild(aggregate2, 1); rootcontainer.AddChild(aggregate1, 5); try { Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @abracadabra AS int; SET @abracadabra=1; ( /*cic_{0}_UnitTestAggregate2*/ SELECT distinct [" + _scratchDatabaseName + @"]..[BulkData].[chi] FROM [" + _scratchDatabaseName + @"]..[BulkData] EXCEPT /*cic_{0}_UnitTestAggregate1*/ SELECT distinct [" + _scratchDatabaseName + @"]..[BulkData].[chi] FROM [" + _scratchDatabaseName + @"]..[BulkData] WHERE ( /*hithere*/ 1=@abracadabra ) ) ", cohortIdentificationConfiguration.ID)) , CollapseWhitespace(builder.SQL)); CohortQueryBuilder builder2 = new CohortQueryBuilder(aggregate1, null); Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @abracadabra AS int; SET @abracadabra=1; /*cic_{0}_UnitTestAggregate1*/ SELECT distinct [" + _scratchDatabaseName + @"]..[BulkData].[chi] FROM [" + _scratchDatabaseName + @"]..[BulkData] WHERE ( /*hithere*/ 1=@abracadabra )", cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder2.SQL)); string selectStar = new CohortQueryBuilder(aggregate1, null).GetDatasetSampleSQL(); Assert.AreEqual( CollapseWhitespace( string.Format( @"DECLARE @abracadabra AS int; SET @abracadabra=1; /*cic_{0}_UnitTestAggregate1*/ SELECT TOP 1000 * FROM [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData] WHERE ( /*hithere*/ 1=@abracadabra )" , cohortIdentificationConfiguration.ID)), CollapseWhitespace(selectStar)); } finally { filter.DeleteInDatabase(); AND.DeleteInDatabase(); rootcontainer.RemoveChild(aggregate1); rootcontainer.RemoveChild(aggregate2); } }
public void CloneChildWithFilter_IDsDifferent() { //aggregate 1 is now a normal non cohort aggregate var container = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.OR); aggregate1.CountSQL = "count(*)"; aggregate1.RootFilterContainer_ID = container.ID; aggregate1.SaveToDatabase(); //with filters var filter = new AggregateFilter(CatalogueRepository, "MyFilter", container); filter.WhereSQL = "sex=@sex"; //and parameters new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); filter.SaveToDatabase(); var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "'M'"; param.SaveToDatabase(); //we are importing this graph aggregate as a new cohort identification aggregate var clone = cohortIdentificationConfiguration.ImportAggregateConfigurationAsIdentifierList(aggregate1, null); //since its a cohort aggregate it should be identical to the origin Aggregate except it has a different ID and no count SQL Assert.AreEqual(clone.CountSQL, null); //get the original sql var aggregateSql = aggregate1.GetQueryBuilder().SQL; try { Assert.AreNotEqual(clone.ID, aggregate1.ID); Assert.AreNotEqual(clone.RootFilterContainer_ID, aggregate1.RootFilterContainer_ID); var cloneContainer = clone.RootFilterContainer; var cloneFilter = cloneContainer.GetFilters().Single(); Assert.AreNotEqual(cloneContainer.ID, container.ID); Assert.AreNotEqual(cloneFilter.ID, filter.ID); var cloneParameter = (AggregateFilterParameter)cloneFilter.GetAllParameters().Single(); Assert.AreNotEqual(cloneParameter.ID, param.ID); //it has a different ID and is part of an aggregate filter container (It is presumed to be involved with cohort identification cohortIdentificationConfiguration) which means it will be called cic_X_ string cohortAggregateSql = new CohortQueryBuilder(clone, null, null).SQL; //the basic aggregate has the filter, parameter and group by Assert.AreEqual(CollapseWhitespace( string.Format( @"DECLARE @sex AS varchar(50); SET @sex='M'; /*cic_{0}_UnitTestAggregate1*/ SELECT [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi], count(*) FROM [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData] WHERE ( /*MyFilter*/ sex=@sex ) group by [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi] order by [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi]", cohortIdentificationConfiguration.ID)), CollapseWhitespace(aggregateSql)); //the expected differences are //1. should not have the count //2. should not have the group by //3. should be marked with the cic comment with the ID matching the CohortIdentificationConfiguration.ID //4. should have a distinct on the identifier column Assert.AreEqual( @"DECLARE @sex AS varchar(50); SET @sex='M'; /*cic_" + cohortIdentificationConfiguration.ID + @"_UnitTestAggregate1*/ SELECT distinct [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData].[chi] FROM [" + TestDatabaseNames.Prefix + @"ScratchArea].[dbo].[BulkData] WHERE ( /*MyFilter*/ sex=@sex )", cohortAggregateSql); clone.RootFilterContainer.DeleteInDatabase(); container.DeleteInDatabase(); } finally { clone.DeleteInDatabase(); } }
public void CohortIdentificationConfiguration_CloneEntirely() { //set the order so that 2 comes before 1 rootcontainer.AddChild(aggregate1, 5); rootcontainer.AddChild(container1); container1.AddChild(aggregate2, 1); container1.AddChild(aggregate3, 2); //create a filter too var container = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.OR); aggregate1.RootFilterContainer_ID = container.ID; aggregate1.SaveToDatabase(); var filter = new AggregateFilter(CatalogueRepository, "MyFilter", container); filter.WhereSQL = "sex=@sex"; new ParameterCreator(new AggregateFilterFactory(CatalogueRepository), null, null).CreateAll(filter, null); filter.SaveToDatabase(); //with a parameter too var param = (AggregateFilterParameter)filter.GetAllParameters().Single(); param.Value = "'M'"; param.SaveToDatabase(); cohortIdentificationConfiguration.RootCohortAggregateContainer_ID = rootcontainer.ID; cohortIdentificationConfiguration.SaveToDatabase(); try { var clone = cohortIdentificationConfiguration.CreateClone(new ThrowImmediatelyCheckNotifier()); //the objects should be different Assert.AreNotEqual(cohortIdentificationConfiguration.ID, clone.ID); Assert.IsTrue(clone.Name.EndsWith("(Clone)")); Assert.AreNotEqual(clone.RootCohortAggregateContainer_ID, cohortIdentificationConfiguration.RootCohortAggregateContainer_ID); Assert.IsNotNull(clone.RootCohortAggregateContainer_ID); var beforeSQL = new CohortQueryBuilder(cohortIdentificationConfiguration, null).SQL; var cloneSQL = new CohortQueryBuilder(clone, null).SQL; beforeSQL = Regex.Replace(beforeSQL, "cic_[0-9]+_", ""); cloneSQL = Regex.Replace(cloneSQL, "cic_[0-9]+_", ""); //the SQL should be the same for them Assert.AreEqual(beforeSQL, cloneSQL); var containerClone = clone.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively() .Where(a => a.RootFilterContainer_ID != null) .Select(ag => ag.RootFilterContainer).Single(); Assert.AreNotEqual(container, containerClone); //cleanup phase clone.DeleteInDatabase(); containerClone.DeleteInDatabase(); } finally { rootcontainer.RemoveChild(aggregate1); container1.RemoveChild(aggregate2); container1.RemoveChild(aggregate3); filter.DeleteInDatabase(); container.DeleteInDatabase(); } }