search

simonw / sqlite-utils

Python CLI utility and library for manipulating SQLite databases
https://sqlite-utils.datasette.io
Apache License 2.0
1.63k stars 111 forks source link

Using enable_fts before search term #265

Open prabhur opened 3 years ago

prabhur commented 3 years ago

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.

dracos commented 1 year ago

enable_fts is a function in datasette, not in this repo, which doesn't do any escaping of search terms. It sounds like from https://docs.datasette.io/en/stable/full_text_search.html#advanced-sqlite-search-queries you might want to enable raw searching, as otherwise it's disabled and everything is escaped by default.