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