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:

• 
  

  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"

  • 
    

    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