This document describes the case of negating database changes that have been packaged and deployed into the Datical pipeline but deemed unnecessary to be deployed all the way into the PROD environment.
The idea is that these changes could be deployed into PROD safely as long as there is also a script which negates those changes.
Examples of such cases are: Adding a new object, e.g., a new table, a new column or a new procedure, and this object is no longer needed. Since the addition of an object is not a concern for data loss, it is safe to deploy such changes into the pipeline. These changes could be safely negated by another script. As a result, the final state of the database would not show any of those changes.
There is a separate use case around changes that are deemed detrimental and therefore cannot be deployed into higher environments. For example, doing so could result in a data loss. This use case is captured in another guide "How To: Abandon changes" which will prevent changes from deploying further in the pipeline.
Step-by-step guide
Process for negating changes is different for stored logic and ddl/dml code.
Changes in "data_dml", "ddl" or "sql_direct" directories
We will assume that a script by the name of "international_billing.sql" was packaged from "sql_direct" directory. This script created several tables. One of those tables "international_billing_custom" is not needed. Steps below will guide you how to negate this change, i.e., drop table international_billing_custom
- Commit a negating script. Since this script simply performs a "drop table" operation, this script can be committed into the "ddl" directory.
- From naming convention perspective, you would want to name this script as something meaningful for users, e.g., "
drop_international_billing_custom_table.sql".- Notice that this will not result in net data loss when both "create table" and "drop table" are deployed to a target environment respectively.
- From naming convention perspective, you would want to name this script as something meaningful for users, e.g., "
- Datical will package negating scripts into changesets.
- Note that the changeset will inherit a label based on the script name. This could be useful later when reviewing status, forecast or deploy reports.
- Because the negating script was packaged in a later commit relative to the initial script, Datical will deploy the negating script in the same order as the SCM commit.
- For example, when deploying changes to PROD environment, Datical will deploy all changesets from the originating script ("
international_billing.sql"). This will also create the unwanted table in PROD. Datical will then proceed with deploying other changesets including the negating changeset which will remove the unwanted table "international_billing_custom" from PROD.
- For example, when deploying changes to PROD environment, Datical will deploy all changesets from the originating script ("
Best Practice
Prior to deploying your changes to the target environment, especially when deploying to PROD, run a Forecast to check that negating changesets are getting picked up by your release labels and deployed in the correct order.
Changes in "procedure", "function", "package", "packagebody", "trigger" and "view" directories
Note: Scripts committed into one of these directories do not move to the "archive" directory. All stored logic scripts stay in their original directories after packaging.
We will assume that a script by the name of "international_customer_dependents.sql" was packaged from the "procedure" directory. This script contains code for a procedure "intl_cust_dep". Because the original script has been packaged already, there exists a Datical changeset for this procedure. During testing, you realize that the procedure needs to be fixed. Steps below will guide you how to package a fix for this procedure.
- Since scripts in the "procedure" directory do not get archived, the original faulty script is already exists in that directory.
- The Developer will checkout the script from SCM
- Rework the script.
- Commit the reworked script using the same file name and in the same directory where it was originally placed ("procedure").
- Packager will detect a new commit in the "procedure" directory. As a result, a second changeset will be created for the same procedure.
- Because the reworked script was packaged in a later commit relative to the original faulty script, Datical will deploy the reworked script in the same order as the SCM commit.
- For example, when deploy changes to PROD environment, Datical will deploy the first changeset for "
intl_cust_dep" procedure. This will create the procedure in PROD with faulty code. Datical will then proceed with deploying other changesets including the reworked changeset which will update the procedure with new code.
- For example, when deploy changes to PROD environment, Datical will deploy the first changeset for "
Related articles