Versions Compared

Old Version 29

changes.mady.by.user Former user

Saved on

compared with

New Version 30

changes.mady.by.user Former user

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

Table of Contents

...

SCM Directory Name

Description

<sql_code_dir>

User-defined directory in SCM to use as the root for SQL scripts. Subdirectories are provided for different types of SQL. Example: sql_code. 

archive

The archive directory is created and managed by the Deployment Packager. Files added in the ddl, sql, sqlplus, sql_direct, and data_dml directories will be copied here after successful Packaging to prevent post Packaging modification. The user does not need to create this directory.

data_dml

Contains scripts that load, manipulate or delete data.

ddl

Contains scripts that make ddl changes such as creating, modifying, and dropping tables, indexes, sequences, and other database objects that are not one of the other types.

ddl_direct

The scripts are run directly using the database's native client. The changesets are database-specific. They cannot be deployed to other database platforms. 

  • DB2 - Command Line Processor Plus (clpplus)

  • Oracle - SQL*Plus (sqlplus)
  • EDB Postgres - EDB*Plus (edbplus)
  • SQL Server - SQLCMD (sqlcmd)

Info

The ddl_direct and sql_direct package methods are identical except for the order in which they are processed in packaging: ddl, ddl_direct, data_dml, sql_direct.

The ddl_direct folder is available with 7.15 and higher.

function

Contains scripts that create or replace one function.

package

Contains scripts that create or replace one package definition.

packagebody

Contains scripts that create new or replace existing package bodies.

procedureContains scripts that create or replace one procedure.

sql

Limited Use.

Creates a sqlFile changeset. When deployed, an internal SQL processor to run the script. These changesets can be deployed to any database platform. This is useful if you have a multi-platform project (e.g., Oracle and SQL Server).

It's strongly suggested to use sql_direct if you use the same database platform in all steps in a project. 

sqlplus

Deprecated. 

Use sql_direct instead.

sql_direct

The scripts are run directly using the database's native client. The changesets are database-specific. They cannot be deployed to other database platforms. 

  • DB2 - Command Line Processor Plus (clpplus)

  • Oracle - SQL*Plus (sqlplus)
  • EDB Postgres - EDB*Plus (edbplus)
  • SQL Server - SQLCMD (sqlcmd)
ssisContains .dtsx files (SSIS Package) and other support files used in ETL processes. 
ssis_projectContains .ispac files (SSIS Project)

trigger

Contains SQL scripts that create or replace one trigger.

viewContains SQL scripts that create or replace one view.

...

Object TypeNotes
Databases and Schema


Warning

Create databases, schema, tablespaces, users and roles outside of Datical. Datical is typically used to manage objects and data once the environment has been setup.


Inserts, Updates and Deletes (DML)

Place DML scripts into the /data_dml folder.

Procedures, Packages, Functions, Views and Triggers (Stored Logic)

Objects are managed as non-transient/rerunnable changes by default in the stored logic folders. Scripts remain in their folders after packaging. They are not moved to the archive directory. This behavior supports managing database changes like application source code: the same code is updated in place as changes are needed.  By (By contrast, once after DDL changes are made , the script is moved to the archive directory.)  

When the changes are committed, Datical repackages the file and increments the version (the versioning feature was available as of Datical DB 4.31). of the change set (datical:version). See Changeset Versioning: version and versionStrategy.

For stored logic scripts, create objects using only use the CREATE command (such as "CREATE PROCEDURE"). When you update and deploy the file again, Datical automatically converts the CREATE to an ALTER as needed. (Do not use "CREATE OR REPLACE" or "CREATE OR ALTER".)

Permissions are maintained on the object when deployed.

Info
titleNote

Stored-logic scripts must create only Scripts in the stored logic folders should only create one object per script.


Complex and Interdependent Database Changes

Normally DDL changes are placed in the /ddl directory (which by default uses the CONVERT packaging method).

However, when you need to package complex and interdependent changes, place them in the /sql_direct folder (which by default uses the DIRECT packaging method). 

