How do you refactor similar looking code and provide params in Liquibase?

Question

I want to use a custom sql bracket in Liquibase that looks like this:

<sql>
  CREATE TRIGGER insert_${param1}_trigger
    BEFORE INSERT ON ${param1}
      FOR EACH ROW BEGIN
        SET NEW.created_at = NOW();
        SET NEW.updated_at = NOW();
      END
</sql>

I would like to call this function in several different changesets and specify param1. I know the inverse functionality is possible because you can specify properties with the <property name="name" value="value"> tag and input the ${name} variable to get the value inserted into the file. However, I am looking to supply the parameter and have the sql above be included in the changeset. Any more general XML or SQL approaches are welcome as well.

Also, I thought the above could be accomplished by using the tag, but I don't think you can specify parameters. — David Groff, Oct 28 '13 at 18:28
You're missing the point of liquibase. It's designed to track changes to your schema, not to become a SQL generation layer. — Mark O'Connor, Oct 29 '13 at 17:50
yea, but if you can't use the tool to keep your schema clean, tight, and correct, what's the point? especially when you think about something that has to be run every time you create a new table such as the above trigger. — David Groff, Oct 30 '13 at 18:56

score 1 · Accepted Answer · answered Oct 29 '13 at 18:26

1

I think what you are looking for is either custom changes or change extensions

Both allow you to specify a java class that can take parameters and create the SQL that should be ran.

answered Oct 29 '13 at 18:26

Nathan Voxland

15,453
2
48
64

Ideally, I would like to have something that didn't require java. I would prefer to have an XML generator that could take in params or a SQL function that took in a param. – David Groff Oct 30 '13 at 19:18
2

There isn't a way to do create a resuable SQL block in the XML definition. As with any XML, you can use Entities (http://www.w3.org/TR/REC-xml/#sec-external-ent) to define reusable blocks, but as far as I know that don't allow you to specify parameters. If you can find a way to do it with standard XML syntax, though, the Liquibase XML parser should honor it. – Nathan Voxland Oct 30 '13 at 20:14

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

0

Did you check how to parameter your changelog?

According to docs:

Parameter values are looked up in the following order:

Passed as a parameter to your Liquibase runner;

As a JVM system property;

In the parameters block ( Tag) of the DatabaseChangeLog file itself.

edited Jun 20 '20 at 09:12

Community

1
1

answered Apr 01 '14 at 21:16

rodrigocprates

498
6
22

How do you refactor similar looking code and provide params in Liquibase?

2 Answers2