public void CacheSingleTask(ICacheableTask cacheableTask, ExternalDatabaseServer queryCachingServer)
{
//if it is already cached don't inception cache
var sql = Tasks[cacheableTask].CountSQL;
if (sql.Trim().StartsWith(CachedAggregateConfigurationResultsManager.CachingPrefix))
{
return;
}
var manager = new CachedAggregateConfigurationResultsManager(queryCachingServer);
var explicitTypes = new List <DatabaseColumnRequest>();
AggregateConfiguration configuration = cacheableTask.GetAggregateConfiguration();
try
{
//the identifier column that we read from
ColumnInfo identifierColumnInfo = configuration.AggregateDimensions.Single(c => c.IsExtractionIdentifier).ColumnInfo;
var destinationDataType = GetDestinationType(identifierColumnInfo.Data_type, cacheableTask, queryCachingServer);
explicitTypes.Add(new DatabaseColumnRequest(identifierColumnInfo.GetRuntimeName(), destinationDataType));
}
catch (Exception e)
{
throw new Exception("Error occurred trying to find the data type of the identifier column when attempting to submit the result data table to the cache", e);
}
CacheCommitArguments args = cacheableTask.GetCacheArguments(sql, Tasks[cacheableTask].Identifiers, explicitTypes.ToArray());
manager.CommitResults(args);
}
public void CacheSingleTask(ICacheableTask cacheableTask, ExternalDatabaseServer queryCachingServer)
{
//if it is already cached don't inception cache
var sql = Tasks[cacheableTask].CountSQL;
if (sql.Trim().StartsWith(CachedAggregateConfigurationResultsManager.CachingPrefix))
{
return;
}
var manager = new CachedAggregateConfigurationResultsManager(queryCachingServer);
var explicitTypes = new List <DatabaseColumnRequest>();
AggregateConfiguration configuration = cacheableTask.GetAggregateConfiguration();
try
{
//the identifier column that we read from
var identifiers = configuration.AggregateDimensions.Where(c => c.IsExtractionIdentifier).ToArray();
if (identifiers.Length != 1)
{
throw new Exception(string.Format(
"There were {0} columns in the configuration marked IsExtractionIdentifier:{1}",
identifiers.Length, string.Join(",", identifiers.Select(i => i.GetRuntimeName()))));
}
var identifierDimension = identifiers[0];
ColumnInfo identifierColumnInfo = identifierDimension.ColumnInfo;
var destinationDataType = GetDestinationType(identifierColumnInfo.Data_type, cacheableTask, queryCachingServer);
explicitTypes.Add(new DatabaseColumnRequest(identifierDimension.GetRuntimeName(), destinationDataType));
//make other non transform Types have explicit values
foreach (AggregateDimension d in configuration.AggregateDimensions)
{
if (d != identifierDimension)
{
//if the user has not changed the SelectSQL and the SelectSQL of the original column is not a transform
if (d.ExtractionInformation.SelectSQL.Equals(d.SelectSQL) && !d.ExtractionInformation.IsProperTransform())
{
//then use the origin datatype
explicitTypes.Add(new DatabaseColumnRequest(d.GetRuntimeName(), GetDestinationType(d.ExtractionInformation.ColumnInfo.Data_type, cacheableTask, queryCachingServer)));
}
}
}
}
catch (Exception e)
{
throw new Exception("Error occurred trying to find the data type of the identifier column when attempting to submit the result data table to the cache", e);
}
CacheCommitArguments args = cacheableTask.GetCacheArguments(sql, Tasks[cacheableTask].Identifiers, explicitTypes.ToArray());
manager.CommitResults(args);
}
public void CreateEntities()
{
_cata =
new Catalogue(CatalogueRepository, "CachedAggregateConfigurationResultsManagerTests");
_config
=
new AggregateConfiguration(CatalogueRepository, _cata, "CachedAggregateConfigurationResultsManagerTests");
_manager = new CachedAggregateConfigurationResultsManager(QueryCachingDatabaseServer);
}
/// <summary>
/// Creates a new result for a single <see cref="AggregateConfiguration"/> or <see cref="CohortAggregateContainer"/>
/// </summary>
/// <param name="cacheServer"></param>
/// <param name="childProvider"></param>
/// <param name="helper"></param>
/// <param name="customise"></param>
public CohortQueryBuilderResult(ExternalDatabaseServer cacheServer, ICoreChildProvider childProvider, CohortQueryBuilderHelper helper, QueryBuilderCustomArgs customise)
{
CacheServer = cacheServer;
ChildProvider = childProvider;
Helper = helper;
Customise = customise;
if (cacheServer != null)
{
CacheManager = new CachedAggregateConfigurationResultsManager(CacheServer);
}
}
protected override void SetUp()
{
base.SetUp();
_cata =
new Catalogue(CatalogueRepository, "CachedAggregateConfigurationResultsManagerTests");
_config
=
new AggregateConfiguration(CatalogueRepository, _cata, "CachedAggregateConfigurationResultsManagerTests");
_manager = new CachedAggregateConfigurationResultsManager(QueryCachingDatabaseServer);
}
public override void Run(AggregateConfiguration ac, CachedAggregateConfigurationResultsManager cache, CancellationToken token)
{
token.ThrowIfCancellationRequested();
// The user of RDMP will have configured ac as either patient index table or normal cohort aggregate
if (ac.IsJoinablePatientIndexTable())
{
// user expects multiple columns from the API
RunAsPatientIndexTable(ac, cache, token);
}
else
{
// user expects only a single linkage identifier to be returned by the API
RunAsIdentifierList(ac, cache, token);
}
}
private void RunAsPatientIndexTable(AggregateConfiguration ac, CachedAggregateConfigurationResultsManager cache, CancellationToken token)
{
using DataTable dt = new DataTable();
dt.Columns.Add("chi", typeof(string));
dt.Columns.Add("dateOfBirth", typeof(DateTime));
dt.Columns.Add("dateOfDeath", typeof(DateTime));
// generate a list of random chis + date of birth/death
var pc = new PersonCollection();
pc.GeneratePeople(GetNumberToGenerate(ac), new Random());
foreach (var p in pc.People)
{
dt.Rows.Add(p.CHI, p.DateOfBirth, p.DateOfDeath ?? (object)DBNull.Value);
}
SubmitPatientIndexTable(dt, ac, cache, true);
}
private void RunAsIdentifierList(AggregateConfiguration ac, CachedAggregateConfigurationResultsManager cache, CancellationToken token)
{
var pc = new PersonCollection();
var requiredNumber = GetNumberToGenerate(ac);
var rand = new Random();
pc.GeneratePeople(requiredNumber, rand);
var set = new HashSet <string>(pc.People.Select(p => p.CHI));
// there may be duplicates, if so we need to bump up the number to match the required count
while (set.Count < requiredNumber)
{
pc.GeneratePeople(1, rand);
set.Add(pc.People[0].CHI);
}
// generate a list of random chis
SubmitIdentifierList("chi", set, ac, cache);
}
public void CreateEntities()
{
_cata =
new Catalogue(CatalogueRepository, "ExtractableAggregateCachingTests");
_table = new TableInfo(CatalogueRepository, "ExtractableAggregateCachingTests");
_columnInfo = new ColumnInfo(CatalogueRepository, "Col1", "varchar(1000)", _table);
_catalogueItem = new CatalogueItem(CatalogueRepository, _cata, "Col1");
_extractionInformation = new ExtractionInformation(CatalogueRepository, _catalogueItem, _columnInfo, "Col1");
_config
=
new AggregateConfiguration(CatalogueRepository, _cata, "ExtractableAggregateCachingTests");
_manager = new CachedAggregateConfigurationResultsManager(QueryCachingDatabaseServer);
}
private int GetCacheCount()
{
var cacheManager = new CachedAggregateConfigurationResultsManager(_cic.QueryCachingServer);
int found = 0;
foreach (var ag in _cic.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively())
{
// just incase they changed the role or something wierd we should nuke all it's roles
found += cacheManager.GetLatestResultsTableUnsafe(ag, AggregateOperation.IndexedExtractionIdentifierList) != null ? 1 : 0;
found += cacheManager.GetLatestResultsTableUnsafe(ag, AggregateOperation.JoinableInceptionQuery) != null ? 1 : 0;
}
foreach (var joinable in _cic.GetAllJoinables())
{
// just incase they changed the role or something wierd we should nuke all it's roles
found += cacheManager.GetLatestResultsTableUnsafe(joinable.AggregateConfiguration, AggregateOperation.IndexedExtractionIdentifierList) != null ? 1 : 0;
found += cacheManager.GetLatestResultsTableUnsafe(joinable.AggregateConfiguration, AggregateOperation.JoinableInceptionQuery) != null ? 1 : 0;
}
return(found);
}
private ICompileable RunAllTasksWithRunner(CohortCompiler cohortCompiler, IDataLoadEventListener listener)
{
if (ClearCohortIdentificationConfigurationCacheBeforeRunning)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Clearing Cohort Identifier Cache"));
var cacheManager = new CachedAggregateConfigurationResultsManager(_cohortIdentificationConfiguration.QueryCachingServer);
cohortCompiler.AddAllTasks(false);
foreach (var cacheable in cohortCompiler.Tasks.Keys.OfType <ICacheableTask>())
{
cacheable.ClearYourselfFromCache(cacheManager);
}
}
var runner = new CohortCompilerRunner(cohortCompiler, Timeout);
runner.RunSubcontainers = false;
runner.PhaseChanged += (s, e) => listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "CohortCompilerRunner entered Phase '" + runner.ExecutionPhase + "'"));
return(runner.Run(_cancelGlobalOperations.Token));
}
/// <summary>
/// Creates a new result for a single <see cref="AggregateConfiguration"/> or <see cref="CohortAggregateContainer"/>
/// </summary>
/// <param name="cacheServer"></param>
/// <param name="childProvider"></param>
/// <param name="helper"></param>
/// <param name="customise"></param>
/// <param name="cancellationToken"></param>
public CohortQueryBuilderResult(ExternalDatabaseServer cacheServer, ICoreChildProvider childProvider, CohortQueryBuilderHelper helper, QueryBuilderCustomArgs customise, CancellationToken cancellationToken)
{
CacheServer = cacheServer;
ChildProvider = childProvider;
Helper = helper;
Customise = customise;
CancellationToken = cancellationToken;
if (cacheServer != null)
{
CacheManager = new CachedAggregateConfigurationResultsManager(CacheServer);
try
{
PluginCohortCompilers = new PluginCohortCompilerFactory(cacheServer.CatalogueRepository.MEF).CreateAll();
}
catch (Exception ex)
{
throw new Exception("Failed to build list of IPluginCohortCompilers", ex);
}
}
}
public override void Execute()
{
base.Execute();
var cacheManager = new CachedAggregateConfigurationResultsManager(_cic.QueryCachingServer);
int deleted = 0;
foreach (var ag in _cic.RootCohortAggregateContainer.GetAllAggregateConfigurationsRecursively())
{
// just incase they changed the role or something wierd we should nuke all it's roles
deleted += cacheManager.DeleteCacheEntryIfAny(ag, AggregateOperation.IndexedExtractionIdentifierList) ? 1 : 0;
deleted += cacheManager.DeleteCacheEntryIfAny(ag, AggregateOperation.JoinableInceptionQuery) ? 1:0;
}
foreach (var joinable in _cic.GetAllJoinables())
{
// just incase they changed the role or something wierd we should nuke all it's roles
deleted += cacheManager.DeleteCacheEntryIfAny(joinable.AggregateConfiguration, AggregateOperation.IndexedExtractionIdentifierList) ? 1 : 0;
deleted += cacheManager.DeleteCacheEntryIfAny(joinable.AggregateConfiguration, AggregateOperation.JoinableInceptionQuery) ? 1 : 0;
}
Show("Cache Entries Cleared", $"Deleted {deleted} cache entries");
}
private void ClearCacheFor(ICacheableTask[] tasks)
{
var manager = new CachedAggregateConfigurationResultsManager(_queryCachingServer);
int successes = 0;
foreach (ICacheableTask t in tasks)
{
try
{
t.ClearYourselfFromCache(manager);
Compiler.CancelTask(t, true);
successes++;
}
catch (Exception exception)
{
ExceptionViewer.Show("Could not clear cache for task " + t, exception);
}
}
RecreateAllTasks();
}
private void btnCache_Click(object sender, EventArgs e)
{
try
{
CachedAggregateConfigurationResultsManager cacheManager = GetCacheManager();
var args = new CacheCommitExtractableAggregate(AggregateConfiguration, QueryEditor.Text, (DataTable)dataGridView1.DataSource, Timeout);
cacheManager.CommitResults(args);
var result = cacheManager.GetLatestResultsTable(AggregateConfiguration, AggregateOperation.ExtractableAggregateResults, QueryEditor.Text);
if (result == null)
{
throw new NullReferenceException("CommitResults passed but GetLatestResultsTable returned false (when we tried to refetch the table name from the cache)");
}
MessageBox.Show("DataTable successfully submitted to:" + result.GetFullyQualifiedName());
btnClearFromCache.Enabled = true;
}
catch (Exception exception)
{
ExceptionViewer.Show(exception);
}
}
/// <summary>
/// Submits the <paramref name="results"/> of calling your API to the cache ready for joining
/// against other datasets as a patient index table. Only use this method if you must return
/// multiple columns.
/// </summary>
/// <param name="results"></param>
/// <param name="aggregate"></param>
/// <param name="cache"></param>
/// <param name="knownTypes">If your DataTable is properly Typed (i.e. columns in <paramref name="results"/> have assigned Types)
/// then pass true. If everything is a string and you want types to be assigned for these for querying later pass false.</param>
protected void SubmitPatientIndexTable(DataTable results, AggregateConfiguration aggregate, CachedAggregateConfigurationResultsManager cache, bool knownTypes)
{
// The data table has to go into the database so we need to know max length of strings, decimal precision etc
Dictionary <string, Guesser> guessers = new Dictionary <string, Guesser>();
foreach (DataColumn col in results.Columns)
{
// if the user told us the datatypes were right then assume they are honest otherwise make it up as you go along
var g = knownTypes ? new Guesser(new DatabaseTypeRequest(col.DataType)) : new Guesser();
// measure data being submitted
g.AdjustToCompensateForValues(col);
guessers.Add(col.ColumnName, g);
}
// this is how you commit the results to the cache
var args = new CacheCommitJoinableInceptionQuery(aggregate, GetDescription(aggregate), results,
guessers.Select(k => new DatabaseColumnRequest(k.Key, k.Value.Guess)).ToArray()
, 5000);
cache.CommitResults(args);
}
public string GetSQLForAggregate(AggregateConfiguration aggregate, int tabDepth, bool isJoinAggregate = false, string overrideSelectList = null, string overrideLimitationSQL = null, int topX = -1)
{
string toReturn = "";
CountOfSubQueries++;
string tabs = "";
string tabplusOne = "";
if (tabDepth != -1)
{
GetTabs(tabDepth, out tabs, out tabplusOne);
}
//make sure it is a valid configuration
string reason;
if (!aggregate.IsAcceptableAsCohortGenerationSource(out reason))
{
throw new QueryBuildingException("Cannot generate a cohort using AggregateConfiguration " + aggregate + " because:" + reason);
}
//get the extraction identifier (method IsAcceptableAsCohortGenerationSource will ensure this linq returns 1 so no need to check again)
AggregateDimension extractionIdentifier = aggregate.AggregateDimensions.Single(d => d.IsExtractionIdentifier);
//create a builder but do it manually, we care about group bys etc or count(*) even
AggregateBuilder builder;
//we are getting SQL for a cohort identification aggregate without a HAVING/count statement so it is actually just 'select patientIdentifier from tableX'
if (string.IsNullOrWhiteSpace(aggregate.HavingSQL) && string.IsNullOrWhiteSpace(aggregate.CountSQL))
{
//select list is the extraction identifier
string selectList;
if (!isJoinAggregate)
{
selectList = extractionIdentifier.SelectSQL + (extractionIdentifier.Alias != null ? " " + extractionIdentifier.Alias: "");
}
else
{
//unless we are also including other columns because this is a patient index joinable inception query
selectList = string.Join("," + Environment.NewLine + tabs,
aggregate.AggregateDimensions.Select(e => e.SelectSQL + (e.Alias != null ? " " + e.Alias : ""))); //joinable patient index tables have patientIdentifier + 1 or more other columns
}
if (overrideSelectList != null)
{
selectList = overrideSelectList;
}
string limitationSQL = overrideLimitationSQL ?? "distinct";
//select list is either [chi] or [chi],[mycolumn],[myexcitingcol] (in the case of a patient index table)
builder = new AggregateBuilder(limitationSQL, selectList, aggregate, aggregate.ForcedJoins);
//false makes it skip them in the SQL it generates (it uses them only in determining JOIN requirements etc but since we passed in the select SQL explicitly it should be the equivellent of telling the query builder to generate a regular select
if (!isJoinAggregate)
{
builder.AddColumn(extractionIdentifier, false);
}
else
{
builder.AddColumnRange(aggregate.AggregateDimensions.ToArray(), false);
}
}
else
{
if (overrideSelectList != null)
{
throw new NotSupportedException("Cannot override Select list on aggregates that have HAVING / Count SQL configured in them");
}
builder = new AggregateBuilder("distinct", aggregate.CountSQL, aggregate, aggregate.ForcedJoins);
//add the extraction information and do group by it
if (!isJoinAggregate)
{
builder.AddColumn(extractionIdentifier, true);
}
else
{
builder.AddColumnRange(aggregate.AggregateDimensions.ToArray(), true);//it's a joinable inception query (See JoinableCohortAggregateConfiguration) - these are allowed additional columns
}
builder.DoNotWriteOutOrderBy = true;
}
if (topX != -1)
{
builder.AggregateTopX = new SpontaneouslyInventedAggregateTopX(new MemoryRepository(), topX, AggregateTopXOrderByDirection.Descending, null);
}
AddJoinablesToBuilder(builder, aggregate, tabDepth);
//set the where container
builder.RootFilterContainer = aggregate.RootFilterContainer;
string builderSqlVerbatimForCheckingAgainstCache = null;
if (CacheServer != null)
{
builderSqlVerbatimForCheckingAgainstCache = GetSqlForBuilderForCheckingAgainstCache(builder);
}
//we will be harnessing the parameters via ImportAndElevate so do not add them to the SQL directly
builder.DoNotWriteOutParameters = true;
var builderSqlWithoutParameters = builder.SQL;
//if we have caching
if (CacheServer != null)
{
CachedAggregateConfigurationResultsManager manager = new CachedAggregateConfigurationResultsManager(CacheServer);
var existingTable = manager.GetLatestResultsTable(aggregate, isJoinAggregate? AggregateOperation.JoinableInceptionQuery: AggregateOperation.IndexedExtractionIdentifierList, builderSqlVerbatimForCheckingAgainstCache);
//if we have the answer already
if (existingTable != null)
{
//reference the answer table
CountOfCachedSubQueries++;
toReturn += tabplusOne + CachedAggregateConfigurationResultsManager.CachingPrefix + aggregate.Name + @"*/" + Environment.NewLine;
toReturn += tabplusOne + "select * from " + existingTable.GetFullyQualifiedName() + Environment.NewLine;
return(toReturn);
}
//we do not have an uptodate answer available in the cache :(
}
//get the SQL from the builder (for the current configuration) - without parameters
string currentBlock = builderSqlWithoutParameters;
//import parameters unless caching was used
Dictionary <string, string> renameOperations;
ParameterManager.ImportAndElevateResolvedParametersFromSubquery(builder.ParameterManager, out renameOperations);
//rename in the SQL too!
foreach (KeyValuePair <string, string> kvp in renameOperations)
{
currentBlock = ParameterCreator.RenameParameterInSQL(currentBlock, kvp.Key, kvp.Value);
}
//tab in the current block
toReturn += tabplusOne + currentBlock.Replace("\r\n", "\r\n" + tabplusOne);
return(toReturn);
}
public abstract void ClearYourselfFromCache(CachedAggregateConfigurationResultsManager manager);
public void CohortIdentificationConfiguration_Join_PatientIndexTable()
{
DataTable header = new DataTable();
header.Columns.Add("ID");
header.Columns.Add("Chi");
header.Columns.Add("Age");
header.Columns.Add("Date");
header.Columns.Add("Healthboard");
header.PrimaryKey = new [] { header.Columns["ID"] };
header.Rows.Add("1", "0101010101", 50, new DateTime(2001, 1, 1), "T");
header.Rows.Add("2", "0202020202", 50, new DateTime(2002, 2, 2), "T");
var hTbl = From.CreateTable("header", header);
var cata = Import(hTbl, out TableInfo hTi, out _);
cata.Name = "My Combo Join Catalogue";
cata.SaveToDatabase();
var scripter = new MasterDatabaseScriptExecutor(To);
var patcher = new QueryCachingPatcher();
scripter.CreateAndPatchDatabase(patcher, new AcceptAllCheckNotifier());
var edsCache = new ExternalDatabaseServer(CatalogueRepository, "Cache", new QueryCachingPatcher());
edsCache.SetProperties(To);
DataTable results = new DataTable();
results.Columns.Add("Header_ID");
results.Columns.Add("TestCode");
results.Columns.Add("Result");
results.Rows.Add("1", "HBA1C", 50);
results.Rows.Add("1", "ECOM", "Hi fellas");
results.Rows.Add("1", "ALB", 100);
results.Rows.Add("2", "ALB", 50);
var rTbl = From.CreateTable("results", results);
var importer = new TableInfoImporter(CatalogueRepository, rTbl);
importer.DoImport(out TableInfo rTi, out ColumnInfo[] rColInfos);
var fe = new ForwardEngineerCatalogue(rTi, rColInfos, true);
fe.ExecuteForwardEngineering(cata);
//Should now be 1 Catalogue with all the columns (tables will have to be joined to build the query though)
Assert.AreEqual(8, cata.GetAllExtractionInformation(ExtractionCategory.Core).Length);
var ji = new JoinInfo(CatalogueRepository,
rTi.ColumnInfos.Single(ci => ci.GetRuntimeName().Equals("Header_ID", StringComparison.CurrentCultureIgnoreCase)),
hTi.ColumnInfos.Single(ci => ci.GetRuntimeName().Equals("ID", StringComparison.CurrentCultureIgnoreCase)),
ExtractionJoinType.Right,
null
);
//setup a cic that uses the cache
var cic = new CohortIdentificationConfiguration(CatalogueRepository, "MyCic");
cic.CreateRootContainerIfNotExists();
cic.QueryCachingServer_ID = edsCache.ID;
cic.SaveToDatabase();
//create a patient index table that shows all the times that they had a test in any HB (with the HB being part of the result set)
var acPatIndex = new AggregateConfiguration(CatalogueRepository, cata, "My PatIndes");
var eiChi = cata.GetAllExtractionInformation(ExtractionCategory.Core).Single(ei => ei.GetRuntimeName().Equals("Chi"));
eiChi.IsExtractionIdentifier = true;
acPatIndex.CountSQL = null;
eiChi.SaveToDatabase();
acPatIndex.AddDimension(eiChi);
acPatIndex.AddDimension(cata.GetAllExtractionInformation(ExtractionCategory.Core).Single(ei => ei.GetRuntimeName().Equals("Date")));
acPatIndex.AddDimension(cata.GetAllExtractionInformation(ExtractionCategory.Core).Single(ei => ei.GetRuntimeName().Equals("Healthboard")));
cic.EnsureNamingConvention(acPatIndex);
var joinable = new JoinableCohortAggregateConfiguration(CatalogueRepository, cic, acPatIndex);
Assert.IsTrue(acPatIndex.IsCohortIdentificationAggregate);
Assert.IsTrue(acPatIndex.IsJoinablePatientIndexTable());
var compiler = new CohortCompiler(cic);
var runner = new CohortCompilerRunner(compiler, 50);
var cancellation = new System.Threading.CancellationToken();
runner.Run(cancellation);
//they should not be executing and should be completed
Assert.IsFalse(compiler.Tasks.Any(t => t.Value.IsExecuting));
Assert.AreEqual(Phase.Finished, runner.ExecutionPhase);
var manager = new CachedAggregateConfigurationResultsManager(edsCache);
var cacheTableName = manager.GetLatestResultsTableUnsafe(acPatIndex, AggregateOperation.JoinableInceptionQuery);
Assert.IsNotNull(cacheTableName, "No results were cached!");
var cacheTable = To.ExpectTable(cacheTableName.GetRuntimeName());
//chi, Date and TestCode
Assert.AreEqual(3, cacheTable.DiscoverColumns().Length);
//healthboard should be a string
Assert.AreEqual(typeof(string), cacheTable.DiscoverColumn("Healthboard").DataType.GetCSharpDataType());
/* Query Cache contains this:
*
* Chi Date Healthboard
* 0101010101 2001-01-01 00:00:00.0000000 T
* 0202020202 2002-02-02 00:00:00.0000000 T
*/
Assert.AreEqual(2, cacheTable.GetRowCount());
//Now we could add a new AggregateConfiguration that uses the joinable!
}
public override void ClearYourselfFromCache(CachedAggregateConfigurationResultsManager manager)
{
manager.DeleteCacheEntryIfAny(Joinable.AggregateConfiguration, AggregateOperation.JoinableInceptionQuery);
}
/// <summary> /// Override to fetch the results from your API. /// </summary> /// <param name="ac">Stores any configuration information about what query to to execute on your API</param> /// <param name="cache">Where to store results. Note you can use helper method <see cref="SubmitIdentifierList"/> instead /// of using this directly</param> /// <param name="token">Check this token for cancellation regularly if your API call takes a while to complete</param> public abstract void Run(AggregateConfiguration ac, CachedAggregateConfigurationResultsManager cache, CancellationToken token);
public void TestGettingAggregateJustFromConfig_DistinctCHISelect()
{
CachedAggregateConfigurationResultsManager manager = new CachedAggregateConfigurationResultsManager(externalDatabaseServer);
cohortIdentificationConfiguration.QueryCachingServer_ID = externalDatabaseServer.ID;
cohortIdentificationConfiguration.SaveToDatabase();
cohortIdentificationConfiguration.CreateRootContainerIfNotExists();
cohortIdentificationConfiguration.RootCohortAggregateContainer.AddChild(aggregate1, 0);
CohortQueryBuilder builder = new CohortQueryBuilder(cohortIdentificationConfiguration, null);
try
{
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData]
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builder.SQL));
var server = queryCacheDatabase.Server;
using (var con = server.GetConnection())
{
con.Open();
var da = server.GetDataAdapter(builder.SQL, con);
var dt = new DataTable();
da.Fill(dt);
manager.CommitResults(new CacheCommitIdentifierList(aggregate1,
string.Format(@"/*cic_{0}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea].dbo.[BulkData]", cohortIdentificationConfiguration.ID), dt, _chiColumnSpecification, 30));
}
CohortQueryBuilder builderCached = new CohortQueryBuilder(cohortIdentificationConfiguration, null);
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"
(
/*Cached:cic_{0}_UnitTestAggregate1*/
select * from [" + queryCacheDatabase.GetRuntimeName() + "]..[IndexedExtractionIdentifierList_AggregateConfiguration" + aggregate1.ID + @"]
)
", cohortIdentificationConfiguration.ID)),
CollapseWhitespace(builderCached.SQL));
}
finally
{
cohortIdentificationConfiguration.RootCohortAggregateContainer.RemoveChild(aggregate1);
}
}
/// <summary>
/// Submits the resulting <paramref name="enumerable"/> list to the query <paramref name="cache"/> as the result
/// of executing the API call of the <paramref name="aggregate"/>
/// </summary>
/// <typeparam name="T">Type of the identifiers, must be a basic value type supported by DBMS e.g. string, int etc</typeparam>
/// <param name="identifierName"></param>
/// <param name="enumerable"></param>
/// <param name="aggregate"></param>
/// <param name="cache"></param>
protected void SubmitIdentifierList <T>(string identifierName, IEnumerable <T> enumerable, AggregateConfiguration aggregate, CachedAggregateConfigurationResultsManager cache)
{
var g = new Guesser(new DatabaseTypeRequest(typeof(T)));
// generate random chi numbers
using var dt = new DataTable();
dt.Columns.Add(identifierName, typeof(T));
foreach (var p in enumerable)
{
dt.Rows.Add(p);
g.AdjustToCompensateForValue(p);
}
// this is how you commit the results to the cache
var args = new CacheCommitIdentifierList(aggregate, GetDescription(aggregate), dt,
new DatabaseColumnRequest(identifierName, g.Guess, false), 5000);
cache.CommitResults(args);
}
public void JoinablesWithCache()
{
string queryCachingDatabaseName = To.GetRuntimeName();
_queryCachingDatabase = To;
var builder = new CohortQueryBuilder(aggregate1, null);
//make aggregate 2 a joinable
var joinable2 = new JoinableCohortAggregateConfiguration(CatalogueRepository, cohortIdentificationConfiguration, aggregate2);
joinable2.AddUser(aggregate1);
//make aggregate 2 have an additional column (dtCreated)
var anotherCol = aggregate2.Catalogue.GetAllExtractionInformation(ExtractionCategory.Any).Single(e => e.GetRuntimeName().Equals("dtCreated"));
aggregate2.AddDimension(anotherCol);
MasterDatabaseScriptExecutor scripter = new MasterDatabaseScriptExecutor(_queryCachingDatabase);
scripter.CreateAndPatchDatabase(new QueryCachingPatcher(), new AcceptAllCheckNotifier());
var queryCachingDatabaseServer = new ExternalDatabaseServer(CatalogueRepository, queryCachingDatabaseName, null);
queryCachingDatabaseServer.SetProperties(_queryCachingDatabase);
//make the builder use the query cache we just set SetUp
builder.CacheServer = queryCachingDatabaseServer;
try
{
var builderForCaching = new CohortQueryBuilder(aggregate2, null, true);
var cacheDt = new DataTable();
using (SqlConnection con = (SqlConnection)Database.Server.GetConnection())
{
con.Open();
SqlDataAdapter da = new SqlDataAdapter(new SqlCommand(builderForCaching.SQL, con));
da.Fill(cacheDt);
}
var cacheManager = new CachedAggregateConfigurationResultsManager(queryCachingDatabaseServer);
cacheManager.CommitResults(new CacheCommitJoinableInceptionQuery(aggregate2, builderForCaching.SQL, cacheDt, null, 30));
try
{
Console.WriteLine(builder.SQL);
using (var con = (SqlConnection)Database.Server.GetConnection())
{
con.Open();
var dbReader = new SqlCommand(builder.SQL, con).ExecuteReader();
//can read at least one row
Assert.IsTrue(dbReader.Read());
}
string expectedTableAlias = "ix" + joinable2.ID;
//after joinables
Assert.AreEqual(
CollapseWhitespace(
string.Format(
@"/*cic_{2}_UnitTestAggregate1*/
SELECT
distinct
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi]
FROM
[" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData]
LEFT Join (
/*Cached:cic_{2}_UnitTestAggregate2*/
select * from [{3}]..[JoinableInceptionQuery_AggregateConfiguration{1}]
){0}
on [" + TestDatabaseNames.Prefix + @"ScratchArea]..[BulkData].[chi] = {0}.chi",
expectedTableAlias, //{0}
aggregate2.ID, //{1}
cohortIdentificationConfiguration.ID, //{2}
queryCachingDatabaseName) //{3}
), CollapseWhitespace(builder.SQL));
}
finally
{
joinable2.Users[0].DeleteInDatabase();
joinable2.DeleteInDatabase();
}
}
finally
{
queryCachingDatabaseServer.DeleteInDatabase();
DiscoveredServerICanCreateRandomDatabasesAndTablesOn.ExpectDatabase(queryCachingDatabaseName).Drop();
}
}
public override void ClearYourselfFromCache(CachedAggregateConfigurationResultsManager manager)
{
manager.DeleteCacheEntryIfAny(Aggregate, AggregateOperation.IndexedExtractionIdentifierList);
}
