Liquibase Enterprise was formerly known as Datical DB.

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Page History

Version 1 Next »

In some cases you may need to override the default packaging behavior for SQL script processing order.

Controlling the order becomes more important when you check in SQL scripts that have dependencies on objects created by other SQL scripts, typically in a single commit that contains many changes to many types of object. 

Specifying Packaging Order Behavior for the Project in deployPackager.properties

Use the packageOrderStrategy property in deployPackager.properties to choose from three strategies. 

  • DEFAULT - Use the default packaging order. See also Processing Order in Fixed Folder Names. 
    • Commit time - Process all files committed at the same as a group. If multiple groups are found, process them in commit-time order. 
    • File type (folder) - Within a commit group, process files in folder order (corresponds to object type)
    • Alphabetical by filename  - Within a folder, process files in alphabetical order. 
  • PACKAGEMETHOD - Do not consider commit order. See also Flexible Folder Names. 
    • packageMethod setting -  Process in order of packageMethod (set in metadata.properties)
    • Alphabetical by filename - Within a packageMethod, process files in alphabetical order.
  • ALPHANUMERIC - Process in alphabetical order only. Do not consider commit time or packageMethod. 

Specifying Folder-Processing Order in metadata.properties

You can meet special needs for processing order by directly specifying the folder-processing order. 

  1. Place a metadata.properties file in the top of the SQL code tree. It must correspond to the directory specified in the sqlScmSQLBaseDir property in the deployPackager.properties file.  
  2. Use the folderOrder property in that metadata.properties file to specify a list of folders.  Files in the folder are pulled to the front of the list of files to package, ahead of files in other directories.  

Examples: Specifying Packaging in a Multiple Schema Project

Each entry in the comma separated list of values in the folderOrder property is used as a filter on the path to the file. 

  • Files matching the filter are moved to the front of the list of files to package. The process is repeated for each value, in order.
  • The sorting takes place after the baseline packaging order is determined using the packageOrderStrategy property. 
  • Any file in the list that does not match one of the filters is processed after the ordered files and according to the packageOrderStrategy setting.

SQL Code Folder Organization and Packaging Strategy for the Examples

The directories in a SQL Code repository for a Datical project that manages two schema are organized primarily as follows: 

  1. By schema name 
  2. Then by object type name.

SQL files are placed in the folders according to the object type they create or modify. 


SQL Code Repository Layout: First by Schema then by Object Type
C:.
└───sql_code
    ├───schema1
    │   ├───constraints
    │   ├───grants
    │   ├───indexes
    │   ├───tables
    │   ├───types
    │   └───views
    └───schema2
        ├───constraints
        ├───grants
        ├───indexes
        ├───tables
        ├───types
        └───views

Goal: Specify Packaging Order

The following examples progressively apply packaging order options. The desired outcome is to have scripts processed in the following order:

  • By a customer object type (folder) order: types, tables, grants, indexes, constraints, views
  • Within each type, scripts for schema2 are packaged before scripts for schema1

Example output is obtained by running Deployment Packager with the preview=true option, which shows what files would be processed but does not actually process them. 

Output is shown for each of the following applications of options. It shows the resulting file order for the options specified. 

  1. packageOrderStrategy order
  2. Single folderOrder value
  3. Multiple folderOrder values in the desired order
  4. Addition of folderOrder values for the parent folders (schema2, schema1)

1. Packaging Order Based on packageOrderStrategy Only

The packageOrderStrategy for this project is set to PACKAGEMETHOD.  Commit order of the files in the repository is not considered during initial ordering.  Files are ordered first by their package method, then by alphabetical order.

To determine default ordering of the files in the repository without packaging, run hammer deployPackager.groovy with the preview=true option:

Executing the Deploy Packager in Preview Mode
>hammer groovy deployPackager.groovy scm=true pipeline=58_DEMO_PIPE1 preview=true


The order in which files will be packaged is the last message in the preview output.

