Advanced FTS queries not working in DB published using datasette-publish-now

[aborruso]

Hi,
in the sf-trees project, it's possible to use *, NEAR, AND, etc. In example the string melanoxylo*.

I have created my first datasette project, I have enabled full-text search, but I cannot use in example *: the query for vill* (I have the word "villa" and "ville").

I have enabled full-text running:

sqlite-utils enable-fts my.db mytable fieldone fieldtwo

I have published it without any option using this command:

datasette publish now commissioniComunePalermo.db --project=my-database

Thank you very much

[simonw]

I'm really confused by this one.

Here's another Datasette running on Now which doesn't exhibit this bug:

https://datasette-public.now.sh/fixtures?sql=select+pk%2C+text1%2C+text2%2C+[name+with+.+and+spaces]+from+searchable+where+rowid+in+(select+rowid+from+searchable_fts+where+searchable_fts+match+escape_fts(%3Asearch))+order+by+pk+limit+101&search=bar%2A

And yet yours, running here, does: https://my-database.now.sh/commissioniComunePalermo?sql=select+rowid+from+youtube_fts+where+youtube_fts+match+escape_fts(%27sedut*%27)&search=sedut%2A&_trace=1

[simonw]

I thought that maybe your table was FTS4 and the Datasette fixtures table was FTS5, but no - yours is FTS5 too according to the schema at the bottom of https://my-database.now.sh/commissioniComunePalermo/youtube_fts

CREATE VIRTUAL TABLE [youtube_fts] USING FTS5 (
                [#text], [commissione],
                content=[youtube]
            );

[simonw]

The escape_fts() function is applied to user-provided ?_search= queries by design - I don't want users to have to know SQLite FTS syntax in order to use search.

If you want to run a query that takes advantage of FTS syntax you would need to do so using either a completely custom SQL query or using the ?_where= table option, like this:

https://latest.datasette.io/fixtures/searchable?_where=rowid+in+(select+rowid+from+searchable_fts+where+searchable_fts+match+%27bar%2A+NEAR+cat%27)

[simonw]

So: my recommendation is for you to use ?_where= or an entirely custom SQL query if you want to use advanced FTS syntax.

[simonw]

... or use the feature I forgot about, ?_searchmode=raw!

https://datasette.readthedocs.io/en/stable/json_api.html#special-table-arguments

[aborruso]

Hi @simonw and thank you.

So: my recommendation is for you to use ?_where= or an entirely custom SQL query if you want to use advanced FTS syntax.

But if I use ?_where= , I have no result https://my-database.now.sh/commissioniComunePalermo?sql=select+rowid+from+youtube_fts+where+youtube_fts+match+escape_fts%28%27vill*%27%29

Is it a bugged db?
What can I do? Delete it and build again?

I don't want users to have to know SQLite FTS syntax in order to use search.

Ok, me neither. But I see what is possible to do here by default and it's great. I can search:

• Acer AND Red > 1,002 rows
• Acer OR Red > 6,637 rows
• Acer or Red > 0 rows

I would like to use it to build my datasette website. How to have it?

Thank you for this great tool

[simonw]

I'm really confused by that example - I don't know why that's not working for you.

Once we figure out what's wrong with that you can get advanced FTS working on your pages by adding &_searchmode=raw to the querystring in the URL for the table pages.

[simonw]

Huh, here's another bug: I'm trying to download your SQLite database from https://my-database.now.sh/commissioniComunePalermo.db but my request is hanging. Eventually I get this error:

An error occurred with this application.

NO_STATUS_CODE_FROM_FUNCTION

That's another datasette-publish-now bug. I'm going to move this issue to that repo.

[simonw]

Could you make a copy of your commissioniComunePalermo.db file available somewhere so I can take a look at it? Running datasette publish now --public might do the trick - that would let me download it from https://my-database.now.sh/_src (which I can't access at the moment).

[aborruso]

I'm attaching it here. Thank you

commissioniComunePalermo.zip

[aborruso]

Once we figure out what's wrong with that you can get advanced FTS working on your pages by adding &_searchmode=raw to the querystring in the URL for the table pages.

Is there a way to add searchmode=raw to configuration and enable it by default, without adding it every time to the URL? Probably here it's in this way.

Thank you