01 HypersonicSQL

Owned by John Fereira
Last updated: Apr 06, 2008 by Mark MarkVersion comment
6 min read

Contents of 01 Installation --> 05 Database --> 01 HypersonicSQL

Overview

Hypersonic SQL is an open source Java database engine with a standard SQL and JDBC interface. It is available from Sourceforge.

uPortal ships with a HSQL database to be used for basic development and testing. HsqlDb is not suitable for qa or production environments.

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.

JAR Configuration

The first step is to determine the correct information to use in /uPortal/pom.xml. Using a Maven Artifact Search Site and searching for "hsqldb" we find the following Maven dependency declaration here.

<dependency>
    <groupId>hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>1.8.0.7</version>
</dependency>

Opening /uPortal/pom.xml there is a section about 90 lines down that reads:

<!-- The JDBC Driver used by uPortal -->
<jdbc.groupId>hsqldb</jdbc.groupId>
<jdbc.artifactId>hsqldb</jdbc.artifactId>
<jdbc.version>${hsqldb.version}</jdbc.version>

The ${hsqldb.version} string refers to a property set in /uPortal/pom.xml, dependency versions are centralized in this pom to ensure they stay consistent between child modules in the project.

JDBC Configuration

Edit /uPortal/uportal-impl/src/main/resources/properties/rdbm.properties and uncomment the lines for hsql Modify the URL, username and password as appropriate:

##### Hypersonic SQL - Server mode
##### Requires first starting Hypersonic SQL with the command:
##### "ant hsql"
hibernate.connection.driver_class=org.hsqldb.jdbcDriver
hibernate.connection.url=jdbc:hsqldb:hsql://localhost:8887
hibernate.connection.username=sa
hibernate.connection.password=
hibernate.dialect=org.hibernate.dialect.HSQLDialect

The /uPortal/uportal-impl/src/main/resources/properties/dbloader.xml properties file 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>HSQL Database Engine</db-name>
    <db-version>1.8.0</db-version>
    <driver-name>HSQL Database Engine Driver</driver-name>
    <driver-version>1.8.0</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

Testing the Configuration

Start Postgres and then in your portal development directory, issue the command:

ant dbtest

If your configuration is correct the output should look very similar to:

Buildfile: build.xml

dbtest:

install-parent-pom:
[artifact:install] [INFO] Installing /home/edalquist/JavaClasses/workspace_33/uPortal/pom.xml to /home/edalquist/.m2/repository/org/jasig/portal/uportal-parent/3.0.0/uportal-parent-3.0.0.pom
    [touch] Creating /tmp/uportal-parent.pom-2117287939-marker
[artifact:dependencies] Downloading: javax/script/script-api/1.0/script-api-1.0.pom from jasig-repository
[artifact:dependencies] Downloading: javax/script/script-api/1.0/script-api-1.0.pom from central
[artifact:dependencies] Downloading: groovy/groovy-all/1.0/groovy-all-1.0.pom from jasig-repository
[artifact:dependencies] Downloading: groovy/groovy-all/1.0/groovy-all-1.0.pom from central
[artifact:dependencies] Downloading: javax/script/groovy-engine/1.0/groovy-engine-1.0.pom from jasig-repository
[artifact:dependencies] Downloading: javax/script/groovy-engine/1.0/groovy-engine-1.0.pom from central
     [echo] Artifact '/home/edalquist/JavaClasses/workspace_33/uPortal_trunk/uportal-impl/target/uportal-impl-3.0.0.jar' is up-to-date
