Tables and schema lost, hsqldb and maven sql plugin

Question

I am developping a web application using Spring (3.1.x), JSF 2, JPA 2 (Hibernate Provider) for tomcat 6.x. I want to test my DAO classes. My application database is under MySql. For the test i want to use HSQLDB in memory.

I made some scripts who create the schema and the tables under hsqldb, I call them with maven sql plugin.

pom.xml:

<plugin>
                <groupId>org.codehaus.mojo</groupId>
                <artifactId>sql-maven-plugin</artifactId>
                <version>1.5</version>

                <dependencies>
          <!-- specify the dependent jdbc driver here -->
                    <dependency>
                        <groupId>hsqldb</groupId>
                        <artifactId>hsqldb</artifactId>
                        <version>1.8.0.10</version>
                    </dependency>
                </dependencies>

        <!-- common configuration shared by all executions -->
                <configuration>
                    <driver>org.hsqldb.jdbcDriver</driver>
                    <url>jdbc:hsqldb:mem:testOpen</url>
                    <username>sa</username>
                    <password></password>
          <!-- You can comment out username/password configurations and
               have maven to look them up in your settings.xml using ${settingsKey}
          -->
                    <settingsKey>sensibleKey</settingsKey>
          <!--all executions are ignored if -Dmaven.test.skip=true-->
                    <skip>${maven.test.skip}</skip>
                </configuration>

                <executions>
                    <execution>
                        <id>drop-db-before-test-if-any</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
              <!-- need another database to drop the targeted one -->
                            <url>jdbc:hsqldb:mem:testOpen</url>
                            <autocommit>true</autocommit>
                            <sqlCommand>DROP SCHEMA testOpen CASCADE</sqlCommand>
              <!-- ignore error when database is not avaiable -->
                            <onError>continue</onError>
                        </configuration>
                    </execution>

                    <execution>
                        <id>create-db</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <url>jdbc:hsqldb:mem:testOpen</url>
              <!-- no transaction -->
                            <autocommit>true</autocommit>
                            <sqlCommand>CREATE SCHEMA testOpen AUTHORIZATION DBA</sqlCommand>
                        </configuration>
                    </execution>

                    <execution>
                        <id>create-tables</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <url>jdbc:hsqldb:mem:testOpen</url>
                            <autocommit>true</autocommit>
                            <srcFiles>
                                <srcFile>conf/script_sql/hsqldb/create_tables.sql</srcFile>
                            </srcFiles>
                        </configuration>
                    </execution>

                    <execution>
                        <id>check-data</id>
                        <phase>process-test-resources</phase>
                        <goals>
                            <goal>execute</goal>
                        </goals>
                        <configuration>
                            <url>jdbc:hsqldb:mem:testOpen</url>
                            <autocommit>true</autocommit>
                            <printResultSet>true</printResultSet>
                             <sqlCommand>SELECT * FROM INFORMATION_SCHEMA.SYSTEM_COLUMNS WHERE TABLE_NAME NOT LIKE 'SYSTEM_%'</sqlCommand>
                        </configuration>
                    </execution>

            </executions>
            </plugin>

persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://java.sun.com/xml/ns/persistence
      http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
    version="2.0">
    <persistence-unit name="C4OpenPU" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <non-jta-data-source>java:comp/env/jdbc/open_tomcat</non-jta-data-source>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLInnoDBDialect" />
            <property name="hibernate.show_sql" value="true"/>
            <property name="hibernate.transaction.flush_before_completion" value="true"/>
            <property name="hibernate.cache.provider_class" value="org.hibernate.cache.HashtableCacheProvider"/>
            <property name="hibernate.connection.zeroDateTimeBehavior" value="convertToNull"/>
        </properties>
    </persistence-unit>

    <persistence-unit name="C4OpenTestPU" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>
        <properties>
            <property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect" />
            <property name="hibernate.show_sql" value="true" />
            <property name="hibernate.connection.driver_class" value="org.hsqldb.jdbcDriver" />
            <property name="hibernate.connection.username" value="sa" />
            <property name="hibernate.connection.password" value="" />
            <property name="hibernate.connection.url" value="jdbc:hsqldb:mem:testOpen" />
            <property name="hibernate.default_schema" value="testLineopen"/>
        </properties>
    </persistence-unit>
</persistence>

The problem is when my test class is called during the test phase my schema didn't seems to be existing. In the check-data execution the tables exists in the good schema. Where is the problem ? Where are the tables ?

EDIT : I also try with file database but it fails, problem of lock. Database lock acquisition failure: lockFile.

Thanks you.

Answer 1

The "Database lock acquisition failure" error you reported clearly indicates the tests are running in different JVM's.

Your best option for testing is an HSQLDB Server instance with the remote_open option. This option cretes a database (which can be an in-memory database) in the server when you make the first connection. See http://hsqldb.org/doc/2.0/guide/listeners-chapt.html for details and examples.

If you are running the tests several times with the same Server instance, you can drop the schemas at the beginning of each run of the test suite.

Answer 2

Likely reason is that tests run in different JVM and they do not know anything about in-memory HSQLDB in some other JVM. Does it work if you use file based instead of in-memory?