Hi,
I have been struggling with database schemas and hibernates default schema support (hibernate.default_schema). I use schema names that needs escaping, where schemas for H2 should be escaped with quotes e.g. “(my) weird schema-name” and for SQL Server with square brackets e.g. [(my) weird schema-name]. Schemas are typically escaped just like column and table identifiers.
I followed some of the forum threads that states, you should just escape the schema name with quotes or square brackets before setting hibernate.default_schema. It kind of works. Every generated SQL statement now contains an escaped schema name. But….. :)
Hbm2dll schema update doesn’t seem to work. The first startup successfully create all the tables, but the second startup writes a lot of error messages stating that the table already exists.
First startup – Creates the database tables.
Code:
21/03/2014 06:18:40:454 [INFO] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000228: Running hbm2ddl schema update
21/03/2014 06:18:40:455 [INFO] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000102: Fetching database metadata
21/03/2014 06:18:40:508 [INFO] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000396: Updating schema
21/03/2014 06:18:52:143 [INFO] java.sql.DatabaseMetaData - HHH000262: Table not found: Attachment
21/03/2014 06:18:52:146 [INFO] java.sql.DatabaseMetaData - HHH000262: Table not found: BAMTaskSummary
21/03/2014 06:18:52:147 [INFO] java.sql.DatabaseMetaData - HHH000262: Table not found: BooleanExpression
21/03/2014 06:18:52:149 [INFO] java.sql.DatabaseMetaData - HHH000262: Table not found: Content
… more stuff here
21/03/2014 06:18:52:431 [INFO] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000232: Schema update complete
Hibernate: insert into "(MY) WEIRD SCHEMA-NAME".SessionInfo (id, lastModificationDate, rulesByteArray, startDate, OPTLOCK) values (null, ?, ?, ?, ?)
… more stuff here
Second startup – Should only cerate tables if not already exists.
Code:
21/03/2014 06:22:44:802 [INFO] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000228: Running hbm2ddl schema update
21/03/2014 06:22:44:803 [INFO] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000102: Fetching database metadata
21/03/2014 06:22:44:821 [INFO] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000396: Updating schema
21/03/2014 06:22:44:845 [INFO] java.sql.DatabaseMetaData - HHH000262: Table not found: Attachment
21/03/2014 06:22:44:856 [INFO] java.sql.DatabaseMetaData - HHH000262: Table not found: BAMTaskSummary
21/03/2014 06:22:44:866 [INFO] java.sql.DatabaseMetaData - HHH000262: Table not found: BooleanExpression
21/03/2014 06:22:44:879 [INFO] java.sql.DatabaseMetaData - HHH000262: Table not found: Content
… more stuff here
21/03/2014 06:22:45:988 [ERROR] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000388: Unsuccessful: create table "(MY) WEIRD SCHEMA-NAME".Attachment (id bigint generated by default as identity, accessType integer, attachedAt timestamp, attachmentContentId bigint not null, contentType varchar(255), name varchar(255), attachment_size integer, attachedBy_id varchar(255), TaskData_Attachments_Id bigint, primary key (id))
21/03/2014 06:22:45:988 [ERROR] org.hibernate.tool.hbm2ddl.SchemaUpdate - Table "ATTACHMENT" already exists; SQL statement:
create table "(MY) WEIRD SCHEMA-NAME".Attachment (id bigint generated by default as identity, accessType integer, attachedAt timestamp, attachmentContentId bigint not null, contentType varchar(255), name varchar(255), attachment_size integer, attachedBy_id varchar(255), TaskData_Attachments_Id bigint, primary key (id)) [42101-175]
21/03/2014 06:22:45:994 [ERROR] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000388: Unsuccessful: create table "(MY) WEIRD SCHEMA-NAME".BAMTaskSummary (pk bigint generated by default as identity, createdDate timestamp, duration bigint, endDate timestamp, processInstanceId bigint not null, startDate timestamp, status varchar(255), taskId bigint not null, taskName varchar(255), userId varchar(255), OPTLOCK integer, primary key (pk))
21/03/2014 06:22:45:994 [ERROR] org.hibernate.tool.hbm2ddl.SchemaUpdate - Table "BAMTASKSUMMARY" already exists; SQL statement:
create table "(MY) WEIRD SCHEMA-NAME".BAMTaskSummary (pk bigint generated by default as identity, createdDate timestamp, duration bigint, endDate timestamp, processInstanceId bigint not null, startDate timestamp, status varchar(255), taskId bigint not null, taskName varchar(255), userId varchar(255), OPTLOCK integer, primary key (pk)) [42101-175]
21/03/2014 06:22:45:998 [ERROR] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000388: Unsuccessful: create table "(MY) WEIRD SCHEMA-NAME".BooleanExpression (id bigint generated by default as identity, expression clob, type varchar(255), Escalation_Constraints_Id bigint, primary key (id))
21/03/2014 06:22:45:998 [ERROR] org.hibernate.tool.hbm2ddl.SchemaUpdate - Table "BOOLEANEXPRESSION" already exists; SQL statement:
create table "(MY) WEIRD SCHEMA-NAME".BooleanExpression (id bigint generated by default as identity, expression clob, type varchar(255), Escalation_Constraints_Id bigint, primary key (id)) [42101-175]
21/03/2014 06:22:46:000 [ERROR] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000388: Unsuccessful: create table "(MY) WEIRD SCHEMA-NAME".Content (id bigint generated by default as identity, content blob, primary key (id))
21/03/2014 06:22:46:000 [ERROR] org.hibernate.tool.hbm2ddl.SchemaUpdate - Table "CONTENT" already exists; SQL statement:
create table "(MY) WEIRD SCHEMA-NAME".Content (id bigint generated by default as identity, content blob, primary key (id)) [42101-175]
… more stuff here
21/03/2014 06:22:46:556 [INFO] org.hibernate.tool.hbm2ddl.SchemaUpdate - HHH000232: Schema update complete
Hibernate: select sessioninf0_.id as id1_26_0_, sessioninf0_.lastModificationDate as lastModi2_26_0_, sessioninf0_.rulesByteArray as rulesByt3_26_0_, sessioninf0_.startDate as startDat4_26_0_, sessioninf0_.OPTLOCK as OPTLOCK5_26_0_ from "(MY) WEIRD SCHEMA-NAME".SessionInfo sessioninf0_ where sessioninf0_.id=?
… more stuff here
My setup.
The database contains a schema with non-standard characters. Hibernate is configured with hibernate.hbm2ddl.auto=update and hibernate.default_schema=”(my) weird schema-name” for H2 and with square brackets for SQL Server. I’m running with Hibernate 4.2.11.Final.
Problems identified.
Investigations so far has shown that, org.hibernate.tool.hbm2ddl.DatabaseMetadata#getTableMetadata() invokes java.sql.DatabaseMetaData#getTables(catalog,schema,name,views) where the schema name is still escaped. I know that this seams obviously since I specified this with hibernate.default_schema. But the problem is that it isn’t a valid argument for a table search. It simply doesn’t find any matches when searching for tables since the schema argument is escaped with either quotes or square brackets. The argument is meant to be a schema pattern for a search and not an escaped schema name.
Workaround
As a work around for now, I have overridden org.hibernate.tool.hbm2ddl.DatabaseMetadata and ensured that the schema is properly unescaped before the call to java.sql.DatabaseMetaData#getTables(..) and it works perfectly even for obscure schema names as [¥ɖאܐઠૌಲೋ] and [!#¤abc&/()?´¨~'*@$€]. But it’s never great to have overridden framework code in your project.
Proposals
I have been looking for alternatives. It would be very nice if hibernate.globally_quoted_identifiers also worked for schemas and not only for column and table identifiers. If I enable globally quoted identifiers and doesn’t escape the hibernate default schema, it gives the following invalid SQL statement.
Code:
Hibernate: insert into (my) weird schema-name."SessionInfo" ("id", "lastModificationDate", "rulesByteArray", "startDate", "OPTLOCK") values (null, ?, ?, ?, ?)
But it would indeed be nice if it also supported escaping for schemas. Another alternative would be if hibernate.default_schema also supported the use of ` as it does if the schema is specified by the use of annotations (this tells hibernate to use database vendor specific escaping also for schemas but only if specified through an annotation). E.g. hibernate.default_schema =`(my) weird schema-name` would also be an okay solution, but special handling will probably still be needed in org.hibernate.tool.hbm2ddl.DatabaseMetadata#getTableMetadata(), but at least the escaping is handled by hibernate then.
Now to the real questions. What do you guys think, do I miss something here, do you see it as a valid proposal for a change? Or do you know of any other approaches that would be a better work around? It should be mentioned that I have no control over used schema names. It could be anything from [abc] to [¥ɖאܐઠૌಲೋ] or [!#¤abc&/()?´¨~'*@$€].
Kind regards
Torben Riis
Login
FAQ
Search