Using liquibase parameter inside loadData CSV

4
votes

I want to abstract functions for generating uuid for each dbms:

<property name="uuid_function" value="gen_random_uuid()" dbms="postgresql"/>
<property name="uuid_function" value="NEWID()" dbms="mssql"/>
<property name="uuid_function" value="sys_guid()" dbms="oracle"/>

And then using this property in my CSV which is loaded by loadData like this:

"${uuid_function}"

But it is not working (parse error on $). The other approach with default values on column inside loadData:

<column name="id" type="COMPUTED" defaultValueComputed="${uuid_function}"/>

..is also not working. Id column is defined as NOT NULL and if I leave the column out of my CSV it complains that columns can't be null, even tho it should use the default value.

How do I use parameters inside my CSV files and/or why is the default value failing me?

2
liquibase

2 Answers

3
votes

I have it working very similar as you describe, so I think it can help you, but instead with a UUID, in my case it is with a Date, any way, the same case.

In my changelog, I have a property to calculate current date:

<property name="now" value="CURRENT_DATE" dbms="hsqldb" />

Then, I have a user table which is created with this changeSet:

<changeSet author="miguel" id="1448735850226-1">
    <createTable tableName="usuario">
        <column name="login" type="VARCHAR(255)">
            <constraints nullable="false"/>
        </column>
        <column name="oid" type="VARCHAR(255)"/>
        <column name="fecha" type="date" defaultValueComputed="${now}">
            <constraints nullable="false"/>
        </column>
        <column name="nombre" type="VARCHAR(255)"/>
        <column name="apellidos" type="VARCHAR(255)"/>
        <column name="activo" type="BOOLEAN"/>
    </createTable>
</changeSet>

As you can see, fecha column is not null and it has defaultValueComputed attribute to ${now} function.

I have a users.csv file that create test users (usuarios-test.csv):

login;oid;nombre;apellidos;activo
user;b776052e-7c9f-11e5-8584-67d602646e6f;Prueba;Prueba;true

As you can see, in the csv file I use headers line and I don't include fecha field. (In my case, UUID are pre-generated, but I could change it as you are).

Finally I have this loadData change

<changeSet author="miguel" id="1448735850226-16">
    <loadData tableName="usuario" encoding="UTF-8" 
      file="src/main/resources/liquibase/changelogs/usuarios-test.csv"
      quotchar="'" separator=";" >
      <column header="login" name="login" type="STRING"/>
      <column header="oid" name="oid" type="STRING"/>
      <column header="nombre" name="nombre" type="STRING"/>
      <column header="apellidos" name="apellidos" type="STRING"/>
      <column header="activo" name="activo" type="BOOLEAN"/>
    </loadData>
</changeSet>

As you can see, I declared all columns present into .csv file and fecha is not included, so defaultValueComputed works and I get users created with current date.

Hope it helps!

2
votes

type="COMPUTED" is wrong. You need to use a correct datatype, but as the datatype for a UUID is different between the DBMS, you also need to create a property for that as well.

The following works for me in Oracle and Postgres:

  <property name="uuid_function" value="gen_random_uuid()" dbms="postgresql"/>
  <property name="uuid_function" value="NEWID()" dbms="mssql"/>
  <property name="uuid_function" value="sys_guid()" dbms="oracle"/>

  <property name="uuid_type" value="uuid" dbms="postgresql"/>
  <property name="uuid_type" value="uniqueidentifier" dbms="mssql"/>
  <property name="uuid_type" value="RAW(32)" dbms="oracle"/>

  <changeSet author="testing" id="1">
    <createTable tableName="foo">
      <column name="id" type="${uuid_type}" defaultValueComputed="${uuid_function}">
        <constraints nullable="false"/>
      </column>
    </createTable>
  </changeSet>

This generates the following table in Postgres:

CREATE TABLE foo
(
   id  uuid    DEFAULT gen_random_uuid() NOT NULL
);

and in Oracle: 

CREATE TABLE FOO
(
   ID  RAW(32)   DEFAULT sys_guid() NOT NULL
);