simonw / simonw/sqlite-utils

Using enable_fts before search term

Open
#265 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
Python
Stars
2.2k
Forks
172
Avg merge
9m
Merged PRs (30d)
1

Description

Many thanks for the sqlite-utils suite of utilities. Has made my life much much easier.
I used this to create a table and enable FTS. All works fine. The datasette utility detects FTS and shows a text box. Searching for a term using that interface works well.

However, when I start to use features by following https://www.sqlite.org/fts5.html section "3. Full-text Query Syntax" I seem to run into issues that I suspect is due to escape_fts wrapper function.

As an example, if i search for the term "^குகை" on the text box in datasette it produces 140 results. However, when i tweak the query produced by datasette to not use "escape_fts" it produces 5 results.

Similarly, when I try to restrict the search to a single column in FTS using a spec like {title : ^குகை} it returns no rows. The same thing pulls results when used without escape_fts. The text in the table is in Tamil language and the search term is a Tamil word.

   ...
    where
      posts_fts match escape_fts(:search)

vs

    ...
    where
      posts_fts match (:search)

Any ideas why? How can I get the benefits of both escaping as well as utilizing different facets of providing / controlling search terms? Thanks.

Contributor guide

Open the contributing guide

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

Start by comparing the escape_fts(:search) and (:search) queries shown in the issue, then read SQLite FTS5's “Full-text Query Syntax” section and trace how Datasette constructs the search query. Done means determining whether escape_fts causes the Tamil and column-restricted searches to fail, with the expected behavior and a reproducible test or documented conclusion.

Written by the indexing model from the issue text.

Assessment

Tech stack
python, sql
Domain
databases, search
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.