Issue: Spellfix1 SQLite Extension?
Author: FaizShah
State: closed
Created: 2019-08-13 18:03:15

Hi all, 

I'm using the FTS extension for SQLite and I would like to test out Spellfix1 for FTS spelling correction in peewee. 

What would be the easiest/best way to add the Spellfix1 extension for SQLite in peewee?

Here is some example code using it without peewee for reference:

```
# Build the extension.
!wget https://sqlite.org/2016/sqlite-src-3110100.zip
!unzip sqlite-src-3110100.zip
!gcc -shared -fPIC -Wall -Isqlite-src-3110100 sqlite-src-3110100/ext/misc/spellfix.c -o spellfix.so
# Load the extension
import sqlite3
db = sqlite3.connect(':memory:')
db.enable_load_extension(True)
db.load_extension('./spellfix')

# Create an FTS table and load some data in here.
cur.execute('''CREATE VIRTUAL TABLE value_fts USING fts5(value)''')
cur.execute('''INSERT INTO value_fts (rowid, value) SELECT id, value FROM data''')

# Create a table with just the vocab from the FTS5 table.
cur.execute('''CREATE VIRTUAL TABLE IF NOT EXISTS value_fts_vocab USING fts5vocab('value_fts', 'row') ''')
# Set up spellfix1 extension with the editcost algo.
cur.execute('''CREATE TABLE IF NOT EXISTS editcost(
  iLang INT,   -- The language ID
  cFrom TEXT,  -- Convert text from this
  cTo   TEXT,  -- Convert text into this
  iCost INT    -- The cost of doing the conversion
)''')
cur.execute('''CREATE VIRTUAL TABLE IF NOT EXISTS value_fts_vocab_spl USING spellfix1(edit_cost_table=editcost)''')
# Load the vocab in the spellfix table.
cur.execute('''INSERT INTO value_fts_vocab_spl (word) SELECT term FROM value_fts_vocab''')
# Use the spellfix extension to correct the spelling from the FTS vocab.
cur.execute('''SELECT *, distance, matchlen FROM value_fts_vocab_spl WHERE word MATCH lower('Genr') ''')
cur.fetchall()
```

Here's some starter code

```python
from peewee import *
from playhouse.sqlite_ext import *

db = CSqliteExtDatabase('/tmp/spellfix.db')
db.load_extension('/path/to/spellfix1')

class WordList(VirtualModel):
    # See http://www.sqlite.org/spellfix1.html
    word = VirtualField(TextField)
    rank = VirtualField(IntegerField)
    distance = VirtualField(IntegerField)
    langid = VirtualField(IntegerField)
    score = VirtualField(FloatField)
    matchlen = VirtualField(IntegerField)
    phonehash = VirtualField(TextField)
    top = VirtualField(IntegerField)
    scope = VirtualField(FloatField)
    soundslike = VirtualField(TextField)

    class Meta:
        database = db
        extension_module = 'spellfix1'
        primary_key = False

WordList.create_table()

with db.atomic():
    with open('/path/to/dictionary') as fh:
        for word in fh:
            WordList.insert({'word': word.strip()}).execute()

words = (
    'speling',
    'heling',
    'lhelping',
)
for word in words:
    query = (WordList
             .select(WordList.word, WordList.score, WordList.distance,
                     WordList.phonehash, WordList.matchlen)
             .where(NodeList((WordList.word, SQL('MATCH'), word)) &
                    (WordList.top == 5))
             .dicts())
    print('WORD: %s' % word)
    for result in query:
        print(result)
```

Thank you Charles, much appreciated. 

Your blog has also been a great resource on closure.c, json1 and FTS5. Thanks for all you do!

Since it was a bit cumbersome to get the spellfix extension along with the python packages I depend on, I made a pip-installable version of it: https://github.com/karlb/sqlite-spellfix. Maybe that is helpful for you, too.

Excellent contribution @karlb thank you! Maybe we could make all the extensions pip installable like "pip install sqlite-extensions[fts5,closures]" using [extras_require](https://stackoverflow.com/a/60307740). I will take a look this weekend :)