Page Comparison

Table of Contents

...

No Format

nopanel	true

<sql_code_dir> # for example, sql_scripts
   archive
   data_dml
   ddl
   ddl_direct
   function
   package
   packagebody
   procedure
   sql_direct
   ssis
   ssis_project
   trigger
   view

Script Directories

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.

procedure Contains 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)

ssis Contains .dtsx files (SSIS Package) and other support files used in ETL processes.

ssis_project Contains .ispac files (SSIS Project)

trigger

Contains SQL scripts that create or replace one trigger.

view Contains SQL scripts that create or replace one view.

...

Object Type Notes

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

title	Note

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 object Place 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 Type

Processing

Directories

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 Type	Processing	Directories
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)

...

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 initial commit group.
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
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

...

Versions Compared

Old Version 29

New Version Current

Key

Script Directories