table.search(..., quote=True) parameter and sqlite-utils search --quote option

[deafmute1]

Hi,
Recently got this error:

Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/__init__.py", line 38, in <module>
    start("/home/ethan/git/music-metadata-indexer/sample", "/home/ethan/git/music-metadata-indexer/test.db")
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/__init__.py", line 23, in start
    scanner.build_database()
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/scan.py", line 79, in build_database
    _import_song(self.db, Path(dirpath).joinpath(f), self.logger) 
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/scan.py", line 23, in _import_song
    db.add_song(filepath)
  File "/home/ethan/git/music-metadata-indexer/src/mmindexer/index.py", line 166, in add_song
    for match in self.search("albums", album): 
  File "/home/ethan/git/music-metadata-indexer/env/lib/python3.9/site-packages/sqlite_utils/db.py", line 1625, in search
    cursor = self.db.execute(
  File "/home/ethan/git/music-metadata-indexer/env/lib/python3.9/site-packages/sqlite_utils/db.py", line 243, in execute
    return self.conn.execute(sql, parameters)
sqlite3.OperationalError: fts5: syntax error near "." 

So, the error seems to suggest there was a "." character somewhere in the SQL command that was causing the error. I did a little digging and found this in the docs: https://www.sqlite.org/fts5.html#fts5_strings. "." is one of the many prohibited characters.

My solution was to just strip these out of the query using this line
query = query.translate({e: None for e in itertools.chain(range(0,26), range(27, 48), range(58,65), range(91,95), [96], range(123,128))})

Perhaps this could be included into the table.search() function?

[simonw]

This is a good call. I have a fix for this in Datasette but it's not in sqlite-utils yet: https://github.com/simonw/datasette/blob/adb5b70de5cec3c3dd37184defe606a082c232cf/datasette/utils/__init__.py#L824-L835

[simonw]

I think I'll do this as an optional table.search(..., escape=True) parameter.

Actually I'll do quote=True for consistency with the new db.quote_fts() method.

[simonw]

Also add sqlite-utils search ... --quote option.

[simonw]

sqlite-utils search fixtures.db searchable 'dog"'
Error: malformed MATCH expression: [dog"]

This error message could suggest retrying with --quote.

[simonw]

% sqlite-utils search fixtures.db searchable 'dog"'
Error: malformed MATCH expression: [dog"]

Try running this again with the --quote option

[simonw]

Documentation for table.search(..., quote=True): https://sqlite-utils.datasette.io/en/latest/python-api.html#searching-with-table-search

In the API reference: https://sqlite-utils.datasette.io/en/latest/reference.html#sqlite_utils.db.Table.search

And for the CLI --quote option: https://sqlite-utils.datasette.io/en/latest/cli.html#executing-searches