Reversal Feature¶
The DATASTAR scripts encapsulate data and their related entities as a scriptable component. When these components are deployed they make the necessary changes to the target environment by inserting, updating and deleting records. This idea of encapsulating data as a component also facilitates DATASTAR to be able to automatically generate the scripts necessary to reverse the changes so that the data can be reverted to it's original state. The reversal feature enables the generation of reversal or rollback scripts at the time of deployment so that if necessary the changes can be reverted.
Prerequisites¶
To take full advantage of the DATASTAR Reversal feature you should automate the build and deployment of the database scripts. DATASTAR comes with all the necessary components to incorporate DATASTAR into "Dev Ops" so that you can fully automate and audit your deployments. It is recommended that you implement the Audit and History tables which can then be automatically updated to store the deployment logs and hold the generated reversal scripts which enable the ability to reverse or rollback the changes after they have been committed.
Deployment History Table¶
The definition of the deployment history is below, please note that it is possible to change the name of table, but the columns must be as defined below:
CREATE TABLE [dbo].[ADS_DEPLOYMENT_HISTORY] (
[ID] [int] IDENTITY(1,1) NOT NULL,
[WORK_ITEM] [varchar](50) NOT NULL,
[VERSION] [int] NOT NULL,
[ARTIFACT] [varchar](50) NOT NULL,
[DATE_STAMP] [datetime] NOT NULL,
[LOG_OUTPUT] [varbinary](max) NULL,
[REVERSAL_ID] [Int] NULL,
[STATUS] char(1) NULL,
CONSTRAINT [ADS_DEPLOYMENT_HISTORY_PK] PRIMARY KEY CLUSTERED ([ID] ASC));
Note: Upgrading
If upgrading from previous versions then the following script will apply the necessary alterations:
Deployment Reversal Table¶
The deployment reversal table is written to during an automated deployment and holds versions of the scripts that reverse the changes made by that release in that environment:
CREATE TABLE [dbo].[ADS_DEPLOYMENT_REVERSAL] (
[ID] [int] NOT NULL,
[SEQUENCE] [int] NOT NULL,
[CATEGORY] [nvarchar](100) NOT NULL,
[NAME] [nvarchar](100) NOT NULL,
[SCRIPT] [varbinary](max) NULL,
CONSTRAINT [ADS_DEPLOYMENT_REVERSAL_PK] PRIMARY KEY CLUSTERED (
[ID] ASC, [SEQUENCE] ASC))
ALTER TABLE [dbo].[ADS_DEPLOYMENT_REVERSAL]
ADD CONSTRAINT ADS_DEPLOYMENT_REVERSAL_FK1
FOREIGN KEY ([ID])
REFERENCES ADS_DEPLOYMENT_HISTORY ([ID])
ON DELETE CASCADE
Deployment Summary Table¶
The deployment summary table is written to during an automated deployment and holds a summary of the data changes made by that release in that environment:
CREATE TABLE [dbo].[ADS_DEPLOYMENT_SUMMARY] (
[ID] [int] NOT NULL,
[TABLE_NAME] [nvarchar](255) NOT NULL,
[INSERTED] [int] NOT NULL,
[UPDATED] [int] NOT NULL,
[DELETED] [int] NOT NULL
CONSTRAINT [ADS_DEPLOYMENT_SUMMARY_PK] PRIMARY KEY CLUSTERED (
[ID] ASC, [TABLE_NAME] ASC))
ALTER TABLE [dbo].[ADS_DEPLOYMENT_SUMMARY]
ADD CONSTRAINT ADS_DEPLOYMENT_SUMMARY_FK1
FOREIGN KEY ([ID])
REFERENCES ADS_DEPLOYMENT_HISTORY ([ID])
ON DELETE CASCADE
Metadata Tags¶
When setting up the static scripts they need to be tagged as being static, this ensures that the script will be included in the reversal scripts and remain unchanged. The static scripts are typically the ones that get triggered to enable and disable constraints. The example below illustrates how to apply the static metadata tag to your scripts:
`-- Metadata: {"static":"true"}``ALTER` `TABLE` `BUSINESS_RULE NOCHECK ``CONSTRAINT` `ALL``ALTER` `TABLE` `BUSINESS_RULE_GROUP NOCHECK ``CONSTRAINT` `ALL`
Define the tables in the DataStar Client¶
You can set this up in the Workspace settings by navigating to File → Workspace and add the Audit, Reversal and Summary table names.
DataStar Client Reversal Feature¶
The DATASTAR scripts encapsulate data and their related entities as a scriptable component. When these components are deployed they make the necessary changes to the target environment by inserting, updating and deleting records. This idea of encapsulating data as a component also facilitates DATASTAR to be able to automatically generate the scripts necessary to reverse the changes so that the data can be reverted to it's original state. The reversal feature enables the generation of reversal or rollback scripts at the time of deployment so that if necessary the changes can be reverted. This guide details how to use the reversal feature in the desktop application as well as the automated command line toolkit.
Deployment Run Scripts¶
When you open or create a deployment file or if you open the packages view at the top of the tab is an option to "Deploy", this will be enabled provided there are no validation errors. When clicking the button:
DataStar will display the database dialog box so that you can select the deployment target, you can also select the deployment mode either "Commit" or "Rollback". You can also set the "Reversal Mode" as None, Package or Directory. If selecting an option other than "None" DataStar will attempt to create the reversal scripts from the target environment into the your local workspace location configured for reversal scripts, the name of the directory or the package name will automatically be created based on the deployment file name. Scripts that are identified as "static" will be copied unaltered, scripts that are identified as being custom will have the reversal applied to the script and DataStar components will be generated from the target environment to crate a snapshot immediately before deployment.
The deployment will then be executed and the logs displayed.
Deployment History¶
When the Audit and Reversal tables have been defined in the Workspace settings as described in the prerequisites then the "Deployment History" option will be enabled. Clicking this button will open a view of the deployment logs using the current workspace database connection. The deployments are listed in descending date order in a paginated results view. Clicking on a deployment history item will show the logs for that deployment in the bottom pane, likewise the reversal scripts will be shown in the right hand pane.
The reversals scrips can be exported to your local file system using the "Export" button, alternatively the changes can be applied to an environment using the "Apply Reversal" button which will prompt for you to choose the target environment.
Manifest Deployment¶
The manifest deployment enables you to deploy a set of scripts to a target environment using a simple manifest file. For example if you have exported or generated some reversal scripts the output directory will contain a manifest of the files and the order in which they should be executed. The "Manifest Deployment" allows you to select these files and execute them against a target database.
Custom Scripts¶
Custom scripts should be avoided were the data being modified is covered by a DataStar component, otherwise you loose the benefits of versioning such as audit history and managing merge conflicts in the event that more then one person is change the same set of data. To get the most from DataStar and to reduce the risk of overwriting data it is important to follow this principal. Note all custom scripts should be re-runnable, such that if the script has already been run then running it again has no adverse effect.
Where custom scripts are absolutely necessary it is possible to get DataStar to create the reversal scripts provided that they include the metadata tags to facilitate the automated reversal. To create custom scripts that DataStar can reverse following the instructions below:
Step-by-step guide¶
a) Mark the script as being custom by inserting the metadata tag:
b) Use --SWITCH comments to control which statements are executed, if the --SWITCH is at the start of the line then it will be swapped to be the end of the line in the reversal script and vice-versa. This allows you to control what will be executed in each mode:
Original Script¶
-- Metadata: {"custom":"true"}
--SWITCH INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value1', 'value2');
INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value3', 'value4'); --SWITCH
Result of Reversal¶
-- Metadata: {"custom":"true"}
INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value1', 'value2'); --SWITCH
--SWITCH INSERT INTO dbo.T1 (column_1, column_2) VALUES ('value3', 'value4');
An alternative approach is also shown below: