Using Flexible Folders

This feature can help you if you cannot use fixed-name folders in SCM under a <sql_code> root. 

Using this capability, you may use any number of arbitrarily named folders under a <sql_code> root. This enables you to use folder names that are meaningful to your environment or instructive to your end users.

When you use flexible folders, the folders containing SQL code must have a metadata.properties file, even if it is blank.

You need to specify two properties in a metadata.properties file in the folder when using flexible folder names.  These properties explicitly specify packaging behavior that can be different than the defaults that are implicit with fixed-name folders.

• packageMethod - specifies how to package code in this folder
• rerunnable - specify whether this code can be repackaged (updated in place)

SQL File Processing

SQL files are packaged in a determined order by type. The packaging method is determined from the type. 

Ordering

Packaging is done in the following order, using the SQLFileType returned from parsing. It is the same order used with static-name directories. (Note that ddl_direct is available in 7.15 and higher.)

The following table shows the packageMethod used for each SQLFileType:

Complex and Interdependent Database Changes

Normally DDL changes are placed in folders that use the CONVERT packaging method. However, when you need to package complex and interdependent changes, place them in a folder that uses the DDL_DIRECT or DIRECT packaging method instead. 

If the order that the multiple statements are listed in the single script is critical to the success of the script, then use the DDL_DIRECT or DIRECT packaging method. The DDL_DIRECT or DIRECT packaging method will preserve the order of the statements in the script. (The CONVERT packaging method may not preserve the order because it 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 use the DIRECT or DDL_DIRECTpackaging method:

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

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

Resources Directory

During SQL file packaging, files are copied to directories under the project Resources directory. The directories have the same name except for files in sql, sqplus, and sql_direct, which are all placed in Resources/sql. 

Properties in metadata.properties

Requirement for metadata.properties

When you are using flexible folders, folders containing SQL code must have a metadata.properties file, even if it is blank. 

• Folders containing metadata.properties file are processed.
• Folders not containing metadata.properties file are skipped.

Available properties for metadata.properties

A full list of the properties that can be defined in metadata.properties is found here: Using the metadata.properties file

Some of the most commonly used properties are mentioned below:

ignore

Set to true or false. If true, packager skips this folder and all subfolders during processing. 

packageMethod

If specified, determines the processing method to use during packaging. The file is further parsed to determine the SQLFileType, which determines the order of processing. 

If not specified, determine the type based on directory location (static-name directories) and parsed SQLFileType. 

• CONVERT (convert)
• STOREDLOGIC (native)
• DIRECT (native)
• DDL_DIRECT (native)
• DATA_DML (native)
• SQLFILE (native)

rerunnable

The newer rerunnable property replaces two older properties that are are deprecated and should no longer be used (allowRepackaging and archive).

Set rerunnable to true or false:

• true - the SQL code file is not archived. It can be repackaged. 
• false - the SQL code file is archived. It cannot be repackaged. 

If not set, packager checks whether fixed-name folders are being used under the <sql_code> root and assigns the default rerunnable property value as follows. In general, stored logic is rerunnable. 

versionStrategy

When you are using the rerunnable=true property, you can have multiple versions of the change set for different iterations of the sql script.  Stored Logic is usually configured to use rerunnable=true.  You can then use the versionStrategy property to indicate whether to deploy all versions of the rerunnable change set, or only deploy the latest version of the rerunnable change set.

Set versionStrategy to deployAll or deployLatest:

• deployAll - deploy all eligible versions in the order they appear in changelog.xml. This is the default.
• deployLatest - deploy only the latest eligible version.

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 Liquibase Enterprise

Oracle Database Objects and Packaging

SQL Server Database Objects and Packaging

Fixed Folder Names

Using the metadata.properties file

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

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