Improve database migrations

[dsonck92]

The current database migration system, while functional, is rather crudely built. There are several problems with it:

• Not running in a transaction. Meaning any abort in-between statements will likely partially apply changes, that will clash on next run
• Failure results in a downgrade. While this sounds like a good thing, in practice it means that you try to undo stuff that is probably not true:
  • If the whole upgrade worked, it wasn't an error so you don't roll back
  • If the upgrade code fails, you're in an incomplete state, and attempting to roll back assuming this works (or surround stuff with "if exists" hiding this problem)
  • If there's another problem, don't touch it

At least for Postgres, if you run the migration in a transaction, you know that any error will rollback the actual changes you proposed, bypassing the need for rollback.

The proposal:

• Doing away with rollbacks altogether, it's a task probably best done manually if there's a collision
• Wrap all version bumps in a transaction so it's certain that the version will either reflect an old state, or the new state, but not in between.

Caveat: I'm not sure how well this is supported by sqlite. However, in its defense, sqlite will have much less connection issues, so only a migration bug could cause issues.

[Sorunome]

shine apparently had a look at exactly this for work (well, not with mx-puppet-bridge, but a different project which uses the exact same schema upgrades)