Using Properties

Property values are defined in the changelog.xml file. Once defined, properties can be added to scripts, to metadata.properties files, and to the datical.project file to provide variability across environments.

1. Create property statements in the changelog. Use context= and label= to set up the conditions where you intend the different values to be used. 
2. Write a SQL script that uses a variable to reference the property. 
3. Package the changeset.
   • As the first step in packaging the variable is resolved to a literal value based on the property and changeset using matching context or labels attributes. 
4. Deploy the changeset. During forecast and deploy you can specify values for context and labels attributes. The changesets with matching contexts or labels are used. 

When you use the diffChangelog command, databases are compared as deployed, with the interpreted property values. To compare changelogs with the property expressions as written (uninterpreted), use Reverse Substitution During diffChangelog option. 

Setting Properties in the Datical DB GUI

In the Datical DB GUI, do the following:

1. Open a project
2. Click the Settings tab
3. Open the Change Log Property Substitution section.
   
   
   
   
4. Click Manage Properties. 
   
   
   
   

5. Add or edit property keys and values, along with the contexts and labels to use to determine when to apply the property substitution. 
   
   
   
   

   Note

   Property statements that use the same name and value must also have a context or label set to differentiate them. That difference expresses the conditions where each value is used. 

   You cannot create more than one property setting for the same condition (contexts and labels settings). 

6. If you provided values for Substitution Mode and Target, you must also select Reverse Substitute During diffChangelog for the project. See the section "Reverse Substitute During diffChangelog Option". 
7. Click Save.

Note that you can also edit the changelog.xml using a text editor if that is more convenient that using the GUI.

Property Syntax

1. Create property statements in the changelog. Use context= and label= to set up the conditions where you intend the different values to be used. 
2. Write a SQL script that uses a variable to reference the property. 
3. Package the changeset. During packaging the variable is resolved to a literal value based on the property and changeset using matching context or labels attributes. 
4. Deploy the changeset. During forecast and deploy you can specify values for context and labels attributes. The changesets with matching contexts or labels are used. 

Appearance in the Changelog

Use GUI to create properties (or edit the changelog.xml using a text editor if that is more convenient). Differentiate properties with the same name by setting different values and contexts. Use the context to specify the environments where the values are applied. 

In the following code example, the context property is used to set SCHEMA_NAME to different values (schema_alpha for the REF context, schema_beta for the DEV context). 

<!-- properties for different schema -->
<property context="REF" name="SCHEMA_NAME" value="schema_alpha"/>
<property context="DEV" name="SCHEMA_NAME" value="schema_beta"/>

Syntax in SQL Scripts

Use the following form where you want to substitute the value of a property in the script:

${property-name}

Use with Oracle Databases

It is common to host multiple environments on a single Oracle instance. Properties can be used for several purposes. Some examples are:

• Schemas - point to the desired copy of a schema. Multiple copies might exist on the same instance. 
• Storage options - such as TABLESPACE
• Sequences - different settings for startsWith and incrementBy, depending on environment

Example Scenario

Normally a workflow through REF > DEV > TEST > PROD uses the same schema name at each step. However, if in DEV the schema is named SCHEMA1  and in QA it is named SCHEMA2, you could set up property statements to give SCHEMA_NAME a different value depending on the context where a changeset is deployed. 

1. Create two property statements in the changelog. Use the same name but different values (value=) and deployment conditions (in this case, context=).
   
   

   Property Definitions in the Changelog

   <!-- properties for different schema -->
   <property context="DEV" name="SCHEMA_NAME" value="SCHEMA1"/>
   <property context="QA" name="SCHEMA_NAME" value="SCHEMA2"/>

2. Write the SQL to use the property name rather than a literal schema name. 
   
   

   SQL Script Using a Property

   CREATE TABLE "${SCHEMA_NAME}"."MYTABLE"
      (MYCOLUMN VARCHAR(100)
      );

3. Package and Deploy. The value used for ${SCHEMA_NAME} is driven by the difference in the context= attribute in the property statements. 
   • In the DEV database, the table is named SCHEMA1.MYTABLE
   • In the QA database, the table is named SCHEMA2.MYTABLE

Using Properties for Schema Names

You can use properties for the schemaName attribute in the following places:

• datical.project file
• metadata.properties file

You may use properties in the managed-schema names.  Here is an example:

SCHEMA1${ENV}
SCHEMA2${ENV}

To use this capability, the ENV property in the previous example needs to have values supplied for it. Errors are returned during packaging if a schema name does not match a schema in the database. 

Set the ENV property in the changelog. In this case, the context property determines the value to use for ENV. 

<property context="DEV" name="ENV" value="_DEV"/>
<property context="QA" name="ENV" value="_QA"/>

The following database schema must exist in the corresponding managed databases:

DEV Database:
SCHEMA1_DEV
SCHEMA2_DEV

QA Database:
SCHEMA1_QA
SCHEMA2_QA

How and When Properties are Interpreted

Properties are evaluated and values substituted during the following operations:

• Packaging (deploy to REF database). SQL scripts are scanned for property expressions in SQL scripts and substituted. 
• Deploy

Property Substitution During Packaging

SQL scripts are scanned for properties. If there is a match with a property defined in the changelog, a value is substituted before packaging. 

<property context="REF_DB" labels="" name="tbl_prefix" value="REF_DB"/>

CREATE TABLE ${tbl_prefix}_newtable(
id INTEGER);

CREATE TABLE REF_DB_newtable(
id INTEGER);

Disabling Property Substitution During Packaging Using metadata.properties

To turn off changelog property interpretation for packaging only, set disablePropertySubstitution to false in a metadata.properties file. Property expressions in SQL scripts are passed through during packaging, rather than interpreted. 

This capability can be used for debugging packaging issues or incremental testing. 

Note that disablePropertySubstitution has no effect on the substitutions made during a deploy operation. 

See Using the metadata.properties file. 

Property Substitution During Forecast and Deploy

When you set a property to be interpreted for a context, then the property is interpreted when changes are forecast for the context or deployed to the context. 

<property context="DEV" name="ENV" value="_DEV"/>
<property context="QA" name="ENV" value="_QA"/>

Reverse Substitution During diffChangelog Option

This option works only for SQL scripts in the ddl folder. 

If the Reverse Substitute During diffChangelog project setting is enabled, then during packaging (specifically during a diffChangelog operation), the original property variable is reverse-substituted into the changelog in place of the value that was placed in the changelog before. 

This feature has the following benefits:

• Property substitution in contexts later than REF. Example: you use a different TABLESPACE size for SQL CREATE TABLE per context. Specifically, it needs to be one size in DEV and one size in QA.  You need to have the variable you use for the tablespace size put back into the changelog so that it can be evaluated during deployment to those contexts. 
• A portable changelog. The changelog can be copied to another project and re-used. 

Targeting Selected Properties with the substitutionMode Attribute

When the option is set, by default all instances of properties are reverse-substituted.

Use the substitutionMode attribute in the property declaration to control whether it is reverse substituted. 

Values are:

• always - always reverse-substitute. A value for the target attribute is also required. 
• never - do not reverse-substitute
• onMatch - reverse substitute this property where it occurs. If target is not specified, the property is substituted wherever the value occurs. Note that this may result in unintended substitution if the value is not unique to the property and appears elsewhere in the changesets. 

Example for substitutionMode=onMatch

1. The following property is set in the changelog. Note that substitution mode set to onMatch. 
   
   

   <property key="SUFFIX" value="REF" context="REFDB" appdba:substitutionMode="onMatch"/>

2. Reverse Substitute During diffChangelog project setting is enabled. 

3. A script in the ddl folder contains the following code for creating a table:
   
   

   CREATE TABLE PROP_SUB_${SUFFIX}

4. When the script is packaged, the property is evaluated during packaging to PROP_SUB_REF.

5. During reverse substitution, the property expression ${SUFFIX} is re-substituted for REF for the name of the createTable changeset that is placed in the changelog. The changeset name is the following string:

   PROP_SUB_${SUFFIX}

Example for substitutionMode=always

