How To: Leverage the "metadata.properties" file


Use the metadata.properties file to specify behavior during packaging. 

Sample File

A sample metadata.properties file is installed with Datical DB in <install-dir>/repl/scripts

Placement and Precedence

The metadata.properties file applies to all files and subdirectories in the directory where it is found, including the root directory for SQL scripts.   Additional metadata.properties files can be used in the subdirectories. A property set in a subdirectory overrides the same property set in a parent directory. 

Properties

Table: metadata.properties file

Property

Values

Description

allowRepackaging


true | false

Deprecated starting with Datical DB v4.37. Use rerunnable instead. 

Starting with Datical DB v4.21, this property controls whether you can reuse a filename. The default value is false.

archive



true | false

Deprecated starting with Datical DB v4.37. Use rerunnable instead. 

Starting with Datical DB v4.21, this property controls whether files in the data_dml directory get copied to the archive directory. The default value is true.

contexts

 contextName[,contextName...]One or more context names to associate a changes with specified environments. Multiple values must be separated by commas. Expressions are allowed. 
disablePropertySubstitutiontrue | falseTurns off interpretation of properties set in the changelog. See Setting Properties in the Project Changelog
disableSqlParsertrue | false

Overrides the project option enableSqlParser=true.  There is no effect if the project-level setting is enableSqlParser=false.  This property can be used in metadata.properties for folders that use packaging method DIRECT or SQLFILE.  See Using SQL Parser

The SQL Parser and the DATA_DML packaging method

When using the DATA_DML packaging method, the SQL Parser is disabled by default for DML with versions 7.6 and higher.

enableRollbackOnErrortrue | false

When enableRollbackOnError=true, Liquibase adds WHENEVER SQLERROR EXIT FAILURE ROLLBACK; into native tools which rolls back all of the possible rollback changes in case there is a failure. This prevents scripts from partially deploying.

When enableRollbackOnError=false, Liquibase adds WHENEVER SQLERROR EXIT SQL.SQLCODE; into native tools. This indicates Liquibase will not rollback if an error occurs which allows failure scripts to still be partially committed. 

You may specify this property in the GUI or in the CLI during the deploy packager operation.


sqlPlus only: enableRollbackOnError is set to true by default.
EdbPlus and ClpPlus only: enableRollbackOnError is set to false by default.

labels

 labelName[,labelName...]

One or more labels that identify this set of changes so they can be easily identified and deployed as a group. Multiple values must be separated by commas. No expressions are allowed. 

rerunnabletrue | false

Starting with Datical DB v4.37, use this property to classify SQL scripts as rerunnable (true) or non-rerunnable (false). Use it in place of the archive and allowRepackaging properties. 

  • Rerunnable scripts can be modified in place and repackaged.  They stay in their original directory and are not archived.
  • Non-rerunnable scripts can be packaged only once. During packaging the original scripts are moved from the original directory to an archive directory. They cannot be copied back and edited after packaging.  To modify non-rerunnable scripts, use the create / replace workflow. See Developer Workflows: Managing Changesets During Development and Test

Stored logic scripts are usually rerunnable as they perform CREATE OR REPLACE operations and can therefore be run multiple times and produce the same result (they are idempotent). 

Set rerunnable to true or false.

  • true - SQL code file is not archived. It can be repackaged.
  • false - SQL code file is archived. It cannot be repackaged.

If not set, the value is assigned based on its type (the folder where it is placed in SCM):

  • ddl - rerunnable=false
  • ddl_direct - rerunnable=false
  • data_dml - rerunnable=false
  • sql_direct - rerunnable=false
  • sql - rerunnable=false
  • procedure - rerunnable=true
  • package - rerunnable=true
  • packagebody - rerunnable=true
  • function - rerunnable=true
  • trigger - rerunnable=true
  • view - rerunnable=true

Important: although the use of archive and allowRepackaging are allowed, they are deprecated. In addition, the following combinations of settings now cause an error during packaging:

  • archive=true and allowRepackaging=true
  • archive=false and allowRepackaging=false

See also Flexible Folder Names. When you use flexible folder names it is important to specify rerunnable correctly. 

requireRollbacktrue | false

During the packaging process, it verifies whether SQL scripts have corresponding -rollback or _rollback files for all scripts affected by the metadata.properties configuration. By default, this validation is set to false.

packageMethodconvert | ddl_direct | direct | data_dml | sqlfile | storedlogic | changelog

Starting with Datical DB v5.0, specifies how to package the file, one of convert, ddl_direct, direct, data_dml, sqlfile, storedlogic. Packager further parses storedlogic files to determine their type, one of function, procedure, package, packagebody, trigger, view.  See Flexible Folder Names.

