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();
}
}
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 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 TestGettingAggregateSQLFromEntirity_Filter_IsDisabled()
{
CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration);
//setup a filter (all filters must be in a container so the container is a default AND container)
var AND1 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filter1_1 = new AggregateFilter(CatalogueRepository, "filter1_1", AND1);
var filter1_2 = new AggregateFilter(CatalogueRepository, "filter1_2", AND1);
var AND2 = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filter2_1 = new AggregateFilter(CatalogueRepository, "filter2_1", AND2);
var filter2_2 = new AggregateFilter(CatalogueRepository, "filter2_2", AND2);
//Filters must belong to containers BEFORE parameter creation
//Make aggregate1 use the filter set we just setup
aggregate1.RootFilterContainer_ID = AND1.ID;
aggregate1.SaveToDatabase();
//Make aggregate3 use the other filter set we just setup
aggregate2.RootFilterContainer_ID = AND2.ID;
aggregate2.SaveToDatabase();
//set the order so that 2 comes before 1
rootcontainer.AddChild(aggregate2, 1);
rootcontainer.AddChild(aggregate1, 5);
filter2_2.IsDisabled = true;
filter2_2.SaveToDatabase();
//give the filter an implicit parameter requiring bit of SQL
foreach (var filter in new IFilter[] { filter1_1, filter1_2, filter2_1, filter2_2 })
{
filter.WhereSQL = "@bob = 'bob'";
filter.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 = "'Boom!'";
param.ParameterSQL = "DECLARE @bob AS varchar(10);";
//change the values of the parameters
if (filter.Equals(filter2_1) || Equals(filter, filter2_2))
{
param.Value = "'Grenades Are Go'";
}
param.SaveToDatabase();
}
Console.WriteLine(builder.SQL);
try
{
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"DECLARE @bob AS varchar(10);
SET @bob='Grenades Are Go';
DECLARE @bob_2 AS varchar(10);
SET @bob_2='Boom!';
(
/*cic_{0}_UnitTestAggregate2*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData]
WHERE
(
/*filter2_1*/
@bob = 'bob'
)
EXCEPT
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData]
WHERE
(
/*filter1_1*/
@bob_2 = 'bob'
AND
/*filter1_2*/
@bob_2 = 'bob'
)
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builder.SQL));
}
finally
{
filter2_2.IsDisabled = false;
filter2_2.SaveToDatabase();
rootcontainer.RemoveChild(aggregate2);
rootcontainer.RemoveChild(aggregate1);
filter1_1.DeleteInDatabase();
filter1_2.DeleteInDatabase();
filter2_1.DeleteInDatabase();
filter2_2.DeleteInDatabase();
AND1.DeleteInDatabase();
AND2.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();
}
}