CODEWITHSUNDEEP
sqlxmloracle-databaseliquibase
Thalys Menezes
Thalys Menezes

Reputation: 371

How to pass property value dynamically to sql tag in liquibase changelog

I'm currently assigning a predefined property value to some tag's attributes, but I want to use it as well inside xml tags. Below is an example based on Liquibase documentation:

<?xml version="1.0" encoding="utf-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.4.xsd"
>

    <property  name="schema.name"  value="DBPECG"/>

    <changeSet id="20201005103200-1" author="felipe.rudolfe" objectQuotingStrategy="QUOTE_ALL_OBJECTS">
        <createTable schemaName="${schema.name}" tableName="TB_IES" >
         .
         .
         .
        </createTable>
    </changeSet>
</databaseChangeLog>

And here is what I want to do. I want to use schema.name in a way such as this, inside sql tag:

<changeSet id="20201005103200-3" author="felipe.rudolfe" objectQuotingStrategy="LEGACY">
    <sql>
        ALTER TABLE ${schema.name}.TB_IES ADD CONSTRAINT...
    </sql>
</changeSet>

Is there a way to do this?

Upvotes: 0

Views: 5659

Answers (1)

Rakhi Agrawal
Rakhi Agrawal

Reputation: 917

Liquibase allows dynamic substitution of properties in changelog files. We can configure multiple properties inside a file and then use them wherever required. In your case, we can just configure property "schemaName" with some value and then use it in changelog file using ${schemaName} syntax.

Liquibase assigns or prioritizes value for configured property in below order:

  1. As an attribute passed to your liquibase runner.
  2. As a JVM sytem property
  3. As an environment variable
  4. As a CLI attribute if you are running liquibase through command line
  5. In liquibase.properties file
  6. In the parameters block (property element of the DATABASECHANGELOG table)

You can do it as below example code snippet:

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
                      http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.8.xsd">

    <changeSet author="author" id="some-unique-id" context="some-context">
        **Your SQL query/ transactional logic goes here**
        <sql>
             ALTER TABLE ${schemaName}.TB_IES ADD CONSTRAINT...
        </sql>
    </changeSet>
    
</databaseChangeLog>

In liquibase.properties file, I will configure this property as follows:

schemaName=DBPECG

Note: above example is using 1 property (schemaName). You can use only even more than that.

If you need help with creating "liquibase.properties" file, visit this link

Cheers!

Upvotes: 1

Related Questions