HSQLDB 1.8 and multiple schemas with hbm2ddl

mwcmpbll · **Joined:** Tue Jun 14, 2005 4:06 pm **Posts:** 4

Hello all,

We use Oracle 9i for our production database, and I am trying to setup in-memory HSQLDB 1.8 to use for unit-testing. I am trying to use SchemaExport by setting the "hibernate.hbm2ddl.auto" property to "create-update" in my Configuration object.

The reason I am using HSQLDB 1.8 is that we have multiple schemas in our Oracle Databases, which are thus in our mapping files (using <schema> tags), and thus need to be in the test database. However, it seems that Hibernate doesn't create the schemas before it tries to create the tables, so I get the errors of the following form:

15:31:27,946 DEBUG SchemaExport:149 - create table OPTOOLS.Workorderfix (
remedyid integer not null,
sequencenumber integer not null,
fixid integer,
primary key (remedyid, sequencenumber)
)
15:31:27,946 ERROR SchemaExport:154 - Unsuccessful: create table OPTOOLS.Workorderfix (remedyid integer not null, sequencenumber integer not null, fixid integer, primary key (remedyid, sequencenumber))
15:31:27,946 ERROR SchemaExport:155 - invalid schema name: OPTOOLS in statement [create table OPTOOLS.Workorderfix]

Is there anything else I can change to make SchemaExport output the CREATE SCHEMA statements that HSQLDB needs?

I am using Hibernate 2.1.8 and I have already backported the changes to the HSQLDBDialect class. Since upgrading to Hibernate 3 isn't an option for me (there is no way I could get it on our project plan), I would be happy with a "Hibernate 3 already does that, and you can find the code in class X". And then I can make the changes for our unit-tests.

Thanks,

Matt

mwcmpbll · **Joined:** Tue Jun 14, 2005 4:06 pm **Posts:** 4

I also tried setting "hibernate.hbm2ddl.auto" property to "create" and got the same results.

Here is my code that builds that database:

Code:

public class HibernateTestUtil {
   
   private static final String jdbc_url = "jdbc:hsqldb:mem:OPTools";
   private static final String jdbc_username = "sa";
   private static final String jdbc_password = "";
   
   private static SessionFactory testSessionFactory;
   
   private static void initializeSessionFactory() throws HibernateException {
      Configuration config;

      config = new Configuration().
//            setProperty("hibernate.dialect", "net.sf.hibernate.dialect.HSQLDialect").
          setProperty("hibernate.dialect", "net.sf.hibernate.dialect.HSQLSeqDialect").
          setProperty("hibernate.connection.driver_class", "org.hsqldb.jdbcDriver").
          setProperty("hibernate.connection.url", jdbc_url).
          setProperty("hibernate.connection.username", jdbc_username).
          setProperty("hibernate.connection.password", jdbc_password).
          setProperty("hibernate.connection.pool_size", "2").
          setProperty("hibernate.connection.autocommit", "true").
          setProperty("hibernate.cache.provider_class", "net.sf.hibernate.cache.HashtableCacheProvider").
          setProperty("hibernate.hbm2ddl.auto", "create").
          setProperty("hibernate.show_sql", "true").
          addClass(Action.class).
          addClass(Actionrequiredpermission.class).
          addClass(Contract.class).
          addClass(Market.class).
          addClass(Systemuser.class).
          addClass(Transactiontype.class).
          addClass(Workorder.class).
          addClass(Workorderaudit.class).
          addClass(Workorderauthorization.class).
          addClass(Workordercheck.class).
          addClass(Workordercomment.class).
          addClass(Workordercorrection.class).
          addClass(Workorderfix.class).
          addClass(Workorderfixinfo.class).
          addClass(Workordergroup.class).
          addClass(Workordergrouping.class).
          addClass(Workorderinbox.class).
          addClass(Workorderoperation.class).
          addClass(Workorderoutbox.class).
          addClass(Workorderremedyinfo.class).
          addClass(Workorderrole.class).
          addClass(Workordershadow.class).
          addClass(Workordersymptom.class).
          addClass(Workordertransaction.class).
          addClass(Workordertransactioncomment.class).
          addClass(Workordertransactionreference.class).
          addClass(Workorderviewer.class);

      testSessionFactory = config.buildSessionFactory();
    }
   
   public static synchronized SessionFactory getSessionFactory()
         throws HibernateException {
      if (testSessionFactory == null) {
         initializeSessionFactory();
      }
      return testSessionFactory;
   }
}

Here's my HSQLDBSeqDialect class that adds the support for sequences from Hibernate 3. It remains untested as I can't get the database to load properly.

Code:

package net.sf.hibernate.dialect;

import java.sql.SQLException;

import net.sf.hibernate.dialect.HSQLDialect;
import net.sf.hibernate.exception.ErrorCodeConverter;
import net.sf.hibernate.exception.JDBCExceptionHelper;
import net.sf.hibernate.exception.TemplatedViolatedConstraintNameExtracter;
import net.sf.hibernate.exception.ViolatedConstraintNameExtracter;