Use the changelog value when packaging Liquibase changelogs. They are processed differently than SQL scripts. See Packaging Liquibase Changelogs and Flexible Folder Names.

ignoretrue | falseDo not process files in this directory and all subdirectories. See Flexible Folder Names.

schemaName


Schema name or comma-separated list of schema names. 

Use either a literal value or a property. (If using a property, it must exist in the changelog, see Setting Properties in the Project Changelog).

If you use fully-qualified object names in SQL scripts, the schema names in the SQL scripts must match the schema names in the project. Packager returns an error if it encounters a schema reference that is not defined in the project. 

For multi-database projects set, schemaName in the form <database>.<schema>Starting in version 5.4, you can use a wildcard for the schema: <database>.*.  This capability allows you to manage all SQL scripts for the database in a single folder. 

Quoting and Casing Considerations When Setting the 'schemaName' property

  • In order to ensure a consistent deployment experience the casing defined for schema names in the Datical Project and the casing used to set this property MUST MATCH.  If they don't match, Deploy Packager will halt with an error
  • It is not necessary to quote schemaName values.  Quotes will be removed during Deploy Packager execution.

Warning

Do not  use schemaName if you are using databaseBackupMode=on_demand in deployPackager.properties with Datical DB versions 5.5 and lower.   (It is okay to use databaseBackupMode=on_demand in deployPackager.properties with Datical DB versions 5.6 and higher.)

versionStrategydeployAll | deployLatest

Specifies how to deploy existing versions of rerunnable changesets if multiple versions are available to deploy. Values are not case-sensitive. 

  • deployAll - (default) deploy all eligible versions in the order they appear in changelog.xml. This has been the strategy used historically (before versioning). 
  • deployLatest - deploy only the latest eligible version. 

If versionStrategy is set to any other value, processing stops with an error.

A changeset is eligible to deploy if it meets criteria set in the deploy operation (label expression).

folderOrderComma-separated list of folders

Specified only in the metadata.properties file at the top of the SQL code tree. That directory must be the one specifed by the sqlScmSQLBaseDirproperty in the deployPackager.properties file.

Provide a list of directories, in the order you want them packaged. Files from these directories are pulled to the front of the packaging order in front of any other folders in the packaging job. See Custom Packaging Order


Sample metadata.properties file

