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 for Liquibase Business.

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. 

   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

   . 

3. 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. 

...

1. . 

Setting Properties in the Datical DB GUI

...

1. Open a project

2. Click the Settings tab

3. Open the Change Log Property Substitution section.

   Image Added

4. Click Manage Properties. 

   Image Added

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

   Image Added

   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). 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". 

6. Click Save.

...

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

...

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. 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. 

3. 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. 

...

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

Property Definitions in the Changelog

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

...

Normally a workflow through REF > DEV > TEST > QA > PROD uses the same schema name at each step. However, if in DEV the schema is named SCHEMA1  and 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

   Code Block
   <!-- 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

   Code Block
   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

...

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

• datical.project filemetadata.properties file

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

...

Properties are evaluated and values substituted during the following operations :

...

with Liquibase Business:

• Deploy

Property Substitution During

...

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

...

Action

...

Code

...

Set a property in the changelog to be used during packaging. 

Note that setting the context to the REF database affects how it behaves during packaging. 

...

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

...

Check in SQL script that uses the property.

The property is substituted before packaging.

...

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

...

SQL script that is packaged

...

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. 

...

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. 

Note

Please discuss this option with your Datical Support representative to ensure its proper usage.

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

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

...

"

...

/>

...

Reverse Substitute During diffChangelog project setting is enabled. 

...

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

CREATE TABLE PROP_SUB_${SUFFIX}

...

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

...

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

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/>

...

Reverse Substitute During diffChangelog project setting is enabled. 

...

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;

...

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>

...

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. 

   Code Block
   <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:

   Code Block
   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:

   Code Block
   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

...