public async Task QueryGenerator_LocationIds()
{
var locationIds = ListOf(Guid.NewGuid(), Guid.NewGuid());
var locationIdsTempTableEntries = locationIds.Select(id => new IdTempTable(id));
var subjectId = Guid.NewGuid();
await using var context = InMemoryStatisticsDbContext();
var cancellationTokenSource = new CancellationTokenSource();
var cancellationToken = cancellationTokenSource.Token;
var tempTableCreator = new Mock <MatchingObservationsQueryGenerator.ITemporaryTableCreator>();
tempTableCreator
.Setup(s => s.CreateTemporaryTable <MatchedObservation>(context, cancellationToken))
.Returns(Task.CompletedTask);
var locationIdsTempTableReference = new Mock <ITempTableQuery <IdTempTable> >();
locationIdsTempTableReference
.SetupGet(t => t.Name)
.Returns("#LocationTempTable");
tempTableCreator
.Setup(s => s
.CreateTemporaryTableAndPopulate(
context,
locationIdsTempTableEntries,
cancellationToken))
.ReturnsAsync(locationIdsTempTableReference.Object);
var queryGenerator = new MatchingObservationsQueryGenerator
{
TempTableCreator = tempTableCreator.Object
};
var(sql, sqlParameters, tempTableReferences) =
await queryGenerator
.GetMatchingObservationsQuery(
context,
subjectId,
null,
locationIds,
null,
cancellationToken);
VerifyAllMocks(tempTableCreator, locationIdsTempTableReference);
const string expectedSql = @"
INSERT INTO #MatchedObservation
SELECT o.id FROM Observation o
WHERE o.SubjectId = @subjectId
AND (o.LocationId IN (SELECT Id FROM #LocationTempTable))
ORDER BY o.Id;";
Assert.Equal(FormatSql(expectedSql), FormatSql(sql));
sqlParameters.AssertDeepEqualTo(ListOf(new SqlParameter("subjectId", subjectId)));
Assert.Equal(locationIdsTempTableReference.Object, Assert.Single(tempTableReferences));
}
public async Task QueryGenerator_TimePeriodQuery()
{
var subjectId = Guid.NewGuid();
await using var context = InMemoryStatisticsDbContext();
var cancellationTokenSource = new CancellationTokenSource();
var cancellationToken = cancellationTokenSource.Token;
var tempTableCreator = new Mock <MatchingObservationsQueryGenerator.ITemporaryTableCreator>();
tempTableCreator
.Setup(s => s.CreateTemporaryTable <MatchedObservation>(context, cancellationToken))
.Returns(Task.CompletedTask);
var queryGenerator = new MatchingObservationsQueryGenerator
{
TempTableCreator = tempTableCreator.Object
};
var(sql, sqlParameters, tempTableReferences) =
await queryGenerator
.GetMatchingObservationsQuery(
context,
subjectId,
null,
null,
new TimePeriodQuery
{
StartCode = TimeIdentifier.AcademicYear,
StartYear = 2015,
EndCode = TimeIdentifier.AcademicYear,
EndYear = 2017
},
cancellationToken);
VerifyAllMocks(tempTableCreator);
const string expectedSql = @"
INSERT INTO #MatchedObservation
SELECT o.id FROM Observation o
WHERE o.SubjectId = @subjectId
AND (
(o.TimeIdentifier = 'AY' AND o.Year = 2015) OR
(o.TimeIdentifier = 'AY' AND o.Year = 2016) OR
(o.TimeIdentifier = 'AY' AND o.Year = 2017)
)
ORDER BY o.Id;";
Assert.Equal(FormatSql(expectedSql), FormatSql(sql));
sqlParameters.AssertDeepEqualTo(ListOf(new SqlParameter("subjectId", subjectId)));
Assert.Empty(tempTableReferences);
}
public async Task QueryGenerator_FullQuery()
{
var subjectId = Guid.NewGuid();
var filter = new Filter
{
Id = Guid.NewGuid(),
SubjectId = subjectId,
FilterGroups = CreateFilterGroups(5, 5)
};
var contextId = Guid.NewGuid().ToString();
await using (var context = InMemoryStatisticsDbContext(contextId))
{
await context.AddAsync(filter);
await context.SaveChangesAsync();
}
var tempTableCreator = new Mock <MatchingObservationsQueryGenerator.ITemporaryTableCreator>(Strict);
var queryGenerator = new MatchingObservationsQueryGenerator
{
TempTableCreator = tempTableCreator.Object
};
await using (var context = InMemoryStatisticsDbContext(contextId))
{
var cancellationTokenSource = new CancellationTokenSource();
var cancellationToken = cancellationTokenSource.Token;
var selectedFilterItemIds = ListOf(
filter.FilterGroups.ToList()[0].FilterItems.ToList()[0].Id,
filter.FilterGroups.ToList()[0].FilterItems.ToList()[2].Id,
filter.FilterGroups.ToList()[1].FilterItems.ToList()[1].Id);
tempTableCreator
.Setup(s =>
s.CreateTemporaryTable <MatchedObservation>(context, cancellationToken))
.Returns(Task.CompletedTask);
var selectedLocationIds = ListOf(Guid.NewGuid(), Guid.NewGuid());
var selectedLocationIdTempTableEntries = selectedLocationIds
.Select(id => new IdTempTable(id))
.ToList();
var locationIdsTempTableReference = new Mock <ITempTableQuery <IdTempTable> >();
locationIdsTempTableReference
.SetupGet(t => t.Name)
.Returns("#LocationTempTable");
tempTableCreator
.Setup(s =>
s.CreateTemporaryTableAndPopulate(
context,
ItIs.EnumerableSequenceEqualTo(selectedLocationIdTempTableEntries),
cancellationToken))
.ReturnsAsync(locationIdsTempTableReference.Object);
var filterItemIdsTempTableReference = new Mock <ITempTableQuery <IdTempTable> >();
filterItemIdsTempTableReference
.SetupGet(t => t.Name)
.Returns("#FilterTempTable");
tempTableCreator
.Setup(s =>
s.CreateTemporaryTableAndPopulate(
context,
ItIs.ListSequenceEqualTo(
selectedFilterItemIds
.OrderBy(id => id)
.Select(id => new IdTempTable(id))
.ToList()),
cancellationToken))
.ReturnsAsync(filterItemIdsTempTableReference.Object);
var tempTableMocks = ListOf(locationIdsTempTableReference);
tempTableMocks.Add(filterItemIdsTempTableReference);
var(sql, sqlParameters, tempTableReferences) =
await queryGenerator
.GetMatchingObservationsQuery(
context,
subjectId,
selectedFilterItemIds,
selectedLocationIds,
new TimePeriodQuery
{
StartCode = TimeIdentifier.AcademicYear,
StartYear = 2015,
EndCode = TimeIdentifier.AcademicYear,
EndYear = 2017
},
cancellationToken);
VerifyAllMocks(tempTableCreator);
VerifyAllMocks(tempTableMocks.Cast <Mock>().ToArray());
const string expectedSql = @"
INSERT INTO #MatchedObservation
SELECT o.id FROM Observation o
WHERE o.SubjectId = @subjectId
AND (
(o.TimeIdentifier = 'AY' AND o.Year = 2015) OR
(o.TimeIdentifier = 'AY' AND o.Year = 2016) OR
(o.TimeIdentifier = 'AY' AND o.Year = 2017)
)
AND (o.LocationId IN (SELECT Id FROM #LocationTempTable))
AND (
EXISTS (SELECT 1 FROM ObservationFilterItem ofi
WHERE ofi.ObservationId = o.id
AND ofi.FilterItemId IN (SELECT Id FROM #FilterTempTable))
)
ORDER BY o.Id;";
Assert.Equal(FormatSql(expectedSql), FormatSql(sql));
sqlParameters.AssertDeepEqualTo(ListOf(new SqlParameter("subjectId", subjectId)));
var expectedTempTableReferences =
tempTableMocks.Select(mock => mock.Object);
// Assert that the temporary table for the LocationIds, and the 3 temporary tables for the
// 3 Filters' sets of selected FilterItemIds are returned so that the calling code can
// dispose of them as it needs to (the order they are returned in is not important).
Assert.Equal(
expectedTempTableReferences.OrderBy(t => t.GetHashCode()),
tempTableReferences.OrderBy(t => t.GetHashCode()));
}
}
public async Task QueryGenerator_FilterItems()
{
var subjectId = Guid.NewGuid();
var filter1 = new Filter
{
Id = Guid.NewGuid(),
SubjectId = subjectId,
FilterGroups = CreateFilterGroups(5, 5)
};
var filter2 = new Filter
{
Id = Guid.NewGuid(),
SubjectId = subjectId,
FilterGroups = CreateFilterGroups(10)
};
var filter3 = new Filter
{
Id = Guid.NewGuid(),
SubjectId = subjectId,
FilterGroups = CreateFilterGroups(2, 2, 2, 2, 2)
};
var unselectedFilter4 = new Filter
{
Id = Guid.NewGuid(),
SubjectId = subjectId,
FilterGroups = CreateFilterGroups(2)
};
var unrelatedFilter = new Filter
{
Id = Guid.NewGuid(),
SubjectId = Guid.NewGuid(),
FilterGroups = CreateFilterGroups(5)
};
var contextId = Guid.NewGuid().ToString();
await using (var context = InMemoryStatisticsDbContext(contextId))
{
await context.AddRangeAsync(
filter1,
filter2,
filter3,
unselectedFilter4,
unrelatedFilter);
await context.SaveChangesAsync();
}
var tempTableCreator = new Mock <MatchingObservationsQueryGenerator.ITemporaryTableCreator>(Strict);
var queryGenerator = new MatchingObservationsQueryGenerator
{
TempTableCreator = tempTableCreator.Object
};
await using (var context = InMemoryStatisticsDbContext(contextId))
{
var cancellationTokenSource = new CancellationTokenSource();
var cancellationToken = cancellationTokenSource.Token;
// Set up some selected Filter Item Ids from 3 of the 4 Filters on this Subject.
//
// Filter1 has the least Filter Items selected and will therefore appear first
// in the list of EXISTS clauses, in order for it to filter as many table rows down
// as possible before the next EXISTS clause is evaluated.
//
// Filter3 then has the second lowest number of Filter Items selected so it will
// appear as the second EXISTS clause, and finally Filter3 will be the final EXISTS
// clause.
var selectedFilter1FilterItemIds = ListOf(
filter1.FilterGroups.ToList()[0].FilterItems.ToList()[0].Id,
filter1.FilterGroups.ToList()[0].FilterItems.ToList()[2].Id,
filter1.FilterGroups.ToList()[1].FilterItems.ToList()[1].Id);
var selectedFilter2FilterItemIds = ListOf(
filter2.FilterGroups.ToList()[0].FilterItems.ToList()[0].Id,
filter2.FilterGroups.ToList()[0].FilterItems.ToList()[2].Id,
filter2.FilterGroups.ToList()[0].FilterItems.ToList()[4].Id,
filter2.FilterGroups.ToList()[0].FilterItems.ToList()[3].Id,
filter2.FilterGroups.ToList()[0].FilterItems.ToList()[7].Id,
filter2.FilterGroups.ToList()[0].FilterItems.ToList()[6].Id,
filter2.FilterGroups.ToList()[0].FilterItems.ToList()[8].Id);
var selectedFilter3FilterItemIds = ListOf(
filter3.FilterGroups.ToList()[0].FilterItems.ToList()[0].Id,
filter3.FilterGroups.ToList()[0].FilterItems.ToList()[1].Id,
filter3.FilterGroups.ToList()[1].FilterItems.ToList()[0].Id,
filter3.FilterGroups.ToList()[2].FilterItems.ToList()[1].Id,
filter3.FilterGroups.ToList()[3].FilterItems.ToList()[0].Id,
filter3.FilterGroups.ToList()[4].FilterItems.ToList()[1].Id);
// An accidental inclusion of a Filter Item Id that doesn't belong to a
// Filter on this Subject will be ignored.
var invalidUnrelatedFilterFilterItemIds = ListOf(
unrelatedFilter.FilterGroups.ToList()[0].FilterItems.ToList()[0].Id);
var selectFilterItemIds = selectedFilter1FilterItemIds
.Concat(selectedFilter2FilterItemIds)
.Concat(selectedFilter3FilterItemIds)
.Concat(invalidUnrelatedFilterFilterItemIds)
.ToList();
tempTableCreator
.Setup(s =>
s.CreateTemporaryTable <MatchedObservation>(context, cancellationToken))
.Returns(Task.CompletedTask);
var selectedFilterItemsByFilter = ListOf(
selectedFilter1FilterItemIds,
selectedFilter2FilterItemIds,
selectedFilter3FilterItemIds);
var selectedFilterItemTempTableEntriesByFilter = selectedFilterItemsByFilter
.Select(filterItemIds => filterItemIds
.Select(id => new IdTempTable(id))
.ToList())
.ToList();
var filterItemIdTempTableReferences =
selectedFilterItemTempTableEntriesByFilter
.Select(tempTableEntries =>
{
var filterNumber = selectedFilterItemTempTableEntriesByFilter.IndexOf(tempTableEntries) + 1;
var orderedIds = tempTableEntries
.OrderBy(t => t.Id)
.ToList();
var tempTableReference = new Mock <ITempTableQuery <IdTempTable> >();
tempTableReference
.SetupGet(t => t.Name)
.Returns($"#Filter{filterNumber}TempTable");
tempTableCreator
.Setup(s =>
s.CreateTemporaryTableAndPopulate(
context,
ItIs.ListSequenceEqualTo(orderedIds),
cancellationToken))
.ReturnsAsync(tempTableReference.Object);
return(tempTableReference);
})
.ToList();
var(sql, sqlParameters, tempTableReferences) =
await queryGenerator
.GetMatchingObservationsQuery(
context,
subjectId,
selectFilterItemIds,
null,
null,
cancellationToken);
VerifyAllMocks(tempTableCreator);
VerifyAllMocks(filterItemIdTempTableReferences.Cast <Mock>().ToArray());
// Ensure that the EXISTS clauses appear in the expected order in the generated query,
// with the Filter with the least number of Filter Items chosen being the first.
const string expectedSql = @"
INSERT INTO #MatchedObservation
SELECT o.id FROM Observation o
WHERE o.SubjectId = @subjectId
AND (
EXISTS (SELECT 1 FROM ObservationFilterItem ofi
WHERE ofi.ObservationId = o.id
AND ofi.FilterItemId IN (SELECT Id FROM #Filter1TempTable)) AND
EXISTS (SELECT 1 FROM ObservationFilterItem ofi
WHERE ofi.ObservationId = o.id
AND ofi.FilterItemId IN (SELECT Id FROM #Filter3TempTable)) AND
EXISTS (SELECT 1 FROM ObservationFilterItem ofi
WHERE ofi.ObservationId = o.id
AND ofi.FilterItemId IN (SELECT Id FROM #Filter2TempTable))
)
ORDER BY o.Id;";
Assert.Equal(FormatSql(expectedSql), FormatSql(sql));
sqlParameters.AssertDeepEqualTo(ListOf(new SqlParameter("subjectId", subjectId)));
var expectedTempTableReferences =
filterItemIdTempTableReferences.Select(mock => mock.Object);
// Assert that the temporary table for the LocationIds, and the 3 temporary tables for the
// 3 Filters' sets of selected FilterItemIds are returned so that the calling code can
// dispose of them as it needs to (the order they are returned in is not important).
Assert.Equal(
expectedTempTableReferences.OrderBy(t => t.GetHashCode()),
tempTableReferences.OrderBy(t => t.GetHashCode()));
}
}
