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 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);
}
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 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);
}
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");
}
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);
}
