Setting up A HypersonicSQL Database

Overview

Hypersonic SQL is an open source Java database engine with a standard SQL and JDBC interface. It is available from Sourceforge at http://hsqldb.sourceforge.net/ . The uPortal 2.1.3 release uses the 1.6.1 release.

Obtaining the Driver

Properties Configuration

The uPortal release is initially configured to use HypersonicSQL so configuration of properties most likely will not be necessary. The information provided here is mostly provided for verification that the system is set up correctly.

The uPortal build.properties may need to be modifed. Find the property called jdbcDriver and change it to point to the HypersonicSQL jar file, for example:

jdbcDriver.jar=./lib/hsqldb.jar

The uPortal rdbm.properties file (in the properties directory) may need to modified to specify the driver properties. The rdbm.properties file contains several sample entries. Uncomment (or add) the lines for the HypersonicSQL database and make whatever changes necessary to match your local database installation, For example:

#### HypersonicSQL
jdbcDriver=org.hsqldb.jdbcDriver
jdbcUrl=jdbc:hsqldb:hsql://localhost:8887
jdbcPassword=sa
jdbcPassword=

The dbloader.xml properties file (also in the properties directory) may also need to be modified. This file is used by the DbLoader tool to create the uPortal database tables and populate the database. It contains several sample entries which create db-type-mappings for different databases. Find the tags for an HypersonicSQL database and modify the db-version, driver-name, and driver-version as necessary. For example:

<db-type-mapping>
  <db-name>HypersonicSQL</db-name>
  <db-version>1.6.1</db-version>
  <driver-name>HypersonicSQL Driver</driver-name>
  <driver-version>1.6.1</driver-version>
  <type><generic>INTEGER</generic><local>INTEGER</local></type>
  <type><generic>VARCHAR</generic><local>VARCHAR</local></type>
  <type><generic>LONGVARCHAR</generic><local>LONGVARCHAR</local></type>
  <!-- map more types here -->
</db-type-mapping>

The PersonDirs.xml file (also in the properties directory) may need to be modified; This file is used if the database is to be used to provide user directory information

<!-- JDBC Properties -->
<driver>org.hsqldb.jdbcDriver</driver>
<url>jdbc:hsqldb:hsql://localhost:8887</url>
<logonid>sa</logonid>
<logonpassword></logonpassword>,
<uidquery>SELECT FIRST_NAME||' '||LAST_NAME AS FIRST_LAST,
FIRST_NAME, LAST_NAME, EMAIL FROM UP_PERSON_DIR WHERE USER_NAME=?></uidquery>

Loading the Database

Loading the database requires a couple of steps

• Create the database: Refer to the HypersonicSQL database documentation for instructions regarding how to create an HypersonicSQL database instances. The database name, port number, and driver information must reflect what was defined in the rdbm.properties file.
• Create the tables and populate the database: From the uPortal build directory, run "ant db" to execute the ant target which loads the database. The ant target uses the Dbloader tool.

NOTE: If you are working from the quickstart release the database comes packaged with the release and is preloaded so creating the database and running "ant db" is unnecessary.

Issues and Known Bugs

The Sourceforge repository contains several available releases. Prior to 1.6.1 the HypersonicSQL database was called "hsql" instead of "hsqldb" and the driver class names were different. If using an older release the properties configuration information will need to be modified.

Some people have encountered a problem with HypersonicSQL where the database fills up with thousands of rows in the UP_USER table with a user id of "0" and user name of "system" with the rest of the fields as null values. Deleting these rows seems to fix the problem.

HypersonicSQL is considered a lightweight database that is not recommended for production use in a large installation.