Implement missing SQLite features

[mattbrailsford]

Whilst looking through the SQLlite generator it appears that many of the features are just throwing compatibility notifications and whilst many of these features aren't implemented directly, some of them at least can be achieved by creating a temporary table with the new changes, copying the data over and then removing the old table

Given this is the suggested approach to work around these limitations, would it be possible to implement these missing features in this way?

[jzabroski]

Syntactic parsing and semantic binding of database dictionary items is tricky. Every engine handles it differently. I had brainstormed some ideas to make this work smoothly awhile ago, but never wrote about it publicly (I didn't want others stealing the ideas and using it in competing projects without credit, since that has happened in the past and been discouraging).

The general idea is to be able to implement "continuations" in FluentMigrator, or some kind of system where you reify a generic Processor and ProcessorContext. Right now, FluentMigrator doesnt have a concept of a processor context in the way some threading frameworks like scala zio do.

Unlocking such functionality would solve some seemingly orthogonal issues, like fully offline migration scripting. Some of those ideas are documented loosely in the ADR folder.

You can put together a proposal at a high level and I can review it.

[mattbrailsford]

In working on the PR #1585 I did attempt to look at this briefly, but I think the trickiest thing here is that realistically it's just gonna be error prone having any generic solution as the whole duplicating a table is fine in simple examples but as soon as you bring views, triggers, constraints into the mix, it becomes a chain reaction of necessary changes.

The SQLite docs does suggest querying the sqlite_schema table to extract SQL statements for all the affected elements (https://www.sqlite.org/lang_altertable.html#otheralter) but this would require migrations to perform quite a bit of logic, querying the table, fetching the results, potentially parsing them and altering the statements (if you don't want to be passing a full schema constantly) and re-applying them but my gut feeling is it's just a big risk that should really be handled by SQLite.

[jzabroski]

This is great research. What should I prioritize giving feedback to you on first?

I'm thinking of releasing a 4.0.0-beta.1 release to let people bang on the changes, once we square away some of these open threads. Thoughts?