The spellfix1 documentation actually tells you how to do this. From the Overview section:
If you intend to use this virtual table in cooperation with an FTS4 table (for spelling correction of search terms) then you might extract the vocabulary using an fts4aux table:
INSERT INTO demo(word) SELECT term FROM search_aux WHERE col='*';
The SELECT term from search_aux WHERE col='*' statement extracts all the indexed tokens.
Connecting this with your examples, where mytable2 is your fts4 virtual table, you can create a fts4aux table and insert those tokens into your mytable3 spellfix1 table with:
CREATE VIRTUAL TABLE mytable2_terms USING fts4aux(mytable2);
INSERT INTO mytable3(word) SELECT term FROM mytable2_terms WHERE col='*';
You probably want to further qualify that query to skip any terms already inserted into spellfix1, otherwise you end up with double entries:
INSERT INTO mytable3(word)
SELECT term FROM mytable2_terms
WHERE col='*' AND
term not in (SELECT word from mytable3_vocab);
Now you can use mytable3 to map misspelled words to corrected tokens, then use those corrected tokens in a MATCH query againsts mytable2.
Depending on your neads, this may mean you need to do your own token handling and query building; there is no exposed fts4 query syntax parser. So your two-token search string would need to be split, each token run through the spellfix1 table to map to existing tokens, and then those tokens fed to the fts4 query.
Ignoring SQL syntax to handle this, using Python to do the splitting is easy enough:
def spellcheck_terms(conn, terms):
cursor = conn.cursor()
base_spellfix = """
SELECT :term{0} as term, word FROM spellfix1data
WHERE word MATCH :term{0} and top=1
"""
terms = terms.split()
params = {"term{}".format(i): t for i, t in enumerate(terms, 1)}
query = " UNION ".join([
base_spellfix.format(i + 1) for i in range(len(params))])
cursor.execute(query, params)
correction_map = dict(cursor)
return " ".join([correction_map.get(t, t) for t in terms])
def spellchecked_search(conn, terms):
corrected_terms = spellcheck_terms(conn, terms)
cursor = conn.cursor()
fts_query = 'SELECT * FROM mytable2 WHERE mytable2 MATCH ?'
cursor.execute(fts_query, (corrected_terms,))
return cursor.fetchall()
This then returns [('All the Carmichael numbers',)] for spellchecked_search(db, "NUMMBER carmickaeel").
Keeping the spellcheck handling in Python then allows you to support more complex FTS queries as needed; you may have to reimplement the expression parser to do so, but at least Python gives you the tools to do just that.
A complete example, packaging up the above approach in a class, which simply extract terms as alphanumeric character sequences (which, by my reading of the expression syntax specs, suffices):
import re
import sqlite3
import sys
class FTS4SpellfixSearch(object):
def __init__(self, conn, spellfix1_path):
self.conn = conn
self.conn.enable_load_extension(True)
self.conn.load_extension(spellfix1_path)
def create_schema(self):
self.conn.executescript(
"""
CREATE VIRTUAL TABLE IF NOT EXISTS fts4data
USING fts4(description text);
CREATE VIRTUAL TABLE IF NOT EXISTS fts4data_terms
USING fts4aux(fts4data);
CREATE VIRTUAL TABLE IF NOT EXISTS spellfix1data
USING spellfix1;
"""
)
def index_text(self, *text):
cursor = self.conn.cursor()
with self.conn:
params = ((t,) for t in text)
cursor.executemany("INSERT INTO fts4data VALUES (?)", params)
cursor.execute(
"""
INSERT INTO spellfix1data(word)
SELECT term FROM fts4data_terms
WHERE col='*' AND
term not in (SELECT word from spellfix1data_vocab)
"""
)
# fts3 / 4 search expression tokenizer
# no attempt is made to validate the expression, only
# to identify valid search terms and extract them.
# the fts3/4 tokenizer considers any alphanumeric ASCII character
# and character in the range U+0080 and over to be terms.
if sys.maxunicode == 0xFFFF:
# UCS2 build, keep it simple, match any UTF-16 codepoint 0080 and over
_fts4_expr_terms = re.compile(u"[a-zA-Z0-9\u0080-\uffff]+")
else:
# UCS4
_fts4_expr_terms = re.compile(u"[a-zA-Z0-9\u0080-\U0010FFFF]+")
def _terms_from_query(self, search_query):
"""Extract search terms from a fts3/4 query
Returns a list of terms and a template such that
template.format(*terms) reconstructs the original query.
terms using partial* syntax are ignored, as you can't distinguish
between a misspelled prefix search that happens to match existing
tokens and a valid spelling that happens to have 'near' tokens in
the spellfix1 database that would not otherwise be matched by fts4
"""
template, terms, lastpos = [], [], 0
for match in self._fts4_expr_terms.finditer(search_query):
token, (start, end) = match.group(), match.span()
# skip columnname: and partial* terms by checking next character
ismeta = search_query[end:end + 1] in {":", "*"}
# skip digits if preceded by "NEAR/"
ismeta = ismeta or (
token.isdigit() and template and template[-1] == "NEAR"
and "/" in search_query[lastpos:start])
if token not in {"AND", "OR", "NOT", "NEAR"} and not ismeta:
# full search term, not a keyword, column name or partial*
terms.append(token)
token = "{}"
template += search_query[lastpos:start], token
lastpos = end
template.append(search_query[lastpos:])
return terms, "".join(template)
def spellcheck_terms(self, search_query):
cursor = self.conn.cursor()
base_spellfix = """
SELECT :term{0} as term, word FROM spellfix1data
WHERE word MATCH :term{0} and top=1
"""
terms, template = self._terms_from_query(search_query)
params = {"term{}".format(i): t for i, t in enumerate(terms, 1)}
query = " UNION ".join(
[base_spellfix.format(i + 1) for i in range(len(params))]
)
cursor.execute(query, params)
correction_map = dict(cursor)
return template.format(*(correction_map.get(t, t) for t in terms))
def search(self, search_query):
corrected_query = self.spellcheck_terms(search_query)
cursor = self.conn.cursor()
fts_query = "SELECT * FROM fts4data WHERE fts4data MATCH ?"
cursor.execute(fts_query, (corrected_query,))
return {
"terms": search_query,
"corrected": corrected_query,
"results": cursor.fetchall(),
}
and an interactive demo using the class:
>>> db = sqlite3.connect(":memory:")
>>> fts = FTS4SpellfixSearch(db, './spellfix')
>>> fts.create_schema()
>>> fts.index_text("All the Carmichael numbers") # your example
>>> from pprint import pprint
>>> pprint(fts.search('NUMMBER carmickaeel'))
{'corrected': 'numbers carmichael',
'results': [('All the Carmichael numbers',)],
'terms': 'NUMMBER carmickaeel'}
>>> fts.index_text(
... "They are great",
... "Here some other numbers",
... )
>>> pprint(fts.search('here some')) # edgecase, multiple spellfix matches
{'corrected': 'here some',
'results': [('Here some other numbers',)],
'terms': 'here some'}
>>> pprint(fts.search('NUMMBER NOT carmickaeel')) # using fts4 query syntax
{'corrected': 'numbers NOT carmichael',
'results': [('Here some other numbers',)],
'terms': 'NUMMBER NOT carmickaeel'}
Or, it could be used with FTS4 to do full-text search using potentially misspelled words.but I can't find a way to make it work. Could you post an example? – Galingale"All the Carmichael numbers"with a query"NUMMBER carmickaeel"(i.e. with both 1) not all the words 2) spelling mistakes) is the Holy Graal in the field of searching text in a database. So having a ready-to-use Sqlite code example for this would be super interesting, and would allow a Google-like user experience for search. – Galingale