Support for FTS5 special INSERT Commands

[FaFre]

This is a low priority point and doesn't affect the actual functionality (theoretically, right now).

The FTS5 index can be optimized with a special command as defined here.

At the moment this leads to following warning:

[WARNING] drift_dev on lib/features/bangs/data/database/database.dart:
line 75, column 41: Some columns are required but not present here. Expected values for trigger, website_name.
   ╷
75 │   INSERT INTO bang_fts(bang_fts) VALUES ('optimize');
   │                                         ^^^^^^^^^^^^
   ╵

The table definition is as follows to understand the warning:

CREATE VIRTUAL TABLE bang_fts 
    USING fts5(
    trigger,
    website_name,
    content=bang,
    prefix='2 3'
  );

In fact, there are a few more special commands, that might not match the actual FTS columns and lead to warnings a s well (I do not use them): https://www.sqlite.org/fts5.html#special_insert_commands

[FaFre]

@simolus3 with a bit of guidance I might me also able to contribute the code :)

[simolus3]

Thanks for offering your help! At the moment, the error is generated by _LintingVisitor.visitInsertStatement in drift_dev/lib/src/analysis/resolver/drift/sqlparser/drift_lints.dart. That's kind of misplaced, this particular error can be detected and raised by the LintingVisitor in the sqlparser package instead.

We need to recognize these scenarios for that linting step, and possibly also in the type inference visitor (TypeResolver in sqlparser). I think a possible approach here would be to introduce a VirtualTable interface in sqlparser that extends Table. That could have optional methods used as hooks (e.g. validateInsert(InsertSource) and infertInsertTypes(InsertSource)) to customize the behavior of these steps for virtual tables. Let me know if that helps to get you started (I might be overlooking some complexity here), so if you have any questions or run into issues I'm happy to look at them.