Metadata-Version: 2.4
Name: datasette-rapidfuzz
Version: 0.1.0
Summary: Datasette plugin adding SQL functions for fuzzy text matching powered by RapidFuzz
Author: Julius Welby
License-Expression: Apache-2.0
Project-URL: Homepage, https://github.com/jwelby/datasette-rapidfuzz
Project-URL: Issues, https://github.com/jwelby/datasette-rapidfuzz/issues
Project-URL: Source, https://github.com/jwelby/datasette-rapidfuzz
Keywords: datasette,datasette-plugin,rapidfuzz,fuzzy-matching,fuzzy-search,string-similarity,sql-functions
Classifier: Development Status :: 4 - Beta
Classifier: Framework :: Datasette
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: Science/Research
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3 :: Only
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Programming Language :: Python :: 3.13
Classifier: Topic :: Database :: Front-Ends
Classifier: Topic :: Text Processing :: Linguistic
Requires-Python: >=3.9
Description-Content-Type: text/markdown
License-File: LICENSE
Requires-Dist: datasette
Requires-Dist: rapidfuzz
Dynamic: license-file

# datasette-rapidfuzz

Datasette plugin adding SQL functions for fuzzy text matching powered by
[RapidFuzz](https://github.com/rapidfuzz/RapidFuzz).

It complements
[datasette-jellyfish](https://github.com/simonw/datasette-jellyfish), which
provides edit-distance and phonetic metrics (Levenshtein, Damerau-Levenshtein,
Jaro, Jaro-Winkler, Soundex, Metaphone). RapidFuzz adds the **token- and
partial-ratio** family — `wratio`, `token_sort_ratio`, `token_set_ratio`,
`partial_ratio` — which tolerate word reordering and partial overlaps that a
plain edit distance cannot express, on a fast C++ core. Use jellyfish for
distance/phonetics, this for ratio-style scoring.

## Installation

Install it into the same environment as Datasette:

```bash
datasette install datasette-rapidfuzz
```

## Usage

This plugin registers five SQLite functions:

```sql
rapidfuzz_ratio(a, b)
rapidfuzz_partial_ratio(a, b)
rapidfuzz_token_sort_ratio(a, b)
rapidfuzz_token_set_ratio(a, b)
rapidfuzz_wratio(a, b)
```

Example:

```sql
select rapidfuzz_wratio('Candollea', 'Candollea 37') as score;
```

Returns:

```text
95.0
```

### Finding the closest matches

To rank rows by similarity to a search term, score each row and sort with
`order by score desc` — the default ascending order returns the *least* similar
rows first. The functions return `null` for a `null` input, and `null` sorts
first when ascending, so a plain `order by score` tends to surface blank rows.
Add `distinct` and filter out nulls to see the closest distinct values rather
than repeated copies of the top hit:

```sql
select distinct
  name,
  rapidfuzz_wratio(name, 'Ginkgo') as score
from things
where name is not null
order by score desc
limit 20;
```

Fuzzy scoring is evaluated row-by-row by SQLite, so on large tables narrow the
rows first (with a `where` clause) or fuzz against a deduplicated helper table:

```sql
create table distinct_names as select distinct name from things;
```

### Finding near-duplicates (self-join)

Comparing a column against itself finds values that are similar but not
identical — likely typos or spelling variants. A self-join is O(n²), so for
anything but a small table you need to **block**: only compare values that
share a cheap key. Blocking on the **first two characters** works well, because
typos and variants almost always differ *inside* a word rather than at the
start, so a variant still shares its opening letters — while two characters keep
each block small:

```sql
with items as (
  select distinct name from things
  where name is not null and substr(name, 1, 2) = 'Ca'
),
pairs as (
  select a.name as a, b.name as b, rapidfuzz_wratio(a.name, b.name) as score
  from items a join items b on a.name < b.name
)
select a, b, score
from pairs
where score >= 90 and score < 100
order by score desc
limit 30;
```

`a.name < b.name` compares each pair once and skips self-pairs; `score < 100`
drops exact matches so only genuine variants remain. Swap the literal `'Ca'`
for `:prefix` to make it an interactive parameter in Datasette.

Blocking trades coverage for speed: a shorter key (`substr(name, 1, 1)`) casts a
wider net but forms far more pairs, while a longer key is faster but misses
variants that differ within the blocked prefix. Raise Datasette's limit for
large blocks (`--setting sql_time_limit_ms 30000`).

To sweep a whole column in one query, move the block into the join and run it
against a deduplicated, **indexed** helper table:

```sql
create table distinct_names as select distinct name from things;
create index idx_block on distinct_names (substr(name, 1, 2), name);

select a.name as a, b.name as b, rapidfuzz_wratio(a.name, b.name) as score
from distinct_names a join distinct_names b
  on substr(a.name, 1, 2) = substr(b.name, 1, 2) and a.name < b.name
where rapidfuzz_wratio(a.name, b.name) >= 90
  and rapidfuzz_wratio(a.name, b.name) < 100
order by score desc;
```

The index lets SQLite seek each block instead of forming the full cross product
— the difference between a query that finishes in seconds and one that times
out. Index a small helper table rather than the source table, so the main
database stays untouched.

## For developers

Clone this repository and install your local copy in editable mode, so your
edits take effect without reinstalling:

```bash
pip install -e . --no-build-isolation
```

Run the tests:

```bash
python -m unittest discover
```
