search

fluentmigrator / fluentmigrator

Fluent migrations framework for .NET
https://fluentmigrator.github.io
Apache License 2.0
3.24k stars 655 forks source link

Implement missing SQLite features #1578

Open mattbrailsford opened 2 years ago

mattbrailsford commented 2 years ago

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

SQLite-ALTER-TABLE-Steps

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

jzabroski commented 2 years ago

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 commented 2 years ago

In working on the PR https://github.com/fluentmigrator/fluentmigrator/pull/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 commented 2 years ago

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?