Skip to content

Liquibase

Jump to bottom
Paul Schwartz edited this page Jun 2, 2015 · 2 revisions

Liquibase is a tool that allows us to apply database changes in an orderly and traceable manner. It is a developer and a deployment tool, not a runtime tool. The official site is http://www.liquibase.org/ We are using version 1.9.5 and it is not likely that we will upgrade to a more recent version

##Running Liquibase

The tool should be run from ...\openelisglobal-core\liquibase as: java -jar .\lib\liquibase-1.9.5.jar --contexts=<NameOfContext> update

The 'contexts' option lets you apply site specific change. Currently there are 6 contexts:

  • haiti
  • haitiLNSP
  • CDIRetroCI
  • CI_IPCI
  • ci_regional
  • ciLNSP
  • Kenya

Using liquibase this way assumes that liquibase.properties is configured correctly. Before you run the commands for the first time review that file and make sure that the name of database being updated is correct and the name and password are like wise correct. The example below will update the htLNSP database (The name of the database and the name of the context may not be the same) with a username of clinlims and password of clinlims

driver: org.postgresql.Driver
classpath: ./lib/postgresql.jar
url: jdbc:postgresql://localhost:5432/htLNSP 
username: clinlims
password: clinlims
defaultSchemaName: clinlims
changeLogFile: ./changeLogs.xml
contexts: ''

If you get an error message saying that check-sums do not match see the UTF-8 note at the bottom of the page

##1.9.5 notes

  1. The 'remarks' attribute does not for postgres. If you need remarks add them as a sql comment
  2. The inline constraints do not work in the 'Add Column' context. The work around is to make the constraints a separate task in the change set
  3. The graphical tools are sort of flacky, even the IntelliJ one which is currently the most stable

##Using contexts

Contexts allow change sets to be applied conditionally. To use a context:

  1. Add optional context to change set i.e. <changeset id ="1" author="bob" context="ProjectA" >....</changeset>
  2. Add the context to the command line i.e. java -jar .\lib\liquibase-1.9.5.jar --contexts=ProjectA update
  3. avoid runAlways="true"

##Gotcha's

  • Because the context is not specified the following seems as though it should not apply the ProjectA changeset: java -jar .\lib\liquibase-1.9.5.jar update however.... it does. It is documented but it is not obvious. If the context is not specified on the command list then all change sets are applied We have come up with a workaround to restore the expected behavior, we added an empty contexts to liquibase.properties.

  • Constraint Bug When you add a column adding a constraint as in

    <addColumn ...><column.....><constraints ..../></columns></addColumn> it fails silently. You have to add the column and then the constraint as in: <addColumn ...><column...../></addColumn><addforgienKeyConstraint ..../>

##Example command lines openElis Contexts and Databases to Update

###Updating a Côte d'Ivoire LNPS Database ####With the database specified on command line

java -jar .\lib\liquibase-1.9.5.jar --contexts=CDIRetroCI --url=jdbc:postgresql://localhost:5432/cdielis update #####With the database specified in liquibase.properties java -jar .\lib\liquibase-1.9.5.jar --contexts=CDIRetroCI update

####Updating a Haiti LNSP Database `java -jar .\lib\liquibase-1.9.5.jar --contexts=haitiLNSP update'

####Updating a Haiti Clinical Database 'java -jar .\lib\liquibase-1.9.5.jar --contexts=haiti update'

##Using functions in SQL statements

Some operations such as inserting the current date into a new table row are done using functions. Functions can be used as values in insert statements. The only tricky part is when a quoted string is needed as part of the function. The following will work and although it may not be apparent there are spaces on either side of the ')'. Without the spaces the character is dropped.

<insert>   
   <column name="id" valueNumeric=" nextval( 'organization_type_seq' ) " />
   <column name="short_name" value="Centerredre"/>
   <column name="description" value="Centerr or Service Organization"/>
   <column name="name_display_key" value="db.organization.type.name.center"/>
   <column name="lastupdated" valueDate='now()' />
 </insert>

##Troubleshooting liquibase SQL Normally liquibase will only report success or failure on updates. If there is an error then rerunning the update with If you run liquibase with --logLevel=fine it will give you more information about the error

example

java -jar .\lib\liquibase-1.9.5.jar --contexts=CDIRetroCI --logLevel=fine update

##Troubleshooting liquibase SQL statements in PGAdmin Just to make trouble shooting the liquibase errors easer and not have to fully qualify tablebase names if you add the following at the top in pgAdmin SQL view then it will be the same as what liquibase does

SET search_path TO clinlims;

##UTF-8 issues If you are developing on a windows machine (or Linux with the LC_ALL, LC_CTYPE and LANG environmental variables not set correctly) then you may get check sum errors when you run liquibase. The reason is that the check sum is dependent on the encoding and if the database you are updating was updated on a machine with a different encoding then the check sums will not match. This has nothing to do with the database encoding, it is the encoding of the shell.

The fix for it is to set the encoding when you run liquibase. As an example:

java -jar -Dfile.encoding=UTF-8 .\lib\liquibase-1.9.5.jar --contexts=haiti update

The key is -Dfile.encoding=UTF-8

Clone this wiki locally