HypersonicSQL

Owned by Jen Bourey
Nov 19, 2014
8 min read

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.

Step 1: Configure the Database Filter     

     1. In the filters folder, locate the default local.properties file under uPortal-4.1.x/filters/local.properties and configure the Database Connection Settings

uPortal/filters/local.properties
# HSQL Configuration
environment.build.hsql.port=8887

# Database Connection Settings (Uncomment the Maven Filters section in rdbm.properties)
environment.build.hibernate.connection.driver_class=org.hsqldb.jdbc.JDBCDriver
environment.build.hibernate.connection.url=jdbc:hsqldb:hsql://localhost:${environment.build.hsql.port}/uPortal
environment.build.hibernate.connection.username=sa
environment.build.hibernate.connection.password=
environment.build.hibernate.dialect=org.hibernate.dialect.HSQLDialect 
environment.build.hibernate.connection.validationQuery=select 1 from INFORMATION_SCHEMA.SYSTEM_USERS

 

Step 2: Add the database driver 

  1. NEW: Open uportal-db/pom.xml file, uncomment and/or modify as needed the driver of your choice

  2. Add the appropriate version properties to the root pom.xml file or enter the appropriate version below
uportal-db/pom.xml
... 
<dependencies>
        <!-- Add any db drivers that are applicable to *any* of your environments -->
	    <dependency>
	        <groupId>org.hsqldb</groupId>
	        <artifactId>hsqldb</artifactId>
	        <version>${hsqldb.version}</version>
	        <scope>compile</scope>
	    </dependency>
        <!--
         | The following db drivers should be uncommented and/or modified as needed for server 
         | deployments.  (Add all thaat are needed.)  Don't forget to add appropriate  .version 
         | properties to the root pom.xml, or simply enter the appropriate version below.
         +-->
		<!--
	    <dependency>
            <groupId>postgresql</groupId>
	        <artifactId>postgresql</artifactId>
	        <version>${postgres.version}</version>
	        <scope>compile</scope>
	    </dependency>
	    <dependency>
	        <groupId>com.ibm.db2</groupId>
	        <artifactId>db2-jdbc</artifactId>
	        <version>${db2.version}</version>
	        <scope>compile</scope>
	    </dependency>
        <dependency>
            <groupId>com.microsoft.sqlserver</groupId>
            <artifactId>sqljdbc4</artifactId>
            <version>${mssql.version}</version>
        </dependency>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>${mysql.version}</version>
        </dependency>
        <dependency>
            <groupId>com.oracle</groupId>
            <artifactId>ojdbc6_g</artifactId>
            <version>${oracle.version}</version>
        </dependency>
        <dependency>
            <groupId>org.sybase</groupId>
            <artifactId>sybase-jconnect</artifactId>
            <version>${sybase.version}</version>
        </dependency>
	    -->
    </dependencies>

Step 3: Test the Configuration

Start hsqldb 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 GenericApplication...
     [java]  INFO [main] jpa.HibernateJpaVendorAdapter Mar/20 14:51:50.907 - Setting CacheProvider 'org.jasig.portal.utils.cache....
     [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]  INFO [main] properties.PropertiesManager Mar/20 14:51:52.973 - Property [org.jasig.portal.car.CarResources.directory] ...
     [java]  INFO [main] car.CarResources Mar/20 14:51:52.973 - CAR directory property 'org.jasig.portal.car.CarResources.directory'...
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 GenericApplication...
     [java] Parsing jar:file:/home/edalquist/JavaClasses/workspace_33/uPortal_trunk/uportal-impl/target/uportal-impl-3.0.0.jar!/properties/...
     [java]  WARN [main] rdbm.DatabaseMetaDataImpl Mar/20 14:51:53.269 - The uPortal database is not initialized, the database tests ...
     [java]  INFO [main] rdbm.DatabaseMetaDataImpl Mar/20 14:51:53.270 - HSQL Database Engine (1.8.0) / HSQL Database Engine Driver (1.8.0)...
     [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],...

     [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,...
     [java] getSystemFunctions:     DATABASE,USER,IDENTITY
     [java] getTimeDateFunctions:   CURDATE,CURTIME,DATEDIFF,DAYNAME,DAY,DAYOFMONTH,DAYOFWEEK,DAYOFYEAR,HOUR,MINUTE,...

     [java] Table Types: GLOBAL TEMPORARY,SYSTEM TABLE,TABLE,VIEW
     [java] SQL Types:   TINYINT,BIGINT,LONGVARBINARY,VARBINARY,BINARY,LONGVARCHAR,CHAR,NUMERIC,DECIMAL,INTEGER,SMALLINT,...

BUILD SUCCESSFUL
Total time: 11 seconds

Verify the values on the Database name, Database version, Driver name, Driver version match those entered in local.properties exactly.

Step 4: Build and Deploy 

Following a successful test, you can execute the command below to build the database tables and copy files to your servlet container. 

Note: Executing the command "ant clean initportal" will drop and recreate the database tables and all existing data will be lost. This will result in a clean uPortal database structure. If you want to keep the contents of your existing database, use "ant clean deploy-war"

ant clean initportal

Step 5: Restart Tomcat

 

Additional notes

With the quick-start distribution the ant 'start' and 'stop' targets start and stop both the hsqldb instance uPortal uses and the Tomcat instance uPortal is deployed to.

For the uPortal only distribution "ant hsql" will start the database and the "ant hsql-shutdown" target will stop it. Doing a CTRL^C in the window running the hsql database will also stop the database. The "-Dspawn=true" option can be specified with "ant hsql" to start the database in the background.

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. 

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

Development Tips

Clustered uPortal Servers sharing HSQL DB

Sometimes it is convenient to use the HSQL DB normally used in a local/development environment to test cluster-oriented features such as ehcache invalidation.  To do this with the bundled HSQL DB make the following changes:

filters/local.properties - change the DB hostname in the JDBC URI from localhost to the IP address of the machine HSQL DB is running on.

filters/local.properties
environment.build.hibernate.connection.url=jdbc:hsqldb:hsql://192.168.0.20:${environment.build.hsql.port}/uPortal

build.xml - on the machine the HSQL DB will run on, change the hostname in the launch parameters from localhost to the IP address of the local machine

build.xml
<!-- In the hsql ant target ... -->
<java fork="true" spawn="${spawn}" maxmemory="160M" dir="${basedir}" classname="org.hsqldb.server.Server" failonerror="${failonerror}">
    <classpath refid="hsql.classpath" />
    <arg value="--database.0" />
    <arg value="${database}" />
    <arg value="--dbname.0" />
    <arg value="uPortal" />
    <arg value="--address" />
    <arg value="192.168.0.20" />  <!-- was localhost -->
    <arg value="--port" />
    <arg value="${port}" />
</java>

Having problems with these instructions?

Please send us feedback at uportal-user@lists.ja-sig.org