SchemaValidator fail when views are involved

Description

All calls in org.hibernate.tool.hbm2ddl.DatabaseMetadata.getTableMetadata to java.sql.DatabaseMetaData.getTables set the fourth parameter (types) to {"TABLE"} (defined as a class-level constant).

In the documentation of getTables it is not defined what are the meanings of the types passed as parameter, only that all the strings must be in java.sql.DatabaseMetaData.getTablesTypes return value. In oracle9i with ojdbc14-10.2.0.1.0.jar views are returned if types contains "VIEW" and not if its {"TABLE"}. It is a major problem only if hibernate.hbm2ddl.auto is set to validate, if it's set to update it will log some errors (since it can't create or alter the table, there is a view with this name) but it won't fail the deployment, and since in SQL views are treated as tables, there will be no more problems.

It should be checked for more databases/jdbc drivers.

It is not obvious what should be the solution.
Pass {"TABLE","VIEW"} always? Pass them if they are in getTableTypes?
Put it as part of the dialect, default to {"TABLE"} and change it in oreacle-dialects?

Environment

database: oracle9i
hibernate: 3.1
jdbc driver: ojdbc14-10.2.0.1.0

Activity

Show:
Jörg Heinicke
November 21, 2006, 4:47 PM

Just wondering why it takes one year to fix such a bug? Hibernate claims often enough that views are handled like tables (e.g. http://www.hibernate.org/116.html#A27), but that's just not true due to this bug. And it is a BUG, not only an improvement, even if it only occurs on schema validation.

Instead of
<class name="MyClass" table="MY_VIEW">
...
</class>

I have to write
<class name="MyClass" table="MY_VIEW">
<subselect>select * from MY_VIEW</subselect>
...
</class>

I'm using IBM DB 2 btw, so it's no Oracle specific problem.

Jörg

Diego Plentz
September 23, 2007, 6:19 PM

I think that passing {"TABLE","VIEW"} always, will solve this issue, since the javadocs

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/DatabaseMetaData.html#getTables(java.lang.String,%20java.lang.String,%20java.lang.String,%20java.lang.String[])

say:
TABLE_TYPE String => table type. Typical types are "TABLE", "VIEW", "SYSTEM TABLE", "GLOBAL TEMPORARY", "LOCAL TEMPORARY", "ALIAS", "SYNONYM".

Yariv Yaari
September 24, 2007, 8:54 PM

Although adding the VIEW would probably work on most databases, at least as far as vies are concerned, shouldn't the solution be more general? What about other types of tables like "Materialized views" in oracle that might be tables of a different type? Why restrict the user if the object can be treated as a table? In the API of this method (getTables) It is specified that the fourth parameter (tableTypes) can be null, so that all types will be returned. Why not pass null?

Julien Kronegg
February 24, 2009, 3:26 PM

Materialized views retrieval is not working using DB2, see http://opensource.atlassian.com/projects/hibernate/browse/HHH-3780

Steve Ebersole
March 21, 2011, 7:06 PM

Bulk closing stale resolved issues

Assignee

Diego Plentz

Reporter

Yariv Yaari

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure