--all option confused by FTS tables

[simonw]

Got this error:

% python -i -m sqlite_history content.db --all
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/Users/simon/Dropbox/Development/sqlite-history/sqlite_history/__main__.py", line 4, in <module>
    run()
  File "/Users/simon/Dropbox/Development/sqlite-history/sqlite_history/cli.py", line 67, in run
    configure_triggers(args.database_path, args.tables)
  File "/Users/simon/Dropbox/Development/sqlite-history/sqlite_history/cli.py", line 20, in configure_triggers
    configure_history(db, table)
  File "/Users/simon/Dropbox/Development/sqlite-history/sqlite_history/__init__.py", line 14, in configure_history
    db.execute(backfill_sql(table, columns))
sqlite3.OperationalError: no such column: rowid
>>> import pdb; pdb.pm()
> /Users/simon/Dropbox/Development/sqlite-history/sqlite_history/__init__.py(14)configure_history()
-> db.execute(backfill_sql(table, columns))
(Pdb) print(table)
releases_fts_idx
(Pdb) print(columns)
['segid', 'term', 'pgno']

[simonw]

Datasette has code relating to this, to figure out which tables should be hidden tables:

https://github.com/simonw/datasette/blob/5890a20c374fb0812d88c9b0ef26a838bfa06c76/datasette/database.py#L391

It's pretty complex, especially when SpatiaLite gets involved.

[simonw]

I'm thinking about dropping the --all feature entirely, but it's REALLY useful to have that.

So maybe I do port across that Datasette code.

[simonw]

This code in particular:

        # Also mark as hidden any tables which start with the name of a hidden table
        # e.g. "searchable_fts" implies "searchable_fts_content" should be hidden
        for table_name in await self.table_names():
            for hidden_table in hidden_tables[:]:
                if table_name.startswith(hidden_table):
                    hidden_tables.append(table_name)
                    continue

[simonw]

Cleanest solution would be to move this logic into sqlite-utils and add that as a dependency here. For the moment though I'm going to copy in a subset of the function (I'll ignore SpatiaLite for the moment).