6 Replies Latest reply on Mar 1, 2008 1:18 AM by Kariem Hussein

    How to create schema for unit testing with in mem HSQLDB?

    Joe Dean Newbie

      I have schema defined on my Entity Beans.  When I try to run a unit test via testNG I get the following error when hbm2ddl tries to create my tables.


      ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table ACCOUNT.ACCOUNT (ACCOUNT_ID integer not null, BALANCE numeric not null, CODE integer not null, COMPANY_ID integer not null, CREATE_DT timestamp not null, DESCRIPTION varchar(1000), LAST_MOD_DT timestamp not null, NAME varchar(255) not null, REGION_ID integer, PROPERTY_ID integer, ACCOUNT_TYPE_ID integer not null, PARENT_ID integer, primary key (ACCOUNT_ID), unique (ACCOUNT_ID))
      ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] invalid schema name: ACCOUNT in statement [create table ACCOUNT.ACCOUNT]




      Is there any way to create the schema before the hbm2ddl tries to run the create table commands?  Or Is there a workaround for my unit tests without having to change the code of my Entity Beans.  (FYI - If I remove the ACCOUNT schema def in my entity bean it creates them fine)


      Here's the ACCOUNT entity bean....


      @Entity
      @Table(name = "ACCOUNT", schema = "ACCOUNT")
      public class Account implements java.io.Serializable {


        • 1. Re: How to create schema for unit testing with in mem HSQLDB?
          Waacow S Newbie

          Does your schema have to be named as 'ACCOUNT'? Try a different schema name. My guess will be 'ACCOUNT' is not a valid schema name in hypersonic.


          thread hijack:  Along the same question with TestNG, how do I make TestNG also run import.sql to pre-load default set of table data?

          • 2. Re: How to create schema for unit testing with in mem HSQLDB?
            Pete Muir Master

            Waacow S wrote on Feb 26, 2008 07:14 AM:


            thread hijack:  Along the same question with TestNG, how do I make TestNG also run import.sql to pre-load default set of table data?


            Check out DBUnit (we have support for this in Seam) - it makes it really easy to load sample data for testing against (much easier than import.sql imo).

            • 3. Re: How to create schema for unit testing with in mem HSQLDB?
              Joe Dean Newbie

              It doesn't matter what I call the schema it HSQLDB requires the schema to be created first.  For example I get the same problem if I change my schema to Seam or any name.  


              ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] Unsuccessful: create table SEAM.ACCOUNT (ACCOUNT_ID integer not null, BALANCE numeric not null, CODE integer not null, COMPANY_ID integer not null, CREATE_DT timestamp not null, DESCRIPTION varchar(1000), LAST_MOD_DT timestamp not null, NAME varchar(255) not null, REGION_ID integer, ACCOUNT_TYPE_ID integer not null, PROPERTY_ID integer, PARENT_ID integer, primary key (ACCOUNT_ID), unique (ACCOUNT_ID))
              ERROR [org.hibernate.tool.hbm2ddl.SchemaExport] invalid schema name: SEAM in statement [create table SEAM.ACCOUNT]



              There's got to be an easy place for me to put this code for unit testing:


              CREATE SCHEMA ACCOUNT AUTHORIZATION DBA



              so that the HSQLDB database will create this schema before hibernate starts creating my tables as defined in my Entity Beans.  FYI - The above code creates an ACCOUNT schema when run by itself.... Can anyone help with this?  I need this because I have different Entity Beans accessing tables in different schemas and I would like to avoid changing my code when running unit tests with Seam. 


              Waacow - try seam-gen.  I used seam-gen and the import.sql just worked for me.  Although, I am planning to use DBUnit to load up my test data.

              • 4. Re: How to create schema for unit testing with in mem HSQLDB?
                Waacow S Newbie

                I'm able to use DBUnitSeamTest and create dataset xml to run TestNG.


                Joe:  I think you might be able also use DBUnitSeamTest to execute sql statement to create schema. You can get a db connection by DBUnitSeamTest.getConnection().getConnection(), then you can execute your statement to create schema. 

                • 5. Re: How to create schema for unit testing with in mem HSQLDB?
                  Joe Dean Newbie

                  Waacow S wrote on Feb 27, 2008 11:11 PM:

                  Joe:  I think you might be able also use DBUnitSeamTest to execute sql statement to create schema. You can get a db connection by DBUnitSeamTest.getConnection().getConnection(), then you can execute your statement to create schema. 


                  Nope this did not work for me. hbm2ddl shema export happens before I can create the schema. 


                  Granted this is more of a hibernate problem, not Seam specific, but it is related.  So any other seam users that need to unit test with schemas.  Here's the patch I had to apply to Hibernate to get this to work:  HHH-1853 


                  Hope this helps someone...

                  • 6. Re: How to create schema for unit testing with in mem HSQLDB?
                    Kariem Hussein Newbie

                    It works for me with two different options (I use one for spring-based test and the other for the Test NG tests with embedded JBoss)



                    • leave the schema name blank so that the appropriate dialect can put it in the default place

                    • use orm.xml to specifiy a valid schema (e.g. PUBLIC) that overrides what you have in the entity beans



                    You just have to make sure that the orm.xml for your tests is not deployed with your application, and that your test code picks it up before your production orm settings file.


                    I would not patch hibernate for adding/removing a schema you don't really need. If it's not in hibernate by default (for good reasons), you might have to maintain the patch in your application to make your tests work.


                    Hoping, this could help you.