Support for rollbacks?

[relaxnow]

I'm a recent convert from LiquiBase (which had it's own XML syntax and more importantly did not support PHP migrations), however I am a bit nervous about there being no support at all for rollbacks, is this something which is in scope for DbPatch?

[mdeletter]

Currently rollbacks aren't supported by dbpatch, mainly because when executing a PHP migration or a complex SQL update there is no simple rollback script that fixes your mistakes.

However Dbpatch can execute a complete dump of your database before each update.
See dump_before_update config setting.

[relaxnow]

It's entirely possible to support rollbacks, even with PHP migrations of complex SQL updates, simply by requiring the user to add a rollback for every patch.
Off course it is impractical to require that a rollback should always be available, but at least it should be an explicit decision to cut with the backwardscompatiblity.

Example:
Project X at version 1.0 is looking to convert the User <-> Group mapping from a One-To-Many to a Many-To-Many, introducing a UserGroup table, and deprecating the GroupId field in the User table.
In version 1.0 it could add a patch which adds the UserGroup table, converts the GroupIds in the Users table to UserGroup rows and renames the GroupId column to GroupId_DEPRECATED_IN_1.0.
A rollback script could convert UserGroup rows back to GroupIds* and rename the GroupId back.
In version 2.0 a patch could be added that removes the deprecated GroupId and does not specify a rollback, indicating a break in BWC.

* This is a good example of when full rollbacks are impossible. However for the use-case 'a critical bug was discovered several hours after an update' a simple script that ignores any groups other than the first (which is likely the migrated one) and logs the grouprelations that it had to throw away would be more than sufficient.

Not supporting this is unfortunate as this means that it's not really a good fit for serious projects where simply restoring a database dump would cause too much lost data / revenue.

[mdeletter]

Currently we don't support rollbacks, this don't mean we won't support it in the (near) future.

First of all we must define how this will be suported. So any ideas (and contributions :) ) are welcome.

[mwillbanks]

On supporting rollbacks:

This does not need to be a process where the project figures it out itself; how it should be done instead is a manual process. The programmer should specify a script that provides the ability to rollback.

[sndpl]

Ok, here are some ideas:

I think it's easy to support rollbacks by creating an extra file for each patch that contains the rollback action, something like:

• patch-0001.sql
• rollback-patch-0001.sql

With this it will also be possible to use php patches for rollback a sql patch.
But an other interesting question will be, how should we rollback:

1. Just execute dbpatch rollback which will rollback one patch (if the rollback patch exist).
2. Specify till which patch you want to rollback, dbpatch rollback --till=12. This options gives some extra problems, what if you are currently on patch 15 and you want to rollback till patch 12 with only a rollback patch for patch 14. If you then execute the update command, it will try to update patch 13,14,15 again, so patch 13 and 15 should have some kind of a check, else they will give an error.
3. Keep track of which patches are executed in one update command and when the user executes the rollback it will rollback all patches from the previous update.

[relaxnow]

I feel very strongly that by default rollbacks should be executed on every update.
So:
UPDATE
ROLLBACK
UPDATE

Maybe this could be a property that is set on all environments except production?
This will help the developer test his rollbacks (which would normally not be used and might be skipped during testing).

Also it's completely impractical to support rolling back 15 versions. Sometimes you need to specify that you're going to throw away some old data now, which will mean that you can't roll back any further.

Example:
patch-0015.php -- adds a new user_groups table, migrates users.group_id to user_groups and renames users.group_id to users.group_id_DEPRECATED
rollback-patch-0015.php -- Takes the first group from user_groups per user and sets it in users.group_id_DEPRECATED, then renames users.group_id_DEPRECATED back to users.group_id.
patch-0016-bwcbreak.php -- Removes users.group_id_DEPRECATED and makes it so DbPatch can't roll back to an earlier version.

[mdeletter]

After 2 years, we gonna implement the rollback functionality as follows:

• Rollback files will be named as: patch-0001.rollback.sql to prevent problems with patch prefixes
• Introducing a new 'rollback' command. When executed only the current(latest) patch will be "rolled back". Dbpatch will throw an error when no rollback file is found.
• A new config parameter 'testrollback' will be introduced. When this parameter is set to true, a 'update-rollback-update' scenario will be executed.

[sndpl]

👍

[relaxnow]

😹 👏