Q:

Configuring HSQL Server for Hibernate tests

I'm just starting with Hibernate and getting my head around things.
Currently I'm trying to setup a testing environment where I can use a HSQL in-memory instance to test my project. 
The error I'm running into is:
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: invalid schema name: TSG

Here are the relevant parts of my project:
persistence.xml
<?xml version="1.0" encoding="UTF-8"?>

    
        org.hibernate.ejb.HibernatePersistence
        com.foo.api.models.tsg.AlgPpcAlgorithmOutputEntity
        
        
            
            
            
            
            
            
            
            
        
    
<persistence-unit name="TestingPersistenceUnit" transaction-type="RESOURCE_LOCAL">
    <provider>org.hibernate.ejb.HibernatePersistence</provider>
    <class>com.foo.api.models.tsg.AlgPpcAlgorithmOutputEntity
    </class>
    <properties>
        <property name="dialect" value="org.hibernate.dialect.HSQLDialect"/>
        <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver"/>
        <property name="hibernate.connection.url" value="jdbc:hsqldb:mem:tsg"/>
        <property name="hbm2ddl.auto" value="create-drop"/>
        <property name="hibernate.connection.autocommit" value="true"/>
        <property name="hibernate.connection.username" value="sa"/>
        <property name="hibernate.connection.password" value=""/>
        <property name="hibernate.show_sql" value="true"/>
    </properties>
</persistence-unit>

