Add FTS snippet functionality

[achansonjr]

SQLite when using the FTS3/4 extensions can support the concept of a snippet. Essentially the snippet is a subset of the indexed column, that has html bold tags surrounding the matched terms.

Source information here: https://www.sqlite.org/fts3.html#section_4_2

So using the example from the above link, a snippet query it might look something like:

SELECT snippet(text) FROM text WHERE text MATCH 'cold';

I have successfully used the VirtualTable implementation to retrieve matching records using the .match(string: String) function, but would like to be able to select the snippet as well.

My current implementation attempt is:

let db = try? Connection("table.database")
let indexTable = VirtualTable("fts_index")
let guidField = Expression<String>("object_guid")
let contentsField = Expression<String>("contents")
let snippet = Expression<String>("snippet(contents)")
let _ = try? db?.run(indexTable.create(.FTS4(guidField, contentsField)))

let matchQuery: QueryType =indexTable.select(snippet, guidField, contentsField).match("cold")
let results = try? db?.prepare(matchQuery)

if let concreteResults = results {
  let _ = concreteResults.flatMap( { 
    print("\($0[guidField]), \($0[snippet]), \($0[contentsField])")
  })
}

I get what I expect for both guidField and contentsField, yet the result of snippet is something puzzling.

3771714D-19AB-464B-80E2-6C5A798D8104, snippet(Expression<String>(template: "\"contents\"", bindings: [])), This is a cold record

I imagine I just need a custom expression, but I am not sure how to implement custom SQLite functions, or else I may just be missing it in the docs.

Thanks for any help, and I would be glad once I know how to implement this for both the snippet and matchInfo capability in SQLite.

Thanks, awesome library and really enjoying using it.

[stephencelis]

Expressions are quoted by default. What you want is to use is Expression.init(literal:) or Expression.init(_:_:): https://github.com/stephencelis/SQLite.swift/blob/master/SQLite/Typed/Expression.swift#L38-L40

I think this snippet helper should work:

func snippet(column: Expression<String>) -> Expression<String> {
    return Expression("snippet(\(column.template))", column.bindings)
}

[achansonjr]

That worked perfectly.

However I think I had a mistake in the earlier explanation, but it would have produced the same issue.

The actual implementation for the snippetHelper is as such:

func snippetWrapper(column: Expression<String>, tableName: String) -> Expression<String> {
    return Expression("snippet(\(tableName))", column.bindings)
  }

In use:

let db = try? Connection("table.database")
let indexTable = VirtualTable("fts_index")
let guidField = Expression<String>("object_guid")
let contentsField = Expression<String>("contents")
let snippet = snippetWrapper(contentsField, tableName: "fts_index")
let _ = try? db?.run(indexTable.create(.FTS4(guidField, contentsField)))

let matchQuery: QueryType =indexTable.select(snippet, guidField, contentsField).match("cold")
let results = try? db?.prepare(matchQuery)

if let concreteResults = results {
  let _ = concreteResults.flatMap( { 
    print("\($0[guidField]), \($0[snippet]), \($0[contentsField])")
  })
}

func snippetWrapper(column: Expression<String>, tableName: String) -> Expression<String> {
    return Expression("snippet(\(tableName))", column.bindings)
  }

Output to console:

3771714D-19AB-464B-80E2-6C5A798D8104, This is a cold record, <b>...</b>This is a <b>cold</b> record<b>...</b>

As an aside, if you have a place you would like these to go, I could make one for snippet, and matchInfo and submit a PR.

[jberkel]

I'm not sure where the best place for these would be. I'm currently working with SQLite + FTS5 and the method signatures are a little bit different there.

All FTS functions (both for FTS4 and 5) always implicitly need a reference to the virtual table. So maybe a wrapper:

struct FTS4Functions {
   let VirtualTable: table
   func snippet(startMatch: String, endMatch: String, ellipses: String, column: Int, tokens: Int) -> Expression<String> 
   // matchInfo etc.
}  

Or a bunch of static functions which all take the VirtualTable as first parameter.

struct FTS4 {
 static func snippet(table: VirtualTable, startMatch: String, .....) -> Expression<String>
}

Thoughts?

[stephanheilner]

@jberkel Did you ever come up with a working solution to get the snippet (or highlight) with FTS5?

[stephanheilner]

Figured it out:

Expression<String>(literal: "snippet(fts_table, 1, '<b>', '</b>', '…', 35)")
Expression<String>(literal: "highlight(fts_table, 1, '<b>', '</b>')")