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