Sample metadata.properties file
# 	
# Placement and Precedence
#
# The metadata.properties file applies to all files and subdirectories in the directory where it is found, including the root directory for SQL scripts.
# Additional metadata.properties files can be used in the subdirectories. A property set in a subdirectory overrides the same property set in a parent directory.
#
#
# The following properties can be set in the metadata.properties file:
#
# disablePropertySubstitution - If 'true' - disables variable substitution performed by packager prior to packaging script.  Default value 'false'.
#
# disableSqlParser   - This is only for Oracle.  Overrides the project option enableSqlParser=true.  There is no effect if the project-level setting is enableSqlParser=false.  
#                      This property can be used in metadata.properties for folders that use packaging method DIRECT or SQLFILE.
#
# labels             - One or more labels that identify this set of changes so they can be easily identified and deployed as a group
#                      labelName[,labelName...]
#
# contexts           - One or more context names to associate a changes with specified environments.
#                      contextName[,contextName...]
#
# allowRepackaging   - DEPRECATED starting with Datical DB v4.37. Use [rerunnable] instead.
#                      This property previously controlled whether you can reuse a filename. The default value was 'false', but it is now deprecated.
#
# archive            - DEPRECATED starting with Datical DB v4.37. Use rerunnable instead.
#                      This property previously controlled whether files in the data_dml directory get copied to the archive directory.  
#                      The default value was 'true', but now it is deprecated.
#
# rerunnable         - Classify SQL scripts as rerunnable (true) or non-rerunnable (false). Use it in place of the [archive] and [allowRepackaging] properties.
#                      Set rerunnable to 'true' or 'false':
#                          true - SQL code file is not archived. It can be repackaged.
#                          false - SQL code file is archived. It cannot be repackaged.
#
#                      If not set, the value is assigned based on its type (the folder where it is placed in SCM):
#                          ddl - rerunnable=false
#                          data_dml - rerunnable=false
#                          ddl_direct - rerunnable=false
#                          sql_direct - rerunnable=false
#                          sql - rerunnable=false
#                          procedure - rerunnable=true
#                          package - rerunnable=true
#                          packagebody - rerunnable=true
#                          function - rerunnable=true
#                          trigger - rerunnable=true
#                          view - rerunnable=true
#
#                      Important: although the use of archive and allowRepackaging are allowed, they are deprecated.
#                      In addition, the following combinations of settings now cause an error during packaging:
#                          archive=true and allowRepackaging=true
#                          archive=false and allowRepackaging=false
#
#                      Also you can use Flexible Folder Names. When you use flexible folder names it is important to specify rerunnable correctly.
#
#
# packageMethod      - Specifies how to package the file. One of 'convert', 'ddl_direct', 'direct', 'data_dml', 'sqlfile', 'storedlogic'.
#                      Packager further parses storedlogic files to determine their type, one of 'function', 'procedure', 'package', 'packagebody', 'trigger', 'view'.
#                      Use the 'changelog' value when packaging Liquibase changelogs. They are processed differently than SQL scripts.
#
# ignore             - If 'true' - skip files in this directory and all subdirectories and do not process them.
#
# schemaName         - Schema name or comma-separated list of schema names.
#                      Use either a literal value or a property. The property must exist in the changelog.
#                      If you use fully-qualified object names in SQL scripts, the schema names in the SQL scripts must match the schema names in the project.
#                      Packager returns an error if it encounters a schema reference that is not defined in the project.
#
#                      For multi-database projects set, schemaName in the form <database>.<schema>.
#                      Starting in version 5.4, you can use a wildcard for the schema: <database>.*.
#                      This capability allows you to manage all SQL scripts for the database in a single folder.
#
#                      WARNING
#                      Do not  use [schemaName] if you are using databaseBackupMode=on_demand in deployPackager.properties.
#
# versionStrategy    - Specifies how to deploy existing versions of changesets if multiple versions are available to deploy.
#                      Values are not case-sensitive. 
#                          deployAll - Deploy all eligible versions in the order they appear in changelog.xml. Default value.
#                                      This has been the strategy used historically (before versioning).
#                          deployLatest - deploy only the latest eligible version. 
#
#                      If versionStrategy is set to any other value, processing stops with an error.
#                      A changeset is eligible to deploy if it meets criteria set in the deploy operation (label expression).
#
#
# The following property is only allowed in the root directory (sqlScmSQLBaseDir) and is not allowed in the subdirectories:
#
# folderOrder        - Comma separated list of folder names to enforce the package order of files. This can be used
#                      when packageMethod ordering (deployPacakger property) is insufficient.
#
#
### Microsoft SQL Server ###
#

labels=
contexts=
rerunnable= 
packageMethod=
schemaName=
versionStrategy=
disablePropertySubstitution=


Assigning Labels and Contexts

You can automatically assign labels and contexts to changesets is by using the labels property and contexts property.

metadata.properties
# This properties file controls label and context values on Deployment Packager generated change sets
labels=label1,label2,label3
contexts=QA,PERF_TEST,UAT


Enabling Repackaging

The allowRepackaging property has been deprecated. Use the rerunnable property instead. 

If for future scripts you want to be able to repackage the without changing the filename and do not want the script to be archived, set rerunnable=true in the metadata.properties folder.  The Deployment Packager then ignores the earlier version of this file and repackages it as a change set with a higher version number than the previous version.  If rerunnable is not set, the Deployment Package ignores any files already packaged and moves them to the archive folder.  You cannot re-use the same script name unless you are using rerunnable=true.

Note: The new script needs to account for what was already deployed by the older script.

For example, 

  1. You package a script that creates a table. 
  2. You want to change the script and repackage it. The changed script still creates the same table.   

The changed script needs to drop the table before re-creating it.

Using schemaName with Multi-schema and Multi-database Projects

Projects that use multiple schemas require that that changesets include a schema specification for object changes.  

Multi-database projects are also multi-schema. Each database may have multiple schema. Use the wildcard ( * ) for the schema in the schemaName attribute to manage the projects using a database folder instead of a folder per schema. 

During deployment, Deployment Packager evaluates the schemaName property. Evaluation behavior varies with the type of script as follows:

  • If a sql script (ddl directory) does not specify a schema name for an object, the first schema listed in the schemaName property is used. Fully-qualified object names are considered valid, even if they use a schema name that does not match the schemaName setting. 
  • If a stored-logic script (function, package, packagebody, procedure, trigger, view directories) specifies a schema name for an object that does not match the first schema in the schemaName list, an error is returned.
  • If a directly-executed script (sql, sql_direct directories) specifies a schema name for an object, the object is deployed to the schema specified in the script. The script setting overrides the schemaName property setting. However, the ChangeLog entry shows the the first item in theschemaName list.

Note

If a single-schema project includes a schemaName setting in metadata.properties, packaging fails with an error. Schema cannot be specified for single-schema projects. 


