Deployment Packager: Managing Variable Values Across Environments in DDL Scripts

Change Log Properties can be used to generalize a setting in a Change Set so that the setting can be fully defined at the time of Forecast or Deployment using values stored in the project's Change Log.

The following example will demonstrate the proper use of Change Log Properties to compensate for the name of a tablespace that varies across steps in a Datical Deployment Plan. When using Change Log Property substitution in the Database Code Packager, the script that's checked in to source code control should be written to succeed in the REF DB environment. No Change Log Properties should be included in the script. Datical will manage the introduction of the Change Log Property during the Packaging process.

The example will use the following SQL Script:

CREATE TABLE TBSP_PROP_TEST 
(
COLUMN1 VARCHAR2(20) 
, COLUMN2 VARCHAR2(20) 
, COLUMN3 VARCHAR2(20) 
) 
TABLESPACE DEVTBSP; 
CREATE INDEX TBSP_IDX_TEST ON "VIDEO_DEV"."TBSP_PROP_TEST" ("COLUMN1", "COLUMN2") 
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS NOCOMPRESS LOGGING
TABLESPACE "DEVTBSP";

The script creates a table named 'TBSP_PROP_TEST' and an index that use a specific tablespace, the name of which differs in each environment. The tablespace name for the REF DB environment is 'DEVTBSP' and is highlighted above. This is the value we will be substituting across the steps in our Deployment Plan

Configuring the Datical Project & Change Log to Use Change Log Properties

There are steps required to facilitate property substitution in a Datical project when Packaging DDL scripts: enabling the Reverse substitution setting in the Datical Project & defining the properties key/value pairs in the Change Log.

Enabling Reverse Substitution in the Datical Project

1. In the Datical Client, open the project and go to the 'Settings' tab in the Deployment Plan editor
2. Expand the 'Change Log Property Substitution' Section
3. Check the 'Reverse Substitute During diffChangeLog' checkbox. Your change will be saved automatically. When this box is checked, Datical will search the Change Sets produced by Packager for occurrences of relevant property values and will replace them with the formatted property keys prior to saving the Change Log and completing the Packager job.
4. Check the updated Datical Project back into source code control for future Packager operations.

Defining Change Log Properties in the Change Log

Change Log properties are stored as XML elements in the product Change Log. It is recommended that property definitions be the first child elements of the 'databaseChangeLog' element in a Change Log XML document.
When Datical encounters a formatted property key, it will attempt to look up the desired value for the current runtime based on the context and/or label configuration of the Change Log properties. Sample properties that use context designations are below.

 
For a Step in a Datical Project that has a default context set to 'DEV' the value 'DEVTBSP' will be substituted when the formatted property key '${TABLESPACE_NAME}' is encountered in a Change Log during Forecast & Deploy. For a Step that has a default context of 'QA' the value 'QATBSP' will used instead.
To continue with the example scenario:

1. Add the property definitions above to the Change Log for your Datical Project.
   1. NOTE: the properties assume tablespaces exist with the name DEVTBSP in your REFDB environment and QATBSP in your QA environment. Either create these tablespaces in the appropriate Oracle environments or substitute the names of existing tablespaces as necessary.
2. Update the 'Context' setting for the REF DB Step in your test Pipeline to 'DEV'
3. Update the 'Context' setting for another Step to 'QA'
4. Check the updated Datical Project back into source code control for future Packager operations.

Packaging the DDL Script & Reviewing the Resulting Change Set

After configuring the Change Log Substitution Properties and the Datical Project, the script can be checked into the ddl folder of the SQL Code Repo and the Database Code Packager can be initiated. There are no special steps to take during Packaging and no additional configuration is required in the SQL Code Repo.
After Packaging completes, evaluate the storage options associated with the Change Set that was created during Packaging:
 
Notice that the literal tablespace name for the REF DB table space has been replaced with the formatted property value '${TABLESPACE_NAME}.' This will allow Datical to replace the value represented by the formatted property with a different value in future Forecasts & Deployments. For example, if a Deployment is performed to the Step in the Pipeline with a default context of 'QA' the resulting change would generate the following SQL:
 

See also: Setting Properties in the Project Changelog