db: proper one-off migration support

[dgw]

Database schema changes happen. Not only that, but sometimes the meaning of a column changes slightly between releases (e.g. #1744).

Sopel could use a system to run needed migrations once-and-only-once on a given database. This probably means storing a "schema version" number/flag somewhere, and writing migration functions to run if the version is too low (or, for the first ones, nonexistent).

I am very optimistically tagging this for 7.1… If it happens before 8.0, I'll be a surprised Pikachu.

[dgw]

Like I said. I'm not a surprised Pikachu.

[Exirel]

So, as seen in an attempt (PR #2317), alembic is a tool to generate, manage, and run database migrations for SQLAlchemy. This is something we should use for this feature: it tags a revision (using a custom table) so it knows what to run against the current schema.

Requirements

Before going further, here is a list of requirements that I think we should have:

• it needs to be a sopel command line, allowing to select a different configuration file
• it needs to allow us to generate new migrations that can be packaged
• it needs to allow Sopel's owner to run migrations that were packaged
• it needs to allow plugin's author to generate new migrations for their plugin (in that case, maybe only packaged version?)
• it needs to collect plugin's migrations to run them alongside Sopel's own migrations

Sopel command line

I suggest a new command named sopel-db, that would be built on top of Alembic, dealing with the complexity of connecting Sopel's configuration with Alembic's configuration. For instance, Alembic needs a ini file for its configuration, but we must not require bot owners to have their own file, and only use the Sopel configuration file. This sopel-db will manage that for the users.

We could have code to either auto-run migration or print a warning when running Sopel without having the appropriate migration (indicating to run sopel-db migrate for example). So it's not because there is a new command that bot owners must use it, but we can make it mandatory. I'm more for "this should be mandatory", because I really want bot owners to understand that we are doing something with their database, and maybe they should do a backup first.

Generate new migrations

Generating migrations is a bit more difficult: only Sopel developers must generate migrations, and put them in the right place. For that, we can consider have a separate ini file. In any case, we must be careful as to not allow bot owners to generate their own DB migrations that would put migration files (also called "revisions") in their install folder (which would make no sense).

I think we can still use sopel-db here: the sub-command that will generate new revisions can check that we have a setup file. For example, we could use pyproject.toml, maybe a section like [sopel.alembic]? I don't know yet, but I think it should be the right thing to do.

Running migrations

A bot owner must be able to run database migration just by using a command, either directly by running sopel or with the specific command sopel-db. In both case, it needs to work for a specific Sopel config (e.g. using -c/--config).

I think this is pretty straightforward, and it's the reason why we need a specific command (or sub-command).

There are, however, two cases to consider first:

• running a new Sopel instance, without an existing database: we should probably just create the database as it should be; however plugin may have data-migrations (like, insert data at startup), so we need to account for that as well under the hood
• running an existing Sopel instance that (may) need migrations: we should probably ask the bot owner to run the migrations (and warn them to do a backup before, just in case)

Generate plugin migrations

This part is probably the most complex. I think it's fair to set some limitation:

• only packaged plugins will be able to generate migrations
• plugin authors must follow the same rules to generate migrations as Sopel's maintainers (i.e. use the same config file format)

We can take advantage of various features of Alembic (which need to be analyzed and tested), such as having multiple named alembic config, using multiple location for script/revision with a single Alembic env, etc.

Run plugin migrations

Once again, this shows why we need to act as a proxy on top of Alembic commands, because only Sopel knows where the plugins are, and so their migrations.

On the other side of the problem, I wonder how a plugin could advertise their migrations: convention? Entry point configuration? I'm still not sure on that one, but I'm sure I'll figure something out.

[dgw]

Bumping to next major version for reasons described in #2293 (comment)