How To: Execute scripts with different content depending on the environment
All environments in a pipeline may not be the same. Sometimes, schema names are different across environments. It could be that your tablespace (or filegroup in SQL Server) are not consistent. But because you need to deploy the same script in all environments, you need to specify a variable to represent the entity that changes from environment to environment.
This document walks through the example where the data in a table needs to vary from environment to environment.
Use case: I need to run DML scripts with different values depending on the environment. For example, I've a CUSTOMER table with the following entry in DEV:
CUSTOMER - DEV
| PKey | ID | Data | Load_Ind |
|---|---|---|---|
| 1 | JD6 | First01 Last01 | N |
| 2 | KY54 | First02 Last02 | N |
In PROD, the content is of course different:
| PKey | ID | Data | Load_Ind |
|---|---|---|---|
| 1 | SC98 | John Doe | N |
| 2 | UI65 | Jane Doe | N |
I want to run a script with the following code in DEV
UPDATE CUSTOMER SET Load_Ind = 'Y' WHERE ID = 'JD6'
and in PROD
UPDATE CUSTOMER SET Load_Ind = 'Y' WHERE ID = 'SC98'
Step-by-step guide
The solution is to use Changelog parameters in your changelog.xml. See also Setting Properties in the Project Changelog.
Add the following <property> tags
<property context="DEV" dbms="postgresql" name="customer.id" value="JD6"/> <property context="PROD" dbms="postgresql" name="customer.id" value="SC98"/>
Then simply modify your SQL script as follow and add under the /sql folder. We will support data_dml and sql_direct
UPDATE CUSTOMER SET Load_Ind = ‘Y’ WHERE ID = '${customer.id}'
dbms: type of database. Optional. Comma separated list value of values.
context: environments. Comma separated list value of values.
Related articles
Setting Properties in the Project Changelog
Copyright © Datical 2012-2020 - Proprietary and Confidential