UI support for running FTS searches

[simonw]

Here's an example query that searches all FTS indexed columns in a table: https://sf-trees-search.now.sh/sf-trees-search-a899b92?sql=select+*+from+Street_Tree_List+where+rowid+in+%28select+rowid+from+Street_Tree_List_fts+where+Street_Tree_List_fts+match+%27grove+london+dpw%27%29%0D%0A

And here's a query that searches a specific column: https://sf-trees-search.now.sh/sf-trees-search-a899b92?sql=select+*+from+Street_Tree_List+where+rowid+in+%28select+rowid+from+Street_Tree_List_fts+where+qSpecies+match+%27london%27%29%0D%0A

If we detect that a table has FTS enabled (which we can do by looking for it as a content table reference in another FTS table's create definition) we should add a search box to the table page which constructs this query - maybe using ?_search=XXX in the query string?

To support search against specified columns, we can do ?_search__ qSpecies=London. - not necessary, see comment below.

• Detect if a table has a FTS index defined against it as a content= parameter
• Decide what to do if there is more than one FTS index (maybe just pick the first one?)
• Add the ?_search= query string argument
• Add the UI

[simonw]

Relevant SQLite docs:

• https://sqlite.org/fts5.html
• https://www.sqlite.org/fts3.html

[simonw]

Since SQLite supports column specifications in the MATCH body itself, there's no need to provide a separate mechanism for specifying columns in the query string: https://sqlite.org/fts5.html#fts5_column_filters

[simonw]

Demo: https://sf-trees.now.sh/sf-trees-ebc2ad9/Street_Tree_List?_search=grove+st