public void TestDeletePredictiveIndex()
{
CreateDocument(1, 2, 3, 4, 5);
CreateDocument(6, 7, 8, 9, 10);
var aggregateModel = new AggregateModel();
aggregateModel.RegisterModel();
var model = nameof(AggregateModel);
var input = AggregateModel.CreateInput("numbers");
var prediction = Function.Prediction(model, input);
var sumIndex = IndexBuilder.PredictiveIndex(model, input, "sum");
Db.CreateIndex("SumIndex", sumIndex);
aggregateModel.AllowCalls = false;
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("sum").EqualTo(Expression.Int(15)))) {
var explain = q.Explain();
explain.Contains("USING INDEX SumIndex").Should().BeTrue();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(1);
aggregateModel.NumberOfCalls.Should().Be(2);
}
Db.DeleteIndex("SumIndex");
aggregateModel.Reset();
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("sum").EqualTo(Expression.Int(15)))) {
var explain = q.Explain();
explain.Contains("USING INDEX SumIndex").Should().BeFalse();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(1);
aggregateModel.NumberOfCalls.Should().Be(2);
}
}
public void TestDeleteIndex()
{
TestCreateIndex();
Db.DeleteIndex("index1");
Db.GetIndexes().ShouldBeEquivalentTo(new[] { "index2", "index3" });
Db.DeleteIndex("index2");
Db.GetIndexes().ShouldBeEquivalentTo(new[] { "index3" });
Db.DeleteIndex("index3");
Db.GetIndexes().Should().BeEmpty();
Db.DeleteIndex("dummy");
Db.DeleteIndex("index1");
Db.DeleteIndex("index2");
Db.DeleteIndex("index3");
}
public void TestDeletePredictiveIndexesSharedCache()
{
CreateDocument(1, 2, 3, 4, 5);
CreateDocument(6, 7, 8, 9, 10);
var aggregateModel = new AggregateModel();
aggregateModel.RegisterModel();
var model = nameof(AggregateModel);
var input = AggregateModel.CreateInput("numbers");
var prediction = Function.Prediction(model, input);
var aggIndex = IndexBuilder.PredictiveIndex(model, input, null);
Db.CreateIndex("AggIndex", aggIndex);
var sumIndex = IndexBuilder.PredictiveIndex(model, input, "sum");
Db.CreateIndex("SumIndex", sumIndex);
var avgIndex = IndexBuilder.PredictiveIndex(model, input, "avg");
Db.CreateIndex("AvgIndex", avgIndex);
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("sum").LessThanOrEqualTo(Expression.Int(15)).Or(
prediction.Property("avg").EqualTo(Expression.Int(8))))) {
var explain = q.Explain();
explain.Contains("USING INDEX SumIndex").Should().BeTrue();
explain.Contains("USING INDEX AvgIndex").Should().BeTrue();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(2);
aggregateModel.NumberOfCalls.Should().Be(2);
}
Db.DeleteIndex("SumIndex");
// Note: With only one index, the SQLite optimizer does not utilize the index
// when using an OR expression. So test each query individually.
aggregateModel.Reset();
aggregateModel.AllowCalls = false;
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("sum").EqualTo(Expression.Int(15)))) {
var explain = q.Explain();
explain.Contains("USING INDEX SumIndex").Should().BeFalse();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(1);
aggregateModel.NumberOfCalls.Should().Be(0);
}
aggregateModel.Reset();
aggregateModel.AllowCalls = false;
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("avg").EqualTo(Expression.Int(8)))) {
var explain = q.Explain();
explain.Contains("USING INDEX AvgIndex").Should().BeTrue();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(1);
aggregateModel.NumberOfCalls.Should().Be(0);
}
Db.DeleteIndex("AvgIndex");
for (int i = 0; i < 2; i++)
{
aggregateModel.Reset();
aggregateModel.AllowCalls = i == 1;
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"))
.From(DataSource.Database(Db))
.Where(prediction.Property("avg").EqualTo(Expression.Int(8)))) {
var explain = q.Explain();
explain.Contains("USING INDEX SumIndex").Should().BeFalse();
explain.Contains("USING INDEX AvgIndex").Should().BeFalse();
var rows = VerifyQuery(q, (n, result) => { result.GetArray(0)?.Count.Should().BeGreaterThan(0); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(1);
if (i == 0)
{
aggregateModel.NumberOfCalls.Should().Be(0);
}
else
{
aggregateModel.NumberOfCalls.Should().BeGreaterThan(0);
}
}
Db.DeleteIndex("AggIndex");
}
}
