Source Code Management (SCM) Mode

Getting files from an SCM system directly makes it easy to process files as they are checked in without any additional intervention required. Once SQL scripts are checked in to the SCM repository, the user can manually start the Deployment Packager via the Datical DB GUI or CLI. It can also be automatically invoked through SCM polling or an SCM trigger in an automated build solution.

Given the higher level of automation available in SCM Mode, there are a few additional steps that must be taken in order to supply the necessary metadata the Deployment Packager requires to successfully process SQL scripts.

Types of Database Changes

CREATE, ALTER, DROP, COMMENT, RENAME

INSERT, UPDATE, DELETE, TRUNCATE, MERGE, SELECT

GRANT, REVOKE

CREATE (OR REPLACE) / DROP PACKAGE, PACKAGE BODY, PROCEDURE, FUNCTION, TRIGGER 

Source Control Operations in the Packager

Defining Labels in SCM Check In Comments

The Datical DB packager will automatically parse the source control commit message, identify strings enclosed by square brackets, and use the strings as labels for the associated sql scripts.

For example, if a developer checked in a SQL script with the comment

"Add a new column foo to the table bar [label1,label2] [label3]"

then, the Deployment Packager would parse

"label1", "label2", and "label3" 

from the comment and add them as labels to the Change Set(s) associated with the file(s) retrieved from the SCM repository.

Automatically associating Rollback files with sql scripts

To include a rollback file for a given script, name the file containing the rollback script the same name as the change script appended with "-rollback" or "_rollback". For example, if the name of the script is AddMiddleNameColumnToAccountTable.sql, then the rollback file would be named either AddMiddleNameColumnToAccountTable-rollback.sql (with a dash) or AddMiddleNameColumnToAccountTable_rollback.sql (with an underscore).

SQL Script Directory Structure in the Development SCM

In order to associate a script with a particular type of content, SQL scripts should be placed in specifically named directories under the root directory of the SQL code repository. The expected directory structure is documented below. For organizational purposes, the directories can contain sub directories but the top level directory structure must be created as documented if you are using a fixed folder configuration.  (There is also the option of using a flexible folder configuration where you can name the folders to whatever names you prefer.)

sql_script_scm_root_dir
> archive
> data_dml
> ddl
> ddl_direct
> function
> package
> packagebody
> procedure
> sql_direct
> trigger
> view

Datical Packaging Methods

• Convert SQL Scripts
  
  • This is the default method used for any change placed in the /ddl folder
  • It utilizes a diff process to capture the changes from the supplied SQL scripts and create them as detailed change sets in the Datical changelog.xml file
  • Datical DB can then execute Forecast and Customer Rules against these detailed change sets
• Native SQL Scripts
  • This method is used for all other packaging folders
  • It does not require the diff process. Because of this, the packaging process is faster
  • The limitation is that Datical DB only supports limited execution of Forecast and Customer Rules against these change sets

Order of Execution in SCM Mode

Deployment Packager in SCM Mode follows 3 ordering tiers for automating the execution order of new SQL scripts:

Tier 1:  Commit Sequence

• • Files in earlier commits will be processed ahead of files in subsequent commits
  • If the same file was modified in a later commit, it's most recent state will be used, but it will be processed in the order of the original commit

Tier 2:  File Type Sequence

Order of execution for a single commit will process as follows:

Tier 3:  Filename Sequence

If multiple files are included in the same commit, and in the same directory, then files will be executed in alphabetical filename order

Packaging Guidelines

General Notes

• Don't use Datical DB to create databases or schema. Create them outside of Datical DB.
• Always store files as UTF8 encoding.

Object Specific Notes

create table
move data from old table to new table
drop old table