/**
 * An SQL dialect compatible with HSQLDB sequences (HSQLDB version 1.7.2+)
 * This is backported from Hibernate 3 in order to enable unit testing with Hibernate2
 * against HsqlDB with mappings that use sequences.
 * 
 * @author Matt Campbell
 */
public class HSQLSeqDialect extends HSQLDialect {
   
    public String getSequenceNextValString(String sequenceName) {
      return "select next value for " + sequenceName
            + " from SYSTEM_SEQUENCES";
   }
    
//   public String getSequenceNextValString(String sequenceName) {
//      return "select next value for " + sequenceName + " from dual_" + sequenceName;
//   }

   public String getCreateSequenceString(String sequenceName) {
      return "create sequence " + sequenceName;
   }
   
//   public String[] getCreateSequenceStrings(String sequenceName) {
//      return new String[]{
//         "create table dual_" + sequenceName + " (zero integer)",
//         "insert into dual_" + sequenceName + " values (0)",
//         "create sequence " + sequenceName + " start with 1"
//      };
//   }

   public String getDropSequenceString(String sequenceName) {
      return "drop sequence " + sequenceName;
   }

//   public String[] getDropSequenceStrings(String sequenceName) {
//      return new String[]{
//         "drop table dual_" + sequenceName + " if exists",
//         "drop sequence " + sequenceName
//      };
//   }

   public boolean supportsSequences() {
      return true;
   }

   public String getQuerySequencesString() {
      return "select sequence_name from system_sequences";
   }
   
   private static class ExceptionConverter extends ErrorCodeConverter {
      private int[] sqlGrammarCodes = new int[]{-22, -28};
      private int[] integrityViolationCodes = new int[]{-8, -9, -177, -104};

      public ExceptionConverter(ViolatedConstraintNameExtracter extracter) {
         super( extracter );
      }

      protected int[] getSQLGrammarErrorCodes() {
         return sqlGrammarCodes;
      }

      protected int[] getIntegrityViolationErrorCodes() {
         return integrityViolationCodes;
      }
   }
   
   public ViolatedConstraintNameExtracter getViolatedConstraintNameExtracter() {
        return EXTRACTER;
   }

   private static ViolatedConstraintNameExtracter EXTRACTER = new TemplatedViolatedConstraintNameExtracter() {

      /**
       * Extract the name of the violated constraint from the given SQLException.
       *
       * @param sqle The exception that was the result of the constraint violation.
       * @return The extracted constraint name.
       */
      public String extractConstraintName(SQLException sqle) {
         String constraintName = null;
         
         int errorCode = JDBCExceptionHelper.extractErrorCode(sqle);

         if ( errorCode == -8 ) {
            constraintName = extractUsingTemplate( "Integrity constraint violation ", " table:", sqle.getMessage() );
         }
         else if ( errorCode == -9 ) {
            constraintName = extractUsingTemplate( "Violation of unique index: ", " in statement [", sqle.getMessage() );
         }
         else if ( errorCode == -104 ) {
            constraintName = extractUsingTemplate( "Unique constraint violation: ", " in statement [", sqle.getMessage() );
         }
         else if ( errorCode == -177 ) {
            constraintName = extractUsingTemplate( "Integrity constraint violation - no parent ", " table:", sqle.getMessage() );
         }

         return constraintName;
      }

   };
}

nathanmoon · **Posted:** Tue Jun 14, 2005 6:08 pm

I've been looking into this, too (getting SchemaExport to create the schema before creating the tables), and it seems that it is not possible, currently, to get Hibernate to do it (in H3). (Someone please correct me if I'm wrong!)

Another nice feature would be if SchemaExport would create the actual db, too...

PaulWilliams · **Joined:** Thu May 11, 2006 6:49 am **Posts:** 3

Does anyone have an answer to this?

I'm having a similar problem - using hsqldb in 'in memory' mode, and relying on hibernate to create the schema at the start of a unit test.

Yet I'm getting 'invalid schema name' errors. Can you force hibernate to create the schema before it creates the tables?

max · **Posted:** Thu May 11, 2006 10:18 am

<database-object ...>

PaulWilliams · **Joined:** Thu May 11, 2006 6:49 am **Posts:** 3

max wrote:

<database-object ...>

Cool. That seems to do the trick. But if i create a CreateSchema.hbm.xml file to create my schema, how do I force Hibernate to process this before any of the hbm files that represent my tables? I cant see anty way of saying that a given mapping depends on another mapping.....

max · **Posted:** Thu May 11, 2006 12:16 pm

did you try and see if it just works ?

de107549 · **Joined:** Mon Mar 07, 2005 3:34 am **Posts:** 3

Hello,

I do have the same problem and I've tried the <database-object> tag. It is executed AFTER the create table statements.

Thank you.

Mirko

max · **Posted:** Thu Jul 20, 2006 5:40 am

so you need to create the schema your self before calling hibernate.