As you can see, I have one peristence-unit for production (which works fine) and one in-memory HSQL one for testing (which I can't get to work).
An example Hibernate entity:
package com.foo.api.models.tsg;

import javax.persistence.*;
import java.math.BigDecimal;

@IdClass(AlgPpcAlgorithmOutputEntityPK.class)
@Table(name = "alg_ppc_algorithm_output", schema = "", catalog = "tsg")
@Entity
public class AlgPpcAlgorithmOutputEntity {
    private int parameterId;

    @Column(name = "parameter_id")
    @Id
    public int getParameterId() {
        return parameterId;
    }

    public void setParameterId(int parameterId) {
        this.parameterId = parameterId;
    }

    private String matchType;

    @Column(name = "matchType")
    @Basic
    public String getMatchType() {
        return matchType;
    }

    // for brevity I have removed the rest of the implementation
    // It was auto-generated by Hibernate and works fine in production.
}

Finally, a simple TestCase class:
package tests.integration;

import com.foo.api.models.tsg.AlgPpcAlgorithmOutputEntity;
import com.foo.api.util.HibernateUtil;
import org.hsqldb.Server;
import org.hsqldb.persist.HsqlProperties;
import org.hsqldb.server.ServerConfiguration;
import org.junit.After;
import org.junit.Before;
import org.junit.Test;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import tests.util.HSQLServerUtil;

import javax.persistence.EntityManagerFactory;
import javax.persistence.EntityManager;
import javax.persistence.Persistence;

import com.foo.api.KeywordManager;

import java.sql.Date;
import java.util.HashSet;

public class KeywordManagerTestCase {

    private static final Logger LOG =   LoggerFactory.getLogger(KeywordManagerTestCase.class);
    private EntityManagerFactory eMF;
    protected EntityManager eM;

    @Before
    public void setUp() throws Exception {

        HsqlProperties props = new HsqlProperties();
        props.setProperty("server.database.0", "mem:tsg");
        props.setProperty("server.dbname.0", "tsg");

        ServerConfiguration.translateDefaultDatabaseProperty(props);

        Server hsqlServer = new Server();
        hsqlServer.setRestartOnShutdown(false);
        hsqlServer.setNoSystemExit(true);
        hsqlServer.setProperties(props);
        hsqlServer.setTrace(true);

        LOG.info("Configured the HSQLDB server...");
        hsqlServer.start();
        LOG.info("HSQLDB server started on port " + hsqlServer.getPort() + "...");

        LOG.info("Loading hibernate...");
        if (eMF == null) {
            eMF = Persistence.createEntityManagerFactory("TestingPersistenceUnit");
        }

        eM = eMF.createEntityManager();
    }

    /**
     * shutdown the server.
     * @throws Exception in case of errors.
     */
    @After
    public void tearDown() throws Exception {
        eM.close();
        HSQLServerUtil.getInstance().stop();
    }

    /**
     * Demo test to see that the number of user records in the database corresponds the flat file inserts.
     */
    @Test
    public void testDemo1() {
        AlgPpcAlgorithmOutputEntity entity = new AlgPpcAlgorithmOutputEntity();
        entity.setParameterId(200);
        entity.setMatchType("aa");
        KeywordManager km;
        eM.persist(entity);

        HashSet<Integer> params = new HashSet<Integer>();
        params.add(200);
        km = new KeywordManager(eM, params, new Date[2]);

        HashSet<AlgPpcAlgorithmOutputEntity> res = km.pullKeywords(params);
        for (AlgPpcAlgorithmOutputEntity s : res) {
            System.out.println(s.getMatchType());
        }

    }

}

I'm sure I have set something up in a strange way, but as I say - this is my very first stab.
Here is what I'm trying to do:

Have the testing configuration (along with all the hibernate class mapping)for the HSQL db present in persistence.xml
Have the HSQL db start for unit tests and create and in-memory database with my project's schema (as described in persistence.xml under the class elements).
Create entity objects and add them to the test db at test-time so I can use the db for my integration tests.

I just can't get past this PersistenceException!
UPDATE
OK, so I realised that I didn't need to setup an explicit HSQL server in the setup of my test cases, because that's exactly what the persistence-unit entry in my persistence.xml is for. Also I have attempted to ALTER the CATALOG so that it matches the catalog used by my mapping classes. The setup of my test case now looks like:
private EntityManagerFactory eMF;
protected EntityManager eM;

@Before
public void setUp() throws Exception {
    LOG.info("Loading hibernate...");
    if (eMF == null) {
        eMF = Persistence.createEntityManagerFactory("TestingPersistenceUnit");
    }

    eM = eMF.createEntityManager();

    EntityTransaction eT = null;
    eT = eM.getTransaction();
    eT.begin();
    Query q = eM.createNativeQuery("ALTER CATALOG PUBLIC RENAME TO TSG");
    q.executeUpdate();
    eT.commit();

    // And also it seems I need to create the schema
    eT = eM.getTransaction();
    eT.begin();
    q = eM.createNativeQuery("CREATE SCHEMA TSG AUTHORIZATION DBA");
    q.executeUpdate();
    eT.commit();
}

However, I just end up with a new error now, specifically: 
javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: user lacks privilege or object not found: ALG_PPC_ALGORITHM_OUTPUT

So I'm getting somewhere but it seems the tables are not being created. I wonder if there is an issue with my persistence.xml?

A:

As far as I know (and I am not a HSQL expert), the TSG you specify in the connection url is the database name which is not the same as the catalog.  See http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#dbc_schemas_schema_objects

In HyperSQL, there is only one catalog per database. The name of the catalog is PUBLIC. You can rename the catalog with the ALTER CATALOG RENAME TO statement. All schemas belong the this catalog. The catalog name has no relation to the file name of the database.

As I read that, when HSQL creates a database it creates a catalog named PUBLIC within that database and a schema named PUBLIC within that catalog.  There can only ever be one catalog per HSQL database.  There can be multiple schemas within that single catalog.
The error you are getting comes actually from the attempt to specify catalog = "tsg" in your mapping.  That catalog does not exist.  Since the HSQL database can contain only one catalog, you will have to rename that PUBLIC catalog to TSG (or change up your mapping).

A:

You are specifying server name in the URL but trying to use memory database, which is causing the issue.
Try using the DB URL as:
           jdbc:hsqldb:mem:tsg

i.e.
  <property name="hibernate.connection.url" value="jdbc:hsqldb:mem:tsg"/>

Also use ALTER CATALOG RENAME TO tsg to change the default catalog name(PUBLIC).
EDIT: To auto create the schema, update below in persistence.xml (hibernate.hbm2ddl.auto in place of hbm2ddl.auto)
    <property name="hibernate.hbm2ddl.auto" value="create-drop"/>

