SQLcl is turning into a absolute beast or universal swiss knife if you will. I’m having so much funk with the utility of this tool. Today I want to talk about how to get one key feature of Liquibase to work with SQLcl and its implementation of Liquibase.

It does a lot of it amazingly well, but there are a few rough corners in the support I have found needed in any large scale implementation. At one client Liquibase is used for a large number of projects, Let’s put the number at around 30. It is also used in a large number of environments.

When you get it to be used by large enterprise systems and many environments you always end up with having to customize things. Such as the name of schemas. Because it is not enough for a liquibase project to just drop into the schema you are logged on with. Rather you are logged on with a central schema for all liquibase executions but the object will be placed in a number of schemas in support of a single application.

You have an schema someone logs in with, object are placed in a schema for logic (views, packages and so forth) and tables are placed in yet another schema. This is a typical setup and follows Bryn Llewellyn’s (who for a long time was the PL/SQL Product Manager at Oracle) hard shell design pretty well. There is a lot more that can be done but let’s for the sake of this consider a situation where we from one liquibase project want to create objects in a schema different from the one logged on and it will be required that we can override it for different environments.

Here is an example for a Liquibase change set.

<changeSet
  id="Create table MM"
  author="MM">
  <createTable
    remarks=""
    schemaName="${mySchema}"
    tableName="MM">
    <column name="the_key"   type="number(27)" remarks="Primary Key"> </column>
    <column name="the_value" type="number(27)" remarks="Holds data">  </column>
  </createTable>
</changeSet>

The only things of interest here different from any other script is probably line 6, schemaName is a reference to a property. This can be set in the XML for the Liquibase project such as this.

<property name="mySchema" value="USER_A"/>
<include file="create_mm.xml"/>

Here the mySchema property is given a default value and it is then used in the create_mm.xml shown above.

Now in a classic Liquibase setup one could override this in the file liquibase.properties or by supplying a new value with the invocation of the liquibase jar with a -DmySchema=USER_B. Neither is directly possible with SQLcl. Sure, you could edit the script invoking sqlcl, but doing that for every run would be kind of tedious.

The first thing to validate is that if invoked with a parameter, SQLcl lets you access it. By editing said script and then in a javascript in SQLcl you could print what System.getProperty(“mySchema”) returns. That however is left as an exercise for the reader.

Since it is is visible, all that is needed then is to set the parameter “mySchema” to the schema of a used the logged in user has the privilege to create a table in. You’d see that it works, unless you want to hack the script, just trust me – it does.

So if SQLcl has a set jvm parameter it can be accessed and the Liquibase part of SQLcl will repsect it and use it. All we need now is to find a way to launch SQLcl and set the property.

APP_VM_OPTS[1]=-DmySchema=USER_B;BASH_ENV=<(declare -p APP_VM_OPTS) sqlcl/bin/sql

That is all you need. You set the array the SQLcl script will use, declare -p shows how it is declared and rather than passing an array (you cannot in bash) you pass the declaration of it in a file sourced when a new process is started. Thus BASH_ENV is set to a file created for you and the invocation of SQLcl will first source it. The result is that APP_VM_OPTS is populated when the SQLcl script starts.

That is all it takes to pass a custom property to SQLcl so it can be used in your Liquibase scripts in SQLcl.