3.10 Tips for Liquibase Settings
3.10.1 How to Exclude Table Properties When Deploying Changelogs to a New Environment
When capturing tables from one environment and deploying to another, there are certain table properties you may want to exclude from the deployment while creating the table. This may be due to these properties being unavailable or undesired in your target environment, such as:
- Partitioning
- Compression
- Tablespace
SQLcl uses the Data Definition Language (DDL) session settings in the target environment, where the database objects are created or altered in (and not the environment the changelog was generated from), to generate the DDL for the objects. By turning off the DDL settings, you can prevent the corresponding table properties from being included in the table creation.
3.10.2 Create Database Objects in a Schema Different From the Connected Schema
With SQLcl Liquibase, you can make deployments to a different database user schema than the one you are connected to. However, you must have the required permissions for the target user schema.
There are three important parameters related to this for the
liquibase update
command:
-output-default-schema|-ouds
Controls if the schema is output or not when running the data definition language (DDL) SQL to create or alter your database object.
-
If
-output-default-schema true
is set, the schema is included.create table storefront.merchandise …
-
If
-output-default-schema false
is set or-output-default-schema
is not set at all, the schema is not included.create table merchandise …
To deploy database objects to a different schema than the connected schema, you must set
-output-default-schema true
so that you can specify the schema you want to create the objects in using-default-schema-name
.-
-default-schema-name|-desn
If
-output-default-schema true
is set, the-desn
parameter controls which schema is output.Use this parameter to specify the schema in which to create your database objects. For example, if you are connected to test_user and you want to create your table in storefront, your command would be:
liquibase update -changelog-file merchandise_table.xml -output-default-schema true -default-schema-name storefront
If
-output-default-schema true
is set and-default-schema-name
is not set, the schema output is based on theownerName
attribute for your changeset in your changelog file.<n0:createSxmlObject objectName="Merchandise" objectType="TABLE" ownerName="LBUSER" replaceIfExists="false" >
If
ownerName
is not set, the schema you are connected to is used.-liquibase-schema-name|-lbsn
One of the big advantages of the Liquibase functionality in SQLcl is the ability to track and manage your changes. This is mainly done with the
databasechangelog
tracking tables automatically created and updated in your schema when you run Liquibase.-liquibase-schema-name
allows you to control in which schema you update Liquibase tracking information through the databasechangelog tables.This allows you to have use cases such as having a control schema or user that tracks all your Liquibase change information, while applying the database objects themselves to other schemas.
For example, you are connected to the user or schema storefront and want to create your merchandise table using your merchandise_table.xml changelog file. However, you do not want Liquibase to create your databasechangelog tables in this schema. You want the tracking information to be stored in the pre-existing databasechangelog tables in your schema control_user.
The following command accomplishes this:
liquibase update -changelog-file merchandise_table.xml -liquibase-schema-name control_user
Note:
Storefront needs to have the permission to write on thedatabasechangelog
,databasechangeloglock
, anddatabasechangelog_actions
tables in control_user.GRANT SELECT, INSERT, UPDATE, DELETE ON CONTROL_USER.DATABASECHANGELOG TO STOREFRONT; GRANT SELECT, INSERT, UPDATE, DELETE ON CONTROL_USER.DATABASECHANGELOGLOCK TO STOREFRONT; GRANT SELECT, INSERT, UPDATE, DELETE ON CONTROL_USER.DATABASECHANGELOG_ACTIONS TO STOREFRONT;