[artifact:install] [INFO] Installing /home/edalquist/JavaClasses/workspace_33/uPortal_trunk/uportal-impl/target/uportal-impl-3.0.0.jar to /home/edalquist/.m2/repository/org/jasig/portal/uportal-impl/3.0.0/uportal-impl-3.0.0.jar
     [echo] Invoking DbTest
     [java]  INFO [main] spring.PortalApplicationContextLocator Mar/20 14:51:48.149 - Creating new lazily initialized GenericApplicationContext for the portal
     [java]  INFO [main] jpa.HibernateJpaVendorAdapter Mar/20 14:51:50.907 - Setting CacheProvider 'org.jasig.portal.utils.cache.hibernate.EhCacheProvider@1779885' on ThreadLocal
     [java] AbandonedObjectPool is used (org.apache.commons.dbcp.AbandonedObjectPool@42a6eb)
     [java]    LogAbandoned: true
     [java]    RemoveAbandoned: true
     [java]    RemoveAbandonedTimeout: 300
     [java]  INFO [main] jndi.DisposableMemoryContextFactory Mar/20 14:51:52.937 - Created new MemoryContext with environment '{java.naming.factory.url.pkgs=tyrex.naming, java.naming.provider.url=, java.naming.factory.initial=org.jasig.portal.jndi.DisposableMemoryContextFactory}'
     [java]  INFO [main] properties.PropertiesManager Mar/20 14:51:52.973 - Property [org.jasig.portal.car.CarResources.directory] was requested but not found.
     [java]  INFO [main] car.CarResources Mar/20 14:51:52.973 - CAR directory property 'org.jasig.portal.car.CarResources.directory' not specified. Defaulting to well-known directory '/WEB-INF/cars'.
     [java]  INFO [main] jndi.JndiManagerImpl Mar/20 14:51:53.081 - Initialized portal JNDI context
     [java]  INFO [main] spring.PortalApplicationContextLocator Mar/20 14:51:53.099 - Created new lazily initialized GenericApplicationContext for the portal in 4949ms
     [java] Parsing jar:file:/home/edalquist/JavaClasses/workspace_33/uPortal_trunk/uportal-impl/target/uportal-impl-3.0.0.jar!/properties/db/dbloader.xml...
     [java]  WARN [main] rdbm.DatabaseMetaDataImpl Mar/20 14:51:53.269 - The uPortal database is not initialized, the database tests will not be performed.
     [java]  INFO [main] rdbm.DatabaseMetaDataImpl Mar/20 14:51:53.270 - HSQL Database Engine (1.8.0) / HSQL Database Engine Driver (1.8.0) database/driver
     [java]     Connected To: jdbc:hsqldb:hsql://localhost:8887
     [java]     WARNING: uPortal tables do no exist, not all meta-data tests were executed.

     [java] Database name:    'HSQL Database Engine'
     [java] Database version: '1.8.0'
     [java] Driver name:      'HSQL Database Engine Driver'
     [java] Driver version:   '1.8.0'
     [java] Driver class:     'HSQL Database Engine Driver'
     [java] Connection URL:   'jdbc:hsqldb:hsql://localhost:8887'
     [java] User:             'SA'

     [java] Type Mappings: [Type[genericType=VARCHAR,local=VARCHAR], Type[genericType=INTEGER,local=INTEGER], Type[genericType=LONGVARCHAR,local=LONGVARCHAR]]

     [java] supportsANSI92EntryLevelSQL: false
     [java] supportsANSI92FullSQL:       false
     [java] supportsCoreSQLGrammar:     true
     [java] supportsExtendedSQLGrammar: false

     [java] supportsTransactions:         true
     [java] supportsMultipleTransactions: true
     [java] supportsOpenCursorsAcrossCommit:      false
     [java] supportsOpenCursorsAcrossRollback:    false
     [java] supportsOpenStatementsAcrossCommit:   true
     [java] supportsOpenStatementsAcrossRollback: true

     [java] supportsStoredProcedures:     true
     [java] supportsOuterJoins:           true
     [java] supportsFullOuterJoins:       false
     [java] supportsLimitedOuterJoins:    true
     [java] supportsBatchUpdates:         true
     [java] supportsColumnAliasing:       true
     [java] supportsExpressionsInOrderBy: true
     [java] supportsOrderByUnrelated:     true
     [java] supportsPositionedDelete:     false
     [java] supportsSelectForUpdate:      false
     [java] supportsUnion:                true
     [java] supportsUnionAll:             true

     [java] getMaxColumnNameLength: 0
     [java] getMaxColumnsInIndex:   0
     [java] getMaxColumnsInOrderBy: 0
     [java] getMaxColumnsInSelect:  0
     [java] getMaxColumnsInTable:   0
     [java] getMaxConnections:      0
     [java] getMaxCursorNameLength: 0
     [java] getMaxIndexLength:      0
     [java] getMaxRowSize:          0
     [java] getMaxStatements:       0
     [java] getMaxTableNameLength:  0
     [java] getMaxTablesInSelect:   0
     [java] getMaxUserNameLength:   0
     [java] getSearchStringEscape:  \
     [java] getStringFunctions:     ASCII,BIT_LENGTH,CHAR,CHAR_LENGTH,CHARACTER_LENGTH,CONCAT,DIFFERENCE,HEXTORAW,INSERT,LCASE,LEFT,LENGTH,LOCATE,LTRIM,OCTET_LENGTH,RAWTOHEX,REPEAT,REPLACE,RIGHT,RTRIM,SOUNDEX,SPACE,SUBSTR,SUBSTRING,UCASE,LOWER,UPPER
     [java] getSystemFunctions:     DATABASE,USER,IDENTITY
     [java] getTimeDateFunctions:   CURDATE,CURTIME,DATEDIFF,DAYNAME,DAY,DAYOFMONTH,DAYOFWEEK,DAYOFYEAR,HOUR,MINUTE,MONTH,MONTHNAME,NOW,QUARTER,SECOND,WEEK,YEAR,TO_CHAR

     [java] Table Types: GLOBAL TEMPORARY,SYSTEM TABLE,TABLE,VIEW
     [java] SQL Types:   TINYINT,BIGINT,LONGVARBINARY,VARBINARY,BINARY,LONGVARCHAR,CHAR,NUMERIC,DECIMAL,INTEGER,SMALLINT,FLOAT,REAL,DOUBLE,VARCHAR,VARCHAR_IGNORECASE,BOOLEAN,DATE,TIME,TIMESTAMP,OTHER

BUILD SUCCESSFUL
Total time: 11 seconds

Verify the values on the Database name, Database version, Driver name, Driver version match those entered in dbloader.xml exactly.

Additional notes

01. Starting HSQL
HSQL OutOfMemoryError

Issues and Known Bugs

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.