fts5 tables are not auto-detected and hidden

[keturn]

I set up a Datasette instance and was following the docs on full-text search.

When I used fts4, datasette automatically hid the FTS tables and added the FTS search box where appropriate, but when I changed to fts5 it no longer does either.

If I manually set fts_table for a view, then search does work as expected.

My table and view creation code looks like this:

connection.execute("""CREATE TABLE IF NOT EXISTS
    captions(image_key text PRIMARY KEY, caption text NOT NULL)
""")
 
connection.execute("""CREATE VIRTUAL TABLE
    captions_fts USING
    fts5(caption, image_key UNINDEXED, content=captions)
""")

[nicokant]

Hi, I'm facing the same issue, looking at the source of fts detection it seems that it uses a sql query for introspection:

select name from sqlite_master
            where rootpage = 0
            and (
                sql like '%VIRTUAL TABLE%USING FTS%content="{table}"%'
                or sql like '%VIRTUAL TABLE%USING FTS%content=[{table}]%'
                or (
                    tbl_name = "{table}"
                    and sql like '%VIRTUAL TABLE%USING FTS%'
                )
            )

See: https://github.com/simonw/datasette/blob/067cc75dfa01612f9a47815b33804361e18bf5c3/datasette/utils/__init__.py#L595

In this case your table definition uses content=captions which is a valid syntax but it's not found by the query, adding "should fix your problem, but since it's a valid syntax probably the query could be changed to detect this case too.