Introduction

Packaging is the first step in the deployment workflow. Packaging is the process of getting SQL scripts refactored and deployed to the first database in the project workflow. By convention the first database is the REF database. 

The refactoring performed depends on the type of change:

• Non-rerunnable changes - SQL scripts that meant to be run only once considered transient. Changes are refactored into XML files. The object model enables easy forecasting of the impact of the change.
  • Example: most DDL files, some DML files. Creating tables, adding columns, dropping indexes, inserting records, etc..
• Rerunnable changes - SQL scripts that perform other functions are managed as SQL scripts. A native SQL interpreter runs them from within the packager to make the changes.
  • Example: Creating stored-logic objects, like packages, procedures, functions, and views. 
  • Example: Native SQL scripts that must run through an interpreter provided with the database (SQL*Plus for Oracle, SQLCMD for SQL Server)

During deployment, the scripts are checked for rules compliance. If they pass, the changes are deployed to the REF database. 

Packaging Tool 

The Deployment Packager can be run from the CLI or the GUI. You provide a configuration file for the project that specifies the options to use during packaging. See Using the required deployPackager.properties file. 

Packaging Sources

You can package SQL scripts from the following sources.  

• Source Code Management (SCM) Repository: Place SQL scripts in a defined location in a source code repository. Configure a properties file with information about source code management repositories where SQL scripts and the Datical DB Project reside. During processing, the Deployment Packager reads the properties file and gets the SQL scripts from the SCM. 
• Manifest File: Define a manifest file to identify the SQL scripts to be processed and provide metadata about the scripts.  The manifest file is passed to the Deployment Packager.

Workflow

Deployment Packager uses the following workflow for changes checked into an SCM. When packaging with a manifest file, the manifest file specifies the files to package (steps 1 and 2 below). 

1. Prereq:
   1. Get Project - Check out Datical project from the SCM (DDB SCM)
   2. Get Scripts - Check out SQL scripts from the SCM (SQL SCM)
2. Determine list of scripts to package from SQL SCM - This is determined by what changes have been introduced to SQL SCM since last successful packaging job (If this is the first packaging job then all scripts will be packaged)
3. Check Scripts for Rules Compliance - Run validation routines, which are a combination of Datical-provided rules and user-defined rules.  Rules ensure that changes are valid and compliant with organizational standards and best practices.
4. Back Up REF Database - If the changes fail, the database is restored to its original state from this backup. 
5. Validate - Check the files against built-in Datical rules and rules provided for the project.  End process if validation fails. (Note: rules validation can be set to fail according to different criteria). 
6. Process Files
   • For scripts that use the convert method (If using Fixed Folders these are scripts packaged in /ddl folder only.  If using Flexible Folders these are scripts that use packageMethod CONVERT.)
     • Snapshot1 the existing schema of the REF database.
     • Convert transient files (like DDL) to an XML object model (changelog) (basic: take DDL, apply to DB using native tools, snapshot again, compare snapshots. The compare produces the object model.)
     • Snapshot2 the schema to record the changes produced by processing the files. 
     • Save a copy of the changelog as it was updated by the changes. 
   • For scripts that use all other methods (If using Fixed folders these are scripts packaged in all other folders such as /ddl_direct, /data_dml, /function, /procedure, /sql_direct,  etc.  If using Flexible Folders these are scripts that use packageMethod STOREDLOGIC, SQLFILE, DIRECT, DDL_DIRECT, or DATA_DML)
     • Prepare non-transient files to be run. (changeset includes a deployPackagerNative call–some analysis of SQL and sanitization)
   • IF there were any CONVERT/DDL scripts, restore the REF database from backup in preparation for Forecast.
7. Forecast Changes - Simulate applying the changes. [ Apply changes represented in the object model (changelog) to an in-memory copy of Snapshot1 ].   Produce a forecast report. 
   • Proceed if forecasting succeeds. 
   • End process and restore the REF database from backup if an error is encountered during the forecast.
8. Deploy Changes to the REF Database.
9. (optional) If there are rollbacks, run the rollbacks for the scripts that were packaged, if validateRollback is enabled.
   • Redeploy Changes to the REF Database.
10. Move non-rerunnable SQL Files to the archive.
11. Commit Changes to SCM repos.
12. Update the packagerReport.html to show details of the Deployment Packager results.

The following diagrams illustrate the workflow.  Click on an image to enlarge it:

CONVERT/ddl folder versus DIRECT/sql_direct folder

The CONVERT packaging method (which is used by default in the ddl folder) does not run the actual sql script during the final deploy.  The CONVERT packaging method (ddl folder) will deploy the change set that was created by comparing the "before" snapshot to the "after" snapshot where the change set is based on the difference between the two.  The CONVERT method (ddl folder) may not preserve the order of statements and it may not include statements that do not result in a change set during the diff process. The CONVERT packaging method is typically slower than other packaging methods.

If the order of the statements in the script is critical or if the script contains statements that would not be represented in a change set generated by diffing the before and after states of the database, you could instead package those scripts with the DIRECT method (which is used by default in the sql_direct folder). Scripts packaged with the DIRECT method in the sql_direct folder (or other folders that you have set to use the DIRECT packaging method) will run the actual script, thereby preserving the statements and the order of the statements.

Note that the possible drawback with using the DIRECT method/sql_direct folder with some configurations is losing some of the simulation/forecasting/modeling/profiling abilities that may happen with the CONVERT method/ddl folder. For Oracle you could use Datical's optional SQL Parser feature to add forecasting to the DIRECT method/sql_direct folder (or other folders that you have set to use the DIRECT packaging method).

Here are pages with additional information about packaging methods, packaging folders, packager performance, and which methods/folders to use for different types of changes:

Placing Files in the SCM Repository

Fixed Folder Names

Flexible Folder Names

Writing SQL Scripts for Liquibase Enterprise

SQL Server Database Objects and Packaging

Oracle Database Objects and Packaging

Using SQL Parser

What is the difference between "ddl" and "sql_direct" folders?

How To: Choose Between CONVERT (ddl) and DDL_DIRECT (ddl_direct) Packaging Methods

How To: Improve Packager Performance