1. The property seq.incrementBy is set in the changelog. Note that substitution mode set to always and target is specified. 
   Using the GUI:

   
   
   Property appearance in the changelog:

   <property name="seq.incrementBy" value="2" context="REF" appdba:substitutionMode="always" appdaba:target=\createSequence\@incrementBy/>

2. Reverse Substitute During diffChangelog project setting is enabled. 

3. A script in the ddl folder contains the following code for creating a sequence. It uses the property ${seq.incrementBy} rather than a literal value, because you want to use a different value for different Datical contexts.  
   
   

   CREATE SEQUENCE century_years
     MINVALUE 1
     MAXVALUE 100
     START WITH 1
     INCREMENT BY ${seq.incrementBy}
     CACHE 20;

4. When the script is packaged, the property is evaluated during packaging to seq.incrementBy, which is set in the property to 2. The resulting changeset 
   
   

       <changeSet author="datical" context="REF" created="Thu Sep 20 09:09:34 CDT 2018" id="create_seq_REF" objectQuotingStrategy="QUOTE_ALL_OBJECTS">
           <createSequence cacheSize="20" incrementBy="2" maxValue="100" minValue="1" sequenceName="century_years" startValue="1"/>
       </changeSet>

5. During reverse substitution, the property expression ${seq.incrementBy} is re-substituted for REF for the value "2"  that was placed in the changeset. The changeset includes the following string:

   incrementBy=${seq.incrementBy}

Example for substitutionMode=never

1. The following property is set in the changelog. Note that substitution mode set to never. 
   
   

   <property key="SUFFIX" value="REF" context="REFDB" appdba:substitutionMode="never"/>

2. Reverse Substitute During diffChangelog project setting is enabled. 

3. A script in the ddl folder contains the following code for creating a table:
   
   

   CREATE TABLE PROP_SUB_${SUFFIX}

4. When the script is packaged, the property is evaluated during packaging to PROP_SUB_REF. 

5. During reverse substitution, the property expression is not re-substituted into the name in the createTable changeset that is placed in the changelog. The changeset name is the following string:

   PROP_SUB_REF

Constraining the Values to Substitute with the target Attribute

Use the target attribute to constrain the values to substitute to just those matching a search expression. 

The search expression takes the form of an XPATH-like target expression.  XPATH is used to specify a nodes in an XML document, in this case the changelog. The syntax for the target attribute is simplified. 

target=<search><attribute>

• <search> - The element to search for. It can be a hierarchical expression of elements and subelements. For example /createTable/column/ means to find all column elements within createTable elements. 
• <attribute> - The attribute to substitute with the property. An attribute is expressed in one of the following ways:
  • @<attr-name> - the name of an attribute for . The @name
  • text() - text string associated with the element.
  • filepath() - the contents of a file associated with the element
• Multiple search-attribute pairs can be separated with a pipe character ( | ), a logical OR.  All of the pairs are put together to express the replacement targets. 

Exclusions

Do not specify search for the following elements. An error is returned and the operation fails if they are specified. 

• databaseChangeLog
• changeSet

Examples for the target attribute

The target attribute constrains reverse-replacements of values with property names to the attributes within elements that you specify. 

/createTable/column/@name

• Locate the column element in a createTable element. Replace within the name attribute. 

/addPrimaryKey/@appdba:fillFactor

• Locate the addPrimaryKey element. Replace the appdba:fillFactor within the attribute. 

/createTable/column/constraints/@nullable

• Locate the constraints element for the column element within the createTable element. Replace within the nullable attribute. 

/createProcedure/@path/filecontent()

• Locate the createProcedure element. Replace within the contents of the file specified by the path attribute. 

/createProcedure/@schemaName|/createFunction/@schemaName|/createTable/@schemaName|

• Locate and replace the schemaName attribute within createProcedure, createFunction, and createTable elements. 

/createSequence/@incrementBy

• Locate and replace the incrementBy attribute within the createSequence element

See also

• How To: Configure a Project Using Oracle Schemas for DBdefs
• Video 12 "Introduction to Subtitution Properties": How Liquibase Enterprise Works