DB migration from UF3 to UF4

[alexweissman]

We could really use a DB migration tool for helping people upgrade from UF3 to UF4.

[alexweissman]

Current thinking is to implement this as a separate upgrade Sprinkle.

[alexweissman]

When I think about this a little more, it seems like our migration tool should simply copy "pure UF" data (users, roles, permissions) from an old v3 DB to a fresh v4 DB.

With v4's Sprinkle system, any additional columns (in the user table, for example) should probably be implemented as a separate table anyway. So, this should be the responsibility of individual developers to customize this part of the migration.

[lcharette]

As discussed on the chat, the plan for V3 -> V4 migration (and V4 -> V5 and V5 -> V6 and...) should be : A fresh install should always start by installing the latest major revision base migration. For example, if last UF version is 5.3.1, the migrate command should install the 5.0.0 base migration then all subsequent migration up to 5.3.1. This is to avoid to run all migrations when we're at UF12.

If you already have 4.1.3 installed, migrate should upgrade your db to 5.0.0 then to 5.3.1, the same way it works now (install all migrations between 4.1.3 and 5.3.1. in order).

Core migration with major changes (like the v3 -> v4 one) should also check for conflicts and fail if a breaking change in the db is found. For example, if UF change the sessions table to blah and I already have a custom blah table, UF can't know what to do with my blah table. Just renaming it to old_blah might not be enough... or what I want. So a Can't upgrade! A table named 'blah' already exist! error should be thrown. Actually, this is just like a code BC. You need to update your code (migration) before you can run the new code (migration).

This upgrade could be done by the migrate command (automatically detect if an existing database is available) or a upgrade command (in a potential upgrade sprinkle). Upgrade command should also optionally backup the original database.

The upgrade command could also work with a remote "source" db. For example :

• Ask for source database
• Connect to source database
• Determine source version
• Check target db (should be the db defined in config)
• BIG WARNING TARGET DB WILL BE WHIPED
• Flush target db (if not empty), copy source to target db
• Run all migrations from source db version to current version

tl;dr we need a new v3 -> V4 migration. We'll also need a way to detect which version the db is in (migration table not available in v3!) and change the migrator so it not run old migration (ex v4 migration if v5 migrations are available).

Note this issue will require #795

[alexweissman]

I'm going to close this, since we now have a short-term solution here: https://github.com/userfrosting/upgrade

We do need a generalized framework for implementing database upgrades, both for the core tables and developer-defined tables. It has become clear that our current migration system is insufficient for a lot of situations. However, I will open a separate issue for this.