Initial File Order Based on 'packageOrderMethod' setting of 'PACKAGEMETHOD'
     PACKAGING ORDER FOR COMMIT FILES:
     ---------------------------------
     packageOrderStrategy: PACKAGEMETHOD
     folderOrder:

     COMMIT ID    PACKAGE METHOD    FILENAME/ROLLBACKFILE
     da62531      convert           schema1\constraints\SCHEMA1_FKS.sql
     da62531      convert           schema1\constraints\SCHEMA1_PK_UQ_CONSTRAINTS.sql
     da62531      convert           schema1\indexes\SCHEMA1_I_NAME.sql
     da62531      convert           schema1\tables\SCHEMA1_T_BORROWING.sql
     da62531      convert           schema1\tables\SCHEMA1_T_CUSTOMER.sql
     da62531      convert           schema1\tables\SCHEMA1_T_CUSTOMER_RATING.sql
     da62531      convert           schema1\tables\SCHEMA1_T_EXEMPLAR.sql
     da62531      convert           schema1\tables\SCHEMA1_T_FILM.sql
     da62531      convert           schema1\tables\SCHEMA1_T_GENRE.sql
     da62531      convert           schema1\tables\SCHEMA1_T_MEDIUM.sql
     da62531      convert           schema1\tables\SCHEMA1_T_ORDER.sql
     da62531      convert           schema2\constraints\SCHEMA2_FKS.sql
     da62531      convert           schema2\constraints\SCHEMA2_PK_UQ_CONSTRAINTS.sql
     da62531      convert           schema2\indexes\SCHEMA2_I_NAME.sql
     da62531      convert           schema2\tables\SCHEMA2_T_BORROWING.sql
     da62531      convert           schema2\tables\SCHEMA2_T_CUSTOMER.sql
     da62531      convert           schema2\tables\SCHEMA2_T_CUSTOMER_RATING.sql
     da62531      convert           schema2\tables\SCHEMA2_T_EXEMPLAR.sql
     da62531      convert           schema2\tables\SCHEMA2_T_FILM.sql
     da62531      convert           schema2\tables\SCHEMA2_T_GENRE.sql
     da62531      convert           schema2\tables\SCHEMA2_T_MEDIUM.sql
     da62531      convert           schema2\tables\SCHEMA2_T_ORDER.sql
     da62531      storedlogic       schema1\views\SCHEMA1_V_CUSTOMER.sql
     da62531      storedlogic       schema1\views\SCHEMA1_V_CUSTOMER_HAS_FILM.sql
     da62531      storedlogic       schema2\views\SCHEMA2_V_CUSTOMER.sql
     da62531      storedlogic       schema2\views\SCHEMA2_V_CUSTOMER_HAS_FILM.sql
     da62531      direct            schema1\grants\SCHEMA1_GRANT_SELECT.sql
     da62531      direct            schema1\types\SCHEMA1_UDTS.sql
     da62531      direct            schema2\types\SCHEMA2_UDTS.sql

This ordering has grouped files by PACKAGEMETHOD (determined by settings in the metadata.properties files in the SQL Code repo) and sub-ordered them alphabetically within each PACKAGEMETHOD grouping. 

2. Specifying a Single Folder in folderOrder

Adding the folderOrder property in a metadata.properties file in the root of this SQL code repository allows the user to reorder these files to accommodate this projects specific dependency rules.  First, the property is set to give anything in a 'types' folder ultimate precedence.

sql_code/metadata.properties Edit #1: Move 'types' to the Top of the Package Order List
folderOrder=types

Running deployPackager.groovy in preview mode again yields the desired ordering results.

'types' First File Order
     PACKAGING ORDER FOR COMMIT FILES:
     ---------------------------------
     packageOrderStrategy: PACKAGEMETHOD
     folderOrder: types

     COMMIT ID    PACKAGE METHOD    FILENAME/ROLLBACKFILE
     da62531      direct            schema1\types\SCHEMA1_UDTS.sql					
     da62531      direct            schema2\types\SCHEMA2_UDTS.sql						 
// ------------------------------------------------------------------------------------------'types' have moved from the bottom to the top of the Packaging Order
     da62531      convert           schema1\constraints\SCHEMA1_FKS.sql
     da62531      convert           schema1\constraints\SCHEMA1_PK_UQ_CONSTRAINTS.sql
     da62531      convert           schema1\indexes\SCHEMA1_I_NAME.sql
     da62531      convert           schema1\tables\SCHEMA1_T_BORROWING.sql
     da62531      convert           schema1\tables\SCHEMA1_T_CUSTOMER.sql
     da62531      convert           schema1\tables\SCHEMA1_T_CUSTOMER_RATING.sql
     da62531      convert           schema1\tables\SCHEMA1_T_EXEMPLAR.sql
     da62531      convert           schema1\tables\SCHEMA1_T_FILM.sql
     da62531      convert           schema1\tables\SCHEMA1_T_GENRE.sql
     da62531      convert           schema1\tables\SCHEMA1_T_MEDIUM.sql
     da62531      convert           schema1\tables\SCHEMA1_T_ORDER.sql
     da62531      convert           schema2\constraints\SCHEMA2_FKS.sql
     da62531      convert           schema2\constraints\SCHEMA2_PK_UQ_CONSTRAINTS.sql
     da62531      convert           schema2\indexes\SCHEMA2_I_NAME.sql
     da62531      convert           schema2\tables\SCHEMA2_T_BORROWING.sql
     da62531      convert           schema2\tables\SCHEMA2_T_CUSTOMER.sql
     da62531      convert           schema2\tables\SCHEMA2_T_CUSTOMER_RATING.sql
     da62531      convert           schema2\tables\SCHEMA2_T_EXEMPLAR.sql
     da62531      convert           schema2\tables\SCHEMA2_T_FILM.sql
     da62531      convert           schema2\tables\SCHEMA2_T_GENRE.sql
     da62531      convert           schema2\tables\SCHEMA2_T_MEDIUM.sql
     da62531      convert           schema2\tables\SCHEMA2_T_ORDER.sql
     da62531      storedlogic       schema1\views\SCHEMA1_V_CUSTOMER.sql
     da62531      storedlogic       schema1\views\SCHEMA1_V_CUSTOMER_HAS_FILM.sql
     da62531      storedlogic       schema2\views\SCHEMA2_V_CUSTOMER.sql
     da62531      storedlogic       schema2\views\SCHEMA2_V_CUSTOMER_HAS_FILM.sql
     da62531      direct            schema1\grants\SCHEMA1_GRANT_SELECT.sql

