Flyway_Integration

• Introduction
• Step by Step guide to setup Flyway command line tool
• Step by Step guide to update to a new revision
• Step by Step guide to create a new revision
• References

Introduction

Flyway is a tool for handling migrations between revisions on a database. This is a guide for demonstrating how to download and update to a revision.

Step by Step guide to setup Flyway command line tool

In order to migrate an existing database into a new database, there are several files that need to be downloaded and configured before this will work.

You need the latest derby files. These can be downloaded here: http://db.apache.org/derby/derby_downloads.html
You will also need the latest flyway COMMAND line tool. This can be downloaded here: http://flywaydb.org/getstarted/download.html

First step: Setup Derby paths.

1.) Unzip the derby zip into a directory. This directory should be for library use, so for a local installation you should place it within ~/usr/local.
2.) Configure the DERBY_HOME and DERBY_INSTALL and JAVA_HOME directories. DERBY_INSTALL and DERBY_HOME should both point to the same directory for where the unzipped file is located. In this instance, we will use the example of /home/user/usr/local/db-derby-bin/. These should be exported as well.
3.) Export the CLASSPATH for the 4 derby*.jar files.

For help with steps 2, and 3, see below (please note user = username on OS):

#JAVA_HOME
JAVA_HOME=/usr/lib/jvm/java-1.7.0-openjdk-1.7.0.19.x86_64/jre

#Derby Paths
DERBY_INSTALL=/home/user/usr/local/db-derby-bin/
DERBY_HOME=/home/user/usr/local/db-derby-bin/

#Exports
export JAVA_HOME
export DERBY_HOME
export DERBY_INSTALL
export CLASSPATH=$DERBY_INSTALL/lib/derby.jar:$DERBY_INSTALL/lib/derbytools.jar:$DERBY_INSTALL/lib/derbyclient.jar:$DERBY_INSTALL/lib/derbynet.jar

Restart your bash (. ~/.bashrc) as needed.

Second step: Setup Flyway command tool.

1.) Unzip the tool. This can also be done in usr/local as well.
2.) Configure the conf/flyway.properties file. An example can be listed below (for derby):

flyway.url=jdbc:derby:/home/user/NiCEFiles/derby/database/datastructuresDatabase
flyway.user=APP
flyway.password=APP

Remember, NiCE has two databases to handle (Datastructures and item). The flyway.url will need to be reconfigured and sql files need to be copied for the itemDatabase.

3.) COPY derbyclient.jar, derby.jar, derbynet.jar into /jars folder within the command line tool. You can locate these files within the derby-db-bin/lib directory.

SQL files should be written for each revision as follows: V#__Base_version.sql where # corresponds to the numerical value of the revision.

Step by Step guide to update to a new revision

1.) RUN ./flyway.sh init to configure the metadata table for the database.
2.) DOWNLOAD latest SQL files. These can be located within the SF website under SQLFILES for the correct database. Place the *.sql files within this directory.
3.) RUN ./flyway.sh migrate to migrate the database. (This assumes that there is a database to migrate within the sql directory).

Step by Step guide to create a new revision

1.) In your flyway installation directory, go into the sql directory. Grab the latest *.sql files and place them into this directory for the SPECIFIC database you are working on.
2.) Make a new file. This should be the next increment in the database version number. V#_Base_version.sql is the naming convention of the database.
3.) Open the V#_Base_revision file, and add the adjustments according. An example is listed below:

ALTER Table "APP"."FORM"
DROP COLUMN "ASDFHI"

4.) Save it, close it.
5.) cd into your flyway installation directory and run ./flyway.sh migrate. This should migrate the database to the latest version. If an error occurs, it will rollback the database.

References

Flyway: www.flywaydb.org