public void TestPredictiveIndexOnValues()
{
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 index = IndexBuilder.PredictiveIndex(model, input, "sum");
Db.CreateIndex("SumIndex", index);
aggregateModel.AllowCalls = false;
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"),
SelectResult.Expression(prediction.Property("sum")).As("sum"))
.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.GetInt(1).Should().Be(15); });
aggregateModel.Error.Should().BeNull();
rows.Should().Be(1);
aggregateModel.NumberOfCalls.Should().Be(2);
}
}
public void TestValueIndexMultipleValues()
{
CreateDocument(1, 2, 3, 4, 5);
CreateDocument(6, 7, 8, 9, 10);
var aggregateModel = new AggregateModel();
aggregateModel.RegisterModel();
var input = AggregateModel.CreateInput("numbers");
var sumPrediction = Function.Prediction(aggregateModel.Name, input).Property("sum");
var avgPrediction = Function.Prediction(aggregateModel.Name, input).Property("avg");
var sumIndex = IndexBuilder.ValueIndex(ValueIndexItem.Expression(sumPrediction));
Db.CreateIndex("SumIndex", sumIndex);
var avgIndex = IndexBuilder.ValueIndex(ValueIndexItem.Expression(avgPrediction));
Db.CreateIndex("AvgIndex", avgIndex);
using (var q = QueryBuilder.Select(SelectResult.Expression(sumPrediction).As("s"),
SelectResult.Expression(avgPrediction).As("a"))
.From(DataSource.Database(Db))
.Where(sumPrediction.LessThanOrEqualTo(Expression.Int(15)).Or(avgPrediction.EqualTo(Expression.Int(8))))) {
var explain = q.Explain();
explain.IndexOf("USING INDEX SumIndex").Should().NotBe(-1, "because the sum index should be used");
explain.IndexOf("USING INDEX AvgIndex").Should().NotBe(-1, "because the average index should be used");
var numRows = VerifyQuery(q, (n, r) =>
{
r.Should().Match <Result>(x => x.GetLong(0) == 15 || x.GetLong(1) == 8);
});
numRows.Should().Be(2);
}
}
public void TestCreateIndex()
{
Db.GetIndexes().Should().BeEmpty();
var fName = Expression.Property("firstName");
var lName = Expression.Property("lastName");
var fNameItem = ValueIndexItem.Property("firstName");
var lNameItem = ValueIndexItem.Expression(lName);
var index1 = IndexBuilder.ValueIndex(fNameItem, lNameItem);
Db.CreateIndex("index1", index1);
var detailItem = FullTextIndexItem.Property("detail");
var index2 = IndexBuilder.FullTextIndex(detailItem);
Db.CreateIndex("index2", index2);
var detailItem2 = FullTextIndexItem.Property("es-detail");
var index3 = IndexBuilder.FullTextIndex(detailItem2).IgnoreAccents(true).SetLanguage("es");
Db.CreateIndex("index3", index3);
Db.GetIndexes().ShouldBeEquivalentTo(new[] { "index1", "index2", "index3" });
}
public void TestValueIndex()
{
CreateDocument(1, 2, 3, 4, 5);
CreateDocument(6, 7, 8, 9, 10);
var aggregateModel = new AggregateModel();
aggregateModel.RegisterModel();
var input = AggregateModel.CreateInput("numbers");
var sumPrediction = Function.Prediction(aggregateModel.Name, input).Property("sum");
var index = IndexBuilder.ValueIndex(ValueIndexItem.Expression(sumPrediction));
Db.CreateIndex("SumIndex", index);
using (var q = QueryBuilder.Select(SelectResult.Property("numbers"),
SelectResult.Expression(sumPrediction))
.From(DataSource.Database(Db))
.Where(sumPrediction.EqualTo(Expression.Int(15)))) {
q.Explain().IndexOf("USING INDEX SumIndex").Should()
.NotBe(-1, "because the query should make use of the index");
var numRows = VerifyQuery(q, (n, r) =>
{
var numbers = r.GetArray(0).Cast <long>().ToList();
var sum = r.GetLong(1);
sum.Should().Be(numbers.Sum());
});
numRows.Should().Be(1);
aggregateModel.NumberOfCalls.Should().Be(2,
"because the value should be cached and not call the prediction function again");
}
}
private int FullTextSearch()
{
_indexFTSBench.Start();
var nameExpr = Expression.Property("Name");
var index = Index.FullTextIndex().On(FullTextIndexItem.Expression(nameExpr));
Db.CreateIndex("nameFTS", index);
_indexFTSBench.Stop();
var ARTIST = Expression.Property("Artist");
var ALBUM = Expression.Property("Album");
var NAME = Expression.Property("Name");
var results = new List <string>();
using (var q = Query.Select(SelectResult.Expression(ARTIST), SelectResult.Expression(ALBUM),
SelectResult.Expression(NAME))
.From(DataSource.Database(Db))
.Where(NAME.Match("'Rock'"))
.OrderBy(Ordering.Property("Artist"), Ordering.Property("Album"))) {
_queryFTSBench.Start();
using (var rows = q.Execute()) {
foreach (var row in rows)
{
results.Add(row.GetString(2));
}
}
}
_queryFTSBench.Stop();
results.Count.Should().Be(30);
return(results.Count);
}
public void TestCreateSameIndexTwice()
{
var item = ValueIndexItem.Expression(Expression.Property("firstName"));
var index = IndexBuilder.ValueIndex(item);
Db.CreateIndex("myindex", index);
Db.CreateIndex("myindex", index);
Db.GetIndexes().ShouldBeEquivalentTo(new[] { "myindex" });
}
private void CreateArtistsIndex()
{
_indexArtistsBench.Start();
var collation = Collation.Unicode().IgnoreCase(true).IgnoreAccents(true);
var artist = Expression.Property("Artist").Collate(collation);
var comp = Expression.Property("Compilation");
var index = Index.ValueIndex().On(ValueIndexItem.Expression(artist), ValueIndexItem.Expression(comp));
Db.CreateIndex("byArtist", index);
_indexArtistsBench.Stop();
}
public void TestConcurrentCreateNCreateIndexDB()
{
const int nDocs = 1000;
var exp1 = new WaitAssert();
var ignore = exp1.RunAssertAsync(() =>
{
CreateDocs(nDocs, "Create").ToList();
});
Db.CreateIndex("sentence", IndexBuilder.FullTextIndex(FullTextIndexItem.Property("sentence")));
exp1.WaitForResult(TimeSpan.FromSeconds(60));
}
public void TestValueIndexCompoundValues()
{
CreateDocument(1, 2, 3, 4, 5);
CreateDocument(6, 7, 8, 9, 10);
var aggregateModel = new AggregateModel();
aggregateModel.RegisterModel();
var input = Expression.Dictionary(new Dictionary <string, object>
{
["numbers"] = Expression.Property("numbers")
});
var sumPrediction = Function.Prediction(aggregateModel.Name, input).Property("sum");
var avgPrediction = Function.Prediction(aggregateModel.Name, input).Property("avg");
var index = IndexBuilder.ValueIndex(ValueIndexItem.Expression(sumPrediction),
ValueIndexItem.Expression(avgPrediction));
Db.CreateIndex("SumAvgIndex", index);
aggregateModel.AllowCalls = false;
using (var q = QueryBuilder.Select(SelectResult.Expression(sumPrediction).As("s"),
SelectResult.Expression(avgPrediction).As("a"))
.From(DataSource.Database(Db))
.Where(sumPrediction.EqualTo(Expression.Int(15)).And(avgPrediction.EqualTo(Expression.Int(3))))) {
var explain = q.Explain();
explain.IndexOf("USING INDEX SumAvgIndex").Should().NotBe(-1, "because the sum index should be used");
var numRows = VerifyQuery(q, (n, r) =>
{
r.GetLong(0).Should().Be(15);
r.GetLong(1).Should().Be(3);
});
numRows.Should().Be(1);
aggregateModel.NumberOfCalls.Should().Be(4);
}
}
public void TestCreateSameNameIndexes()
{
var fName = Expression.Property("firstName");
var lName = Expression.Property("lastName");
var fNameItem = ValueIndexItem.Expression(fName);
var fNameIndex = IndexBuilder.ValueIndex(fNameItem);
Db.CreateIndex("myindex", fNameIndex);
var lNameItem = ValueIndexItem.Expression(lName);
var lNameIndex = IndexBuilder.ValueIndex(lNameItem);
Db.CreateIndex("myindex", lNameIndex);
Db.GetIndexes().ShouldBeEquivalentTo(new[] { "myindex" }, "because lNameIndex should overwrite fNameIndex");
var detailItem = FullTextIndexItem.Property("detail");
var detailIndex = IndexBuilder.FullTextIndex(detailItem);
Db.CreateIndex("myindex", detailIndex);
Db.GetIndexes().ShouldBeEquivalentTo(new[] { "myindex" }, "because detailIndex should overwrite lNameIndex");
}
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");
}
}
