4

I have an issue testing a Hibernate application which queries multiple catalogs/schemas.

The production database is Sybase and in addition to entities mapped to the default catalog/schema there are two entities mapped as below. There are therefore three catalogs in total.

@Table(catalog = "corp_ref_db", schema = "dbo", name = "WORKFORCE_V2")
public class EmployeeRecord implements Serializable {
}

@Table(catalog = "reference", schema = "dbo", name="cntry")
public class Country implements Serializable {
}

This all works in the application without any issues. However when unit testing my usual strategy is to use HSQL with hibernate's ddl flag set to auto and have dbunit populate the tables.

This all works fine when the tables are all in the same schema.

However, since adding these additional tables, testing is broken as the DDL will not run as HSQL only supports one catalog.

create table corp_ref_db.dbo.WORKFORCE_V2
user lacks privilege or object not found: CORP_REF_DB

If there were only two catalogs then I think it would maybe be possible to get round this by changing the default catalog and schema in the HSQL database to that one explicitly defined:

Is there any other in-memory database for which this might work or is there any strategy for getting the tests to run in HSQL.

I had thought of providing an orm.xml file which specified the default catalog and schema (overiding any annotations and having all the defined tables created in the default catalog/schema) however these overrides do not seem to be observed when the DDL is executed i.e. I get the same error as above.

Essentially, then I would like to run my existing tests and either somehow have the tables created as they are defined in the mappings or somehow override the catalog/schema definitions at the entity level.

I cannot think of any way to achieve either outcome. Any ideas?

Gray
  • 115,027
  • 24
  • 293
  • 354
Alan Hay
  • 22,665
  • 4
  • 56
  • 110

2 Answers2

0

I believe H2 supports catalogs. I haven't used them in it myself, but there's a CATALOGS table in the Information Schema.

sharakan
  • 6,821
  • 1
  • 34
  • 61
  • Gave H2 a try. Catalog specified in the mapping is used as a schema. DDL execution threw a schema not found exception. Therefore added some custom DDL to create the schemas before the Hibernate DDL executes. Would still be an issue with the schema defined in the mapping (dbo) so set that to blank for now. Would now expect this to work however even thoug the create schemas executes correctly (CREATE SCHEMA corp_ref_db AUTHORIZATION sa )the create table statement still fails? Schema "CORP_REF_DB" not found; – Alan Hay Apr 25 '12 at 15:32
  • case sensitive? bit of a long shot, but i've had issues with that in SQL Server depending on configuration. – sharakan Apr 25 '12 at 15:58
  • I've tried upper and lower case with no success. Will have another play about with it today and see if I get anywhere. – Alan Hay Apr 26 '12 at 08:12
  • @AlanHay are you able to use a different Configuration in production vs test? If so, I believe there's a way to do this in such a way as to avoid using schemas/catalogs in HSQL (or H2). – sharakan May 10 '12 at 20:07
  • @sharakan what's your idea? I am facing the same problem, and I am able to use different configuration for production and test. :) – Elias Dorneles Aug 27 '12 at 16:06
  • @eljunior To be honest, I don't remember at all what my other idea was. But I just noticed this answer about NamingStrategy (http://stackoverflow.com/questions/4313095/jpa-hibernate-and-custom-table-prefixes) and you could use that to throw catalog/schema prefixes on to table names conditionally. – sharakan Sep 02 '12 at 01:04
  • @sharakan thanks, good to know! I've already solved my problem using custom orm.xml files to override the catalog names for the unit tests. – Elias Dorneles Sep 03 '12 at 16:25
0

I managed to achieve something like this in H2 via IGNORE_CATALOGS property and version 1.4.200

However, the url example from their docs did not seem to work for me, so I added a statement in my schema.xml: SET IGNORE_CATALOGS = true;

str8tonowhere
  • 46
  • 3