SQLite Check if Table is FTS4

Question

I'm developing an Android app that uses a SQLite database with FTS4 tables.

In the app there's an option to import a database from the external memory. This database needs to be checked to confirm that it has all the correct tables and columns. I already have the code to do that however I don't know how to check if the tables are "normal" or FTS4. This will result in problems later on with queries with MATCH on them.

The only way I can think of to check if the tables are FTS4 is to do a random query with MATCH and if it gets an error it's because they are not.

Is there a better way to do this like with just a command?

Answer 1

Using MATCH on a plain table results in an error message only if the table has at least one row.

FTS table have a virtual column with the same name as the table name. So you could try a query like SELECT MyTable FROM MyTable.

You could check whether the shadow tables (MyTable_content, MyTable_segdir, etc.) exist.

You could check the CREATE TABLE statement in the system table: SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'MyTable';