On relative paths with SQLcl and Liquibase

This question popped twice in the last few days, and that’s always a prompt for me to put together a blog post AND sort our product docs. Here is that blog post.

But first, the question –

Can we use relative paths in our changeSets?
Customers A & B

Yes! Let’s look at an example.

In my scenario I’m running SQLcl, interactively, and I’m going to process 3 changeSets, that will:

create a table, CSVS
load said table with the SQLcl LOAD command
print the current working directory from inside the SQLcl script runner

Our controller changeLog will use relative paths.

The changeSet in step 2 will use a relative path for the location of the CSV file.

My SQLcl runtime CWD will be the rel-paths-load directory, home of the controller.xml

So I can simply run –

lb update -changelog-file controller.xml

The output

That directory listing/path gets important in a few paragraphs.

We can also see in there feedback like

'Running Changeset: dirA/table-load.xml'

That’s using the relative file path location as referenced in the controller, it’s in a subdirectory, under the controller in dirA.

The table1-load changeSet, using SQLcl’s LOAD command

I’ve talked about this previously, full example is here.

But let’s do a fresh take. In the previous example, I used the ‘cd’ command in my changeSet to tell SQLcl where to look, but maybe you want to use relative paths in SQLcl’s script engine as well.

<changeSet id="8002" author="thatJeffSmith" failOnError="false"   runAlways="true" >
    <n0:runOracleScript objectName="load-tble-1" objectType="SCRIPT" ownerName="HR" sourceType="STRING" replaceIfExists="false">
      <n0:source><![CDATA[
       load csvs ..\..\..\..\..\users\jdsmith\desktop\lb\rel-paths-load\dirA\dirB\csvs.csv
       commit;
      ]]></n0:source>
    </n0:runOracleScript>
</changeSet>

The scriptrunner process that kicks off to handle the runOracleScript changeSet adopts the current working directory from where SQLcl was kicked off. I’m starting SQLcl from where it’s installed,

c:\sqlcl\23.3\sqlcl\bin

So when I want to reference the location of my CSV file in dirB, the relative path gets a bit fun. For debugging/testing, I created another changeSet that simply printed for me the CWD from inside the scriptrunner –

<changeSet id="8003" author="thatJeffSmith" failOnError="false"   runAlways="true" >
    <n0:runOracleScript objectName="create-tble-1" objectType="SCRIPT" ownerName="HR" sourceType="STRING" replaceIfExists="false">
      <n0:source><![CDATA[
          !dir
      ]]></n0:source>
    </n0:runOracleScript>
</changeSet>

Yeah, I’m just doing a !dir to see where I’m at (in Windows CMD speak.) And here’s a callback from the output I showed at the beginning, seeing where the SQLcl scriptrunner path is set to…

Hence the ../../../../ to get back to C:\

Don’t forget the -search-path option for the lb update cmd

Instead of starting off with a ‘cd C:\Users\JDSMITH\Desktop\lb\rel-paths-load’ in my interactive SQLcl shell session, I can use the -search-path directive (Liquibase Docs) to tell Liquibase where to look for files.

lb update -search-path C:\…\Desktop\lb\rel-paths-load -changelog-file controller.xml

23.4 Sneak Peek

There can be lots of typing when you’re working in interactive mode. We’re in the process of setting up tab-completion for all of the commands in SQLcl, and 23.4 will have it ready for the liquibase (lb) commands.