SQLite: RETURNING clause doesn't work with virtual tables

[roji]

Our new usage of the RETURNING clause in 7.0.0 is incompatible with SQLite virtual tables. Note that the provider doesn't yet support creating virtual tables in migrations (but can still be used to update tables created externally/via raw SQL).

We can add metadata about virtual tables, which would trigger opting out of RETURNING. We can also add a general context option to opt out of it, which would be an escape hatch for any further incompatibility scenarios.

Note that we already automatically opt out of RETURNING for old versions of SQLite (#28911).

Thanks @JesperTreetop for reporting this in #27663 (comment).

[ajcvickers]

/cc @bricelam to look into virtual tables.

[bricelam]

Virtual tables just have two pieces of information--the backing virtual table module, and a comma-separated list of zero or more arguments. The API could look something like this:

t.IsVirtualTable("csv", "filename='thefile.csv'");
t.IsVirtualTable("fts5", "Name", "Address", "Uuid ININDEXED", "tokenizer='porter ascii'");

[roji]

Providing a standalone opt-out from RETURNING is now tracked by #29916 - should we have a separate issue to track implementing virtual tables in Sqlite?

[roji]

Note: for anyone blocked by this, you can revert to the previous SQLite behavior (without the RETURNING clause) by calling ReplaceService as follows:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseSqlite("Filename=/tmp/foo.sqlite")
        .ReplaceService<IUpdateSqlGenerator, SqliteLegacyUpdateSqlGenerator>();

Note that SqliteLegacyUpdateSqlGenerator is considered an internal service (in the Internal namespace), and will very likely go away in EF 8.0. But this is a good workaround in the meantime.