01 HypersonicSQL

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.5.1 release uses the 1.7.2.4 release of Hypercontent.

Obtaining the Driver

The HypersonicSQL driver comes packaged with the database download. It is available from Sourceforge at http://hsqldb.sourceforge.net/ .

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/db 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.7.2.4</db-version>
      <driver-name>HypersonicSQL Driver</driver-name>
      <driver-version>1.7.2.4</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>

If using a different version of HSQL you will need to modify there strings for db-version and driver-version

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.

Additional notes

01. Starting HSQL
HSQL OutOfMemoryError

Issues and Known Bugs

The Sourceforge repository contains several available releases. Prior to version 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. See the HSQL OutOfMemoryError for a description of this problem.

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