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