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.

A sample file is below.

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

Database name	Context
REF1	REF1
DEV1	DEV1
DEV2	DEV2
DEV3	DEV3
QA	QA
STAGE	STAGE
PROD	PROD