public AggregateFilterContainerMenu(RDMPContextMenuStripArgs args, AggregateFilterContainer filterContainer)
: base(
new AggregateFilterFactory(args.ItemActivator.RepositoryLocator.CatalogueRepository),
args, filterContainer)
{
Add(new ExecuteCommandDisableOrEnable(_activator, filterContainer));
}
private CohortIdentificationConfiguration CreateCohortIdentificationConfiguration(ExtractionFilter inclusionFilter1)
{
//Create the top level configuration object
var cic = new CohortIdentificationConfiguration(_repos.CatalogueRepository, "Tayside Lung Cancer Cohort");
//create a UNION container for Inclusion Criteria
var container = new CohortAggregateContainer(_repos.CatalogueRepository, SetOperation.UNION);
container.Name = "Inclusion Criteria";
container.SaveToDatabase();
cic.RootCohortAggregateContainer_ID = container.ID;
cic.SaveToDatabase();
//Create a new cohort set to the 'Inclusion Criteria' based on the filters Catalogue
var cata = inclusionFilter1.ExtractionInformation.CatalogueItem.Catalogue;
var ac = cic.CreateNewEmptyConfigurationForCatalogue(cata, (a, b) => { throw new Exception("Problem encountered with chi column(s)"); }, false);
container.AddChild(ac, 0);
//Add the filter to the WHERE logic of the cohort set
var whereContainer = new AggregateFilterContainer(_repos.CatalogueRepository, FilterContainerOperation.OR);
ac.Name = "People with " + inclusionFilter1.Name;
ac.RootFilterContainer_ID = whereContainer.ID;
cic.EnsureNamingConvention(ac); //this will put cicx at the front and cause implicit SaveToDatabase
FilterImporter filterImporter = new FilterImporter(new AggregateFilterFactory(_repos.CatalogueRepository), null);
var cloneFilter = filterImporter.ImportFilter(inclusionFilter1, null);
whereContainer.AddChild(cloneFilter);
return(cic);
}
/// <summary>
/// Creates a new patient index table based on Biochemistry which selects the distinct dates of "NA" test results
/// for every patient
/// </summary>
/// <param name="db"></param>
/// <param name="people"></param>
/// <param name="r"></param>
/// <param name="cic"></param>
/// <returns></returns>
private JoinableCohortAggregateConfiguration SetupPatientIndexTable(DiscoveredDatabase db, PersonCollection people, Random r, CohortIdentificationConfiguration cic)
{
var syntax = db.Server.GetQuerySyntaxHelper();
var tbl = CreateDataset <Biochemistry>(db, people, 10000, r);
var cata = Import(tbl, out _, out _, out _, out ExtractionInformation[] eis);
var chi = eis.Single(ei => ei.GetRuntimeName().Equals("chi", StringComparison.CurrentCultureIgnoreCase));
var code = eis.Single(ei => ei.GetRuntimeName().Equals("TestCode", StringComparison.CurrentCultureIgnoreCase));
var date = eis.Single(ei => ei.GetRuntimeName().Equals("SampleDate", StringComparison.CurrentCultureIgnoreCase));
chi.IsExtractionIdentifier = true;
chi.SaveToDatabase();
var ac = new AggregateConfiguration(CatalogueRepository, cata, "NA by date");
ac.AddDimension(chi);
ac.AddDimension(code);
ac.AddDimension(date);
ac.CountSQL = null;
cic.EnsureNamingConvention(ac);
var and = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
var filter = new AggregateFilter(CatalogueRepository, "TestCode is NA", and);
filter.WhereSQL = syntax.EnsureWrapped("TestCode") + " = 'NA'";
filter.SaveToDatabase();
ac.RootFilterContainer_ID = and.ID;
ac.SaveToDatabase();
return(new JoinableCohortAggregateConfiguration(CatalogueRepository, cic, ac));
}
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();
}
private void AddFilter(DiscoveredDatabase db, string filterName, string whereSqlFirstHalf, AggregateConfiguration ac, bool useParameter, string paramName, string paramValue)
{
var container = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND);
ac.RootFilterContainer_ID = container.ID;
ac.SaveToDatabase();
//create a filter
var filter = new AggregateFilter(CatalogueRepository, filterName, container);
if (useParameter)
{
filter.WhereSQL = whereSqlFirstHalf + paramName;
var parameterSql = db.Server.GetQuerySyntaxHelper()
.GetParameterDeclaration(paramName, new DatabaseTypeRequest(typeof(DateTime)));
var parameter = filter.GetFilterFactory().CreateNewParameter(filter, parameterSql);
parameter.Value = paramValue;
parameter.SaveToDatabase();
}
else
{
filter.WhereSQL = whereSqlFirstHalf + paramValue;
}
filter.SaveToDatabase();
}
private void AddFilterContainer()
{
var newContainer = new AggregateFilterContainer(RepositoryLocator.CatalogueRepository, FilterContainerOperation.AND);
_aggregate.RootFilterContainer_ID = newContainer.ID;
_aggregate.SaveToDatabase();
Publish(_aggregate);
}
public void CreateAFilter()
{
aggregate1.RootFilterContainer_ID = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND).ID;
aggregate1.SaveToDatabase();
_chiExtractionInformation = aggregate1.AggregateDimensions.Single().ExtractionInformation;
_container = aggregate1.RootFilterContainer;
_filter = new AggregateFilter(CatalogueRepository, "folk", _container);
}
protected override void SetUp()
{
base.SetUp();
aggregate1.RootFilterContainer_ID = new AggregateFilterContainer(CatalogueRepository, FilterContainerOperation.AND).ID;
aggregate1.SaveToDatabase();
_chiExtractionInformation = aggregate1.AggregateDimensions.Single().ExtractionInformation;
_container = (AggregateFilterContainer)aggregate1.RootFilterContainer;
_filter = new AggregateFilter(CatalogueRepository, "folk", _container);
}
public void CreateCohortSet(CohortIdentificationConfiguration cic, CohortAggregateContainer targetContainer, int order)
{
var cata = cbxCatalogues.SelectedItem as Catalogue;
if (cata == null)
{
throw new Exception("Catalogue has not been picked!");
}
var cataCommand = new CatalogueCombineable(cata);
//use this one
cataCommand.ResolveMultipleExtractionIdentifiers = (s, e) => cbxColumns.SelectedItem as ExtractionInformation;
var cmd = new ExecuteCommandAddCatalogueToCohortIdentificationSetContainer(_activator, cataCommand, targetContainer);
cmd.SkipMandatoryFilterCreation = true;
cmd.Execute();
var aggregate = cmd.AggregateCreatedIfAny;
var filterOp = (FilterContainerOperation)ddAndOr.SelectedItem;
IContainer filterContainer;
if (aggregate.RootFilterContainer_ID != null)
{
//this is the case if there are mandatory filters in the dataset
filterContainer = aggregate.RootFilterContainer;
filterContainer.Operation = filterOp;
filterContainer.SaveToDatabase();
}
else
{
filterContainer = new AggregateFilterContainer(_activator.RepositoryLocator.CatalogueRepository, filterOp);
}
aggregate.Order = order;
aggregate.RootFilterContainer_ID = filterContainer.ID;
aggregate.SaveToDatabase();
List <IFilter> filtersAddedSoFar = new List <IFilter>();
foreach (var ui in _filterUIs)
{
var f = ui.CreateFilter(new AggregateFilterFactory(_activator.RepositoryLocator.CatalogueRepository), filterContainer, filtersAddedSoFar.ToArray());
filtersAddedSoFar.Add(f);
}
}
/// <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);
}
private IFilter CreateFilter(AggregateConfiguration graph, string name, string whereSql)
{
AggregateFilterContainer container;
if (graph.RootFilterContainer_ID == null)
{
container = new AggregateFilterContainer(_repos.CatalogueRepository, FilterContainerOperation.AND);
graph.RootFilterContainer_ID = container.ID;
graph.SaveToDatabase();
}
else
{
container = graph.RootFilterContainer;
}
var filter = new AggregateFilter(_repos.CatalogueRepository, name, container);
filter.WhereSQL = whereSql;
filter.SaveToDatabase();
return(filter);
}
private void ImportMandatoryFilters(Catalogue catalogue, AggregateConfiguration configuration, ISqlParameter[] globalParameters)
{
var filterImporter = new FilterImporter(new AggregateFilterFactory((ICatalogueRepository)catalogue.Repository), globalParameters);
//Find any Mandatory Filters
var mandatoryFilters = catalogue.GetAllMandatoryFilters();
List <AggregateFilter> createdSoFar = new List <AggregateFilter>();
if (mandatoryFilters.Any())
{
var container = new AggregateFilterContainer((ICatalogueRepository)Repository, FilterContainerOperation.AND);
configuration.RootFilterContainer_ID = container.ID;
configuration.SaveToDatabase();
foreach (ExtractionFilter filter in mandatoryFilters)
{
var newFilter = (AggregateFilter)filterImporter.ImportFilter(filter, createdSoFar.ToArray());
container.AddChild(newFilter);
createdSoFar.Add(newFilter);
}
}
}
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();
}
}
/// <summary>
/// Creates a minimum viable object of Type T. This includes the object and any dependencies e.g. a
/// <see cref="ColumnInfo"/> cannot exist without a <see cref="TableInfo"/>.
/// </summary>
/// <typeparam name="T">Type of object you want to create</typeparam>
/// <returns></returns>
/// <exception cref="NotSupportedException">If there is not yet an implementation for the given T. Feel free to write one.</exception>
protected T WhenIHaveA <T>() where T : DatabaseEntity
{
if (typeof(T) == typeof(Catalogue))
{
return((T)(object)Save(new Catalogue(Repository, "Mycata")));
}
if (typeof(T) == typeof(ExtendedProperty))
{
return((T)(object)new ExtendedProperty(Repository, Save(new Catalogue(Repository, "Mycata")), "TestProp", 0));
}
if (typeof(T) == typeof(CatalogueItem))
{
var cata = new Catalogue(Repository, "Mycata");
return((T)(object)new CatalogueItem(Repository, cata, "MyCataItem"));
}
if (typeof(T) == typeof(ExtractionInformation))
{
var col = WhenIHaveA <ColumnInfo>();
var cata = new Catalogue(Repository, "Mycata");
var ci = new CatalogueItem(Repository, cata, "MyCataItem");
var ei = new ExtractionInformation(Repository, ci, col, "MyCataItem");
return((T)(object)Save(ei));
}
if (typeof(T) == typeof(TableInfo))
{
var table = new TableInfo(Repository, "My_Table")
{
DatabaseType = DatabaseType.MicrosoftSQLServer
};
return((T)(object)table);
}
if (typeof(T) == typeof(ColumnInfo))
{
var ti = WhenIHaveA <TableInfo>();
var col = new ColumnInfo(Repository, "My_Col", "varchar(10)", ti);
return((T)(object)col);
}
if (typeof(T) == typeof(AggregateConfiguration))
{
ExtractionInformation dateEi;
ExtractionInformation otherEi;
return((T)(object)WhenIHaveA <AggregateConfiguration>(out dateEi, out otherEi));
}
if (typeof(T) == typeof(ExternalDatabaseServer))
{
return((T)(object)Save(new ExternalDatabaseServer(Repository, "My Server", null)));
}
if (typeof(T) == typeof(ANOTable))
{
ExternalDatabaseServer server;
return((T)(object)WhenIHaveA <ANOTable>(out server));
}
if (typeof(T) == typeof(LoadMetadata))
{
//creates the table, column, catalogue, catalogue item and extraction information
var ei = WhenIHaveA <ExtractionInformation>();
var cata = ei.CatalogueItem.Catalogue;
var ti = ei.ColumnInfo.TableInfo;
ti.Server = "localhost";
ti.Database = "mydb";
ti.SaveToDatabase();
var lmd = new LoadMetadata(Repository, "MyLoad");
cata.LoadMetadata_ID = lmd.ID;
cata.SaveToDatabase();
return((T)(object)lmd);
}
if (typeof(T) == typeof(AggregateTopX))
{
var agg = WhenIHaveA <AggregateConfiguration>();
return((T)(object)new AggregateTopX(Repository, agg, 10));
}
if (typeof(T) == typeof(ConnectionStringKeyword))
{
return((T)(object)new ConnectionStringKeyword(Repository, DatabaseType.MicrosoftSQLServer, "MultipleActiveResultSets", "true"));
}
if (typeof(T) == typeof(DashboardLayout))
{
return((T)(object)new DashboardLayout(Repository, "My Layout"));
}
if (typeof(T) == typeof(DashboardControl))
{
var layout = WhenIHaveA <DashboardLayout>();
return((T)(object)Save(new DashboardControl(Repository, layout, typeof(int), 0, 0, 100, 100, "")
{
ControlType = "GoodBadCataloguePieChart"
}));
}
if (typeof(T) == typeof(DashboardObjectUse))
{
var layout = WhenIHaveA <DashboardLayout>();
var control = Save(new DashboardControl(Repository, layout, typeof(int), 0, 0, 100, 100, "")
{
ControlType = "GoodBadCataloguePieChart"
});
var use = new DashboardObjectUse(Repository, control, WhenIHaveA <Catalogue>());
return((T)(object)use);
}
if (typeof(T) == typeof(ExtractionFilter))
{
var ei = WhenIHaveA <ExtractionInformation>();
return((T)(object)new ExtractionFilter(Repository, "My Filter", ei));
}
if (typeof(T) == typeof(ExtractionFilterParameter))
{
var filter = WhenIHaveA <ExtractionFilter>();
filter.WhereSQL = "@myParam = 'T'";
return((T)(object)new ExtractionFilterParameter(Repository, "DECLARE @myParam varchar(10)", filter));
}
if (typeof(T) == typeof(ExtractionFilterParameterSetValue))
{
var parameter = WhenIHaveA <ExtractionFilterParameter>();
var set = new ExtractionFilterParameterSet(Repository, parameter.ExtractionFilter, "Parameter Set");
return((T)(object)new ExtractionFilterParameterSetValue(Repository, set, parameter));
}
if (typeof(T) == typeof(ExtractionFilterParameterSet))
{
return((T)(object)WhenIHaveA <ExtractionFilterParameterSetValue>().ExtractionFilterParameterSet);
}
if (typeof(T) == typeof(Favourite))
{
return((T)(object)new Favourite(Repository, WhenIHaveA <Catalogue>()));
}
if (typeof(T) == typeof(ObjectExport))
{
ShareManager sm;
return((T)(object)WhenIHaveA <ObjectExport>(out sm));
}
if (typeof(T) == typeof(ObjectImport))
{
ShareManager sm;
ObjectExport export = WhenIHaveA <ObjectExport>(out sm);
return((T)(object)sm.GetImportAs(export.SharingUID, WhenIHaveA <Catalogue>()));
}
if (typeof(T) == typeof(WindowLayout))
{
return((T)(object)new WindowLayout(Repository, "My window arrangement", "<html><body>ignore this</body></html>"));
}
if (typeof(T) == typeof(RemoteRDMP))
{
return((T)(object)new RemoteRDMP(Repository));
}
if (typeof(T) == typeof(CohortIdentificationConfiguration))
{
return((T)(object)new CohortIdentificationConfiguration(Repository, "My cic"));
}
if (typeof(T) == typeof(JoinableCohortAggregateConfiguration))
{
var config = WhenIHaveCohortAggregateConfiguration("PatientIndexTable");
var cic = WhenIHaveA <CohortIdentificationConfiguration>();
cic.EnsureNamingConvention(config);
return((T)(object)new JoinableCohortAggregateConfiguration(Repository, cic, config));
}
if (typeof(T) == typeof(JoinableCohortAggregateConfigurationUse))
{
var joinable = WhenIHaveA <JoinableCohortAggregateConfiguration>();
var config = WhenIHaveCohortAggregateConfiguration("Aggregate");
return((T)(object)joinable.AddUser(config));
}
if (typeof(T) == typeof(Rdmp.Core.Curation.Data.Plugin))
{
return((T)(object)new Rdmp.Core.Curation.Data.Plugin(Repository, new FileInfo("bob.nupkg"), new Version(1, 1, 1), new Version(1, 1, 1)));
}
if (typeof(T) == typeof(LoadModuleAssembly))
{
var dll = Path.Combine(TestContext.CurrentContext.TestDirectory, "a.nupkg");
File.WriteAllBytes(dll, new byte[] { 0x11 });
return((T)(object)new LoadModuleAssembly(Repository, new FileInfo(dll), WhenIHaveA <Rdmp.Core.Curation.Data.Plugin>()));
}
if (typeof(T) == typeof(AggregateContinuousDateAxis))
{
ExtractionInformation dateEi;
ExtractionInformation otherEi;
var config = WhenIHaveA <AggregateConfiguration>(out dateEi, out otherEi);
//remove the other Ei
config.AggregateDimensions[0].DeleteInDatabase();
//add the date one
var dim = new AggregateDimension(Repository, dateEi, config);
return((T)(object)new AggregateContinuousDateAxis(Repository, dim));
}
if (typeof(T) == typeof(AggregateDimension))
{
return((T)(object)WhenIHaveA <AggregateConfiguration>().AggregateDimensions[0]);
}
if (typeof(T) == typeof(AggregateFilterContainer))
{
var config = WhenIHaveA <AggregateConfiguration>();
var container = new AggregateFilterContainer(Repository, FilterContainerOperation.AND);
config.RootFilterContainer_ID = container.ID;
config.SaveToDatabase();
return((T)(object)container);
}
if (typeof(T) == typeof(AggregateFilter))
{
var container = WhenIHaveA <AggregateFilterContainer>();
return((T)(object)new AggregateFilter(Repository, "My Filter", container));
}
if (typeof(T) == typeof(AggregateFilterParameter))
{
var filter = WhenIHaveA <AggregateFilter>();
filter.WhereSQL = "@MyP = 'mnnn apples'";
filter.SaveToDatabase();
return((T)filter.GetFilterFactory().CreateNewParameter(filter, "DECLARE @MyP as varchar(10)"));
}
if (typeof(T) == typeof(LoadProgress))
{
return((T)(object)new LoadProgress(Repository, WhenIHaveA <LoadMetadata>()));
}
if (typeof(T) == typeof(CacheProgress))
{
return((T)(object)new CacheProgress(Repository, WhenIHaveA <LoadProgress>()));
}
if (typeof(T) == typeof(CacheFetchFailure))
{
return((T)(object)new CacheFetchFailure(Repository, WhenIHaveA <CacheProgress>(), DateTime.Now.Subtract(new TimeSpan(1, 0, 0, 0)), DateTime.Now, new Exception("It didn't work")));
}
if (typeof(T) == typeof(CohortAggregateContainer))
{
var cic = WhenIHaveA <CohortIdentificationConfiguration>();
cic.CreateRootContainerIfNotExists();
return((T)(object)cic.RootCohortAggregateContainer);
}
if (typeof(T) == typeof(AnyTableSqlParameter))
{
var cic = WhenIHaveA <CohortIdentificationConfiguration>();
return((T)(object)new AnyTableSqlParameter(Repository, cic, "DECLARE @myGlobal as varchar(10)"));
}
if (typeof(T) == typeof(DataAccessCredentials))
{
return((T)(object)new DataAccessCredentials(Repository, "My credentials"));
}
if (typeof(T) == typeof(GovernancePeriod))
{
return((T)(object)new GovernancePeriod(Repository));
}
if (typeof(T) == typeof(GovernanceDocument))
{
var fi = new FileInfo(Path.Combine(TestContext.CurrentContext.TestDirectory, "myfile.txt"));
return((T)(object)new GovernanceDocument(Repository, WhenIHaveA <GovernancePeriod>(), fi));
}
if (typeof(T) == typeof(PermissionWindow))
{
return((T)(object)new PermissionWindow(Repository));
}
if (typeof(T) == typeof(JoinInfo))
{
ColumnInfo col1;
ColumnInfo col2;
ColumnInfo col3;
WhenIHaveTwoTables(out col1, out col2, out col3);
return((T)(object)new JoinInfo(Repository, col1, col2, ExtractionJoinType.Left, null));
}
if (typeof(T) == typeof(Lookup))
{
ColumnInfo col1;
ColumnInfo col2;
ColumnInfo col3;
WhenIHaveTwoTables(out col1, out col2, out col3);
return((T)(object)new Lookup(Repository, col3, col1, col2, ExtractionJoinType.Left, null));
}
if (typeof(T) == typeof(LookupCompositeJoinInfo))
{
var lookup = WhenIHaveA <Lookup>();
var otherJoinFk = new ColumnInfo(Repository, "otherJoinKeyForeign", "int", lookup.ForeignKey.TableInfo);
var otherJoinPk = new ColumnInfo(Repository, "otherJoinKeyPrimary", "int", lookup.PrimaryKey.TableInfo);
return((T)(object)new LookupCompositeJoinInfo(Repository, lookup, otherJoinFk, otherJoinPk));
}
if (typeof(T) == typeof(Pipeline))
{
return((T)(object)new Pipeline(Repository, "My Pipeline"));
}
if (typeof(T) == typeof(PipelineComponent))
{
return((T)(object)new PipelineComponent(Repository, WhenIHaveA <Pipeline>(), typeof(ColumnForbidder), 0, "My Component"));
}
if (typeof(T) == typeof(PipelineComponentArgument))
{
var comp = WhenIHaveA <PipelineComponent>();
return((T)comp.CreateArgumentsForClassIfNotExists <ColumnForbidder>().First());
}
if (typeof(T) == typeof(PreLoadDiscardedColumn))
{
return((T)(object)new PreLoadDiscardedColumn(Repository, WhenIHaveA <TableInfo>(), "MyDiscardedColum"));
}
if (typeof(T) == typeof(ProcessTask))
{
return((T)(object)new ProcessTask(Repository, WhenIHaveA <LoadMetadata>(), LoadStage.AdjustRaw));
}
if (typeof(T) == typeof(ProcessTaskArgument))
{
return((T)(object)new ProcessTaskArgument(Repository, WhenIHaveA <ProcessTask>()));
}
if (typeof(T) == typeof(StandardRegex))
{
return((T)(object)new StandardRegex(Repository));
}
if (typeof(T) == typeof(SupportingSQLTable))
{
return((T)(object)new SupportingSQLTable(Repository, WhenIHaveA <Catalogue>(), "Some Handy Query"));
}
if (typeof(T) == typeof(TicketingSystemConfiguration))
{
return((T)(object)new TicketingSystemConfiguration(Repository, "My Ticketing System"));
}
if (typeof(T) == typeof(SupportingDocument))
{
return((T)(object)new SupportingDocument(Repository, WhenIHaveA <Catalogue>(), "HelpFile.docx"));
}
if (typeof(T) == typeof(Project))
{
return((T)(object)new Project(Repository, "My Project"));
}
if (typeof(T) == typeof(ExtractionConfiguration))
{
return((T)(object)new ExtractionConfiguration(Repository, WhenIHaveA <Project>()));
}
if (typeof(T) == typeof(ExtractableDataSet))
{
//To make an extractable dataset we need an extraction identifier (e.g. chi) that will be linked in the cohort
var ei = WhenIHaveA <ExtractionInformation>();
ei.IsExtractionIdentifier = true;
ei.SaveToDatabase();
//And we need another column too just for sanity sakes (in the same table)
var ci2 = new CatalogueItem(Repository, ei.CatalogueItem.Catalogue, "ci2");
var col2 = new ColumnInfo(Repository, "My_Col2", "varchar(10)", ei.ColumnInfo.TableInfo);
var ei2 = new ExtractionInformation(Repository, ci2, col2, col2.GetFullyQualifiedName());
return((T)(object)new ExtractableDataSet(Repository, ei.CatalogueItem.Catalogue));
}
if (typeof(T) == typeof(CumulativeExtractionResults))
{
return((T)(object)new CumulativeExtractionResults(Repository, WhenIHaveA <ExtractionConfiguration>(), WhenIHaveA <ExtractableDataSet>(), "SELECT * FROM Anywhere"));
}
if (typeof(T) == typeof(SelectedDataSets))
{
var eds = WhenIHaveA <ExtractableDataSet>();
var config = WhenIHaveA <ExtractionConfiguration>();
foreach (var ei in eds.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any))
{
var ec = new ExtractableColumn(Repository, eds, config, ei, ei.Order, ei.SelectSQL);
}
return((T)(object)new SelectedDataSets(Repository, config, eds, null));
}
if (typeof(T) == typeof(ReleaseLog))
{
var file = Path.Combine(TestContext.CurrentContext.TestDirectory, "myDataset.csv");
File.WriteAllText(file, "omg rows");
var sds = WhenIHaveA <SelectedDataSets>();
new CumulativeExtractionResults(Repository, sds.ExtractionConfiguration, sds.ExtractableDataSet, "SELECT * FROM ANYWHERE");
var potential = new FlatFileReleasePotential(new RepositoryProvider(Repository), sds);
return((T)(object)new ReleaseLog(Repository,
potential,
new ReleaseEnvironmentPotential(sds.ExtractionConfiguration),
false,
new DirectoryInfo(TestContext.CurrentContext.TestDirectory),
new FileInfo(file)));
}
if (typeof(T) == typeof(ExtractableDataSetPackage))
{
return((T)(object)new ExtractableDataSetPackage(Repository, "My Cool Package"));
}
if (typeof(T) == typeof(SupplementalExtractionResults))
{
return((T)(object)new SupplementalExtractionResults(Repository, WhenIHaveA <CumulativeExtractionResults>(), "Select * from Lookup", WhenIHaveA <SupportingSQLTable>()));
}
if (typeof(T) == typeof(SelectedDataSetsForcedJoin))
{
return((T)(object)new SelectedDataSetsForcedJoin(Repository, WhenIHaveA <SelectedDataSets>(), WhenIHaveA <TableInfo>()));
}
if (typeof(T) == typeof(ProjectCohortIdentificationConfigurationAssociation))
{
return((T)(object)new ProjectCohortIdentificationConfigurationAssociation(Repository, WhenIHaveA <Project>(), WhenIHaveA <CohortIdentificationConfiguration>()));
}
if (typeof(T) == typeof(ExternalCohortTable))
{
return(Save((T)(object)new ExternalCohortTable(Repository, "My cohorts", DatabaseType.MicrosoftSQLServer)
{
Database = "MyCohortsDb",
DefinitionTableForeignKeyField = "c_id",
PrivateIdentifierField = "priv",
ReleaseIdentifierField = "rel",
TableName = "Cohorts",
DefinitionTableName = "InventoryTable",
Server = "localhost\\sqlexpress"
}));
}
if (typeof(T) == typeof(ExtractableCohort))
{
throw new NotSupportedException("You should inherit from TestsRequiringACohort instead, cohorts have to exist to be constructed");
}
if (typeof(T) == typeof(GlobalExtractionFilterParameter))
{
return((T)(object)new GlobalExtractionFilterParameter(Repository, WhenIHaveA <ExtractionConfiguration>(), "DECLARE @ExtractionGlobal as varchar(100)"));
}
if (typeof(T) == typeof(ExtractableColumn))
{
var ei = WhenIHaveA <ExtractionInformation>();
var eds = new ExtractableDataSet(Repository, ei.CatalogueItem.Catalogue);
var config = WhenIHaveA <ExtractionConfiguration>();
config.AddDatasetToConfiguration(eds);
return((T)(object)config.GetAllExtractableColumnsFor(eds).Single());
}
if (typeof(T) == typeof(FilterContainer))
{
var sds = WhenIHaveA <SelectedDataSets>();
var container = new FilterContainer(Repository, FilterContainerOperation.AND);
sds.RootFilterContainer_ID = container.ID;
sds.SaveToDatabase();
return((T)(object)container);
}
if (typeof(T) == typeof(DeployedExtractionFilter))
{
var container = WhenIHaveA <FilterContainer>();
return((T)(object)new DeployedExtractionFilter(Repository, "Fish = 'haddock'", container));
}
if (typeof(T) == typeof(DeployedExtractionFilterParameter))
{
var filter = WhenIHaveA <DeployedExtractionFilter>();
filter.WhereSQL = "@had = 'enough'";
return((T)(object)filter.GetFilterFactory().CreateNewParameter(filter, "DECLARE @had as varchar(100)"));
}
if (typeof(T) == typeof(ExtractionProgress))
{
var cata = new Catalogue(Repository, "MyCata");
var cataItem = new CatalogueItem(Repository, cata, "MyCol");
var table = new TableInfo(Repository, "MyTable");
var col = new ColumnInfo(Repository, "mycol", "datetime", table);
var ei = new ExtractionInformation(Repository, cataItem, col, "mycol");
cata.TimeCoverage_ExtractionInformation_ID = ei.ID;
cata.SaveToDatabase();
var eds = new ExtractableDataSet(Repository, cata);
var project = new Project(Repository, "My Proj");
var config = new ExtractionConfiguration(Repository, project);
var sds = new SelectedDataSets(Repository, config, eds, null);
return((T)(object)new ExtractionProgress(Repository, sds));
}
throw new TestCaseNotWrittenYetException(typeof(T));
}
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);
}
}
private AggregateConfiguration CreateCloneOfAggregateConfigurationPrivate(AggregateConfiguration toClone, ChooseWhichExtractionIdentifierToUseFromManyHandler resolveMultipleExtractionIdentifiers)
{
var cataRepo = CatalogueRepository;
//two cases here either the import has a custom freaky CHI column (dimension) or it doesn't reference CHI at all if it is freaky we want to preserve it's freakyness
ExtractionInformation underlyingExtractionInformation;
IColumn extractionIdentifier = GetExtractionIdentifierFrom(toClone, out underlyingExtractionInformation, resolveMultipleExtractionIdentifiers);
//clone will not have axis or pivot or dimensions other than extraction identifier
var newConfiguration = toClone.ShallowClone();
//make it's name follow the naming convention e.g. cic_105_LINK103_MyAggregate
EnsureNamingConvention(newConfiguration);
//now clear it's pivot dimension, make it not extratcable and make it's countSQL basic/sane
newConfiguration.PivotOnDimensionID = null;
newConfiguration.IsExtractable = false;
newConfiguration.CountSQL = null;//clear the count sql
//clone parameters
foreach (AnyTableSqlParameter toCloneParameter in toClone.Parameters)
{
var newParam = new AnyTableSqlParameter((ICatalogueRepository)newConfiguration.Repository, newConfiguration, toCloneParameter.ParameterSQL);
newParam.Value = toCloneParameter.Value;
newParam.Comment = toCloneParameter.Comment;
newParam.SaveToDatabase();
}
//now clone it's AggregateForcedJoins
foreach (var t in cataRepo.AggregateForcedJoinManager.GetAllForcedJoinsFor(toClone))
{
cataRepo.AggregateForcedJoinManager.CreateLinkBetween(newConfiguration, t);
}
//now give it 1 dimension which is the only IsExtractionIdentifier column
var newDimension = new AggregateDimension(cataRepo, underlyingExtractionInformation, newConfiguration);
//the thing we were cloning had a freaky CHI column (probably had a collate or something involved in it or a masterchi)
if (extractionIdentifier is AggregateDimension)
{
//preserve it's freakyness
newDimension.Alias = extractionIdentifier.Alias;
newDimension.SelectSQL = extractionIdentifier.SelectSQL;
newDimension.Order = extractionIdentifier.Order;
newDimension.SaveToDatabase();
}
//now rewire all it's filters
if (toClone.RootFilterContainer_ID != null) //if it has any filters
{
//get the tree
AggregateFilterContainer oldRootContainer = toClone.RootFilterContainer;
//clone the tree
var newRootContainer = oldRootContainer.DeepCloneEntireTreeRecursivelyIncludingFilters();
newConfiguration.RootFilterContainer_ID = newRootContainer.ID;
}
newConfiguration.SaveToDatabase();
return(newConfiguration);
}
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();
}
}
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();
}
}
