Chapter 7. Upgrading and Database Migration

Upgrading to higher versions of JTrac is usually as simple as replacing the WAR file and deleting the web-app server temporary / work folder before re-starting. In some rare cases the database schema may have changed and a script for updating the database would then be provided. It may also happen that upgrading the Jetty version would be recommended in case you happen to be using the bundled Jetty webapp-server for deploying JTrac.

7.1. Deleting webapp temporary files

In case you are using the bundled Jetty server in the JTrac distribution, you should delete the contents of the folder called "work". Note that Jetty expects a folder called "work" to exist when it starts, so ensure that you leave the empty "work" folder in place.

If you are using another application server please follow the steps for removing temporary files corresponding to the server that you are using.

7.2. Upgrading the Database

If the JTrac release notes mention database schema changes, you need to additionally run an upgrade script before restarting the webapp-server with the new WAR in place. Upgrade scripts come in the form of Beanshell scripts (*.bsh) and would be always released within the "etc" folder of the JTrac downloadable distribution.

These files are tested on HSQLDB and may need minor changes to work with other databases. No re-compilation is needed, therefore making it extremely easy for you to tweak them if required. Of course it is strongly recommended that you backup your old data before an upgrade. Please use the JTrac forums for help and for helping other users with your experiences.

If you are shadowing the development builds you would be able to obtain the latest database upgrade scripts from here: http://j-trac.svn.sourceforge.net/viewvc/j-trac/trunk/jtrac/etc/ Look for files that end with ".bsh" - e.g: "jtrac-hsqldb-2_0rc3-to-2_0.bsh"

Steps to follow:

  • First, stop the server, JTrac should not be connected to the database.

  • The "java" command should be available on the command line.

  • Get the latest beanshell jar file (~300 KB) from here: http://www.beanshell.org/bsh-2.0b4.jar

  • Get the right JDBC driver for your database. If you have been using the embedded HSQLDB database, hsqldb-x-x-x.jar will be available in the jtrac war file within "lib".

  • Edit the connection params (jdbc URL, username, password) to match your database. For HSQLDB, to make it easier - you can copy the beanshell jar and the hsqldb jar into the "db" folder of "jtrac.home". The db folder will contain a "jtrac.script" file. There may also be a "jtrac.log" file. So when you are in the same directory as the HSQLDB data files, the URL is "jdbc:hsqldb:file:jtrac". Otherwise, replace the "jtrac" part in the URL with the relative or absolute path of where the "jtrac.script" file exists on your file-system.

  • Execute the beanshell script from the command line. The database driver should be added to the classpath along with the beanshell jar. This is how the command would look like:

    
    java -cp bsh-2.0b4.jar;hsqldb-1.8.0.1.jar bsh.Interpreter jtrac-hsqldb-2_0rc3-to-2_0.bsh
                    

  • If there were no errors, you can now upgrade the WAR (don't forget to delete the temporary files) and restart.

7.3. Upgrading Jetty

If you are using the embedded Jetty web-app server, you can also choose to upgrade the JAR files that make up Jetty. This is optional, but is as simple as overwriting the contents of the "lib" directory with the corresponding files within the new distribution. You can easily check if the Jetty related JAR file versions have changed because the file-names clearly include the version numbers.

7.4. Database Migration

Using the Beanshell based approach above, it is relatively straightforward to port data into another database. This is useful especially in those cases where you start off using JTrac with the embedded HSQLDB database and then decide to scale up and move to something like MySQL.

First, you should start the application re-configured to point to the new database and let JTrac do the hard work of creating all the database tables. It is not necessary for Jtrac to be shut-down before proceeding with the next step, but don't log-in yet!

A Beanshell script called "jtrac-hsqldb-to-mysql.bsh" is available in the "etc" folder of the JTrac distribution. You can execute this just like described in the previous section on upgrading. Note that you will need to have both the database drivers on the classpath in this case. You should of course edit the database connection parameters (for database 1 and database 2) to suit your environment.

This particular script is extremely generic and it should be possible to use this to migrate across combinations of databases other than HSQLDB and MySQL.