fts5 syntax error when using punctuation

[clausjuhl]

Hi Simon

I get a syntax error when using punctuation or special characters in a fulltext search (using fts5). I created the virtual table using sqlite-utils' "enable-fts"-command.

The same error appears on Niche Museums https://www.niche-museums.com/browse/search?q=park., but works fine in most of your other datasette-examples, e.g. register-of-members-interests https://register-of-members-interests.datasettes.com/regmem-98dc8b7/items?_search=mins.

What am I doing wrong? Many thanks!

[simonw]

I've not yet been able to figure out what the escaping rule are for FTS5 queries.

If we figure out how those work maybe we can bundle them as a custom function?

select ... where docs_fts match fts_escape(:search)

[simonw]

https://stackoverflow.com/a/43756146 says that an escaping mechanism that works is this one:

select * from blah where term match '"bacon" "and" "eggs"'

So split on whitespace and then encapsulate each search term in double quotes.

[clausjuhl]

Hi Simon

Thank you for adding the escape_function, but it does not work on my datasette-installation (0.33). I've added the following file to my datasette-dir: /plugins/sql_functions.py:

from datasette import hookimpl

def escape_fts_query(query):
    bits = query.split()
    return ' '.join('"{}"'.format(bit.replace('"', '')) for bit in bits)

@hookimpl
def prepare_connection(conn):
    conn.create_function("escape_fts_query", 1, escape_fts_query)`

It has no effect on the standard queries to the tables though, as they still produce errors when including any characters like '-', '/', '+' or '?'

Does the function only work when using costum queries, where I can include the escape_fts-function explicitly in the sql-query?

PS. I'm calling datasette with --plugins=plugins, and my other plugins work just fine.
PPS. The fts5 virtual table is created with 'sqlite3' like so:

CREATE VIRTUAL TABLE "cases_fts" USING FTS5( title, subtitle, resume, suggestion, presentation, detail = full, content_rowid = 'id', content = 'cases', tokenize='unicode61', 'remove_diacritics 2', 'tokenchars "-_"' );

Thanks!