JAYJAYBEE · **Joined:** Sat Oct 29, 2005 7:01 am **Posts:** 4

Hello,

I currently try to perform unit tests for my web application.

I'm using

I encountered the same problem : Schema creation is made after table creation.

So i decided to :

Results:
in my hsql database .script file, i have my complete database script successfully generated and it seems correct. Though, when i try to insert a row in a table using my DAOs that i want to test, i have an error:
"Table not found in statement [select max(ID) from DEPARTMENT]"

Well in fact, DEPARTMENT table should be accessed as "MYSCHEMA1.DEPARTMENT" because it belongs to MYSCHEMA1.

Do you have any idea or solution to this problem ?

JAYJAYBEE · **Joined:** Sat Oct 29, 2005 7:01 am **Posts:** 4

I kept on trying make "unit test" for my application with HSQLDB (not in memory database but a file one). The latter is successully created as I checked it using an external database explorer. Schemas are created and tables too.

Though, my problem is the following:
Schemas (declared in each hibernate mapping file) are not taken into account in any underlying sql queries generated by hibernate. As a consequence, exceptions are thrown (tables not found).

Do I Have to configure something in hibernate ?. I currently use HSQLDialect for my unit tests. Is dialect the cause of my problem ?

JAYJAYBEE · **Joined:** Sat Oct 29, 2005 7:01 am **Posts:** 4

IGx89 · **Joined:** Fri Jul 28, 2006 10:53 am **Posts:** 15

I'm also currently being stymied by this Hibernate bug; any new information?

vernid · **Joined:** Tue Mar 21, 2006 1:29 pm **Posts:** 9

Hi,

In our development environment we use continuum and dbUnit to continouesly run our dao unit test.

With dbunit we reload the database with clean data set before each unit test and then of course if we have only one database to share accross all projects setup in continuum, we can come with a concurrency issue. b.e. Project 1 starts, resets the database while project 2 unit-tests are running.

In such environment an in-memory database (i.e HSQLDB or H2) can solve this issue.

Our application uses also different db schemas.

Now, we have the same problem as described above as we use an in-memory db, we don't have the chance to create 'in a nice way' the schema before running the tests.

As the database-object always runs AFTER the table creation, can I suggest that the hbm2ddl tool creates the schemas according to the same rule as the table (update, create, create-drop...) and this before trying to create the first table of a given schema.

This should solve our problems, at least we will be able to use in-memory database.

Thx and Best Regards
Dominique

ScotHale · **Joined:** Tue Jan 09, 2007 3:24 pm **Posts:** 1

Here I am using my production hibernate configuration xml file, and overriding it with test data programatically in this test HsqldbSessionFactorySingleton. I have to explicitly create the schema before creating the sessionFactory. Then everything should work for you in regards to unit tests. ( Note that this code was not designed for production use, just unit testing ).

Code:

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.Statement;

import org.hibernate.SessionFactory;
import org.hibernate.cfg.Configuration;

public class HsqldbSessionFactorySingleton {
   private static final String JDBC_URL = "jdbc:hsqldb:mem:images";
   private static final SessionFactory sessionFactory;

   static {
      try {
         //CREATE DATABASE MANUALLY
         Class.forName("org.hsqldb.jdbcDriver" );
         Connection conn = DriverManager.getConnection(JDBC_URL,"sa","");
         Statement st = conn.createStatement();    // statements
         String expression = "CREATE SCHEMA images AUTHORIZATION DBA";
         st.executeUpdate(expression);    // run the query
           st.close();
           conn.close();
         
         //SETUP HIBERNATE
         Configuration configuration = new Configuration().configure();

         // OVERRIDE ANY DATABASE SETTINGS WITH TEST SETTINGS
         configuration.setProperty("hibernate.connection.driver_class",
               "org.hsqldb.jdbcDriver");
         configuration.setProperty("hibernate.connection.url",
               JDBC_URL);
         configuration.setProperty("hibernate.connection.username", "sa");
         configuration.setProperty("hibernate.connection.password", "");
         configuration.setProperty("hibernate.connection.pool_size", "1");
         configuration.setProperty("hibernate.dialect", "org.hibernate.dialect.HSQLDialect");
         configuration.setProperty("hibernate.current_session_context_class", "thread");
         configuration.setProperty("hibernate.cache.provider_class", "org.hibernate.cache.NoCacheProvider");
         configuration.setProperty("hibernate.show_sql", "true");
         configuration.setProperty("hibernate.format_sql", "true");
         configuration.setProperty("hibernate.use_sql_comments", "true");
         configuration.setProperty("hibernate.hbm2ddl.auto", "create-drop");
         // Create the SessionFactory from hibernate.cfg.xml
         sessionFactory = configuration.buildSessionFactory();
      } catch (Throwable ex) {
         // Make sure you log the exception, as it might be swallowed
         System.err.println("Initial SessionFactory creation failed." + ex);
         throw new ExceptionInInitializerError(ex);
      }
   }

   public static SessionFactory getSessionFactory() {
      return sessionFactory;
   }
}