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