How To: Leverage the "metadata.properties" file

This guide is applicable when

• When your database changes do not deploy to all environments in the pipeline (or alternately, a few changes deploy to some environments while other change deploy to all environments)
• When developers need to specify labels at the time of commit or check in
• When you need to repackage your script which was previously packaged 

Overview

The "metadata.properties" file can be a powerful tool when packaging database changes. 

You can automatically assign labels and contexts to generated change sets by using ‘metadata.properties’ files that contain label and context specifications.

When a "metadata.properties" file is discovered during packaging in SCM mode, the Deployment Packager will add the labels and contexts defined in the properties file to all change sets produced by scripts checked into that directory. Labels and contexts assigned in this way are also hierarchical. The Deployment Packager will also associate labels and contexts with change sets generated by scripts processed in sub-directories of the directory where the metadata.properties file resides. To assign different labels and contexts to changes produced by scripts in these subdirectories, simply create a new "metadata.properties" file in the appropriate subdirectory. The labels and contexts will overwrite the values set by any metadata.properties file in ancestor directories.

The "metadata.properties" file also allow for files to be repackaged. For use cases when a particular script has already been packaged, and developers would like to fix the script and push it into SCM again without renaming the file name, the “allowRepackaging=true” will let Deployment Packager know to ignore the earlier version of this file and begin repackage the file. Note: The new script needs to account for what was already deploy by the older script. For example, the new script would need to “drop table” if it is creating the table which was previously created by the older script.

metadata.properties file

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

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 name	Context
      REF1	REF1
      DEV1	DEV1
      DEV2	DEV2
      DEV3	DEV3
      QA	QA
      STAGE	STAGE
      PROD	PROD

• 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