public override string HackExtractionSQL(string sql, IDataLoadEventListener listener)
{
SetServer();
//call base hacks
sql = base.HackExtractionSQL(sql, listener);
if (_doNotMigrate)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Cohort and Data are on same server so no migration will occur"));
return(sql);
}
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Original (unhacked) SQL was " + sql, null));
//now replace database with tempdb
var extractableCohort = Request.ExtractableCohort;
var extractableCohortSource = extractableCohort.ExternalCohortTable;
var syntaxHelperFactory = new QuerySyntaxHelperFactory();
var sourceSyntax = syntaxHelperFactory.Create(extractableCohortSource.DatabaseType);
var destinationSyntax = syntaxHelperFactory.Create(_server.DatabaseType);
//To replace (in this order)
//Cohort database.table.privateId
//Cohort database.table.releaseId
//Cohort database.table.cohortdefinitionId
//Cohort database.table name
Dictionary <string, string> replacementStrings = new Dictionary <string, string>();
var sourceDb = sourceSyntax.GetRuntimeName(extractableCohortSource.Database);
var sourceTable = sourceSyntax.GetRuntimeName(extractableCohortSource.TableName);
var sourcePrivateId = sourceSyntax.GetRuntimeName(extractableCohort.GetPrivateIdentifier());
var sourceReleaseId = sourceSyntax.GetRuntimeName(extractableCohort.GetReleaseIdentifier());
var sourceCohortDefinitionId = sourceSyntax.GetRuntimeName(extractableCohortSource.DefinitionTableForeignKeyField);
//Swaps the given entity for the same entity but in _tempDb
AddReplacement(replacementStrings, sourceDb, sourceTable, sourcePrivateId, sourceSyntax, destinationSyntax);
AddReplacement(replacementStrings, sourceDb, sourceTable, sourceReleaseId, sourceSyntax, destinationSyntax);
AddReplacement(replacementStrings, sourceDb, sourceTable, sourceCohortDefinitionId, sourceSyntax, destinationSyntax);
AddReplacement(replacementStrings, sourceDb, sourceTable, sourceSyntax, destinationSyntax);
foreach (KeyValuePair <string, string> r in replacementStrings)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Replacing '" + r.Key + "' with '" + r.Value + "'", null));
if (!sql.Contains(r.Key))
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "SQL extraction query string did not contain the text '" + r.Key + "' (which we expected to replace with '" + r.Value + ""));
}
sql = sql.Replace(r.Key, r.Value);
}
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Adjusted (hacked) SQL was " + sql, null));
//replace [MyCohortDatabase].. with [tempdb].. (while dealing with Cohort..Cohort replacement correctly as well as 'Cohort.dbo.Cohort.Fish' correctly)
return(sql);
}
public void EnsureWrapped_MultipleCalls(DatabaseType dbType)
{
var syntax = new QuerySyntaxHelperFactory().Create(dbType);
string once = syntax.EnsureWrapped("ff");
string twice = syntax.EnsureWrapped(once);
Assert.AreEqual(once, twice);
}
/// <summary>
/// Gets an IQuerySyntaxHelper for the <see cref="GetDistinctLiveDatabaseServerType"/> amongst all underlying <see cref="TableInfo"/>. This can be used to assist query building.
/// </summary>
/// <returns></returns>
public IQuerySyntaxHelper GetQuerySyntaxHelper()
{
var f = new QuerySyntaxHelperFactory();
var type = GetDistinctLiveDatabaseServerType();
if (type == null)
{
throw new AmbiguousDatabaseTypeException("Catalogue '" + this + "' has no extractable columns so no Database Type could be determined");
}
return(f.Create(type.Value));
}
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;
}
/// <summary>
/// This produces the SQL that would retrieve the specified dataset columns including any JOINS
///
/// <para>It uses:
/// QueryBuilder and then it adds some custom lines for linking to the cohort</para>
/// </summary>
/// <returns></returns>
public QueryBuilder GetSQLCommandForFullExtractionSet(ExtractDatasetCommand request, out List <ReleaseIdentifierSubstitution> substitutions)
{
if (request.QueryBuilder != null)
{
throw new Exception("Creation of a QueryBuilder from a request can only happen once, to access the results of the creation use the cached answer in the request.QueryBuilder property");
}
if (!request.ColumnsToExtract.Any())
{
throw new Exception("No columns are marked for extraction in this configuration");
}
if (request.ExtractableCohort == null)
{
throw new NullReferenceException("No Cohort selected");
}
var databaseType = request.Catalogue.GetDistinctLiveDatabaseServerType();
if (databaseType == null)
{
throw new NotSupportedException("Catalogue " + request.Catalogue + " did not know what DatabaseType it hosted, how can we extract from it! does it have no TableInfos?");
}
var syntaxHelper = new QuerySyntaxHelperFactory().Create(databaseType.Value);
substitutions = new List <ReleaseIdentifierSubstitution>();
var memoryRepository = new MemoryRepository();
switch (request.ColumnsToExtract.Count(c => c.IsExtractionIdentifier))
{
//no extraction identifiers
case 0: throw new Exception("There are no Columns in this dataset (" + request + ") marked as IsExtractionIdentifier");
//a single extraction identifier e.g. CHI X died on date Y with conditions a,b and c
case 1: substitutions.Add(new ReleaseIdentifierSubstitution(memoryRepository, request.ColumnsToExtract.FirstOrDefault(c => c.IsExtractionIdentifier), request.ExtractableCohort, false, syntaxHelper));
break;
//multiple extraction identifiers e.g. Mother X had Babies A, B, C where A,B and C are all CHIs that must be subbed for ProCHIs
default:
foreach (IColumn columnToSubstituteForReleaseIdentifier in request.ColumnsToExtract.Where(c => c.IsExtractionIdentifier))
{
substitutions.Add(new ReleaseIdentifierSubstitution(memoryRepository, columnToSubstituteForReleaseIdentifier, request.ExtractableCohort, true, syntaxHelper));
}
break;
}
string hashingAlgorithm = _repository.DataExportPropertyManager.GetValue(DataExportProperty.HashingAlgorithmPattern);
if (string.IsNullOrWhiteSpace(hashingAlgorithm))
{
hashingAlgorithm = null;
}
//identify any tables we are supposed to force join to
var forcedJoins = request.SelectedDataSets.SelectedDataSetsForcedJoins;
QueryBuilder queryBuilder = new QueryBuilder("DISTINCT ", hashingAlgorithm, forcedJoins.Select(s => s.TableInfo).ToArray());
queryBuilder.TopX = request.TopX;
queryBuilder.SetSalt(request.Salt.GetSalt());
//add the constant parameters
foreach (ConstantParameter parameter in GetConstantParameters(syntaxHelper, request.Configuration, request.ExtractableCohort))
{
queryBuilder.ParameterManager.AddGlobalParameter(parameter);
}
//add the global parameters
foreach (var globalExtractionFilterParameter in request.Configuration.GlobalExtractionFilterParameters)
{
queryBuilder.ParameterManager.AddGlobalParameter(globalExtractionFilterParameter);
}
//remove the identification column from the query
request.ColumnsToExtract.RemoveAll(c => c.IsExtractionIdentifier);
//add in the ReleaseIdentifier in place of the identification column
queryBuilder.AddColumnRange(substitutions.ToArray());
//add the rest of the columns to the query
queryBuilder.AddColumnRange(request.ColumnsToExtract.Cast <IColumn>().ToArray());
//add the users selected filters
queryBuilder.RootFilterContainer = request.Configuration.GetFilterContainerFor(request.DatasetBundle.DataSet);
ExternalCohortTable externalCohortTable = _repository.GetObjectByID <ExternalCohortTable>(request.ExtractableCohort.ExternalCohortTable_ID);
if (request.ExtractableCohort != null)
{
//the JOIN with the cohort table:
string cohortJoin;
if (substitutions.Count == 1)
{
cohortJoin = " INNER JOIN " + externalCohortTable.TableName + " ON " + substitutions.Single().JoinSQL;
}
else
{
cohortJoin = " INNER JOIN " + externalCohortTable.TableName + " ON " + string.Join(" OR ", substitutions.Select(s => s.JoinSQL));
}
//add the JOIN in after any other joins
queryBuilder.AddCustomLine(cohortJoin, QueryComponent.JoinInfoJoin);
//add the filter cohortID because our new Cohort system uses ID number and a giant combo table with all the cohorts in it we need to say Select XX from XX join Cohort Where Cohort number = Y
queryBuilder.AddCustomLine(request.ExtractableCohort.WhereSQL(), QueryComponent.WHERE);
}
request.QueryBuilder = queryBuilder;
return(queryBuilder);
}
