Change Log Parameters for Custom SQL Changesets

General Discussion
1

Does it work to use Change Log Parameters in a Custom SQL Changeset?

Here is a link to Change Log Parameters:
http://www.liquibase.org/manual/changelog_parameters

We need to do a bunch of change owner, revoke, and grant statements for different roles in the database.  We want our clients to be able to define the names of their roles and want our Custom SQL Changeset to hopefully look like the following:

          ALTER SCHEMA a_schema_name OWNER TO ${user.admin};     REVOKE ALL ON SCHEMA a_schema_name FROM public;     GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA a_schema_name TO ${user.user};     ALTER TABLE a_schema_name.a_table OWNER TO ${user.admin};     REVOKE ALL ON TABLE a_schema_name.a_table FROM public;     GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE a_schema_name.a_table TO ${user.user};     GRANT SELECT ON TABLE a_schema_name.a_table TO ${user.read_only};  

Will the above Change Log Parameter substitution work?

Thank you for your time.

2

After a little testing I found out that it is possible to use Change Log Parameters in Custom SQL Changesets.

Which was a little bit a surprise, but a very pleasant one!

We were only able to get the parameters passed by adding them to the changelog.xml file:

    http://www.liquibase.org/xml/ns/dbchangelog/1.9"         xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"         xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog/1.9         http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-1.9.xsd">                   SELECT COALESCE(MAX(int4(id)),1)         FROM databasechangelog         WHERE author='MajorVersion'     
    <property name="user.admin" value="admin" />
<property name="user.user" value="user" />
<property name="user.read_only" value="read_only" />

<include file="changesets/20100122-9.xml" relativeToChangelogFile="true" />

            …

But, now I’m trying to pass in the ${parameters} via the liquibase.properties file.

Does anyone know if that works?

Is it possible to pass in ${parameters} via an ant script?

Is it possible to load the liquibase.properties file using an ant script or is using the liquibase.properties file only for running with the command line?

Thank you again for your time.

3

Currrently it doesn’t use the liquibase.properties file, although it probably should.  You can set parameters as a JVM system property, passing them to however you execute liquibase (ant property, command line -D property, etc) or in the changelog itself. 

Nathan

4

Unfortunately, I have not been able to get either the ant properties or -D system properties to work.

Here is what I’ve tried…

And just a friendly reminder that this is a changeset that is involving straight sql commands via the tags and not the other pre-built tags that come with liquibase.

So here is the changeset we’re trying to execute:

          ALTER SCHEMA a_schema_name OWNER TO ${user.admin};     REVOKE ALL ON SCHEMA a_schema_name FROM public;     GRANT SELECT, INSERT, UPDATE, DELETE ON SCHEMA a_schema_name TO ${user.user};     ALTER TABLE a_schema_name.a_table OWNER TO ${user.admin};     REVOKE ALL ON TABLE a_schema_name.a_table FROM public;     GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE a_schema_name.a_table TO ${user.user};     GRANT SELECT ON TABLE a_schema_name.a_table TO ${user.read_only};  

Then I’ve tried both of the following…

Adding parameters to our liquibase-tasks.ant.xml file:

      ...         ...   <target name="update" ...

Then we also tried passing -D system properties via the command line when running ant (actually tried this via the command line and through the ant task runner in eclipse):

    C:\java\src\ESG-CET-Trunk\Ant>ant -v -f liquibase-tasks.ant.xml install -Dsql.jdbc.database.user=database_admin -Dsql.jdbc.database.password=database_pwd -Dsql.jdbc.database.hostport=localhost:5432 -Dsql.jdbc.database.name=unittest_db -Duser.admin=database_admin -Duser.user=database_user -user.read_only=database_read_only

But every time we run we get the following exception:
Reason: liquibase.exception.JDBCException: Error executing SQL ALTER SCHEMA curator OWNER TO ${user.admin}:
          Caused By: Error executing SQL ALTER SCHEMA a_schema_name OWNER TO ${user.admin}:
          Caused By: ERROR: syntax error at or near “$”
        at org.apache.tools.ant.ProjectHelper.addLocationToBuildException(ProjectHelper.java:541)
        at org.apache.tools.ant.taskdefs.Ant.execute(Ant.java:418)
        at org.apache.tools.ant.taskdefs.CallTarget.execute(CallTarget.java:105)
        at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:288)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
        at java.lang.reflect.Method.invoke(Method.java:597)
        at org.apache.tools.ant.dispatch.DispatchUtils.execute(DispatchUtils.java:105)
        at org.apache.tools.ant.Task.perform(Task.java:348)
        at org.apache.tools.ant.Target.execute(Target.java:357)
        at org.apache.tools.ant.Target.performTasks(Target.java:385)
        at org.apache.tools.ant.Project.executeSortedTargets(Project.java:1329)
        at org.apache.tools.ant.Project.executeTarget(Project.java:1298)
        at org.apache.tools.ant.helper.DefaultExecutor.executeTargets(DefaultExecutor.java:41)
        at org.apache.tools.ant.Project.executeTargets(Project.java:1181)
        at org.apache.tools.ant.Main.runBuild(Main.java:698)
        at org.apache.tools.ant.Main.startAnt(Main.java:199)
        at org.apache.tools.ant.launch.Launcher.run(Launcher.java:257)
        at org.apache.tools.ant.launch.Launcher.main(Launcher.java:104)

Is there a specific place in the code source that I could look into to see what is going for substitution values in the tags?

Thank you for your time and a fantastic product.

5

My apologies for not mentioning this in previous posts, we are using version 1.9.5 of Liquibase.

6

Actually, I think the trouble is that in the 1.9 series, the changelog parameters were only available in XML attributes, not in the bodies of tags.  You can test it out by creating a test changeSet with a tag that uses one of your passed parameters as the table name.

The upcoming 2.0 does support parameters embedded in tag bodies.

Nathan

7

Perfect.

Thank you for the reply.

8

@nvoxland , similar to this code worked well for us in 4.9.1 version, but in 4.17.2 version it seems that included file couldn’t see the property

<property name="prop" value="something" />

<include file="changesets/20100122-9.xml" relativeToChangelogFile="true" />

After investigation found that this code works in 4.11.0 and doesn’t in 4.12.0