private MappingSchema SetupFtsMapping(SQLiteFTS type)
{
var ms = new MappingSchema();
ms.GetFluentMappingBuilder()
.Entity <FtsTable>()
.HasTableName(type.ToString() + "_TABLE")
.HasColumn(t => t.text1)
.HasColumn(t => t.text2);
return(ms);
}
public void MatchByColumnSubQueryOptimizationTest([IncludeDataSources(true, TestProvName.AllSQLite)] string context, [Values(SQLiteFTS.FTS3, SQLiteFTS.FTS4)] SQLiteFTS type)
{
using (var db = GetDataContext(context, SetupFtsMapping(type)))
{
var subquery = db.GetTable <FtsTable>().Where(r => Sql.Ext.SQLite().Match(r.text1, "found"));
var query = db.GetTable <FtsTable>().Where(r => subquery.Select(_ => Sql.Ext.SQLite().RowId(_)).Contains(Sql.Ext.SQLite().RowId(r)));
var results = query.ToList();
Assert.AreEqual(1, results.Count);
Assert.AreEqual("record not found", results[0].text1);
Assert.AreEqual("empty", results[0].text2);
}
}
public void MatchByTable([IncludeDataSources(true, TestProvName.AllSQLite)] string context, [Values] SQLiteFTS type)
{
using (var db = GetDataContext(context, SetupFtsMapping(type)))
{
var query = db.GetTable <FtsTable>().Where(r => Sql.Ext.SQLite().Match(r, "something"));
if (type != SQLiteFTS.FTS5)
{
var results = query.ToList();
Assert.AreEqual(1, results.Count);
Assert.AreEqual("looking for something?", results[0].text1);
Assert.AreEqual("found it!", results[0].text2);
}
else
{
var sql = query.ToString();
Assert.That(sql.Contains("[r].[FTS5_TABLE] MATCH 'something'"));
}
}
}
public void Fts3CommandAutoMerge([IncludeDataSources(TestProvName.AllSQLite)] string context, [Values(SQLiteFTS.FTS3, SQLiteFTS.FTS4)] SQLiteFTS type)
{
using (var db = new TestDataConnection(context))
{
db.AddMappingSchema(SetupFtsMapping(type));
db.FTS3AutoMerge(db.GetTable <FtsTable>(), 5);
var tableName = type.ToString() + "_TABLE";
Assert.AreEqual($"INSERT INTO [{tableName}]([{tableName}]) VALUES('automerge=5')", db.LastQuery);
}
}
public void Fts3Snippet6([IncludeDataSources(true, TestProvName.AllSQLite)] string context, [Values(SQLiteFTS.FTS3, SQLiteFTS.FTS4)] SQLiteFTS type)
{
using (var db = GetDataContext(context, SetupFtsMapping(type)))
{
var result = db.GetTable <FtsTable>()
.Where(r => Sql.Ext.SQLite().Match(r, "cool"))
.Select(r => Sql.Ext.SQLite().FTS3Snippet(r, ">", "<", "[zzz]", 1, 1))
.Single();
Assert.AreEqual("[zzz]>Cool<[zzz]", result);
}
}
public void Fts3Snippet4([IncludeDataSources(true, TestProvName.AllSQLite)] string context, [Values(SQLiteFTS.FTS3, SQLiteFTS.FTS4)] SQLiteFTS type)
{
using (var db = GetDataContext(context, SetupFtsMapping(type)))
{
var result = db.GetTable <FtsTable>()
.Where(r => Sql.Ext.SQLite().Match(r, "cool"))
.Select(r => Sql.Ext.SQLite().FTS3Snippet(r, ">", "<", "[zzz]"))
.Single();
Assert.AreEqual("[zzz]3oC drops. >Cool< in the upper portion, minimum temperature 14-16oC and >cool< elsewhere, minimum[zzz]", result);
}
}
public void Fts3Snippet3([IncludeDataSources(true, TestProvName.AllSQLite)] string context, [Values(SQLiteFTS.FTS3, SQLiteFTS.FTS4)] SQLiteFTS type)
{
using (var db = GetDataContext(context, SetupFtsMapping(type)))
{
var result = db.GetTable <FtsTable>()
.Where(r => Sql.Ext.SQLite().Match(r, "looking"))
.Select(r => Sql.Ext.SQLite().FTS3Snippet(r, "->", "<-"))
.Single();
Assert.AreEqual("->looking<- for something?", result);
}
}
public void Fts3MatchInfoWithFormat([IncludeDataSources(true, TestProvName.AllSQLite)] string context, [Values(SQLiteFTS.FTS3, SQLiteFTS.FTS4)] SQLiteFTS type)
{
using (var db = GetDataContext(context, SetupFtsMapping(type)))
{
var query = db.GetTable <FtsTable>()
.Where(r => Sql.Ext.SQLite().Match(r, "found"))
.OrderBy(r => Sql.Ext.SQLite().RowId(r))
.Select(r => new { r.text1, matchInfo = Sql.Ext.SQLite().FTS3MatchInfo(r, "pc") });
var results = query.ToList();
Assert.AreEqual(2, results.Count);
Assert.AreEqual("looking for something?", results[0].text1);
AreEqual(new byte[] { 1, 0, 0, 0, 2, 0, 0, 0 }, results[0].matchInfo);
Assert.AreEqual("record not found", results[1].text1);
AreEqual(new byte[] { 1, 0, 0, 0, 2, 0, 0, 0 }, results[1].matchInfo);
}
}
public void Fts3Offsets([IncludeDataSources(true, TestProvName.AllSQLite)] string context, [Values(SQLiteFTS.FTS3, SQLiteFTS.FTS4)] SQLiteFTS type)
{
using (var db = GetDataContext(context, SetupFtsMapping(type)))
{
var query = db.GetTable <FtsTable>()
.Where(r => Sql.Ext.SQLite().Match(r, "found"))
.OrderBy(r => Sql.Ext.SQLite().RowId(r))
.Select(r => new { r.text1, offsets = Sql.Ext.SQLite().FTS3Offsets(r) });
var results = query.ToList();
Assert.AreEqual(2, results.Count);
Assert.AreEqual("looking for something?", results[0].text1);
Assert.AreEqual("1 0 0 5", results[0].offsets);
Assert.AreEqual("record not found", results[1].text1);
Assert.AreEqual("0 0 11 5", results[1].offsets);
}
}
public void RowId([IncludeDataSources(true, TestProvName.AllSQLite)] string context, [Values] SQLiteFTS type)
{
using (var db = GetDataContext(context, SetupFtsMapping(type)))
{
var query = db.GetTable <FtsTable>().Where(r => Sql.Ext.SQLite().RowId(r) == 3);
if (type != SQLiteFTS.FTS5)
{
var results = query.ToList();
Assert.AreEqual(1, results.Count);
Assert.AreEqual("record not found", results[0].text1);
Assert.AreEqual("empty", results[0].text2);
}
else
{
var sql = query.ToString();
Assert.That(sql.Contains("[r].[rowid] = 3"));
}
}
}