How to pass sql substitution parameters to Liquibase changelog?

[hallatech]

Hi Steve,

I have some changeSets in a groovy changeLog file that contains substitution parameters. I'm passing the parameter as a -D property on the gradle CLI but its not being recognised.

e.g. gradle updatePub -Pcontexts=backport -Dtarget=local

The updatePub task is a GradleBuild wrapper task that has all the necessary liquibase parameters setup.

An example changeset might contain: update(tableName: 'site_configuration') { column(name: 'production_url', value:"xxx-${target}-mystore.com") where('id=xxx') }

Everything works fine without the target param but when its added the command fails with:

liquibase.exception.LiquibaseException: Unexpected error running Liquibase: No such property: target for class: org.liquibase.groovy.delegate.ColumnDelegate

The interesting thing is that I did have it working at some point but I cannot get back to that point. If I list the gradle startParameter I can see it coming through correctly as: systemPropertiesArgs={target=local},...

I've also tried running as the basic: gradle update and passing all the params but have the same issue. Any ideas what I need to do to pass this parameter through?

[hallatech]

I just realised what I had changed in the interim: the plugin version. With this version it works as expected: classpath 'net.saliman:gradle-liquibase-plugin:1.0.2' (i.e. Liquibase 3.3.2) With the latest it doesn't work anymore: classpath "org.liquibase:liquibase-gradle-plugin:1.2.1" (i.e. Liquibase 3.4.2)

[stevesaliman]

Changelog parameters are passed through the changelogParameters method in an activity of the liquibase block of the build.gradle file. For example:

liquibase {
  activities {
    main {
      changeLogParameters([ target: 'local'])
    }
  }
}

Would add -Dtarget=local to the arguments it passes to Liquibase. Since build.gradle is groovy code, you could use conditional logic to only pass changelog parameters if your target actually has a value.

[totomz]

@stevesaliman this is not working in 1.2.1. This is the relevant part of my build.gradle

buildscript {
  repositories {
    mavenLocal()
    mavenCentral()
  }
  dependencies {
    classpath 'org.liquibase:liquibase-gradle-plugin:1.2.1'
    classpath 'postgresql:postgresql:9.1-901.jdbc4'
  }
}

liquibase {
  activities {
    main {
      changeLogFile 'conf/liquibase/schemas/auth/update.groovy'
      url 'jdbc:postgresql://localhost:5432/test'
      changeLogParameters([seqStartValu: '100000'])
    }
  }

This results in No such property: seqStartValue for class: org.liquibase.groovy.delegate.ChangeSetDelegate

I tried with -DseqStartValue=100, and by calling System.setProperty("seqStartValue", "1000001")

With version 1.1.1 1.1.0 everything is working

[stevesaliman]

I have a hunch what is going on here. I can't say for sure without seeing the relevant changeSet, but I think I know what is happening. The short answer is to make sure you use single quotes around your token in the changeSet.

The longer answer is as follows:

A changeset is technically a groovy script that gets executed by the Groovy DSL parser. The problem we're trying to solve is to have a token that gets replaced by Liquibase itself. The error message you are getting suggests that the changeSet is not making it past the Groovy parser because it is not valid Groovy code - the token we want Liquibase to handle is being interpreted as a missing Groovy variable.

I just tried a test, and was able to successfully pass changelog parameters to the following changeset:

changeSet(id: 'update-data', author: 'steve') {
  def username='steve'
    update(tableName: 'steve') {
      column(name: 'description', value: '${myToken}')
    where "name='${username}'"
   }
}

There are two things to note about my changeSet.

1. ${username} is ultimately enclosed in double quotes, so it is treated as a simple Groovy variable. The parser has no problem with it because I declared the username variable inside the script.
2. ${myToken} is wrapped in single quotes, so Groovy treats it as a literal, and passes it through to Liquibase, which then sees it as a token. Liquibase then replaces the token with the value from my build.gradle file. In my case in have changeLogParameters([ myToken: 'myParamValue']) in my build.gradle file.

When I checked my table in the database it had "myParamValue" in the database column, just as I expected.

[totomz]

Sorry for the delay in the answer, yes you are right. Changing the quoting made it works