Hibernate automatic schema generation, PostgreSQL

[Limbic System]

Hi folks,

I'm having trouble integrating H2 with Hibernate & PostgreSQL.  To be honest I'm not sure this is truly an H2 issue or a Hibernate one.  The background is that we have a rather large legacy infrastructure built around Postgres and Hibernate.  All of our "unit" tests are really functional tests that actually exercise Postgres.  I'm trying to change them (or at least add new ones) so that they can talk to an H2 or HSQLDB in-memory database, both for speed and independence from the DB.  I've been stymied by a couple of problems.  

The principal issue is that Hibernate doesn't seem to auto-generate schemas if you use JPA-style annotations and a non-default schema.  I tried applying a patch suggested in the Hibernate JIRA.  With this patch applied, I see from the H2 logging that the schema creation is happening, but it's happening after the table creation, which obviously doesn't work.  If I try creating my schemas manually, it gets further along, but then it fails trying to alter constraints on tables that haven't been created yet.  As I said, I'm really not sure H2 is at fault here, but I'm hoping maybe someone on this forum has some experience with this. 

I'm using the following.

            String jdbcUrl = "jdbc:h2:mem:report;MODE=PostgreSQL;DB_CLOSE_DELAY=-1;TRACE_LEVEL_SYSTEM_OUT=3";

            Class.forName("org.h2.Driver");

            Connection conn = DriverManager.getConnection(jdbcUrl, "DBA", "");

            Statement st = conn.createStatement();

            // add some mappings for postgresql types

            st.executeUpdate("CREATE domain IF NOT EXISTS IP4R AS CLOB;");

            st.executeUpdate("CREATE domain IF NOT EXISTS CIDR AS CLOB;");

            st.executeUpdate("CREATE domain IF NOT EXISTS MACADDR AS CLOB;");

            st.close();

            conn.close();

            Configuration config = new AnnotationConfiguration().

                    addAnnotatedClass(com.example.JobMessage.class).       // my custom annotated class

                    addAnnotatedClass(com.example.Job.class).                    // my custom annotated class

                    setProperty("hibernate.dialect", "org.hibernate.dialect.H2Dialect").

                    setProperty("hibernate.connection.driver_class", driver).

                    setProperty("hibernate.connection.url", jdbcUrl).

                    setProperty("hibernate.connection.username", "dba").

                    setProperty("hibernate.connection.password", "").

                    setProperty("hibernate.connection.pool_size", "1").

                    setProperty("hibernate.connection.autocommit", "true").

                    setProperty("hibernate.cache.provider_class", "org.hibernate.cache.NoCacheProvider").

                    setProperty("hibernate.hbm2ddl.auto", "create").

                    setProperty("hibernate.show_sql", "true");

            SessionFactory sessionFactory = config.buildSessionFactory();

We're using Hibernate 3.2.2.ga (patched, as per above) and Hibernate-annotations 3.2.1.ga.  I tried this with both H2 1.1.101 and 1.0.79.   If someone could point me to a known-compatible set of versions of all these packages, that might do the trick.  Any other help or suggestions are welcome.

Thanks

[Mike Monkiewicz]

I'm afraid I can only be of limited help. We use Hibernate with two
separate databases, MS SQL & H2. We use Hibernate 3.3 with
annotations 3.2, I believe. So it can work. The largest difference
between our setups is that we configure Hibernate with either
hibernate.cfg.xml or through Spring's applicationContext.xml.

The one thing I did notice... have you explicitly set the schemaUpdate
property to true? It seems like it's trying to create it, so that may
not be the issue, but at least it's a start.

Also, if you switch the dialect & jdbcURL to another database, but
leave the rest of your configuration the same... does it auto-create
that database? It would at least rule out H2 (or H2+Hibernate) as the
problem.

On Oct 23, 10:35 am, "Limbic System" <limbicsys...@gmail.com> wrote:
> Hi folks,
>
> I'm having trouble integrating H2 with Hibernate & PostgreSQL.  To be honest
> I'm not sure this is truly an H2 issue or a Hibernate one.  The background
> is that we have a rather large legacy infrastructure built around Postgres
> and Hibernate.  All of our "unit" tests are really functional tests that
> actually exercise Postgres.  I'm trying to change them (or at least add new
> ones) so that they can talk to an H2 or HSQLDB in-memory database, both for
> speed and independence from the DB.  I've been stymied by a couple of
> problems.
> The principal issue is that Hibernate doesn't seem to auto-generate schemas
> if you use JPA-style annotations and a non-default schema.  I tried applying

> a patch <http://opensource.atlassian.com/projects/hibernate/browse/HHH-1853>

[Thomas Mueller]

Hi,

> All of our "unit" tests are really functional tests that actually exercise Postgres.

So your test cases work with PostgreSQL, but don't work with H2?

> setProperty("hibernate.dialect", "org.hibernate.dialect.H2Dialect").

Did you try using the PostgreSQL dialect for H2? That should work -
please tell me if it doesn't, if possible with the exception message
and the .trace.db file. Maybe you need to add ;TRACE_LEVEL_FILE=3 to
make sure enough trace information is written.

If H2 works with the PostgreSQL dialect but not the H2 dialect, then
could you run the test with both dialects and ;TRACE_LEVEL_FILE=3, and
then upload both .trace.db files?

Regards,
Thomas