3. Specifying Multiple Folders for More Complex Dependencies

Use multiple values for folderOrder in the metadata.properties file to fulfill more complex dependency ordering.

Consider the following dependencies between SQL files: 

  • constraints and indexes must be packaged after tables because they refer to a table. 
  • tables must be packaged after types, because they refer to a type
  • views must be packaged after grants, because they may rely on select operations on object. The grants provide the select privilege to desired objects.


For these constraints, with files organized by schema and object type as specified above, you would use the following directory order. 

  • types
  • tables
  • grants
  • indexes
  • constraints
  • views


sql_code/metadata.properties Edit #2: Order the rest of the object type subdirectories
folderOrder=types,tables,grants,indexes,constraints,views
Second Level Sub-directory Dependency Ordering Applied
     PACKAGING ORDER FOR COMMIT FILES:
     ---------------------------------
     packageOrderStrategy: PACKAGEMETHOD
     folderOrder: types, tables, grants, indexes, constraints, views

     COMMIT ID    PACKAGE METHOD    FILENAME/ROLLBACKFILE
     da62531      direct            schema1\types\SCHEMA1_UDTS.sql							
     da62531      direct            schema2\types\SCHEMA2_UDTS.sql
// ------------------------------------------------------------------------------------------'types' first
     da62531      convert           schema1\tables\SCHEMA1_T_BORROWING.sql					
     da62531      convert           schema1\tables\SCHEMA1_T_CUSTOMER.sql					
     da62531      convert           schema1\tables\SCHEMA1_T_CUSTOMER_RATING.sql
     da62531      convert           schema1\tables\SCHEMA1_T_EXEMPLAR.sql
     da62531      convert           schema1\tables\SCHEMA1_T_FILM.sql
     da62531      convert           schema1\tables\SCHEMA1_T_GENRE.sql
     da62531      convert           schema1\tables\SCHEMA1_T_MEDIUM.sql
     da62531      convert           schema1\tables\SCHEMA1_T_ORDER.sql
     da62531      convert           schema2\tables\SCHEMA2_T_BORROWING.sql
     da62531      convert           schema2\tables\SCHEMA2_T_CUSTOMER.sql
     da62531      convert           schema2\tables\SCHEMA2_T_CUSTOMER_RATING.sql
     da62531      convert           schema2\tables\SCHEMA2_T_EXEMPLAR.sql
     da62531      convert           schema2\tables\SCHEMA2_T_FILM.sql
     da62531      convert           schema2\tables\SCHEMA2_T_GENRE.sql
     da62531      convert           schema2\tables\SCHEMA2_T_MEDIUM.sql
     da62531      convert           schema2\tables\SCHEMA2_T_ORDER.sql
// ------------------------------------------------------------------------------------------then 'tables'
     da62531      direct            schema1\grants\SCHEMA1_GRANT_SELECT.sql					
// ------------------------------------------------------------------------------------------then 'grants'
     da62531      convert           schema1\indexes\SCHEMA1_I_NAME.sql						
     da62531      convert           schema2\indexes\SCHEMA2_I_NAME.sql
// ------------------------------------------------------------------------------------------then 'indexes'
     da62531      convert           schema1\constraints\SCHEMA1_FKS.sql						
     da62531      convert           schema1\constraints\SCHEMA1_PK_UQ_CONSTRAINTS.sql
     da62531      convert           schema2\constraints\SCHEMA2_FKS.sql
     da62531      convert           schema2\constraints\SCHEMA2_PK_UQ_CONSTRAINTS.sql