The schemaName property is evaluated only for some types of scripts, corresponding with the script directories:

EvaluatedNot evaluated

data_dml

ddl

ddl_direct

function

package

packagebody

procedure

sql

sql_direct (*see note about Microsoft SQL Server below)

trigger

view

sqlplus


*For Microsoft SQL Server scripts must use fully qualified object names and references in the sql_direct folder, unless your schema is dbo AND dbo is set as the default schema for your Datical user.


For examples, see near the bottom of How To: Leverage the "metadata.properties" file.

Examples:

Let us work with an example. Assume that this is the directory structure within your sql_code directory:

Using Labels

Use-case: Apply "hotfix" label to certain ddl scripts

  • Create a "hotfix" subdirectory in the ddl directory 

  • Create a "metadata.properties" file in the "hotfix" subdirectory. 

  • Add the following line in the metadata.properties file:

  • metadata.properties - hotfix
    labels=hotfix
  • Now, when Deployment Packager packages any script from the "hotfix" subdirectory, those changes will automatically receive the "hotfix" label (because the label is defined in "metadata.properties" file in the hotfix subdirectory)

    • Lets say "table_user_session_fix1.sql" script is dropped into sql_code/ddl/hotfix directory and it is committed into SCM. 
    • When packager runs, it will pick up this script and realize that there is a metadata.properties file located in the same directory and it contains labels=hotfix. Therefore, packager will apply this label ("hotfix") to all changesets generated from this sql script.
    • Note that in addition to applying the "hotfix" label, other labels are also applied: 
      • The script name is a label 
      • The label passed into the Deployment Packager when packager was invoked (via "hammer groovy deployPackager.groovy --label=<comma_separated_list_of_labels>")


Using Contexts

Use case: Changes need to be deployed to specific Dev environments

Certain projects have requirements where they have multiple DEV databases (e.g., DEV1, DEV2, DEV3, etc.). Each DEV environment is given to a specific team or designated for a specific feature. As such, scripts get deployed to a specific DEV database (instead of deploying to all DEV databases).Once scripts are ready for QA, then all scripts are deployed into a single QA environment. Here is a high level diagram to show typical deployment path:



This can be achieved using Datical's "context". Similar to labels, a context refers to a specific database (or databases) in the pipeline. When a Datical pipeline is configured, each connection to database is given a context. This context can be used to perform deployments. 

Where do you specify context for a database environment in Datical project?

  • In Datical DB GUI, click on "Edit Connection" link for each database in the project:
  • Specify context here. If no context is provided, click on the "Configure" button on the right to type in the context
  •  → 
  • Click "OK".
  • Repeat for all database environments in the project, including database environments in other pipelines that exist in the project.
    • Your context can match your database name as shown in the table below:
      • Database nameContext
        REF1REF1
        DEV1DEV1
        DEV2DEV2
        DEV3DEV3
        QAQA
        STAGESTAGE
        PRODPROD
  • Finally, make sure to push the project into your SCM (e.g., Bitbucket).

Step-by-step guide

  • Setup directory structure such that there are DEV subdirectories in each of the sql_code directories
    • For example, in data_dml directory, there would be three DEV directories - one for each DEV environment - DEV1, DEV2 and DEV3
  • In each DEV subdirectory, there would be a "metadata.properties" file which would contain "contexts" flag:
    • In DEV1 subdirectory, the "metadata.properties" file would contain "contexts=REF1,DEV1,QA,STAGE,PROD"
    • In DEV2 subdirectory, the "metadata.properties" file would contain "contexts=REF1,DEV2,QA,STAGE,PROD"
    • In DEV3 subdirectory, the "metadata.properties" file would contain "contexts=REF1,DEV3,QA,STAGE,PROD"
    • metadata.properties - DEV1
      contexts=REF1,DEV1,QA,STAGE,PROD
  • When packager picks up scripts from these subdirectories, it will automatically apply all context to resulting changesets. 
    • Lets say "shopping_cart_aug.sql" script is dropped into sql_code/ddl/DEV1 directory and it is committed into SCM. 
    • When packager runs, it will pick up this script and realize that there is a metadata.properties file located in the same directory (ddl/DEV1) and it contains contexts=REF1,DEV1,QA,STAGE,PROD. Therefore, packager will apply these contexts to all changesets generated from this sql script.
  • Now, when you deploy your changes to DEV1 environment, these two changesets will be DEPLOYED.
  • When you deploy your changes to DEV2 or DEV3 environments, these two changesets will be SKIPPED because the context for these changesets do not match with the database context as specified in the Datical project


Copyright © Datical 2012-2020 - Proprietary and Confidential