Flyway Integration

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. You will need to do this if you update NiCE and there has been an update to the schema of the database.

The first section is an introduction to setting up the tools, but the later sections show how to modify the database for developers and users alike!

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, ICE 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 and there are sql files configured correctly in 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.

Notes:

• "APP" in the previous example represents the user name. "FORM" is the database table name.
• Assume every name (table, column, etc) is capitalized.
• An indepth example of how to deal with inbetween revisions is given below.

Having an "inbetween" revision database (or for newer users of NiCE)

For later revisions of the database, in order to handle the the differences between the use of flyway and the correct way to do migrations, some work on the user will need to be done in order to make this revision process work correctly.

1.) Figure out your revision number that is the latest for your database.
2.) Grab the correct files after your revision.
3.) Rename the revision files to their correct number.
4.) RUN java org.apache.derby.tools.dblook -d jdbc:derby:database > V1_Base_version.sql. This dumps the base schema into the version. Move the sql file to the /sql directory.
5.) RUN ./flyway.sh init
6.) RUN ./flyway.sh migrate

Now a more thorough example:

Now, lets assume you have NiCE executable and it has been a while since you updated. Since your install, there has been 3 new revisions to the database. Currently you are on revision 4 (you figured it out by looking at time stamps between the NiCE product and the dates within the SQL Files). You notice that the datastructuresDatabase has only been updated. Also note your user name for the computer you are working on is called bob.

The follow files are in SQLFILES for datastructuresDatabase:
V1_Base_version.sql
V2_Base_version.sql
V3_Base_version.sql
V4_Base_version.sql
V5_Base_version.sql
V6_Base_version.sql
V7_Base_version.sql

1.) You would clear out all the files within /sql directory of flyway.
2.) Copy V5_Base_version.sql, V6_Base_version.sql, V7_Base_version.sql. You would rename them to V2_Base_version.sql, V3_Base_version.sql, V4_Base_version.sql respectively.
3.) RUN java org.apache.derby.tools.dblook -d jdbc:derby:/home/bob/NiCEFiles/derby/database/datastructuresDatabase > V1_Base_version.sql. This dumps the base schema into the version. Move the sql file to the /sql directory.
4.) RUN ./flyway.sh init from the flyway root directory.
5.) RUN ./flyway.sh migrate

Thats it!

References

Flyway: www.flywaydb.org