protected override string GetSQL()
{
CohortQueryBuilder builder = new CohortQueryBuilder(AggregateConfiguration, CohortIdentificationConfigurationIfAny.GetAllParameters(), true);
var sql = builder.SQL;
var extractionIdentifier = AggregateConfiguration.AggregateDimensions.Single(d => d.IsExtractionIdentifier);
//IMPORTANT: We are using impromptu SQL instead of a Spontaneous container / CustomLine because we want the CohortQueryBuilder to decide to use
//the cached table data (if any). If it senses we are monkeying with the query it will run it verbatim which will be very slow.
string whereString = AggregateConfiguration.RootFilterContainer_ID != null ? "AND " : "WHERE ";
var impromptuSql = whereString + extractionIdentifier.SelectSQL + " IN (SELECT " +
_extractableCohort.GetPrivateIdentifier() + " FROM " +
_extractableCohort.ExternalCohortTable.TableName + " WHERE " + _extractableCohort.WhereSQL() + ")";
//if there is a group by then we must insert the AND patient in cohort bit before the group by but after any WHERE containers
int insertionPoint = sql.IndexOf("group by", 0, StringComparison.CurrentCultureIgnoreCase);
//if there isn't a group by
if (insertionPoint == -1)
{
return(sql + Environment.NewLine + impromptuSql);
}
//there is a group by
return(sql.Substring(0, insertionPoint) + Environment.NewLine + impromptuSql + Environment.NewLine + sql.Substring(insertionPoint, sql.Length - insertionPoint));
}
public void TestOrdering_ContainerThenAggregate()
{
CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration);
//set the order so that a configuration is in position 1
rootcontainer.AddChild(aggregate1, 2);
//then a container in position 2
container1.Order = 1;
container1.SaveToDatabase();
rootcontainer.AddChild(container1);
//container 1 contains both other aggregates
container1.AddChild(aggregate2, 1);
container1.AddChild(aggregate3, 2);
try
{
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"(
(
/*cic_{0}_UnitTestAggregate2*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
UNION
/*cic_{0}_UnitTestAggregate3*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
)
EXCEPT
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
)", cohortIdentificationConfiguration.ID))
, CollapseWhitespace(builder.SQL));
}
finally
{
container1.RemoveChild(aggregate2);
container1.RemoveChild(aggregate3);
rootcontainer.RemoveChild(aggregate1);
}
}
public string GetSql()
{
string sql = "";
var ac = AggregateConfiguration;
if (ac.IsCohortIdentificationAggregate)
{
var cic = ac.GetCohortIdentificationConfigurationIfAny();
var globals = cic.GetAllParameters();
var builder = new CohortQueryBuilder(ac, globals, null);
if (UseQueryCache)
{
builder.CacheServer = GetCacheServer();
}
sql = TopX.HasValue ? builder.GetDatasetSampleSQL(TopX.Value) : builder.SQL;
}
else
{
var builder = ac.GetQueryBuilder();
sql = builder.SQL;
}
return(sql);
}
public string GetSql()
{
string sql = "";
var ac = AggregateConfiguration;
if (ac.IsCohortIdentificationAggregate)
{
var cic = ac.GetCohortIdentificationConfigurationIfAny();
var isJoinable = ac.IsJoinablePatientIndexTable();
var globals = cic.GetAllParameters();
var builder = new CohortQueryBuilder(ac, globals, isJoinable);
if (UseQueryCache)
{
builder.CacheServer = GetCacheServer();
}
sql = builder.GetDatasetSampleSQL(100);
}
else
{
var builder = ac.GetQueryBuilder();
sql = builder.SQL;
}
return(sql);
}
private void GenerateQuery()
{
QueryEditor.ReadOnly = false;
CommonFunctionality.ResetChecks();
try
{
var cic = (CohortIdentificationConfiguration)DatabaseObject;
var builder = new CohortQueryBuilder(cic, null);
if (!btnUseCache.Checked && cic.QueryCachingServer_ID.HasValue)
{
builder.CacheServer = null;
}
QueryEditor.Text = builder.SQL;
CommonFunctionality.ScintillaGoRed(QueryEditor, false);
}
catch (Exception ex)
{
QueryEditor.Text = "Could not build Sql:" + ex.Message;
CommonFunctionality.ScintillaGoRed(QueryEditor, true);
CommonFunctionality.Fatal("Failed to build query", ex);
}
QueryEditor.ReadOnly = true;
}
private ParameterCollectionUIOptions Create(CohortIdentificationConfiguration cohortIdentificationConfiguration, ICoreChildProvider coreChildProvider)
{
var builder = new CohortQueryBuilder(cohortIdentificationConfiguration, coreChildProvider);
builder.RegenerateSQL();
var paramManager = builder.ParameterManager;
return(new ParameterCollectionUIOptions(UseCaseCohortIdentificationConfiguration, cohortIdentificationConfiguration, ParameterLevel.Global, paramManager));
}
public void TestGettingAggregateJustFromConfig_DistinctCHISelect()
{
CohortQueryBuilder builder = new CohortQueryBuilder(aggregate1, null);
Assert.AreEqual(CollapseWhitespace(string.Format(@"/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]", cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.SQL));
}
public string GetSql()
{
var builder = new CohortQueryBuilder(CohortIdentificationConfiguration, null);
if (!UseQueryCache && CohortIdentificationConfiguration.QueryCachingServer_ID.HasValue)
{
builder.CacheServer = null;
}
return(builder.SQL);
}
public void TestGettingAggregateJustFromConfig_SelectStar()
{
CohortQueryBuilder builder = new CohortQueryBuilder(aggregate1, null);
Assert.AreEqual(CollapseWhitespace(
string.Format(@"/*cic_{0}_UnitTestAggregate1*/
SELECT
TOP 1000
*
FROM
[" + _scratchDatabaseName + @"]..[BulkData]", cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.GetDatasetSampleSQL()));
}
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 IDataAccessPoint GetDataAccessPoint()
{
var cache = GetCacheServer();
if (UseQueryCache && cache != null)
{
return(cache);
}
else
{
var builder = new CohortQueryBuilder(CohortIdentificationConfiguration, null);
builder.RegenerateSQL();
return(new SelfCertifyingDataAccessPoint(builder.Results.TargetServer));
}
}
private void GenerateQuery()
{
QueryEditor.ReadOnly = false;
var cic = (CohortIdentificationConfiguration)DatabaseObject;
var builder = new CohortQueryBuilder(cic);
if (!btnUseCache.Checked && cic.QueryCachingServer_ID.HasValue)
{
builder.CacheServer = null;
}
QueryEditor.Text = builder.SQL;
QueryEditor.ReadOnly = true;
}
public void ShortcutFiltersWork_ProperlyReplicatesParentAndHasFK()
{
_filter.WhereSQL = "folk=1";
_filter.SaveToDatabase();
string sql = new CohortQueryBuilder(aggregate1, null).SQL;
Console.WriteLine(sql);
Assert.IsTrue(sql.Contains("folk=1"));
var shortcutAggregate =
new AggregateConfiguration(CatalogueRepository, testData.catalogue, "UnitTestShortcutAggregate");
new AggregateDimension(CatalogueRepository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("sex")), shortcutAggregate);
//before it is a shortcut it has no filters
Assert.IsFalse(shortcutAggregate.GetQueryBuilder().SQL.Contains("WHERE"));
//make it a shortcut
shortcutAggregate.OverrideFiltersByUsingParentAggregateConfigurationInstead_ID = aggregate1.ID;
shortcutAggregate.SaveToDatabase();
string sqlShortcut = shortcutAggregate.GetQueryBuilder().SQL;
//shortcut should have it's own dimensions
Assert.IsTrue(sqlShortcut.Contains("[sex]"));
Assert.IsFalse(sqlShortcut.Contains("[chi]"));
//but should have a REFERENCE (not a clone!) to aggregate 1's filters
Assert.IsTrue(sqlShortcut.Contains("folk=1"));
//make sure it is a reference by changing the original
_filter.WhereSQL = "folk=2";
_filter.SaveToDatabase();
Assert.IsTrue(shortcutAggregate.GetQueryBuilder().SQL.Contains("folk=2"));
//shouldnt work because of the dependency of the child - should give a foreign key error
Assert.Throws <SqlException>(aggregate1.DeleteInDatabase);
//delete the child
shortcutAggregate.DeleteInDatabase();
aggregate1.DeleteInDatabase();
aggregate1 = null;
}
private ParameterCollectionUIOptions Create(CohortIdentificationConfiguration cohortIdentificationConfiguration)
{
var builder = new CohortQueryBuilder(cohortIdentificationConfiguration);
try
{
builder.RegenerateSQL();
}
catch (QueryBuildingException ex)
{
ExceptionViewer.Show("There was a problem resolving all the underlying parameters in all your various Aggregates, the following dialogue is reliable only for the Globals", ex);
}
var paramManager = builder.ParameterManager;
return(new ParameterCollectionUIOptions(UseCaseCohortIdentificationConfiguration, cohortIdentificationConfiguration, ParameterLevel.Global, paramManager));
}
public void TestGettingAggregateSQLFromEntirity()
{
CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration);
Assert.AreEqual(null, aggregate1.GetCohortAggregateContainerIfAny());
//set the order so that 2 comes before 1
rootcontainer.AddChild(aggregate2, 1);
rootcontainer.AddChild(aggregate1, 5);
Assert.AreEqual(rootcontainer, aggregate1.GetCohortAggregateContainerIfAny());
try
{
Assert.AreEqual(
CollapseWhitespace(string.Format(
@"(
/*cic_{0}_UnitTestAggregate2*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
EXCEPT
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
)"
, cohortIdentificationConfiguration.ID))
, CollapseWhitespace(builder.SQL));
}
finally
{
rootcontainer.RemoveChild(aggregate1);
rootcontainer.RemoveChild(aggregate2);
}
}
protected virtual string GetSQL()
{
if (!AggregateConfiguration.IsCohortIdentificationAggregate)
{
var builder = AggregateConfiguration.GetQueryBuilder();
return(builder.SQL);
}
var cic = AggregateConfiguration.GetCohortIdentificationConfigurationIfAny();
if (cic == null)
{
throw new Exception("There GetCohortIdentificationConfiguration is unknown for '" + AggregateConfiguration + "'");
}
var cohortBuilder = new CohortQueryBuilder(AggregateConfiguration, cic.GetAllParameters(), null);
return(cohortBuilder.SQL);
}
public ParameterCollectionUIOptions Create(AggregateConfiguration aggregateConfiguration)
{
ParameterManager pm;
if (aggregateConfiguration.IsCohortIdentificationAggregate)
{
//Add the globals if it is part of a CohortIdentificationConfiguration
var cic = aggregateConfiguration.GetCohortIdentificationConfigurationIfAny();
var globals = cic != null?cic.GetAllParameters() : new ISqlParameter[0];
var builder = new CohortQueryBuilder(aggregateConfiguration, globals);
pm = builder.ParameterManager;
try
{
//Generate the SQL which will make it find all the other parameters
builder.RegenerateSQL();
}
catch (QueryBuildingException)
{
//if theres a problem with parameters or anything else, it is okay this diaog might let the user fix that
}
}
else
{
var builder = aggregateConfiguration.GetQueryBuilder();
pm = builder.ParameterManager;
try
{
//Generate the SQL which will make it find all the other parameters
builder.RegenerateSQL();
}
catch (QueryBuildingException)
{
//if theres a problem with parameters or anything else, it is okay this diaog might let the user fix that
}
}
return(new ParameterCollectionUIOptions(UseCaseAggregateConfiguration, aggregateConfiguration, ParameterLevel.CompositeQueryLevel, pm));
}
public void Test_GetDatasetSampleSQL_WithHAVING()
{
aggregate1.HavingSQL = "count(*)>1";
CohortQueryBuilder builder = new CohortQueryBuilder(aggregate1, null);
Assert.AreEqual(CollapseWhitespace(
string.Format(@"/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
TOP 1000
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
group by
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
HAVING
count(*)>1", cohortIdentificationConfiguration.ID)), CollapseWhitespace(builder.GetDatasetSampleSQL()));
}
public void TestGettingAggregateSQL_Aggregate_IsDisabled()
{
Assert.AreEqual(null, aggregate1.GetCohortAggregateContainerIfAny());
//set the order so that 2 comes before 1
rootcontainer.AddChild(aggregate2, 1);
rootcontainer.AddChild(aggregate1, 5);
//disable aggregate 1
aggregate1.IsDisabled = true;
aggregate1.SaveToDatabase();
Assert.AreEqual(rootcontainer, aggregate1.GetCohortAggregateContainerIfAny());
CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration, null);
try
{
Assert.AreEqual(
CollapseWhitespace(string.Format(
@"(
/*cic_{0}_UnitTestAggregate2*/
SELECT
distinct
[" + _scratchDatabaseName + @"].dbo.[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"].dbo.[BulkData]
)"
, cohortIdentificationConfiguration.ID))
, CollapseWhitespace(builder.SQL));
}
finally
{
aggregate1.IsDisabled = false;
aggregate1.SaveToDatabase();
rootcontainer.RemoveChild(aggregate1);
rootcontainer.RemoveChild(aggregate2);
}
}
private void TestAddingTvfToCIC()
{
var root = _cic.RootCohortAggregateContainer;
root.Operation = SetOperation.EXCEPT;
root.SaveToDatabase();
//declare a global parameter of 1 on the aggregate
_cicAggregate = _cic.ImportAggregateConfigurationAsIdentifierList(_aggregate, (s, e) => { return(null); });
//it should have imported the global parameter as part of the import right?
Assert.AreEqual(1, _cicAggregate.GetAllParameters().Count());
//add the new cic to the container
root.AddChild(_cicAggregate, 2);
//So container is:
// EXCEPT
//People in _nonTvfCatalogue (3)
//People in _tvfCatalogue (with @numberOfRecords = 1) (1)
//result should be 2
var qb = new CohortQueryBuilder(_cic);
var sql = qb.SQL;
var db = DataAccessPortal.GetInstance().ExpectDatabase(_tvfTableInfo, DataAccessContext.InternalDataProcessing);
using (var con = db.Server.GetConnection())
{
con.Open();
var r = db.Server.GetCommand(sql, con).ExecuteReader();
//2 chi numbers should be returned
Assert.IsTrue(r.Read());
Assert.IsTrue(r.Read());
Assert.IsFalse(r.Read());
}
}
public void TestGettingAggregateSQLFromEntirity_StopEarly()
{
rootcontainer.AddChild(aggregate1, 1);
rootcontainer.AddChild(aggregate2, 2);
rootcontainer.AddChild(aggregate3, 3);
CohortQueryBuilder builder = new CohortQueryBuilder(rootcontainer, null);
builder.StopContainerWhenYouReach = aggregate2;
try
{
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
EXCEPT
/*cic_{0}_UnitTestAggregate2*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builder.SQL));
CohortQueryBuilder builder2 = new CohortQueryBuilder(rootcontainer, null);
builder2.StopContainerWhenYouReach = null;
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
EXCEPT
/*cic_{0}_UnitTestAggregate2*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
EXCEPT
/*cic_{0}_UnitTestAggregate3*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builder2.SQL));
}
finally
{
rootcontainer.RemoveChild(aggregate1);
rootcontainer.RemoveChild(aggregate2);
rootcontainer.RemoveChild(aggregate3);
}
}
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 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 TestGettingAggregateJustFromConfig_DistinctCHISelect()
{
CachedAggregateConfigurationResultsManager manager = new CachedAggregateConfigurationResultsManager(externalDatabaseServer);
cohortIdentificationConfiguration.QueryCachingServer_ID = externalDatabaseServer.ID;
cohortIdentificationConfiguration.SaveToDatabase();
cohortIdentificationConfiguration.CreateRootContainerIfNotExists();
cohortIdentificationConfiguration.RootCohortAggregateContainer.AddChild(aggregate1, 0);
CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration, null);
try
{
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData]
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builder.SQL));
var server = queryCacheDatabase.Server;
using (var con = server.GetConnection())
{
con.Open();
var da = server.GetDataAdapter(builder.SQL, con);
var dt = new DataTable();
da.Fill(dt);
manager.CommitResults(new CacheCommitIdentifierList(aggregate1,
string.Format(@"/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData]", cohortIdentificationConfiguration.ID), dt, _chiColumnSpecification, 30));
}
CohortQueryBuilder builderCached = new CohortQueryBuilder(cohortIdentificationConfiguration, null);
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*Cached:cic_{0}_UnitTestAggregate1*/
select * from [" + queryCacheDatabase.GetRuntimeName() + "]..[IndexedExtractionIdentifierList_AggregateConfiguration" + aggregate1.ID + @"]
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builderCached.SQL));
}
finally
{
cohortIdentificationConfiguration.RootCohortAggregateContainer.RemoveChild(aggregate1);
}
}
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 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 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 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_StopEarlyContainer()
{
rootcontainer.AddChild(aggregate1, -5);
container1.AddChild(aggregate2, 2);
container1.AddChild(aggregate3, 3);
rootcontainer.AddChild(container1);
AggregateConfiguration aggregate4 = new AggregateConfiguration(CatalogueRepository, testData.catalogue, "UnitTestAggregate4");
new AggregateDimension(CatalogueRepository, testData.extractionInformations.Single(e => e.GetRuntimeName().Equals("chi")), aggregate4);
rootcontainer.AddChild(aggregate4, 5);
CohortQueryBuilder builder = new CohortQueryBuilder(rootcontainer, null);
//Looks like:
/*
*
* EXCEPT
* Aggregate 1
* UNION <-----We tell it to stop after this container
* Aggregate2
* Aggregate3
* Aggregate 4
*/
builder.StopContainerWhenYouReach = container1;
try
{
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
EXCEPT
(
/*cic_{0}_UnitTestAggregate2*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
UNION
/*cic_{0}_UnitTestAggregate3*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
)
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builder.SQL));
}
finally
{
rootcontainer.RemoveChild(aggregate1);
rootcontainer.RemoveChild(aggregate4);
container1.RemoveChild(aggregate2);
container1.RemoveChild(aggregate3);
aggregate4.DeleteInDatabase();
}
}
public void TestHavingSQL()
{
rootcontainer.AddChild(aggregate1, -5);
container1.AddChild(aggregate2, 2);
container1.AddChild(aggregate3, 3);
aggregate2.HavingSQL = "count(*)>1";
aggregate2.SaveToDatabase();
aggregate1.HavingSQL = "SUM(Result)>10";
aggregate1.SaveToDatabase();
try
{
rootcontainer.AddChild(container1);
CohortQueryBuilder builder = new CohortQueryBuilder(rootcontainer, null);
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
group by
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
HAVING
SUM(Result)>10
EXCEPT
(
/*cic_{0}_UnitTestAggregate2*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
group by
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
HAVING
count(*)>1
UNION
/*cic_{0}_UnitTestAggregate3*/
SELECT
distinct
[" + _scratchDatabaseName + @"]..[BulkData].[chi]
FROM
[" + _scratchDatabaseName + @"]..[BulkData]
)
)
", cohortIdentificationConfiguration.ID))
, CollapseWhitespace(builder.SQL));
}
finally
{
rootcontainer.RemoveChild(aggregate1);
container1.RemoveChild(aggregate2);
container1.RemoveChild(aggregate3);
aggregate2.HavingSQL = null;
aggregate2.SaveToDatabase();
aggregate1.HavingSQL = null;
aggregate1.SaveToDatabase();
}
}
