Datasette 0.29.3 repairs full-text search discovery

A database can contain a working full-text index while the application reading it fails to offer search. Datasette has just fixed that particular disagreement. A change in how sqlite-utils quoted table names exposed an assumption in Datasette discovery code, even though the resulting database was valid. The 0.29.3 package published on September 3 includes the repair. Its versioned changelog also records a CodeMirror fix on the database page.

I like SQLite because a database file can remain useful across different programs. That makes this small compatibility repair more interesting to me than its patch number suggests. The failure occurred where one tool interpreted the schema written by another. Neither a new database format nor a new search engine was necessary to cause it.

Discovery depended on the spelling of SQL

Issue 570, opened on September 2, identifies the problem in the detect_fts function copied into Datasette. The older query searched sqlite_master for SQL containing a virtual-table declaration, an FTS module, and an external-content setting written as content="table". New sqlite-utils output used square brackets around that name, a spelling the discovery query did not recognize.

The implementation shipped in 0.29.3 retains the original pattern and adds a second branch for content=[table]. It also retains a branch for a matching virtual table name. This is a specific extension of the SQL patterns that Datasette recognizes. It does not introduce a general SQL parser or claim to interpret every equivalent declaration.

That distinction explains the scope of the bug. SQLite could interpret the declaration and maintain its own database structures. Datasette was examining the declaration as text to decide which user interface to display. An application can therefore lose automatic discovery without losing the underlying capability. A missing search box in this case does not establish that the index is absent or damaged.

There is a practical design tradeoff in this implementation. Reading schema SQL keeps discovery within a query against the database catalog. It also makes the reader sensitive to choices made by schema generators. Supporting another quoting convention repairs the known disagreement while preserving the existing discovery approach. The patch changes what Datasette recognizes, so rebuilding the database around the older spelling would address the wrong component.

How the search association works

The full-text search documentation for this release describes an external-content arrangement. An ordinary table holds the records, and an FTS virtual table provides search over selected columns. The content argument associates the search table with the ordinary table. Datasette recognizes that relationship and adds a search interface to the ordinary table page.

For an existing items table with name and description columns, the documented setup creates an items_fts virtual table using FTS4. Populating that table requires a separate insert that copies the relevant row identifiers and text. Creating the virtual table alone does not populate the search index. These are separate operations in the documented setup, which matters when diagnosing an empty search result.

The release documentation also provides a shorter command through sqlite-utils:

sqlite-utils enable-fts mydatabase.db items name description

This command illustrates why the compatibility issue affects normal use. A user can follow Datasette documentation, build the index with its companion tool, and then depend on Datasette to discover the association. The programs communicate through the SQLite file and its schema. Their agreement about how to recognize that schema is part of the publishing workflow.

Once configured, a table accepts the _search query-string parameter. The documentation explains that Datasette searches the indexed columns and uses matching row identifiers to select records from the ordinary table. Searching is therefore an operation over the configured FTS table. A text filter on an arbitrary column is a different operation and does not explain this particular discovery failure.

The producer change had a separate purpose

The related sqlite-utils pull request 57 adds an option to create triggers that keep an FTS table synchronized with its parent table. It also changes escaping to square brackets. Simon Willison explicitly identifies the resulting detection failure as a Datasette bug in that discussion. The producer did not need to revert its escaping change to accommodate the assumption in the reader.

Keeping an index populated as records change is a different concern from discovering that index. The pull request makes that separation concrete. Trigger creation concerns future changes to the parent records, while Datasette recognition concerns the schema already present in the file. Correcting detection cannot repair an index that was never populated, and creating synchronization triggers does not itself correct the reader pattern.

I value tools that preserve this separation because it makes failures easier to explain. A publishing application should be able to recognize a valid database created by another program. Where recognition is incomplete, explicit configuration should remain available so that an incidental formatting choice does not determine which operations users can reach.

Explicit configuration remains useful

Datasette already supports an fts_table metadata property for selecting the associated search table. Its release documentation also describes _fts_table as a per-request override. An fts_pk property or _fts_pk parameter supplies the relevant key when the association uses something other than rowid. These mechanisms also support views, for which automatic discovery alone cannot express every desired relationship.

Those settings are useful for deliberately designed interfaces, even after the automatic detection repair. A view can present selected columns while search uses an index associated with the underlying records. The application then needs an explicit mapping between the displayed rows and the indexed identifiers. Version 0.29.3 restores the common automatic case while retaining that configuration for databases whose presentation requires more information than schema inspection supplies.