private AggregateConfiguration SetupAggregateWithPivot(DatabaseType type, ExtractionInformation[] extractionInformations,
Catalogue catalogue, out AggregateDimension axisDimension, out AggregateDimension pivotDimension)
{
var axisCol =
extractionInformations.Single(
e => e.GetRuntimeName().Equals("EventDate", StringComparison.CurrentCultureIgnoreCase));
var categoryCol =
extractionInformations.Single(
e => e.GetRuntimeName().Equals("Category", StringComparison.CurrentCultureIgnoreCase));
var configuration = new AggregateConfiguration(CatalogueRepository, catalogue, "GroupBy_Category");
axisDimension = new AggregateDimension(CatalogueRepository, axisCol, configuration);
pivotDimension = new AggregateDimension(CatalogueRepository, categoryCol, configuration);
var axis = new AggregateContinuousDateAxis(CatalogueRepository, axisDimension);
axis.StartDate = "'2000-01-01'";
axis.AxisIncrement = AxisIncrement.Year;
axis.SaveToDatabase();
return(configuration);
}
public void CreateEntities()
{
_c = new Catalogue(CatalogueRepository, "AggregateBuilderTests");
_cataItem1 = new CatalogueItem(CatalogueRepository, _c, "Col1");
_cataItem2 = new CatalogueItem(CatalogueRepository, _c, "Col2");
_ti = new TableInfo(CatalogueRepository, "T1");
_columnInfo1 = new ColumnInfo(CatalogueRepository, "Col1", "varchar(100)", _ti);
_columnInfo2 = new ColumnInfo(CatalogueRepository, "Col2", "date", _ti);
_ei1 = new ExtractionInformation(CatalogueRepository, _cataItem1, _columnInfo1, _columnInfo1.Name);
_ei2 = new ExtractionInformation(CatalogueRepository, _cataItem2, _columnInfo2, _columnInfo2.Name);
_configuration = new AggregateConfiguration(CatalogueRepository, _c, "MyConfig");
_dimension1 = new AggregateDimension(CatalogueRepository, _ei1, _configuration);
_dimension2 = new AggregateDimension(CatalogueRepository, _ei2, _configuration);
_dimension1.Order = 1;
_dimension1.SaveToDatabase();
_dimension2.Order = 2;
_dimension2.SaveToDatabase();
}
/// <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));
}
void olvSelectColumns_CellClick(object sender, CellClickEventArgs e)
{
if (e.Column == olvAddRemove)
{
var countColumn = e.Model as AggregateCountColumn;
var importableColumn = e.Model as ExtractionInformation;
var dimensionColumn = e.Model as AggregateDimension;
if (countColumn == null && importableColumn == null && dimensionColumn == null)
{
throw new Exception("Object in list view of type that wasn't IColumn, it was " + e.Model.GetType().Name);
}
//if it is an add
if (_availableColumns.Contains(e.Model))
{
//count column added
if (countColumn != null)
{
if (_options.GetCountColumnRequirement(_aggregate) == CountColumnRequirement.CannotHaveOne)
{
WideMessageBox.Show("Cohort Sets cannot have count columns", "A count column is a SELECT column with an aggregate function (count(*), sum(x) etc). The SELECT component for cohort identification must be the patient id column only.");
}
else
{
Save(countColumn);
}
}
//regular column added
if (importableColumn != null)
{
var dimension = new AggregateDimension(Activator.RepositoryLocator.CatalogueRepository, importableColumn, _aggregate);
_availableColumns.Remove(importableColumn);
_includedColumns.Add(dimension);
olvSelectColumns.RemoveObject(importableColumn);
olvSelectColumns.AddObject(dimension);
olvSelectColumns.EnsureModelVisible(dimension);
Save(dimension);
//object doesn't exist, that might cause problems
return;
}
}
else
{
//it is a removal
//user is trying to remove count column
if (countColumn != null)
{
if (_options.GetCountColumnRequirement(_aggregate) == CountColumnRequirement.MustHaveOne)
{
return; //leave it checked - removal is forbidden
}
_aggregate.CountSQL = "";
_aggregate.SaveToDatabase();
_includedColumns.Remove(countColumn);
_availableColumns.Add(countColumn);
olvSelectColumns.RemoveObject(countColumn);
olvSelectColumns.AddObject(countColumn);
olvSelectColumns.EnsureModelVisible(countColumn);
Activator.RefreshBus.Publish(this, new RefreshObjectEventArgs(_aggregate));
}
//user is trying to remove a dimension
if (dimensionColumn != null)
{
dimensionColumn.DeleteInDatabase();
//get the master it was based on
var extractionInformation = dimensionColumn.ExtractionInformation;
try
{
dimensionColumn.DeleteInDatabase();
}
catch (Exception ex)
{
//couldn't delete it so don't update the UI just tell the user why
ExceptionViewer.Show(ex);
return;
}
//remove it from the inclusion list
_includedColumns.Remove(dimensionColumn);
olvSelectColumns.RemoveObject(dimensionColumn);
//add the master importable version it was based on into available columns again
_availableColumns.Add(extractionInformation);
olvSelectColumns.AddObject(extractionInformation);
Save(extractionInformation);
}
}
}
}
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);
}
/// <summary>
/// Populates _sql (SQL property) and resolves all parameters, filters containers etc. Basically Finalizes this query builder
/// </summary>
public void RegenerateSQL()
{
SelectColumns.Sort();
//things we discover below, set them all to default values again
_pivotDimension = null;
_axisAppliesToDimension = null;
_axis = null;
_isCohortIdentificationAggregate = false;
ParameterManager.ClearNonGlobals();
if (_queryLevelParameterProvider != null)
{
ParameterManager.AddParametersFor(_queryLevelParameterProvider, ParameterLevel.QueryLevel);
}
TableInfo primary;
TablesUsedInQuery = SqlQueryBuilderHelper.GetTablesUsedInQuery(this, out primary, _forceJoinsToTheseTables);
var tables = _forceJoinsToTheseTables != null
? TablesUsedInQuery.Union(_forceJoinsToTheseTables).ToList()
: TablesUsedInQuery;
if (!tables.Any())
{
throw new QueryBuildingException("No tables could be identified for the query. Try adding a column or a force join");
}
//get the database language syntax based on the tables used in the query
_syntaxHelper = SqlQueryBuilderHelper.GetSyntaxHelper(tables);
//tell the count column what language it is
if (_countColumn != null)
{
_isCohortIdentificationAggregate = _aggregateConfigurationIfAny != null && _aggregateConfigurationIfAny.IsCohortIdentificationAggregate;
//if it is not a cic aggregate then make sure it has an alias e.g. count(*) AS MyCount. cic aggregates take extreme liberties with this field like passing in 'distinct chi' and '*' and other wacky stuff that is so not cool
_countColumn.SetQuerySyntaxHelper(_syntaxHelper, !_isCohortIdentificationAggregate);
}
IAggregateHelper aggregateHelper = _syntaxHelper.AggregateHelper;
if (_pivotID != -1)
{
try
{
_pivotDimension = SelectColumns.Single(
qtc => qtc.IColumn is AggregateDimension
&&
((AggregateDimension)qtc.IColumn).ID == _pivotID);
}
catch (Exception e)
{
throw new QueryBuildingException("Problem occurred when trying to find PivotDimension ID " + _pivotID + " in SelectColumns list", e);
}
}
foreach (AggregateDimension dimension in SelectColumns.Select(c => c.IColumn).Where(e => e is AggregateDimension))
{
var availableAxis = dimension.AggregateContinuousDateAxis;
if (availableAxis != null)
{
if (_axis != null)
{
throw new QueryBuildingException(
"Multiple dimensions have an AggregateContinuousDateAxis within the same configuration (Dimensions " + _axisAppliesToDimension.GetRuntimeName() + " and " + dimension.GetRuntimeName() + ")");
}
else
{
_axis = availableAxis;
_axisAppliesToDimension = dimension;
}
}
}
if (_pivotDimension != null)
{
if (_pivotDimension.IColumn == _axisAppliesToDimension)
{
throw new QueryBuildingException("Column " + _pivotDimension.IColumn + " is both a PIVOT and has an AXIS configured on it, you cannot have both.");
}
}
//work out all the filters
Filters = SqlQueryBuilderHelper.GetAllFiltersUsedInContainerTreeRecursively(RootFilterContainer);
//tell the manager about them
ParameterManager.AddParametersFor(Filters);
if (AggregateTopX != null)
{
SqlQueryBuilderHelper.HandleTopX(this, _syntaxHelper, AggregateTopX.TopX);
}
else
{
SqlQueryBuilderHelper.ClearTopX(this);
}
//if user wants to force join to some other tables that don't appear in the SELECT list, who are we to stop him!
if (_forceJoinsToTheseTables != null)
{
foreach (TableInfo t in _forceJoinsToTheseTables)
{
if (!TablesUsedInQuery.Contains(t))
{
TablesUsedInQuery.Add(t);
ParameterManager.AddParametersFor(t);
}
//if user has force joined to a primary extraction table
if (t.IsPrimaryExtractionTable)
{
if (primary == null) //we don't currently know the primary (i.e. none of the SELECT columns were from primary tables so use this table as primary)
{
primary = t;
}
else if (primary.ID == t.ID) //we know the primary already but it is the same table so thats fine
{
continue;
}
else
{
//this isn't fine
throw new QueryBuildingException("You chose to FORCE a join to table " + t + " which is marked IsPrimaryExtractionTable but you have also selected a column called " + primary + " which is also an IsPrimaryExtractionTable (cannot have 2 different primary extraction tables)");
}
}
}
}
this.PrimaryExtractionTable = primary;
SqlQueryBuilderHelper.FindLookups(this);
JoinsUsedInQuery = SqlQueryBuilderHelper.FindRequiredJoins(this);
var queryLines = new List <CustomLine>();
_sql = "";
ValidateDimensions();
//assuming we were not told to ignore the writing out of parameters!
if (!DoNotWriteOutParameters)
{
foreach (ISqlParameter parameter in ParameterManager.GetFinalResolvedParametersList())
{
queryLines.Add(new CustomLine(QueryBuilder.GetParameterDeclarationSQL(parameter), QueryComponent.VariableDeclaration));
}
}
CompileCustomLinesInStageAndAddToList(QueryComponent.VariableDeclaration, queryLines);
//put the name in as SQL comments followed by the SQL e.g. the name of an AggregateConfiguration or whatever
GetSelectSQL(queryLines);
queryLines.Add(new CustomLine(SqlQueryBuilderHelper.GetFROMSQL(this), QueryComponent.FROM));
CompileCustomLinesInStageAndAddToList(QueryComponent.JoinInfoJoin, queryLines);
queryLines.Add(new CustomLine(SqlQueryBuilderHelper.GetWHERESQL(this), QueryComponent.WHERE));
CompileCustomLinesInStageAndAddToList(QueryComponent.WHERE, queryLines);
GetGroupBySQL(queryLines, aggregateHelper);
queryLines = queryLines.Where(l => !string.IsNullOrWhiteSpace(l.Text)).ToList();
_sql = aggregateHelper.BuildAggregate(queryLines, _axis);
}
/// <summary>
/// Returns the SQL you need to include in your nested query (in UNION / EXCEPT / INTERSECT). This does not include parameter declarations (which
/// would appear at the very top) and includes rename operations dependant on what has been written out before by (tracked by <see cref="ParameterManager"/>).
///
/// <para>Use <paramref name="args"/> for the original un renamed / including parameter declarations e.g. to test for cache hits</para>
/// </summary>
/// <param name="aggregate"></param>
/// <param name="args"></param>
/// <returns></returns>
public CohortQueryBuilderDependencySql GetSQLForAggregate(AggregateConfiguration aggregate, QueryBuilderArgs args)
{
bool isJoinAggregate = aggregate.IsCohortIdentificationAggregate;
//make sure it is a valid configuration
string reason;
if (!aggregate.IsAcceptableAsCohortGenerationSource(out reason))
{
throw new QueryBuildingException("Cannot generate a cohort using AggregateConfiguration " + aggregate + " because:" + reason);
}
//get the extraction identifier (method IsAcceptableAsCohortGenerationSource will ensure this linq returns 1 so no need to check again)
AggregateDimension extractionIdentifier = aggregate.AggregateDimensions.Single(d => d.IsExtractionIdentifier);
//create a builder but do it manually, we care about group bys etc or count(*) even
AggregateBuilder builder;
//we are getting SQL for a cohort identification aggregate without a HAVING/count statement so it is actually just 'select patientIdentifier from tableX'
if (string.IsNullOrWhiteSpace(aggregate.HavingSQL) && string.IsNullOrWhiteSpace(aggregate.CountSQL))
{
//select list is the extraction identifier
string selectList;
if (!isJoinAggregate)
{
selectList = extractionIdentifier.SelectSQL + (extractionIdentifier.Alias != null ? " " + extractionIdentifier.Alias: "");
}
else
{
//unless we are also including other columns because this is a patient index joinable inception query
selectList = string.Join("," + Environment.NewLine,
aggregate.AggregateDimensions.Select(e => e.SelectSQL + (e.Alias != null ? " " + e.Alias : ""))); //joinable patient index tables have patientIdentifier + 1 or more other columns
}
if (args.OverrideSelectList != null)
{
selectList = args.OverrideSelectList;
}
string limitationSQL = args?.OverrideLimitationSQL ?? "distinct";
//select list is either [chi] or [chi],[mycolumn],[myexcitingcol] (in the case of a patient index table)
builder = new AggregateBuilder(limitationSQL, selectList, aggregate, aggregate.ForcedJoins);
//false makes it skip them in the SQL it generates (it uses them only in determining JOIN requirements etc but since we passed in the select SQL explicitly it should be the equivellent of telling the query builder to generate a regular select
if (!isJoinAggregate)
{
builder.AddColumn(extractionIdentifier, false);
}
else
{
builder.AddColumnRange(aggregate.AggregateDimensions.ToArray(), false);
}
}
else
{
if (args.OverrideSelectList != null)
{
throw new NotSupportedException("Cannot override Select list on aggregates that have HAVING / Count SQL configured in them");
}
builder = new AggregateBuilder("distinct", aggregate.CountSQL, aggregate, aggregate.ForcedJoins);
//add the extraction information and do group by it
if (!isJoinAggregate)
{
builder.AddColumn(extractionIdentifier, true);
}
else
{
builder.AddColumnRange(aggregate.AggregateDimensions.ToArray(), true);//it's a joinable inception query (See JoinableCohortAggregateConfiguration) - these are allowed additional columns
}
builder.DoNotWriteOutOrderBy = true;
}
if (args.TopX != -1)
{
builder.AggregateTopX = new SpontaneouslyInventedAggregateTopX(new MemoryRepository(), args.TopX, AggregateTopXOrderByDirection.Descending, null);
}
//make sure builder has globals
foreach (var global in args.Globals)
{
builder.ParameterManager.AddGlobalParameter(global);
}
//Add the inception join
if (args.JoinIfAny != null)
{
AddJoinToBuilder(aggregate, extractionIdentifier, builder, args);
}
//set the where container
builder.RootFilterContainer = aggregate.RootFilterContainer;
//we will be harnessing the parameters via ImportAndElevate so do not add them to the SQL directly
builder.DoNotWriteOutParameters = true;
var builderSqlWithoutParameters = builder.SQL;
//get the SQL from the builder (for the current configuration) - without parameters
string currentBlock = builderSqlWithoutParameters;
var toReturn = new CohortQueryBuilderDependencySql(currentBlock, builder.ParameterManager);
if (args.JoinSql != null)
{
toReturn.ParametersUsed.MergeWithoutRename(args.JoinSql.ParametersUsed);
}
//we need to generate the full SQL with parameters (and no rename operations) so we can do cache hit tests
//renaming is deferred to later
return(toReturn);
}
public override void Execute()
{
base.Execute();
if (string.IsNullOrWhiteSpace(column) && !askAtRuntime)
{
aggregate.GetAxisIfAny()?.DeleteInDatabase();
}
else
{
if (aggregate.GetAxisIfAny() != null)
{
throw new Exception($"Aggregate {aggregate} already has an axis");
}
var opts = new AggregateBuilderOptionsFactory().Create(aggregate);
AggregateDimension match = null;
if (askAtRuntime)
{
var possible = aggregate.AggregateDimensions.Where(d => d.IsDate()).ToArray();
if (!possible.Any())
{
throw new Exception($"There are no AggregateDimensions in {aggregate} that can be used as an axis (Dimensions must be Date Type)");
}
match = (AggregateDimension)BasicActivator.SelectOne("Choose axis dimension", possible);
if (match == null)
{
return;
}
}
else
{
match = aggregate.AggregateDimensions.FirstOrDefault(a => string.Equals(column, a.ToString()));
if (match == null)
{
throw new Exception($"Could not find AggregateDimension {column} in Aggregate {aggregate} so could not set it as an axis dimension. Try adding the column to the aggregate first");
}
}
if (!match.IsDate())
{
throw new Exception($"AggregateDimension {match} is not a Date so cannot set it as an axis for Aggregate {aggregate}");
}
var enable = opts.ShouldBeEnabled(AggregateEditorSection.AXIS, aggregate);
if (!enable)
{
throw new Exception($"Current state of Aggregate {aggregate} does not support having an axis Dimension");
}
var axis = new AggregateContinuousDateAxis(BasicActivator.RepositoryLocator.CatalogueRepository, match);
axis.SaveToDatabase();
}
Publish(aggregate);
}
public string GetSQLForAggregate(AggregateConfiguration aggregate, int tabDepth, bool isJoinAggregate = false, string overrideSelectList = null, string overrideLimitationSQL = null, int topX = -1)
{
string toReturn = "";
CountOfSubQueries++;
string tabs = "";
string tabplusOne = "";
if (tabDepth != -1)
{
GetTabs(tabDepth, out tabs, out tabplusOne);
}
//make sure it is a valid configuration
string reason;
if (!aggregate.IsAcceptableAsCohortGenerationSource(out reason))
{
throw new QueryBuildingException("Cannot generate a cohort using AggregateConfiguration " + aggregate + " because:" + reason);
}
//get the extraction identifier (method IsAcceptableAsCohortGenerationSource will ensure this linq returns 1 so no need to check again)
AggregateDimension extractionIdentifier = aggregate.AggregateDimensions.Single(d => d.IsExtractionIdentifier);
//create a builder but do it manually, we care about group bys etc or count(*) even
AggregateBuilder builder;
//we are getting SQL for a cohort identification aggregate without a HAVING/count statement so it is actually just 'select patientIdentifier from tableX'
if (string.IsNullOrWhiteSpace(aggregate.HavingSQL) && string.IsNullOrWhiteSpace(aggregate.CountSQL))
{
//select list is the extraction identifier
string selectList;
if (!isJoinAggregate)
{
selectList = extractionIdentifier.SelectSQL + (extractionIdentifier.Alias != null ? " " + extractionIdentifier.Alias: "");
}
else
{
//unless we are also including other columns because this is a patient index joinable inception query
selectList = string.Join("," + Environment.NewLine + tabs,
aggregate.AggregateDimensions.Select(e => e.SelectSQL + (e.Alias != null ? " " + e.Alias : ""))); //joinable patient index tables have patientIdentifier + 1 or more other columns
}
if (overrideSelectList != null)
{
selectList = overrideSelectList;
}
string limitationSQL = overrideLimitationSQL ?? "distinct";
//select list is either [chi] or [chi],[mycolumn],[myexcitingcol] (in the case of a patient index table)
builder = new AggregateBuilder(limitationSQL, selectList, aggregate, aggregate.ForcedJoins);
//false makes it skip them in the SQL it generates (it uses them only in determining JOIN requirements etc but since we passed in the select SQL explicitly it should be the equivellent of telling the query builder to generate a regular select
if (!isJoinAggregate)
{
builder.AddColumn(extractionIdentifier, false);
}
else
{
builder.AddColumnRange(aggregate.AggregateDimensions.ToArray(), false);
}
}
else
{
if (overrideSelectList != null)
{
throw new NotSupportedException("Cannot override Select list on aggregates that have HAVING / Count SQL configured in them");
}
builder = new AggregateBuilder("distinct", aggregate.CountSQL, aggregate, aggregate.ForcedJoins);
//add the extraction information and do group by it
if (!isJoinAggregate)
{
builder.AddColumn(extractionIdentifier, true);
}
else
{
builder.AddColumnRange(aggregate.AggregateDimensions.ToArray(), true);//it's a joinable inception query (See JoinableCohortAggregateConfiguration) - these are allowed additional columns
}
builder.DoNotWriteOutOrderBy = true;
}
if (topX != -1)
{
builder.AggregateTopX = new SpontaneouslyInventedAggregateTopX(new MemoryRepository(), topX, AggregateTopXOrderByDirection.Descending, null);
}
AddJoinablesToBuilder(builder, aggregate, tabDepth);
//set the where container
builder.RootFilterContainer = aggregate.RootFilterContainer;
string builderSqlVerbatimForCheckingAgainstCache = null;
if (CacheServer != null)
{
builderSqlVerbatimForCheckingAgainstCache = GetSqlForBuilderForCheckingAgainstCache(builder);
}
//we will be harnessing the parameters via ImportAndElevate so do not add them to the SQL directly
builder.DoNotWriteOutParameters = true;
var builderSqlWithoutParameters = builder.SQL;
//if we have caching
if (CacheServer != null)
{
CachedAggregateConfigurationResultsManager manager = new CachedAggregateConfigurationResultsManager(CacheServer);
var existingTable = manager.GetLatestResultsTable(aggregate, isJoinAggregate? AggregateOperation.JoinableInceptionQuery: AggregateOperation.IndexedExtractionIdentifierList, builderSqlVerbatimForCheckingAgainstCache);
//if we have the answer already
if (existingTable != null)
{
//reference the answer table
CountOfCachedSubQueries++;
toReturn += tabplusOne + CachedAggregateConfigurationResultsManager.CachingPrefix + aggregate.Name + @"*/" + Environment.NewLine;
toReturn += tabplusOne + "select * from " + existingTable.GetFullyQualifiedName() + Environment.NewLine;
return(toReturn);
}
//we do not have an uptodate answer available in the cache :(
}
//get the SQL from the builder (for the current configuration) - without parameters
string currentBlock = builderSqlWithoutParameters;
//import parameters unless caching was used
Dictionary <string, string> renameOperations;
ParameterManager.ImportAndElevateResolvedParametersFromSubquery(builder.ParameterManager, out renameOperations);
//rename in the SQL too!
foreach (KeyValuePair <string, string> kvp in renameOperations)
{
currentBlock = ParameterCreator.RenameParameterInSQL(currentBlock, kvp.Key, kvp.Value);
}
//tab in the current block
toReturn += tabplusOne + currentBlock.Replace("\r\n", "\r\n" + tabplusOne);
return(toReturn);
}
