fts search on a column doesn't work anymore due to escape_fts

[Krazybug]

Hi and first, thank you for this awesome work you make with this projet.

On a db indexed in full text search, I can't query on indexed column anymore.

This request "cauvin language:ita": is running smoothly on a old version of datasette but not on the current version.

Compare the current version query
select uuid, title, authors, year, series, language, formats, publisher, tags, identifiers from summary where rowid in (select rowid from summary_fts where summary_fts match escape_fts(:search)) order by uuid limit 101

To an older version:

select title, authors, series, uuid, language, identifiers, tags, publisher, formats, year, links from summary where rowid in (select rowid from summary_fts where summary_fts match :search) order by uuid limit 101

language is a searchable column but now the search string is known as "cauvin language:ita" literally as a search term. columns are not parsed.

[simonw]

This was a deliberate change from #651.

The _search_colname= alternative argument for doing this still works - compare these two:

• https://latest.datasette.io/fixtures/searchable?_search=dog
• https://latest.datasette.io/fixtures/searchable?_search_tex1=dog

If you want to use advanced search syntax on those pages you can do so using the &_searchmode=raw option - I added better documentation for this the other day: https://datasette.readthedocs.io/en/latest/full_text_search.html#advanced-sqlite-search-queries

Example: https://latest.datasette.io/fixtures/searchable?_search=text1:dog&_searchmode=raw

[Krazybug]

Thank you. Now it's ok with the url

http://localhost:8001/index/summary?_search=language%3Aeng&_sort=title&_searchmode=raw

But I'm not able to manage it in the metadata file. Here is mine (note that the sort column is taken into account)
Here it is:

{
    "databases": {
      "index": {
        "tables": {
            "summary": {
                "sort": "title",
                "searchmode": "raw"
            }
        }
      }
    }
  }

Any idea ?

[simonw]

I added "searchmode": "raw" as a supported option for table metadata in #1389 and released that in Datasette 0.58.