The Datical DB rollback feature allows you to undo changes you have deployed to a database. The changelog for the project maintains a sequential record of all changesets. To perform a rollback operation, you must specify which changeset you want to roll back to. Datical then does the work to undo all the changes that have been deployed to the database since that changeset and removes changesets from the changelog back to the specified changeset that you want to roll back to.

Running Rollback Operations

While there are several supported rollback methods, customers typically roll back the last deployment. This can be performed from either the Desktop GUI or Desktop CLI.  When you run the rollback last deploy operation, all rollback scripts associated with the changesets from the last deployment are rolled back and the record of their deployment is removed from the DATABASECHANGELOG table.

For more information, see the following documents:

• Performing Rollback Operations in the GUI
• Performing Rollback Operations in the CLI

Specifying Rollback Scripts

The best practice is to provide a rollback script for each SQL script. You check it in to your SCM along with the SQL script. During a rollback operation, Datical DB automatically runs the rollback script. 

For more information see: Associating Rollback Scripts with SQL Scripts

Rollback File Validation During Packaging

When you package a non-rerunnable script (most DDL and DML scripts) with a provided rollback file, you have the option of validating the rollback file. Depending on the setting of validateRollback in deployPackager.properties, validation is performed in the following ways:

• none - Do not test rollback scripts. THIS IS THE DEFAULT.
• withoutCompare - Validate that the rollback scripts execute successfully. This does NOT test the effect of the rollback scripts on the database.
  • If the rollback script execution fails, packager exits with an ERROR.
• withCompare - Validate that the rollback scripts execute successfully and compare the before/after state of the database to ensure the rollback scripts completely returned the database to its original state.
  • If the rollback script execution fails, packager exits with an ERROR. 
  • If the "rollback effective" validation fails, packager continues and shows a WARNING message in the log. 

For more information see: Rollback File Validation During Packaging

Rolling Back Stored Logic

Stored logic collectively refers to database objects that are programmatic (function, package, packagebody, procedure, trigger, view).  Datical manages them in place and allows them to be redeployed.  

All stored logic changesets are marked with a version. Every time a stored logic script is packaged, it will be assigned a version number starting at 1. The version number is incremented with each subsequent packaging job.

Running Rollback Last Deploy on stored logic rolls back the current version to the previously deployed version.  If you need to roll back more than one deployment, then you must perform a Rollback Last Deploy operation once per deployment until you get to the desired state.

For each stored logic deployment, Datical keeps track of the previously deployed version number. Datical uses this "previously deployed version number" to know what version to rollback to.

Requirements for Starting to Use Datical with Existing Stored Logic

• The stored logic in your REF environment should be the same as in your PROD environment.  This is a normal part of starting to use Datical. See Initializing Databases in the Project. 
• Remove stray stored logic scripts from all steps in the release pipeline BEFORE beginning to package and deploy changes. 

Rolling Back Stored Logic to Version 0

The first time a package, packagebody, trigger, view, function, or procedure script is packaged – we look to see if that object exists in the Reference Database (used for packaging).

• If it exists, we create a "version 0" of the script to be the pre-existing version for rollback
• If it doesn't exist, we create a "version 0" which is a DROP of the object for rollback

The first script packaged of the object will be version 1

When running rollback, the previously deployed version will be re-deployed. 

For backwards compatibility, if no version 0 exists and you perform a rollback that would require a version 0 change, Datical will:

1. Output a warning message to the console
2. Leave the stored logic object deployed to the database
3. Update the DATABASECHANGELOG tracking table to remove the deployment record of the stored logic object

Effectively, if no version 0 exists, Datical will NOT perform a rollback of the stored logic object but WILL rollback the record of the stored logic deployment in the environment. This way, you can re-deploy the stored logic object and you don't suffer from Datical accidentally doing a DROP on the stored logic object.

Limitations

Rollback to 0 is not supported on the following database platforms:

• PostgreSQL
• EDB Postgres Advanced Server
• DB2 on z/OS

Development Workflows

To learn more: Developer Workflows: Managing Changesets During Development and Test