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