private void GetInsertData(DiscoveredServer server, DiscoveredDatabase database, ICheckNotifier checkNotifier)
{
var memoryRepository = new MemoryCatalogueRepository();
var sytnaxHelper = server.GetQuerySyntaxHelper();
string tableName = _tableInfo.Name;
string archiveTableName = sytnaxHelper.EnsureFullyQualified(database.GetRuntimeName(), _tableInfo.Schema, _tableInfo.GetRuntimeName() + "_Archive");
var whereStatement = "";
foreach (ColumnInfo pk in _pks)
{
whereStatement += string.Format("{0}.{1} = {2}.{1} AND ", tableName, pk.GetRuntimeName(), archiveTableName);
}
var qb = new QueryBuilder(null, null, new[] { _tableInfo });
qb.TopX = _batchSize;
qb.AddColumnRange(_tableInfo.ColumnInfos.Select(c => new ColumnInfoToIColumn(memoryRepository, c)).ToArray());
//where
var filter1 = new SpontaneouslyInventedFilter(memoryRepository, null, SpecialFieldNames.DataLoadRunID + " = " + _dataLoadRunID, "DataLoadRunID matches", null, null);
var filter2 =
new SpontaneouslyInventedFilter(memoryRepository, null,
string.Format(@" not exists (
select 1 from {0} where {1} {2} < {3}
)", archiveTableName, whereStatement, SpecialFieldNames.DataLoadRunID, _dataLoadRunID),
"Record doesn't exist in archive", null, null);
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, new [] { filter1, filter2 }, FilterContainerOperation.AND);
Inserts = new DataTable();
FillTableWithQueryIfUserConsents(Inserts, qb.SQL, checkNotifier, server);
}
public SpontaneouslyInventedFilterContainer(MemoryCatalogueRepository repo, IContainer[] subContainersIfAny, IFilter[] filtersIfAny, FilterContainerOperation operation) : base(repo)
{
repo.InsertAndHydrate(this, new Dictionary <string, object>());
if (subContainersIfAny != null)
{
foreach (IContainer container in subContainersIfAny)
{
AddChild(container);
}
}
if (filtersIfAny != null)
{
foreach (IFilter filter in filtersIfAny)
{
if (filter is SpontaneouslyInventedFilter)
{
AddChild(filter);
}
else
{
AddChild(new SpontaneouslyInventedFilter(repo, this, filter.WhereSQL, filter.Name, filter.Description, filter.GetAllParameters()));
}
}
}
Operation = operation;
}
public string GetSql()
{
var qb = new QueryBuilder(null, null);
if (ViewType == ViewType.TOP_100)
{
qb.TopX = 100;
}
var memoryRepository = new MemoryCatalogueRepository();
qb.AddColumnRange(TableInfo.ColumnInfos.Select(c => new ColumnInfoToIColumn(memoryRepository, c)).ToArray());
var filter = GetFilterIfAny();
if (filter != null)
{
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, new[] { filter }, FilterContainerOperation.AND);
}
if (ViewType == ViewType.Aggregate)
{
qb.AddCustomLine("count(*),", QueryComponent.QueryTimeColumn);
}
var sql = qb.SQL;
if (ViewType == ViewType.Aggregate)
{
throw new NotSupportedException("ViewType.Aggregate can only be applied to ColumnInfos not TableInfos");
}
return(sql);
}
public string GetCommand()
{
var repo = new MemoryCatalogueRepository();
var qb = new QueryBuilder("distinct", null);
qb.AddColumn(new ColumnInfoToIColumn(repo, _keyColumn)
{
Order = 0
});
qb.AddColumn(new ColumnInfoToIColumn(repo, _descriptionColumn)
{
Order = 1
});
qb.TopX = 100;
var container = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.AND);
if (!string.IsNullOrWhiteSpace(tbCode.Text))
{
var codeFilter = new SpontaneouslyInventedFilter(repo, container, _keyColumn.GetFullyQualifiedName() + " LIKE '" + tbCode.Text + "%'", "Key Starts", "", null);
container.AddChild(codeFilter);
}
if (!string.IsNullOrWhiteSpace(tbDescription.Text))
{
var codeFilter = new SpontaneouslyInventedFilter(repo, container, _descriptionColumn.GetFullyQualifiedName() + " LIKE '%" + tbDescription.Text + "%'", "Description Contains", "", null);
container.AddChild(codeFilter);
}
qb.RootFilterContainer = container;
return(qb.SQL);
}
public void IsReadonly_SpontaneousContainer()
{
var memoryrepo = new MemoryCatalogueRepository();
var c = new SpontaneouslyInventedFilterContainer(memoryrepo, null, null, FilterContainerOperation.AND);
Assert.IsFalse(c.ShouldBeReadOnly(out _), "Spont containers should never be in UI but lets not tell the programmer they shouldn't be edited");
}
private AggregateBuilder GetAdjustedForRecordsIn(IFilter singleFilterOnly = null)
{
if (_cohort == null)
{
throw new NotSupportedException("This method only works when there is a cohort aggregate, it does not work for CohortAggregateContainers");
}
var memoryRepository = new MemoryCatalogueRepository();
//Get a builder for creating the basic aggregate graph
var summaryBuilder = _summary.GetQueryBuilder();
//Find it's root container if it has one
var summaryRootContainer = summaryBuilder.RootFilterContainer;
//work out a filter SQL that will restrict the graph generated only to the cohort
IContainer cohortRootContainer = _cohort.RootFilterContainer;
//if we are only graphing a single filter from the Cohort
if (singleFilterOnly != null)
{
cohortRootContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, new [] { singleFilterOnly }, FilterContainerOperation.AND);
}
//so hacky, we pass in the summary builder (a blatant lie!) and tell the CohortQueryBuilderHelper it belongs to AggregateConfiguration _cohort (when it doesn't). This
//will result in any PatientIndex tables associated with _cohort being propagated into the _summary builder
var cohortHelper = new CohortQueryBuilderHelper(_globals, new ParameterManager(_globals), _cohort.GetCohortIdentificationConfigurationIfAny().QueryCachingServer);
cohortHelper.AddJoinablesToBuilder(summaryBuilder, _cohort, 1);
//if the cohort has no WHERE SQL
if (cohortRootContainer == null)
{
return(summaryBuilder); //summary can be run verbatim
}
//the summary has no WHERE SQL
if (summaryRootContainer == null)
{
summaryBuilder.RootFilterContainer = cohortRootContainer;//hijack the cohorts root container
}
else
{
//they both have WHERE SQL
//Create a new spontaneous container (virtual memory only container) that contains both subtrees
var spontContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, new[] { cohortRootContainer, summaryRootContainer }, null, FilterContainerOperation.AND);
summaryBuilder.RootFilterContainer = spontContainer;
}
//better import the globals because WHERE logic from the cohort has been inherited... only problem will be if there are conflicting globals in users aggregate but that's just tough luck
foreach (ISqlParameter p in _globals)
{
summaryBuilder.ParameterManager.AddGlobalParameter(p);
}
return(summaryBuilder);
}
public void TestMemoryVsDatabaseRepository_CatalogueConstructor()
{
var memoryRepository = new MemoryCatalogueRepository(CatalogueRepository.GetServerDefaults());
Catalogue memCatalogue = new Catalogue(memoryRepository, "My New Catalogue");
Catalogue dbCatalogue = new Catalogue(CatalogueRepository, "My New Catalogue");
UnitTests.AssertAreEqual(memCatalogue, dbCatalogue);
}
/// <summary>
/// Override to change what filters are included in the WHERE Sql of your query. Default behaviour is to match on the
/// <see cref="KeyTag"/> and AND with all <see cref="ICatalogue.GetAllMandatoryFilters"/> listed on the <see cref="Catalogue"/>
/// </summary>
/// <param name="memoryRepo"></param>
/// <param name="rootContainer"></param>
/// <returns></returns>
protected virtual IEnumerable <IFilter> GetFilters(MemoryCatalogueRepository memoryRepo, IContainer rootContainer)
{
yield return(new SpontaneouslyInventedFilter(memoryRepo, rootContainer, KeyTag + "= '{0}'",
"Filter Series", "Filters by series UID", null));
foreach (var filter in Columns.Catalogue.GetAllMandatoryFilters())
{
yield return(filter);
}
}
public void TestImportingATable(DatabaseType dbType)
{
DataTable dt = new DataTable();
dt.Columns.Add("Do");
dt.Columns.Add("Ray");
dt.Columns.Add("Me");
dt.Columns.Add("Fa");
dt.Columns.Add("So");
var db = GetCleanedServer(dbType);
var tbl = db.CreateTable("OmgTables", dt);
var memoryRepository = new MemoryCatalogueRepository(CatalogueRepository.GetServerDefaults());
var importer1 = new TableInfoImporter(memoryRepository, tbl, DataAccessContext.Any);
TableInfo memTableInfo;
ColumnInfo[] memColumnInfos;
Catalogue memCatalogue;
CatalogueItem[] memCatalogueItems;
ExtractionInformation[] memExtractionInformations;
importer1.DoImport(out memTableInfo, out memColumnInfos);
var forwardEngineer1 = new ForwardEngineerCatalogue(memTableInfo, memColumnInfos);
forwardEngineer1.ExecuteForwardEngineering(out memCatalogue, out memCatalogueItems, out memExtractionInformations);
TableInfo dbTableInfo;
ColumnInfo[] dbColumnInfos;
Catalogue dbCatalogue;
CatalogueItem[] dbCatalogueItems;
ExtractionInformation[] dbExtractionInformations;
var importerdb = new TableInfoImporter(CatalogueRepository, tbl, DataAccessContext.Any);
importerdb.DoImport(out dbTableInfo, out dbColumnInfos);
var forwardEngineer2 = new ForwardEngineerCatalogue(dbTableInfo, dbColumnInfos);
forwardEngineer2.ExecuteForwardEngineering(out dbCatalogue, out dbCatalogueItems, out dbExtractionInformations);
UnitTests.AssertAreEqual(memCatalogue, dbCatalogue);
UnitTests.AssertAreEqual(memTableInfo, dbTableInfo);
UnitTests.AssertAreEqual(memCatalogue.CatalogueItems, dbCatalogue.CatalogueItems);
UnitTests.AssertAreEqual(memCatalogue.GetAllExtractionInformation(ExtractionCategory.Any), dbCatalogue.GetAllExtractionInformation(ExtractionCategory.Any));
UnitTests.AssertAreEqual(memCatalogue.CatalogueItems.Select(ci => ci.ColumnInfo), dbCatalogue.CatalogueItems.Select(ci => ci.ColumnInfo));
}
public void SourceRead_ToTable_IgnoringSuperflousColumn_LoadMetadata()
{
var repo = new MemoryCatalogueRepository();
var lmd = new LoadMetadata(repo, "MyLoad");
var cata1 = new Catalogue(repo, "PatientCatalogue");
var ci1 = new CatalogueItem(repo, cata1, "PatientAge");
var ti1 = new TableInfo(repo, "PatientTableInfo");
var colInfo1 = new ColumnInfo(repo, "PatientAge", "varchar(100)", ti1);
ci1.ColumnInfo_ID = colInfo1.ID;
ci1.SaveToDatabase();
cata1.LoadMetadata_ID = lmd.ID;
cata1.SaveToDatabase();
var cata2 = new Catalogue(repo, "FileCatalogue");
var ci2 = new CatalogueItem(repo, cata2, "RelFileName");
var ti2 = new TableInfo(repo, "FileTableInfo");
var colInfo2 = new ColumnInfo(repo, "RelFileName", "varchar(100)", ti2);
ci2.ColumnInfo_ID = colInfo2.ID;
ci2.SaveToDatabase();
cata2.LoadMetadata_ID = lmd.ID;
cata2.SaveToDatabase();
var source = new DicomDatasetCollectionSource();
source.InvalidDataHandlingStrategy = InvalidDataHandling.ThrowException;
var ds = new DicomDataset();
ds.Add(DicomTag.PatientAge, "123Y");
var sequence = new DicomSequence(DicomTag.AcquisitionContextSequence,
new DicomDataset()
{
{ DicomTag.WedgeAngleFloat, "3.40282347e+038" }
});
ds.Add(sequence);
var worklist = new ExplicitListDicomDatasetWorklist(new[] { ds }, "fish.dcm");
source.PreInitialize(worklist, new ThrowImmediatelyDataLoadEventListener());
source.FilenameField = "RelFileName";
source.UseAllTableInfoInLoadAsFieldMap = lmd;
var dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
Assert.AreEqual("123Y", dt.Rows[0]["PatientAge"]);
Assert.AreEqual("fish.dcm", dt.Rows[0]["RelFileName"]);
Assert.AreEqual(2, dt.Columns.Count);
}
public void TestMemoryRepository_LiveLogging()
{
var memoryRepository = new MemoryCatalogueRepository();
var loggingServer = new ExternalDatabaseServer(memoryRepository, "My Logging Server", null);
memoryRepository.SetDefault(PermissableDefaults.LiveLoggingServer_ID, loggingServer);
Catalogue memCatalogue = new Catalogue(memoryRepository, "My New Catalogue");
Assert.AreEqual(memCatalogue.LiveLoggingServer_ID, loggingServer.ID);
}
public void TestMemoryRepository_AggregateConfigurationConstructor()
{
var memoryRepository = new MemoryCatalogueRepository(CatalogueRepository.GetServerDefaults());
Catalogue memCatalogue = new Catalogue(memoryRepository, "My New Catalogue");
Catalogue dbCatalogue = new Catalogue(CatalogueRepository, "My New Catalogue");
var memAggregate = new AggregateConfiguration(memoryRepository, memCatalogue, "My New Aggregate");
var dbAggregate = new AggregateConfiguration(CatalogueRepository, dbCatalogue, "My New Aggregate");
UnitTests.AssertAreEqual(memAggregate, dbAggregate);
}
/// <summary>
/// Generates the WHERE logic for the query. Adds a single root container with AND operation and then adds
/// all filters in <see cref="GetFilters"/>. It is better to override <see cref="GetFilters"/> unless you want
/// to create a nested container tree for the query.
/// </summary>
/// <returns></returns>
protected virtual IContainer GetWhereLogic()
{
//make a root WHERE container in memory
var memory = new MemoryCatalogueRepository();
var container = new SpontaneouslyInventedFilterContainer(memory, null, null, FilterContainerOperation.AND);
//Get all filters that we are to add and add them to the root
foreach (IFilter filter in GetFilters(memory, container))
{
container.AddChild(new SpontaneouslyInventedFilter(memory, container, filter.WhereSQL, filter.Name, filter.Description, filter.GetAllParameters()));
}
return(container);
}
/// <summary>
/// Creates a new temporary (unsaveable) filter in the given memory <paramref name="repo"/>
/// </summary>
/// <param name="repo">The repository to store the temporary object in</param>
/// <param name="notionalParent"></param>
/// <param name="whereSql"></param>
/// <param name="name"></param>
/// <param name="description"></param>
/// <param name="filterParametersIfAny"></param>
public SpontaneouslyInventedFilter(MemoryCatalogueRepository repo, IContainer notionalParent, string whereSql, string name, string description, ISqlParameter[] filterParametersIfAny)
{
_repo = repo;
_filterParametersIfAny = filterParametersIfAny;
WhereSQL = whereSql;
Name = name;
Description = description;
repo.InsertAndHydrate(this, new Dictionary <string, object>());
if (notionalParent != null)
{
repo.AddChild(notionalParent, this);
FilterContainer_ID = notionalParent.ID;
}
}
public ExecuteCommandPacsFetch(IBasicActivateItems activator, string start, string end, string remoteAeUri, int remotePort, string remoteAeTitle, string localAeUri, int localPort, string localAeTitle, string outDir, int maxRetries) : base(activator)
{
var startDate = DateTime.Parse(start);
var endDate = DateTime.Parse(end);
// Make something that kinda looks like a valid DLE load
var memory = new MemoryCatalogueRepository();
var lmd = new LoadMetadata(memory);
var dir = Directory.CreateDirectory(outDir);
var results = LoadDirectory.CreateDirectoryStructure(dir, "out", true);
lmd.LocationOfFlatFiles = results.RootPath.FullName;
lmd.SaveToDatabase();
var lp = new LoadProgress(memory, lmd);
var cp = new CacheProgress(memory, lp);
//Create the source component only and a valid request range to fetch
_source = new PACSSource
{
RemoteAEUri = new Uri("http://" + remoteAeUri),
RemoteAEPort = remotePort,
RemoteAETitle = remoteAeTitle,
LocalAEUri = new Uri("http://" + localAeUri),
LocalAEPort = localPort,
LocalAETitle = localAeTitle,
TransferTimeOutInSeconds = 50000,
Modality = "ALL",
MaxRetries = maxRetries
};
//<- rly? its not gonna pass without an http!?
_request = new BackfillCacheFetchRequest(BasicActivator.RepositoryLocator.CatalogueRepository, startDate)
{
ChunkPeriod = endDate.Subtract(startDate), CacheProgress = cp
};
//Initialize it
_source.PreInitialize(BasicActivator.RepositoryLocator.CatalogueRepository, new ThrowImmediatelyDataLoadEventListener {
WriteToConsole = true
});
_source.PreInitialize(this, new ThrowImmediatelyDataLoadEventListener {
WriteToConsole = true
});
}
private AggregateBuilder GetAdjustedForExtractionIdentifiersIn()
{
var cachingServer = GetQueryCachingServer();
if (cachingServer == null)
{
throw new NotSupportedException("No Query Caching Server configured");
}
var memoryRepository = new MemoryCatalogueRepository();
//Get a builder for creating the basic aggregate graph
var builder = _summary.GetQueryBuilder();
//Find it's root container if it has one
var oldRootContainer = builder.RootFilterContainer;
//Create a new spontaneous container (virtual memory only container, this will include an in line filter that restricts the graph to match the cohort and then include a subcontainer with the old root container - if there was one)
var spontContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, oldRootContainer != null ? new[] { oldRootContainer } : null, null, FilterContainerOperation.AND);
//work out a filter SQL that will restrict the graph generated only to the cohort
CohortQueryBuilder cohortQueryBuilder = GetBuilder();
cohortQueryBuilder.CacheServer = cachingServer;
//It is comming direct from the cache so we don't need to output any parameters... the only ones that would appear are the globals anyway and those are not needed since cache
cohortQueryBuilder.DoNotWriteOutParameters = true;
//the basic cohort SQL select chi from dataset where ....
var cohortSql = cohortQueryBuilder.SQL;
if (cohortQueryBuilder.CountOfCachedSubQueries == 0 || cohortQueryBuilder.CountOfSubQueries != cohortQueryBuilder.CountOfCachedSubQueries)
{
throw new NotSupportedException("Only works for 100% Cached queries, your query has " + cohortQueryBuilder.CountOfCachedSubQueries + "/" + cohortQueryBuilder.CountOfSubQueries + " queries cached");
}
//there will be a single dimension on the cohort aggregate so this translates to "MyTable.MyDataset.CHI in Select(
var filterSql = _extractionIdentifierColumn.SelectSQL + " IN (" + cohortSql + ")";
//Add a filter which restricts the graph generated to the cohort only
spontContainer.AddChild(new SpontaneouslyInventedFilter(memoryRepository, spontContainer, filterSql, "Patient is in cohort", "Ensures the patients in the summary aggregate are also in the cohort aggregate (and only them)", null));
builder.RootFilterContainer = spontContainer;
return(builder);
}
public void TestMemoryVsDatabaseRepository_ProcessTaskConstructor()
{
var memoryRepository = new MemoryCatalogueRepository(CatalogueRepository.GetServerDefaults());
var memLmd = new LoadMetadata(memoryRepository, "My New Load");
var dbLmd = new LoadMetadata(CatalogueRepository, "My New Load");
UnitTests.AssertAreEqual(memLmd, dbLmd);
var memPt = new ProcessTask(memoryRepository, memLmd, LoadStage.AdjustRaw)
{
Name = "MyPt"
};
var dbPt = new ProcessTask(CatalogueRepository, dbLmd, LoadStage.AdjustRaw)
{
Name = "MyPt"
};
UnitTests.AssertAreEqual(memPt, dbPt);
}
private string GetMappingTableSql()
{
var repo = new MemoryCatalogueRepository();
var qb = new QueryBuilder("DISTINCT", null, null);
qb.AddColumn(new ColumnInfoToIColumn(repo, MappingFromColumn));
qb.AddColumn(new ColumnInfoToIColumn(repo, MappingToColumn));
if (!string.IsNullOrWhiteSpace(WHERELogic))
{
var container = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.AND);
var filter = new SpontaneouslyInventedFilter(repo, container, WHERELogic, "WHERELogic", null, null);
container.AddChild(filter);
qb.RootFilterContainer = container;
}
return(qb.SQL);
}
private void AddWHEREToBuilder_CategoryIsTOrNumberGreaterThan42(AggregateBuilder builder, DatabaseType type)
{
var syntaxHelper = new QuerySyntaxHelperFactory().Create(type);
var declaration = syntaxHelper.GetParameterDeclaration("@category", new DatabaseTypeRequest(typeof(string), 1));
var repo = new MemoryCatalogueRepository();
var ORContainer = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.OR);
var constParam = new ConstantParameter(declaration, "'T'", "T Category Only", syntaxHelper);
//this is deliberately duplication, it tests that the parameter compiles as well as that any dynamic sql doesn't get thrown by quotes
var filter1 = new SpontaneouslyInventedFilter(repo, ORContainer, "(Category=@category OR Category = 'T')", "Category Is @category",
"ensures the records belong to the category @category", new ISqlParameter[] { constParam });
var filter2 = new SpontaneouslyInventedFilter(repo, ORContainer, "NumberInTrouble > 42",
"number in trouble greater than 42", "See above", null);
ORContainer.AddChild(filter1);
ORContainer.AddChild(filter2);
builder.RootFilterContainer = ORContainer;
}
public void SourceRead_ToTable_IgnoringSuperflousColumn_TableInfo()
{
var repo = new MemoryCatalogueRepository();
var ti = new TableInfo(repo, "MyTable");
new ColumnInfo(repo, "PatientAge", "varchar(100)", ti);
new ColumnInfo(repo, "RelFileName", "varchar(100)", ti);
var source = new DicomDatasetCollectionSource();
source.InvalidDataHandlingStrategy = InvalidDataHandling.ThrowException;
var ds = new DicomDataset();
ds.Add(DicomTag.PatientAge, "123Y");
var sequence = new DicomSequence(DicomTag.AcquisitionContextSequence,
new DicomDataset()
{
{ DicomTag.WedgeAngleFloat, "3.40282347e+038" }
});
ds.Add(sequence);
var worklist = new ExplicitListDicomDatasetWorklist(new[] { ds }, "fish.dcm");
source.PreInitialize(worklist, new ThrowImmediatelyDataLoadEventListener());
source.FilenameField = "RelFileName";
source.FieldMapTableIfAny = ti;
var dt = source.GetChunk(new ThrowImmediatelyDataLoadEventListener(), new GracefulCancellationToken());
Assert.AreEqual("123Y", dt.Rows[0]["PatientAge"]);
Assert.AreEqual("fish.dcm", dt.Rows[0]["RelFileName"]);
Assert.AreEqual(2, dt.Columns.Count);
}
private AggregateBuilder GetAdjustedForRecordsIn(IFilter singleFilterOnly = null)
{
if (_cohort == null)
{
throw new NotSupportedException("This method only works when there is a cohort aggregate, it does not work for CohortAggregateContainers");
}
var memoryRepository = new MemoryCatalogueRepository();
//Get a builder for creating the basic aggregate graph
var summaryBuilder = _summary.GetQueryBuilder();
//Find it's root container if it has one
var summaryRootContainer = summaryBuilder.RootFilterContainer;
//work out a filter SQL that will restrict the graph generated only to the cohort
IContainer cohortRootContainer = _cohort.RootFilterContainer;
//if we are only graphing a single filter from the Cohort
if (singleFilterOnly != null)
{
cohortRootContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, new [] { singleFilterOnly }, FilterContainerOperation.AND);
}
var joinUse = _cohort.PatientIndexJoinablesUsed.SingleOrDefault();
var joinTo = joinUse?.JoinableCohortAggregateConfiguration?.AggregateConfiguration;
//if there is a patient index table we must join to it
if (joinUse != null)
{
//get sql for the join table
var builder = new CohortQueryBuilder(joinTo, _globals, null);
var joinableSql = new CohortQueryBuilderDependencySql(builder.SQL, builder.ParameterManager);
var helper = new CohortQueryBuilderHelper();
var extractionIdentifierColumn = _summary.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any)
.Where(ei => ei.IsExtractionIdentifier).ToArray();
if (extractionIdentifierColumn.Length != 1)
{
throw new Exception($"Catalogue behind {_summary} must have exactly 1 IsExtractionIdentifier column but it had " + extractionIdentifierColumn.Length);
}
helper.AddJoinToBuilder(_summary, extractionIdentifierColumn[0], summaryBuilder, new QueryBuilderArgs(joinUse, joinTo, joinableSql, null, _globals));
}
//if the cohort has no WHERE SQL
if (cohortRootContainer == null)
{
return(summaryBuilder); //summary can be run verbatim
}
//the summary has no WHERE SQL
if (summaryRootContainer == null)
{
summaryBuilder.RootFilterContainer = cohortRootContainer;//hijack the cohorts root container
}
else
{
//they both have WHERE SQL
//Create a new spontaneous container (virtual memory only container) that contains both subtrees
var spontContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, new[] { cohortRootContainer, summaryRootContainer }, null, FilterContainerOperation.AND);
summaryBuilder.RootFilterContainer = spontContainer;
}
//better import the globals because WHERE logic from the cohort has been inherited... only problem will be if there are conflicting globals in users aggregate but that's just tough luck
foreach (ISqlParameter p in _globals)
{
summaryBuilder.ParameterManager.AddGlobalParameter(p);
}
return(summaryBuilder);
}
/// <summary>
/// Constructs a new filter by copying out the values from the supplied IFilter
/// </summary>
/// <param name="repo">The repository to store the temporary object in</param>
/// <param name="copyFrom"></param>
public SpontaneouslyInventedFilter(MemoryCatalogueRepository repo, IFilter copyFrom) : this(repo, null, copyFrom.WhereSQL, copyFrom.Name, copyFrom.Description, copyFrom.GetAllParameters())
{
}
protected override AggregateBuilder GetQueryBuilder(AggregateConfiguration aggregateConfiguration)
{
if (Request == null)
{
throw new Exception("Request has not been initialized yet, has SetCollection not yet been called?");
}
var repo = new MemoryCatalogueRepository();
//we are hijacking the query builder creation for this graph
AggregateBuilder toReturn = base.GetQueryBuilder(aggregateConfiguration);
//instead of only filtering on the filters of the Aggregate, also filter on the configurations data extraction filters AND on the cohort ID
var spontedContainer = new SpontaneouslyInventedFilterContainer(repo, null, null, FilterContainerOperation.AND);
//the aggregate has filters (it probably does)
if (toReturn.RootFilterContainer != null)
{
spontedContainer.AddChild(toReturn.RootFilterContainer);//add it
}
//the cohort extraction request has filters?
if (Request.QueryBuilder.RootFilterContainer != null)
{
spontedContainer.AddChild(Request.QueryBuilder.RootFilterContainer);//add those too
}
//now also add the cohort where statement
string cohortWhereSql = Request.ExtractableCohort.WhereSQL();
var spontedFilter = new SpontaneouslyInventedFilter(repo, spontedContainer, cohortWhereSql, "Cohort ID Filter",
"Cohort ID Filter (" + Request.ExtractableCohort + ")", null);
//now we need to figure out what impromptu joins are going on in the main query extraction
//Normally we have an impromptu join e.g. Inner Join MyCohortTable on CHI = MyCohortTable.CHI
//Also we can expect there to be joins to custom tables e.g. Left Join MyCohortCustomTable1 on CHI = MyCustomCohortTable1.CHI (so they can select freaky researcher columns like PatientBarcode etc
//Finally we expect that there is an impromptu filter which does the cohort ID restriction on the query - we have already dealt with that above with a SpontaneouslyInventedFilter so we can ignore those
//But maybe some other programmer has sneaked in some other custom lines we should worry about
var customLines = Request.QueryBuilder.CustomLines.ToArray();
//we expected a custom line for this (which we have dealt with above so throw it away)
customLines = customLines.Where(c => c.Text != Request.ExtractableCohort.WhereSQL()).ToArray();
//now all that should be left are custom lines which are joins (in theory, otherwise complain)
if (customLines.Any(c => c.LocationToInsert != QueryComponent.JoinInfoJoin))
{
throw new QueryBuildingException("We were busy hijacking the ISqlQueryBuilder returned by Request.GetQueryBuilder and were looking for the custom table / cohort table join custom lines but then we noticed there were other custom lines in the query (like non join ones!)");
}
if (!customLines.Any())
{
throw new Exception("Expected there to be at least 1 custom join line returned by the ISqlQueryBuilder fetched with Request.GetQueryBuilder but it had 0 so how did it know what cohort table to join against?");
}
foreach (CustomLine line in customLines)
{
toReturn.AddCustomLine(line.Text, QueryComponent.JoinInfoJoin);
}
spontedContainer.AddChild(spontedFilter);
//now set the original aggregate that we are hijacking to have the new sponted container (which includes the original filters + the extraction ones + the cohort ID one)
toReturn.RootFilterContainer = spontedContainer;
return(toReturn);
}
public void Execute()
{
if (_planManager.TargetDatabase == null)
{
throw new Exception("PlanManager has no TargetDatabase set");
}
var memoryRepo = new MemoryCatalogueRepository();
using (_catalogueRepository.BeginNewTransactedConnection())
{
try
{
//for each skipped table
foreach (var skippedTable in _planManager.SkippedTables)
{
//we might have to refactor or port JoinInfos to these tables so we should establish what the parenthood of them was
foreach (ColumnInfo columnInfo in skippedTable.ColumnInfos)
{
GetNewColumnInfoForOld(columnInfo, true);
}
}
//for each table that isn't being skipped
foreach (var oldTableInfo in _planManager.TableInfos.Except(_planManager.SkippedTables))
{
List <DatabaseColumnRequest> columnsToCreate = new List <DatabaseColumnRequest>();
Dictionary <string, ColumnInfo> migratedColumns = new Dictionary <string, ColumnInfo>(StringComparer.CurrentCultureIgnoreCase);
var querybuilderForMigratingTable = new QueryBuilder(null, null);
//for each column we are not skipping (Drop) work out the endpoint datatype (planner knows this)
foreach (ColumnInfo columnInfo in oldTableInfo.ColumnInfos)
{
var columnPlan = _planManager.GetPlanForColumnInfo(columnInfo);
if (columnPlan.Plan != Plan.Drop)
{
//add the column verbatim to the query builder because we know we have to read it from source
querybuilderForMigratingTable.AddColumn(new ColumnInfoToIColumn(memoryRepo, columnInfo));
string colName = columnInfo.GetRuntimeName();
//if it is being ano tabled then give the table name ANO as a prefix
if (columnPlan.Plan == Plan.ANO)
{
colName = "ANO" + colName;
}
migratedColumns.Add(colName, columnInfo);
columnsToCreate.Add(new DatabaseColumnRequest(colName, columnPlan.GetEndpointDataType(), !columnInfo.IsPrimaryKey)
{
IsPrimaryKey = columnInfo.IsPrimaryKey
});
}
}
SelectSQLForMigrations.Add(oldTableInfo, querybuilderForMigratingTable);
//Create the actual table
var tbl = _planManager.TargetDatabase.CreateTable(oldTableInfo.GetRuntimeName(), columnsToCreate.ToArray());
//import the created table
TableInfoImporter importer = new TableInfoImporter(_catalogueRepository, tbl);
importer.DoImport(out var newTableInfo, out var newColumnInfos);
//Audit the parenthood of the TableInfo/ColumnInfos
AuditParenthood(oldTableInfo, newTableInfo);
foreach (ColumnInfo newColumnInfo in newColumnInfos)
{
var oldColumnInfo = migratedColumns[newColumnInfo.GetRuntimeName()];
var columnPlan = _planManager.GetPlanForColumnInfo(oldColumnInfo);
if (columnPlan.Plan == Plan.ANO)
{
newColumnInfo.ANOTable_ID = columnPlan.ANOTable.ID;
newColumnInfo.SaveToDatabase();
}
//if there was a dilution configured we need to setup a virtual DLE load only column of the input type (this ensures RAW has a valid datatype)
if (columnPlan.Plan == Plan.Dilute)
{
//Create a discarded (load only) column with name matching the new columninfo
var discard = new PreLoadDiscardedColumn(_catalogueRepository, newTableInfo, newColumnInfo.GetRuntimeName());
//record that it exists to support dilution and that the data type matches the input (old) ColumnInfo (i.e. not the new data type!)
discard.Destination = DiscardedColumnDestination.Dilute;
discard.SqlDataType = oldColumnInfo.Data_type;
discard.SaveToDatabase();
DilutionOperationsForMigrations.Add(discard, columnPlan.Dilution);
}
AuditParenthood(oldColumnInfo, newColumnInfo);
}
if (DilutionOperationsForMigrations.Any())
{
newTableInfo.IdentifierDumpServer_ID = _planManager.GetIdentifierDumpServer().ID;
newTableInfo.SaveToDatabase();
}
}
NewCatalogue = _planManager.Catalogue.ShallowClone();
NewCatalogue.Name = "ANO" + _planManager.Catalogue.Name;
NewCatalogue.Folder = new CatalogueFolder(NewCatalogue, "\\anonymous" + NewCatalogue.Folder.Path);
NewCatalogue.SaveToDatabase();
AuditParenthood(_planManager.Catalogue, NewCatalogue);
//For each of the old ExtractionInformations (95% of the time that's just a reference to a ColumnInfo e.g. '[People].[Height]' but 5% of the time it's some horrible aliased transform e.g. 'dbo.RunMyCoolFunction([People].[Height]) as BigHeight'
foreach (CatalogueItem oldCatalogueItem in _planManager.Catalogue.CatalogueItems)
{
var oldColumnInfo = oldCatalogueItem.ColumnInfo;
//catalogue item is not connected to any ColumnInfo
if (oldColumnInfo == null)
{
continue;
}
var columnPlan = _planManager.GetPlanForColumnInfo(oldColumnInfo);
//we are not migrating it anyway
if (columnPlan.Plan == Plan.Drop)
{
continue;
}
ColumnInfo newColumnInfo = GetNewColumnInfoForOld(oldColumnInfo);
var newCatalogueItem = oldCatalogueItem.ShallowClone(NewCatalogue);
//and rewire it's ColumnInfo to the cloned child one
newCatalogueItem.ColumnInfo_ID = newColumnInfo.ID;
//If the old CatalogueItem had the same name as it's underlying ColumnInfo then we should use the new one otherwise just copy the old name whatever it was
newCatalogueItem.Name = oldCatalogueItem.Name.Equals(oldColumnInfo.Name) ? newColumnInfo.GetRuntimeName() : oldCatalogueItem.Name;
//add ANO to the front if the underlying column was annoed
if (newColumnInfo.GetRuntimeName().StartsWith("ANO") && !newCatalogueItem.Name.StartsWith("ANO"))
{
newCatalogueItem.Name = "ANO" + newCatalogueItem.Name;
}
newCatalogueItem.SaveToDatabase();
var oldExtractionInformation = oldCatalogueItem.ExtractionInformation;
//if the plan is to make the ColumnInfo extractable
if (columnPlan.ExtractionCategoryIfAny != null)
{
//Create a new ExtractionInformation for the new Catalogue
var newExtractionInformation = new ExtractionInformation(_catalogueRepository, newCatalogueItem, newColumnInfo, newColumnInfo.Name);
newExtractionInformation.ExtractionCategory = columnPlan.ExtractionCategoryIfAny.Value;
newExtractionInformation.SaveToDatabase();
//if it was previously extractable
if (oldExtractionInformation != null)
{
var refactorer = new SelectSQLRefactorer();
//restore the old SQL as it existed in the origin table
newExtractionInformation.SelectSQL = oldExtractionInformation.SelectSQL;
//do a refactor on the old column name for the new column name
refactorer.RefactorColumnName(newExtractionInformation, oldColumnInfo, newColumnInfo.Name, true);
//also refactor any other column names that might be referenced by the transform SQL e.g. it could be a combo column name where forename + surname is the value of the ExtractionInformation
foreach (var kvpOtherCols in _parenthoodDictionary.Where(kvp => kvp.Key is ColumnInfo))
{
//if it's one we have already done, dont do it again
if (Equals(kvpOtherCols.Value, newColumnInfo))
{
continue;
}
//otherwise do a non strict refactoring (don't worry if you don't finda ny references)
refactorer.RefactorColumnName(newExtractionInformation, (ColumnInfo)kvpOtherCols.Key, ((ColumnInfo)(kvpOtherCols.Value)).Name, false);
}
//make the new one exactly as extractable
newExtractionInformation.Order = oldExtractionInformation.Order;
newExtractionInformation.Alias = oldExtractionInformation.Alias;
newExtractionInformation.IsExtractionIdentifier = oldExtractionInformation.IsExtractionIdentifier;
newExtractionInformation.HashOnDataRelease = oldExtractionInformation.HashOnDataRelease;
newExtractionInformation.IsPrimaryKey = oldExtractionInformation.IsPrimaryKey;
newExtractionInformation.SaveToDatabase();
}
AuditParenthood(oldCatalogueItem, newCatalogueItem);
if (oldExtractionInformation != null)
{
AuditParenthood(oldExtractionInformation, newExtractionInformation);
}
}
}
var existingJoinInfos = _catalogueRepository.GetAllObjects <JoinInfo>();
var existingLookups = _catalogueRepository.GetAllObjects <Lookup>();
var existingLookupComposites = _catalogueRepository.GetAllObjects <LookupCompositeJoinInfo>();
//migrate join infos
foreach (JoinInfo joinInfo in _planManager.GetJoinInfosRequiredCatalogue())
{
var newFk = GetNewColumnInfoForOld(joinInfo.ForeignKey);
var newPk = GetNewColumnInfoForOld(joinInfo.PrimaryKey);
//already exists
if (!existingJoinInfos.Any(ej => ej.ForeignKey_ID == newFk.ID && ej.PrimaryKey_ID == newPk.ID))
{
new JoinInfo(_catalogueRepository, newFk, newPk, joinInfo.ExtractionJoinType, joinInfo.Collation); //create it
}
}
//migrate Lookups
foreach (Lookup lookup in _planManager.GetLookupsRequiredCatalogue())
{
//Find the new columns in the ANO table that match the old lookup columns
var newDesc = GetNewColumnInfoForOld(lookup.Description);
var newFk = GetNewColumnInfoForOld(lookup.ForeignKey);
var newPk = GetNewColumnInfoForOld(lookup.PrimaryKey);
//see if we already have a Lookup declared for the NEW columns (unlikely)
Lookup newLookup = existingLookups.SingleOrDefault(l => l.Description_ID == newDesc.ID && l.ForeignKey_ID == newFk.ID);
//create new Lookup that mirrors the old but references the ANO columns instead
if (newLookup == null)
{
newLookup = new Lookup(_catalogueRepository, newDesc, newFk, newPk, lookup.ExtractionJoinType, lookup.Collation);
}
//also mirror any composite (secondary, tertiary join column pairs needed for the Lookup to operate correclty e.g. where TestCode 'HAB1' means 2 different things depending on healthboard)
foreach (LookupCompositeJoinInfo compositeJoin in lookup.GetSupplementalJoins().Cast <LookupCompositeJoinInfo>())
{
var newCompositeFk = GetNewColumnInfoForOld(compositeJoin.ForeignKey);
var newCompositePk = GetNewColumnInfoForOld(compositeJoin.PrimaryKey);
if (!existingLookupComposites.Any(c => c.ForeignKey_ID == newCompositeFk.ID && c.PrimaryKey_ID == newCompositePk.ID))
{
new LookupCompositeJoinInfo(_catalogueRepository, newLookup, newCompositeFk, newCompositePk, compositeJoin.Collation);
}
}
}
//create new data load confguration
LoadMetadata = new LoadMetadata(_catalogueRepository, "Anonymising " + NewCatalogue);
LoadMetadata.EnsureLoggingWorksFor(NewCatalogue);
NewCatalogue.LoadMetadata_ID = LoadMetadata.ID;
NewCatalogue.SaveToDatabase();
if (_planManager.DateColumn != null)
{
LoadProgressIfAny = new LoadProgress(_catalogueRepository, LoadMetadata);
LoadProgressIfAny.OriginDate = _planManager.StartDate;
LoadProgressIfAny.SaveToDatabase();
//date column based migration only works for single TableInfo migrations (see Plan Manager checks)
var qb = SelectSQLForMigrations.Single(kvp => !kvp.Key.IsLookupTable()).Value;
qb.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memoryRepo, null,
new[]
{
new SpontaneouslyInventedFilter(memoryRepo, null, _planManager.DateColumn + " >= @startDate", "After batch start date", "", null),
new SpontaneouslyInventedFilter(memoryRepo, null, _planManager.DateColumn + " <= @endDate", "Before batch end date", "", null),
}
, FilterContainerOperation.AND);
}
try
{
foreach (QueryBuilder qb in SelectSQLForMigrations.Values)
{
Console.WriteLine(qb.SQL);
}
}
catch (Exception e)
{
throw new Exception("Failed to generate migration SQL", e);
}
_catalogueRepository.EndTransactedConnection(true);
}
catch (Exception ex)
{
_catalogueRepository.EndTransactedConnection(false);
throw new Exception("Failed to create ANO version, transaction rolled back succesfully", ex);
}
}
}
private void Initialize()
{
//Figure out which UID columns exist in the Catalogue, do not require file path to be in Catalogue
_columnSet = QueryToExecuteColumnSet.Create(_catalogue, false);
//Tells us the DBMS type
var syntax = _catalogue.GetQuerySyntaxHelper();
//For storing the OR container and filter(s)
var memory = new MemoryCatalogueRepository();
//builds SQL we will run in lookup stage
_queryBuilder = new QueryBuilder(null, null);
//all we care about is if the uid appears if it does then we are rejecting it
_queryBuilder.TopX = 1;
//Filter is OR i.e. StudyInstanceUID = @StudyInstanceUID OR SeriesInstanceUID = @SeriesInstanceUID
var container = _queryBuilder.RootFilterContainer = new SpontaneouslyInventedFilterContainer(memory, null, null, FilterContainerOperation.OR);
//Build SELECT and WHERE bits of the query
if (_columnSet.StudyTagColumn != null)
{
_queryBuilder.AddColumn(_columnSet.StudyTagColumn);
string whereSql =
$"{_columnSet.StudyTagColumn.SelectSQL} = {syntax.ParameterSymbol}{QueryToExecuteColumnSet.DefaultStudyIdColumnName}";
_studyFilter = new SpontaneouslyInventedFilter(memory, container, whereSql, "Study UID Filter", "", null);
container.AddChild(_studyFilter);
}
if (_columnSet.SeriesTagColumn != null)
{
_queryBuilder.AddColumn(_columnSet.SeriesTagColumn);
string whereSql =
$"{_columnSet.SeriesTagColumn.SelectSQL} = {syntax.ParameterSymbol}{QueryToExecuteColumnSet.DefaultSeriesIdColumnName}";
_seriesFilter = new SpontaneouslyInventedFilter(memory, container, whereSql, "Series UID Filter", "", null);
container.AddChild(_seriesFilter);
}
if (_columnSet.InstanceTagColumn != null)
{
_queryBuilder.AddColumn(_columnSet.InstanceTagColumn);
string whereSql =
$"{_columnSet.InstanceTagColumn.SelectSQL} = {syntax.ParameterSymbol}{QueryToExecuteColumnSet.DefaultInstanceIdColumnName}";
_instanceFilter = new SpontaneouslyInventedFilter(memory, container, whereSql, "Instance UID Filter", "", null);
container.AddChild(_instanceFilter);
}
// Make sure the query builder looks valid
if (!_queryBuilder.SelectColumns.Any())
{
throw new NotSupportedException($"Blacklist Catalogue {_catalogue} (ID={_catalogue.ID}) did not have any Core ExtractionInformation columns corresponding to any of the image UID tags (e.g. StudyInstanceUID, SeriesInstanceUID, SOPInstanceUID).");
}
try
{
// make sure we can connect to the server
_server = _catalogue.GetDistinctLiveDatabaseServer(DataAccessContext.DataExport, true);
_server.TestConnection();
}
catch (Exception e)
{
throw new Exception($"Failed to test connection for Catalogue {_catalogue}", e);
}
// run a test lookup query against the remote database
DoLookup("test1", "test2", "test3");
}
public void OpportunisticJoinRequired()
{
var memory = new MemoryRepository();
//tables and columns
TableInfo head = new TableInfo(CatalogueRepository, "Head");
ColumnInfo col1 = new ColumnInfo(CatalogueRepository, "TestResultSetNumber", "int", head);
ColumnInfo col2 = new ColumnInfo(CatalogueRepository, "PK", "int", head);
TableInfo result = new TableInfo(CatalogueRepository, "[biochemistry]..[Result]");
ColumnInfo col3 = new ColumnInfo(CatalogueRepository, "FK", "int", result);
ColumnInfo col4 = new ColumnInfo(CatalogueRepository, "Code", "varchar(10)", result);
ColumnInfo col5 = new ColumnInfo(CatalogueRepository, "[biochemistry]..[Result].[OmgBob]", "varchar(10)", result);
//we can join on col2 = col3
new JoinInfo(CatalogueRepository, col3, col2, ExtractionJoinType.Right, "");
//CASE 1 : Only 1 column used so no join needed
var queryBuilder = new QueryBuilder(null, null);
var icol1 = new ColumnInfoToIColumn(memory, col1);
icol1.Order = 1;
queryBuilder.AddColumn(icol1);
TableInfo primary;
var tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(1, tablesUsed.Count);
Assert.AreEqual(head, tablesUsed[0]);
//CASE 2 : 2 columns used one from each table so join is needed
queryBuilder = new QueryBuilder(null, null);
queryBuilder.AddColumn(new ColumnInfoToIColumn(memory, col1));
var icol4 = new ColumnInfoToIColumn(memory, col4);
icol4.Order = 2;
queryBuilder.AddColumn(icol4);
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(2, tablesUsed.Count);
Assert.AreEqual(head, tablesUsed[0]);
Assert.AreEqual(result, tablesUsed[1]);
Assert.AreEqual(CollapseWhitespace(@"SELECT
TestResultSetNumber,
Code
FROM
[biochemistry]..[Result] Right JOIN Head ON FK = PK"), CollapseWhitespace(queryBuilder.SQL));
var memoryRepository = new MemoryCatalogueRepository();
var spontContainer = new SpontaneouslyInventedFilterContainer(memoryRepository, null, null, FilterContainerOperation.AND);
var spontFilter = new SpontaneouslyInventedFilter(memoryRepository, spontContainer, "[biochemistry]..[Result].[OmgBob] = 'T'",
"My Filter", "Causes spontaneous requirement for joining compeltely", null);
spontContainer.AddChild(spontFilter);
//CASE 3 : Only 1 column from Head but filter contains a reference to Result column
queryBuilder = new QueryBuilder(null, null);
queryBuilder.AddColumn(new ColumnInfoToIColumn(memory, col1));
//without the filter
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(1, tablesUsed.Count);
//set the filter
queryBuilder.RootFilterContainer = spontContainer;
//this is super sneaky but makes the queryBuilder populate it's Filters property... basically your not supposed to use SqlQueryBuilderHelper for this kind of thing
Console.WriteLine(queryBuilder.SQL);
queryBuilder.ParameterManager.ClearNonGlobals();
//with the filter
tablesUsed = SqlQueryBuilderHelper.GetTablesUsedInQuery(queryBuilder, out primary, null);
Assert.AreEqual(2, tablesUsed.Count);
}
