Database Configuration

By default, GPIR comes with a pre-configured HypersonicSQL database that runs "In-process" inside of the Tomcat web container. Of course, this is not the most optimal configuration for a production service. Below are some alternative configurations that will perform better and be more reilable.

Configure HypersonicSQL Server Mode

This configuration is most ideal if you have already added data to the default HypersonicSQL in-process database that is included with a standard GridPort installation and don't want to migrate the data to another database server. It takes advantage of the fact that both 'In-Process' and 'Server' modes of HypersonicSQL use the same database directory structure, which makes migration simple.

First, you will need access to the hsqldb-version.jar file. This file can be found in the $CATALINA_HOME/webapps/gpir/WEB-INF/lib directory inside your gpir webapp You will need to specify the location of this file in a later step (NOTE: You do not need to copy it anywhere).

It is recommended that you copy the current GPIR database located in $CATALINA_HOME/webapps/gpir/WEB-INF/db to another appropriate location outside of the gpir webapp. This will prevent the database from being overwritten or deleted if at any point you need to upgrade GPIR. Once you've copied the directory, check to see that there's not a gpir.lck file present and if there is, remove it.

Use the following single command to start the database server (NOTE: the command spans two lines here so that it will fit): 

 
localhost> java -cp [path to hsqldb.jar] org.hsqldb.Server -database.0 \
        [path to database dir]/gpir -dbname.0 gpir
The "gpir" part of the path in the -database.0 argument is important because it tells the server to find all files in that start with the prefix "gpir.". This will be enough to test your database configuration but you will most likely want to consult the HypersonicSQL documentation on how to run Hsqldb as a System Daemon.

You will also need to modify the db.url property in the project.properties file in the GPIR source directory. 


db.url=jdbc:hsqldb:hsql://localhost:9001/gpir
Redeploy GPIR in order for the changes to take effect. 

maven install
GPIR is now configured to use the new Server instance rather than the In-process database. Restart Tomcat to run GPIR with the new configuration.

Configure PostgreSQL

Assuming you have installed and configured PostgreSQL database according to the documentation at http://www.postgresql.org you will need to configure GPIR to use it as its data source. Open the project.properties file in the GPIR source directory in your favorite editor. We have provided you with a boilerplate PostgreSQL configuration that you can customize for your own configuration. First, comment out the HypersonicSQL configuration and uncomment the PostgreSQL configuration. 


# Hypersonic SQL
#db.type=hypersonicsql
#db.driver=org.hsqldb.jdbcDriver
#db.url=jdbc:hsqldb:file:${env.CATALINA_HOME}/webapps/gpir/WEB-INF/db/gpir
#db.username=sa
#db.password=
#hibernate.dialect=net.sf.hibernate.dialect.HSQLDialect

# PostgreSQL
db.type=postgresql
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://localhost:5432/GPIR
db.username=
db.password=
hibernate.dialect=net.sf.hibernate.dialect.PostgreSQLDialect
Depending on where you installed PostgreSQL (local or remote relative to where the GPIR service is running) you will probably need to modify the db.url property accordingly with the correct hostname and port number (NOTE: 5432 is the default PostgreSQL port number). You will also need to modify the db.username and db.password properties with your PostgreSQL username and password.

Deploy GPIR making sure that you specify the -Dinitdb=true argument to initialize the database (NOTE: this assumes a clean installation of PostgreSQL that doesn't already have a GPIR database initialized). 

 maven install -Dinitdb=true