Getting project root in Liquibase sql changeset

Question

I have a requirement in my project where I want to read files into table CLOB data. The database is oracle 12g. We are using liquibase to maintain the data. The project is in springboot. Liquibase uses .sql files to load changes.

The hurdle I am facing is that sql needs the absolute path to the file rather than the path relative to the current changeset file.

Any pointers about how I can use a "." or the "classpath:" in the sql of the changeset?

One of the attempts I have made are sending a property in the changeset through the property tag withen the databaseChangeLog tag. Values of such properties are available in the changeset file, but they dont interpret placeholders like "classpath:". Hence I am stuck. The dbms_lob.fileopen only accepts a directory object with the absolute path. No "." etc is understood. The . in a filepath starts at the database installation root which is of no use to me. I would like the path to where all the changeset files are stored. Please help If you have solved this in any way..

I will post more details if required.. Appreciate the help..

Answer 1

As example what work for me: i'va put this argument on command line as reference for the master changelog

--changeLogFile=src/main/resources/changelog/db.changelog-master.xml

and in the master file

<include file="./changes/db.changelog-ddl-.....xml" relativeToChangelogFile="true"/>

It will be the same for sql file in changeset - they will be referenced relative to the .xml

Answer 2

What about setting up a directory object in oracle in advance and then use symbolic linking on OS level. Depending on your exact requirements you can use symbolic linking on directory level, or you can even directly link the files into the oracle directory, so that Oracle can see files from many different directories at once in the directory object. They need to have different filenames then. I have not tried this idea, but I guess this approach is what I would try out first. HTH KR

Answer 3

Liquibase supports properties in changelogs that can be set in various ways - more information: http://www.liquibase.org/documentation/changelog_parameters.html

Here is a snippet of that documentation:

Liquibase allows dynamic substitution of parameters in a changelog. The parameters to replace are described using the ${} syntax.
Parameter values are looked up in the following order:

• Passed as a parameter to your Liquibase runner (see Ant, command_line, etc. documentation for how to pass them)
• As a JVM system property In the parameters block ( Tag) of the DatabaseChangeLog file itself.

Example of how could be used:

<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-2.0.xsd
    http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd">

<property name="clob.type" value="clob" dbms="oracle"/>
<property name="clob.type" value="longtext" dbms="mysql"/>

<changeSet id="1" author="joe">
     <createTable tableName="table1">
         <column name="id" type="int"/>
         <column name="${columnname}" type="varchar(20)"/>
     </createTable>
</changeSet>

easily you can set file path inside SQL change-set via a property while property value passed with JVM or Build-tools.

@echo off
call Liquibase --changeLogFile=myChangeLogFile.xml update -Dcolumnname=myclm

However I think you can use ${java.class.path} without set it's value from outside.