If the order that the multiple statements are listed in the single script is critical to the success of the script, then put it in the /sql_direct folder.  The /sql_direct folder will preserve the order of the statements in the script.  (The /ddl directory may not preserve the order because the CONVERT packaging method creates the change sets by doing a diff of before and after snapshots, and that comparison process does not know the original order of the statements.)

Examples of sets of statements in a single sql script that need to be placed into /sql_direct:


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


Code Block
alter table drop pk constraint
alter table drop column (a column that the pk constraint depends on)
alter table add column (a column that the pk constraint depends on)
alter table add pk constraint


Rename any objectPlace SQL scripts into the /sql or /sql_direct folder.  When you do, they are processed as a rename operation (rather than a DROP and CREATE).
Using Changelog Properties

All sql scripts can take advantage of properties.

Property key/value pairs are set directly in the changelog. See /wiki/spaces/DDOC59/pages/795771617. 

...

Process TypeProcessingDirectories
Transient
  • Archived: After the changes are processed, the SQL script is moved to the archive directory.
  • SQL scripts with the same name in the same directory cannot be re-packaged

ddl

ddl_direct

sql_direct

data_dml

Non-transient
  • Not archived: The SQL script remains in the directory and is versioned. 
  • SQL scripts can be repackaged
all other directories

...

Process TypeProcessingDirectories
Convert SQL
  • Converts SQL code to produce changesets into an object model (based on the differences when comparing before and after states of REF)
  • Full Forecasting and Rules 
  • Typically slower than other folders/methods
  • The actual script is NOT run during the final deploy (therefore certain statements or the ordering of statements in the original script may not be applied)

ddl folder (CONVERT packaging method)

Native SQL
  • Runs SQL to produce the changes and embeds native SQL calls in the changesets
  • Limited Forecasting and Rules if not using SQL Parser (Oracle customers can optionally enable SQL Parser for Oracle for forecasting)
  • Faster than ddl folder (when ddl folder is using its default CONVERT packaging method)
  • The actual sql script IS run
  • The native script timeout setting that we recommend setting for your REF environments IS applicable because it uses the native SQL client tools (SQLPlus, SQLCmd, ClpPlus, EDBPlus)
  • Cannot use passwords that contain spaces or these special characters @ & / : < > " ' ` | ^ ! = , \

ddl_direct, data_dml, and sql_direct folders (DDL_DIRECT, DATA_DML, and DIRECT packaging methods)


Stored Logic
  • Uses JDBC to create, modify, or delete stored logic objects
  • Limited Forecasting and Rules (Oracle, SQL Server, and DB2 customers can optionally enable the Stored Logic Validity Check project setting)
  • Faster than ddl folder (when ddl folder is using its default CONVERT packaging method)
  • The actual sql script IS run
  • Only include one statement per script
  • Files should be UTF-8 in stored logic folders
  • The native script timeout setting that we recommend setting for your REF environments is NOT applicable because JDBC connection is used
function, package, packagebody, procedure, trigger, and view folders (STOREDLOGIC packaging method)
SQLFILE
  • Uses JDBC 
  • If you have multiple statements, they must be semicolon-delimited
  • You cannot have semicolons within the statement itself
  • The native script timeout setting that we recommend setting for your REF environments is NOT applicable because JDBC connection is used
sql folder (SQLFILE packaging method)

...

  1. Tier 1:  Commit Sequence
    1. The group of files in a single commit is processed as a unit. Within a commit, files are processed within the file type sequence and then within the filename sequence. 
    2. Groups of commits are processed in order of checkin time. The earliest commit is processed first, then the next, and so on. 
    3. If you commit a file as part of a group and then later commit it again with changes (with a group), it is processed with the later commit group. 
  2. Tier 2:  File Type Sequence
    1. ddl
    2. ddl_direct (available in 7.15 and higher)
    3. view
    4. ssis/ssis_project
    5. function
    6. procedure
    7. package
    8. packagebody
    9. trigger
    10. sql
    11. sqlplus (deprecated)
    12. sql_direct
    13. data_dml
  3. 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

...

See also these pages for an overview of packager workflows, guidelines for writing scripts, and when to use which folder or packaging method:

Overview of Packaging

Writing SQL Scripts for Datical DBLiquibase Enterprise

Oracle Database Objects and Packaging

...