public void BasicCase()
{
var ex = Assert.Throws <ArgumentException>(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", new DataTable(), 30)));
Assert.IsTrue(ex.Message.StartsWith("The DataTable that you claimed was an ExtractableAggregateResults had zero columns and therefore cannot be cached"));
DataTable dt = new DataTable();
dt.Columns.Add("Col1");
dt.Rows.Add("fishy!");
var ex2 = Assert.Throws <NotSupportedException>(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", dt, 30)));
Assert.IsTrue(
ex2.Message.StartsWith(
"Aggregate ExtractableAggregateCachingTests is not marked as IsExtractable therefore cannot be cached"));
_config.IsExtractable = true;
_config.SaveToDatabase();
//make the underlying column an is extraction identifier
_extractionInformation.IsExtractionIdentifier = true;
_extractionInformation.SaveToDatabase();
AggregateDimension dim = new AggregateDimension(CatalogueRepository, _extractionInformation, _config);
_config.ClearAllInjections();
var ex3 = Assert.Throws <NotSupportedException>(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", dt, 30)));
Assert.IsTrue(
ex3.Message.StartsWith(
"Aggregate ExtractableAggregateCachingTests contains dimensions marked as IsExtractionIdentifier or HashOnDataRelease (Col1)"));
_extractionInformation.IsExtractionIdentifier = false;
_extractionInformation.SaveToDatabase();
_config.ClearAllInjections();
Assert.DoesNotThrow(() => _manager.CommitResults(new CacheCommitExtractableAggregate(_config, "I've got a lovely bunch of coconuts", dt, 30)));
dim.DeleteInDatabase();
using (var con = DataAccessPortal.GetInstance().ExpectServer(QueryCachingDatabaseServer, DataAccessContext.InternalDataProcessing).GetConnection())
{
IHasFullyQualifiedNameToo table = _manager.GetLatestResultsTableUnsafe(_config, AggregateOperation.ExtractableAggregateResults);
con.Open();
using (var cmd = DatabaseCommandHelper.GetCommand("Select * from " + table.GetFullyQualifiedName(), con))
using (var r = cmd.ExecuteReader())
{
Assert.IsTrue(r.Read());
Assert.AreEqual("fishy!", r["Col1"]);
}
}
}
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);
}
/// <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 CommitResults_CreatesTablessuccessfully()
{
DataTable dt = new DataTable();
dt.Columns.Add("MyCol");
dt.Rows.Add("0101010101");
dt.Rows.Add("0201010101");
dt.Rows.Add("0101310101");
//commit it 3 times, should just overwrite
_manager.CommitResults(new CacheCommitIdentifierList(_config, SomeComplexBitOfSqlCode, dt, _myColSpecification, 30));
_manager.CommitResults(new CacheCommitIdentifierList(_config, SomeComplexBitOfSqlCode, dt, _myColSpecification, 30));
_manager.CommitResults(new CacheCommitIdentifierList(_config, SomeComplexBitOfSqlCode, dt, _myColSpecification, 30));
var resultsTableName = _manager.GetLatestResultsTableUnsafe(_config, AggregateOperation.IndexedExtractionIdentifierList);
Assert.AreEqual("IndexedExtractionIdentifierList_AggregateConfiguration" + _config.ID, resultsTableName.GetRuntimeName());
var table = DataAccessPortal.GetInstance()
.ExpectDatabase(QueryCachingDatabaseServer, DataAccessContext.InternalDataProcessing)
.ExpectTable(resultsTableName.GetRuntimeName());
Assert.IsTrue(table.Exists());
var col = table.DiscoverColumn("MyCol");
Assert.IsNotNull(col);
Assert.AreEqual("varchar(10)", col.DataType.SQLType);
using (var con = DataAccessPortal.GetInstance().ExpectServer(QueryCachingDatabaseServer, DataAccessContext.InternalDataProcessing).GetConnection())
{
con.Open();
var dt2 = new DataTable();
SqlDataAdapter da = new SqlDataAdapter("Select * from " + resultsTableName.GetFullyQualifiedName(), (SqlConnection)con);
da.Fill(dt2);
Assert.AreEqual(dt.Rows.Count, dt2.Rows.Count);
con.Close();
}
Assert.IsNotNull(_manager.GetLatestResultsTable(_config, AggregateOperation.IndexedExtractionIdentifierList, SomeComplexBitOfSqlCode));
Assert.IsNull(_manager.GetLatestResultsTable(_config, AggregateOperation.IndexedExtractionIdentifierList, "select name,height,scalecount from fish"));
}
/// <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);
}
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);
}
}
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 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);
}
}