spellfix-sqlite

An xcframework build of SQLite's spellfix1 extension for iOS and macOS.

Building

make

This will:

1. Download spellfix.c from the SQLite repository (matching your system's SQLite version)
2. Compile it for arm64-macos, arm64-ios, and arm64-sim (iOS Simulator)
3. Create spellfix.xcframework

Specifying SQLite Version

By default, the build uses your system's SQLite version. To specify a different version:

make SQLITE_VERSION=3.45.0

Usage

Loading the Extension

import SQLite3

var db: OpaquePointer?
sqlite3_open(":memory:", &db)

// Enable extension loading
sqlite3_enable_load_extension(db, 1)

// Load spellfix
let extensionPath = Bundle.main.path(forResource: "libspellfix", ofType: "dylib")
sqlite3_load_extension(db, extensionPath, nil, nil)

Using spellfix1

-- Create a spellfix virtual table
CREATE VIRTUAL TABLE demo USING spellfix1;

-- Insert some words
INSERT INTO demo(word) VALUES ('apple'), ('application'), ('appetite');

-- Find similar words
SELECT word, score FROM demo WHERE word MATCH 'aple' LIMIT 5;

Using spellfix1_editdist for Edit Distance

The spellfix1_editdist(A, B) function calculates the edit distance (Levenshtein distance) between two ASCII strings. This is useful for fuzzy matching against a regular table.

-- Create a table with words
CREATE TABLE words (
    id INTEGER PRIMARY KEY,
    word TEXT NOT NULL
);

-- Create an index on the word column for faster lookups
CREATE INDEX idx_words_word ON words(word);

-- Insert some data
INSERT INTO words (word) VALUES
    ('apple'), ('application'), ('appetite'), ('banana'), ('bandana');

-- Calculate edit distance between a search term and all words
SELECT
    word,
    spellfix1_editdist('aple', word) AS distance
FROM words
ORDER BY distance
LIMIT 5;

-- Find words within a specific edit distance threshold
SELECT
    word,
    spellfix1_editdist('apple', word) AS distance
FROM words
WHERE spellfix1_editdist('apple', word) <= 3
ORDER BY distance;

Note: The spellfix1_editdist function only works with ASCII strings. For unicode support, use the editdist3 function instead.

Index Recommendations

The spellfix1_editdist function is a scalar function that computes distance at query time, so SQLite cannot use a standard B-tree index to optimize the distance calculation itself. However, you should still create indexes for:

1. The word column - Speeds up table scans and any additional WHERE clauses
2. Any columns used in JOINs or additional filters

For large datasets where fuzzy search performance is critical, consider using the spellfix1 virtual table (shown above) instead, as it uses phonetic indexing to quickly find candidate matches.

iOS Considerations

On iOS, dynamic extension loading may be restricted depending on your app's configuration. You may need to:

• Ensure the dylib is properly code-signed
• Include it in your app bundle's Frameworks directory
• Configure your app's entitlements appropriately

Clean

make clean

Releasing

To create a new release with the xcframework attached:

make release VERSION=1.0.0

This will:

1. Build the xcframework for all platforms
2. Create spellfix.xcframework.zip
3. Create and push a git tag
4. Create a GitHub release with auto-generated notes and the zip attached

Prerequisites:

• GitHub CLI (gh) must be installed and authenticated
• You must have push access to the repository

License

The spellfix extension is part of SQLite and is in the public domain.