// ------------------------------------------------------------------------------------------then 'constraints'
     da62531      storedlogic       schema1\views\SCHEMA1_V_CUSTOMER.sql					
     da62531      storedlogic       schema1\views\SCHEMA1_V_CUSTOMER_HAS_FILM.sql
     da62531      storedlogic       schema2\views\SCHEMA2_V_CUSTOMER.sql
     da62531      storedlogic       schema2\views\SCHEMA2_V_CUSTOMER_HAS_FILM.sql
// ------------------------------------------------------------------------------------------then 'views'

4. Adding Parent Directories to the Ordering

Consider an additional constraint: for each of the preceding object type folders, schema2 scripts should precede schema1 scripts during packaging. 

To apply this constraint to the example, append the ordering for the parent directories of the object type directories in the order that they should be processed. 

sql_code/metadata.properties Edit #3: Process Scripts in the 'schema2' Directory Tree Before the 'schema1' Directory Tree
folderOrder=types,tables,grants,indexes,constraints,views,schema2,schema1
First & Second Level Sub-directory Dependency Ordering Applied
     PACKAGING ORDER FOR COMMIT FILES:
     ---------------------------------
     packageOrderStrategy: PACKAGEMETHOD
     folderOrder: types, tables, grants, indexes, constraints, views, schema2, schema1

     COMMIT ID    PACKAGE METHOD    FILENAME/ROLLBACKFILE
     da62531      direct            schema2\types\SCHEMA2_UDTS.sql						
     da62531      direct            schema1\types\SCHEMA1_UDTS.sql
// ------------------------------------------------------------------------------------------'types' first; schema2 tree is processed first
     da62531      convert           schema2\tables\SCHEMA2_T_BORROWING.sql
     da62531      convert           schema2\tables\SCHEMA2_T_CUSTOMER.sql
     da62531      convert           schema2\tables\SCHEMA2_T_CUSTOMER_RATING.sql
     da62531      convert           schema2\tables\SCHEMA2_T_EXEMPLAR.sql
     da62531      convert           schema2\tables\SCHEMA2_T_FILM.sql
     da62531      convert           schema2\tables\SCHEMA2_T_GENRE.sql
     da62531      convert           schema2\tables\SCHEMA2_T_MEDIUM.sql
     da62531      convert           schema2\tables\SCHEMA2_T_ORDER.sql
     da62531      convert           schema1\tables\SCHEMA1_T_BORROWING.sql
     da62531      convert           schema1\tables\SCHEMA1_T_CUSTOMER.sql
     da62531      convert           schema1\tables\SCHEMA1_T_CUSTOMER_RATING.sql
     da62531      convert           schema1\tables\SCHEMA1_T_EXEMPLAR.sql
     da62531      convert           schema1\tables\SCHEMA1_T_FILM.sql
     da62531      convert           schema1\tables\SCHEMA1_T_GENRE.sql
     da62531      convert           schema1\tables\SCHEMA1_T_MEDIUM.sql
     da62531      convert           schema1\tables\SCHEMA1_T_ORDER.sql
// ------------------------------------------------------------------------------------------then 'tables'; schema2 tree is processed first
     da62531      direct            schema1\grants\SCHEMA1_GRANT_SELECT.sql
// ------------------------------------------------------------------------------------------then 'grants'
     da62531      convert           schema2\indexes\SCHEMA2_I_NAME.sql
     da62531      convert           schema1\indexes\SCHEMA1_I_NAME.sql
// ------------------------------------------------------------------------------------------then 'indexes'; schema2 tree is processed first
     da62531      convert           schema2\constraints\SCHEMA2_FKS.sql
     da62531      convert           schema2\constraints\SCHEMA2_PK_UQ_CONSTRAINTS.sql
     da62531      convert           schema1\constraints\SCHEMA1_FKS.sql
     da62531      convert           schema1\constraints\SCHEMA1_PK_UQ_CONSTRAINTS.sql
// ------------------------------------------------------------------------------------------then 'constraints'; schema2 tree is processed first
     da62531      storedlogic       schema2\views\SCHEMA2_V_CUSTOMER.sql
     da62531      storedlogic       schema2\views\SCHEMA2_V_CUSTOMER_HAS_FILM.sql
     da62531      storedlogic       schema1\views\SCHEMA1_V_CUSTOMER.sql
     da62531      storedlogic       schema1\views\SCHEMA1_V_CUSTOMER_HAS_FILM.sql
// ------------------------------------------------------------------------------------------then 'views'; schema2 tree is processed first


  • No labels

Copyright © Liquibase 2012-2022 - Proprietary and Confidential