Liquibase Workflow - GeoscienceAustralia/egeodesy GitHub Wiki

TODO - syntax errror generated on GEOMETRY columns breaks updates part way though the application.
TODO - Ensure development always takes place against the latest baseline by making hibernate empty the database and create db objects before running a test - IS THIS A GOOD IDEA?

This document describes the process used at GA for capturing database structure changes and making them available for downstream deployment.

##Capturing a baseline

If your baseline is already set up, go directly to the next section

This is only done once at the begining, or if we need to reset the baseline at some point

Run a test, any test, against the external-db profile to populate the geodesydb postgres database with the current hibernate-state - note that this will not capture any non-hibernate changes made to the database

• Have a local copy of liquibase downloaded and available ( for this, I'm using 3.5.1 )
• change to your liquibase directory
• create a liquibase.properties file
• put your jdbc driver in the liquibase/lib dir, on you classpath, or specify it's location in the properties file.

url=jdbc:postgresql://localhost/geodesydb
username=geodesy
password=geodesypw
defaultSchemaName=geodesy
changeLogFile=baseline.xml

and run it to generate the change log

./liquibase   generatechangelog

you now have a file ( baseline.xml ) which captures the schema definition as created by Hibernate.

Copy this file to geodesy-domain-model/src/main/resources/db

Add it's definition to the main changelog - edit geodesy-database-changelog.xml to include the baseline, like this

<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
                   xmlns:ext="http://www.liquibase.org/xml/ns/dbchangelog-ext"
                   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog-ext http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-ext.xsd http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.3.xsd">
    <include file="db/geodesy-db-schema-baseline.xml" /> <!--your new baseline xml goes here -->
</databaseChangeLog>

• do some testing here to be sure that the baseline is correct
• watch out for case differences if you are changeing between different database engines

( Most people start here )

There are 3 Scenarios here

• Hand crafted changes to database structure ( ie, FK constraints )
• New objects ( created by Hibernate )
• Modifications to existing objects

And they are all the same as far as Liquibase is concerned - they make changes to a database which we need to capture, craft, and deploy.

###Doing it...

The first time the databases need to be setup. This can be achieved by running:

src/test/bash/createPostgresDB.sh

As per the header of the script run it with:

sudo -u postgres src/test/bash/createPostgresDB.sh

Except on Macs that don't need sudo.

At this point, we want to capture the differences between the current baseline and the new hibernate generated environment. The new hibernate generated environment should be based on the current baseline, plus any changes that Hibernate created, PLUS any hand crafted db structure changes, like foreign keys, triggers etc.

When work on a feature is complete get geodesydb into a state where it as all the changes you need to capture, and none that you don't. ie, run a test against your geodesydb database t

$ mvn test -D test=SiteLogRepositoryTest#saveAllSiteLogs -Pdb-external -DignoreDependencies

The pom.xml file has a profile, db-diff, which automates the much of the rest of the process

There are 3 steps

1. clean out the baseline/reference database
2. apply the current baseline definition to the baseline/reference database
3. capture the differences between the baseline/reference, and your working version as set up by your test above

$ mvn process-test-resources -P db-diff

You will end up with some output which looks like this:

[INFO] --- liquibase-maven-plugin:3.5.1:dropAll (postgres-drop) @ geodesy-domain-model ---
[INFO] ------------------------------------------------------------------------
[INFO] Parsing Liquibase Properties File
[INFO]   File: src/main/resources/liquibase-postgres-baseline.properties
[INFO]   'changeLogFile' in properties file is not being used by this task.
[INFO] ------------------------------------------------------------------------
[INFO] Executing on Database: jdbc:postgresql://localhost/geodesy_baseline_db
INFO 26/05/16 11:30 AM: liquibase: Successfully acquired change log lock
INFO 26/05/16 11:30 AM: liquibase: Dropping Database Objects in schema: geodesy_baseline_db.geodesy
[INFO] ------------------------------------------------------------------------
<snip>
[INFO] --- liquibase-maven-plugin:3.5.1:update (postgres-baseline-build) @ geodesy-domain-model ---
[INFO] ------------------------------------------------------------------------
[INFO] Parsing Liquibase Properties File
[INFO]   File: src/main/resources/liquibase-postgres-baseline.properties
[INFO] ------------------------------------------------------------------------
[INFO] Executing on Database: jdbc:postgresql://localhost/geodesy_baseline_db
<snip>
ChangeSet db/geodesy-sprint-2-changes.xml::1464225600439-3::simon (generated) ran successfully in 4ms
<snip>
[INFO] Parsing Liquibase Properties File
[INFO]   File: src/main/resources/liquibase-postgres-diff.properties
[INFO] ------------------------------------------------------------------------
[INFO] Executing on Database: jdbc:postgresql://localhost/geodesy_baseline_db
[INFO] Performing Diff on database geodesy @ jdbc:postgresql://localhost/geodesy_baseline_db (Default Schema: geodesy)
INFO 26/05/16 11:30 AM: liquibase: target/db-changelog-2016-05-26T01:30:21Z.xml does not exist, creating
[INFO] Differences written to Change Log File, target/db-changelog-2016-05-26T01:30:21Z.xml

**What does the diff do ? **

liquibase:diff 'Generates a diff between the specified database and the reference database. The output is either a report or a changelog depending on the value of the diffChangeLogFile parameter.'

##Incorporating the changes into the baseline

Check the new changelog to be sure it's doing what you want
The new changelog is identified at the end of the maven build process, and will look something like

[INFO] Differences written to Change Log File, target/db-changelog-2016-05-26T01:30:21Z.xml

copy the new changelog to the `src/main/resources/db' folder and rename it to something meaningful.

edit geodesy-database-changelog.xml to include the new changelog, like this

<?xml version="1.0" encoding="UTF-8"?>
 ...snipped...
    <include file="db/geodesy-db-schema-baseline.xml" />
    <include file="db/geodesy-new-db-changelog.xml" />
</databaseChangeLog>

##Test it If you re-run a maven build now, it will clean out the baseline db, apply the new changelog and compare it with your database.
There should be no changes found, and it will generate an empty changelog gile.

##if OK, commit bundle the changelog with your change so the db changes are automatically applied to the postgres db at deployment time, and for other devs.

##Extras

from http://forum.liquibase.org/topic/liquibase-diff-cannot-find-database-driver-23-1-2013

One nice undocumented thing is that liquibase will automatically add any jars that are present in its lib directory to the classpath. Don't have a lib directory? Make one, and put your database driver .jar files in there. Then you don't have to add them to any Liquibase classpath manually.

Another nice undocumented thing (noticing a trend?) is that the --driver argument is not required. JDBC will find the proper driver to handle the URL for you.

###How to Make Liquibase compare 2 postgres db's from the command line

You will need 3 liquibase properties files ( or specify 3 sets of properties on the command line)

1. Populate the first database via the baseline of Hibernate
2. (optional) enerate a change log of the structure from the first database only do this if you want to keep a record, or add it to your documented baseline

$ cat liquibase.properties-geodesydb
url=jdbc:postgresql://localhost/geodesydb
username=geodesy
password=geodesypw
defaultSchemaName=geodesy
changeLogFile=pg-baseline.xml

$ ./liquibase generatechangelog

Note and check the file pg-baseline.xml

3. check the differences between the 2 databases This option outputs to the screen in readable format so you can sanity check it

$ cat liquibase.properties-pg-pg-compare
#url=db with changes - new baseline.
referenceUrl=jdbc:postgresql://localhost/geodesydb
referenceUsername=geodesy
referencePassword=geodesypw

# reference=old baseline ( usually your slightly behind postgresdb ) 
url=jdbc:postgresql://localhost/geodesy_baseline_db
username=geodesy
password=geodesypw
defaultSchemaName=geodesy

#Other params
changeLogFile=db-diff-changelog.xml

$ ./liquibase diff

4. generate a changeLog to capture the difference Use the same properties as for the above step

./liquibase diffChangeLog

Now you can use the generated changelog any way you need

5. Apply the captured changes to the db that needs updating

$ cat liquibase.properties
url=jdbc:postgresql://localhost/geodesy_baseline_db
username=geodesy
password=geodesypw
defaultSchemaName=geodesy
changeLogFile=db-diff-changelog.xml
$ 
$ ./liquibase update
Liquibase Update Successful

and check the db to make sure it updated successfully.

###Some Rough Notes

System.setProperty("h2.baseDir", "target/");
      final String h2Url = "jdbc:h2:./h2-test-db;INIT=CREATE SCHEMA IF NOT EXISTS geodesy;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE;AUTO_SERVER=TRUE";